r - 从不规则间隔的 pdf 中提取字符串到整洁的 R 数据帧中

Question

我正在努力自学一个过程，将不规则间隔的 PDF 表格转换为 R 中整洁的数据框。我的目标是从最近的巴基斯坦人口普查中提取人口数据，该数据目前分布在 137 个单独的 pdf 文件中. Here是一个示例目标文件。我已经能够将其他指南中的一些必要步骤拼凑起来，将 pdf 分解为文本字符串，但我对正则表达式感到困惑，我认为这些正则表达式是将文本进一步转换为数据框所必需的。

到目前为止我已经弄清楚的步骤:

# import file
district_import <- pdf_text("http://www.pbscensus.gov.pk/sites/default/files/bwpsr/kp/ABBOTTABAD_BLOCKWISE.pdf")

# convert text to string
data <- toString(district_import)

# convert text to character lines
data <- read_lines(data)

# clean up page headers and footers
header_row_1 <- grep("POPULATION AND HOUSEHOLD DETAIL FROM BLOCK TO DISTRICT LEVEL", data)
header_row_2 <- grep("KHYBER PAKHTUNKHWA", data)
header_row_3 <- grep("ADMIN UNIT", data)
footer_row <- grep("Page ", data)

data <- data[- c(header_row_1, header_row_2, header_row_3, footer_row)]

在这个阶段我可以产生以下内容:

> head(data, 15)
 [1] "ABBOTTABAD DISTRICT                                              1,332,912      216,534"
 [2] "     ABBOTTABAD TEHSIL                                             981,590      161,445"
 [3] "           ABBOTTABAD CANTONMENT                                   138,311        21183"
 [4] "                        CHARGE NO 01                              138,311         21183"
 [5] "                              CIRCLE NO 01                         12,150          1847"
 [6] "                                     023010101                      5,131           705"
 [7] "                                     023010102                      2,654           435"
 [8] "                                     023010103                      1,004           173"
 [9] "                                     023010104                      2,216           349"
[10] "                                     023010105                         94            14"
[11] "                                     023010106                      1,051           171"
[12] "                              CIRCLE NO 02                         15,383          2435"
[13] "                                     023010201                      1,352           211"
[14] "                                     023010202                      1,019           161"
[15] "                                     023010203                      4,079           691"

(请注意，虽然它通过此处的分界点出现，但前导空格的长度在整个文档中对于各个分区行政区划并不一致，我预计在 137 个区中也不会一致我最终的目标是循环并整合到一个全国范围的数据框架中。)

从这一点我想要的输出是按照以下几行将其转换为整洁的数据框，以人口普查 block (六位数代码，原始 pdf 中未按名称标识)作为基本组织单位:

             district         sub_lvl01             sub_lvl02    sub_lvl03    sub_lvl04 census_block population household
                <chr>             <chr>                 <chr>        <chr>        <chr>        <chr>      <chr>     <chr>
1 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010101      5,131       705
2 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010102      2,654       435
3 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010103      1,004       173
4 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010104      2,216       349
5 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010105         94        14
6 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01 CIRCLE NO 01    023010106      1,051       171
... etc 

我一直在尝试使用正则表达式，试图弄清楚如何提取它，但在这样做时相当迷茫，特别是考虑到变量之间缺少标准分隔符。

在 regex101.com 上玩了一下，我认为这段代码至少可以让我提取人口和家庭数字:

 pop_hh_str <- str_match_all(data, "(?!\\d{6})(?<=\\s)\\d*[,.]*\\d*[,.]*\\d*")

但这会创建一个很大的列表，其中仍然包含空格，我不清楚如何将其转换为类似于数据框的任何内容(或与其他行政区变量相匹配)。

任何关于如何考虑解决这个问题的指导将不胜感激!

Answer 1

数据

(因为我不想安装 pdftools，所以我手动重新创建了您的数据):

data <- c("ABBOTTABAD DISTRICT                                              1,332,912      216,534", "     ABBOTTABAD TEHSIL                                             981,590      161,445", "           ABBOTTABAD CANTONMENT                                   138,311        21183", "                        CHARGE NO 01                              138,311         21183", "                              CIRCLE NO 01                         12,150          1847", "                                     023010101                      5,131           705", "                                     023010102                      2,654           435", "                                     023010103                      1,004           173", "                                     023010104                      2,216           349", "                                     023010105                         94            14", "                                     023010106                      1,051           171", "                              CIRCLE NO 02                         15,383          2435", "                                     023010201                      1,352           211", "                                     023010202                      1,019           161", "                                     023010203                      4,079           691")
# data is now identical to what you showed as 15 lines of your `data`

处理中:按空格拆分字符串

通常，在这种情况下，可以这样做:

strsplit(data, "\\s+") # "\\s+" meaning: 1 or more white spaces

但在这种情况下，字符之间可以有 1 个空格，所以我们想要超过 1 个空格，因此 "\\s{2,}"(至少两个 ws)作为列的分隔符。其次，有时在数据之前和/或之后有前导/尾随空格。所以我们通过 trimws()

预先清理行的前导/尾随空格

因此:

strsplit(trimws(data), "\\s{2,}")

然后我们可以使用 Reduce()

逐行绑定(bind)这些值

df <- Reduce(rbind, strsplit(trimws(data), "\\s{2,}"))
rownames(df) <- 1:dim(df)[1] # just give at least numbers as rownames
df <- as.data.frame(df)

输出:

   [,1]                    [,2]        [,3]     
1  "ABBOTTABAD DISTRICT"   "1,332,912" "216,534"
2  "ABBOTTABAD TEHSIL"     "981,590"   "161,445"
3  "ABBOTTABAD CANTONMENT" "138,311"   "21183"  
4  "CHARGE NO 01"          "138,311"   "21183"  
5  "CIRCLE NO 01"          "12,150"    "1847"   
6  "023010101"             "5,131"     "705"    
7  "023010102"             "2,654"     "435"    
8  "023010103"             "1,004"     "173"    
9  "023010104"             "2,216"     "349"    
10 "023010105"             "94"        "14"     
11 "023010106"             "1,051"     "171"    
12 "CIRCLE NO 02"          "15,383"    "2435"   
13 "023010201"             "1,352"     "211"    
14 "023010202"             "1,019"     "161"    
15 "023010203"             "4,079"     "691" 

从这里开始，您将需要构建辅助列，这些辅助列具有在哪一行出现哪种类型的信息的计数器....这样的计数将帮助您将数据框拆分为子数据框。 split() 会很有用...

我编写了一些函数，这些函数可能有助于对 data vec 中一行的“级别”进行分类，方法是计算行的开头是否有超过 k 个空格。

not.more.than.k.leading.whitespaces <- function(s, k) {
  !grepl(paste0("^\\s{", k, ",}"), s)
}

leveler <- function(s, k) {
  cumsum(not.more.than.k.leading.whitespaces(s, k))
}

我会像这样使用它们:

df$level0 <- leveler(data, 0)
df$level1 <- leveler(data, 5)
df$level2 <- leveler(data, 11)
df$level3 <- leveler(data, 24)
df$level4 <- leveler(data, 37)

# important helper function:
annotate.by.first.row <- function(df, col, col.title) {
  # take first row's column content and add it to the df as a column content
  info <- df[1, col]
  rowsn <- dim(df)[1]
  df.new <- df[2:rowsn, ]
  df.new[, col.title] <- info
  df.new
}

# split data frame to a list of sub data frames
df.l0 <- split(df, df$level0)
# apply our helper function for annotation column generation
# using the information of the first row of the sub data frames
df.a0.l <- lapply(df.l0, annotate.by.first.row, 1, "district")

# cycle through: split, flatten, annotate.by.first.row
# to add next first row information as a column
df.s1.ll <- lapply(df.a0.l, function(df) split(df, df$level1))
df.s1.l <- unlist(df.s1.ll, recursive = FALSE)
df.a1.l <- lapply(df.s1.l, annotate.by.first.row, 1, "thesil")

# repeat the cycles ...
df.s2.ll <- lapply(df.a1.l, function(df) split(df, df$level2))
df.s2.l <- unlist(df.s2.ll, recursive = FALSE)
df.a2.l <- lapply(df.s2.l, annotate.by.first.row, 1, "cantonment")

df.s3.ll <- lapply(df.a2.l, function(df) split(df, df$level3))
df.s3.l <- unlist(df.s3.ll, recursive = FALSE)
df.a3.l <- lapply(df.s3.l, annotate.by.first.row, 1, "charge")

df.s4.ll <- lapply(df.a3.l, function(df) split(df, df$level4))
df.s4.l <- unlist(df.s4.ll, recursive = FALSE)
df.a4.l <- lapply(df.s4.l, annotate.by.first.row, 1, "circle")

# fuse subdata frames by `Reduce(rbind, ...)`
res.df <- Reduce(rbind, df.a4.l)
res.cleaned.df <- res.df[, c("district", "thesil", "cantonment", "charge", "circle", "V1", "V2", "V3")]

通过拆分、压平、按第一行注释这样的连续步骤，你可以到达你想要的地方。

> res.cleaned.df
#               district            thesil            cantonment       charge
# 6  ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 7  ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 8  ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 9  ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 10 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 11 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 13 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 14 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
# 15 ABBOTTABAD DISTRICT ABBOTTABAD TEHSIL ABBOTTABAD CANTONMENT CHARGE NO 01
#          circle        V1    V2  V3
# 6  CIRCLE NO 01 023010101 5,131 705
# 7  CIRCLE NO 01 023010102 2,654 435
# 8  CIRCLE NO 01 023010103 1,004 173
# 9  CIRCLE NO 01 023010104 2,216 349
# 10 CIRCLE NO 01 023010105    94  14
# 11 CIRCLE NO 01 023010106 1,051 171
# 13 CIRCLE NO 02 023010201 1,352 211
# 14 CIRCLE NO 02 023010202 1,019 161
# 15 CIRCLE NO 02 023010203 4,079 691

稍微紧凑和有规律地做:

# abstract over the split-flatten-annotate cycle/pattern by:
spl.fl.annotate <- function(df.a.l, col, col.name) {
  df.sN.ll <- lapply(df.a.l, function(df) split(df, df[, col]))
  df.sN.l  <- unlist(df.sN.ll, recursive = FALSE)
  lapply(df.sN.l, annotate.by.first.row, 1, col.name)
}

# now the cycles can be written as:
df.a0.l <- spl.fl.annotate(list(`0` = df), "level0", "district")
df.a1.l <- spl.fl.annotate(df.a0.l, "level1", "thesil")
df.a2.l <- spl.fl.annotate(df.a1.l, "level2", "cantonment")
df.a3.l <- spl.fl.annotate(df.a2.l, "level3", "charge")
df.a4.l <- spl.fl.annotate(df.a3.l, "level4", "circle")

# fuse subdata frames by `Reduce(rbind, ...)`
res.df <- Reduce(rbind, df.a4.l)
res.cleaned.df <- res.df[, c("district", "thesil", "cantonment", "charge", "circle", "V1", "V2", "V3")]