R 语言：如何整理 2022 年县域统计年鉴：caj 文件转 pdf、文本识别与数据清洗

最近知网上更新了 2022 年中国县域统计年鉴，不过并没有提供 excel 格式的数据。所以我只下载了 caj 文件。今天的课程中我们将一起学习如何把 caj 文件转换成 pdf 文件、对 pdf 文件进行文本识别以及处理识别后的结果。

后面如果知网提供了 excel 文件就直接整理 excel 文件即可。不过本文讲解的方法依然适合很多没有提供 excel 文件的统计年鉴数据提取整理。

caj 文件转换成 pdf 文件

caj 文件需要使用知网提供的 caj 阅读器打开，我们没办法直接对 caj 文件进行文本识别和提取，因此我们需要先把 caj 文件转换成 pdf 或者其他可处理的格式。

对于页数较少的 caj 文件，可以通过一页页的截图，不过页数多的话这种方法就非常麻烦了。为此我找到了一个把 caj 转换成 pdf 文件的工具。

附件中我为大家下载好了三个文件：

1. caj2pdf-qt-macos-x86_64-v0.1.5.zip：Mac OS 安装
2. caj2pdf-qt-windows-x86-v0.1.5.zip：32 位的 Windows OS 安装
3. caj2pdf-qt-windows-x86_64-v0.1.5.zip：64 位 Windows OS 安装

Windows 上的安装使用我尚未测试，大家可以遇到问题的时候再来研究。

我使用的是 Mac OS，使用过程也遇到了问题：

双击打开：

不过我们可以这样打开。首先右键选择“显示包内容”：

找到这个文件：

双击打开即可：

然后使用这个工具转换 caj 文件生成 pdf 文件即可。

这样我们就得到了 pdf 文件：中国县域统计年鉴_王贵荣_主编_2021年县(市)社会经济主要指标.pdf。

pdf 文件识别文本、提取表格数据

图像的文本识别工具很多，例如 tesseract，不过尝试之后发现都不是特别好用，最好用的工具还是这个在线的应用。

如果要识别的文件页数较少，选择免费转换即可。付费转换的价格也不贵，例如这份 pdf 文件有 419 页，收费 17.67 元（如果要识别很多文件，最好先把文件合并成一个 pdf 文件上传识别，因为这个网站是阶梯收费，页数越多，单价越低）。

上传之后根据自己的需要设定解析参数即可：

解析过程可能需要数十分钟，耐心等待即可。

这次大家就不用再付费转换了，附件中有我转换后的数据，大家可以使用附件中的结果继续学习后面的内容。也就是 中国县域统计年鉴_王贵荣_主编_2021年县(市)社会经济主要指标_output.xlsx 文件。

手动检查

解析的结果并不一定能直接使用 R 语言处理（有很多细节不好编程进行），我们需要先手动检查下做一些准备工作。

这里主要是手动从每个表格中提取省份信息生成一个新列，见文件“整理结果.xlsx”。

在 R 语言中处理

下面我们使用 R 语言读取处理这个文件：

library(tidyverse)
library(readxl)
readxl::read_xlsx("整理结果.xlsx", col_names = LETTERS[1:12]) -> df
df %>%
  # 填补省份
  fill(A) %>%
  # 去除无效的行
  dplyr::filter(!(is.na(D) & is.na(E) & is.na(F)
                  & is.na(G) & is.na(H) & is.na(I)
                  & is.na(J) & is.na(K) & is.na(L))) %>%
  # 去除 B 中的空格
  mutate(B = str_remove_all(B, " ")) -> df1

# 统一 B 中的指标名称
df1 %>%
  mutate(B = str_remove_all(B, ","),
         B = str_remove_all(B, "-"),
         B = str_remove_all(B, "\\."),
         B = str_remove_all(B, "~"),
         B = str_remove_all(B, "・"),
         B = str_remove_all(B, "，"),
         B = str_remove_all(B, "、")) %>%
  distinct(B) %>%
  arrange(B) %>%
  dplyr::filter(!is.na(B) & B != "一、基本情况行政区域面积" & B != "一基本情况行政区域面积") %>%
  mutate(B = if_else(B %in% c("提供住宿的民政服务机构床位数",
                              "提供住宿的民谢艮务机构床位数",
                              "提供住宿的民政0艮务机构床位数"),
                     "提供住宿的民政服务机构床位数", B),
         B = if_else(B %in% c("提供住宿的民呦艮务机构",
                              "提供住宿的民政^务机构",
                              "提供住宿的民斑艮务机构",
                              "提供住宿的民班艮务机构"),
                     "提供住宿的民政服务机构", B)) %>%
  distinct(B)
#> # A tibble: 24 × 1
#>    B
#>    <chr>
#>  1 乡
#>  2 住户存款余额
#>  3 医疗卫生机构床位
#>  4 固定电话用户
#>  5 地区生产总值
#>  6 地方一般公共预算支出
#>  7 地方一般公共预算收入
#>  8 小学在校学生
#>  9 年末金融机构各项贷款余额
#> 10 户籍人口
#> # ℹ 14 more rows

# 这样就没问题了

使用该代码处理原始数据：

df1 %>%
  mutate(B = str_remove_all(B, ","),
         B = str_remove_all(B, "-"),
         B = str_remove_all(B, "\\."),
         B = str_remove_all(B, "~"),
         B = str_remove_all(B, "・"),
         B = str_remove_all(B, "，"),
         B = str_remove_all(B, "、")) %>%
  dplyr::filter(!is.na(B) & B != "一、基本情况行政区域面积" & B != "一基本情况行政区域面积") %>%
  mutate(B = if_else(B %in% c("提供住宿的民政服务机构床位数",
                              "提供住宿的民谢艮务机构床位数",
                              "提供住宿的民政0艮务机构床位数"),
                     "提供住宿的民政服务机构床位数", B),
         B = if_else(B %in% c("提供住宿的民呦艮务机构",
                              "提供住宿的民政^务机构",
                              "提供住宿的民斑艮务机构",
                              "提供住宿的民班艮务机构"),
                     "提供住宿的民政服务机构", B)) -> df2

根据 B == “指标” 拆分每个表：

df2 %>%
  mutate(z = if_else(B == "指标", row.names(.), "")) %>%
  mutate(z = as.numeric(z)) %>%
  fill(z) -> df2

检查有没有识别错误的：

df2 %>%
  count(z) %>%
  arrange(desc(n))
#> # A tibble: 418 × 2
#>        z     n
#>    <dbl> <int>
#>  1  6161    43
#>  2     1    24
#>  3    25    24
#>  4    49    24
#>  5   119    24
#>  6   143    24
#>  7   213    24
#>  8   237    24
#>  9   284    24
#> 10   308    24
#> # ℹ 408 more rows

# 可以看到 n 的最大数量是 43，说明有识别错误的，修正下

df2 %>%
  dplyr::filter(z == 6161)
#> # A tibble: 43 × 13
#>    A     B     C     D     E     F     G     H     I     J     K     L         z
#>    <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <lgl> <lgl> <lgl> <dbl>
#>  1 广西… 指标  单位  宁明… 龙州… 大新… 天等… 凭祥… <NA>  NA    NA    NA     6161
#>  2 广西… 行政… 平方… 3704  2311  2748  2165  645   <NA>  NA    NA    NA     6161
#>  3 广西… 乡    个    6     7     9     7     <NA>  <NA>  NA    NA    NA     6161
#>  4 广西… 镇    个    7     5     5     6     4     <NA>  NA    NA    NA     6161
#>  5 广西… 户籍… 万人  44.3  27.4  38.3  45.5  11.7  <NA>  NA    NA    NA     6161
#>  6 广西… 地区… 万元  1183… 1041… 1193… 8702… 8455… <NA>  NA    NA    NA     6161
#>  7 广西… 第一… 万元  3478… 3000… 2797… 1750… 74243 <NA>  NA    NA    NA     6161
#>  8 广西… 第二… 万元  3378… 2422… 4391… 2285… 2731… <NA>  NA    NA    NA     6161
#>  9 广西… 第三… 万元  4982… 4994… 4749… 4667… 4981… <NA>  NA    NA    NA     6161
#> 10 广西… 地方… 万元  30186 29986 32699 22977 37648 <NA>  NA    NA    NA     6161
#> # ℹ 33 more rows

可以看到有部分海南省的数据少了“指标”这一行，我们再回到原始数据修正这一行数据得到“整理结果2.xlsx”

重新处理 “整理结果2.xlsx”：

readxl::read_xlsx("整理结果2.xlsx", col_names = LETTERS[1:12]) -> df
df %>%
  fill(A) %>%
  dplyr::filter(!(is.na(D) & is.na(E) & is.na(F)
                  & is.na(G) & is.na(H) & is.na(I)
                  & is.na(J) & is.na(K) & is.na(L))) %>%
  mutate(B = str_remove_all(B, " ")) %>%
  mutate(B = str_remove_all(B, ","),
         B = str_remove_all(B, "-"),
         B = str_remove_all(B, "\\."),
         B = str_remove_all(B, "~"),
         B = str_remove_all(B, "・"),
         B = str_remove_all(B, "，"),
         B = str_remove_all(B, "、")) %>%
  dplyr::filter(!is.na(B) & B != "一、基本情况行政区域面积" & B != "一基本情况行政区域面积") %>%
  mutate(B = if_else(B %in% c("提供住宿的民政服务机构床位数",
                              "提供住宿的民谢艮务机构床位数",
                              "提供住宿的民政0艮务机构床位数"),
                     "提供住宿的民政服务机构床位数", B),
         B = if_else(B %in% c("提供住宿的民呦艮务机构",
                              "提供住宿的民政^务机构",
                              "提供住宿的民斑艮务机构",
                              "提供住宿的民班艮务机构"),
                     "提供住宿的民政服务机构", B)) %>%
  mutate(z = if_else(B == "指标", row.names(.), "")) %>%
  mutate(z = as.numeric(z)) %>%
  fill(z) -> df2
# 再次检查有没有识别错误的
df2 %>%
  count(z) %>%
  arrange(desc(n))
#> # A tibble: 419 × 2
#>        z     n
#>    <dbl> <int>
#>  1     1    24
#>  2    25    24
#>  3    49    24
#>  4   119    24
#>  5   143    24
#>  6   213    24
#>  7   237    24
#>  8   284    24
#>  9   308    24
#> 10   332    24
#> # ℹ 409 more rows

继续整理 df2：

df2 %>%
  select(-C) %>%
  gather(D:L, key = "variable", value = "value")  %>%
  spread(B, value) %>%
  select(z, A, 指标, everything()) %>%
  # 删除不是数据的
  dplyr::filter(!is.na(指标)) %>%
  select(-variable) %>%
  # 使用 type_convert() 把能转换成数值变量的都转换成数值变量
  type_convert() -> df3

字符串变量有这些：

df3 %>%
  select_if(is.character) %>%
  colnames(.)
#>  [1] "A"                        "指标"
#>  [3] "地区生产总值"             "固定电话用户"
#>  [5] "街道办事处"               "年末金融机构各项贷款余额"
#>  [7] "设施农业种植占地面积"     "提供住宿的民政服务机构"
#>  [9] "乡"                       "住户存款余额"

不过还需要注意，type_convert() 在处理的时候，会自动忽略逗号（认为是会计数字分隔符），但是文本识别的时候是经常会把小数点识别成逗号。所以我还需要检查下 df2 的变量中哪些变量有逗号：

df2 %>%
  select(-C) %>%
  gather(D:L, key = "variable", value = "value") %>%
  dplyr::filter(str_detect(value, ","))
#> # A tibble: 5 × 5
#>   A                B                        z variable value
#>   <chr>            <chr>                <dbl> <chr>    <chr>
#> 1 内蒙古自治区     户籍人口              1188 D        46,8
#> 2 新疆维吾尔自治区 固定电话用户          9438 E        ,     21511
#> 3 内蒙古自治区     户籍人口              1302 H        39,7
#> 4 陕西省           设施农业种植占地面积  8296 H        ,76
#> 5 新疆维吾尔自治区 乡                    9414 H        ,10

所以除了上面的那些变量，我们还需要检查“户籍人口”变量！

除了 “A” 和“指标”两个变量，其他变量都应该是数值变量，现在我们需要逐一检查为什么出现了这个问题。

把“整理结果2.xlsx”复制一份命名为“整理结果3.xlsx”，然后逐个检查为什么有些变量无法 type_convert()：

df3 %>%
  mutate(乡2 = as.numeric(乡)) %>%
  dplyr::filter(is.na(乡2) & !is.na(乡)) %>%
  select(A, 指标, 乡, 乡2)
#> # A tibble: 1 × 4
#>   A                指标     乡      乡2
#>   <chr>            <chr>    <chr> <dbl>
#> 1 新疆维吾尔自治区 英吉沙县 ,10      NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(住户存款余额2 = as.numeric(住户存款余额)) %>%
  dplyr::filter(is.na(住户存款余额2) & !is.na(住户存款余额)) %>%
  select(A, 指标, 住户存款余额, 住户存款余额2)
#> # A tibble: 1 × 4
#>   A      指标   住户存款余额 住户存款余额2
#>   <chr>  <chr>  <chr>                <dbl>
#> 1 河北省 滦州市 386力 82                NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(固定电话用户2 = as.numeric(固定电话用户)) %>%
  dplyr::filter(is.na(固定电话用户2) & !is.na(固定电话用户)) %>%
  select(A, 指标, 固定电话用户, 固定电话用户2)
#> # A tibble: 1 × 4
#>   A                指标   固定电话用户 固定电话用户2
#>   <chr>            <chr>  <chr>                <dbl>
#> 1 新疆维吾尔自治区 莎车县 ,     21511             NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(地区生产总值2 = as.numeric(地区生产总值)) %>%
  dplyr::filter(is.na(地区生产总值2) & !is.na(地区生产总值)) %>%
  select(A, 指标, 地区生产总值, 地区生产总值2)
#> # A tibble: 1 × 4
#>   A      指标   地区生产总值 地区生产总值2
#>   <chr>  <chr>  <chr>                <dbl>
#> 1 四川省 荥经县 82 乃 33                NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(年末金融机构各项贷款余额2 = as.numeric(年末金融机构各项贷款余额)) %>%
  dplyr::filter(is.na(年末金融机构各项贷款余额2) & !is.na(年末金融机构各项贷款余额)) %>%
  select(A, 指标, 年末金融机构各项贷款余额, 年末金融机构各项贷款余额2)
#> # A tibble: 2 × 4
#>   A      指标   年末金融机构各项贷款余额 年末金融机构各项贷款余额2
#>   <chr>  <chr>  <chr>                                        <dbl>
#> 1 湖南省 衡东县 15277万                                         NA
#> 2 陕西省 洛川县 65 乃 29                                        NA
# 在“整理结果3.xlsx”中改正这个错误。

# 另外户籍人口变量里面有 “46,8”、“39,7” 需要手动改正。

df3 %>%
  mutate(提供住宿的民政服务机构2 = as.numeric(提供住宿的民政服务机构)) %>%
  dplyr::filter(is.na(提供住宿的民政服务机构2) & !is.na(提供住宿的民政服务机构)) %>%
  select(A, 指标, 提供住宿的民政服务机构, 提供住宿的民政服务机构2)
#> # A tibble: 1 × 4
#>   A      指标   提供住宿的民政服务机构 提供住宿的民政服务机构2
#>   <chr>  <chr>  <chr>                                    <dbl>
#> 1 四川省 金川县 I                                           NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(街道办事处2 = as.numeric(街道办事处)) %>%
  dplyr::filter(is.na(街道办事处2) & !is.na(街道办事处)) %>%
  select(A, 指标, 街道办事处, 街道办事处2)
#> # A tibble: 1 × 4
#>   A      指标   街道办事处 街道办事处2
#>   <chr>  <chr>  <chr>            <dbl>
#> 1 福建省 松溪县 I                   NA
# 在“整理结果3.xlsx”中改正这个错误。

df3 %>%
  mutate(设施农业种植占地面积2 = as.numeric(设施农业种植占地面积)) %>%
  dplyr::filter(is.na(设施农业种植占地面积2) & !is.na(设施农业种植占地面积)) %>%
  select(A, 指标, 设施农业种植占地面积, 设施农业种植占地面积2)
#> # A tibble: 1 × 4
#>   A      指标   设施农业种植占地面积 设施农业种植占地面积2
#>   <chr>  <chr>  <chr>                                <dbl>
#> 1 陕西省 麟游县 ,76                                     NA

# 在“整理结果3.xlsx”中改正这个错误。

# 另外户籍人口变量里面有 “46,8”、“39,7” 需要手动改正。

这样我们就纠正了这些错误。

重新处理 “整理结果3.xlsx”：

readxl::read_xlsx("整理结果3.xlsx", col_names = LETTERS[1:12]) -> df
df %>%
  fill(A) %>%
  dplyr::filter(!(is.na(D) & is.na(E) & is.na(F)
                  & is.na(G) & is.na(H) & is.na(I)
                  & is.na(J) & is.na(K) & is.na(L))) %>%
  mutate(B = str_remove_all(B, " ")) %>%
  mutate(B = str_remove_all(B, ","),
         B = str_remove_all(B, "-"),
         B = str_remove_all(B, "\\."),
         B = str_remove_all(B, "~"),
         B = str_remove_all(B, "・"),
         B = str_remove_all(B, "，"),
         B = str_remove_all(B, "、")) %>%
  dplyr::filter(!is.na(B) & B != "一、基本情况行政区域面积" & B != "一基本情况行政区域面积") %>%
  mutate(B = if_else(B %in% c("提供住宿的民政服务机构床位数",
                              "提供住宿的民谢艮务机构床位数",
                              "提供住宿的民政0艮务机构床位数"),
                     "提供住宿的民政服务机构床位数", B),
         B = if_else(B %in% c("提供住宿的民呦艮务机构",
                              "提供住宿的民政^务机构",
                              "提供住宿的民斑艮务机构",
                              "提供住宿的民班艮务机构"),
                     "提供住宿的民政服务机构", B)) %>%
  mutate(z = if_else(B == "指标", row.names(.), "")) %>%
  mutate(z = as.numeric(z)) %>%
  fill(z) %>%
  select(-C) %>%
  gather(D:L, key = "variable", value = "value")  %>%
  spread(B, value) %>%
  select(z, A, 指标, everything()) %>%
  dplyr::filter(!is.na(指标)) %>%
  select(-variable) %>%
  type_convert() %>%
  rename(省 = A, 县 = 指标) %>%
  select(省, 县, 行政区域面积, 乡, 镇, 街道办事处, 户籍人口,
           地区生产总值, 第一产业增加值, 第二产业增加值, 第三产业增加值,
           地方一般公共预算收入, 地方一般公共预算支出, 住户存款余额,
           年末金融机构各项贷款余额, 设施农业种植占地面积, 油料产量,
           棉花产量, 规模以上工业企业, 固定电话用户, 普通中学在校学生,
           小学在校学生, 医疗卫生机构床位, 提供住宿的民政服务机构,
           提供住宿的民政服务机构床位数) -> df4

最后我们再检查变量的数值中是否有识别错误的，通常我会检查数据的分布：

hist(df4$乡)
# 其他变量类似

也可以把所有的变量分布绘制到一幅图上：

# 或者一起绘制
df4 %>% gather(-c("省", "县"), key = "variable", value = "value") %>%
  ggplot(aes(x = value)) +
  geom_histogram(fill = "#709ae1", color = "black", linewidth = 0.2) +
  geom_freqpoly(color = "black", linewidth = 0.2) +
  facet_wrap(~variable, nrow = 5, scales = "free") +
  theme_minimal(base_family = cnfont) +
  theme(panel.grid.minor = element_blank(),
        panel.grid.major = element_line(linewidth = 0.2,
                                        linetype = "dotdash"),
        plot.background = element_rect(fill = "white", color = "white"),
        strip.background = element_rect(fill = "gray", linewidth = 0.1),
        strip.text = element_text(color = "black", size = 6),
        axis.text = element_text(size = 4)) +
  labs(x = "", y = "") -> p

ggsave("pica.png", width = 20, height = 15, units = "cm", device = png)

看起来基本没什么明显的问题，大家如果时间充裕的话，也可以逐个检查那些分布右偏的变量。

检查区县名称的识别错误

虽然在人工检查过程中我们也纠正了一些识别错误问题，不过应该还有很多我们没有发现，在下次课中我们会讲解如何为数据添加行政区划代码，在这个过程中我们就能发现和纠正区县名称识别错误的问题了。另外如果把多年的数据匹配起来，就可以通过检查数据的趋势来识别出数值错误的问题。

点击这里跳转到 RStata 短书平台获取附件：R 语言：如何整理 2022 年县域统计年鉴：caj 文件转 pdf、文本识别与数据清洗