How to create nested Json data in pandas?

user_5304058 0
Viewed 41

I have a CSV file which I convert into JSON. However, in JSON, I need to format a specific column with curly brackets.

The field time has value "DAY=20220524", this has to be converted into {"DAY":20170801}

json data:
{"ID":200,"Type":"ABC","time":"DAY=20220524"}
{"ID":400,"Type":"ABC","time":"NOON=20220524"}

expected output:
{"ID":200,"Type":"ABC","time": {"DAY":20170801}}
{"ID":400,"Type":"ABC","time": {"DAY":20170801}}

I am not sure how do I do this. Can anyone please help me with this?

pandas python
1 Answers

With the following file.json:

[
    {
        "ID": 200,
        "Type": "ABC",
        "time": "DAY=20220524"
    },
    {
        "ID": 400,
        "Type": "ABC",
        "time": "NOON=20220524"
    }
]

Here is one way to do it:

import pandas as pd

pd.read_json("file.json").assign(
    time=lambda df_: df_["time"].apply(lambda x: f"{{{x}}}")
).to_json("new_file.json", orient="records")

In new_file.json:

[
    {
        "ID": 200,
        "Type": "ABC",
        "time": "{DAY=20220524}"
    },
    {
        "ID": 400,
        "Type": "ABC",
        "time": "{NOON=20220524}"
    }
]
user_112460560
Related