CODEWITHSUNDEEP
pythonpandasdataframe
Clock Slave
Clock Slave

Reputation: 7977

Pandas join two dataframes and keep data from one dataset if some rows are not present in other dataframe

I have two dataframes, df1 and df2

import pandas as pd

df1 = pd.DataFrame([["name_"+str(i) for i in range(10)], list(range(10))]).transpose()
df1.columns = ["Name", "Score"]

df2 = pd.DataFrame([["name_"+str(i) for i in range(2,7)], list(range(12,17))]).transpose()
df2.columns = ["Name", "Score"]

I want to join the dataframes such that all rows that are in df1 are present in the result but wherever the Name values in df1 are present in df2, the result should contain values from df2. Essentially, I want to update df1 with values from df2 wherever the Name the matches

This is what I am looking for

     Name  Score
0  name_0      0
1  name_1      1
2  name_2     12
3  name_3     13
4  name_4     14
5  name_5     15
6  name_6     16
7  name_7      7
8  name_8      8
9  name_9      9

Is there a way I can do this operation using a join operation. I was able to do this using an assignment operation

df1.loc[df1['Name'].isin(df2['Name']), "Score"] = df2["Score"].values

Is there a way I can do this using a join operation?

Upvotes: 1

Views: 36

Answers (1)

jezrael
jezrael

Reputation: 862921

One idea is use DataFrame.update:

df1 = df1.set_index('Name')
df2 = df2.set_index('Name')

df1.update(df2)
df1 = df1.reset_index()
print (df1)
     Name Score
0  name_0     0
1  name_1     1
2  name_2    12
3  name_3    13
4  name_4    14
5  name_5    15
6  name_6    16
7  name_7     7
8  name_8     8
9  name_9     9

Is there a way I can do this using a join operation?

Yes, you can join together and then replace missing values with Series.fillna and DataFrame.pop for extract column:

df = df1.join(df2.set_index('Name'), on='Name', lsuffix='_')
#merge alternative
#df = df1.merge(df2, on='Name', how='left', suffixes=('_', ''))
df['Score'] = df['Score'].fillna(df.pop('Score_'))
print (df)
     Name  Score
0  name_0      0
1  name_1      1
2  name_2     12
3  name_3     13
4  name_4     14
5  name_5     15
6  name_6     16
7  name_7      7
8  name_8      8
9  name_9      9

Upvotes: 1

Related Questions