<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/the-evolution-of-data-literacy-at-the-u-s-department-of-energy-finding-power-grid-cyber-attacks-in-a-data-haystack/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/09/12/the-evolution-of-data-literacy-at-the-u-s-department-of-energy-finding-power-grid-cyber-attacks-in-a-data-haystack/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I was chatting with some cyber-mates at a recent event and the topic of cyber attacks on the U.S. power-grid came up (as it often does these days). The conversation was brief, but the topic made its way into active memory and resurfaced when I saw today’s <a rel="nofollow" href="https://tinyletter.com/data-is-plural/letters/data-is-plural-2018-09-12-edition" target="_blank">Data Is Plural</a> newsletter which noted that <em>“Utility companies are required to report major power outages and other “electric disturbance events” to the Department of Energy within a business day (or, depending on the type of event, sooner) of the incident. The federal agency then aggregates the reports annual summary datasets.”</em> (follow the links to the newsletter to get the URLs for the site since Jeremy deserves your <img alt="👀" src="https://i2.wp.com/s.w.org/images/core/emoji/11/72x72/1f440.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1">).</p>
<p>Many of us data nerds use the Data Is Plural newsletters as fodder for class assignments, blog posts or personal “data katas”. This time, I was after cyber attack data.</p>
<p>When you head to the annual reports URL, you’re greeted with a SharePoint-driven HTML table:</p>
<p><a rel="nofollow" href="https://rud.is/b/2018/09/12/the-evolution-of-data-literacy-at-the-u-s-department-of-energy-finding-power-grid-cyber-attacks-in-a-data-haystack/html-table/" target="_blank"><img data-orig-file="https://i1.wp.com/rud.is/b/wp-content/uploads/2018/09/html-table.png?fit=400%2C726&amp;ssl=1" data-orig-size="400,726" data-medium-file="https://i1.wp.com/rud.is/b/wp-content/uploads/2018/09/html-table.png?fit=165%2C300&amp;ssl=1" data-attachment-id="11486" data-image-meta="{" data-image-description="" src="https://i1.wp.com/rud.is/b/wp-content/uploads/2018/09/html-table.png?resize=400%2C726&ssl=1" data-permalink="https://rud.is/b/2018/09/12/the-evolution-of-data-literacy-at-the-u-s-department-of-energy-finding-power-grid-cyber-attacks-in-a-data-haystack/html-table/" data-large-file="https://i1.wp.com/rud.is/b/wp-content/uploads/2018/09/html-table.png?fit=400%2C726&amp;ssl=1" alt="" data-image-title="html-table" width="400" data-comments-opened="1" class="wp-image-11486" data-recalc-dims="1" height="726"></a></p>
<p>So, our options are PDF or XLS (and I mean <code>.xls</code>, too, they’re not modern <code>.xlsx</code> files). We’ll opt for the latter and cache them locally before working on them. One “gotcha” is that the <code>href</code>s look like this: <code>https://www.oe.netl.doe.gov/download.aspx?type=OE417XLS&amp;ID=78</code> — i.e. no filenames. <em>But</em>, the filenames come along for the ride when an HTTP <code>GET</code> or <code>HEAD</code> request is issued in a <a rel="nofollow" href="https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition" target="_blank"><code>content-disposition</code> response header</a>. We’ll use this metadata instead of siphoning off the year from the first column of the table:</p>
<pre><code>library(rvest)
library(readxl)
library(tidyverse)

doe &lt;- read_html(&quot;https://www.oe.netl.doe.gov/OE417_annual_summary.aspx&quot;)

dir.create(&quot;~/Data/doe-cache-dir&quot;, showWarnings = FALSE)

html_nodes(doe, xpath=&quot;.//a[contains(., 'XLS')]&quot;) %>%
  html_attr(&quot;href&quot;) %>%
  { .pb &lt;- progress_estimated(length(.)) ; . } %>% # we likely don't rly need progress bars tho
  walk(~{

    .pb$tick()$print()

    dl_url &lt;- sprintf(&quot;https://www.oe.netl.doe.gov/%s&quot;, .x)

    res &lt;- HEAD(dl_url) # so we can get the filename
    stop_for_status(res) # halt on network errors

    fil &lt;- str_replace(
      string = res$headers['content-disposition'],
      pattern = &quot;attachment; filename=&quot;,
      replacement = &quot;~/Data/doe-cache-dir/&quot;
    )

    if (!file.exists(fil)) { # this pattern allows us to issue a lightweight HTTP HEAD request, then cache and refresh w/o wasting server/our bandwidth/cpu
      res &lt;- GET(dl_url, httr::write_disk(fil))
      stop_for_status(res)
      Sys.sleep(5) # be kind to the server(s) but only if we're downloading data files since HEAD requests don't really tax services
    }

  })</code></pre>
<p>Let's do a quick check for the likelihood of uniformity. Some of these files go back to 2002 and I suspect they're more geared for &quot;printing&quot; (the PDF counterparts were a clue) than programmatic processing:</p>
<pre><code># check to see if the files are all the same (spoiler alert: they're not)
list.files(&quot;~/Data/doe-cache-dir&quot;, &quot;xls&quot;, full.names=TRUE) %>%
  map_df(~list(
    fil = basename(.x),
    ncols = read_xls(.x, col_names=FALSE, col_types=&quot;text&quot;) %>% ncol()
  )) -> cols_profile

cols_profile
## # A tibble: 17 x 2
##    fil                     ncols
##    </code></pre>