How to read json file with nested file as a pandas dataframe

Question

I tried to read this json file:

'https://www.redistricting.state.pa.us/Resources/GISData/Districts/Legislative/House/2021-Final/JSON/2022%20LRC-House-Final.json'

from urllib.request import urlopen
import json
with urlopen('https://www.redistricting.state.pa.us/Resources/GISData/Districts/Legislative/House/2021-Final/JSON/2022%20LRC-House-Final.json') as response:
    level_1=json.load(response)

However, I cannot create dataframe from it using:

pd.DataFrame(level_1)

I get error:

ValueError: All arrays must be of the same length

How can I solve the problem?

baduker · Accepted Answer · 2022-09-12 19:03:43Z

1

You could try this and then massage the data to your liking:

import requests
import pandas as pd

api_url = 'https://www.redistricting.state.pa.us/Resources/GISData/Districts/Legislative/House/2021-Final/JSON/2022%20LRC-House-Final.json'

print(pd.json_normalize(requests.get(api_url).json()).T)

Output:

                                                                0
name                                                    Districts
map_layer_type                                               Area
bounds          [[-80.661781, 39.651554], [-74.547303, 42.5843...
center                                    [-77.604542, 41.117936]
zoom                                                            8
median_zoom                                                    10
count                                                         203
property_names  [Area, District, Members, Locked, Name, Adj_Po...
type                                            FeatureCollection
features        [{'type': 'Feature', 'id': 1, 'properties': {'...

If you want features then why not try this?

import requests
import pandas as pd

api_url = 'https://www.redistricting.state.pa.us/Resources/GISData/Districts/Legislative/House/2021-Final/JSON/2022%20LRC-House-Final.json'

print(pd.DataFrame(requests.get(api_url).json()["features"]))

edited Sep 12, 2022 at 19:03

answered Sep 12, 2022 at 18:55

baduker

20.1k9 gold badges43 silver badges63 bronze badges

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

1 Comment

Demi Over a year ago

Sorry for not making it clear from the beginning, but I am interested in 'features' row's data. I want to make df from this row

Collectives™ on Stack Overflow

How to read json file with nested file as a 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