Convert pandas multiindex dataframe to nested dictionary

Question

I have a pandas multiindex dataframe that I'm trying to output as a nested dictionary.

# create the dataset
data = {'clump_thickness': {(0, 0): 274.0, (0, 1): 19.0, (1, 0): 67.0, (1, 1): 12.0, (2, 0): 83.0, (2, 1): 45.0, (3, 0): 16.0, (3, 1): 40.0, (4, 0): 4.0, (4, 1): 54.0, (5, 0): 0.0, (5, 1): 69.0, (6, 0): 0.0, (6, 1): 0.0, (7, 0): 0.0, (7, 1): 0.0, (8, 0): 0.0, (8, 1): 0.0, (9, 0): 0.0, (9, 1): 0.0}}
df = pd.DataFrame(data)
df.head()
#      clump_thickness
# 0 0            274.0
#   1             19.0
# 1 0             67.0
#   1             12.0
# 2 0             83.0

df is the dataframe that I want to output as a nested dictionary. The output I'm looking for is in the form -

{"0":
{
  "0":274,
  "1":19
},
"1":{
  "0":67,
  "1":12
},
"2":{
  "0":83,
  "1":45
},
"3":{
  "0":16,
  "1":40
},
"4":{
  "0":4,
  "1":54
},
"5":{
  "0":0,
  "1":69
}
}

Here the first index forms the keys of the outer most dictionary. For each key we have a dictionary stored whose keys are the values in the second index.

When I do df.to_dict(), the instead of nesting, the multiindex is returned as a tuple. How do I achieve this?

jezrael · Accepted Answer · 2017-12-21T08:26:19.747

6

For me working:

d = {l: df.xs(l)['clump_thickness'].to_dict() for l in df.index.levels[0]}

Another solution similar like DataFrame with MultiIndex to dict , but is necessary filter column for Series:

d = df.groupby(level=0).apply(lambda df: df.xs(df.name).clump_thickness.to_dict()).to_dict()

print (d)

{0: {0: 274.0, 1: 19.0},
 1: {0: 67.0, 1: 12.0},
 2: {0: 83.0, 1: 45.0},
 3: {0: 16.0, 1: 40.0},
 4: {0: 4.0, 1: 54.0},
 5: {0: 0.0, 1: 69.0},
 6: {0: 0.0, 1: 0.0},
 7: {0: 0.0, 1: 0.0},
 8: {0: 0.0, 1: 0.0},
 9: {0: 0.0, 1: 0.0}}

edited Dec 21 '17 at 08:26

answered Dec 21 '17 at 08:18

jezrael

822,522
95
1,334
1,252

But it is different, this is Series, there is DataFrame :( – jezrael Dec 21 '17 at 08:19
Another solution make feel like its a dupe, better to link the answer – Bharath M Shetty Dec 21 '17 at 08:25
How would you do this for a series rather than a dataframe? – Mingo Nov 15 '19 at 12:31
@Mingo if df is series, only omit `['clump_thickness']` – jezrael Nov 15 '19 at 12:34

score -1 · Answer 2 · edited Mar 15 '18 at 23:25

-1

df.unstack().clump_thickness.apply(lambda x: x.to_dict(), axis=1).to_dict()

edited Mar 15 '18 at 23:25

Philip

4,128
5
31
49

answered Mar 15 '18 at 23:02

Amir

189
2
12

Convert pandas multiindex dataframe to nested dictionary

2 Answers2

Linked