When Documents Become Databases – Tabulizer R Wrapper for Tabula PDF Table Extractor

喜欢ヅ旅行 2022-08-21 12:53 11阅读 0赞

Although not necessarily the best way of publishing data, data tables in PDF documents can often be extracted quite easily, particularly if the tables are regular and the cell contents reasonably space.

For example, official timing sheets for F1 races are published by the FIA as *event and timing information* in a set of PDF documents containing tabulated timing data:

![R\_-\_Best\_Sector\_Times\_pdf\_\_1\_page\_][R_-_Best_Sector_Times_pdf_1_page]

In the past, I’ve written a variety of hand crafted scrapers to extract data from the timing sheets, but the regular way in which the data is presented in the documents means that they are quite amenable to scraping using a PDF table extractor such as [Tabula][]. Tabula exists as both a server application, accessed via a web browser, or as a service using the [tabula extractor][] Java application.

I don’t recall how I came across it, but the [tabulizer][] R package provides a wrapper for *tabula extractor* (bundled within the package), that lets you access the service via it’s command line calls. (One dependency you do need to take care of is to have Java installed; adding Java into an RStudio docker container would be one way of taking care of this.)

Running the default extractor command on the above PDF pulls out the data of the inner table:

extract\_tables('Best Sector Times.pdf')

![fia\_pdf\_sector\_extract][fia_pdf_sector_extract]

Where the data is spread across multiple pages, you get a data frame per page.

![R\_-\_Lap\_Analysis\_pdf\_\_page\_3\_of\_8\_][R_-_Lap_Analysis_pdf_page_3_of_8]

Note that the headings for the distinct tables are omitted. Tabula’s “table guesser” identifies the body of the table, but not the spanning column headers.

The default settings are such that tabula will try to scrape data from every page in the document.

![fia\_pdf\_scrape2][fia_pdf_scrape2]

Individual pages, or sets of pages, can be selected using the *pages*parameter. For example:

*  extract\_tables('Lap Analysis.pdf',pages=1
 *  extract\_tables('Lap Analysis.pdf',pages=c(2,3))

Specified areas for scraping can also be specified using the *area*parameter:

extract\_tables('Lap Analysis.pdf', pages=8, guess=F, area=list(c(178, 10, 230, 500)))

The area parameter appears to take co-ordinates in the form: *top, left, width, height* is now fixed to take co-ordinates in the same form as those produced by tabula app debug: *top, left, bottom, right*.

You can find the necessary co-ordinates using the tabula app: if you select an area and preview the data, the selected co-ordinates are viewable in the browser developer tools console area.

![Select\_Tables\_\_\_Tabula\_concole][Select_Tables_Tabula_concole]

The tabula console output gives co-ordinates in the form: *top, left, bottom, right* so you need to do some sums to convert these numbers to the arguments that the tabulizer *area* parameter wants.

![fia\_pdf\_head\_scrape][fia_pdf_head_scrape]

Using a combination of “guess” to find the dominant table, and specified areas, we can extract the data we need from the PDF and combine it to provide a structured and clearly labeled dataframe.

On my to do list: add this data source recipe to the [Wrangling F1 Data With R][] book…

[R_-_Best_Sector_Times_pdf_1_page]: /images/20220724/34e0d13d460249b187fccdd3ed3074df.png
[Tabula]: http://tabula.technology/
[tabula extractor]: https://github.com/tabulapdf/tabula-extractor
[tabulizer]: https://github.com/leeper/tabulizer
[fia_pdf_sector_extract]: /images/20220724/2547d949dcf244ff97b73f2befc16460.png
[R_-_Lap_Analysis_pdf_page_3_of_8]: /images/20220724/6dfa60b4546e4e19a35f50eef5aeaf01.png
[fia_pdf_scrape2]: /images/20220724/a82b06d6b0b3463c81393f2e7c44732a.png
[Select_Tables_Tabula_concole]: /images/20220724/a9d5e49016ef4966821cb3fb1c5aabb7.png
[fia_pdf_head_scrape]: /images/20220724/3096180ec3ef4cbb84c7d15c8f830a38.png
[Wrangling F1 Data With R]: https://leanpub.com/wranglingf1datawithr/