Q:

pandas groupby to nested json -- dont want calculated fields

I'm working on a d3.js graphic.  My data is in a huge multitab .xls. I have to grab data from each tab, so I decided to dump it all in pandas and export some .json.
original data, spread across many tabs:
demography, area, state, month, rate
over 65,   region2, GA, May, 23
over 65,  region2, AL, May, 25
NaN,  random_odd_data, mistake, error
18-65, region2, GA, 77
18-65, region2, AL, 75

now, put in pandas, merged and cleaned up:
     demography area     state  month rate
0    over 65    region2  GA     May   23
1    over 65    region2  AL     May   25
2    18-65      region2  GA     May   50
3    18-65      region2  AL     May   55

now, group it
group = df.groupby(['state', 'demography'])

yields 
<pandas.core.groupby.DataFrameGroupBy object at 0x106939610>

trying this:
group = df.groupby(['state', 'demography']).count()

yields something almost correct, except I don't want to count anything, I just want "rate"
state    demography  area   month  rate
AL       over 65     1      1      1
         18-65       1      1      1
GA       over 65     1      1      1
         18-65       1      1      1

sure enough, this only exports "1" for every value, lol:
group.reset_index().to_json("myjson2.json", orient="index")

dang I am almost there, how do I export it so each state is a parent? 
[
    {
        "state": "Alabama",
        "over 65": 25,
        "18-65": 50

    },
    {
        "state": "Georgia",
        "over 65": 23,
        "18-65": 55
    }
]

A:

The count method counts the number of non-NaN entries for each group in each column, hence why they're all 1 here (each group is size 1, there are no-NaNs).
(I can't find a specific link, but it is mentioned in the groupby docs.)

I think what you actually want is a pivot_table:
In [11]: res = df.pivot_table('rate', 'state', 'demography')

In [12]: res
Out[12]:
demography  18-65  over65
state
AL             55      25
GA             50      23

I think you're looking for orient='records' (you need to reset_index first though):
In [13]: res.reset_index().to_json(orient='records')
Out[13]: '[{"state":"AL","18-65":55,"over65":25},{"state":"GA","18-65":50,"over65":23}]'

