python - 如何将 pandas 数据透视表转换为 JSON

Question

这个问题已经有答案了:

Pandas dataframe to json without index (4 个回答)

已关闭 5 年前。

我使用 pandas 数据透视表创建了如下所示的表格。

print(pd_pivot_table)

category_id  name
3            name3        0.329204
24           name24       0.323727
31           name31       0.319526
19           name19       0.008992
23           name23       0.005897

我想基于这个pivot_table创建JSON，但我不知道如何。

[
  {
    "category_id": 3,
    "name": "name3",
    "score": 0.329204
  },
  {
    "category_id": 24,
    "name": "name24",
    "score": 0.323727
  },
  {
    "category_id": 31,
    "name": "name31",
    "score": 0.319526
  },
  {
    "category_id": 19,
    "name": "name19",
    "score": 0.008992
  },
  {
    "category_id": 23,
    "name": "name23",
    "score": 0.005897
  }
]

或者，我一开始就不知道如何获取category_id和name值。即使你编写下面的代码也无法得到你想要的结果。

for data in pd_pivot_table:
  print(data) # 0.329204
  print(data["category_id"]) # *** IndexError: invalid index to scalar variable.

Answer 1

您可以使用Series.reset_index首先是 DataFrame，然后是 DataFrame.to_json :

print (df)
category_id  name  
3            name3     0.329204
24           name24    0.323727
31           name31    0.319526
19           name19    0.008992
23           name23    0.005897
Name: score, dtype: float64

print (type(df))
<class 'pandas.core.series.Series'>

json = df.reset_index().to_json(orient='records')
print (json)
[{"category_id":3,"name":"name3","score":0.329204},
 {"category_id":24,"name":"name24","score":0.323727},
 {"category_id":31,"name":"name31","score":0.319526},
 {"category_id":19,"name":"name19","score":0.008992},
 {"category_id":23,"name":"name23","score":0.005897}]

如果需要输出到文件:

df.reset_index().to_json('file.json',orient='records')

详细信息:

print (df.reset_index())
   category_id    name     score
0            3   name3  0.329204
1           24  name24  0.323727
2           31  name31  0.319526
3           19  name19  0.008992
4           23  name23  0.005897

print (type(df.reset_index()))
<class 'pandas.core.frame.DataFrame'>