如何使用R编程批量替换列数据修正人口普查数据列偏移问题

核心逻辑 #

仅针对2010-2016年的记录，从最大编号的DP05列倒序向左赋值，避免正向赋值时提前覆盖源列数据：

• DP05_000N列的新值 = 同记录DP05_000(N-1)列的原值
• 2017-2019年的记录保持不变

代码实现 #

方法1：Base R 实现 #

# 先加载示例数据
census_data <- structure(list(GEO_ID = c("0100000US", "0100000US", "0100000US", 
"0100000US", "0100000US", "0100000US", "0100000US", "0100000US", 
"0100000US", "0100000US"), NAME = c("United States", "United States", 
"United States", "United States", "United States", "United States", 
"United States", "United States", "United States", "United States"
), DP05_0004E = c("20131420", "20170377", "20137884", "20052112", 
"19973711", "19912018", "19866960", "97", "97", "97"), DP05_0005E = c("20116654", 
"20207046", "20311310", "20409060", "20460355", "20501982", "20508363", 
"19853515", "19836850", "19767670"), DP05_0006E = c("20643730", 
"20631572", "20647280", "20672609", "20698883", "20679786", "20664537", 
"20445122", "20311494", "20157477"), DP05_0007E = c("22132691", 
"22083463", "21930781", "21715074", "21510534", "21354481", "21256545", 
"20713111", "20817419", "20927278"), DP05_0008E = c("21214118", 
"21463191", "21775439", "22099887", "22407472", "22604232", "22612610", 
"21219050", "21204226", "21208186"), DP05_0009E = c("40191013", 
"40668821", "41184290", "41711277", "42310182", "42881649", "43397907", 
"22501965", "22286970", "22015108"), DP05_0010E = c("42206141", 
"41683228", "41227505", "40874162", "40723040", "40651910", "40548400", 
"44044173", "44567976", "45030415"), DP05_0011E = c("44302697", 
"44579668", "44646979", "44506268", "44248186", "43895858", "43460466", 
"40656419", "40763210", "40978831"), DP05_0012E = c("18817728", 
"19215139", "19680816", "20165892", "20623001", "21001947", "21291513", 
"43091143", "42589573", "42072620"), DP05_0013E = c("15459667", 
"16292447", "16924986", "17479211", "17973759", "18415681", "18770229", 
"21523460", "21611374", "21654255"), DP05_0014E = c("20493467", 
"21152731", "22012061", "22957030", "23993984", "25135167", "26355308", 
"19224060", "19675357", "20102159"), Year = c("2010", "2011", 
"2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019"
)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))

# 提取所有DP05开头的列并按编号排序
dp_cols <- sort(grep("^DP05_\\d+E$", names(census_data), value = TRUE))
# 筛选需要调整的年份行
adjust_rows <- census_data$Year %in% 2010:2016

# 倒序赋值，避免覆盖源数据
for (i in length(dp_cols):2) {
  census_data[adjust_rows, dp_cols[i]] <- census_data[adjust_rows, dp_cols[i-1]]
}

# 可选：调整完成后删除不再需要的DP05_0004E列
# census_data$DP05_0004E <- NULL

方法2：dplyr 实现 #

library(dplyr)
library(stringr)

census_data <- census_data %>%
  # 仅对2010-2016年的记录执行调整
  mutate(across(matches("^DP05_\\d+E$"), ~if_else(
    Year %in% 2010:2016,
    # 提取当前列的编号，减1后取对应列的值
    get(paste0("DP05_", str_pad(as.integer(str_extract(cur_column(), "\\d+")) - 1, 4, pad = "0"), "E")),
    .x
  ), .names = "{.col}"))

验证效果 #

调整后可通过以下代码核对：

# 查看2010年调整前后的DP05_0004E和DP05_0005E值
census_data %>% 
  filter(Year == 2010) %>% 
  select(DP05_0004E, DP05_0005E, DP05_0006E)

可看到2010年的DP05_0005E值与原DP05_0004E值一致，DP05_0006E值与原DP05_0005E值一致，符合偏移调整要求。

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