Combine two dataframes with same values in several columns

Question

I would like to combine two datframe with same index number but different columns number like:

>>> df1 col_1 col_2 col_3 col_4 0 a x NaN 54 1 a y 5 34 2 b z NaN 64 3 c z 7 23 >>> df2 col_1 col_2 col_3 col_4 col_5 0 a x NaN 14 14 1 b z NaN 9 7 2 c z 7 51 53 3 a y 5 87 66

The df2 will combine in df1 based on the values of col_1, col_2 andcol_3.

But the order of the rows will not be the same.

I want to combine them based on the order of df1

And the answer will be like this:

 col_1 col_2 col_3 col_4 col_4 col_5 0 a x NaN 54 14 14 1 a y 5 34 87 66 2 b z NaN 64 9 7 3 c z 7 23 51 53

I don't care about the column name, so you guys can modify them if you need.

Do you want to ignore the values in col1, col2 and col3 from df2? — razdi
– razdi, Commented May 10, 2019 at 4:25

gmds · Accepted Answer · 2019-05-10 05:34:07Z

If I understand correctly, you want to join on the indices without sorting:

result = df1.join(df2.drop(columns=['col_1', 'col_2', 'col_3']), lsuffix='x', rsuffix='y', sort=False) print(result)

Output:

 col_1 col_2 col_3 col_4x col_4y col_5 0 a x NaN 54 14 14 1 a y 5 34 9 7 2 b z NaN 64 51 53 3 c z 7 23 87 66

Otherwise, a simple merge on the first three columns, again without sorting, will do:

result = df1.merge(df2, on=['col_1', 'col_2', 'col_3'], sort=False) print(result)

Output:

 col_1 col_2 col_3 col_4_x col_4_y col_5 0 a x NaN 54 14 14 1 a y 5 34 87 66 2 b z NaN 64 9 7 3 c z 7 23 51 53

didn't notice the weird request (thought he just wanted it sorted based on df1.). good catch.
oh... I'm so sorry. df2 's values in col_4 and col_5 might change the order in to df1.
I have modified the result. This is my first time to ask the question, sorry
@zero Yes, it is rather strange. I originally also gave a merge-based answer.

heena bawa · Accepted Answer · 2019-05-10 04:46:56Z

You can use:

df1.merge(df2, how='right', on=['col_1', 'col_2', 'col_3'], sort=False) col_1 col_2 col_3 col_4_x col_4_y col_5 0 a x NaN 54 14 14 1 a y 5.0 34 87 66 2 b z NaN 64 9 7 3 c z 7.0 23 51 53

Ajay Shah · Accepted Answer · 2019-05-10 04:31:27Z

Just use the merge function available

import pandas as pd df=df1.merge(df2,left_index=True)

Collectives™ on Stack Overflow

Combine two dataframes with same values in several columns

3 Answers 3

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Linked

Related