Pulling data from a PDF to a dataframe (while avoiding dynamic issues)

user_17081051 0
Viewed 157

I'm trying to convert a pdf into a dataframe, however because the column titles are being repeated on each page (and there's a note on the final page), I'm finding it difficult to think of an appropriate way of putting it into a dataframe while simultaneously dealing with any dynamic issues.

I've read it into R as an object in the following method:

library(pdftools)
library(dplyr)
library(tidyverse)

temps <- tempfile(fileext = ".pdf")

download.file("https://www.dmo.gov.uk/dmo_static_reports/Gilt%20Operations.pdf", destfile = temps, mode="wb")

And I'm thinking I would do something like:

ops <- pdf_text(temps) %>%
  readr::read_lines()

Then I was thinking about dropping the unnecessary lines explicitly, then converting it into a dataframe. However given the issues mentioned above, I don't think that'll work in the long term.

Does anyone have any advice on the best solution for this?

dataframe r pdf
1 Answers

It is a bit tricky, but maybe you want something like this. First reduces the repeated whitespace between each string using str_squish. After the whitespace, we can use strsplit to split the elements of each string into substrings:

library(pdftools)
library(dplyr)
library(tidyverse)
PDF <- pdf_text(temps) %>%
  readr::read_lines() %>%
  str_squish() %>%
  strsplit(split = " ")

The following code will remove the empty lists which has character(0):

PDF <- Filter(length, PDF)

Now you have lists in a lists with different lengths. To create a dataframe from lists in a list, you can use the following code:

PDF %>% 
  map_df(~.x %>% 
           map(~if(length(.)) . else NA) %>% 
           do.call(what = cbind) %>% 
           as_tibble)

Output:

# A tibble: 1,476 × 35
   V1        V2    V3    V4    V5    V6    V7    V8    V9    V10   V11   V12   V13   V14   V15   V16   V17  
   <chr>     <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
 1 Data      Date: 17-M… NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 2 GILT      MARK… NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 3 RESULTS   OF    MARK… OPER… NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 4 RESULTS   OF    OUTR… GILT  AUCT… NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 5 Auction   Date  Gilt  Name  Inde… Lag   Amou… Issu… Amou… Aver… Bid   to    Cover Yield Yield Issue Date 
 6 for       Inde… (£    mill… Sold  to    Acce… Ratio at    Tail  at    Auct… NA    NA    NA    NA    NA   
 7 Gilts     nomi… CRND  Price (AAP) AAP   (bp)  NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 8 (£        mill… (£)   NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
 9 nominal)  NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA   
10 17-May-2… 1¼%   Trea… Gilt  2051  2,18… 0     82.6… 2.91  2.04… 0.2   18-M… NA    NA    NA    NA    NA   
# … with 1,466 more rows, and 18 more variables: V18 <chr>, V19 <chr>, V20 <chr>, V21 <chr>, V22 <chr>,
#   V23 <chr>, V24 <chr>, V25 <chr>, V26 <chr>, V27 <chr>, V28 <chr>, V29 <chr>, V30 <chr>, V31 <chr>,
#   V32 <chr>, V33 <chr>, V34 <chr>, V35 <chr>

Now you have a dataframe of the PDF data with different lengths.

user_142827140
Related