Change value according to match in another tab in pandas

user_9581909 0
Viewed 62

Hello I have two df such as

df1

COL1 COL2
A 1
A 2
B 3
B 4
C 5
D 6
E 7
F 8

df2

Old New 
A  A.1
C  C.1
E  E.1

and I would like to replace COL1values matching in df2.Old by the df2.New value.

And get : df1

COL1 COL2
A.1 1
A.1 2
B 3
B 4
C.1 5
D 6
E.1 7
F 8

Does someone have an idea please ?

pandas dataframe python-3.x python
2 Answers

Use df.to_dict with Series.map:

In [516]: x = df2.set_index('Old').to_dict()['New']

In [519]: df1['COL1'] = df1['COL1'].map(x).fillna(df1['COL1'])

In [521]: df1
Out[521]: 
  COL1  COL2
0  A.1     1
1  A.1     2
2    B     3
3    B     4
4  C.1     5
5    D     6
6  E.1     7
7    F     8

Time comparisons:

@adir bargirl's answer:

In [540]: def f1():
     ...:     df1['COL1'] = df1.merge(df2.rename(columns={'Old':'COL1'}),on='COL1',how='left')['New'].fillna(df1['COL1'])
     ...: 

In [542]: %timeit f1()
2.09 ms ± 74.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

My answer:

In [541]: def f2():
     ...:     x = df2.set_index('Old').to_dict()['New']
     ...:     df1['COL1'] = df1['COL1'].map(x).fillna(df1['COL1'])
     ...: 

In [543]: %timeit f2()
1.06 ms ± 14.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
user_58208140 
df1 = pd.read_csv(io.StringIO('''COL1 COL2
A 1
A 2
B 3
B 4
C 5
D 6
E 7
F 8 '''),sep='\s+')
df2 = pd.read_csv(io.StringIO('''Old New 
A  A.1
C  C.1
E  E.1 '''),sep='\s+')

df1['COL1'] = df1.merge(df2.rename(columns={'Old':'COL1'}),on='COL1',how='left')['New'].fillna(df1['COL1'])

Output:

    COL1    COL2
0   A.1 1
1   A.1 2
2   B   3
3   B   4
4   C.1 5
5   D   6
6   E.1 7
7   F   8
user_88938270
Related