Mining Text from PDF Files, Part 1: PDF with Text

Intro

I wanted to find out how to mine text from PDF files with R. I’m experimenting with different formats, which will each have their own blog post. This first one is about PDF files with just text in them. The second one will be about extracting text in tables and in the third one I will extract text that’s in a picture inside a PDF file.

I’m assuming you’re using RStudio as your IDE (Integrated Development Environment). I’m sure most of this can be done with using something else as well, but I don’t know how the DataEditR part, for instance, will work in another environment.

pdftools in action

For this experiment, I’m using this cool package called pdftools. I know I’m only scratching the surface here and if you’d like to know more, you should probably read the package’s documentation.

1. Let’s get ready

Before we go any further, I’m going to load the packages we’ll be needing today:

library(pdftools)
# The main package for this operation

library(tidyverse)
# Prerequisite to everything

library(DataEditR)
# More about this one when we get there, but basically 'Excel in R'

library(writexl)
# My go-to package for writing Excel files

I’ll also show you the raw material. If you’d like to try this at home, you can save the PDF file shown below. What we’re looking at here is (Spotify’s weekly top 100 chart for Finland (2021-05-14 - 2021-05-21), but in text form.

As you can see, the formatting is a bit rough and (spoiler alert) it will cause us some problems along the way. Let’s see how we’ll be able to handle them.

fig. 1 - The raw material

2. Read in the PDF file

Next, let’s read in the pdf text file as a character vector using pdf_text():

pdf_with_text <- pdf_text("index_files/pdf_with_text.pdf")
pdf_with_text
## [1] "artist        track           rank          streams\n\nLil Nas X     MONTERO (Call Me By Your Name)               1             275091\n\nBlind Channel Dark Side       2             260403\n\nSANNI         Pettäjä         3             255770\n\nHaloo Helsinki!               Piilotan mun kyyneleet       4             238089\n\nDoja Cat      Kiss Me More (feat. SZA)      5              236820\n\nCledos, BEHM Life (Sun luo) 6               224839\n\nMasked Wolf Astronaut In The Ocean          7              202630\n\nBEHM          Frida           8             200910\n\nPyhimys, Eino Grön            Hyvät hautajaiset            9             196586\n\ncostee        Ne voi liittyy (feat. BIZI)   10             194852\n\nRiton, Nightcrawlers          Friday (feat. Mufasa & Hypeman) - Dopamine Re-Edit        11            186470\n\nTion Wayne, Russ Millions     Body (Remix) [feat. ArrDee, E1 (3x3), ZT (3x3), Bugzy Malone, Buni, Fivio Foreign & Darkoo]   12   182369\n\nNathan Evans Wellerman - Sea Shanty / 220 KID x Billen Ted Remix         13             182353\n\nDua Lipa      Levitating (feat. DaBaby)     14             180922\n\nEtta          Prinsessa       15            175544\n\nBEHM          Hei rakas       16            168578\n\nJustin Bieber Peaches (feat. Daniel Caesar & Giveon)       17            167833\n\nKeko Salata   Kaipaan sua (feat. Boyat & Samuli Heimo) 18                158543\n"                                               
## [2] "YB026, Nuteh Jonez            Steppasin Partyy        19       154737\n\nibe             Tunteet       20             147176\n\nBillie Eilish   Your Power    21             143926\n\nThe Weeknd Save Your Tears (with Ariana Grande) (Remix)        22       142865\n\nwilliam         Penelope (feat. Clever)      23       142381\n\nPolo G          RAPSTAR       24             135300\n\nOlivia Rodrigo good 4 u       25             132459\n\nATB, Topic, A7S               Your Love (9PM)         26       132176\n\nMajestic, Boney M.            Rasputin       27       129677\n\nP!nk, Willow Sage Hart        Cover Me In Sunshine    28       126374\n\nABREU           20 Ave Mariaa 29             125031\n\nwilliam         Flyys         30             121863\n\nBrädi           Keväät        31             121534\n\nAurora          Vettä kaivoon (feat. Keko Salata)     32       119095\n\nTeflon Brothers, Pandora      I Love You     33       117764\n\nPortion Boys Kyläbaari        34             116293\n\nLauri Tähkä     Aavikko       35             114680\n\nTiësto          The Business 36              111908\n\nPihlaja         Paha Barbi    37             110442\n"                                                                                                                                                                                                                                                                                                       
## [3] "ibe, Blacflaco, Elastinen     WEST SIDE BABY             38       110222\n\nBEHM           Päästä varpaisiin           39            110066\n\nBEHM           Lupaan         40           109843\n\nIlta           Kelle mä soitan             41            108466\n\nThe Weeknd Blinding Lights 42              106940\n\nAlan Walker, Conor Maynard Believers       43            106449\n\nKaija Koo      Sateenkaari pimeessä        44            104221\n\nBEHM           Tivolit        45           103219\n\nColdplay       Higher Power 46             102244\n\nTom Odell      Another Love 47             99598\n\nAva Max        My Head & My Heart          48            96021\n\nBella Poarch Build a Bitch 49              95933\n\nPyrythekid     Epäkohtelias (feat. Axel Kala & Gettomasa) 50      93762\n\nThe Weeknd Save Your Tears                 51            93498\n\nNightshift, Pyhimys, ibe, Dreas            Kivullisii    52       90460\n\nJVG            Ikuinen vappu 53            89440\n\nAURORA         Runaway        54           83727\n\nOlivia Rodrigo drivers license 55          83344\n\nEllinoora      Dinosauruksii 56            82398\n"                                                                                                                                                                                                                                                                                                                            
## [4] "Kube            100              57                82145\n\nJanna          Maailma meidän jälkeen              58                81706\n\nDuncan Laurence                  Arcade            59                81427\n\nGasellit, Karri Koira            Me ei mennä rikki                   60      78202\n\nArttu Wiskari Tässäkö tää oli? (feat. Leavings-Orkesteri) 61                 77709\n\nJ. Cole        amari             62                76892\n\nOlivia Rodrigo deja vu           63                75942\n\nBruno Mars, Anderson .Paak, Silk Sonic             Leave The Door Open       64      75299\n\nHapporadio     Jos et olis siinä                   65                74306\n\nOfenbach        Wasted Love (feat. Lagique) 66                       74285\n\nibe            molemmat          67                73492\n\nJ. Cole        p r i d e . i s . t h e . d e v i l (with Lil Baby)   68      72613\n\nBEHM           Minä vai maailma (feat. Keko Salata)                  69      71289\n\nHaloo Helsinki!                  Lady Domina 70                      70890\n\nErika Vikman Syntisten pöytä                       71                70638\n\nScurtDae        Back to Life (Birthdae)            72                70592\n\nJ. Cole        m y . l i f e (with 21 Savage & Morray)               73      70345\n\nThe Kid LAROI WITHOUT YOU                          74                68620\n\nImagine Dragons                  Follow You        75                67793\n"
## [5] "Erin          Niinku koko ajan             76           67644\n\nElias Kaskinen Kerran elämässä             77           67296\n\nVIIVI         Lääke            78          67076\n\nJ. Cole       i n t e r l u d e 79         66523\n\nKuningasidea Pohjolan tuulet               80           66243\n\nChebaleba     Kesäfiilistelyy (feat. RicoWamos)         81           65072\n\nCardi B       Up               82          64987\n\nStudio Killers Jenny (I Wanna Ruin Our Friendship)      83           64202\n\nElastinen     Epäröimättä hetkeekään (feat. Jenni Vartiainen)        84            63930\n\nGettomasa     Silmät           85          63448\n\nMikael GabrielIntiaanikesä     86          62128\n\nOneRepublic Run                87          62096\n\nKymppilinja   Minä (feat. Mariska)         88           61114\n\nSamu Haber Täältä tullaan 89               60235\n\nSurf Curse    Freaks           90          59496\n\nMartin Garrix We Are The People (feat. Bono & The Edge) - Official UEFA EURO 2020 Song     91   58058\n\nGlass Animals Heat Waves       92          57694\n\n24kGoldn      Mood (feat. iann dior)       93           57086\n\nAxel Kala     Moni meist       94          55938\n"                                                                                                                                                                                                                                                                            
## [6] "Poju          Esson baariin 95             55892\n\nDMNDS, Strange Fruits Music, Fallen Roses Calabria (feat. Lujavo & Nito-Onna)        96   55805\n\nAleksanteri Hakaniemi       Bonsaipuu      97            55657\n\nDJ Khaled     I DID IT (feat. Post Malone, Megan Thee Stallion, Lil Baby & DaBaby)   98   55644\n\nKeko Salata   Vanha (feat. BEHM)           99            55451\n\nKlamydia      Pienen pojan elämää          100           54190\n"

As you can see, we got the text, but it’s in six big chunks that need to be split up, before it’s of any use to us. Which brings me to…

3. Split the character vector into a list

We should now create a list by splitting the character vector with strsplit (using those row change marks (\ + n):

pdf_with_text_list <- strsplit(pdf_with_text, split = "\n")

pdf_with_text_list
## [[1]]
##  [1] "artist        track           rank          streams"                                                                                    
##  [2] ""                                                                                                                                       
##  [3] "Lil Nas X     MONTERO (Call Me By Your Name)               1             275091"                                                        
##  [4] ""                                                                                                                                       
##  [5] "Blind Channel Dark Side       2             260403"                                                                                     
##  [6] ""                                                                                                                                       
##  [7] "SANNI         Pettäjä         3             255770"                                                                                     
##  [8] ""                                                                                                                                       
##  [9] "Haloo Helsinki!               Piilotan mun kyyneleet       4             238089"                                                        
## [10] ""                                                                                                                                       
## [11] "Doja Cat      Kiss Me More (feat. SZA)      5              236820"                                                                      
## [12] ""                                                                                                                                       
## [13] "Cledos, BEHM Life (Sun luo) 6               224839"                                                                                     
## [14] ""                                                                                                                                       
## [15] "Masked Wolf Astronaut In The Ocean          7              202630"                                                                      
## [16] ""                                                                                                                                       
## [17] "BEHM          Frida           8             200910"                                                                                     
## [18] ""                                                                                                                                       
## [19] "Pyhimys, Eino Grön            Hyvät hautajaiset            9             196586"                                                        
## [20] ""                                                                                                                                       
## [21] "costee        Ne voi liittyy (feat. BIZI)   10             194852"                                                                      
## [22] ""                                                                                                                                       
## [23] "Riton, Nightcrawlers          Friday (feat. Mufasa & Hypeman) - Dopamine Re-Edit        11            186470"                           
## [24] ""                                                                                                                                       
## [25] "Tion Wayne, Russ Millions     Body (Remix) [feat. ArrDee, E1 (3x3), ZT (3x3), Bugzy Malone, Buni, Fivio Foreign & Darkoo]   12   182369"
## [26] ""                                                                                                                                       
## [27] "Nathan Evans Wellerman - Sea Shanty / 220 KID x Billen Ted Remix         13             182353"                                         
## [28] ""                                                                                                                                       
## [29] "Dua Lipa      Levitating (feat. DaBaby)     14             180922"                                                                      
## [30] ""                                                                                                                                       
## [31] "Etta          Prinsessa       15            175544"                                                                                     
## [32] ""                                                                                                                                       
## [33] "BEHM          Hei rakas       16            168578"                                                                                     
## [34] ""                                                                                                                                       
## [35] "Justin Bieber Peaches (feat. Daniel Caesar & Giveon)       17            167833"                                                        
## [36] ""                                                                                                                                       
## [37] "Keko Salata   Kaipaan sua (feat. Boyat & Samuli Heimo) 18                158543"                                                        
## 
## [[2]]
##  [1] "YB026, Nuteh Jonez            Steppasin Partyy        19       154737"         
##  [2] ""                                                                              
##  [3] "ibe             Tunteet       20             147176"                           
##  [4] ""                                                                              
##  [5] "Billie Eilish   Your Power    21             143926"                           
##  [6] ""                                                                              
##  [7] "The Weeknd Save Your Tears (with Ariana Grande) (Remix)        22       142865"
##  [8] ""                                                                              
##  [9] "william         Penelope (feat. Clever)      23       142381"                  
## [10] ""                                                                              
## [11] "Polo G          RAPSTAR       24             135300"                           
## [12] ""                                                                              
## [13] "Olivia Rodrigo good 4 u       25             132459"                           
## [14] ""                                                                              
## [15] "ATB, Topic, A7S               Your Love (9PM)         26       132176"         
## [16] ""                                                                              
## [17] "Majestic, Boney M.            Rasputin       27       129677"                  
## [18] ""                                                                              
## [19] "P!nk, Willow Sage Hart        Cover Me In Sunshine    28       126374"         
## [20] ""                                                                              
## [21] "ABREU           20 Ave Mariaa 29             125031"                           
## [22] ""                                                                              
## [23] "william         Flyys         30             121863"                           
## [24] ""                                                                              
## [25] "Brädi           Keväät        31             121534"                           
## [26] ""                                                                              
## [27] "Aurora          Vettä kaivoon (feat. Keko Salata)     32       119095"         
## [28] ""                                                                              
## [29] "Teflon Brothers, Pandora      I Love You     33       117764"                  
## [30] ""                                                                              
## [31] "Portion Boys Kyläbaari        34             116293"                           
## [32] ""                                                                              
## [33] "Lauri Tähkä     Aavikko       35             114680"                           
## [34] ""                                                                              
## [35] "Tiësto          The Business 36              111908"                           
## [36] ""                                                                              
## [37] "Pihlaja         Paha Barbi    37             110442"                           
## 
## [[3]]
##  [1] "ibe, Blacflaco, Elastinen     WEST SIDE BABY             38       110222"
##  [2] ""                                                                        
##  [3] "BEHM           Päästä varpaisiin           39            110066"         
##  [4] ""                                                                        
##  [5] "BEHM           Lupaan         40           109843"                       
##  [6] ""                                                                        
##  [7] "Ilta           Kelle mä soitan             41            108466"         
##  [8] ""                                                                        
##  [9] "The Weeknd Blinding Lights 42              106940"                       
## [10] ""                                                                        
## [11] "Alan Walker, Conor Maynard Believers       43            106449"         
## [12] ""                                                                        
## [13] "Kaija Koo      Sateenkaari pimeessä        44            104221"         
## [14] ""                                                                        
## [15] "BEHM           Tivolit        45           103219"                       
## [16] ""                                                                        
## [17] "Coldplay       Higher Power 46             102244"                       
## [18] ""                                                                        
## [19] "Tom Odell      Another Love 47             99598"                        
## [20] ""                                                                        
## [21] "Ava Max        My Head & My Heart          48            96021"          
## [22] ""                                                                        
## [23] "Bella Poarch Build a Bitch 49              95933"                        
## [24] ""                                                                        
## [25] "Pyrythekid     Epäkohtelias (feat. Axel Kala & Gettomasa) 50      93762" 
## [26] ""                                                                        
## [27] "The Weeknd Save Your Tears                 51            93498"          
## [28] ""                                                                        
## [29] "Nightshift, Pyhimys, ibe, Dreas            Kivullisii    52       90460" 
## [30] ""                                                                        
## [31] "JVG            Ikuinen vappu 53            89440"                        
## [32] ""                                                                        
## [33] "AURORA         Runaway        54           83727"                        
## [34] ""                                                                        
## [35] "Olivia Rodrigo drivers license 55          83344"                        
## [36] ""                                                                        
## [37] "Ellinoora      Dinosauruksii 56            82398"                        
## 
## [[4]]
##  [1] "Kube            100              57                82145"                                  
##  [2] ""                                                                                          
##  [3] "Janna          Maailma meidän jälkeen              58                81706"                
##  [4] ""                                                                                          
##  [5] "Duncan Laurence                  Arcade            59                81427"                
##  [6] ""                                                                                          
##  [7] "Gasellit, Karri Koira            Me ei mennä rikki                   60      78202"        
##  [8] ""                                                                                          
##  [9] "Arttu Wiskari Tässäkö tää oli? (feat. Leavings-Orkesteri) 61                 77709"        
## [10] ""                                                                                          
## [11] "J. Cole        amari             62                76892"                                  
## [12] ""                                                                                          
## [13] "Olivia Rodrigo deja vu           63                75942"                                  
## [14] ""                                                                                          
## [15] "Bruno Mars, Anderson .Paak, Silk Sonic             Leave The Door Open       64      75299"
## [16] ""                                                                                          
## [17] "Happoradio     Jos et olis siinä                   65                74306"                
## [18] ""                                                                                          
## [19] "Ofenbach        Wasted Love (feat. Lagique) 66                       74285"                
## [20] ""                                                                                          
## [21] "ibe            molemmat          67                73492"                                  
## [22] ""                                                                                          
## [23] "J. Cole        p r i d e . i s . t h e . d e v i l (with Lil Baby)   68      72613"        
## [24] ""                                                                                          
## [25] "BEHM           Minä vai maailma (feat. Keko Salata)                  69      71289"        
## [26] ""                                                                                          
## [27] "Haloo Helsinki!                  Lady Domina 70                      70890"                
## [28] ""                                                                                          
## [29] "Erika Vikman Syntisten pöytä                       71                70638"                
## [30] ""                                                                                          
## [31] "ScurtDae        Back to Life (Birthdae)            72                70592"                
## [32] ""                                                                                          
## [33] "J. Cole        m y . l i f e (with 21 Savage & Morray)               73      70345"        
## [34] ""                                                                                          
## [35] "The Kid LAROI WITHOUT YOU                          74                68620"                
## [36] ""                                                                                          
## [37] "Imagine Dragons                  Follow You        75                67793"                
## 
## [[5]]
##  [1] "Erin          Niinku koko ajan             76           67644"                                        
##  [2] ""                                                                                                     
##  [3] "Elias Kaskinen Kerran elämässä             77           67296"                                        
##  [4] ""                                                                                                     
##  [5] "VIIVI         Lääke            78          67076"                                                     
##  [6] ""                                                                                                     
##  [7] "J. Cole       i n t e r l u d e 79         66523"                                                     
##  [8] ""                                                                                                     
##  [9] "Kuningasidea Pohjolan tuulet               80           66243"                                        
## [10] ""                                                                                                     
## [11] "Chebaleba     Kesäfiilistelyy (feat. RicoWamos)         81           65072"                           
## [12] ""                                                                                                     
## [13] "Cardi B       Up               82          64987"                                                     
## [14] ""                                                                                                     
## [15] "Studio Killers Jenny (I Wanna Ruin Our Friendship)      83           64202"                           
## [16] ""                                                                                                     
## [17] "Elastinen     Epäröimättä hetkeekään (feat. Jenni Vartiainen)        84            63930"             
## [18] ""                                                                                                     
## [19] "Gettomasa     Silmät           85          63448"                                                     
## [20] ""                                                                                                     
## [21] "Mikael GabrielIntiaanikesä     86          62128"                                                     
## [22] ""                                                                                                     
## [23] "OneRepublic Run                87          62096"                                                     
## [24] ""                                                                                                     
## [25] "Kymppilinja   Minä (feat. Mariska)         88           61114"                                        
## [26] ""                                                                                                     
## [27] "Samu Haber Täältä tullaan 89               60235"                                                     
## [28] ""                                                                                                     
## [29] "Surf Curse    Freaks           90          59496"                                                     
## [30] ""                                                                                                     
## [31] "Martin Garrix We Are The People (feat. Bono & The Edge) - Official UEFA EURO 2020 Song     91   58058"
## [32] ""                                                                                                     
## [33] "Glass Animals Heat Waves       92          57694"                                                     
## [34] ""                                                                                                     
## [35] "24kGoldn      Mood (feat. iann dior)       93           57086"                                        
## [36] ""                                                                                                     
## [37] "Axel Kala     Moni meist       94          55938"                                                     
## 
## [[6]]
##  [1] "Poju          Esson baariin 95             55892"                                               
##  [2] ""                                                                                               
##  [3] "DMNDS, Strange Fruits Music, Fallen Roses Calabria (feat. Lujavo & Nito-Onna)        96   55805"
##  [4] ""                                                                                               
##  [5] "Aleksanteri Hakaniemi       Bonsaipuu      97            55657"                                 
##  [6] ""                                                                                               
##  [7] "DJ Khaled     I DID IT (feat. Post Malone, Megan Thee Stallion, Lil Baby & DaBaby)   98   55644"
##  [8] ""                                                                                               
##  [9] "Keko Salata   Vanha (feat. BEHM)           99            55451"                                 
## [10] ""                                                                                               
## [11] "Klamydia      Pienen pojan elämää          100           54190"

Now, that looks better already, but we’re still not quite there. We want to get all the rows together.

4. Unlist() and create a tibble

Let’s start by unlisting the list and creating a tibble (a special kind of a data frame):

pdf_with_text_tbl <- as_tibble(unlist(pdf_with_text_list))

pdf_with_text_tbl
## # A tibble: 196 x 1
##    value                                                                        
##    <chr>                                                                        
##  1 "artist        track           rank          streams"                        
##  2 ""                                                                           
##  3 "Lil Nas X     MONTERO (Call Me By Your Name)               1             27~
##  4 ""                                                                           
##  5 "Blind Channel Dark Side       2             260403"                         
##  6 ""                                                                           
##  7 "SANNI         Pettäjä         3             255770"                         
##  8 ""                                                                           
##  9 "Haloo Helsinki!               Piilotan mun kyyneleet       4             23~
## 10 ""                                                                           
## # ... with 186 more rows

Nice! We now have all 196 rows together. But since it’s supposed to be a top 100 chart, we can see that there are 96 extra rows that need to be taken care of. Let’s next see what we can do about it.

5. Filter out empty rows and remove first row

Let’s now filter out the empty rows and remove the first row with slice(). It does contain the column titles, so you probably want to copy them to safety first. We’ll be needing them later.

pdf_with_text_filtered_tbl <- pdf_with_text_tbl %>%
  filter(value != "") %>%
  slice(-1) # Note to self and others: slice(n) to keep the nth row, slice(-n) to get rid of it 

pdf_with_text_filtered_tbl
## # A tibble: 100 x 1
##    value                                                                        
##    <chr>                                                                        
##  1 Lil Nas X     MONTERO (Call Me By Your Name)               1             275~
##  2 Blind Channel Dark Side       2             260403                           
##  3 SANNI         Pettäjä         3             255770                           
##  4 Haloo Helsinki!               Piilotan mun kyyneleet       4             238~
##  5 Doja Cat      Kiss Me More (feat. SZA)      5              236820            
##  6 Cledos, BEHM Life (Sun luo) 6               224839                           
##  7 Masked Wolf Astronaut In The Ocean          7              202630            
##  8 BEHM          Frida           8             200910                           
##  9 Pyhimys, Eino Grön            Hyvät hautajaiset            9             196~
## 10 costee        Ne voi liittyy (feat. BIZI)   10             194852            
## # ... with 90 more rows

That’s more like it. A hundred rows for a hundred tracks. But now for the tricky part. We know that there should be four columns, but we only see one, value.

6. Separate text into columns with regex

This is the part that I have to start with a HUGE DISCLAIMER: I’m not an expert on regex (regular expression). I’m pretty sure there would be a better way to do this next part. And if you are reading this and know what it is, please let me know!

As we can see from the previous tibble, Blind Channel and their song Dark Side are separated by only one empty character. If all the “columns” were separated by at least two empty characters, this would be an easy task. And since that’s the closest thing to an acceptable solution, that’s what we’ll still do.

pdf_with_text_separated_tbl <- pdf_with_text_filtered_tbl %>% 
  separate(col      = value,
           # col    = the column which we are separating
           
           into     = c("artist", "track", "rank", "streams"),
           # into   = the new column names
           
           sep      = "[ ]{2,}",
           # sep    = the regex pattern to separate the value column by, in this case by using two or more empty characters
           
           remove   = TRUE)
## Warning: Expected 4 pieces. Missing pieces filled with `NA` in 38 rows [2, 6, 7,
## 13, 17, 18, 22, 25, 29, 34, 36, 42, 43, 46, 47, 49, 50, 51, 53, 55, ...].
           # remove = if TRUE, removes the original value column

pdf_with_text_separated_tbl
## # A tibble: 100 x 4
##    artist                            track                         rank  streams
##    <chr>                             <chr>                         <chr> <chr>  
##  1 Lil Nas X                         MONTERO (Call Me By Your Nam~ 1     275091 
##  2 Blind Channel Dark Side           2                             2604~ <NA>   
##  3 SANNI                             Pettäjä                       3     255770 
##  4 Haloo Helsinki!                   Piilotan mun kyyneleet        4     238089 
##  5 Doja Cat                          Kiss Me More (feat. SZA)      5     236820 
##  6 Cledos, BEHM Life (Sun luo) 6     224839                        <NA>  <NA>   
##  7 Masked Wolf Astronaut In The Oce~ 7                             2026~ <NA>   
##  8 BEHM                              Frida                         8     200910 
##  9 Pyhimys, Eino Grön                Hyvät hautajaiset             9     196586 
## 10 costee                            Ne voi liittyy (feat. BIZI)   10    194852 
## # ... with 90 more rows

As you can see, we received a warning about missing pieces that were now filled with NA. Since I don’t see a proggammable way out of this situation, we’ll try to fix things using the DataEditR package. Only one quick mutate() to change the rank and stream columns’ types from text to numeric:

pdf_with_text_separated_tbl <- pdf_with_text_separated_tbl %>% 
  mutate(
    rank    = as.numeric(rank),
    streams = as.numeric(streams)
  )

pdf_with_text_separated_tbl
## # A tibble: 100 x 4
##    artist                            track                          rank streams
##    <chr>                             <chr>                         <dbl>   <dbl>
##  1 Lil Nas X                         MONTERO (Call Me By Your Na~      1  275091
##  2 Blind Channel Dark Side           2                            260403      NA
##  3 SANNI                             Pettäjä                           3  255770
##  4 Haloo Helsinki!                   Piilotan mun kyyneleet            4  238089
##  5 Doja Cat                          Kiss Me More (feat. SZA)          5  236820
##  6 Cledos, BEHM Life (Sun luo) 6     224839                           NA      NA
##  7 Masked Wolf Astronaut In The Oce~ 7                            202630      NA
##  8 BEHM                              Frida                             8  200910
##  9 Pyhimys, Eino Grön                Hyvät hautajaiset                 9  196586
## 10 costee                            Ne voi liittyy (feat. BIZI)      10  194852
## # ... with 90 more rows

7. Finish things manually with DataEditR

This is the code I would use to start the DataEditR (inside of RStudio):

pdf_with_text_final_tbl <- data_edit(pdf_with_text_separated_tbl)

You can then cut and paste the values as you would do in Excel.

fig. 2 - DataEditR (before)

When you’re done editing, you should click the Refresh(?) button [1] and then Done [2].

fig. 3 - DataEditR (after)

We now have the pdf_with_text_final_tbl to finally save as an Excel file:

pdf_with_text_final_tbl
## # A tibble: 100 x 4
##    artist             track                           rank streams
##    <chr>              <chr>                          <dbl>   <dbl>
##  1 Lil Nas X          MONTERO (Call Me By Your Name)     1  275091
##  2 Blind Channel      Dark Side                          2  260403
##  3 SANNI              Pettäjä                            3  255770
##  4 Haloo Helsinki!    Piilotan mun kyyneleet             4  238089
##  5 Doja Cat           Kiss Me More (feat. SZA)           5  236820
##  6 Cledos, BEHM       Life (Sun luo)                     6  224839
##  7 Masked Wolf        Astronaut In The Ocean             7  202630
##  8 BEHM               Frida                              8  200910
##  9 Pyhimys, Eino Grön Hyvät hautajaiset                  9  196586
## 10 costee             Ne voi liittyy (feat. BIZI)       10  194852
## # ... with 90 more rows

Almost there! The data is now clean as a whistle and all that is left to do, at the moment, is to save it as an excel file.

8. Create Excel file with writexl

write_xlsx(pdf_with_text_final_tbl, "index_files/excel_from_pdf_with_text.xlsx")
# You should change the file path to suit your needs

fig. 4 - Extract from the final Excel file

Outro

Beautiful, isn’t it! It took us a while, but we got there eventually. Thanks for reading this far. If you’re curious to see, how the same data behaves if it’s in a table inside a PDF, tune in next week for part 2 of this PDF trilogy! Until then, happy text mining!

ps. I’m more than happy to chat about all things data. Just send me a message on LinkedIn if you wish to do so!