Parsing Json in Python Null data

Question

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!

That's not json, those are dictionaries. They can easily be serialized into json with json.dumps(a). — jordanm
– jordanm, Commented Apr 14, 2017 at 18:09
@jordanm that is true, but given that the question asks about putting the data into a pandas.DataFrame, I suspect the OP does not want to serialize the dictionaries. — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Apr 14, 2017 at 18:43
subneighborhoods is a list, how does the data in it looks like? What do you expect the relevant column in locations to contain? — tmrlvi
– tmrlvi, Commented Apr 14, 2017 at 19:05
I expect to have the following table city_id, city_name, name, latitud, longitud, country_id, country_name, state_id, state_name, subneighborhoods 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, [] — Raymont
– Raymont, Commented Apr 14, 2017 at 19:13

tmrlvi · Accepted Answer · 2017-04-14 19:12:28Z

1

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)

answered Apr 14, 2017 at 19:12

tmrlvi

2,36019 silver badges37 bronze badges

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

1 Comment

Raymont Over a year ago

It was solved by if tree_json['geo_information'] is None: tree_json.update({"geo_information": {"location": {"latitude": None, "longitude": None}}})

Collectives™ on Stack Overflow

Parsing Json in Python Null data

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