Python extract elements from Json string

Question

I have a Json string from which I'm able to extract few components like formatted_address,lat,lng, but I'm unable to extract feature(values) of other components like intersection, political, country, administrative_area_level_1 , administrative_area_level_2 , administrative_area_level_3 , administrative_area_level_4, administrative_area_level_5, colloquial_area , locality , ward, neighborhood, premise, subpremise etc which is under long_name I'm expecting datatable like

formatted_address              px_val      py_val      political      country      administrative_area_level_1   ..  ..                 
Satya Niwas, Kanti Nagar..   19.1096591  72.8674712 Kanti Nagar,JB Nagar India   maharashtra   ..  ..                                                                 
82, Bamanpuri, Ajit Nagar..  19.109749   72.867249    Bamanpuri    India maharashtra   .. .. 
    .
    . 
    .

Here is the sample JSON string

{'results': [{'address_components': [{'long_name': 'Satya Niwas',
     'short_name': 'Satya Niwas',
     'types': ['establishment', 'point_of_interest', 'premise']},
    {'long_name': 'Kanti Nagar',
     'short_name': 'Kanti Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400059', 'short_name': '400059', 'types': ['postal_code']}],
   'formatted_address': 'Satya Niwas, Kanti Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400059, India',
   'geometry': {'bounds': {'northeast': {'lat': 19.1097923, 'lng': 72.8675306},
     'southwest': {'lat': 19.1095784, 'lng': 72.8673391}},
    'location': {'lat': 19.1096591, 'lng': 72.8674712},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1110343302915,
      'lng': 72.8687838302915},
     'southwest': {'lat': 19.1083363697085, 'lng': 72.86608586970848}}},
   'place_id': 'ChIJ4UsP5DjI5zsR8hgwhHo9wEk',
   'types': ['establishment', 'point_of_interest', 'premise']},
  {'address_components': [{'long_name': '82',
     'short_name': '82',
     'types': ['premise']},
    {'long_name': 'Bamanpuri',
     'short_name': 'Bamanpuri',
     'types': ['neighborhood', 'political']},
    {'long_name': 'Ajit Nagar',
     'short_name': 'Ajit Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400053', 'short_name': '400053', 'types': ['postal_code']}],
   'formatted_address': '82, Bamanpuri, Ajit Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400053, India',
   'geometry': {'location': {'lat': 19.109749, 'lng': 72.867249},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1110979802915,
      'lng': 72.8685979802915},
     'southwest': {'lat': 19.1084000197085, 'lng': 72.86590001970849}}},
   'place_id': 'ChIJqYip4zjI5zsR0Yg8bdXQX3o',
   'types': ['street_address']},
  {'address_components': [{'long_name': 'Todi Building',
     'short_name': 'Todi Building',
     'types': ['premise']},
    {'long_name': 'Sheth Bhavanidas Benani Marg',
     'short_name': 'Sheth Bhavanidas Benani Marg',
     'types': ['route']},
    {'long_name': 'Kanti Nagar',
     'short_name': 'Kanti Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400059', 'short_name': '400059', 'types': ['postal_code']}],
   'formatted_address': 'Todi Building, Sheth Bhavanidas Benani Marg, Kanti Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400059, India',
   'geometry': {'location': {'lat': 19.1098265, 'lng': 72.86778869999999},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1111754802915,
      'lng': 72.86913768029149},
     'southwest': {'lat': 19.1084775197085, 'lng': 72.86643971970848}}},
   'place_id': 'ChIJo5bq3zjI5zsR2hRaNQF3xd0',
   'types': ['premise']},
  {'address_components': [{'long_name': 'KASI APARTMENTS',
     'short_name': 'KASI APARTMENTS',
     'types': ['establishment', 'point_of_interest', 'premise']},
    {'long_name': 'Shriniwas Bagarka Road',
     'short_name': 'Shriniwas Bagarka Rd',
     'types': ['route']},
    {'long_name': 'Bamanpuri',
     'short_name': 'Bamanpuri',
     'types': ['neighborhood', 'political']},
    {'long_name': 'Kanti Nagar',
     'short_name': 'Kanti Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400059', 'short_name': '400059', 'types': ['postal_code']}],
   'formatted_address': 'KASI APARTMENTS, Shriniwas Bagarka Rd, Bamanpuri, Kanti Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400059, India',
   'geometry': {'location': {'lat': 19.1093338, 'lng': 72.8670515},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1106827802915,
      'lng': 72.86840048029151},
     'southwest': {'lat': 19.10798481970849, 'lng': 72.86570251970849}}},
   'place_id': 'ChIJoUz25DjI5zsRiMoiQtq5kXs',
   'types': ['establishment', 'point_of_interest', 'premise']},
  {'address_components': [{'long_name': 'Silver Line Apts.',
     'short_name': 'Silver Line Apts.',
     'types': ['premise']},
    {'long_name': 'Bamanpuri',
     'short_name': 'Bamanpuri',
     'types': ['neighborhood', 'political']},
    {'long_name': 'J.B. Nagar',
     'short_name': 'J.B. Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400047', 'short_name': '400047', 'types': ['postal_code']}],
   'formatted_address': 'Silver Line Apts., Bamanpuri, J.B. Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400047, India',
   'geometry': {'location': {'lat': 19.1091075, 'lng': 72.8670776},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1104564802915,
      'lng': 72.86842658029151},
     'southwest': {'lat': 19.1077585197085, 'lng': 72.86572861970849}}},
   'place_id': 'ChIJEQ3_ZzjI5zsR9LxIP1h2b2c',
   'types': ['premise']},
  {'address_components': [{'long_name': 'Gokul panch chs',
     'short_name': 'Gokul panch chs',
     'types': ['establishment', 'point_of_interest']},
    {'long_name': '81-B', 'short_name': '81-B', 'types': ['street_number']},
    {'long_name': 'Sheth Bhavanidas Benani Marg',
     'short_name': 'Sheth Bhavanidas Benani Marg',
     'types': ['route']},
    {'long_name': 'Bamanpuri',
     'short_name': 'Bamanpuri',
     'types': ['neighborhood', 'political']},
    {'long_name': 'Ajit Nagar',
     'short_name': 'Ajit Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400047', 'short_name': '400047', 'types': ['postal_code']}],
   'formatted_address': 'Gokul panch chs, 81-B, 81-B, Sheth Bhavanidas Benani Marg, Bamanpuri, Ajit Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400047, India',
   'geometry': {'location': {'lat': 19.1098713, 'lng': 72.86705669999999},
    'location_type': 'ROOFTOP',
    'viewport': {'northeast': {'lat': 19.1112202802915,
      'lng': 72.8684056802915},
     'southwest': {'lat': 19.1085223197085, 'lng': 72.8657077197085}}},
   'place_id': 'ChIJpUuz4jjI5zsRpgQdmR5E1v0',
   'types': ['establishment', 'point_of_interest']},
  {'address_components': [{'long_name': 'Ajit Nagar',
     'short_name': 'Ajit Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_3']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400047', 'short_name': '400047', 'types': ['postal_code']}],
   'formatted_address': 'Ajit Nagar, J B Nagar, Andheri East, Mumbai, Maharashtra 400047, India',
   'geometry': {'bounds': {'northeast': {'lat': 19.1119198, 'lng': 72.8714133},
     'southwest': {'lat': 19.1085396, 'lng': 72.8662167}},
    'location': {'lat': 19.1103164, 'lng': 72.8680732},
    'location_type': 'APPROXIMATE',
    'viewport': {'northeast': {'lat': 19.1119198, 'lng': 72.8714133},
     'southwest': {'lat': 19.1085396, 'lng': 72.8662167}}},
   'place_id': 'ChIJPWPg4zjI5zsRJWPFphEkcxc',
   'types': ['political', 'sublocality', 'sublocality_level_3']},
  {'address_components': [{'long_name': 'Bamanpuri',
     'short_name': 'Bamanpuri',
     'types': ['neighborhood', 'political']},
    {'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400047', 'short_name': '400047', 'types': ['postal_code']}],
   'formatted_address': 'Bamanpuri, J B Nagar, Andheri East, Mumbai, Maharashtra 400047, India',
   'geometry': {'bounds': {'northeast': {'lat': 19.1102874, 'lng': 72.869838},
     'southwest': {'lat': 19.1060651, 'lng': 72.8635609}},
    'location': {'lat': 19.1084347, 'lng': 72.86574929999999},
    'location_type': 'APPROXIMATE',
    'viewport': {'northeast': {'lat': 19.1102874, 'lng': 72.869838},
     'southwest': {'lat': 19.1060651, 'lng': 72.8635609}}},
   'place_id': 'ChIJIYgnUDjI5zsRK_Zl9Zy_QkY',
   'types': ['neighborhood', 'political']},
  {'address_components': [{'long_name': 'J B Nagar',
     'short_name': 'J B Nagar',
     'types': ['political', 'sublocality', 'sublocality_level_2']},
    {'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']},
    {'long_name': '400047', 'short_name': '400047', 'types': ['postal_code']}],
   'formatted_address': 'J B Nagar, Andheri East, Mumbai, Maharashtra 400047, India',
   'geometry': {'bounds': {'northeast': {'lat': 19.1161579, 'lng': 72.871533},
     'southwest': {'lat': 19.1008041, 'lng': 72.8606231}},
    'location': {'lat': 19.1110621, 'lng': 72.8655922},
    'location_type': 'APPROXIMATE',
    'viewport': {'northeast': {'lat': 19.1161579, 'lng': 72.871533},
     'southwest': {'lat': 19.1008041, 'lng': 72.8606231}}},
   'place_id': 'ChIJt8_u6TjI5zsRR9eE5rMK45A',
   'types': ['political', 'sublocality', 'sublocality_level_2']},
  {'address_components': [{'long_name': 'Andheri East',
     'short_name': 'Andheri East',
     'types': ['political', 'sublocality', 'sublocality_level_1']},
    {'long_name': 'Mumbai',
     'short_name': 'Mumbai',
     'types': ['locality', 'political']},
    {'long_name': 'Mumbai Suburban',
     'short_name': 'Mumbai Suburban',
     'types': ['administrative_area_level_2', 'political']},
    {'long_name': 'Maharashtra',
     'short_name': 'MH',
     'types': ['administrative_area_level_1', 'political']},
    {'long_name': 'India',
     'short_name': 'IN',
     'types': ['country', 'political']}],
   'formatted_address': 'Andheri East, Mumbai, Maharashtra, India',
   'geometry': {'bounds': {'northeast': {'lat': 19.1327276,
      'lng': 72.89305499999999},
     'southwest': {'lat': 19.096748, 'lng': 72.843926}},
    'location': {'lat': 19.1154908, 'lng': 72.8726952},
    'location_type': 'APPROXIMATE',
    'viewport': {'northeast': {'lat': 19.1327276, 'lng': 72.89305499999999},
     'southwest': {'lat': 19.096748, 'lng': 72.843926}}},
   'place_id': 'ChIJMbHfQRu25zsRMazdY3UpaKY',
   'types': ['political', 'sublocality', 'sublocality_level_1']}],
 'status': 'OK'}

Here is the snippet of code

    import json
    import pandas as pd
    line="json_str"
    json_st = json.loads(line)
    country=[]
    political=[]
    address_fields = { 
    'intersection': [],        
    'political': [],        
    'country': []
}

for json_str in json_st:
    address_fields = {

        'intersection': [],        
        'political': [],        
        'country': []
    }
    if isinstance(json_st,dict): 
         first_address_components = json_st['results']
         #format_add = json_st['results'][0]
    else:
         first_address_components = json_st[0]['address_components']
    for item in first_address_components:

        for field_key in address_fields.keys():
                #address_fields[field_key].append( str(format_add['formatted_address']))
              if field_key in item['types']:
               address_fields[field_key].append(item['long_name'])

    address_fields = {key: ', '.join(values) for key, values in address_fields.items()}
    country.append(address_fields['country'])   
    political.append(address_fields['political'])

It gives error

json_st['results']['address_components']
Traceback (most recent call last):

  File "<ipython-input-94-315fa8711f9d>", line 1, in <module>
    json_st['results']['address_components']

TypeError: list indices must be integers or slices, not str

I'm getting the first 3 columns of expected O/P but unable to extract other columns. Any suggestion on the same will be helpful

Thanks

Domnick

`json_st['results'][0]['geometry']['location']['lat']` and `json_st['results'][0]['geometry']['location']['lng']` — Domnick, Oct 31 '17 at 12:53
its a bit hard but we can simplify solution with json_normalize hope by solution helps. — Bharath M Shetty, Oct 31 '17 at 13:14
I would like to help if you say what you want to fill in `political` , `country` column. — Bharath M Shetty, Oct 31 '17 at 13:24

Bharath M Shetty · Accepted Answer · 2017-11-01T05:15:55.500

I would go for json_normalize, thought of one line answer but I dont think its possible i.e (Here I did only for px_val and py_val you can do similar things for other columns)

from pandas.io.json import json_normalize

import pandas as pd
import json

with open('dat.json') as f:
    data = json.load(f)

result = json_normalize(data,'results')

result['px_val'] = result['geometry'].apply(json_normalize).apply(lambda x : x['location.lat'])
result['py_val'] = result['geometry'].apply(json_normalize).apply(lambda x : x['location.lng'])

print(result[['formatted_address','px_val','py_val']])

                              formatted_address        px_val      py_val
0  Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...  19.109659  72.867471
1  82, Bamanpuri, Ajit Nagar, J B Nagar, Andheri ...  19.109749  72.867249
2  Todi Building, Sheth Bhavanidas Benani Marg, K...  19.109827  72.867789
3  KASI APARTMENTS, Shriniwas Bagarka Rd, Bamanpu...  19.109334  72.867052
4  Silver Line Apts., Bamanpuri, J.B. Nagar, J B ...  19.109108  72.867078
5  Gokul panch chs, 81-B, 81-B, Sheth Bhavanidas ...  19.109871  72.867057
6  Ajit Nagar, J B Nagar, Andheri East, Mumbai, M...  19.110316  72.868073
7  Bamanpuri, J B Nagar, Andheri East, Mumbai, Ma...  19.108435  72.865749
8  J B Nagar, Andheri East, Mumbai, Maharashtra 4...  19.111062  72.865592
9           Andheri East, Mumbai, Maharashtra, India  19.115491  72.872695

I try to parse political certainly not proud of this solution i.e

pol = []
for i in result['address_components'].apply(json_normalize):
     pol.append(','.join(i.apply(lambda x : x['long_name'] if 'political' in x['types'] else np.nan,1).dropna()))

result['political'] = pol

Output result['political']

0    Kanti Nagar,J B Nagar,Andheri East,Mumbai,Mumb...
1    Bamanpuri,Ajit Nagar,J B Nagar,Andheri East,Mu...
2    Kanti Nagar,J B Nagar,Andheri East,Mumbai,Mumb...
3    Bamanpuri,Kanti Nagar,J B Nagar,Andheri East,M...
4    Bamanpuri,J.B. Nagar,J B Nagar,Andheri East,Mu...
5    Bamanpuri,Ajit Nagar,J B Nagar,Andheri East,Mu...
6    Ajit Nagar,J B Nagar,Andheri East,Mumbai,Mumba...
7    Bamanpuri,J B Nagar,Andheri East,Mumbai,Mumbai...
8    J B Nagar,Andheri East,Mumbai,Mumbai Suburban,...
9    Andheri East,Mumbai,Mumbai Suburban,Maharashtr...
Name: political, dtype: object

To convert it to a method we can do

def get_cols(st):
    pol = []
    for i in result['address_components'].apply(json_normalize):
         pol.append(','.join(i.apply(lambda x : x['long_name'] if st in x['types'] else np.nan,1).dropna()))

   return  pol

result['political'] = get_cols('political') 
# This will assign the new column political with data.

If you come across JsonDecodeError try replacing the `'` in your json with `"` — Bharath M Shetty, Oct 31 '17 at 13:15
the solution you gave for `result['political']` can it be similarly done for other columns like `intersection, political, country, administrative_area_level_1 , administrative_area_level_2 , administrative_area_level_3 , administrative_area_level_4, administrative_area_level_5, colloquial_area , locality , ward, neighborhood, premise etc` ?? — Domnick, Nov 01 '17 at 04:44
Yes it can be done. Take that for loop and create a method to get your job done. — Bharath M Shetty, Nov 01 '17 at 04:45
what is `np` in `else np.nan,1).dropna())` code for calculating `result['political']` — Domnick, Nov 01 '17 at 04:49
Its numpy. `import numpy as np`. I added a snippet to help you out. Do vote if you liked the solution. — Bharath M Shetty, Nov 01 '17 at 04:50
your code worked like charm...have upvoted it as well.. Thanks a lot for your effort... — Domnick, Nov 01 '17 at 05:29
How can I access `types` values i.e `get_cols('types')` but this gives me `['', '', '', '', '', '', '', '', '', '']` — Domnick, Nov 01 '17 at 08:54

score 1 · Answer 2 · answered Oct 31 '17 at 13:45

This is rather a pretty broad question...

To help you to start:

record_path = ['address_components']

meta= [
  'formatted_address',
  ['geometry','location','lat'],
  ['geometry','location','lng'],  
]

x = pd.io.json.json_normalize(d['results'], record_path, meta)

Result:

In [20]: pd.options.display.max_rows = 15

In [21]: x
Out[21]:
          long_name       short_name                                          types  \
0       Satya Niwas      Satya Niwas    [establishment, point_of_interest, premise]
1       Kanti Nagar      Kanti Nagar  [political, sublocality, sublocality_level_3]
2         J B Nagar        J B Nagar  [political, sublocality, sublocality_level_2]
3      Andheri East     Andheri East  [political, sublocality, sublocality_level_1]
4            Mumbai           Mumbai                          [locality, political]
5   Mumbai Suburban  Mumbai Suburban       [administrative_area_level_2, political]
6       Maharashtra               MH       [administrative_area_level_1, political]
..              ...              ...                                            ...
83            India               IN                           [country, political]
84           400047           400047                                  [postal_code]
85     Andheri East     Andheri East  [political, sublocality, sublocality_level_1]
86           Mumbai           Mumbai                          [locality, political]
87  Mumbai Suburban  Mumbai Suburban       [administrative_area_level_2, political]
88      Maharashtra               MH       [administrative_area_level_1, political]
89            India               IN                           [country, political]

                                    formatted_address  geometry.location.lat  geometry.location.lng
0   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
1   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
2   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
3   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
4   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
5   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
6   Satya Niwas, Kanti Nagar, J B Nagar, Andheri E...              19.109659              72.867471
..                                                ...                    ...                    ...
83  J B Nagar, Andheri East, Mumbai, Maharashtra 4...              19.111062              72.865592
84  J B Nagar, Andheri East, Mumbai, Maharashtra 4...              19.111062              72.865592
85           Andheri East, Mumbai, Maharashtra, India              19.115491              72.872695
86           Andheri East, Mumbai, Maharashtra, India              19.115491              72.872695
87           Andheri East, Mumbai, Maharashtra, India              19.115491              72.872695
88           Andheri East, Mumbai, Maharashtra, India              19.115491              72.872695
89           Andheri East, Mumbai, Maharashtra, India              19.115491              72.872695

[90 rows x 6 columns]

I tried my best to go into `json_normalize` TBH I used it for the first time. Yours look really neat. The tough one is to get the political column and rest of others. — Bharath M Shetty, Oct 31 '17 at 13:50
@Bharath, thank you! Yeah, `json_normalize` is bit tricky... I think we can continue using something similar to [this](https://stackoverflow.com/a/40449726/5741205), applying it to `type` column... — MaxU - stand with Ukraine, Oct 31 '17 at 13:52
@MaxU what I expect is that the `types` column from `x` should be transposed such that I would be able to get the O/P like the expected o/p posted — Domnick, Nov 01 '17 at 05:06

Prime Reaper · Answer 3 · 2017-10-31T12:38:54.663

0

You need to understand schema of your data.

Error in json_st['results']['address_components']

because json_st['results'] is an array

Check it here http://jsoneditoronline.org

Here is some sample

for result in data['results']:
    print type(result)
    for address_component in result['address_components']:
        print type(address_component)
        print address_component['long_name']
        print address_component['short_name']
        for _type in address_component['types']:
            print _type

edited Oct 31 '17 at 12:38

answered Oct 31 '17 at 12:19

Prime Reaper

176
2

`type(json_st)` is `dict` – Domnick Oct 31 '17 at 12:24
@Domnick and what is type of json_st['result']? – Prime Reaper Oct 31 '17 at 12:29

Python extract elements from Json string

3 Answers3

Linked