convert pandas dataframe to Json objects array

Question

I am building a machine learning app with Flask and Angular. I am retrieving a mysql table in my pyhton backend and i want to do sone filtering method using pandas dataframe. I did that part easily by converint Json Array to pandas dataframe. But when I try to return the dataframe by converting it to a json object array it will look like this.

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')

then the oputput json Arry wiil be:-

"[{\"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}]"

I don't understand this "\" symbol comes in the output. I need this kindof output. (this is a random sample)

[
{
    "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
},

Help me if you can. Thanks

Answer 1

use json.loads to convert string into 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
}]

Second Solution

Just use to_dict() with 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
}]