r - PDF抓取: get company and subsidiaries tables

Question

我正在尝试抓取这个PDF包含有关公司子公司的信息。我看过很多使用 R 包 Tabulizer 的帖子，但不幸的是，由于某些原因，它在我的 Mac 上不起作用。由于 Tabulizer 使用 Java 依赖项，我尝试安装不同版本的 Java (6-13)，然后重新安装软件包，但仍然没有成功(当我运行 extract_tables R session 时会发生这种情况)中止)。

我需要从第 19 页开始抓取整个 pdf，并构建一个显示公司名称及其子公司的表格。在 pdf 中，名称以任何字母/数字/符号开头，而子公司以单点或双点开头。

所以我尝试使用 pdftools 和 pdftables 软件包。下面的代码提供了一个与第 19 页类似的表格:

library(pdftools)
library(pdftables)
library(tidyverse)

tt = pdf_text("~/DATA/978-1-912036-41-7-Who Owns Whom UK-Ireland-Volume-1.pdf")

df <- tt[19]
df2 <- strsplit(df, '  ')
 
df3 <-as.data.frame(do.call(cbind, df2)) %>% 
 filter(V1!="") %>% 
 mutate(V2=str_split_fixed(V1, "England . ", 2)) %>% 
 mutate(V3=str_split_fixed(V1, "England", 2)) %>% 
 select(V2,V3,V1) %>% 
 mutate(V1=ifelse(V1==V3,"",V1),V3=ifelse(V3==V2,"",V3)) %>% 
 select(V3,V2,V1) %>% 
 mutate_at(c("V1"), funs(lead), n = 1 ) %>% 
 mutate_at(c("V3"), funs(lag), n = 1 ) %>% 
 unite(V4,V1, V2, V3, sep = "", remove = FALSE)

我确信有一个更复杂的函数可以更巧妙地完成此操作。例如，将 '\n' 或 '\r' 与 strsplit 结合使用:

 df2 <- strsplit(df, '\n') 
 df3 <- do.call(cbind.data.frame, df2)

比我更有经验的人可以建议我如何抓取这张 table 吗？

Answer 1

就像@Justin Coco 暗示的那样，这很有趣。代码最终比我预期的要复杂一些，但我认为结果应该是你想象的那样。

我使用了 pdf_data 而不是 pdf_text，这样我就可以处理单词的位置。

library(pdftools)
#> Using poppler version 0.86.1
library(tidyverse)
pdf_location <- "/location/of/pdf"
pdf_raw <- pdf_data(pdf_location)

然后我编写了一个可以处理 PDF 页面的函数:

get_table <- function(x, page) {
  x[[page]] %>% # select page, I use this variable again below, which is why I'm not simply looping through the whole object

    filter(y > 25, y < 833) %>% # above and below these positions is the pdf header which we are not interested in
    mutate(column = case_when( # I check the x-positions where the columns start an end and transformed them into column numbers
      x >= 36 & x < 220 ~ 1L,
      x >= 220 & x < 403 ~ 2L,
      x >= 403 ~ 3L,
    )) %>% 
    mutate(newrow = case_when( # check if this is a new line
      column == 1L & x == 36  ~ TRUE, 
      column == 2L & x == 220 ~ TRUE,
      column == 3L & x == 403 ~ TRUE,
      TRUE ~ FALSE
    ),
    row = cumsum(newrow), # get the row number
    subsidiary = newrow & text == ".") %>% # as you say, subsidiary names start with "."
    group_by(row, column) %>% # grouping and summarising moves the text into one 'cell'
    summarise(text = paste(text, collapse = " "), 
              subsidiary = sum(subsidiary) > 0,
              .groups = "drop") %>% 
    mutate(headline = !str_detect(text, "\\s")) %>% # the category headlines (@, A, B, C, etc.) are still in there but can be identified easily since they lack whitespace
    mutate(row = ifelse(row > 1 & !subsidiary & !lag(subsidiary) & !lag(headline), lag(row), row),
           row = ifelse(row > 1 & !subsidiary & !lag(subsidiary) & !lag(headline), lag(row), row)) %>% # some company names stretch over up to three lines but lines are not indented
    group_by(row, column) %>% 
    summarise(text = paste(text, collapse = " "), 
              subsidiary = sum(subsidiary) > 0,
              headline = head(headline, 1),
              .groups = "drop")  %>% 
    
    mutate(page = page, .before = row) # finally add the page number to keep track
}

您可以在一页上进行测试或一次循环所有页面:

pdf_df <- map_df(19:1428, ~get_table(pdf_raw, page = .x))  

我已经喜欢 df，但您要求表格应该“显示公司名称及其子公司”。因此，让我们对 pdf_df 对象进行更多争论。

pdf_df %>% 
  filter(!headline) %>% 
  mutate(company_nr = cumsum(!subsidiary)) %>% 
  group_by(company_nr) %>% 
  mutate(company = text[!subsidiary & !headline]) %>% 
  filter(subsidiary) %>% 
  select(company_nr, company, subsidiary = text)
#> # A tibble: 303,380 x 3
#> # Groups:   company_nr [115,477]
#>    company_nr company                             subsidiary                    
#>         <int> <chr>                               <chr>                         
#>  1          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . ?What If! China Holdings Li…
#>  2          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . . ?What If! Innovation Sing…
#>  3          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . ?What If! Joint Ventures Li…
#>  4          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . ?What If! Limited England   
#>  5          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . . ? What If ! Inventors Lim…
#>  6          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . . ? What If ! Training Limi…
#>  7          1 ?WHAT IF! HOLDINGS LIMITED The Gla… . Nobby Styles Limited Englan…
#>  8          2 @A COMPANY LIMITED Premier Suite 4… . Aviva Holdings Limited Engl…
#>  9          2 @A COMPANY LIMITED Premier Suite 4… . Copper Mountain Networks Li…
#> 10          2 @A COMPANY LIMITED Premier Suite 4… . Just Ties Limited England   
#> # … with 303,370 more rows

^{由 reprex package 于 2021 年 5 月 23 日创建(v2.0.0)}

如果有问题，请在评论中告诉我。显然，我没有浏览所有页面来检查脚本是否有一些特定公司名称等的怪癖，但第一页对我来说看起来不错。