CODEWITHSUNDEEP
pythonpandas
ah bon
ah bon

Reputation: 10061

Merge dataframes specific columns together based on one column in Pandas

Let's say I have the following two dataframes:

import pandas as pd

df1 = pd.DataFrame({'ID': ['01', '02', '03', '04', '05', '06'],
    'Name':['Jack','Sue', pd.np.nan,'Bob','Alice','John'],
    'City':['Seattle','SF','LA','OC', pd.np.nan, pd.np.nan],
    'A': [1, 2.1, pd.np.nan, 4.7, 5.6, 6.8],
    'B': [.25, pd.np.nan, pd.np.nan, 4, 12.2, 14.4]})

df2 = pd.DataFrame({'id': ['03', '05', '06', '07', '08', '09'],
    'Name':['Mery',pd.np.nan, pd.np.nan,'Bill','Alice','John'],
    'City':['NY','DC','LA','DC', 'LA', pd.np.nan],
    'A': [1, 5.6, 6.8, 4.7, 5.6, 6.8],
    'C': [0.5, pd.np.nan, pd.np.nan, 5, 3.7, 6.8],
    'Num_children':[2,0,0,3,2,1],
    'Num_pets':[5,1,0,5,2,2]})

I would like to update columns 'id', 'Name', 'City', 'A', 'C', 'Num_children' from df2 to df1, using ID from df1 and 'id' from df2 as key, this is expected output I want:

     ID   Name     City    A      B    C      Num_children 
0   01   Jack  Seattle  1.0   0.25  NaN           NaN  
1   02    Sue       SF  2.1    NaN  NaN           NaN     
2   03   Mery       LA  1.0    NaN  0.5           2.0     
3   04    Bob       OC  4.7   4.00  NaN           NaN     
4   05  Alice       DC  5.6  12.20  NaN           0.0   
5   06   John       LA  6.8  14.40  NaN           0.0    
6   07   Bill       DC  4.7    NaN  5.0           3.0     
7   08  Alice       LA  5.6    NaN  3.7           2.0     
8   09   John      NaN  6.8    NaN  6.8           1.0

My actual output:

#cols_to_use = df2.columns.difference(df1.columns)
cols_to_use = ['id', 'Name', 'City', 'A', 'C', 'Num_children']
df = pd.merge(df1, df2[cols_to_use], left_on = 'ID', right_on = 'id', how='outer')
print(df)

    ID Name_x   City_x  A_x      B   id Name_y City_y  A_y    C  Num_children
0   01   Jack  Seattle  1.0   0.25  NaN    NaN    NaN  NaN  NaN           NaN
1   02    Sue       SF  2.1    NaN  NaN    NaN    NaN  NaN  NaN           NaN
2   03    NaN       LA  NaN    NaN   03   Mery     NY  1.0  0.5           2.0
3   04    Bob       OC  4.7   4.00  NaN    NaN    NaN  NaN  NaN           NaN
4   05  Alice      NaN  5.6  12.20   05    NaN     DC  5.6  NaN           0.0
5   06   John      NaN  6.8  14.40   06    NaN     LA  6.8  NaN           0.0
6  NaN    NaN      NaN  NaN    NaN   07   Bill     DC  4.7  5.0           3.0
7  NaN    NaN      NaN  NaN    NaN   08  Alice     LA  5.6  3.7           2.0
8  NaN    NaN      NaN  NaN    NaN   09   John    NaN  6.8  6.8           1.0

How could merge them correctly? Thanks.

Upvotes: 1

Views: 35

Answers (1)

jezrael
jezrael

Reputation: 863611

Use DataFrame.combine_first with DataFrame.set_index, last get new index name by DataFrame.rename_axis with DataFrame.reset_index:

cols_to_use = ['id', 'Name', 'City', 'A', 'C', 'Num_children']
df = (df2[cols_to_use].set_index('id')
                      .combine_first(df1.set_index('ID'))
                      .rename_axis('ID')
                      .reset_index())
print (df)
   ID    A      B    C     City   Name  Num_children
0  01  1.0   0.25  NaN  Seattle   Jack           NaN
1  02  2.1    NaN  NaN       SF    Sue           NaN
2  03  1.0    NaN  0.5       NY   Mery           2.0
3  04  4.7   4.00  NaN       OC    Bob           NaN
4  05  5.6  12.20  NaN       DC  Alice           0.0
5  06  6.8  14.40  NaN       LA   John           0.0
6  07  4.7    NaN  5.0       DC   Bill           3.0
7  08  5.6    NaN  3.7       LA  Alice           2.0
8  09  6.8    NaN  6.8      NaN   John           1.0

Upvotes: 1

Related Questions