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

Answer 1

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