{"id":11483,"date":"2018-09-12T11:33:07","date_gmt":"2018-09-12T16:33:07","guid":{"rendered":"https:\/\/rud.is\/b\/?p=11483"},"modified":"2018-09-12T11:39:03","modified_gmt":"2018-09-12T16:39:03","slug":"the-evolution-of-data-literacy-at-the-u-s-department-of-energy-finding-power-grid-cyber-attacks-in-a-data-haystack","status":"publish","type":"post","link":"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\/","title":{"rendered":"The Evolution of Data Literacy at the U.S. Department of Energy + Finding Power Grid Cyber Attacks in a Data Haystack"},"content":{"rendered":"

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 Data Is Plural<\/a> newsletter which noted that “Utility companies are required to report major power outages and other \u201celectric disturbance events\u201d 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 ?).<\/p>\n

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>\n

When you head to the annual reports URL, you’re greeted with a SharePoint-driven HTML table:<\/p>\n

\"\"<\/a><\/p>\n

So, our options are PDF or XLS (and I mean .xls<\/code>, too, they’re not modern .xlsx<\/code> files). We’ll opt for the latter and cache them locally before working on them. One “gotcha” is that the href<\/code>s look like this: https:\/\/www.oe.netl.doe.gov\/download.aspx?type=OE417XLS&ID=78<\/code> — i.e. no filenames. But<\/em>, the filenames come along for the ride when an HTTP GET<\/code> or HEAD<\/code> request is issued in a 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>\n

library(rvest)\nlibrary(readxl)\nlibrary(tidyverse)\n\ndoe <- read_html(\"https:\/\/www.oe.netl.doe.gov\/OE417_annual_summary.aspx\")\n\ndir.create(\"~\/Data\/doe-cache-dir\", showWarnings = FALSE)\n\nhtml_nodes(doe, xpath=\".\/\/a[contains(., 'XLS')]\") %>%\n  html_attr(\"href\") %>%\n  { .pb <<- progress_estimated(length(.)) ; . } %>% # we likely don't rly need progress bars tho\n  walk(~{\n\n    .pb$tick()$print()\n\n    dl_url <- sprintf(\"https:\/\/www.oe.netl.doe.gov\/%s\", .x)\n\n    res <- HEAD(dl_url) # so we can get the filename\n    stop_for_status(res) # halt on network errors\n\n    fil <- str_replace(\n      string = res$headers['content-disposition'],\n      pattern = \"attachment; filename=\",\n      replacement = \"~\/Data\/doe-cache-dir\/\"\n    )\n\n    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\n      res <- GET(dl_url, httr::write_disk(fil))\n      stop_for_status(res)\n      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\n    }\n\n  })<\/code><\/pre>\n
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 \"printing\" (the PDF counterparts were a clue) than programmatic processing:<\/p>\n
# check to see if the files are all the same (spoiler alert: they're not)\nlist.files(\"~\/Data\/doe-cache-dir\", \"xls\", full.names=TRUE) %>%\n  map_df(~list(\n    fil = basename(.x),\n    ncols = read_xls(.x, col_names=FALSE, col_types=\"text\") %>% ncol()\n  )) -> cols_profile\n\ncols_profile\n## # A tibble: 17 x 2\n##    fil                     ncols\n##                       \n##  1 2002_Annual_Summary.xls     8\n##  2 2003_Annual_Summary.xls     8\n##  3 2004_Annual_Summary.xls     8\n##  4 2005_Annual_Summary.xls     8\n##  5 2006_Annual_Summary.xls     8\n##  6 2007_Annual_Summary.xls     8\n##  7 2008_Annual_Summary.xls     8\n##  8 2009_Annual_Summary.xls     8\n##  9 2010_Annual_Summary.xls     8\n## 10 2011_Annual_Summary.xls     9\n## 11 2012_Annual_Summary.xls     9\n## 12 2013_Annual_Summary.xls     9\n## 13 2014_Annual_Summary.xls     9\n## 14 2015_Annual_Summary.xls    11\n## 15 2016_Annual_Summary.xls    11\n## 16 2017_Annual_Summary.xls    11\n## 17 2018_Annual_Summary.xls    11<\/code><\/pre>\n
O_o<\/p>\n
At this point, I paused and wanted to see what was going on in the minds of the DoE staffers charged with releasing this data.<\/p>\n\n\t\t