Python JSON -> CSV different headers-CodePudding

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

CodePudding user response：

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