How to import nested json including lists into python as Dataframe

Question

{
"type": "FeatureCollection",
"name": "Altstadt Nord",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "Name": "City-Martinsviertel", "description": null }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 6.9595637, 50.9418396 ], [ 6.956624, 50.9417382 ], [ 6.9543173, 50.941603 ], [ 6.9529869, 50.9413664 ], [ 6.953062, 50.9408593 ], [ 6.9532873, 50.9396289 ], [ 6.9533624, 50.9388176 ], [ 6.9529333, 50.9378373 ], [ 6.9527509, 50.9371815 ], [ 6.9528367, 50.9360659 ], [ 6.9532122, 50.9352884 ], [ 6.9540705, 50.9350653 ], [ 6.9553258, 50.9350044 ], [ 6.9568815, 50.9351667 ], [ 6.9602074, 50.9355047 ], [ 6.9608189, 50.9349165 ], [ 6.9633939, 50.9348827 ], [ 6.9629433, 50.9410622 ], [ 6.9616236, 50.9412176 ], [ 6.9603898, 50.9414881 ], [ 6.9595637, 50.9418396 ] ] ] } },
{ "type": "Feature", "properties": { "Name": "Gereonsviertel", "description": null }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 6.9629433, 50.9410622 ], [ 6.9629433, 50.9431646 ], [ 6.9611408, 50.9433539 ], [ 6.9601752, 50.9436649 ], [ 6.9588234, 50.9443409 ], [ 6.9579651, 50.9449763 ], [ 6.9573213, 50.945801 ], [ 6.9563128, 50.9451926 ], [ 6.9551756, 50.9448546 ], [ 6.9535663, 50.9446518 ], [ 6.9523432, 50.9449763 ], [ 6.9494464, 50.9452602 ], [ 6.9473435, 50.9454495 ], [ 6.9466998, 50.9456928 ], [ 6.9458415, 50.946531 ], [ 6.9434168, 50.9453954 ], [ 6.9424726, 50.9451926 ], [ 6.9404342, 50.9429888 ], [ 6.9404771, 50.9425156 ], [ 6.9403269, 50.9415016 ], [ 6.9400479, 50.9405281 ], [ 6.9426228, 50.9399872 ], [ 6.9439103, 50.9400143 ], [ 6.9453051, 50.9404875 ], [ 6.9461634, 50.9408931 ], [ 6.9467427, 50.941096 ], [ 6.9475581, 50.9410013 ], [ 6.9504227, 50.9413191 ], [ 6.9529869, 50.9413664 ], [ 6.9547464, 50.9416368 ], [ 6.9595637, 50.9418396 ], [ 6.9603898, 50.9414881 ], [ 6.9616236, 50.9412176 ], [ 6.9629433, 50.9410622 ] ] ] } },
{ "type": "Feature", "properties": { "Name": "Kunibertsviertel", "description": null }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 6.9629433, 50.9431646 ], [ 6.9637129, 50.9454917 ], [ 6.9651506, 50.9479252 ], [ 6.9666097, 50.9499124 ], [ 6.9667599, 50.9500882 ], [ 6.9587777, 50.9502504 ], [ 6.9573213, 50.945801 ], [ 6.9579651, 50.9449763 ], [ 6.9588234, 50.9443409 ], [ 6.9601752, 50.9436649 ], [ 6.9611408, 50.9433539 ], [ 6.9629433, 50.9431646 ] ] ] } },
{ "type": "Feature", "properties": { "Name": "Nördlich Neumarkt", "description": null }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 6.9390331, 50.9364418 ], [ 6.9417153, 50.9358738 ], [ 6.9462214, 50.9358062 ], [ 6.9490109, 50.9355628 ], [ 6.9505129, 50.9353329 ], [ 6.9523798, 50.9352924 ], [ 6.9532122, 50.9352884 ], [ 6.9528367, 50.9360659 ], [ 6.9527509, 50.9371815 ], [ 6.9529333, 50.9378373 ], [ 6.9533624, 50.9388176 ], [ 6.9532381, 50.9398222 ], [ 6.9529869, 50.9413664 ], [ 6.9504227, 50.9413191 ], [ 6.9475581, 50.9410013 ], [ 6.9467427, 50.941096 ], [ 6.9453051, 50.9404875 ], [ 6.9439103, 50.9400143 ], [ 6.9424663, 50.9399574 ], [ 6.9400479, 50.9405281 ], [ 6.9390331, 50.9364418 ] ] ] } }
]
}

I got this json data and wand to import to to a pandas Dataframe:

I tried:

pd.read_json('path.json')

But got this Error: ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.

can somebody please help? I already tried json_normalize but that did not work either.

dbc · Accepted Answer · 2021-12-18 15:41:56Z

1

If you just need the result part in a dataframe, then the code from this answer by Rao Sahab to Read JSON to pandas dataframe - ValueError: Mixing dicts with non-Series may lead to ambiguous ordering will help you:

import json
import pandas as pd
data = json.load(open('json_file.json'))

df = pd.DataFrame(data["result"])

edited Dec 18, 2021 at 15:41

dbc

120k27 gold badges273 silver badges404 bronze badges

answered Dec 18, 2021 at 14:53

Lucas Campos

1081 silver badge8 bronze badges

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

2 Comments

Jojo Over a year ago

that sadly results in this error TypeError: the JSON object must be str, bytes or bytearray, not TextIOWrapper

Lucas Campos Over a year ago

you can pass the data like: df = pd.DataFrame(data['features'])

Collectives™ on Stack Overflow

How to import nested json including lists into python as Dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related