CODEWITHSUNDEEP
pythonpandasdataframeappendconcatenation
DPdl
DPdl

Reputation: 755

Merging/Combining Dataframes in Pandas

I have a df1, example:

     B    A    C
B         1
A              1
C    2

,and a df2, example:

    C    E    D
C        2    3
E             1
D   2

The column and row 'C' is common in both dataframes.

I would like to combine these dataframes such that I get,

    B    A    C    D    E
B        1
A             1
C   2              2    3
D                       1
E   2

Is there an easy way to do this? pd.concat and pd.append do not seem to work. Thanks!

Edit: df1.combine_first(df2) works (thanks @jezarel), but can we keep the original ordering?

Upvotes: 0

Views: 151

Answers (1)

jezrael
jezrael

Reputation: 862481

There is problem combine_first always sorted columns namd index, so need reindex with combine columns names:

idx = df1.columns.append(df2.columns).unique()
print (idx)
Index(['B', 'A', 'C', 'E', 'D'], dtype='object')

df = df1.combine_first(df2).reindex(index=idx, columns=idx)
print (df)
     B    A    C    E    D
B  NaN  1.0  NaN  NaN  NaN
A  NaN  NaN  1.0  NaN  NaN
C  2.0  NaN  NaN  2.0  3.0
E  NaN  NaN  NaN  NaN  1.0
D  NaN  NaN  2.0  NaN  NaN

More general solution:

c = df1.columns.append(df2.columns).unique()
i = df1.index.append(df2.index).unique()

df = df1.combine_first(df2).reindex(index=i, columns=c)

Upvotes: 2

Related Questions