Python JSON -> CSV different headers

Question

I have a json file that is like so:

{"16CD7631-0ED0-4DA0-8D3B-8BBB41992EED": {"id": "16CD7631-0ED0-4DA0-8D3B-8BBB41992EED", "longitude": "-122.406417", "reportType": "Other", "latitude": "37.785834"}, "91CA4A9C-9A48-41A2-8453-07CBC8DC723E": {"id": "91CA4A9C-9A48-41A2-8453-07CBC8DC723E", "longitude": "-1.1932383", "reportType": "Street Obstruction", "latitude": "45.8827419"}}

The goal is to get this to turn into a csv file like so:

id,longitude,reportType,latitude
16CD7631-0ED0-4DA0-8D3B-8BBB41992EED,-122.406417,Other,37.785834
91CA4A9C-9A48-41A2-8453-07CBC8DC723E,-1.1932383,Street Obstruction,45.8827419

I tried just doing

with open('sample.json', encoding='utf-8') as inputfile:
        df = pd.read_json(inputfile)

df.to_csv('csvfile.csv', encoding='utf-8', index=False)

But because the name of each document was named its id, I get incorrect output. What is the best way to achieve my goal? Thanks

Timeless · Accepted Answer · 2022-12-04 21:04:03Z

1

You can use pandas.json_normalize.

Try this :

import json
import pandas as pd

with open('sample.json', encoding='utf-8') as inputfile:
    data = json.load(inputfile)
    df = pd.json_normalize(data[k] for k in data.keys())

# Output :

print(df.to_string())

                                     id    longitude          reportType    latitude
0  16CD7631-0ED0-4DA0-8D3B-8BBB41992EED  -122.406417               Other   37.785834
1  91CA4A9C-9A48-41A2-8453-07CBC8DC723E   -1.1932383  Street Obstruction  45.8827419

edited Dec 4, 2022 at 21:04

answered Dec 4, 2022 at 20:29

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

4 Comments

CeilingSystems Over a year ago

Thanks for reply, what should data point to?

CeilingSystems Over a year ago

Hello, sorry to keep bugging you, is there way to remove the first column (0 and 1) from this? Thank you

Timeless Over a year ago

If you're saving the dataframe in a .csv, you can use index=False like this : df.to_csv("outputfile.csv", index=False).

Timeless Over a year ago

Or if you wanna just print the dataframe, use print(df.to_string(index=False)).

Collectives™ on Stack Overflow

Python JSON -> CSV different headers

1 Answer 1

# Output :

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related