reverse geocoding dataframes for states

Question

I have a code with pandas and geopy that obtains a dataframe from excel and obtains locations from coordinates (its slow).

import pandas as pd
import geopy as gp

geolocator = gp.geocoders.Nominatim(user_agent="Fortune")

BC = pd.read_excel("MEXSCTv2.xlsx",sheet_name='BC')

BC['coord']=BC['LATITUD'].astype("string")+","+BC['LONGITUD'].astype("string")
BC['location'] = BC['coord'].apply(geolocator.reverse)
BC[['coord','location']]

gives:

coord   location
0   31.874957,-116.56535    (Carretera Ensenada-San Felipe, Ensenada, Muni...
1   31.887848,-116.504794   (Carretera Ensenada-San Felipe, Ensenada, Muni...
2   31.887947,-116.492945   (Carretera Ensenada-San Felipe, Ensenada, Muni...
3   31.888869,-116.280557   (Carretera Ensenada-San Felipe, Rancho Agua Vi...
4   31.848649,-116.075738   (Carretera Ensenada-San Felipe, Rancho los Man...
... ... ...
242 32.406411,-115.194782   (Carretera Mexicali-Estación Coahuila, Ejido N...
243 32.31572,-115.189541    (Carretera Mexicali-Estación Coahuila, Municip...
244 32.243859,-115.167499   (Carretera Mexicali-Estación Coahuila, Las Pal...
245 32.215375,-115.167491   (Carretera Mexicali-Estación Coahuila, Colonia...
246 32.184844,-115.013685   (Carretera Mexicali-Estación Coahuila, Colonia...

i want this code to get only the state (all the locations I will get in my codes are in Mexico)

i found a way to obtain the state from individual locations but not for dataframes.

geolocator.reverse(BC['coord'][0]).raw['address']['state']

gives:

'Baja California'

pls help, im not really familiar with apply

Answer 1

Solved!

import pandas as pd
import geopy as gp

geolocator = gp.geocoders.Nominatim(user_agent="Fortune")

AGS = pd.read_excel("MEXSCTv2.xlsx",sheet_name='AGS')

AGS['coord']=AGS['LATITUD'].astype("string")+","+AGS['LONGITUD'].astype("string")
AGS['location'] = AGS['coord'].apply(geolocator.reverse)
for x in AGS.index:
    AGS['location'][x]=AGS['location'][x].raw['address']['state']

AGS[['coord','location']]

though there are some coordinates that dont have a state, but i hope it will be usefull for someone

coord   location
0   22.146725,-102.289321   Aguascalientes
1   22.146632,-102.289177   Aguascalientes
2   22.146797,-102.28931    Aguascalientes
3   22.146887,-102.289308   Aguascalientes
4   22.147222,-102.282276   Aguascalientes
... ... ...
243 22.226139,-102.1833     Aguascalientes
244 22.30369,-102.195026    Aguascalientes
245 22.150629,-102.246997   Aguascalientes
246 21.773008,-102.290597   Aguascalientes
247 21.814783,-102.353772   Aguascalientes