Pandas multi index dataframe to nested dictionary

Question

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}...

Ted Petrou · Accepted Answer · 2017-02-10 03:37:19Z

26

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}}}

answered Feb 10, 2017 at 3:37

Ted Petrou

62.4k19 gold badges139 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

parasu Over a year ago

Never knew about the xs method. Works great! thank you!

Nikhil VJ Over a year ago

I tried this on my dataframe that had 3 levels of idexes. Didn't work unfortunately.

Frank Over a year ago

@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]}

anilbey Over a year ago

Why to_dict('index') instead of just to_dict()?

June Skeeter · Accepted Answer · 2025-02-14 19:51:05Z

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]: dict_f[level] = createDictFromPandas(df.xs(level)) return dict_f

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?

Collectives™ on Stack Overflow

Pandas multi index dataframe to nested dictionary

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related