Creating columns from scraped pdf with cuts on spaces

Question

I'm trying to create a data frame from the following PDF

library(tabulizer)
url <- "https://doccs.ny.gov/system/files/documents/2020/06/doccs-covid-19-confirmed-by-facility-6.30.2020.pdf"
tab1 <- extract_tables(url)

However, when I call tab1 it only has one column:

      [,1]                                                                     
 [1,] "NYS DOCCS INCARCERATED INDIVIDUALS COVID-19 REPORT BY REPORTED FACILITY"
 [2,] "AS OF JUNE 29, 2020 AT 3:00 PM"                                         
 [3,] "POSITIVE CASE STATUS OTHER TESTS"                                       
 [4,] "TOTAL"                                                                  
 [5,] "FACILITY RECOVERED DECEASED POSITIVE PENDING NEGATIVE"                  
 [6,] "TOTAL 495 16 519 97 805"                                                
 [7,] "ADIRONDACK 0 0 0 75 0"                                                  
 [8,] "ALBION 0 0 0 0 2"                                                       
 [9,] "ALTONA 0 0 0 0 1"

I would like to extract what should be the individual columns to create a dataframe (e.g. for row 7 I extract its contents into the following columns: Facility ("Adirondack") Recovered (0) Decesased (0) Positive (0) Pending (75) Negative (0) ). I'm thinking that the most efficient way to do this would be to make cuts in tab1 based on spaces, but this doesn't work since some of the facilities have multiple words in them, so the space cut would get messed up. Does anyone have an idea for a solution? Thanks for the help!

score 2 · Answer 1 · answered Jul 01 '20 at 00:10

Here is how I would handle this using the "lattice" method of table extraction from the tabulizer package.

#install.packages("tidyverse")
library(tidyverse)
#install.packages("janitor")
library(janitor)
#install.packages("tabulizer")
library(tabulizer)
url <- "https://doccs.ny.gov/system/files/documents/2020/06/doccs-covid-19-confirmed-by-facility-6.30.2020.pdf"
tab1 <- tabulizer::extract_tables(url, method = "lattice") %>% 
  as.data.frame() %>%
  dplyr::slice(-1,-2) %>% 
  janitor::row_to_names(row_number = 1)

denis · Accepted Answer · 2020-06-30T22:13:20.577

Here is a workaround:

library(tabulizer)

url <- "https://doccs.ny.gov/system/files/documents/2020/06/doccs-covid-19-confirmed-by-facility-6.30.2020.pdf"
tab1 <- extract_tables(url)

plouf <- tab1[[1]][6:dim(tab1[[1]])[1],] 
plouf <- gsub("([A-Z]+) ([A-Z]+)","\\1_\\2",plouf)
df <- read.table(text = paste0(t(plouf) ,collapse = "\n\r"),sep = " ")
names(df) <- strsplit(tab1[[1]][5,]," ")[[1]]

           FACILITY RECOVERED DECEASED POSITIVE PENDING NEGATIVE
1             TOTAL       495       16      519      97      805
2        ADIRONDACK         0        0        0      75        0
3            ALBION         0        0        0       0        2
4            ALTONA         0        0        0       0        1
5            ATTICA         2        0        2       1        7
6            AUBURN         0        0        0       0       10
7         BARE_HILL         0        0        0       0        6
8     BEDFORD_HILLS        43        1       44       5       53
9      CAPE_VINCENT         0        0        0       0        0
10           CAYUGA         0        0        0       2        1
11          CLINTON         1        0        1       0       25
12          COLLINS         1        0        1       0       13
13        COXSACKIE         1        0        1       0       57
14        DOWNSTATE         1        0        1       0       12
15          EASTERN        17        1       20       0       17
16        EDGECOMBE         0        0        0       0        0
17           ELMIRA         0        0        0       1       20
18         FISHKILL        78        5       83       4       98
19      FIVE_POINTS         0        0        0       0        4
20         FRANKLIN         1        0        1       0       24

I take the table after the title, then remove the spaces between the FACILITY names with gsub (I actually replace them with _, so you can rechange to space after if you want. You can also use str_replace from stringr instead of gsub).

I then use read.table, forcing the text with a end of line after each line. I add the name after (because if not, they get changed in the gsub and read.table do not read them properly).

Creating columns from scraped pdf with cuts on spaces

2 Answers2