Convert pandas Series to specific JSON structure

user_4418481 0
Viewed 69

I have an excel sheet that looks as follows:

enter image description here

I would like to create from this table a JSON that looks like this:

{
    "lion": [{
        "a": 0,
        "b": 1,
        "c": 23,
        "d": 55
        }, {
        "a": 1,
        "b": 23,
        "c": 32,
        "d": 24
    }],
    "king": [{
        "a": 55,
        "b": 66,
        "c": 77,
        "d": 88
    }]
}

In order to do so, I tried the following:

import pandas;
import json;

excel_data = pandas.read_excel('my_excel.xlsx', sheet_name = 'sheet1')

for index, row in excel_data.iterrows():
    row.dropna(inplace = True) # for cases when Nan
    d = row.to_dict()
    j = json.dumps(d)

however, the result i'm getting is a JSON without the required structure:

{"name": 'lion',"a": 0,"b": 1,"c": 23,"d": 55,"a": 1,"b": 23,"c": 32,"d": 24}

how can I split it so it would look like the JSON I need?

Thank you

pandas python json
1 Answers

Without dataframe constructor, it's difficult to help you much.

Try:

from collections import defaultdict
import json

out = df.melt('name', var_name='key', value_name='val') \
        .assign(row=lambda x: x.groupby(['name', 'key']).cumcount()) \
        .query('val.notna()')

data = defaultdict(list)
for (name, _), subdf in out.groupby(['name', 'row'], sort=False):
    data[name].append(subdf.set_index('key')['val'].to_dict())

data = json.dumps(data, indent=2)

Output:

>>> data
{
  "lion": [
    {
      "a": 0.0,
      "b": 1.0,
      "c": 23.0,
      "d": 55.0
    },
    {
      "a": 1.0,
      "b": 23.0,
      "c": 32.0,
      "d": 24.0
    }
  ],
  "king": [
    {
      "a": 55.0,
      "b": 66.0,
      "c": 77.0,
      "d": 88.0
    }
  ]
}

Setup:

import pandas as pd
import numpy as np

d = {'index': [0, 1],
     'columns': ['name', 'a', 'b', 'c', 'd', 'a', 'b', 'c', 'd'],
     'data': [['lion', 0, 1, 23, 55, 1, 23, 32, 24],
              ['king', 55, 66, 77, 88, np.nan, np.nan, np.nan, np.nan]]}
df = pd.DataFrame(**d)
user_152399510
Related