Copy a column from one dataframe to another when they both share an index in Pandas

Question

I am trying to copy a column of data from one dataframe to another, using the index as a reference. When copying the column, I want to fill any entry that does not appear in both dataframes with a NaN.

For example, I have these two dummy dfs:

df1 = 
       col_1  col_2  col_3  col_4
index
A          1      4      7     10
B          2      5      8     11
C          3      6      9     12

df2 = 
       col_5  col_6
index
A         13     15
C         14     16

And I would like to copy col_5 to df1 based on the shared index so df1 looks like:

df1 = 
       col_1  col_2  col_3  col_4  col_5
index
A          1      4      7     10   15
B          2      5      8     11   NaN
C          3      6      9     12   16

Since they're different lengths I can't simply do df1['col_5'] = df2['col_5'], and I didn't have any success with a df1.merge(df2, how='left'), and then I'd have to drop any unwanted columns anyway.

Any help appreciated. Thanks!

merge is the right way to go for this sort of thing. When you say you "didn't have any success" with it, what did you try, and what was wrong with your result? — G. Anderson
– G. Anderson, Commented Jul 17, 2019 at 17:56

VishRajpal · Accepted Answer · 2019-07-17 18:39:29Z

1

The merge function will work when using index as join key AND selecting out the columns from the right dataframe. merge has parameters to use left and right index in join. Example below works:

merged_df = df_1.merge(df_2['col_5'], left_index=True, right_index=True, how='left')

answered Jul 17, 2019 at 18:39

VishRajpal

564 bronze badges

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

Comments

M4GIMAX · Accepted Answer · 2019-07-17 18:12:16Z

0

You can do df1.insert(df1.shape[1], "col_5", df2["col_5"], which will put col_5 at the end (df1.shape[1] returns the number of columns in df1) of df1 with the indices properly matched.

answered Jul 17, 2019 at 18:12

M4GIMAX

11 bronze badge

Comments

fantabolous · Accepted Answer · 2023-06-06 02:31:50Z

0

Best practice is concat

df = pd.concat([df1, df2.col_5], axis='columns')

That said, as another commenter mentioned, df1['col_5'] = df2['col_5'] should work too.

answered Jun 6, 2023 at 2:31

fantabolous

22.9k8 gold badges58 silver badges52 bronze badges

Collectives™ on Stack Overflow

Copy a column from one dataframe to another when they both share an index in Pandas

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related