Map the values from 2 dataframe columns to a value in a dict and create a new column-CodePudding

I have a dataframe which looks like below:

column_1 column_2
ABC       POI
XYZ       LOK 
DEF       JOY
ASD       QWE

I have a dict which has key based on column_1 and column_2 and different values like below:

d = {('ABC', 'POI'): 'Name1',
     ('XYZ', 'LOK'): 'Name2',
     ('DEF', 'JOY'): 'Name3',
     ('ASD', 'QWE'): 'Name4'}

I need to create a different column in the dataframe which maps the value of column_1 and column_2 from the dict to the new column. So, the resulted dataframe should look like below:

column_1 column_2  Result
ABC       POI       Name1
XYZ       LOK       Name2
DEF       JOY       Name3
ASD       QWE       Name4

CodePudding user response：

Use DataFrame.join with convert dictionary to Series:

#correct dict
d = {('ABC', 'POI'): 'Name1',
     ('XYZ', 'LOK'): 'Name2',
     ('DEF', 'JOY'): 'Name3',
     ('ASD', 'QWE'): 'Name4'}


df = df.join(pd.Series(d, name='Result'), on=['column_1','column_2'])
print (df)
  column_1 column_2 Result
0      ABC      POI  Name1
1      XYZ      LOK  Name2
2      DEF      JOY  Name3
3      ASD      QWE  Name4

CodePudding user response：

IMO, the simplest is to convert to MultiIndex and map you dictionary:

df['Result'] = pd.MultiIndex.from_frame(df).map(d)

or to restrict to the two relevant columns:

df['Result'] = pd.MultiIndex.from_frame(df[['column_1','column_2']]).map(d)

output:

  column_1 column_2 Result
0      ABC      POI  Name1
1      XYZ      LOK  Name2
2      DEF      JOY  Name3
3      ASD      QWE  Name4

input:

d = {('ABC', 'POI'): 'Name1',
     ('XYZ', 'LOK'): 'Name2',
     ('DEF', 'JOY'): 'Name3',
     ('ASD', 'QWE'): 'Name4'}