How can I create a nested JSON file from a Pandas dataframe in Python?

Question

I have dataframe like below:

 df.head(2)

Ord MOT  MVT  CUST  CreationSla  CreationPlanned CreationProposed  PickupSla  PickupPlanned PickupProposed
 12  TR    TT   DEA  12-3-2020      12-3-2020      12-3-2020       14-3-2020   14-3-2020    14-3-2020
 15  ZR    TD   DET  15-3-2020      15-3-2020      15-3-2020       16-3-2020   16-3-2020    16-3-2020

I want to create a nested JSON file in the below format:

Expected output

{
    "Ord" : "12",
    "MOT" : "TR",
    "MVT" : "TT",
    "CUST" : "DEA",
    "milestone" : {
        "creation" : {
            "sla" : "12-3-2020",
            "plan" : "12-3-2020",
            "proposed" : "12-3-2020"
        },
        "Pickup" : {
            "sla" : "14-3-2020",
            "plan" : "14-3-2020",
            "proposed" : "14-3-2020"
        }
    }
}

How can this be done in Python?

Peter Mortensen · Accepted Answer · 2020-04-01 16:42:14Z

4

You can create a JSON template and send data to it:

d = """{
    "Ord" : "%s",
    "MOT" : "%s",
    "MVT" : "%s",
    "CUST" : "%s",
    "milestone" : {
        "creation" : {
            "sla" : "%s",
            "plan" : "%s",
            "proposed" : "%s"
        },
        "Pickup" : {
            "sla" : "%s",
            "plan" : "%s",
            "proposed" : "%s"
        }
    }
}
"""
js = []

for item in df.values:
    js.append(json.loads(d%tuple(item.tolist())))

print(json.dumps(js))

Output:

[{"Ord": "a", "MOT": "TR", "MVT": "TT", "CUST": "DEA", "milestone": {"creation": {"sla": "12-3-2020", "plan": "12-3-2020", "proposed": "12-3-2020"}, "Pickup": {"sla": "14-3-2020", "plan": "14-3-2020", "proposed": "14-3-2020"}}}, {"Ord": "b", "MOT": "ZR", "MVT": "TD", "CUST": "DET", "milestone": {"creation": {"sla": "15-3-2020", "plan": "15-3-2020", "proposed": "15-3-2020"}, "Pickup": {"sla": "16-3-2020", "plan": "16-3-2020", "proposed": "16-3-2020"}}}]

edited Apr 1, 2020 at 16:42

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Apr 1, 2020 at 2:43

RaminNietzsche

2,8111 gold badge23 silver badges37 bronze badges

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

2 Comments

RaminNietzsche Over a year ago

@Rahulrajan You can imort json, I changed my answer

Maarten Fabré Over a year ago

a template with keyword arguments link and passing a dict to it will be more robust than this

Peter Mortensen · Accepted Answer · 2020-04-01 16:44:03Z

Since you mentioned Pandas, I use wide_to_long, and then groupby to create your format. Notice this requires you to change the level when your data format changes.

s=pd.wide_to_long(df,stubnames=['Creation','Pickup'],i=['Ord', 'MOT', 'MVT', 'CUST'],j='type' , suffix='\w+').stack().unstack(level=-2)
js=[{**dict(zip(s.index.names[:-1],x)),**{'milestone' : y.reset_index(level=[0,1,2,3],drop=True).to_dict('i') }} for x , y in s.groupby(level=[0,1,2,3])]
js
[{'Ord': 12, 'MOT': 'TR', 'MVT': 'TT', 'CUST': 'DEA',
 'milestone':
  {'Creation':
  {'Planned': '12-3-2020', 'Proposed': '12-3-2020', 'Sla': '12-3-2020'}, 'Pickup': {'Planned': '14-3-2020', 'Proposed': '14-3-2020', 'Sla': '14-3-2020'}}},
 {'Ord': 15, 'MOT': 'ZR', 'MVT': 'TD', 'CUST': 'DET',
  'milestone':
  {'Creation':
  {'Planned': '15-3-2020', 'Proposed': '15-3-2020', 'Sla': '15-3-2020'}, 'Pickup': {'Planned': '16-3-2020', 'Proposed': '16-3-2020', 'Sla': '16-3-2020'}}}]

Peter Mortensen · Accepted Answer · 2020-08-23 23:14:05Z

1

Firstly, you will need to loop over rows of your dataframe.

Then with each row, you create a dictionary. The full idea is like this:

result = []
predefined_columns = ['Creation', 'Pickup', 'Departure']
mapping_cl = []
for column in df.columns:
    flag = False
    for sub_str in predefined_columns:
        if sub_str in column:
            flag = True
            mapping_cl.append(sub_str)
            break
    if not flag:
        mapping_cl.append(False)
for index, row in df.iterrows():
    item = {}
    for cl in mapping_cl:
        if cl:
            item[cl] = {}
    for i, column in enumerate(df.columns):
        if mapping_cl[i]:
            cl_name = column.split(mapping_cl[i])[-1]
            item[mapping_cl[i]][cl_name] = row[column]
        else:
            item[column] = row[column]
    result.append(item)

Now result is a list of dict as you want:

edited Aug 23, 2020 at 23:14

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Apr 1, 2020 at 2:25

Binh

1,1736 silver badges8 bronze badges

5 Comments

Rahul rajan Over a year ago

what if I have subcolumn Deperature with its timestamps mentioned .

Binh Over a year ago

So the point is you need to predefine all the columns name that you want to split it up. Then you will check the column if it appears in that list or not, if it does, you will split it up then. I will change my code to suitable to it

Rahul rajan Over a year ago

That would be nice

Binh Over a year ago

Hope that could help

Binh Over a year ago

I just edited, hope this one will solve your problem with as predefined columns as possible

Collectives™ on Stack Overflow

How can I create a nested JSON file from a Pandas dataframe in Python?

Expected output

3 Answers 3

Output:

2 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Expected output

3 Answers 3

Output:

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related