Define specific json export format of pandas dataframe

Question

I need to export my DF into a specific JSON format, but I'm struggling to format it in the right way.

I'd like to create a subsection with shop_details that show the city and location for the shop if it's known, otherwise it should be left empty.

Code for my DF:

from pandas import DataFrame

Data = {'item_type': ['Iphone','Computer','Computer'],
        'purch_price': [1200,700,700],
        'sale_price': [1150,'NaN','NaN'],
        'city': ['NaN','Los Angeles','San Jose'],
        'location': ['NaN','1st street', '2nd street']
        }

DF looks like this:

  item_type  purch_price sale_price         city    location
0    Iphone         1200       1150          NaN         NaN
1  Computer          700        NaN  Los Angeles  1st street
2  Computer          700        NaN     San Jose  2nd street

The output format should look like below:

[{
    "item_type": "Iphone",
    "purch_price": "1200",
    "sale_price": "1150",
    "shop_details": []
  },
  {
    "item_type": "Computer",
    "purch_price": "700",
    "sale_price": "600",
    "shop_details": [{
        "city": "Los Angeles",
        "location": "1st street"
      },
      {
        "city": "San Jose",
        "location": "2nd street"
      }
    ]
  }
]

Hi @harvpan! I have yet to find something that comes close. I've searched through SO and google, and messed around with the following two sources (that seem to come closest to what I need) to no avail unfortunately: [link](https://stackoverflow.com/questions/35698067/userdefined-json-format-from-pandas-dataframe/) and [link](https://datatofish.com/export-pandas-dataframe-json/) I feel I should groupby various columns, but can't figure out what exactly.. — Deus14, Sep 13 '19 at 19:36

DarkDrassher34 · Accepted Answer · 2019-09-13T20:25:19.713

import json

df = df.fillna('')

def shop_details(row):
    if row['city'] != '' and row['location'] !='':
        return [{'city': row['city'], 'location': row['location']}]
    else:
        return []

df['shop_details'] = df.apply(lambda row: shop_details(row), axis = 1)

df = df.drop(['city', 'location'], axis = 1)

json.dumps(df.to_dict('records'))

Only problem is we do not group by item_type, but you should do some of the work ;)

score 0 · Answer 2 · answered Sep 13 '19 at 19:31

You can do like below to achieve your output. Thanks

from pandas import DataFrame

Data = {'item_type': ['Iphone','Computer','Computer'],
        'purch_price': [1200,700,700],
        'sale_price': [1150,'NaN','NaN'],
        'city': ['NaN','Los Angeles','San Jose'],
        'location': ['NaN','1st street', '2nd street']
        }

df = DataFrame(Data, columns= ['item_type', 'purch_price', 'sale_price', 'city','location' ])

Export = df.to_json ('path where you want to export your json file')

Thanks for your answer, unfortunately this does not give me the output I wanted. — Deus14, Sep 13 '19 at 19:44

Define specific json export format of pandas dataframe

2 Answers2