Replace values in pandas dataframe based on other dataframe

Question

I have two dataframes, one in the form of:

#   X   Y   
1   2   0.0 
2   5   0.0 
3   10  0.0 
4   15  0.0 
5   17  0.0 
6   21  0.0

and one in the form of:

I want to replace all the ABC values from the second dataframe, with the X values; so I want go over the ABC df and if the number matches the # of df1 to replace it with the X value

the end table should look:

is there a way I can do it?

BENY · Accepted Answer · 2019-08-20 18:45:22Z

3

IIUC replace

df1.replace(df.set_index('#').X)
Out[382]: 
    A   B   C
0   2  15   5
1   5  17  10
2  10  21  15

answered Aug 20, 2019 at 18:45

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user7440787 · Accepted Answer · 2019-08-20 18:35:39Z

1

say your first DataFrame is a and your second is b, you can map b columns to a.x values like this:

b.apply(lambda y: a.x[(y -1).tolist()].values)

The result is:

    A   B   C
0   2  15   5
1   5  17  10
2  10  21  15

answered Aug 20, 2019 at 18:35

user7440787

8417 silver badges23 bronze badges

Comments

ansev · Accepted Answer · 2019-08-20 18:43:48Z

1

Only you should use:

df1.set_index('#',inplace = True)
df=df.apply(lambda x: x.replace(df1.loc[x,'X']))

Example:

import pandas as pd
import numpy as np
df1=pd.DataFrame()
df1['#']=[1,2,3,4,5,6]
df1['X']=[2,5,10,15,17,21]
df1['Y']=[0,0,0,0,0,0]
df=pd.DataFrame()
df['A']=[1,2,3]
df['B']=[4,5,6]
df['C']=[2,3,4]
df1.set_index('#',inplace = True)
df=df.apply(lambda x: x.replace(df1.loc[x,'X']))
print(df)

Output:

    A   B   C
0   2  15   5
1   5  17  10
2  10  21  15

Note df1.set_index('#',inplace = True) set '#' column like index . if this column was already the index it is not necessary to execute it

edited Aug 20, 2019 at 18:43

answered Aug 20, 2019 at 18:37

ansev

31k5 gold badges21 silver badges33 bronze badges

Collectives™ on Stack Overflow

Replace values in pandas dataframe based on other dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related