python - replace values in dataframe using another dataframe with same index

Question

I have a dataframe with missing values for some columns:

a = pd.DataFrame(data = {"name":['bob','sue','dave'],'status':[np.NaN,np.NaN,'A'],'team':['red','blue',np.NaN]},index=[100,101,105])

Dataframe a

I have another dataframe with the same index where some of the missing values have been replaced:

b = pd.DataFrame(data = {"name":['bob','sue','dave'],'status':['I','O','A'],'team':['red','blue',np.NaN]},index=[100,101,105])

Dataframe b

Is there a way to map dataframe b to a so that the values for specific columns in a are replaced? There are lots of other rows in a that aren't in b so I only want to replace the rows that have the same index.

I tried this but it sets the values to np.NaN:

a['status'] = a['status'].map(b['status'])
a['team'] = a['team'].map(b['team'])

Dataframe a after mapping

a.combine_first(b) ?

sushanth
– sushanth

2020-08-13 15:41:00 +00:00
Commented Aug 13, 2020 at 15:41 — sushanth
– sushanth, Commented Aug 13, 2020 at 15:41

Olasimbo · Accepted Answer · 2020-08-13 16:53:51Z

1

It can be done by using the slice operation. The index of the second dataframe is used to slice the first dataframe

You then assign the second dataframe.

a.loc[b.index] = b

Output:

     name status  team
100   bob      I   red
101   sue      O  blue
105  dave      A   NaN

answered Aug 13, 2020 at 16:53

Olasimbo

1,0637 silver badges15 bronze badges

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

Comments

Frans Sjöström · Accepted Answer · 2020-08-13 16:57:12Z

1

All credit to @Sushanth

>>> a.combine_first(b)
     name status  team
100   bob      I   red
101   sue      O  blue
105  dave      A   NaN

answered Aug 13, 2020 at 16:57

Frans Sjöström

4654 silver badges8 bronze badges

Collectives™ on Stack Overflow

python - replace values in dataframe using another dataframe with same index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related