[SOLVED] Obtain State Name from Google Trends Interest by City

Obtain State Name from Google Trends Interest by City

Suppose you inquire the following:

gtrends("google", geo="US")$interest_by_city

This returns how many searches for the term "google" occurred across cities in the US. However, it does not provide any information regarding which state each city belongs to.

I have tried merging this data set with several others including city and state names. Given that the same city name can be present in many states, it is unclear to me how to identify which city was the one Google Trends provided data for.

I provide below a more detailed MWE.

library(gtrendsR)
library(USAboundariesData) 

data1 <- gtrends("google", geo= "US")$interest_by_city
data1$city <- data1$location
data2 <- us_cities(map_date = NULL)
data3 <- merge(data1, data2, by="city")

And this yields the following problem:

    city        state 
  Alexandria   Louisiana      
  Alexandria   Indiana       
  Alexandria   Kentucky        
  Alexandria   Virginia       
  Alexandria   Minnesota

making it difficult to know which "Alexandria" Google Trends provided the data for.

Any hints in how to identify the state of each city would be much appreciated.

Solution

One way around this is to collect the cities per state and then just rbind the respective data frames. You could first make a vector of state codes like so

states <- paste0("US-",state.abb)

I then just used purrr for its map and reduce functionality to create a single frame

    data <- purrr::reduce(purrr::map(states, function(x){
      cities = gtrends("google", geo = x)$interest_by_city
    }),
    rbind)