Using multiple columns to while mapping dictionary to dataframe

Question

Looking to use multiple columns for creating a new column while using a dictionary to create the new columns values. Simple example below:

df:

Col1     Col2    Col3
Dog      Bird    Cat
Blue     Red     Black
Bad      Sad     Glad

my_dict = {'Bird': 'AAA','Blue':'BBB','Glad':'ZZZ'}

desired df:

Col1     Col2    Col3      NewCol
Dog      Bird    Cat       AAA
Blue     Red     Black     BBB
Bad      Sad     Glad      ZZZ

I've played around with the map function (df.NewCol = df.Col.map(my_dict))... but it only allows me to use one column to search for the keys in my dictionary. I need the Col1, Col2, AND Col3 columns to search through my dictionary in order to create NewCol.

Any ideas? thanks!

Answer 1

Option 1: apply map with ffill. This doesn't assume one valid entry per row.

# this will take the last occurrence of valid entry in a row
# change to .bfill(1).iloc[:,0] to get the first
df['NewCol'] = df.apply(lambda x: x.map(my_dict)).ffill(1).iloc[:,-1]

Option 2: map on stack and assign. This approach assumes only one valid entry per row.

df['NewCol'] = (df.stack().map(my_dict)
                  .reset_index(level=1, drop=True)
                  .dropna()
               )

Output:

   Col1  Col2   Col3 NewCol
0   Dog  Bird    Cat    AAA
1  Blue   Red  Black    BBB
2   Bad   Sad   Glad    ZZZ