Convert column into Json using Dataframe(python)

Question

Python #datas from API

plan_get = pd.DataFrame(rows, columns=columns) #plan_get return all json data
return Response({"MESSAGE": "FOUND","DATA":json.loads(plan_get.to_json(orient='records'))})

Actual Output:

[{
    "customer_name": "ABI2",
    "location_name": "Cherai2",
    "employee_name": "ASU2",
    "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

},
{
    "customer_name": "ABI",
    "location_name": "Cherai",
    "employee_name": "ASU",
    "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

}]

Expected Output:

[{
    "customer_name": "ABI2",
    "location_name": "Cherai2",
    "employee_name": "ASU2",
    "Sales_Plan_Details": [{"Month": "2019-1",
        "Quantity": 10, "Product_Gid": 3}]

},
{
    "customer_name": "ABI",
    "location_name": "Cherai",
    "employee_name": "ASU",
    "Sales_Plan_Details": [{"Month": "2019-1",
        "Quantity": 10, "Product_Gid": 3}]

}]

Here I'm using pandas DataFrame to pass json data. My question is how would I convert Sales_Plan_Details(column) to JSON object before returning.

Answer 1

Use json.loads or ast.literal_eval for convert strings to list of dicts:

import ast, json

df = pd.DataFrame(rows) 
df['Sales_Plan_Details'] = df['Sales_Plan_Details'].apply(json.loads)
#alternative solution
#df['Sales_Plan_Details'] = df['Sales_Plan_Details'].apply(ast.literal_eval)

j = df.to_json(orient='records')
print (j)
[{"Sales_Plan_Details":[{"Month":"2019-1","Quantity":10,"Product_Gid":3}],
  "customer_name":"ABI2","employee_name":"ASU2","location_name":"Cherai2"},
{"Sales_Plan_Details":[{"Month":"2019-1","Quantity":10,"Product_Gid":3}],
 "customer_name":"ABI","employee_name":"ASU","location_name":"Cherai"}]

Setup:

rows= [{
                    "customer_name": "ABI2",
                    "location_name": "Cherai2",
                    "employee_name": "ASU2",
                    "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

    },
{
                "customer_name": "ABI",
                "location_name": "Cherai",
                "employee_name": "ASU",
                "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

}]

Answer 2

You can use list comprehensions to map the Sales_Plan_Details values.

You can use json.loads() to deserialize the list value from the string.

import json

dataframe_json = [
    {
                    "customer_name": "ABI2",
                    "location_name": "Cherai2",
                    "employee_name": "ASU2",
                    "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

    },
    {
                    "customer_name": "ABI",
                    "location_name": "Cherai",
                    "employee_name": "ASU",
                    "Sales_Plan_Details": "[{\"Month\": \"2019-1\", \"Quantity\": 10, \"Product_Gid\": 3}]"

    }]

# get the "Sales_Plan_Details" key value's from the list
sales_plan_details_nested_list = [sales_plan_details_dict for sales_plan_details_dict in json.loads(item("Sales_Plan_Details")) for item in dataframe_json]

# flatten the list
sales_plan_details_list = [item for sublist in sales_plan_details_nested_list for item in sublist]

# pretty print the list now
print(json.dumps(sales_plan_details_list, indent=True))