How to convert a Python dataframe object to json?

Question

I have a dataframe like this:

   date  ...   ID     value_1  value_2  value_3
0  03/03/2018    ...  12345    111       1      30
1  03/03/2018    ...  89547    222       2      50
2  02/03/2018    ...  12345    333       5      80

I want to convert it to JSON format like this:


{
    "XYZ": [
        {
            "Id": 123456,
            "date": "2021-03-03 09:00:00", # this field need to change datetime format
            "value_3": 30,
            "value_2": 1,
            "ABC": [
                {
                    "value_1": 111,
                    "type": "int" # 'type' field will always be 'int'
                }
            ]
        },
        {
            "Id": 123456,
            "date": "2021-03-02 09:00:00", # this field need to change datetime format
            "value_3": 80,
            "value_2": 5,
            "ABC": [
                {
                    "value_1": 333,
                    "type": "int" # 'type' field will always be 'int'
                }
            ]
        },
        {
            "Id": 89547,
            "date": "2021-03-03 09:00:00", # this field need to change datetime format
            "value_3": 50,
            "value_2": 2,
            "ABC": [
                {
                    "value_1": 222,
                    "type": "int" # 'type' field will always be 'int'
                }
            ]
        }
    ]
}

I'm not very familiar with data manipulation in Python, is there a simple way to do the conversion (built-in function or any libraries?)? Many thanks.

jezrael · Accepted Answer · 2021-03-23 13:04:59Z

2

Use:

import json

#convert values to datetimes
df['documentdate'] = pd.to_datetime(df['documentdate'], dayfirst=True, errors='coerce')

#change format of ABC
df['ABC'] = df['value_1'].apply(lambda x: [ {"value_1": x, "type": "int" }])             
#remove columns
df = df.drop('value_1', axis=1)
#convert to dict and then to json
j = json.dumps({"XYZ":df.to_dict(orient='records')}, default=str)

print (j)

EDIT:

For convert datetimes is possible also use:

#https://stackoverflow.com/a/11875813/2901002
from bson import json_util
import json

json.dumps({"XYZ":df.to_dict(orient='records')}, default=json_util.default)

edited Mar 23, 2021 at 13:04

answered Mar 23, 2021 at 12:24

jezrael

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

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

2 Comments

wawawa Over a year ago

Thanks, just a follow-up question if that's ok, what if I want to display value_3 as VALUE_3 in json, what's the best way to do it? Simply change the column name?

wawawa Over a year ago

Hi @jezrael thanks for the answer, I realised that the format I needed is a little bit different, I raised a new question here stackoverflow.com/questions/66788170/…, could you also take a look please? Many thanks.

Bradypodion · Accepted Answer · 2021-03-23 12:24:05Z

0

If you're using Pandas lib to handle DataFrames it should look something like this:

import pandas as pd

df = pd.DataFrame([["a", "b"], ["c", "d"]],
                 index=["row 1", "row 2"],
                 columns=["col 1", "col 2"],)

result = df.to_json(orient="split")

To turn your json into python object:

import json

parsed = json.loads(result)

answered Mar 23, 2021 at 12:24

Bradypodion

817 bronze badges

Collectives™ on Stack Overflow

How to convert a Python dataframe object to json?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related