11

Let's say I have the following dataframe

df = pd.DataFrame({0: {('A', 'a'): 1, ('A', 'b'): 6, ('B', 'a'): 2, ('B', 'b'): 7},
 1: {('A', 'a'): 2, ('A', 'b'): 7, ('B', 'a'): 3, ('B', 'b'): 8},
 2: {('A', 'a'): 3, ('A', 'b'): 8, ('B', 'a'): 4, ('B', 'b'): 9},
 3: {('A', 'a'): 4, ('A', 'b'): 9, ('B', 'a'): 5, ('B', 'b'): 1},
 4: {('A', 'a'): 5, ('A', 'b'): 1, ('B', 'a'): 6, ('B', 'b'): 2}})

which looks this:

     0  1  2  3  4
A a  1  2  3  4  5
  b  6  7  8  9  1
B a  2  3  4  5  6
  b  7  8  9  1  2

When I convert this to a dictionary via to_dict (regardless of stacking, unstacking), I get a dictionary whose keys are tuples:

df.transpose().to_dict()

{('A', 'a'): {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},
 ('A', 'b'): {0: 6, 1: 7, 2: 8, 3: 9, 4: 1},
 ('B', 'a'): {0: 2, 1: 3, 2: 4, 3: 5, 4: 6},
 ('B', 'b'): {0: 7, 1: 8, 2: 9, 3: 1, 4: 2}}

What I'd like instead is a nested dict like this:

{'A':{'a': {0: 1, 1:2, 2:3, 3:4, 4:5}, 'b':{0:6, 1:7, 2:8, 3:9,4:1}...
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
parasu
  • 933
  • 7
  • 13

2 Answers2

21

You can use a dictionary comprehension to iterate through the outer levels (values 'A' and 'B') and use the xs method to slice the frame by those levels.

{level: df.xs(level).to_dict('index') for level in df.index.levels[0]}

{'A': {'a': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},
  'b': {0: 6, 1: 7, 2: 8, 3: 9, 4: 1}},
 'B': {'a': {0: 2, 1: 3, 2: 4, 3: 5, 4: 6},
  'b': {0: 7, 1: 8, 2: 9, 3: 1, 4: 2}}}
Ted Petrou
  • 59,042
  • 19
  • 131
  • 136
  • Never knew about the xs method. Works great! thank you! – parasu Feb 10 '17 at 03:43
  • I tried this on my dataframe that had 3 levels of idexes. Didn't work unfortunately. – Nikhil VJ Feb 29 '20 at 02:21
  • 1
    @NikhilVJ You can expand this approach to any number of levels. You just have to nest another dictionary comprehension around the previous one for each additional level. Here is an example for 2 levels: `{level0: {level1: df.xs([level0, level1]).to_dict('index') for level1 in df.index.levels[1]} for level0 in df.index.levels[0]}` – Frank Apr 18 '20 at 07:49
  • Why `to_dict('index')` instead of just `to_dict()`? – anilbey May 31 '21 at 14:58
3

For n levels you could have something recursive like this:

def createDictFromPandas(df):
    if (df.index.nlevels==1):
        return df.to_dict()
    dict_f = {}
    for level in df.index.levels[0]:
        if (level in df.index):
            dict_f[level] = createDictFromPandas(df.xs([level]))
    return dict_f
ouflak
  • 2,458
  • 10
  • 44
  • 49
Bernat Esquirol
  • 31
  • 1
  • Why is `if (level in df.index):` necessary? Surely if you're looping through `df.index.levels[0]` then that if statement will always be true? – ogb119 Nov 19 '22 at 10:07