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.

what is your desired output? please include that in your question. — Nihal
– Nihal, Commented Dec 19, 2018 at 6:12
is the json output you're getting from the statement json.loads(plan_get.to_json(orient='records')) — Kunal Mukherjee
– Kunal Mukherjee, Commented Dec 19, 2018 at 6:19

jezrael · Accepted Answer · 2018-12-19 07:04:31Z

12

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}]"

}]

answered Dec 19, 2018 at 7:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

selvakumar Over a year ago

Brilliant Thanks @jezrael

Suganesh Kumar Over a year ago

ast.literal_eval method working fine for me... Thanks @jezrael

Kunal Mukherjee · Accepted Answer · 2018-12-19 06:37:25Z

1

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

edited Dec 19, 2018 at 6:37

answered Dec 19, 2018 at 6:28

Kunal Mukherjee

5,8733 gold badges29 silver badges55 bronze badges

2 Comments

Kunal Mukherjee Over a year ago

@selvakumar can you check now?

selvakumar Over a year ago

Thanks For Your response.. Your code really helped me @kunal

Collectives™ on Stack Overflow

Convert column into Json using Dataframe(python)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related