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, May 10 '19 at 04:25
Possible duplicate of [Append column to pandas dataframe](https://stackoverflow.com/questions/20602947/append-column-to-pandas-dataframe) — razdi, May 10 '19 at 04:28

gmds · Answer 1 · 2019-05-10T05:34:07.993

3

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

edited May 10 '19 at 05:34

answered May 10 '19 at 04:28

gmds

19,325
4
32
58

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

score 1 · Answer 2 · answered May 10 '19 at 04:46

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

score 0 · Answer 3 · answered May 10 '19 at 04:31

0

Just use the merge function available

import pandas as pd

df=df1.merge(df2,left_index=True)

answered May 10 '19 at 04:31

Ajay Shah

414
5
10

Combine two dataframes with same values in several columns

3 Answers3