Pandas : Change values of a range of cells using another dataframe

Question

I hope you're doing well. So I have two dataframes, and I want to change the values of a specific range using the other dataframe as following :

So I have these two dataframes

Dataframe 1 :

    A   B   C
0   foo 2   3
1   foo 9   nan
2   foo 1   4
3   bar 90  1
4   boo 12  89

Dataframe 2 :

    M   N   O
0   foo 8   19
1   foo 3   8
2   foo 5   nan
3   bar 0   16
4   boo 1   100

So I want to replace the values of B and C concerning the 'foo' item (column A) in dataframe 1 by the values of N and O concerning the 'foo' item (column M) that exist in dataframe 2 as following :

Dataframe 1 :

    A   B   C
0   foo 8   19
1   foo 3   8
2   foo 5   nan
3   bar 90  1
4   boo 12  89

mozway · Accepted Answer · 2021-08-01 11:59:31Z

2

You can use pandas.DataFrame.where with a condition on df1['A']:

df1[['B', 'C']] = df1[['B', 'C']].where(~df1['A'].eq('foo'), df2[['B', 'C']])

or the other way around:

df1[['B', 'C']] = df2[['B', 'C']].where(df1['A'].eq('foo'), df1[['B', 'C']])

If the columns in df2 have a different name, you can access the underlying numpy array with .values:

df1[['B', 'C']] = df1[['B', 'C']].where(~df1['A'].eq('foo'), df2[['N', 'O']].values)

edited Aug 1, 2021 at 11:59

answered Aug 1, 2021 at 11:27

mozway

267k13 gold badges56 silver badges106 bronze badges

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

2 Comments

Abdelfattah Boutanes Over a year ago

Yes it works, thank you... but what if the names of the columns are different ?... for example in dataframe 1 we have A, B, C and in dataframe 2 we have M, N , O (where A and M are similar but different name, and the same for B and N, and C and O)... I have edited my question..

mozway Over a year ago

@AbdelfattahBoutanes Sure, I added a third command for this case. Let me know if this works for you.

Mohammad · Accepted Answer · 2021-08-01 11:15:05Z

0

df1 = pd.DataFrame({'A': ['foo','foo','foo','bar','boo'],'B': [2,9,1,90,12],'C':[3,np.nan,4,1,89]})
df2 = pd.DataFrame({'A': ['foo','foo','foo','bar','boo'],'B': [8,3,5,0,1],'C':[19,8,np.nan,16,100]})

df1.loc[df1['A']=='foo',['B','C']] = df2[df2['A']=='foo'][['B','C']]
df1

Gives:

    A   B   C
0   foo 8   19.0
1   foo 3   8.0
2   foo 5   NaN
3   bar 90  1.0
4   boo 12  89.0

answered Aug 1, 2021 at 11:15

Mohammad

3,4162 gold badges22 silver badges35 bronze badges

2 Comments

Steele Farnsworth Over a year ago

Any reason you don't do df2.loc[df2['A'] == 'foo', ['B', 'C']] for the right side of the assignment statement?

Mohammad Over a year ago

No, I'm just used to writing it this way. The two codes are equivalent. The only requirement is that you use loc in the left side.

Collectives™ on Stack Overflow

Pandas : Change values of a range of cells using another dataframe

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related