Pandas - Use Value in Column as key in separate dictionary

Question

I have a dictionary where the key is a name and the value is another dictionary. For example:

dict1 = {name1: {...}, name2: {...},...}

I also have a Pandas dataframe df with columns 'A', and 'B'. I would like to add a column to the dataframe using those column values as keys in dict1. For example, for each row the value in 'A' to be used as the key in dict1 and the value in 'B' to be used as the key to the inner dictionary, with the result being put in column C.

I am trying things like df[C] = dict1[df[A]][df[C]] or df[C] = df[[A,B]].apply(lambda x,y: dict1[x][y]) but nothing seems to be working. Ideally I would like do this without looping through the rows without something like df.itertuples().

Example df row (A, B):

'key1' | 'key2'

I want the following (A, B, C):

'key1' | 'key2' | dict1['key1']['key2']

Please post a testable example with your expected output , in other words a [mcve](https://stackoverflow.com/help/minimal-reproducible-example) — anky, Apr 29 '20 at 16:03
Just posted an example row of what I have vs. what I want. Please let me know if more is needed. — formicaman, Apr 29 '20 at 16:06
[How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — anky, Apr 29 '20 at 16:07

score 1 · Accepted Answer · answered Apr 29 '20 at 16:10

1

You are close - just need to modify your code for apply. Try df['C'] = df.apply(lambda row: dict1[row.A][row.B], axis=1)

answered Apr 29 '20 at 16:10

Eric Truett

2,970
1
16
21

score 0 · Answer 2 · answered Apr 29 '20 at 16:19

you can use pd.concat to create a dataframe with all your dictionaries, then rename_axis, reindex and reset_index like:

#dummy example
df  = pd.DataFrame({'A':list('aaabbc'), 
                    'B':[0,1,0,0,1,1]})
dict1 = {'a': {0:3, 1:5}, 'b': {0:1, 1:8}, 'c': {0:2, 1:6}}
# do the job
df_f = pd.concat([pd.Series(d, name='C') for d in dict1.values()], 
                 keys=dict1.keys())\
         .rename_axis(['A','B'])\
         .reindex(df[['A','B']]).reset_index()

print (df_f)
   A  B  C
0  a  0  3
1  a  1  5
2  a  0  3
3  b  0  1
4  b  1  8
5  c  1  6

Pandas - Use Value in Column as key in separate dictionary

2 Answers2