如何在R中实现地址标准化：将单门牌号替换为对应门牌号范围

需求说明：按街道分组，将属于某门牌号范围（如10-12）的单个门牌号（如10、12）替换为对应的范围字符串，不同街道的门牌号不混淆。

示例数据 #

data <- data.frame(
  street = c('smith st','smith st','smith st','smith st','smith st','park ave','park ave','park ave','george lane','george lane'),
  blg_no = c('10','10-12','36','12','13-15','27','12','27-29','34-38','15')
)

方法1：使用tidyverse（dplyr + stringr） #

适合熟悉tidy语法的用户，代码可读性强：

library(dplyr)
library(stringr)

# 提取各街道的门牌号范围规则
range_rules <- data %>%
  filter(str_detect(blg_no, "-")) %>%
  separate(blg_no, into = c("start", "end"), sep = "-", convert = TRUE) %>%
  group_by(street) %>%
  mutate(range_str = paste(start, end, sep = "-"))

# 标准化门牌号
standardized_data <- data %>%
  mutate(row_id = row_number()) %>%  # 保留原行顺序
  left_join(range_rules, by = "street") %>%
  mutate(
    blg_no_num = as.numeric(blg_no),
    # 匹配单个门牌号对应的范围
    match_range = ifelse(
      !is.na(blg_no_num) & blg_no_num >= start & blg_no_num <= end,
      range_str,
      blg_no
    ),
    # 最终门牌号：范围号保留，单个号匹配后替换
    final_blg_no = ifelse(str_detect(blg_no, "-"), blg_no, match_range)
  ) %>%
  group_by(row_id) %>%
  mutate(final_blg_no = first(na.omit(final_blg_no))) %>%  # 取有效匹配结果
  ungroup() %>%
  select(street, blg_no = final_blg_no) %>%
  arrange(row_id) %>%
  select(-row_id)  # 移除行号列

# 查看结果
print(standardized_data)

方法2：使用data.table（高效处理大数据） #

适合处理大规模数据集，运行速度快：

library(data.table)

setDT(data)

# 提取各街道的门牌号范围规则
range_dt <- data[grepl("-", blg_no), 
                 .(start = as.integer(strsplit(blg_no, "-")[[1]][1]),
                   end = as.integer(strsplit(blg_no, "-")[[1]][2]),
                   range_str = blg_no), 
                 by = .(street, blg_no)]
range_dt <- range_dt[, .(start, end, range_str), by = street]

# 非等值连接匹配范围
data[, blg_no_num := as.numeric(blg_no)]
standardized_dt <- data[range_dt, 
                        on = .(street, blg_no_num >= start, blg_no_num <= end),
                        matched_range := i.range_str]

# 生成最终标准化门牌号
standardized_dt[, final_blg_no := ifelse(grepl("-", blg_no), blg_no, 
                                         ifelse(!is.na(matched_range), matched_range, blg_no))]
standardized_dt <- standardized_dt[, .(street, blg_no = final_blg_no)]

# 查看结果
print(standardized_dt)

方法3：基础R实现（无需额外包） #

不需要安装第三方包，适合纯基础R环境：

# 提取每个街道的门牌号范围
street_ranges <- lapply(split(data, data$street), function(df) {
  range_entries <- df$blg_no[grepl("-", df$blg_no)]
  if (length(range_entries) == 0) return(NULL)
  
  range_df <- do.call(rbind, strsplit(range_entries, "-")) %>%
    as.data.frame(stringsAsFactors = FALSE) %>%
    setNames(c("start", "end")) %>%
    mutate(
      start = as.integer(start),
      end = as.integer(end),
      range_str = range_entries
    )
  return(range_df)
})

# 逐个街道处理门牌号标准化
standardized_list <- lapply(names(street_ranges), function(street_name) {
  df <- data[data$street == street_name, ]
  ranges <- street_ranges[[street_name]]
  
  if (is.null(ranges)) return(df)
  
  df$blg_no_num <- as.numeric(df$blg_no)
  df$final_blg_no <- df$blg_no
  
  # 遍历每个范围，替换匹配的单个门牌号
  for (i in seq(nrow(ranges))) {
    match_mask <- !grepl("-", df$blg_no) & 
                  df$blg_no_num >= ranges$start[i] & 
                  df$blg_no_num <= ranges$end[i]
    df$final_blg_no[match_mask] <- ranges$range_str[i]
  }
  
  return(df[, c("street", "final_blg_no")] %>% setNames(c("street", "blg_no")))
})

# 合并结果并还原原顺序
standardized_data_base <- do.call(rbind, standardized_list)
standardized_data_base <- standardized_data_base[
  order(match(paste(standardized_data_base$street, standardized_data_base$blg_no), 
              paste(data$street, data$blg_no))), 
]

# 查看结果
print(standardized_data_base)

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