我遇到了将县分配给某些城市地方的问题。通过acs包查询时
> geo.lookup(state = "NY", place = "New York")
state state.name county.name place place.name
1 36 New York <NA> NA <NA>
2 36 New York Bronx County, Kings County, New York County, Queens County, Richmond County 51000 New York city
3 36 New York Oneida County 51011 New York Mills village
，例如，您可以看到“纽约”有很多县。洛杉矶，波特兰，俄克拉荷马州，哥伦布等也是如此。如何将这些数据分配给“县”？
当前使用以下代码将“ county.name”与相应的县FIPS代码进行匹配。不幸的是，它仅适用于查询中仅输出一个县名的情况。
dat <- c("New York, NY","Boston, MA","Los Angeles, CA","Dallas, TX","Palo Alto, CA")
dat <- strsplit(dat, ",")
dat
library(tigris)
library(acs)
data(fips_codes) # FIPS codes with state, code, county information
GeoLookup <- lapply(dat,function(x) {
geo.lookup(state = trimws(x[2]), place = trimws(x[1]))[2,]
})
df <- bind_rows(GeoLookup)
#Rename cols to match
colnames(fips_codes) = c("state.abb", "statefips", "state.name", "countyfips", "county.name")
# Here is a problem, because it works with one item in "county.name" but not more than one (see output below).
df <- df %>% left_join(fips_codes, by = c("state.name", "county.name"))
df
返回值：
state state.name county.name place place.name state.abb statefips countyfips
1 36 New York Bronx County, Kings County, New York County, Queens County, Richmond County 51000 New York city <NA> <NA> <NA>
2 25 Massachusetts Suffolk County 7000 Boston city MA 25 025
3 6 California Los Angeles County 20802 East Los Angeles CDP CA 06 037
4 48 Texas Collin County, Dallas County, Denton County, Kaufman County, Rockwall County 19000 Dallas city <NA> <NA> <NA>
5 6 California San Mateo County 20956 East Palo Alto city CA 06 081
为了保留数据，最好将left_join匹配为“查找county.name包含的内容place.name（名称中不包含xy城市），或者默认情况下选择第一项。很高兴看到如何做到这一点。
总的来说：我认为，没有比这种方法更好的方法了吗？
谢谢你的帮助！
像下面的代码那样创建一个“长”数据帧以进行连接的东西呢？我们使用tidyverse管道运算符来链接操作。strsplit返回一个列表，我们unnest堆栈列表中的值（即用每一个组合去县城的名字state.name和place.name）为长数据帧，其中每个county.name现在都有自己的行。
library(tigris)
library(acs)
library(tidyverse)
dat = geo.lookup(state = "NY", place = "New York")
state state.name county.name place place.name 1 36 New York <NA> NA <NA> 2 36 New York Bronx County, Kings County, New York County, Queens County, Richmond County 51000 New York city 3 36 New York Oneida County 51011 New York Mills village
dat = dat %>%
group_by(state.name, place.name) %>%
mutate(county.name = strsplit(county.name, ", ")) %>%
unnest
state state.name place place.name county.name <chr> <chr> <int> <chr> <chr> 1 36 New York NA <NA> <NA> 2 36 New York 51000 New York city Bronx County 3 36 New York 51000 New York city Kings County 4 36 New York 51000 New York city New York County 5 36 New York 51000 New York city Queens County 6 36 New York 51000 New York city Richmond County 7 36 New York 51011 New York Mills village Oneida County
更新：关于您评论中的第二个问题，假设您已经有了都会区的向量，该如何处理：
dat <- c("New York, NY","Boston, MA","Los Angeles, CA","Dallas, TX","Palo Alto, CA")
df <- map_df(strsplit(dat, ", "), function(x) {
geo.lookup(state = x[2], place = x[1])[-1, ] %>%
group_by(state.name, place.name) %>%
mutate(county.name = strsplit(county.name, ", ")) %>%
unnest
})
df
state state.name place place.name county.name 1 36 New York 51000 New York city Bronx County 2 36 New York 51000 New York city Kings County 3 36 New York 51000 New York city New York County 4 36 New York 51000 New York city Queens County 5 36 New York 51000 New York city Richmond County 6 36 New York 51011 New York Mills village Oneida County 7 25 Massachusetts 7000 Boston city Suffolk County 8 25 Massachusetts 7000 Boston city Suffolk County 9 6 California 20802 East Los Angeles CDP Los Angeles County 10 6 California 39612 Lake Los Angeles CDP Los Angeles County 11 6 California 44000 Los Angeles city Los Angeles County 12 48 Texas 19000 Dallas city Collin County 13 48 Texas 19000 Dallas city Dallas County 14 48 Texas 19000 Dallas city Denton County 15 48 Texas 19000 Dallas city Kaufman County 16 48 Texas 19000 Dallas city Rockwall County 17 48 Texas 40516 Lake Dallas city Denton County 18 6 California 20956 East Palo Alto city San Mateo County 19 6 California 55282 Palo Alto city Santa Clara County
更新2：如果我理解您的评论，对于具有多个县的城市（实际上是示例中的地名），我们只希望包含与城市名称相同的县（例如，对于纽约州，为新县）约克市），否则列表中的第一个县。以下代码选择与城市同名的县，或者如果没有，则选择该城市的第一个县。您可能需要进行一些调整才能使其在整个美国都可以使用。例如，要在路易斯安那州使用它，可能需要gsub(" County| Parish"...而不是gsub(" County"...。
map_df(strsplit(dat, ", "), function(x) {
geo.lookup(state = x[2], place = x[1])[-1, ] %>%
group_by(state.name, place.name) %>%
mutate(county.name = strsplit(county.name, ", ")) %>%
unnest %>%
slice(max(1, which(grepl(sub(" [A-Za-z]*$","", place.name), gsub(" County", "", county.name))), na.rm=TRUE))
})
state state.name place place.name county.name <chr> <chr> <int> <chr> <chr> 1 36 New York 51000 New York city New York County 2 36 New York 51011 New York Mills village Oneida County 3 25 Massachusetts 7000 Boston city Suffolk County 4 6 California 20802 East Los Angeles CDP Los Angeles County 5 6 California 39612 Lake Los Angeles CDP Los Angeles County 6 6 California 44000 Los Angeles city Los Angeles County 7 48 Texas 19000 Dallas city Dallas County 8 48 Texas 40516 Lake Dallas city Denton County 9 6 California 20956 East Palo Alto city San Mateo County 10 6 California 55282 Palo Alto city Santa Clara County
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句