3

let's suppose I have a dataframe with at least two columns col1 and col2. Also I have a dictionary of dictionaries, whose keys consist of the values in col1 resp. col2.

import pandas as pd
dict_of_dicts = {'x0': {'y0':1, 'y1':2, 'y2':3}, 'x1': {'y0':0, 'y1':0,    'y2':1}, 'x2': {'y0':2, 'y1':1, 'y2':3}} 
df = pd.DataFrame( {'col1': ['x1', 'x2', 'x2'], 'col2': ['y0', 'y1', 'y0']} )
print(df)
  col1 col2
0   x1   y0
1   x2   y1
2   x2   y0

Now I want to create a third column that contains the value of my dictionary with the keys given by col1 and col2 in the respective line. Something like

df['col3'] = dict_of_dicts[df['col1']][df['col2']].

The result should look like this:

  col1 col2  col3
0   x1   y0     0
1   x2   y1     1
2   x2   y0     2

It should be similar to "map", as explained here Adding a new pandas column with mapped value from a dictionary

But I rely on two columns. Could anybody help me with that, please?

By the way: I actually don't have to use a dictionary of dictionaries (as just described). I could also use a table (dataframe) with the one set of keys as index set of the dataframe and the other set of keys as the column names. But also here I don't know how to access a specific "cell" which would be specified by the values in col1 and col2.

I hope my problem is clear.

Thank you, Nadja

Community
  • 1
  • 1
Nadja Eb
  • 53
  • 1
  • 7
  • 1
    Your expected output doesn't match what inspection suggests you're looking for. Is that 0 correct? – DSM Mar 13 '17 at 15:11
  • you are right, there was a mistake I just corrected. Thanks. – Nadja Eb Mar 13 '17 at 15:19
  • Hello everybody, I have kind of a follow-up question: Say I do not have a dict of dicts but a dataframe. And instead of having the keys for the dict of dicts, I have the index and the column name of the data frame. How would it work then? Thanks a lot! – Nadja Eb Mar 21 '17 at 16:40
  • If you have a follow-up question which is different, then open a new question with your new input, desired output, what you tried, and why it didn't work. StackOverflow works in a question/answer format, not threads. – DSM Mar 21 '17 at 17:06

3 Answers3

3

I think simple pandas.DataFrame.apply with anonimous function should work fine:

df.apply(lambda x: dict_of_dicts[x.col1][x.col2], axis=1)
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
1

Are you sure your desired output is correct? x1 - y0 is a 0 in your table. If so, this will work and make use only of internal functions of pandas, in hope that they are well optimized:

df2 = pd.DataFrame(dict_of_dicts)
df2 = df2.unstack().reset_index()
df.merge(df2, left_on=['col1', 'col2'], right_on=['level_0', 'level_1'], how='left')

Which will result in:

  col1 col2 level_0 level_1  0
0   x1   y0      x1      y0  0
1   x2   y1      x2      y1  1
2   x2   y0      x2      y0  2
languitar
  • 6,554
  • 2
  • 37
  • 62
1

It should be possible to use list comprehension in the following manner:

df['col3'] = [dict_of_dicts[x][y] for x, y in zip(df['col1'], df['col2'])]
print(df)

  col1 col2  col3
0   x1   y0     0
1   x2   y1     1
2   x2   y0     2
Vince W.
  • 3,561
  • 3
  • 31
  • 59