CODEWITHSUNDEEP
pythonjsonpython-3.xpandasdictionary
Hrvoje
Hrvoje

Reputation: 15152

Build JSON object from pandas dataframe

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?

Upvotes: 0

Views: 715

Answers (1)

jezrael
jezrael

Reputation: 862541

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"]}

Upvotes: 1

Related Questions