I’m using Google Maps API to geocode addresses. Ultimately I need to add these to a data frame and then into a database. I’m having issues parsing the JSON into a data frame. I’ve been searching various threads and haven’t come up with a solution.

Code:

    import googlemaps
    from datetime import datetime
    import pandas as pd

    geocode_results = gmaps.geocode(address)

JSON:

[{'address_components': [{'long_name': '1600',
    'short_name': '1600',
    'types': ['street_number']},
   {'long_name': 'Pennsylvania Avenue Northwest',
    'short_name': 'Pennsylvania Avenue NW',
    'types': ['route']},
   {'long_name': 'Northwest Washington',
    'short_name': 'Northwest Washington',
    'types': ['neighborhood', 'political']},
   {'long_name': 'Washington',
    'short_name': 'Washington',
    'types': ['locality', 'political']},
   {'long_name': 'District of Columbia',
    'short_name': 'DC',
    'types': ['administrative_area_level_1', 'political']},
   {'long_name': 'United States',
    'short_name': 'US',
    'types': ['country', 'political']},
   {'long_name': '20500', 'short_name': '20500', 'types': ['postal_code']},
   {'long_name': '0005',
    'short_name': '0005',
    'types': ['postal_code_suffix']}],
  'buildings': [],
  'entrances': [],
  'formatted_address': '1600 Pennsylvania Avenue NW, Washington, DC 20500, USA',
  'geometry': {'location': {'lat': 38.8977263, 'lng': -77.0363089},
   'location_type': 'ROOFTOP',
   'viewport': {'northeast': {'lat': 38.8996082802915, 'lng': -77.0363089},
    'southwest': {'lat': 38.8969103197085, 'lng': -77.03945980000002}}},
  'place_id': 'ChIJj29ffVS3t4kRssvmz4DOiZE',
  'plus_code': {'compound_code': 'VXX7+3F Washington, DC',
   'global_code': '87C4VXX7+3F'},
  'types': ['street_address']}]

If I use pd.json_normalize(geocode_results), get a data frame but the address_compnents are nested into a singe column/row.

enter image description here

I’ve tried: ‘pd.json_normalize(geocode_results, record_path=[‘address_components’])’ but this makes the reverse of what I want. It created a long rather than wide data frame.

Ideally I’d have one row in this case and each column are the fields:

street_number_long,
street_number_short,
…formmatted_address etc.

Any ideas?

I’ve tried Google, Stackoverflow searching, Youtube etc. The answers are slightly different to my problem solution. Maybe because the json reponse I have is from using Google’s gmaps.geocode rather than requests.get(). I don’t get a results section or response code (which I don’t need).

I also tried geocode_results.keys() to see the keys which doesn’t work here.