How to convert nested json to dataframe?

Question

{
    "RecordSet": {
        "geometryType": "esriGeometryPoint",
        "spatialReference": {
            "wkid": "DLT0"
        },
        "features": [{
                "geometry": {
                    "x": 4941.9900000002,
                    "y": 27766.2190000005,
                    "spatialReference": {
                        "wkid": "DLT0"
                    }
                },
                "attributes": {
                    "COMMUNITY_": 35,
                    "NAME_ENG": "FOUR POINTS ,
                    "LATITUDE": 25.1404585,
                    "LONGITUDE": 55.2599731,
                    "MNUM": "261 89753"
                }
            },
            {
                "geometry": {
                    "x": 5080.0719999997,
                    "y": 23025.9379999992,
                    "spatialReference": {
                        "wkid": "DLT0"
                    }
                },
                "attributes": {
                    "COMMUNITY_": 12,
                    "NAME_ENG": "PARK  - PORT ",
                    "LATITUDE": 25.24831,
                    "LONGITUDE": 55.312064,
                    "MNUM": "321445"
                }
            }
        ]
    }
}

Code:

Try1. pd.DataFrame(sum(json.load(open('test.json')), [])

Try2. df = pd.concat([pd.DataFrame(x) for x in data], ignore_index=False)

Try3. df = pd.json_normalize(data)
      df.columns = df.columns.map(lambda x: x.split(".")[-1])
      df

Try4: test = pd.json_normalize(data)

Trying to get the columns - x,y,spatialRef,COMMUNITY_,NAME_ENG,LATITUDE,LONGITUDE, MNUM. I have looked at other questions on this topic, have tried various ways to load the JSON file into pandas. Kindly let me how to find the best solution

Answer 1

• simplest is a single use of json_normalize() giving reference to the list key
• more sophisticated you can expand everything

js = {'RecordSet': {'geometryType': 'esriGeometryPoint',
  'spatialReference': {'wkid': 'DLT0'},
  'features': [{'geometry': {'x': 4941.9900000002,
     'y': 27766.2190000005,
     'spatialReference': {'wkid': 'DLT0'}},
    'attributes': {'COMMUNITY_': 35,
     'NAME_ENG': 'FOUR POINTS',
     'LATITUDE': 25.1404585,
     'LONGITUDE': 55.2599731,
     'MNUM': '261 89753'}},
   {'geometry': {'x': 5080.0719999997,
     'y': 23025.9379999992,
     'spatialReference': {'wkid': 'DLT0'}},
    'attributes': {'COMMUNITY_': 12,
     'NAME_ENG': 'PARK  - PORT ',
     'LATITUDE': 25.24831,
     'LONGITUDE': 55.312064,
     'MNUM': '321445'}}]}}

# siumplest
pd.json_normalize(js["RecordSet"]["features"])

# complete
df = pd.json_normalize(js, record_path=[["RecordSet","features"]], meta="RecordSet")
df = df.join(df["RecordSet"].apply(pd.Series)).drop(columns=["RecordSet","features"])
df.join(df["spatialReference"].apply(pd.Series)).drop(columns=["spatialReference"])