Merging Panda Dataframes - perserve orginial order and overwrite columns

user_13969844 0
Viewed 39
df1 = pd.DataFrame([(1,5),(2,10),(3,15)],columns=["2009","2008"],index=["C","A","B"])

   2009  2008
C     1     5
A     2    10
B     3    15

df2 = pd.DataFrame([(5,7),(11,14),(14,15)],columns=["2008","2007"],index=["D","B","C"])

   2008  2007
D     5     7
B    11    14
C    14    15

desired_output =
   2009  2008  2007
C     1     5   15
A     2    10   na
B     3    15   14
D    na     5    7

I know there are four main ways to combine two dataframes: join, merge, append, concat and I have experimented with a number of ways of doing them but I cannot seem to succeed.

df1.merge(df2,how="outer",left_index=True,right_index=True,on="2008")
   2009  2008  2007
A   2.0    10   NaN
B   3.0    15  14.0
C   1.0     5  15.0
D   NaN     5   7.0

is the closest I could find - but the columns get resorted. I want all intersecting indices to come first in original order of df1, then any non-intersecting indices to be append (ideally also in order of df2)

Any help would be appreciated?

pandas python
2 Answers

You can try this using pd.Index.difference with DataFrame.append to maintain both index and columns order.

idx = df2.index.difference(df1.index)
df1.append(df2.loc[idx]).fillna(df2)

   2009  2008  2007
C   1.0     5  15.0
A   2.0    10   NaN
B   3.0    15  14.0
D   NaN     5   7.0
user_124164530

Try combine_first with reindex and union column indexes with sort=False:

df1.combine_first(df2).reindex(df1.columns.union(df2.columns, sort=False), axis=1)

Output:

    2009  2008  2007
A   2.0  10.0   NaN
B   3.0  15.0  14.0
C   1.0   5.0  15.0
D   NaN   5.0   7.0
user_63615310
Related