I have a dataset of around 10000 rows. I have the Address, City, State and Zipcode values. I do not have lat/long coordinates. I would like to retrieve the county name without taking a large amount of time. I have tried library(tinygeocoder) but it takes around 14 seconds for 100 values, and is giving a 'time-out' error when I put in the entire dataset. Plus, it's outputting a fip code, which I have to join to get the actual county name. Reproducible example:

library(tidygeocoder)
library(dplyr)

df <-   tidygeocoder::louisville[,1:4]
county_fips <- data.frame (fips = c("111", "112"),
                           county = c("Jefferson", "Montgomery"))

geocoded <- df %>% geocode(street = street, city = city, state = state,
                           method = 'census', full_results = TRUE,
                           api_options = list(census_return_type = 'geographies'))

df$fips <- geocoded$county_fips
df_new <- merge(x=df, y=county_fips, by="fips", all.x = T)

CodePudding user response：

You can use a public dataset that links city and/or zipcode to county. I found these websites with such data:

• https://www.unitedstateszipcodes.org/zip-code-database
• https://simplemaps.com/data/us-cities

You can then do a left join on the linking column (presumably city or zipcode but will depend on the dataset):

df = merge(x=df, y=public_dataset, by="City", all.x=T)

If performance is an issue, you can select just the county and linking columns from the public data set before you do the merge.

public_dataset = public_dataset %>% select(County, City)

CodePudding user response：

The slow performance is due to tinygeocoder's use of the the Census Bureau's API in order to match data. Asking the API to match thousands of addresses is the slow down, and I'm not aware of a different way to do this.

However, we can at least pare down the number of addresses that you are putting into the API. Maybe if we get that number low enough the code will run.

The ZIP Code Tabulation Areas (ZCTA) shows the relationships between ZIP Codes and county names (as well as FIPS). A "|" delimited file with a description of the data can be found on the Bureau's website.

Counting the number of times a ZIP code shows up tells us if a ZIP code spans multiple counties. If the frequency == 1, then you can freely translate the ZIP code to the county.

ZCTA <- read.delim("tab20_zcta520_county20_natl.txt", sep="|")
n_occur <- data.frame(table(ZCTA$GEOID_ZCTA5_20))
head(n_occur, 10)

In these results, addresses with ZIP codes 00611 and 00622 can be mapped to the corresponding counties without sending the addresses through the API. If your addresses are very urban, then you may be lucky in that the ZIP codes are small area-wise and may not span typically multiple counties.