Map multiple columns using Series from another DataFrame

Question

I have two DataFrames. I need is to replace the text in columns B, C, and D in df1 with the values from df2['SC'], based on the value of df2['Title'].

df1

A     B      C      D
Dave  Green  Blue   Yellow
Pete  Red
Phil  Purple

df2

  A     ID   N    SC     Title
  Dave  1    5      2    Green
  Dave  1    10     2    Blue    
  Dave  1    15     3    Yellow
  Pete  2    100    3    Red
  Phil  3    200    4    Purple

Desired output:

 A     B     C      D    
 Dave  2     2     3
 Pete  3
 Phil  4

Also matching on 'A'? What happens if both Dave and Phil have 'Green' or is that never a possibility? — ALollz
– ALollz, Commented Jun 17, 2019 at 15:24
@ALollz That's a good point and I failed to mention that... Actually I think the below solution just flagged that issue as 'InvalidIndexError: Reindexing only valid with uniquely valued Index objects' — C_psy
– C_psy, Commented Jun 17, 2019 at 15:31

user3483203 · Accepted Answer · 2019-06-17 15:03:38Z

3

Using stack + map + unstack

df1.set_index('A').stack().map(df2.set_index('Title')['SC']).unstack()

        B    C    D
A
Dave  2.0  2.0  3.0
Pete  3.0  NaN  NaN
Phil  4.0  NaN  NaN

If a column contains all NaN it will be lost. To avoid this you could reindex

.reindex(df1.columns, axis=1)             # append to previous command

answered Jun 17, 2019 at 15:03

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

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

1 Comment

C_psy Over a year ago

I realise 'Title' is not necessarily unique to a Name (A) which I think causes the following - 'InvalidIndexError: Reindexing only valid with uniquely valued Index objects -- The SC will always be matched to a Title however, it's just that they could both appear more than once against different values in A

Collectives™ on Stack Overflow

Map multiple columns using Series from another DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related