CODEWITHSUNDEEP
pythonjsonpandasnull
Raymont
Raymont

Reputation: 293

Parsing Json in Python Null data

Suppose I have the following two Json.

a={"id": "TUxNQkFHVUNBTTA0",
  "name": "Campestre 1a. Secc.",
  "city": {
    "id": "TUxNQ0FHVTk2NjY",
    "name": "Aguascalientes"
  },
  "state": {
    "id": "TUxNUEFHVTMwNjE",
    "name": "Aguascalientes"
  },
  "country": {
    "id": "MX",
    "name": "Mexico"
  },
  "geo_information": None,
  "subneighborhoods": [
  ]
}

b={
  "id": "TUxNTUxNQkFHVTNOSg",
  "name": "Aeropuerto Aguascalientes (Lic. Jesus Teran Peredo)",
  "city": {
    "id": "TUxNQ0FHVTk2NjY",
    "name": "Aguascalientes"
  },
  "state": {
    "id": "TUxNUEFHVTMwNjE",
    "name": "Aguascalientes"
  },
  "country": {
    "id": "MX",
    "name": "Mexico"
  },
  "geo_information": {
    "location": {
      "latitude": 21.701155,
      "longitude": -102.31439
    }
  },
  "subneighborhoods": [
  ]
}


print b

and I want to create a table 'locations' with the next columns:

locations = pandas.DataFrame(columns=['city_id', 'city_name', 'name', 'latitud', 'longitud', 'country_id', 'country_name', 'state_id', 'state_name', 'subneighborhoods', 'id'])

Expect to have the following data:

I expect to have the following table

TUxNQkFHVUNBTTA0, Campestre 1a. Secc., TUxNQ0FHVTk2NjY, Aguascalientes, TUxNUEFHVTMwNjE, Aguascalientes, MX, Mexico, Null, Null, []
TUxNTUxNQkFHVTNOSg, Aeropuerto Aguascalientes (Lic. Jesus Teran Peredo), TUxNQ0FHVTk2NjY, Aguascalientes, TUxNUEFHVTMwNjE, Aguascalientes, MX, Mexico, 21.701155,  -102.31439, []

As in 'a' the geo_information is None, I can not create the table. How con I solve this issue?

Thanks!

Upvotes: 1

Views: 173

Answers (1)

tmrlvi
tmrlvi

Reputation: 2361

Did you try json_normalizer? It will do what you request, just with a dot instead of underline.

In[1]: from pandas.io.json import json_normalize

In[2]: pd.DataFrame(json_normalize([a,b]))
Out[2]: 
           city.id       city.name country.id country.name  geo_information  \
0  TUxNQ0FHVTk2NjY  Aguascalientes         MX       Mexico              NaN
1  TUxNQ0FHVTk2NjY  Aguascalientes         MX       Mexico              NaN

   geo_information.location.latitude  geo_information.location.longitude  \
0                                NaN                                 NaN
1                          21.701155                          -102.31439

                   id                                               name  \
0    TUxNQkFHVUNBTTA0                                Campestre 1a. Secc.
1  TUxNTUxNQkFHVTNOSg  Aeropuerto Aguascalientes (Lic. Jesus Teran Pe...

          state.id      state.name subneighborhoods
0  TUxNUEFHVTMwNjE  Aguascalientes               []
1  TUxNUEFHVTMwNjE  Aguascalientes               []

(However, it will leave subneighborhoods intact, which is not necessarily what you want)

Upvotes: 1

Related Questions