Merge or Concat Dataframes using Pandas

Question

Simplified. I have 2 dataframes that I would like to merge/concatenate/join together into one using the following scenario as framework.

df1 looks like

              C1              C2       C3
0   1659712000.0    1659712000.0    YQHDK
1   5797862000.0    5797862000.0    YQHJW
2    846369000.0     846369000.0    YQHMF
3    508287000.0     508287000.0    YQHRV
4    878002000.0     878002000.0    YQHVT
5            NaN    5178784324.0    YQHRM

While df2 looks like

       C3             C1
0   YQHRM   2362463460.0

What I desire is to fill in the NaN value as follows:

              C1              C2       C3
0   1659712000.0    1659712000.0    YQHDK
1   5797862000.0    5797862000.0    YQHJW
2    846369000.0     846369000.0    YQHMF
3    508287000.0     508287000.0    YQHRV
4    878002000.0     878002000.0    YQHVT
5   2362463460.0    5178784324.0    YQHRM

I've tried using df1.merge(df2, how='left', on='C3), but this creates two C1 columns, a C1_x and a C1_y.

I've also tried using pd.concat([df1, df2]) but this results in two rows for YQHRM'.

What am I missing here?

Stefan · Accepted Answer · 2016-06-08 15:15:39Z

3

Take a look at combine_first():

df1.set_index('C3')
df2.set_index('C3')    
df2.combine_first(df1)

                 C1          C2
C3                             
YQHDK  1.659712e+09  1659712000
YQHJW  5.797862e+09  5797862000
YQHMF  8.463690e+08   846369000
YQHRM  2.362463e+09  5178784324
YQHRV  5.082870e+08   508287000
YQHVT  8.780020e+08   878002000

df2.reset_index() will revert the index back to column.

answered Jun 8, 2016 at 15:15

Stefan

43.1k13 gold badges80 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge or Concat Dataframes using Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related