How can I transfer my working for loop over to pandas much quicker apply functionality?

Question

I have a dataframe with longitude and latitude columns. I need to get the county name for the location based on long and lat values with the help of the geoPy package.

 longitude  latitude  housing_median_age  total_rooms  total_bedrooms  \
0    -114.31     34.19                15.0       5612.0          1283.0   
1    -114.47     34.40                19.0       7650.0          1901.0   
2    -114.56     33.69                17.0        720.0           174.0   
3    -114.57     33.64                14.0       1501.0           337.0   
4    -114.57     33.57                20.0       1454.0           326.0   

   population  households  median_income  median_house_value  
0      1015.0       472.0         1.4936             66900.0  
1      1129.0       463.0         1.8200             80100.0  
2       333.0       117.0         1.6509             85700.0  
3       515.0       226.0         3.1917             73400.0  
4       624.0       262.0         1.9250             65500.0

I had success with a for loop:

geolocator = geopy.Nominatim(user_agent='1234')

for index, row in df.iloc[:10, :].iterrows():
    location = geolocator.reverse([row["latitude"], row["longitude"]])
    county = location.raw['address']['county']
    print(county)

The dataset has 17,000 rows, so that should be a problem, right?

So I've been trying to figure out how to build a function which I could use in pandas.apply() in order to get quicker results.

def get_zipcodes():
    location = geolocator.reverse([row["latitude"], row["longitude"]])
    county = location.raw['address']['county']
    print(county)

counties = get_zipcodes()

I'm stuck and don't know how to use apply (or any other clever method) in here. Help is much appreciated.

`apply` is a convenience method that is basically still a Python `for` loop under the hood. Pandas provides a great many vectorized operations that iterate in C, making them faster than iterating in Python. — CrazyChucky, May 25 '22 at 21:34

constantstranger · Accepted Answer · 2022-05-25T22:20:52.710

The pandas calculations in your code are unlikely to be the speed bottleneck when using geopy (see this answer to a different geopy question).

However, if there's a possibility that there may be a significant number of rows with duplicate latitude, longitude coordinates, you can use the @cache (or @lru_cache(None)) decorator from functools.

Here is how to use apply() on your dataframe with no special caching:

df["county"] = df.apply(lambda row: geolocator.reverse([row["latitude"], row["longitude"]]).raw["address"]["county"], axis=1)

Full test code:

import geopy
geolocator = geopy.Nominatim(user_agent='1234')
import pandas as pd
df = pd.DataFrame({
'longitude':[-114.31,-114.47,-114.56,-114.57,-114.57], 
'latitude':[34.19,34.40,33.69,33.64,33.57], 
'housing_median_age':[15]*5, 
'total_rooms':[1000]*5, 
'total_bedrooms':[500]*5, 
'population':[800]*5})

print(df)

df["county"] = df.apply(lambda row: geolocator.reverse([row["latitude"], row["longitude"]]).raw["address"]["county"], axis=1)
print(df)

Input:

   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  population
0    -114.31     34.19                  15         1000             500         800
1    -114.47     34.40                  15         1000             500         800
2    -114.56     33.69                  15         1000             500         800
3    -114.57     33.64                  15         1000             500         800
4    -114.57     33.57                  15         1000             500         800

Output:

   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  population                 county
0    -114.31     34.19                  15         1000             500         800  San Bernardino County
1    -114.47     34.40                  15         1000             500         800  San Bernardino County
2    -114.56     33.69                  15         1000             500         800       Riverside County
3    -114.57     33.64                  15         1000             500         800       Riverside County
4    -114.57     33.57                  15         1000             500         800       Riverside County

Here is how to use a decorator to cache results (i.e., to avoid going all the way out to a geopy server multiple times) for identical latitude, longitude coordinates:

from functools import cache
@cache
def bar(lat, long):
    return geolocator.reverse([lat, long]).raw["address"]["county"]

def foo(row):
    return bar(row["latitude"], row["longitude"])
df["county"] = df.apply(foo, axis=1)

Wow, what a beautiful and concise answer. Thank you so much! A quick question: how did you manage to copy/show the dataframe so nicely (in a code chuck I presume?). I — Dince-afk, May 25 '22 at 22:20
Please see my updated answer for a possible optimization. Regarding your question about copying/showing the dataframe, not sure I understand. For output, I just copied the results of `print` in my python code executed on the command line. — constantstranger, May 25 '22 at 22:23
Right, I figured it out, how to copy it out of PyCharm. Thanks! And thanks for the update, I will try it out and come back to you. — Dince-afk, May 25 '22 at 23:29
It does work, thanks! The cache is a great idea. There are around 800 unique long and lat values, instead of the 17,000 total rows! What I did additionally want to implement was a `print()` current row counter, that tells me how many rows are left as well as what the current one is. Adding ` print(row.index)` to `foo()` does not work. Can you help me with that as well? — Dince-afk, May 25 '22 at 23:48
You may want to look at tqdm, which would allow you to replace the `apply(foo)` line with this: `from tqdm import tqdm; tqdm.pandas(); df["county"] = df.progress_apply(foo, axis=1)`. It will show a progress meter indicating how far the `progress_apply()` call has gotten. — constantstranger, May 25 '22 at 23:56
Works like I charm! I have learned a lot from you. Thank you so much. — Dince-afk, May 26 '22 at 00:06
I got an error, I guess because it's too huge a bulk. Here it is: "GeocoderUnavailable: HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Max retries exceeded with url: /reverse?lat=37.95&lon=-121.27&format=json&addressdetails=1 (Caused by ReadTimeoutError("HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)"))" Is there any way to circumvent this? Like a timer that holds the execution and continues after a "safe" time? — Dince-afk, May 26 '22 at 08:53
I'm not a geopy expert, but here are some resources I found searching for "geopy timeout problem": [set timeout in options](https://geopy.readthedocs.io/en/stable/index.html#geopy.geocoders.options), [SO question about timeout](https://stackoverflow.com/questions/30218394/timeout-error-in-python-geopy-geocoder). Good luck — constantstranger, May 26 '22 at 11:22

How can I transfer my working for loop over to pandas much quicker apply functionality?

1 Answers1