Build JSON object from pandas dataframe

Question

I'm trying to format pandas dataframe:

> year mileage model    manufacturer    power fuel_type price
> 0 2011    184000  c-klasa Mercedes-Benz   161 diesel  114340
> 1 2013    102000  v40 Volvo   130 diesel  80511
> 2 2014    191000  scenic  Renault 85  diesel  57613
> 3 1996    210000  vectra  Opel    85  benzin  6278
> 4 2005    258000  tucson  Hyundai 83  diesel  41363
> 5 2007    325000  astra   Opel    74  diesel  26590
> 6 2002    200000  megane  Renault 79  plin    16988
> 7 2011    191000  touran  VW  77  diesel  62783
> 8 2007    210000  118 BMW 105 diesel  44318
> 9 2012    104000  3   Mazda   85  diesel  63522
> 10    2011    68000   c3  Citroen 54  benzin  44318
> 11    1993    200000  ax  Citroen 37  diesel  43467
> 12    2011    142000  twingo  Renault 55  benzin  28068
> 13    2005    280000  320 BMW 120 diesel  28068

output to fit JSON object requirements. Here's my code:

for model, car in carsDF.groupby('manufacturer'):
    print("{\"",model,":\"[\"",'","'.join(car['model'].unique()),"\"]},")

which yields:

> {" Alfa Romeo
> :"["156","159","146","147","giulietta","gt","33","mito","166","145","brera","sprint","spider","155","ostalo
> "]}, {" Aston Martin :"[" vantage "]},...

Which is ok except for spaces that shows each time I use escape chars "\".

How to create JSON object without them? Is there any better way to generate JSON object for case like this?

Can you add some sample data?

jezrael
– jezrael

2019-01-16 07:09:26 +00:00
Commented Jan 16, 2019 at 7:09 — jezrael
– jezrael, Commented Jan 16, 2019 at 7:09
I've added it..

Hrvoje
– Hrvoje

2019-01-16 07:20:38 +00:00
Commented Jan 16, 2019 at 7:20 — Hrvoje
– Hrvoje, Commented Jan 16, 2019 at 7:20

jezrael · Accepted Answer · 2019-01-16 07:21:55Z

1

I believe you need create Series by unique values by SeriesGroupBy.unique and then convert to json by Series.to_json:

j = carsDF.groupby('manufacturer')['model'].unique().to_json()
print (j)

{
    "BMW": ["118", "320"],
    "Citroen": ["c3", "ax"],
    "Hyundai": ["tucson"],
    "Mazda": ["3"],
    "Mercedes-Benz": ["c-klasa"],
    "Opel": ["vectra", "astra"],
    "Renault": ["scenic", "megane", "twingo"],
    "VW": ["touran"],
    "Volvo": ["v40"]
}

If want each json separately solution is create dictionaries and convert to jsons:

import json

for model, car in carsDF.groupby('manufacturer'):
    print (json.dumps({model: car['model'].unique().tolist()}))

{"BMW": ["118", "320"]}
{"Citroen": ["c3", "ax"]}
{"Hyundai": ["tucson"]}
{"Mazda": ["3"]}
{"Mercedes-Benz": ["c-klasa"]}
{"Opel": ["vectra", "astra"]}
{"Renault": ["scenic", "megane", "twingo"]}
{"VW": ["touran"]}
{"Volvo": ["v40"]}

edited Jan 16, 2019 at 7:21

answered Jan 16, 2019 at 7:16

jezrael

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

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

1 Comment

Hrvoje Over a year ago

can you please add to you answer how to create JSON like var data in this fiddle: jsfiddle.net/036easd8/1

Collectives™ on Stack Overflow

Build JSON object from pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related