如何将人口普查区的FIPS Code转换为GISJOIN？

Excel 操作方法 #

FIPSCode 转 GISJOIN #

假设FIPSCode数据在A列（从A2开始），在B2单元格输入以下公式，下拉填充即可批量转换：

="G"&TEXT(LEFT(A2,1),"00")&TEXT(MID(A2,2,3),"0000")&TEXT(RIGHT(A2,6),"0000000")

• 逻辑说明：
  • LEFT(A2,1)提取州码，TEXT(..., "00")补零为两位（如1→01）
  • MID(A2,2,3)提取县码，TEXT(..., "0000")补零为四位（如073→0073）
  • RIGHT(A2,6)提取普查区码，TEXT(..., "0000000")补零为七位（如011803→0011803）
  • 拼接固定前缀"G"生成最终GISJOIN

GISJOIN 转 FIPSCode #

假设GISJOIN数据在A列（从A2开始），在B2单元格输入以下公式，下拉填充：

=VALUE(RIGHT(MID(A2,2,2),1)&RIGHT(MID(A2,4,4),3)&RIGHT(MID(A2,8,7),6))

• 逻辑说明：
  • MID(A2,2,2)取州码部分（01），RIGHT(...,1)得到原州码1
  • MID(A2,4,4)取县码部分（0073），RIGHT(...,3)得到原县码073
  • MID(A2,8,7)取普查区码部分（0011803），RIGHT(...,6)得到原普查区码011803
  • VALUE()将拼接后的文本转为数值型（如需保留文本格式可去掉该函数）

R 操作方法 #

FIPSCode 转 GISJOIN #

假设数据框为df，FIPSCode列名为fips，两种实现方式：

基础R写法

library(stringr)

# 拆分FIPS各部分并补零
df$state <- str_pad(substr(df$fips, 1, 1), width = 2, side = "left", pad = "0")
df$county <- str_pad(substr(df$fips, 2, 4), width = 4, side = "left", pad = "0")
df$tract <- str_pad(substr(df$fips, 5, 10), width = 7, side = "left", pad = "0")

# 拼接生成GISJOIN
df$gisjoin <- paste0("G", df$state, df$county, df$tract)

dplyr 写法

library(dplyr)
library(stringr)

df <- df %>%
  mutate(
    state = str_pad(substr(fips, 1, 1), 2, "left", "0"),
    county = str_pad(substr(fips, 2, 4), 4, "left", "0"),
    tract = str_pad(substr(fips, 5, 10), 7, "left", "0"),
    gisjoin = paste0("G", state, county, tract)
  )

GISJOIN 转 FIPSCode #

基于数据框df，GISJOIN列名为gisjoin：

基础R写法

# 提取各部分的原始编码
df$state <- substr(df$gisjoin, 3, 3)
df$county <- substr(df$gisjoin, 6, 8)
df$tract <- substr(df$gisjoin, 9, 14)

# 拼接生成FIPSCode
df$fips <- paste0(df$state, df$county, df$tract) %>% as.numeric()

dplyr 写法

library(dplyr)

df <- df %>%
  mutate(
    state = substr(gisjoin, 3, 3),
    county = substr(gisjoin, 6, 8),
    tract = substr(gisjoin, 9, 14),
    fips = paste0(state, county, tract) %>% as.numeric()
  )

• 逻辑说明：直接从GISJOIN字符串中提取对应位置的原始编码片段，拼接后转为数值型（保留文本格式可去掉as.numeric()）

内容的提问来源于stack exchange，提问作者Tathagato