Need to merge a DataFrame with another DataFrame without affecting existing data
df1:
| Name | Subject | mark |
|---|---|---|
| a | Ta | 52 |
| b | En | |
| c | Ma | |
| d | Ss | 60 |
df2:
| Name | mark |
|---|---|
| b | 57 |
| c | 58 |
Expected Output:
| Name | Subject | mark |
|---|---|---|
| a | Ta | 52 |
| b | En | 57 |
| c | Ma | 58 |
| d | Ss | 60 |
Add a comment
|
3 Answers
Use combine_first after setting Name as index:
df1.set_index('Name').combine_first(df2.set_index('Name')).reset_index()
output:
Name Subject mark
0 a Ta 52.0
1 b En 57.0
2 c Ma 58.0
3 d Ss 60.0
Comments
Try using merge and combine_first:
>>> df = df1.merge(df2, on='Name', how='outer')
>>> df['mark'] = df.pop('mark_x').combine_first(df.pop('mark_y'))
>>> df
Name Subject mark
0 a Ta 52.0
1 b En 57.0
2 c Ma 58.0
3 d Ss 60.0
>>>
2 Comments
mozway
Looks a bit complicated for a simple combine ;) combine first uses index for keys (see my answer)
U13-Forward
@mozway Ah you nailed it... nice one +1
One of the ways in which you can achieve this is by using the below steps:
- Inner join the 2 tables using the
pandas.merge()command. - Create a new column which basically checks if the
markscolumn fromdf1is not None, then take that value, else, takedf2column value.
