# Ad Hoc Data Analysis From The Unix Command Line/Printable version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Ad_Hoc_Data_Analysis_From_The_Unix_Command_Line

# Preliminaries
## Formatting

These typesetting conventions will be used when presenting example interactions at the command line:
The "$ " is the shell prompt. What you type is shown in boldface; command output is in regular type.

## Example data

I will use the following sample files in the examples.

### The Unix password file

The password file can be found in /etc/passwd. Every user on the system has one line (record) in the file. Each record has seven fields separated by colon (':') characters. The fields are username, encrypted password, userid, default group, gecos, home directory and default shell. We can look at the first few lines with the head command, which prints just the first few lines of a file. Correspondingly, the tail command prints just the last few lines.

### Census data

The US Census releases Public Use Microdata Samples (PUMS) on its website. We will use the 1% sample of Washington state's data, the file pums_53.dat, which can be downloaded here
Important note: The format of this data file is described in an excel spreadsheet that can be downloaded here.

## Developer efficiency vs. computer efficiency

The techniques discussed here are usually extremely efficient in terms of developer time, but generally less efficient in terms of compute resources (CPU, I/O, memory). This kind of brute force and ignorance may be inelegant, but when you don't yet understand the scope of your problem, it is usually best to spend 30 seconds writing a program that will run for 3 hours than vice versa.

## The online manual

The "man" command displays information about a given command (colloquially referred to as the command's "man page"). The online man pages are an extremely valuable resource; if you do any serious work with the commands presented here, you'll eventually read all their man pages top to bottom. In Unix literature the man page for a command (or function, or file) is typically referred to as command(n). The number "n" specifies a section of the manual to disambiguate entries which exist in multiple sections. So, passwd(1) is the man page for the passwd command, and passwd(5) is the man page for the passwd file. On a Linux system you ask for a certain section of the manual by giving the section number as the first argument as in "man 5 passwd". Here's what the man command has to say about itself:
Standard Input, Standard Output, Redirection and Pipes →

# Standard Input, Standard Output, Redirection and Pipes

"This is the Unix philosophy: Write programs that do one thing and do it well. Write programs to work together. Write programs to handle text streams, because that is a universal interface."—Doug McIlroy, the inventor of Unix pipes
The commands I'm going to talk about here are called filters. Data passes through them and they modify it a bit on the way. These commands read data from their "standard input" and write data to their "standard output." By default, standard input is your keyboard and standard output is your screen. For example, the tr command is a filter that translates one set of characters to another. This invocation of tr turns all lower case characters to upper case characters:
Ctrl-d is how you tell the command from the keyboard that you're done entering input.
You can tell your shell to connect standard output to a file instead of your screen using the ">" operator. The term for this is "redirection". One would talk about "redirecting" tr's output to a file. Later you can use the cat command to write the file to your screen.
Many Unix commands that take a file as an argument will read from standard input if not given a file. For example, the grep command searches a file for a string and prints the lines that match. If I wanted to find my entry in the password file I might say:
But I could also redirect a file to grep's standard input using "<" operator. You can see that the "<" and ">" operators are like little arrows that indicate the flow of data.
You can use the pipe "|" operator to connect the standard output of one command to the standard input of the next. The cat command reads a file and writes it to its standard output, so yet another way to find my entry in the password file is:
For a slightly more interesting example, the mail command will send a message it reads from standard input. Let's send my entry in the password file to me in an email.

# Using output with headers

In many situations, you end up with output that has a first line that is a header describing the data, and subsequent lines that are the data. An example is ps:
If you wish to manipulate the data but not the header use tail with -n switch to start at line 2. For example:
This output shows only "bash" processes (because of grep)
← Preliminaries · Counting Part 1 - grep and wc →

## References
# Counting Part 1 - grep and wc

    "90% of data analysis is counting" - John Rauser
...well, at least once you've figured out the right question to ask, which is, perhaps, the other 90%.

## Example - Counting the size of a population

The simplest command for counting things is wc, which stands for word count. By default, wc prints the number of lines, words, and characters in a file.
Nearly always we just want to count the number of lines (records), which can be done by giving the -l option to wc.

## Example - Using grep to select a subset

So, recalling that this is a 1% sample, there were 8.5 million people in Washington as of the 2000 census? Nope, the census data has two kinds of records, one for households and one for persons. The first character of a record, an H or P, indicates which kind of record it is. We can grep for and count just person records like this:
The caret '^' means that the 'P' must occur at the beginning of the line. So there were about 5.9 million people in Washington State in 2000. Also interesting, the average household had 59,150/(85,025-59,150) = 2.3 people.
← Standard Input, Standard Output, Redirection and Pipes · Picking The Data Apart With cut →

# Picking The Data Apart With cut
## Fixed width data

How many households had just 1 person? Referring to the file layout, we see that the 106th and 107th characters of a household record indicate the number of people in the household. We can use the cut command to pull out just that bit of data from each record. The argument -c106-107 instructs cut to print the 106th through 107th characters of each line. The head command prints just the first few lines of a file (or its standard input).
You can give cut a comma separated list to pull out multiple ranges. To see the household id along with the number of occupants of the household:
The -c argument is used for working with so called "fixed-width" data. Data where the columns of a record are found at certain offset in bytes from the beginning of a record. Fixed width data abounds on a Unix system. ls -l writes its output in a fixed width format:
As does ps:
Returning to the question of how many 1 person households are there in Washington:
7,192, or about 28% of households have only one occupant.

## Delimited data

In delimited data, elements of a record are separated by a special 'delimiter' character. In the password file, fields are delimited by colons:
The 7th column of the password file is the user's login shell. How many people use bash as their shell?
You can give either -c or -f a comma separated list, so to see a few users that use tcsh as their shell:

### Tricky delimiters

The space character is a common delimiter. Unfortunately, your shell probably discards all extra whitespace on the command line. You can sneak a space character past your shell by wrapping it in quotes, like: cut -d" " -f 5
The tab character is another common delimiter. It can be hard to spot, because on the screen it just looks like any other white space. The od (octal dump) command can give you insight into the precise formatting of a file. For instance I have a file which maps first names to genders (with 95% probability). When casually inspected, it looks like fixed width data:
But on closer inspection there are tab characters delimiting the columns:
The first thing to do is read your system's manpage on "cut": it may already delimit by tab by default. If not, it requires a bit of trickery to get a tab character past your shell to the cut command. First, many shells have a feature called tab completion; when you hit tab they don't actually insert a tab, instead they attempt to figure out which file, directory or command you want and type that instead. In many shells you can overcome this special functionality by typing a control-v first. Whatever character you type after the control-v is literally inserted. Like a space character, you need to protect the tab character with quotes or the shell will discard it like any other white space separating pieces of the command line.
So to get the ratio of male first names to female first names I might run the following commands. Between the double quotes I typed control-v and then hit tab.
Apparently there's much more variation in female names than male names.
If your system's cut command delimits on tab, the above command becomes simply cut -f2 gender.txt.
← Counting Part 1 - grep and wc · Joining The Data with join →

# Joining The Data with join

Please note - Join assumes that that input data is sorted based on the key on which the join is going to take place.

## Delimited data

In delimited data, elements of a record are separated by a special 'delimiter' character. In the CSV files, fields are delimited by commas or tabs:
Explanation of options:
0 denotes the match (join) field (needed when using "-a") 1.2 denotes field 2 from file 1 ("j1") 2.2 denotes field 2 from file 2 ("j2").
Using the "-a" option creates a full outer join as in SQL.
This command must be given two and only two input files.

## Multi-file Joins

To join several files you can loop through them.
File "J" is now the full outer join of "j1", "j2".
and so on through j4, j5, ...
For many files this is best done with a loop

## Sorted Data Note

join assumes that the input data has been sorted by the field to be joined. See section on sort for details. • Counting Part 2 - sort and uniq
Credits: Some text adapted from Ted Harding's email to the R mailing list.
← Picking The Data Apart With cut · Counting Part 2 - sort and uniq →

# Counting Part 2 - sort and uniq

So far we've seen how to use cut, grep and wc to select and count records with certain qualities. But each set of records we'd like to count requires a separate command, as with counting the numbers of male and female names in the most recent example. Combining the uniq and sort commands allows us to count many groups at once.

## uniq and sort

The uniq command squashes out contiguous duplicate lines. That is, it copies from its standard input to its standard output, but if a line is identical to the immediately preceding line, the duplicate line is not written. For example:
Note that 'a' is written twice because uniq compares only to the immediately preceding line. If the data is sorted first, we get each distinct record just once:
Finally, giving the -c option causes uniq to write counts associated with each distinct entry:
Sorting a CSV file by an arbitrary column is easy as well:

## Example - Creating a frequency table

The combination of sort and uniq -c is extremely powerful. It allows one to create frequency tables from virtually any record oriented text data. Returning to the name to gender mapping of the previous chapter, we could have gotten the count of male and female names in one command like this:

## Example - Creating another frequency table

And returning to the census data, we can now easily compute the complete distribution of occupants per household:

## Example - Verifying a primary key

This is a good opportunity to point out a big benefit of being able to play with data in this fashion. It allows you to quickly spot potential problems in a dataset. In the above example, why are there 1,796 households with 0 occupants? As another example of quickly verifying the integrity of data, let's make sure that household id is truly a unique identifier:
This grep invocation will print only lines that do not (because of the -v flag) begin with a series of spaces followed by a 1 (the count from uniq -c) followed by a tab (entered using the control-v trick). Since the number of lines written is zero, we know that each household id occurs once and only once in the file.
The technique of grepping uniq's output for lines with a certain count is generally useful. One other common application is finding the set of overlapping (duplicated) keys in a pair of files by grepping the output of uniq -c for lines that begin with a 2.

## Example - A frequency table sorted by most common category

Throwing an extra sort on the end of the pipeline will sort the frequency table so that the most common class is at the top (or bottom). This is useful when data is categorical and does not have a natural order. You'll want to give sort the -n option so that it sorts the counts numerically instead of lexically, and I like to give the -r option to reverse the sort so that the output is sorted in descending order, but this just a stylistic issue. For example, here is the distribution of household heating fuel from most common to least common:
Type 3, electricity, is most common, followed by type 1, gas. Type 7 is solar power.

## Converting the frequency table to proper CSV

The output of uniq -c is not in proper CSV form. This makes is necessary to convert the output if further operations on the output are wanted. Here we use a bit of inline perl to rewrite the lines and reverse the order of the fields.
← Joining The Data with join · Rewriting The Data With Inline perl →

# Rewriting The Data With Inline perl
## Command Line perl

A tutorial on perl is beyond the scope of this document; if you don't know perl, you should learn at least a little bit. If you invoke perl like perl -n -e '#a perl statement' the -n option causes perl to wrap your -e argument in a implicit while loop like this:
This loop reads standard input a line at a time into the variable $_, and then executes the statement(s) give by the -e argument. Given -p instead of -n, perl to adds a print statement to the loop as well:

## Example - Using perl to create an indicator variable

Education level is recorded in columns 53-54 as ordered set of categories, where 11 and above indicates a college degree. Let's condense this to a single indicator variable for completed college or not. The raw data:
And once passed through the perl script:
And the final result:
About 36% of Washingtonians have a college degree.

## Example - computing conditional probability of membership in two sets

Let's look at the relationship between education level and whether or not people ride their bikes to work. People's mode of transportation to work is encoded as a series of categories in columns 191-192, where category 9 indicates a bicycle. We'll use an inline perl script to rewrite both education level and mode of transportation:
55/(55+36447) = 0.15% of non college educated people ride their bike to work. 111/(111+20219) = 0.56% of college educated people ride their bike to work.
Sociological interpretation is left as an exercise for the reader.

## Example - A histogram with custom bucket size

Suppose we wanted to take a look at distribution of personal incomes. The normal trick of sort and uniq would work, but the personal income in the census data has resolution down to the $10 level, so the output would be very long and it would be hard to quickly see the pattern. We can use perl to round the income data down to the nearest $10,000 on the fly. Before the inline perl script:
And after:
And finally, the distribution (up to $100,000). The extra grep [0-9] ensures that blank records are not considered in the distribution.

## Example - Finding the median (or any percentile) of a distribution

If we sort all the incomes in order and had a way to pluck out the middle number, we could easily get the median. I'll give two ways to do this. The first uses cat -n. If given the -n option, cat prepends line numbers to each line. We see that there are 46,359 non blank records, so the 23179th one in sorted order is the median.
An even simpler method, using head and tail:
The median income in Washington state in 2000 was $19,900.

## Example - Finding the average of a distribution

What about the average? One way to compute the average is to accumulate a running sum with perl, and do the division by hand at the end:
$1314603988/ 46359 = $28357.0393666818
You could also get perl to do this division with an END block which perl will execute only after it has exhausted standard input:
← Counting Part 2 - sort and uniq · Quick Plotting With gnuplot →

# Quick Plotting With gnuplot
## Example - creating a scatter plot

Does the early bird get the worm? Let's look at the relationship between the time a person leaves for work and their income. Income is recorded in columns 297-303, and the time a person leaves for work is recorded in columns 196-198, encoded in ten minute intervals. This pipeline extracts, cleans and formats the data:
The greps knock out records for which either field is null, and the perl script inserts a space between the two columns so gnuplot can parse the columns apart. Plotting in gnuplot is simple:
And the resulting plot:
Recall that 0 on the x-axis is midnight, and 20 is 200 minutes after midnight or about 3:20am. Increased density in the beginning of the traditional 1st and 2nd shift periods is apparent. Folks who work regular business hours clearly have higher incomes. It would be interesting to compute the average income in each time bucket, but that makes a pretty hairy command line perl script. Here is it in all its gruesome glory:
You can plot the result for yourself if you're curious.

## Example - Creating a bar chart with gnuplot

Let's look at historic immigration rates among Washingtonians. Year of immigration is recorded in columns 78-81, and 0000 means the person is a native born citizen. We can apply the usual tricks with cut, grep, sort, and uniq, but it's a bit hard to see the patterns when scrolling back and forth in text output, it would nicer if we could see a plot.
Gnuplot is a fine graphing tool for this purpose, but it wants the category label to come first, and the count to come second, so we need to write a perl script to reverse uniq's output and stick the result in a file. See perlrun(1) for details on the -a and -F options to perl.
Now we can make a bar chart from the contents of the file with gnuplot.
Here's the graph gnuplot creates:
Be a bit careful interpreting this plot, only people who are still alive can be counted, so it naturally goes up and to the right (people who immigrated more recently have a better chance of still being alive). That said, there seems to have been an increase in immigration after the end of World War II, and also a spike after the end if the Vietnam war. I remain at a loss to explain the spike around 1980, consult your local historian.

## External links

* http://gnuplot.sourceforge.net/demo/
set term win; set grid;A1=-300;A2=250;n=360;z=2500;splot(x**2)/(A1**2) + (y**2)/(A2**2) - ((2*x*y)/(A1*A2))*cos(n-z) - (sin(n-z))**2
← Rewriting The Data With Inline perl · Appendices →

# Appendices
## Appendix A: pcalc source code

A perl read-eval-print loop. This makes a very handy calculator on the command line. Example usage:
Source:

## Appendix B: Random unfinished ideas

Ideas too good to delete, but that aren't fleshed out.

### Micro shell scripts from the command line

Example - which .so has the object I want?

### James' xargs trick

James uses echo with xargs and feeds one xargs' output into another xargs in clever ways to build up complex command lines.

### Example - Finding duplicate keys in two files

← Quick Plotting With gnuplot