Pandas: mapping with dict using more than 1 column

Question

I got basic DataFrame:

df = pd.DataFrame([[1, 2],[3, 4],[5, 6],[7, 8]],
                  index=['A','B','C','D'], columns=['X','Y'])

I would like the map function to work on columns X & Y and obtain this:

   X  Y  Chk
A  1  2  found A
B  3  4  found B
C  5  6  found C
D  7  8  found D

For this, I created a dict for 2 keys:

mapped = {1:{2:'found A'}, 3:{4:'found B'},5:{6:'found C'}, 7:{8:'found D'}}

And used the applymap method on the DataFrame:

df['Chk'] = df[['X','Y']].applymap(mapped)

Unfortunately, I got an error message:

TypeError: ("'dict' object is not callable", 'occurred at index X')

Is the code wrong, or is it the dict-based mapping that simply does not support more than 1 column ?

jezrael · Accepted Answer · 2017-11-14T09:38:22.997

Create DataFrame and then Series with MultiIndex by stack first and then join:

s = pd.DataFrame(mapped).stack().rename('Chk')
print (s)
2  1    found A
4  3    found B
6  5    found C
8  7    found D
Name: Chk, dtype: object

df = df.join(s, on=['Y','X'])
print (df)
   X  Y      Chk
A  1  2  found A
B  3  4  found B
C  5  6  found C
D  7  8  found D

If possible create DataFrame for mapping and then use merge:

mapped = {'X': [1, 3, 5, 7], 
         'Chk': ['found A', 'found B', 'found C', 'found D'],
         'Y': [2, 4, 6, 8]}

df1 = pd.DataFrame(mapped)
print (df1)
       Chk  X  Y
0  found A  1  2
1  found B  3  4
2  found C  5  6
3  found D  7  8

df = pd.merge(df, df1, how='left', on=['X','Y'])
print (df)
   X  Y      Chk
0  1  2  found A
1  3  4  found B
2  5  6  found C
3  7  8  found D

Pandas: mapping with dict using more than 1 column

1 Answers1