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 !
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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With