如何将熊猫系列转换为所需的JSON格式？

Question

我有以下数据，我需要做的是应用聚合函数，然后是groupby。

我的数据如下：data.csv

id,category,sub_category,count 
0,x,sub1,10 
1,x,sub2,20 
2,x,sub2,10 
3,y,sub3,30 
4,y,sub3,5 
5,y,sub4,15 
6,z,sub5,20 

在这里，我试图让通过子类的计数明智的。之后，我需要以JSON格式存储结果。以下代码可以帮助我实现这一目标。 test.py

import pandas as pd 
df = pd.read_csv('data.csv') 
sub_category_total = df['count'].groupby([df['category'], df['sub_category']]).sum() 
print sub_category_total.reset_index().to_json(orient = "records") 

上面的代码给了我下面的格式。

[{"category":"x","sub_category":"sub1","count":10},{"category":"x","sub_category":"sub2","count":30},{"category":"y","sub_category":"sub3","count":35},{"category":"y","sub_category":"sub4","count":15},{"category":"z","sub_category":"sub5","count":20}] 

但是，我想要的格式如下：

{ 
"x":[{ 
    "sub_category":"sub1", 
    "count":10 
    }, 
    { 
    "sub_category":"sub2", 
     "count":30}], 
"y":[{ 
    "sub_category":"sub3", 
    "count":35 
    }, 
    { 
    "sub_category":"sub4", 
    "count":15}], 
"z":[{ 
    "sub_category":"sub5", 
     "count":20}] 
} 

通过下面的讨论@How to convert pandas DataFrame result to user defined json format，我更换了最后两行的test.py用，

g = df.groupby('category')[["sub_category","count"]].apply(lambda x: x.to_dict(orient='records')) 
print g.to_json() 

它给我以下输出。

{"x":[{"count":10,"sub_category":"sub1"},{"count":20,"sub_category":"sub2"},{"count":10,"sub_category":"sub2"}],"y":[{"count":30,"sub_category":"sub3"},{"count":5,"sub_category":"sub3"},{"count":15,"sub_category":"sub4"}],"z":[{"count":20,"sub_category":"sub5"}]} 

虽然上述结果有点类似我想要的格式，我不能在这里，因为它会引发错误说执行任何聚合功能。因此，我最终得到了数据文件中的所有行。

有人能帮我实现上面的JSON格式吗？

Answer 1

我认为你可以sum第一骨料，参数as_index=False加入groupby，所以输出Dataframedf1然后用other solution：

df1 = (df.groupby(['category','sub_category'], as_index=False)['count'].sum()) 
print (df1) 
    category sub_category count 
0  x   sub1  10 
1  x   sub2  30 
2  y   sub3  35 
3  y   sub4  15 
4  z   sub5  20 

g = df1.groupby('category')[["sub_category","count"]] 
     .apply(lambda x: x.to_dict(orient='records')) 

print (g.to_json()) 

{ 
    "x": [{ 
     "sub_category": "sub1", 
     "count": 10 
    }, { 
     "sub_category": "sub2", 
     "count": 30 
    }], 
    "y": [{ 
     "sub_category": "sub3", 
     "count": 35 
    }, { 
     "sub_category": "sub4", 
     "count": 15 
    }], 
    "z": [{ 
     "sub_category": "sub5", 
     "count": 20 
    }] 
}