将Pandas数据框转换为JSON对象数组。

Question

我正在使用Flask和Angular构建机器学习应用程序。我在Python后端检索了一个mysql表，并希望使用pandas dataframe进行一些过滤方法。通过将Json数组转换为pandas dataframe，我很容易地完成了这部分工作。但是当我尝试将其转换为json对象数组来返回dataframe时，它看起来像这样。

from flask import request
from flask_restful import Resource
from Model import db, EmployeeTaskSummary, EmployeeTaskSummarySchema
import json

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np

employee_task_summary_schemas = EmployeeTaskSummarySchema(many=True)
employee_task_summary_schema = EmployeeTaskSummarySchema()

class EmployeeTaskSummaryResource(Resource):
    def get(self):
       employee_task_summary = EmployeeTaskSummary.query.all()
       employee_task_summary = 
       employee_task_summary_schemas.dump(employee_task_summary).data
       df=pd.DataFrame(employee_task_summary)
       return df.head(5).to_json(orient='records')

那么输出的 JSON 数组将会是：

"[{\"closed_issues\":0,\"created_issues\":0,\"designation\":\"STL\",\"gitlab_additions\":0,\"gitlab_deletions\":0,\"inprogress_issues\":0,\"month\":1,\"name\":\"Madawa Jeev.\",\"onhold_issues\":0,\"open_issues\":0,\"project_name\":\"DSN\",\"re_opened_issues\":0,\"resolved_issues\":0,\"timelogs\":0,\"week\":1,\"year\":2019},{\"closed_issues\":0,\"created_issues\":0,\"designation\":\"STL\",\"gitlab_additions\":0,\"gitlab_deletions\":0,\"inprogress_issues\":0,\"month\":1,\"name\":\"Madawa Jeev.\",\"onhold_issues\":0,\"open_issues\":0,\"project_name\":\"DSN\",\"re_opened_issues\":0,\"resolved_issues\":0,\"timelogs\":0,\"week\":2,\"year\":2019},{\"closed_issues\":0,\"created_issues\":0,\"designation\":\"STL\",\"gitlab_additions\":0,\"gitlab_deletions\":0,\"inprogress_issues\":0,\"month\":1,\"name\":\"Madawa Jeev.\",\"onhold_issues\":0,\"open_issues\":7,\"project_name\":\"DSN\",\"re_opened_issues\":0,\"resolved_issues\":0,\"timelogs\":0,\"week\":3,\"year\":2019},{\"closed_issues\":0,\"created_issues\":0,\"designation\":\"STL\",\"gitlab_additions\":0,\"gitlab_deletions\":0,\"inprogress_issues\":0,\"month\":1,\"name\":\"Madawa Jeev.\",\"onhold_issues\":0,\"open_issues\":0,\"project_name\":\"DSN\",\"re_opened_issues\":0,\"resolved_issues\":0,\"timelogs\":0,\"week\":4,\"year\":2019},{\"closed_issues\":0,\"created_issues\":0,\"designation\":\"STL\",\"gitlab_additions\":0,\"gitlab_deletions\":0,\"inprogress_issues\":0,\"month\":1,\"name\":\"Madawa Jeev.\",\"onhold_issues\":0,\"open_issues\":18,\"project_name\":\"DSN\",\"re_opened_issues\":0,\"resolved_issues\":0,\"timelogs\":0,\"week\":5,\"year\":2019}]"

我不理解输出中出现的“\”符号。我需要这种类型的输出。（这是一个随机示例）

[
{
    "id": 1,
    "designation": "SE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 12,
    "gitlab_deletions": 3,
    "name": "abc",
    "timelog": 1234,
    "month": 1
},
{
    "id": 2,
    "designation": "SSE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 21,
    "gitlab_deletions": 2,
    "name": "asd",
    "timelog": 2342,
    "month": 1
},

如果您能帮助我就太感谢了。谢谢。

- anas

---

@tawab_shakeel的回答很好，可以给你期望的格式。回答你为什么会有那些反斜杠，是因为pandas中的to_json函数创建了一个字符串格式的Json。因为它使用双引号作为整个Json的定界符，所以必须添加反斜杠来转义键和字符串值中的双引号。只需打印该字符串，您就会看到它的作用 :)。 - SmileyProd

@SmileyProd明白了 :) - anas

Answer 1

使用json.loads将字符串转换为JSON。

import json
res = json.loads(df.to_json(orient='records'))
print(res)

[
{
    "id": 1,
    "designation": "SE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 12,
    "gitlab_deletions": 3,
    "name": "abc",
    "timelog": 1234,
    "month": 1
},
{
    "id": 2,
    "designation": "SSE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 21,
    "gitlab_deletions": 2,
    "name": "asd",
    "timelog": 2342,
    "month": 1
}]

第二种解决方案

只需使用to_dict()，并将orient ='records'参数传入即可。

res = df.to_dict(orient='records')
print(res)

[
{
    "id": 1,
    "designation": "SE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 12,
    "gitlab_deletions": 3,
    "name": "abc",
    "timelog": 1234,
    "month": 1
},
{
    "id": 2,
    "designation": "SSE",
    "year": 2019,
    "week": 1,
    "gitlab_additions": 21,
    "gitlab_deletions": 2,
    "name": "asd",
    "timelog": 2342,
    "month": 1
}]

- tawab_shakeel

---

@AhkamNaseek 很棒 :) - tawab_shakeel