[FIXED] Merging and grouping multiple csv files with Ipython and pandas

Issue

I desire either something like this:

Column A  Column B  Column C
100       200       No Value
400       No value  500

When CSV files lookes like this:

CSV File 1

Column A  Column B
100       200

CSV File 2

Column A  Column C
400       500

I have started importing with something similar to this:

file_list = [CSV File 1, CSV File 2]
#Empty list
list = []
for n in range(len(file_list)):
    g = pd.read_csv(file_list[n], delimiter = "\;")
    list.append(g)
#Data frame for all the values
real_list = pd.concat(list, axis = 1)

This gives the result of something similar to this.

Column A    Column B    Column A    Column B
100         200         400         500

Which is wrong.

Any ideas are much appreciated =)

Solution

Just perform an outer merge:

In [8]:

df.merge(df1, how='outer')
Out[8]:
   Column A  Column B  Column C
0       100       200       NaN
1       400       NaN       500

The reason you get that result when using concat is that you are concatenating column-wise and it's aligning on the common index values

Answered By - EdChum