Overwrite Frame with other Frame after comparing

user_15329807 0
Viewed 60

2 Frames and I want the 2nd Frame to "overwrite"/update the 1st Frame. Basically, where Table1-colB-value = Table2-oldB-value, overwrite Table1-colB-value with Table2-newB-value.

Table1

colA colB
1 X
2 Y
3 Z
4 W
5 X
6 X
7 W

Table2

oldB newB
X L
Y M
Z N
W O

Admired result after overwriting:

colA colB
1 L
2 M
3 N
4 O
5 L
6 L
7 O

Is something like that possible? I did something similar using replace, but comparing 2 frames would be way better, e.g. in case the new-value-input-frame changes you wouldn't have to adjust the replace-code at every respective point.

pandas python
1 Answers

There are plenty of ways to do this, a fast way would be to convert your df2 to dict, using oldB column as your keys, and then mapit on your colB in df1:

d = dict(zip(df2.oldB, df2.newB))
df1['new_colB'] = df1['colB'].map(d)

Will get back

   colA colB new_colB
0     1    X        L
1     2    Y        M
2     3    Z        N
3     4    W        O
4     5    X        L
5     6    X        L
6     7    W        O

EDIT: I have added a P in colB of df1 to illustrate.

You can pretty much do the same process, just wrap it in np.where(), and set the condition like below, or use fillna() to fill the nulls with 'colB'

# Using np.where
import numpy as np
df1['new_colB'] = np.where(df1['colB'].map(d).isnull(),df1['colB'],df1['colB'].map(d))

# Using fillna()
df1['new_colB'] = (df1['colB'].map(d)).fillna(df1['colB'])

I would go for 2nd option because the first one map's twice.

Both will give you:

df1
   colA colB new_colB
0     1    X        L
1     2    Y        M
2     3    Z        N
3     4    W        O
4     5    X        L
5     6    X        L
6     7    W        O
7     8    P        P
user_91673820
Related