Python pandas - particular merge/replacement

Question

new to pandas operations, I have these two dataframes:

import pandas as pd 

df = pd.DataFrame({'name': ['a','a','b','b','c','c'], 'id':[1,2,1,2,1,2], 'val1':[0,0,0,0,0,0],'val2':[0,0,0,0,0,0],'val3':[0,0,0,0,0,0]})

   id name  val1  val2  val3
0   1    a     0     0     0
1   2    a     0     0     0
2   1    b     0     0     0
3   2    b     0     0     0
4   1    c     0     0     0
5   2    c     0     0     0

subdf = pd.DataFrame({'name': ['a','b','c'], 'id':[1,1,2],'val1':[0.3,0.4,0.7], 'val2':[4,5,4]}

   id name  val1  val2
0   1    a   0.3     4
1   1    b   0.4     5
2   2    c   0.7     4

I would like to obtain as output:

   id name  val1  val2  val3
0   1    a   0.3     4     0
1   2    a   0.0     0     0
2   1    b   0.4     5     0
3   2    b   0.0     0     0
4   1    c   0.0     0     0
5   2    c   0.7     4     0

But I did not catch example of replacement, just additions of columns/rows from the tutorials I saw !

EdChum · Accepted Answer · 2014-09-10T11:02:05.887

This takes a couple steps, left merge on the columns that match, this will create 'x' and 'y' where there are clashes:

In [25]:

merged = df.merge(subdf, on=['id', 'name'], how='left')
merged
Out[25]:
   id name  val1_x  val2_x  val3  val1_y  val2_y
0   1    a       0       0     0     0.3       4
1   2    a       0       0     0     NaN     NaN
2   1    b       0       0     0     0.4       5
3   2    b       0       0     0     NaN     NaN
4   1    c       0       0     0     NaN     NaN
5   2    c       0       0     0     0.7       4
In [26]:
# take the values that of interest from the clashes
merged['val1'] = np.max(merged[['val1_x', 'val1_y']], axis=1)
merged['val2'] = np.max(merged[['val2_x', 'val2_y']], axis=1)
merged
Out[26]:
   id name  val1_x  val2_x  val3  val1_y  val2_y  val1  val2
0   1    a       0       0     0     0.3       4   0.3     4
1   2    a       0       0     0     NaN     NaN   0.0     0
2   1    b       0       0     0     0.4       5   0.4     5
3   2    b       0       0     0     NaN     NaN   0.0     0
4   1    c       0       0     0     NaN     NaN   0.0     0
5   2    c       0       0     0     0.7       4   0.7     4
In [27]:
# drop the additional columns
merged = merged.drop(labels=['val1_x', 'val1_y','val2_x', 'val2_y'], axis=1)
merged
Out[27]:
   id name  val3  val1  val2
0   1    a     0   0.3     4
1   2    a     0   0.0     0
2   1    b     0   0.4     5
3   2    b     0   0.0     0
4   1    c     0   0.0     0
5   2    c     0   0.7     4

Another method would be to sort both df's on 'id' and 'name' and then call update:

In [30]:

df = df.sort(columns=['id','name'])
subdf = subdf.sort(columns=['id','name'])
df.update(subdf)
df
Out[30]:
   id name  val1  val2  val3
0   1    a   0.3     4     0
2   2    c   0.7     4     0
4   1    c   0.0     0     0
1   1    b   0.4     5     0
3   2    b   0.0     0     0
5   2    c   0.0     0     0

In this case you used `np.max` logic to seperate the column data. In my case there are 17 columns. I want to replace all the data in main df with data coming from left df using left join. I have total 700 rows and need to left join 17 rows. When I try merge with left join , all other rows get NaN data , how can I tackle this problem? — Murtaza Haji, Sep 19 '20 at 23:57
`sort()` is deprecated. You can use `df.sort_values(by=['id, name'])` — Alex Montoya, May 18 '21 at 16:12

score 3 · Answer 2 · answered Sep 18 '18 at 21:53

The sort function in the second part of the above answer has been deprecated. The code for users using Pandas 0.20+ for achieving the same effect is:

df1 = pd.DataFrames(usecols=['A', 'B']) # You want to merge TO this
df2 = pd.DataFrames(usecols=['A', 'B']) # You want to merge FROM this 
df1 = df1.sort_values (by=['A', 'B'])
df2 = df2.sort_values (by=['A', 'B'])
df1.update(df2)

Refer to: Pandas Documentation

score 3 · Answer 3 · answered Nov 14 '19 at 16:53

Updated version with update method. Inspired by Nic

I managed to it with concat but is not as elegant as this one below with update and DataFrame are copied, which I believe with bigger tables could result in problems with memory and/or speed.

df = pd.DataFrame({'name': list('aabbcc'), 'id':[1,2]*3, 'val1':[0]*6,'val2':[0]*6,'val3':[0]*6})

subdf = pd.DataFrame({'name': list('abc'), 'id':[1,1,2],'val1':[0.3,0.4,0.7], 'val2':[4,5,4]})

df.set_index(['name','id'], inplace=True)
df.update(subdf.set_index(['name','id']))
df.reset_index(inplace=True)
df

Result:

    name    id  val1    val2    val3
0   a       1   0.3     4.0     0
1   a       2   0.0     0.0     0
2   b       1   0.4     5.0     0
3   b       2   0.0     0.0     0
4   c       1   0.0     0.0     0
5   c       2   0.7     4.0     0

Minor drawback is that pandas.DataFrame.update changes the dtypes pointed out by JAB

score 1 · Answer 4 · answered Sep 02 '20 at 15:09

Another solution is, if all the values of val1 and val2 are 0, you can delete the columns

df = pd.DataFrame({'name': ['a','a','b','b','c','c'], 'id':[1,2,1,2,1,2], 'val1':[0,0,0,0,0,0],'val2':[0,0,0,0,0,0],'val3':[0,0,0,0,0,0]})
subdf = pd.DataFrame({'name': ['a','b','c'], 'id':[1,1,2],'val1':[0.3,0.4,0.7], 'val2':[4,5,4]})

print (df)

   id name  val1  val2  val3
0   1    a     0     0     0
1   2    a     0     0     0
2   1    b     0     0     0
3   2    b     0     0     0
4   1    c     0     0     0
5   2    c     0     0     0

print (subdf)

   id name  val1  val2
0   1    a   0.3     4
1   1    b   0.4     5
2   2    c   0.7     4 

df = df.drop(['val1', 'val2'], axis=1)

print (df)

   id name  val3
0   1    a     0
1   2    a     0
2   1    b     0
3   2    b     0
4   1    c     0
5   2    c     0

Then do the merge

df = df.merge(subdf, on=['id', 'name'], how='left')

print (df)

  name  id  val3  val1  val2
0    a   1     0   0.3   4.0
1    a   2     0   NaN   NaN
2    b   1     0   0.4   5.0
3    b   2     0   NaN   NaN
4    c   1     0   NaN   NaN
5    c   2     0   0.7   4.0

And finally use fillna to replace the NaN values.

df['val1'].fillna(0, inplace=True)
df['val2'].fillna(0, inplace=True)

print (df)

  name  id  val3  val1  val2
0    a   1     0   0.3   4.0
1    a   2     0   0.0   0.0
2    b   1     0   0.4   5.0
3    b   2     0   0.0   0.0
4    c   1     0   0.0   0.0
5    c   2     0   0.7   4.0

To sort the columns use

column_names = ['id', 'name', 'val1', 'val2', 'val3']
df = df.reindex(columns=column_names)

print (df)

   id name  val1  val2  val3
0   1    a   0.3   4.0     0
1   2    a   0.0   0.0     0
2   1    b   0.4   5.0     0
3   2    b   0.0   0.0     0
4   1    c   0.0   0.0     0
5   2    c   0.7   4.0     0

And to parse column to int use

df['val2'] = df['val2'].astype(int)

print (df)

   id name  val1  val2  val3
0   1    a   0.3     4     0
1   2    a   0.0     0     0
2   1    b   0.4     5     0
3   2    b   0.0     0     0
4   1    c   0.0     0     0
5   2    c   0.7     4     0

Python pandas - particular merge/replacement

4 Answers4

Linked