alphanumeric alphanumeric - 1 month ago 10
Python Question

How to concat multiple Dataframes

The

result
dataframe should be the combined version of three other dataframes:
dfa
,
dfb
and
dfc
and it should be:

a b c
0 1.0 0.0 0
1 0 10.0 0
2 0 0 100


Unfortunately the code below produces a different result:

result = pd.DataFrame()
dfa = pd.DataFrame({'a':[1]})
dfb = pd.DataFrame({'b':[10]})
dfc = pd.DataFrame({'c':[100]})

for df in [dfa, dfb, dfc]:
result = result.reindex(columns=df.columns, fill_value=0)
result = pd.concat([result, df], ignore_index=True)

print result


Here is what the results is:

c
0 0
1 0
2 100


What could be done to get the
result
needed?

Answer

I think you need Index.union for find final columns names for reindex:

cols = dfa.columns.union(dfb.columns).union(dfc.columns)
print (cols)
Index(['a', 'b', 'c'], dtype='object')

Then in loop reindex and append new DataFrames to list of DataFrames dfs, which is finally concat:

dfs = []
for df in [dfa, dfb, dfc]:
    result = df.reindex(columns=cols, fill_value=0)
    dfs.append(result)

result = pd.concat(dfs, ignore_index=True)
print (result)
   a   b    c
0  1   0    0
1  0  10    0
2  0   0  100

If in dataframes dfa, dfb and dfc are not NaN values, you can use DataFrame.fillna, but more general working with NaN is first solution:

result = pd.concat([dfa, dfb,dfc], ignore_index=True).fillna(0).astype(int)
print (result)
   a   b    c
0  1   0    0
1  0  10    0
2  0   0  100

Sample with NaN:

dfa = pd.DataFrame({'a':[1]})
dfb = pd.DataFrame({'b':[10]})
dfc = pd.DataFrame({'c':[np.nan]})

cols = dfa.columns.union(dfb.columns).union(dfc.columns)

dfs = []
for df in [dfa, dfb, dfc]:
    result = df.reindex(columns=cols, fill_value=0)
    dfs.append(result)

result = pd.concat(dfs, ignore_index=True)
print (result)
   a   b    c
0  1   0  0.0
1  0  10  0.0
2  0   0  NaN

#data NaN are overwrite by 0
print (pd.concat([dfa, dfb,dfc], ignore_index=True).fillna(0).astype(int))
   a   b  c
0  1   0  0
1  0  10  0
2  0   0  0