CODEWITHSUNDEEP
pythonpandasdataframedictionary
formicaman
formicaman

Reputation: 1357

Pandas - Use Value in Column as key in separate dictionary

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']

Upvotes: 1

Views: 1582

Answers (2)

Ben.T
Ben.T

Reputation: 29635

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

Upvotes: 0

Eric Truett
Eric Truett

Reputation: 3010

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)

Upvotes: 1

Related Questions