Using multiple columns to while mapping dictionary to dataframe

user_12076197 0
Viewed 611

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!

pandas dataframe python
4 Answers

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
user_42384080

Using more Python stuff in a comprehension

This is more obtuse... but I think it's fun. Likely faster in some contexts but probably not worth the added confusion.

df.assign(NewCol=[min(map(my_dict.get, t), key=pd.isna) for t in zip(*map(df.get, df))])

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

Another way uses replace on dataframe and compare against df and ffill

df['NewCol'] = df.replace(my_dict).where(lambda x: x != df).ffill(1).iloc[:,-1]

Out[550]:
   Col1  Col2   Col3 NewCol
0   Dog  Bird    Cat    AAA
1  Blue   Red  Black    BBB
2   Bad   Sad   Glad    ZZZ

Or Use stack, droplevel

df['NewCol'] = df.replace(my_dict).where(lambda x: x != df).stack().droplevel(1)
user_101892140

If a row has one key and one key only, another approach would be chaining map, ravel and dropna as below:

df['NewCol'] = pd.Series(df.apply(lambda x: x.map(my_dict)).values.ravel()).dropna().values

Output:

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