convert pandas multiindex series to Json python

Question

Hi I have two pandas series similar to below

PnL

           Product Name      Price
Company A  Orange            3000
Company B  Apple             2000
           Grapes            1000

Tax

           Product Name      Price
Company A  Orange            100
Company B  Apple             100
           Grapes            10

I would like to transform the pandas series into the following JSON format

{'PnL':{'Company A':{'productName':'Orange','price':3000},
        'Company B':[{'productName':'Apple','price':2000},
                     {'productName':'Grapes','price':1000}]
       },
 'Tax':{'Company A':{'productName':'Orange','price':100},
        'Company B':[{'productName':'Apple','price':100},
                     {'productName':'Grapes','price':10}]
       }
}

I have tried to use the code below

convertedJson = json.dumps([{'company': k[0], 'productName':k[1],'price': v} for k,v in df.items()])

but I cannot form the JSON which I want to produce. Thank you for your help

Use the to_json function - pandas.pydata.org/pandas-docs/stable/generated/… — Nordle
– Nordle, Commented Oct 22, 2018 at 7:15
Thanks for your advice, but I have tried with to_json(orient="index") but it cannot produce what I want — Platalea Minor
– Platalea Minor, Commented Oct 22, 2018 at 7:23

tgrandje · Accepted Answer · 2024-12-15 11:24:21Z

13

You can use concat for join DataFrames together and then groupby with to_dict for expected output:

df = pd.concat([s1, s2], keys=('PnL','Tax')).reset_index()
df.columns = ['type','company','productName','price']
print (df)
  type    company productName  price
0  PnL  Company A      Orange   3000
1  PnL  Company B       Apple   2000
2  PnL  Company B      Grapes   1000
3  Tax  Company A      Orange   3000
4  Tax  Company B       Apple   2000
5  Tax  Company B      Grapes   1000

d = (df.groupby(['type','company'])['productName','price']
       .apply(lambda x: x.to_dict('r'))
       .reset_index(name='data')
       .groupby('type')['company','data']
       .apply(lambda x: x.set_index('company')['data'].to_dict())
       .to_json()
       )

print (d)

{
    "PnL": {
        "Company A": [{
            "productName": "Orange",
            "price": 3000
        }],
        "Company B": [{
            "productName": "Apple",
            "price": 2000
        }, {
            "productName": "Grapes",
            "price": 1000
        }]
    },
    "Tax": {
        "Company A": [{
            "productName": "Orange",
            "price": 3000
        }],
        "Company B": [{
            "productName": "Apple",
            "price": 2000
        }, {
            "productName": "Grapes",
            "price": 1000
        }]
    }
}

edited Dec 15, 2024 at 11:24

tgrandje

2,5623 gold badges20 silver badges39 bronze badges

answered Oct 22, 2018 at 11:07

jezrael

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

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

1 Comment

Astrian_72954 Over a year ago

Can you explain the line reset_index(name='data') @jezrael

Collectives™ on Stack Overflow

convert pandas multiindex series to Json python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related