如何将巴基斯坦人口普查非规则PDF表格转为R的tidy dataframe？

我完全懂你现在的困境——从非规则的PDF层级文本里提取出规范的tidy dataframe确实需要点技巧，尤其是要把行政区层级和最细粒度的普查区块数据对应起来。咱们一步步来解决这个问题：

1. 先理清数据的层级逻辑 #

看你提供的文本行示例，每一行的缩进空格数和关键词后缀（比如DISTRICT/TEHSIL）其实已经明确了层级关系：

• 无缩进：District级（比如ABBOTTABAD DISTRICT）
• 1个空格开头：Tehsil级（比如ABBOTTABAD TEHSIL）
• 1个空格开头+含CANTONMENT：Tehsil下的子层级（比如ABBOTTABAD CANTONMENT）
• 2个空格开头：Charge级（CHARGE NO 01）
• 3个空格开头：Circle级（CIRCLE NO 01）
• 4个空格开头：8位数字的普查区块（023010101）

同时每一行末尾都固定跟着两个数值：人口（population）和户数（household），我们要先把每一行的「名称/层级标识」和「数值」拆分开。

2. 优化正则提取逻辑 #

你之前的正则不够精准，我们换两个更明确的正则来拆分内容：

• 提取名称：匹配开头到最后两个数值之前的部分（自动去掉开头空格和末尾的数值）
• 提取人口和户数：分别匹配行末的两个带逗号的数值

用stringr和tibble来实现：

library(stringr)
library(dplyr)
library(tidyr)
library(purrr)

# 先把清理后的数据转成tibble，方便处理
data_df <- tibble(raw_line = data) %>%
  mutate(
    # 计算每行开头的空格数，辅助判断层级
    indent = str_count(raw_line, "^\\s+"),
    # 提取名称部分：去掉开头空格和末尾的数值
    segment_name = str_trim(str_extract(raw_line, "^\\s*(.*?)\\s+(?=\\d[\\d,]+\\s+\\d[\\d,]+$)")),
    # 提取人口（倒数第二个数值）
    population = str_extract(raw_line, "\\d[\\d,]+(?=\\s+\\d[\\d,]+$)"),
    # 提取户数（最后一个数值）
    household = str_extract(raw_line, "\\d[\\d,]+$"),
    # 标记每行的层级类型
    level = case_when(
      str_detect(segment_name, "DISTRICT$") ~ "district",
      str_detect(segment_name, "TEHSIL$") ~ "sub_lvl01",
      str_detect(segment_name, "CANTONMENT$") ~ "sub_lvl02",
      str_detect(segment_name, "CHARGE NO") ~ "sub_lvl03",
      str_detect(segment_name, "CIRCLE NO") ~ "sub_lvl04",
      str_detect(segment_name, "^\\d{8}$") ~ "census_block"
    )
  )

3. 填充层级，生成Tidy Dataframe #

接下来的关键是把上级行政区的信息「向下填充」到所有子层级行里，最后只保留普查区块的行（这是我们需要的最细粒度数据）：

tidy_census_data <- data_df %>%
  # 按缩进排序，确保上级层级在前面
  arrange(indent) %>%
  # 填充District信息到所有下行
  fill(district = ifelse(level == "district", segment_name, NA), .direction = "down") %>%
  # 填充sub_lvl01（Tehsil）信息
  fill(sub_lvl01 = ifelse(level == "sub_lvl01", segment_name, NA), .direction = "down") %>%
  # 填充sub_lvl02（Cantonment）信息
  fill(sub_lvl02 = ifelse(level == "sub_lvl02", segment_name, NA), .direction = "down") %>%
  # 填充sub_lvl03（Charge）信息
  fill(sub_lvl03 = ifelse(level == "sub_lvl03", segment_name, NA), .direction = "down") %>%
  # 填充sub_lvl04（Circle）信息
  fill(sub_lvl04 = ifelse(level == "sub_lvl04", segment_name, NA), .direction = "down") %>%
  # 只保留普查区块的行
  filter(level == "census_block") %>%
  # 整理列顺序，匹配你的期望格式
  select(
    district,
    sub_lvl01,
    sub_lvl02,
    sub_lvl03,
    sub_lvl04,
    census_block = segment_name,
    population,
    household
  )

运行这段代码后，你就能得到完全符合期望的tidy dataframe，比如你提供的示例行最终会输出：

4. 批量处理137份PDF的建议 #

既然你有137份PDF要处理，把上面的逻辑封装成函数，用purrr::map_dfr批量处理会高效很多：

# 封装处理单份PDF的函数
process_single_pdf <- function(pdf_url) {
  # 导入并清理PDF文本
  raw_pdf <- pdf_text(pdf_url)
  text_str <- toString(raw_pdf)
  text_lines <- read_lines(text_str)
  
  # 清理页眉页脚
  header_patterns <- c(
    "POPULATION AND HOUSEHOLD DETAIL FROM BLOCK TO DISTRICT LEVEL",
    "KHYBER PAKHTUNKHWA",
    "ADMIN UNIT"
  )
  header_rows <- unlist(lapply(header_patterns, function(p) grep(p, text_lines)))
  footer_rows <- grep("Page ", text_lines)
  cleaned_lines <- text_lines[-c(header_rows, footer_rows)]
  
  # 重复处理逻辑
  tibble(raw_line = cleaned_lines) %>%
    mutate(
      indent = str_count(raw_line, "^\\s+"),
      segment_name = str_trim(str_extract(raw_line, "^\\s*(.*?)\\s+(?=\\d[\\d,]+\\s+\\d[\\d,]+$)")),
      population = str_extract(raw_line, "\\d[\\d,]+(?=\\s+\\d[\\d,]+$)"),
      household = str_extract(raw_line, "\\d[\\d,]+$"),
      level = case_when(
        str_detect(segment_name, "DISTRICT$") ~ "district",
        str_detect(segment_name, "TEHSIL$") ~ "sub_lvl01",
        str_detect(segment_name, "CANTONMENT$") ~ "sub_lvl02",
        str_detect(segment_name, "CHARGE NO") ~ "sub_lvl03",
        str_detect(segment_name, "CIRCLE NO") ~ "sub_lvl04",
        str_detect(segment_name, "^\\d{8}$") ~ "census_block"
      )
    ) %>%
    arrange(indent) %>%
    fill(district = ifelse(level == "district", segment_name, NA), .direction = "down") %>%
    fill(sub_lvl01 = ifelse(level == "sub_lvl01", segment_name, NA), .direction = "down") %>%
    fill(sub_lvl02 = ifelse(level == "sub_lvl02", segment_name, NA), .direction = "down") %>%
    fill(sub_lvl03 = ifelse(level == "sub_lvl03", segment_name, NA), .direction = "down") %>%
    fill(sub_lvl04 = ifelse(level == "sub_lvl04", segment_name, NA), .direction = "down") %>%
    filter(level == "census_block") %>%
    select(
      district,
      sub_lvl01,
      sub_lvl02,
      sub_lvl03,
      sub_lvl04,
      census_block = segment_name,
      population,
      household
    )
}

# 假设你有所有PDF的URL列表
pdf_url_list <- c(
  "http://www.pbscensus.gov.pk/sites/default/files/bwpsr/kp/ABBOTTABAD_BLOCKWISE.pdf",
  # 添加其他136个PDF的URL...
)

# 批量处理所有PDF，合并成一个大的dataframe
all_census_data <- map_dfr(pdf_url_list, process_single_pdf)

如果遇到个别PDF的格式有细微差异（比如缩进数不同），只需要调整level里的判断条件或者indent的逻辑就行。

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