<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/wrangling-data-table-out-of-the-fbi-2017-ic3-crime-report/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/05/08/wrangling-data-table-out-of-the-fbi-2017-ic3-crime-report/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The U.S. FBI Internet Crime Complaint Center was established in 2000 to receive complaints of Internet crime. They produce an annual report, <a rel="nofollow" href="https://www.fbi.gov/news/pressrel/press-releases/fbi-releases-the-ic3-2017-internet-crime-report-and-calls-for-increased-public-awareness" target="_blank">just released 2017’s edition</a>, and I need the data from it. Since I have to wrangle it out, I thought some folks might like to play long at home, especially since it turns out I had to use both <code>tabulizer</code> and <code>pdftools</code> to accomplish my goal.</p>
<p>Concepts presented:</p>
<ul>
<li>PDF scraping (with both <code>tabulizer</code> and <code>pdftools</code>)</li>
<li><code>asciiruler</code></li>
<li>general string manipulation</li>
<li><code>case_when()</code> vs <code>ifelse()</code> for text cleanup</li>
<li>reformatting data for <code>ggraph</code> treemaps</li>
</ul>
<p>Let’s get started! (NOTE: you can click/tap on any image for a larger version)</p>
<pre>
<code>
library(stringi)
library(pdftools)
library(tabulizer)
library(igraph)
library(ggraph) # devtools::install_github(&quot;thomasp85/ggraph&quot;)
library(hrbrthemes)
library(tidyverse)

ic3_file &lt;- &quot;~/Data/2017-ic3-report.pdf&quot; # change &quot;~/Data&quot; for your system

if (!file.exists(ic3_file)) { # don't waste anyone's bandwidth
  download.file(&quot;https://pdf.ic3.gov/2017_IC3Report.pdf&quot;, ic3_file)
}
</code>
</pre>
<p>Let's try <code>pdftools</code> since I like text wrangling</p>
<pre>
<code>
cat(pdftools::pdf_text(ic3_file)[[20]])
##                                                             2017 Internet Crime Report         20
## 2017 Crime Types
##                                  By Victim Count
## Crime Type                         Victims     Crime Type                            Victims
## Non-Payment/Non-Delivery           84,079      Misrepresentation                        5,437
## Personal Data Breach               30,904      Corporate Data Breach                    3,785
## Phishing/Vishing/Smishing/Pharming 25,344      Investment                               3,089
## Overpayment                        23,135      Malware/Scareware/Virus                  3,089
## No Lead Value                      20,241      Lottery/Sweepstakes                      3,012
## Identity Theft                     17,636      IPR/Copyright and                        2,644
##                                                Counterfeit
## Advanced Fee                       16,368      Ransomware                               1,783
## Harassment/Threats of Violence     16,194      Crimes Against Children                  1,300
## Employment                         15,784      Denial of Service/TDoS                   1,201
## BEC/EAC                            15,690      Civil Matter                             1,057
## Confidence Fraud/Romance           15,372      Re-shipping                              1,025
## Credit Card Fraud                  15,220      Charity                                    436
## Extortion                          14,938      Health Care Related                        406
## Other                              14,023      Gambling                                   203
## Tech Support                       10,949      Terrorism                                  177
## Real Estate/Rental                  9,645      Hacktivist                                 158
## Government Impersonation            9,149
## Descriptors*
## Social Media                       19,986      *These descriptors relate to the medium or
## Virtual Currency                    4,139      tool used to facilitate the crime, and are used
##                                                by the IC3 for tracking purposes only. They
##                                                are available only after another crime type
##                                                has been selected.
</code>
</pre>
<p>OK, I don't like text wrangling that much. How about <code>tabulizer</code>?</p>
<pre>
<code>
tabulizer::extract_tables(ic3_file, pages = 20)

## list()
</code>
</pre>
<p>Well, that's disappointing. Perhaps if we target the tables on the PDF pages we'll have better luck. You can find them on pages 20 and 21 if you downloaded your own copy. Here are some smaller, static views of them:</p>
<p><a rel="nofollow" href="https://i0.wp.com/rud.is/b/wp-content/uploads/2018/05/ic3-2017-pgs.png?ssl=1" target="_blank"><img data-orig-file="https://i0.wp.com/rud.is/b/wp-content/uploads/2018/05/ic3-2017-pgs.png?fit=656%2C1590&amp;ssl=1" data-orig-size="656,1590" data-medium-file="https://i0.wp.com/rud.is/b/wp-content/uploads/2018/05/ic3-2017-pgs.png?fit=124%2C300&amp;ssl=1" data-attachment-id="10260" data-image-meta="{" data-image-description="" src="https://i0.wp.com/rud.is/b/wp-content/uploads/2018/05/ic3-2017-pgs.png?resize=656%2C1590&ssl=1" data-permalink="https://rud.is/b/2018/05/08/wrangling-data-table-out-of-the-fbi-2017-ic3-crime-report/ic3-2017-pgs/" data-large-file="https://i0.wp.com/rud.is/b/wp-content/uploads/2018/05/ic3-2017-pgs.png?fit=422%2C1024&amp;ssl=1" alt="" data-image-title="ic3-2017-pgs" width="450" data-comments-opened="1" class="wp-image-10260" data-recalc-dims="1"></a></p>
<p>I can't show the <code>tabulizer</code> pane (well I <em>could</em> if I had time to screen capture and make an animated gif) but run this to get the areas:</p>
<pre>
<code>
areas &lt;- tabulizer::locate_areas(ic3_file, pages = 20:21)

# this is what ^^ produces for my rectangles:

list(
  c(top = 137.11911357341, left = 66.864265927978, bottom = 413.5512465374, right = 519.90581717452),
  c(top = 134.92520775623, left = 64.670360110803, bottom = 458.52631578947, right = 529.7783933518)
) -> areas
</code>
</pre>
<p>Now, see if <code>tabulizer</code> can do a better job. We'll start with the first page:</p>
<pre>
<code>
tab &lt;- tabulizer::extract_tables(ic3_file, pages = 20, area = areas[1])

tab
## [[1]]
##       [,1]                                 [,2]              
##  [1,] &quot;&quot;                                   &quot;By Victim Cou nt&quot;
##  [2,] &quot;Crime Type&quot;                         &quot;Victims&quot;         
##  [3,] &quot;Non-Payment/Non-Delivery&quot;           &quot;84,079&quot;          
##  [4,] &quot;Personal Data Breach&quot;               &quot;30,904&quot;          
##  [5,] &quot;Phishing/Vishing/Smishing/Pharming&quot; &quot;25,344&quot;          
##  [6,] &quot;Overpayment&quot;                        &quot;23,135&quot;          
##  [7,] &quot;No Lead Value&quot;                      &quot;20,241&quot;          
##  [8,] &quot;Identity Theft&quot;                     &quot;17,636&quot;          
##  [9,] &quot;&quot;                                   &quot;&quot;                
## [10,] &quot;Advanced Fee&quot;                       &quot;16,368&quot;          
## [11,] &quot;Harassment/Threats of Violence&quot;     &quot;16,194&quot;          
## [12,] &quot;Employment&quot;                         &quot;15,784&quot;          
## [13,] &quot;BEC/EAC&quot;                            &quot;15,690&quot;          
## [14,] &quot;Confidence Fraud/Romance&quot;           &quot;15,372&quot;          
## [15,] &quot;Credit Card Fraud&quot;                  &quot;15,220&quot;          
## [16,] &quot;Extortion&quot;                          &quot;14,938&quot;          
## [17,] &quot;Other&quot;                              &quot;14,023&quot;          
## [18,] &quot;Tech Support&quot;                       &quot;10,949&quot;          
## [19,] &quot;Real Estate/Rental&quot;                 &quot;9,645&quot;           
## [20,] &quot;G overnment Impersonation&quot;          &quot;9,149&quot;           
## [21,] &quot;&quot;                                   &quot;&quot;                
## [22,] &quot;Descriptors*&quot;                       &quot;&quot;                
##       [,3]                      [,4]     
##  [1,] &quot;&quot;                        &quot;&quot;       
##  [2,] &quot;Crime Type&quot;              &quot;Victims&quot;
##  [3,] &quot;Misrepresentation&quot;       &quot;5,437&quot;  
##  [4,] &quot;Corporate Data Breach&quot;   &quot;3,785&quot;  
##  [5,] &quot;Investment&quot;              &quot;3,089&quot;  
##  [6,] &quot;Malware/Scareware/Virus&quot; &quot;3,089&quot;  
##  [7,] &quot;Lottery/Sweepstakes&quot;     &quot;3,012&quot;  
##  [8,] &quot;IPR/Copyright and&quot;       &quot;2,644&quot;  
##  [9,] &quot;Counterfeit&quot;             &quot;&quot;       
## [10,] &quot;Ransomware&quot;              &quot;1,783&quot;  
## [11,] &quot;Crimes Against Children&quot; &quot;1,300&quot;  
## [12,] &quot;Denial of Service/TDoS&quot;  &quot;1,201&quot;  
## [13,] &quot;Civil Matter&quot;            &quot;1,057&quot;  
## [14,] &quot;Re-shipping&quot;             &quot;1,025&quot;  
## [15,] &quot;Charity&quot;                 &quot;436&quot;    
## [16,] &quot;Health Care Related&quot;     &quot;406&quot;    
## [17,] &quot;Gambling&quot;                &quot;203&quot;    
## [18,] &quot;Terrorism&quot;               &quot;177&quot;    
## [19,] &quot;Hacktivist&quot;              &quot;158&quot;    
## [20,] &quot;&quot;                        &quot;&quot;       
## [21,] &quot;&quot;                        &quot;&quot;       
## [22,] &quot;&quot;                        &quot;&quot;
</code>
</pre>
<p>Looking good. How does it look data-frame'd?</p>
<pre>
<code>
tab &lt;- as_data_frame(tab[[1]])

print(tab, n=50)
## # A tibble: 22 x 4
##    V1                                 V2               V3            V4   
##  1 &quot;&quot;                                 By Victim Cou nt &quot;&quot;            &quot;&quot;   
##  2 Crime Type                         Victims          Crime Type    Vict…
##  3 Non-Payment/Non-Delivery           84,079           Misrepresent… 5,437
##  4 Personal Data Breach               30,904           Corporate Da… 3,785
##  5 Phishing/Vishing/Smishing/Pharming 25,344           Investment    3,089
##  6 Overpayment                        23,135           Malware/Scar… 3,089
##  7 No Lead Value                      20,241           Lottery/Swee… 3,012
##  8 Identity Theft                     17,636           IPR/Copyrigh… 2,644
##  9 &quot;&quot;                                 &quot;&quot;               Counterfeit   &quot;&quot;   
## 10 Advanced Fee                       16,368           Ransomware    1,783
## 11 Harassment/Threats of Violence     16,194           Crimes Again… 1,300
## 12 Employment                         15,784           Denial of Se… 1,201
## 13 BEC/EAC                            15,690           Civil Matter  1,057
## 14 Confidence Fraud/Romance           15,372           Re-shipping   1,025
## 15 Credit Card Fraud                  15,220           Charity       436  
## 16 Extortion                          14,938           Health Care … 406  
## 17 Other                              14,023           Gambling      203  
## 18 Tech Support                       10,949           Terrorism     177  
## 19 Real Estate/Rental                 9,645            Hacktivist    158  
## 20 G overnment Impersonation          9,149            &quot;&quot;            &quot;&quot;   
## 21 &quot;&quot;                                 &quot;&quot;               &quot;&quot;            &quot;&quot;   
## 22 Descriptors*                       &quot;&quot;               &quot;&quot;            &quot;&quot;
</code>
</pre>
<p>Still pretty good. Cleaning it up is pretty simple from here. Just filter out some rows, parse some numbers, fix some chopped labels and boom - done:</p>
<pre>
<code>
tab &lt;- filter(tab[3:21,], !V2 == &quot;&quot;)

bind_rows(
  select(tab, crime = V1, n_victims = V2),
  select(tab, crime = V3, n_victims = V4)
) %>%
  filter(crime != &quot;&quot;) %>%
  mutate(n_victims = readr::parse_number(n_victims)) %>%
  mutate(crime = case_when(
    stri_detect_fixed(crime, &quot;G o&quot;) ~ &quot;Government Impersonation&quot;,
    stri_detect_fixed(crime, &quot;IPR/C&quot;) ~ &quot;IPR/Copyright and Counterfeit&quot;,
    TRUE ~ crime
  )) %>%
  print(n=50) -> ic3_2017_crimes_victim_ct
## # A tibble: 33 x 2
##    crime                              n_victims
##    </code></pre>