9

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
  • 704,970
  • 168
  • 1,303
  • 1,425
parasu
  • 842
  • 7
  • 12

2 Answers2

16

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
  • 52,577
  • 18
  • 119
  • 125
  • 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
1

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,408
  • 10
  • 40
  • 47
Bernat Esquirol
  • 11
  • 1