pythonpandasnominatim

Add latitude and longitude column to existing data frame


I am unable to add latitude and longitude column to existing data frame. I have extracted coordinates from one of the column Restaurant_Location from my data-set using below code.

location = [x for x in rest_df['Restaurant_Location'].unique().tolist() if type(x) == str]
latitude = []
longitude =  []
for i in range(0, len(location)):
    if(type(location[i]) == str):
        ctr=0
        while True:
            try:
                address = location[i] + ', Mumbai, India'
                geolocator = Nominatim(user_agent="ny_explorer")
                loc = geolocator.geocode(address)
                latitude.append(loc.latitude)
                longitude.append(loc.longitude)
                print('The geographical coordinate of location are {}, {}.'.format(loc.latitude, loc.longitude))
            except:
                ctr+=1
                if(ctr==7):
                    print(i)
                    latitude.append(address)
                    longitude.append(address)
                    break
                continue
            break

I am able to get the desired Output

The geographical coordinate of location are 19.1840129, 72.8412155.
The geographical coordinate of location are 19.0583358, 72.8302669.

But after running above code successfully rest_df.head() is not showing location_latitude & location_longitude columns in my data-frame, which I want to add in the data-frame. Also there are several other columns in my data-frame. Please let me know where I am doing mistake?


Solution

  • Here is a pice of code that should work, I deleted some stuff as I'm not sure why, but you can still put them back if you do other things in it. new_df should have all the rows from your original dataframe, and added the two columns you want. I have not been able to test as I don't have your data, so they might have typo but the idea is there

    location = [x for x in rest_df['Restaurant_Location'].unique().tolist() 
                if type(x) == str]
    latitude = []
    longitude =  []
    for i in range(0, len(location)):
        # remove things that does not seem usefull here
        try:
            address = location[i] + ', Mumbai, India'
            geolocator = Nominatim(user_agent="ny_explorer")
            loc = geolocator.geocode(address)
            latitude.append(loc.latitude)
            longitude.append(loc.longitude)
            print('The geographical coordinate of location are {}, {}.'.format(loc.latitude, loc.longitude))
        except:
            # in the case the geolocator does not work, then add nan element to list
            # to keep the right size
            latitude.append(np.nan)
            longitude.append(np.nan)
    # create a dataframe with the locatio, latitude and longitude
    df_ = pd.DataFrame({'Restaurant_Location':location, 
                        'location_latitude': latitude,
                        'location_longitude':longitude})
    # merge on Restaurant_Location with rest_df to get the column 
    new_df = rest_df.merge(df_, on='Restaurant_Location', how='left')