python - pandas groupby to nested json -

python - pandas groupby to nested json -

i utilize pandas groupby generate stacked tables. want output resulting nested relations json. there way extract nested json filed stacked table produces?

let's have df like:

year office candidate amount 2010 mayor joe smith 100.00 2010 mayor jay gould 12.00 2010 govnr pati mara 500.00 2010 govnr jess rapp 50.00 2010 govnr jess rapp 30.00

i can do:

grouped = df.groupby('year', 'office', 'candidate').sum() print grouped amount year office candidate 2010 mayor joe smith 100 jay gould 12 govnr pati mara 500 jess rapp 80

beautiful! of course, i'd real nested json via command along lines of grouped.to_json. feature isn't available. workarounds?

so, want like:

{"2010": {"mayor": [ {"joe smith": 100}, {"jay gould": 12} ] }, {"govnr": [ {"pati mara":500}, {"jess rapp": 80} ] } }

don

i don't think think there built-in pandas create nested dictionary of data. below code should work in general series multiindex, using defaultdict

the nesting code iterates through each level of multindex, adding layers dictionary until deepest layer assigned series value.

in [99]: collections import defaultdict in [100]: results = defaultdict(lambda: defaultdict(dict)) in [101]: index, value in grouped.itertuples(): ...: i, key in enumerate(index): ...: if == 0: ...: nested = results[key] ...: elif == len(index) - 1: ...: nested[key] = value ...: else: ...: nested = nested[key] in [102]: results out[102]: defaultdict(<function <lambda> @ 0x7ff17c76d1b8>, {2010: defaultdict(<type 'dict'>, {'govnr': {'pati mara': 500.0, 'jess rapp': 80.0}, 'mayor': {'joe smith': 100.0, 'jay gould': 12.0}})}) in [106]: print json.dumps(results, indent=4) { "2010": { "govnr": { "pati mara": 500.0, "jess rapp": 80.0 }, "mayor": { "joe smith": 100.0, "jay gould": 12.0 } } }

python json pandas