Command line tools for data analysis in Linux

So you’ve landed on some data you want to analyze ? The Linux terminal can be faster, more powerful, and more easily scriptable. So let’s try out a few of the many simple open source tools for data analysis and see how they work.

head and tail

First, let’s get started by getting a handle on the file. What’s in it? What does its format look like? You can use the cat command to display a file in the terminal, but that’s not going to do us much good if you’re working with files more than a few dozen lines.

Enter head and tail. Both are utilities for showing a specified number of lines from the top or bottom of the file. If you don’t specify the number of lines you want to see, you’ll get 10. Let’s try it with our file.

$ tail -n 3 jan2017articles.csv
02 Jan 2017,Article,Scott Nesbitt,3 tips for effectively using wikis for documentation,1,/article/17/1/tips-using-wiki-documentation,"Documentation, Wiki",710
02 Jan 2017,Article,Jen Wike Huger,The Opensource.com preview for January,0,/article/17/1/editorial-preview-january,,358
02 Jan 2017,Poll,Jason Baker,What is your open source New Year's resolution?,1,/poll/17/1/what-your-open-source-new-years-resolution,,186

Looking at those last three lines, I can pick out a date, author name, title, and a few other pieces of information immediately. But I don’t know what every column is. Let’s look at the top of the file and see if it has headers to explain what each column means:

$ head -n 1 jan2017articles.csv
Post date,Content type,Author,Title,Comment count,Path,Tags,Word count

Okay, that all makes sense now. Looks like we’ve got a list of articles with the date they were published, the type of content for each one, the author, title, number of comments, relative URL, the tags each article has, and the word count.

wc

That’s great, but how big is this file? Are we talking about dozens of articles we want to analyze, or hundreds, or even thousands? The wc command can help with that. Short for “word count,” wc can count the number of bytes, characters, words, or lines in the file. In our case, we want to know the number of lines.

$ wc -l jan2017articles.csv
93 jan2017articles.csv

And, there it is. 93 lines in this file; since we know the first row contained headers, we can surmise that this is a list of 92 articles.

grep

Okay, now let’s ask ourselves: Out of these 92 articles, how many of them were about a security topic? For our purposes, let’s say we’re interested in articles that mention security anywhere in the entry, whether in the title, the list of tags, or somewhere else. The grep tool can help us with that. With grep, you can search a file or other input for a particular pattern of characters. grep is an incredibly powerful tool, thanks to the regular expressions you can build to match very precise patterns. But for now, let’s just search for a simple string.

$ grep -i "security" jan2017articles.csv
30 Jan 2017,Article,Tiberius Hefflin,4 ways to improve your security online right now,3,/article/17/1/4-ways-improve-your-online-security,Security and encryption,1242
28 Jan 2017,Article,Subhashish Panigrahi,How communities in India support privacy and software freedom,0,/article/17/1/how-communities-india-support-privacy-software-freedom,Security and encryption,453
27 Jan 2017,Article,Alan Smithee,Data Privacy Day 2017: Solutions for everyday privacy,5,/article/17/1/every-day-privacy,"Big data, Security and encryption",1424
04 Jan 2017,Article,Daniel J Walsh,50 ways to avoid getting hacked in 2017,14,/article/17/1/yearbook-50-ways-avoid-getting-hacked,"Yearbook, 2016 Open Source Yearbook, Security and encryption, Containers, Docker, Linux",2143

The format we used was grep, followed by the -i flag (which tells grep not to be case sensitive), followed by the pattern we wanted to search for, and then the file in which we were searching. It looks like we had four security-related articles last month. But let’s imagine we got a much longer list than we could easily count. Using a pipe, we could combine grep with the wc command we just learned about above, to get a count of the total lines mentioning security.

$ grep -i "security" jan2017articles.csv | wc -l
4

In this case, wc took the output of our grep command, and used it as its input, without ever having to worry about saving it anywhere first. This is why piping input and output, in particular when combined with a little shell scripting, makes the terminal such a powerful tool for data analysis.

tr

A CSV file is a pretty helpful format for many analysis scenarios, but what if you need to convert the file to a different format for use in a different application? Maybe you need tab separators instead of commas, or maybe you want to change them to some HTML so that you can use the data output in a table. The tr command can help you with that, by translating from one type of character to another. Like the other examples, you can also pipe input and output to this command.

Let’s try another multi-part example, by creating a TSV (tab separated values) file with just the articles that published on January 20.

$ grep "20 Jan 2017" jan2017articles.csv | tr ',' '\t' > jan20only.tsv

What’s going on here? First, we searched for the date in question, using grep. We piped this output to the tr command, which we used to replace the commas with tabs (denoted with ‘\t’). But where did it go? Well, the > character redirected the output to our new file instead of the screen. All of this work in one command sequence. We can then verify that the jan20only.tsv file contains the data that we expected.

$ cat jan20only.tsv
20 Jan 2017     Article Kushal Das      5 ways to expand your project's contributor base        2       /article/17/1/expand-project-contributor-base    Getting started 690
20 Jan 2017     Article D Ruth Bavousett        How to write web apps in R with Shiny   2       /article/17/1/writing-new-web-apps-shiny Web development 218
20 Jan 2017     Article Jason Baker     "Top 5: Shell scripting  the Cinnamon Linux desktop environment  and more"       0       /article/17/1/top-5-january-20  Top 5   214
20 Jan 2017     Article Tracy Miranda   How is your community promoting diversity?      1       /article/17/1/take-action-diversity-tech Diversity and inclusion 1007

sort

What if we wanted to learn more details about a particular column? Which article in our new list of articles is the longest? Let’s build on our last example. Now that we have a list of articles from just January 20, we can use the sort command to sort by the word count column. Of course, we don’t strictly speaking need an intermediate file here; we could have piped the output of the last command instead. But sometimes it’s simply easier to break long steps into smaller ones rather than creating gigantic chains of commands.

$ sort -nr -t$'\t' -k8 jan20only.tsv | head -n 1
20 Jan 2017     Article Tracy Miranda   How is your community promoting diversity?      1       /article/17/1/take-action-diversity-tech Diversity and inclusion 1007

This is another long example, so let’s break down what’s happening. FIrst, we’re using the sort command to sort by the number of words. The -nr option tells sort to do a numeric sort, and to return the results in reverse order (largest to smallest). The next -t$’\t’ tells sort that the delimiter is the tab (‘\t’). (You can read why you need the dollar sign here; in short, it’s to tell the shell that this is a string that needs processing to turn the \n into an actual tab). The -k8 portion of the command tells sort to use the eighth column, which is the column for word count in our example.

Finally, the whole output is piped to head with instructions just to show the top line, which is our result, the article from this file with the highest word count.

sed

You might want to select specific lines of a file. sed, short for stream editor, is one way to do this. What if you wanted to combine multiple files that all had headers? You would only want one set of headers to appear for the whole file, so you would need a way to scrape out the extras. Or what if you wanted to grab only a particular range of lines? sed is your tool. It’s also a great way to do a bulk find and replace a file.

Let’s create a new file with no headers from our list of articles, suitable for combining with other files (if, for example, I had a different file for every month and wanted to put them together).

$ sed '1 d' jan2017articles.csv > jan17no_headers.csv

The ‘1 d’ option tells sed to delete the first line. sed is far more powerful than this, and I’d recommend reading up further on its replacement powers.

cut

What if, instead of wanting to remove a row, I wanted to remove a column? What if I wanted to pick out just one column? Let’s create a new list of authors for our example we built above.

$ cut -d',' -f3 jan17no_headers.csv > authors.txt

In this simple example, we told cut with -d’,’ that this is a comma-delimited file, that we wanted the third column (-f3), and to send the output to a new file called authors.txt.

uniq

That last example left us with a list of authors, but, how many unique authors are on that list? How many articles did each author write? Enter uniq. With uniq, you can easily find out. Let’s sort the file, find uniques, then outputs a file that has a count of the number of articles written by each author.

sort authors.txt | uniq -c > authors-sorted.txt

Glimpsing at the file, we can now see how many articles each author had. Let’s just look at the last three lines to be sure it worked.

$ tail -n3 authors-sorted.txt
      1 Tracy Miranda
      1 Veer Muchandi
      3 VM (Vicky) Brasseur

awk

Let’s look at one more tool in our command-line data analysis toolbelt today, awk. awk is another one of those tools that I’m going to give far too little credit to; it’s really a powerhouse worth exploring on its own. It is another great tool for replacement, but also much more. Let’s go back to the TSV file we made earlier of just the January 20 articles, and use that to create a new list of just the authors of those articles, along with the number of words each author wrote.

$ awk -F "\t" '{print $3 "  " $NF}' jan20only.tsv
Kushal Das  690
D Ruth Bavousett  218
Jason Baker  214
Tracy Miranda  1007

What’s going on here? The -F “\t” we pass to awk simply tells it that we’re working with tab-separated data. Within the braces, we’re actually telling awk to execute just a little bit of code. We’re telling it to print the third column with $3, and then the last column with $NF (the “number of fields”), and place two spaces between them to make it a little more legible.