CODEWITHSUNDEEP
pythonpandasdataframe
Ben Doner
Ben Doner

Reputation: 301

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

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!

Upvotes: 1

Views: 3047

Answers (3)

fantabolous
fantabolous

Reputation: 22696

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.

Upvotes: 0

VishRajpal
VishRajpal

Reputation: 56

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')

Upvotes: 1

M4GIMAX
M4GIMAX

Reputation: 1

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.

Upvotes: 0

Related Questions