Contrived Clueless Command Line Data Crunching¶

Hints¶

The NASA data is unstructured text that looks like structured data.

Here is what the first 8 lines and the bottom 5 lines look like:

http://stash.compciv.org/2017/nasa-gistemp-sample.txt

Global Surface Air Temperature Anomaly (C) (Base: 1951-1980)
------------------------------------------------------------
 Year  Annual_Mean 5-year_Mean
--------------------------------
 1880     -0.55         *
 1881     -0.55         *
 1882     -0.46     -0.58
 1883     -0.53     -0.60
 2014      0.85      0.91
 2015      0.95         *
 2016      1.23         *

------------------------------------

We want the first two columns (Year and Annual_Mean) and none of the boilerplate, such as the headers or the ---------- lines.

As further evidence that this isn’t structured data, paste the above text into any spreadsheet:

We have to use a regex to bend it to our desired format. We want the Year and the Annual_Mean. What’s that as a regex pattern?

• A 4 digit number (the year)
• Followed by one-or-more spaces
• Followed by an optional negative sign (remember when the Earth’s temperature dropped annually? I don’t)
• Followed by one or more digits
• Followed by a literal dot character
• Followed by one or more digits

Here’s one way to do that pattern; finish it up with a capturing group and the use of ack with --output:

$ curl -s http://stash.compciv.org/2017/nasa-gistemp.txt \
    | ack -o '\d{4} +-?\d+\.\d+'

Useful regular expression techniques:

• optionality with ? http://www.regular-expressions.info/alternation.html
• limited repetition with {n} http://www.regular-expressions.info/repeat.html
• one-or-more matching with + http://www.regular-expressions.info/repeat.html
• Capturing groups with ( ) http://www.regular-expressions.info/brackets.html

Because the data file is 100+ lines, you may want to test against this 13-line version just so it’s easier to predict what you want to see as the answer:

http://stash.compciv.org/2017/nasa-gistemp-sample.txt

To see an example of how I iteratively create the regex pattern, step-by-step, check out this animated demo:

https://asciinema.org/a/100135

Remember your UP key!

Hints¶

This is similar to the previous problem of unstructured text data from NASA. But you have to write a pattern that matches only the numbers in the top half of a file. Easiest way to do that: just get the first 57 lines of the file, and you don’t have to worry about the “Future Scenarios” data at all.

This is a good use for the head program:

$ curl -s http://stash.compciv.org/2017/nasa-ghgases.txt \
    | head -n 57

Hints¶

Use csvcut -n to first figure out which columns correspond to the eviction reasons of “Breach” or “Nuisance”. You also need to use the “File Date” column in order to extract the year of the eviction.

curl -s http://stash.compciv.org/2017/sf-evictions.csv | csvcut -n

Hints¶

This is a situation where ack, or any other line-by-line pattern matcher won’t work. CSV data can have multiple lines if a column has newline characters in it, and ack only works on one line at a time.

The csvkit suite of tools has a tool named csvgrep for exactly this occasion. Use the -c flag to specify which column you want to match against, and -r to specify the regex pattern.

“$100,000,000” is a nine-digit number:

$ curl -s http://stash.compciv.org/2017/menlo-park-building-permits.csv \
    | csvgrep -c 8 -r '\d{9,}'

Hints¶

Start off by using csvcut to trim the data to the only columns you need – year and vote total:

$ curl -s http://stash.compciv.org/2017/us-presidential-election-county-results.csv \
    | csvcut -c year,vote_total \
    | ack '^2004'

Benford’s Law states that given a set of naturally-incrementing numbers, we should expect 30% of the first-digits of the numbers to be 1. Use a regex to extract the leading character of every number-string.

Hints¶

Note that I don’t require printing the output as CSV (you probably have memorized the steps to do so by now).

The police shootings data, which the Washington Post collected by hand, was the backbone of their 2015 Pulitzer Award: http://www.pulitzer.org/prize-winners-by-year/2016

Related reading:

Inside the Washington Post’s police shootings database: An oral history

Hints¶

The Guardian is ostensibly keeping track of the same thing as the Washington Post, but the numbers are far different. Consider the difference in scope of “fatal encounters” that the two organizations use. Also, note that both have different ways of labeling race/ethnicity – in every real-world dataset, data collectors will have varying opinions on the methodology.

Related reading:

• Inside The Counted: how Guardian US has tracked police killings nationwide
• Reddit AMA with the Guardian reporters

Hints¶

Again, another example of extracting data from HTML/XML via regex, which is almost never a good solution...though in this case, the pattern is simple enough. If you know nothing about HTML, at least you know that it is just text.

Hints¶

There is no column for just “Race”, but there is a column of race and gender in which each entry is exactly 2 capital letters. Use a regex to capture that, and then filter for just the letters that refer to race.