Useful Unix commands for data science

http://www.gregreda.com/2013/07/15/unix-commands-for-data-science/

by gjreda • 13 years ago

221 106 13 years ago

gnosis4• 13 years ago

I was hoping to see an article about some neat new utilities specifically tailored for doing advanced data analysis.

Instead this is a set of basic examples of bog-standard tools that every newbie *nix user should be already familiar with: cat, awk, head, tail, wc, grep, sed, sort, uniq

pmelendez• 13 years ago

"tools that every newbie nix user should be already familiar with"

The key word is should... you might be surprised how many "not newbie" nix users are not aware of those commands or how using them in this fashion. Specially awk.

D9u• 13 years ago

I forgot all about wc, but thanks to this article I may remember it the next time I need it.

Thanks!

jlgaddis2• 13 years ago

Lots of people are familiar with the "basics" of each of these commands but many of them (awk, sed) are very powerful utilities that can do much, much more than it appears at first glance.

Osmium• 13 years ago

Don't forget there are only ever going to be more unix newbies in the world. It's not like they're a dying breed. There are more people than ever who have never been exposed to unix tools who might benefit from them (myself included several years ago).

robinson-wall• 13 years ago

Indeed. I'm going to start calling myself a data scientist now, instead of a sysadmin. See if I can't get a raise.

jbert4• 13 years ago

I like slicing and dicing with awk, grep and friends too.

One thing I find odd that you have to drop to a full language (awk, perl etc) to sum a column of numbers. Am I missing a utility?

  echo "1\n2\n3\n" | sum # should print 6 with hyphothetical sum command

I suppose more generally you could have a 'fold initial op' and:

  echo "1\n2\n3\n4\n" | fold 0 +   # should print 10
  echo "1\n2\n3\n4\n" | fold 1 \*  # should print 24

But I guess at that point you're close enough to using awk/perk/whatever anyway. Which probably answers my question.

gnosis4• 13 years ago

Here's one way to do this with the standard "dc" (RPN calculator) utility:

  echo "1\n2\n3\n+\n+\np\n" | dc -

Or, a little more legibly:

[Edited to fix bug]

Not sure how to automate this to sum 1000 values without needing to explicitly insert 999 + signs, though. Haven't explored dc in depth myself yet. There's probably some way to do it with a macro or something, but it may not be pretty.

phaemon• 13 years ago

You could do:

echo -e "1\n2\n3\n4" | paste -sd+ | bc

kind of cheating though! :-)

andrus• 13 years ago

Just for grins:

    $ alias sum="xargs | tr ' ' '+' | bc"
    $ echo -e "1\n2\n3\n" | sum
    6

mjn4• 13 years ago

If you consider the use of dc/bc as in the other solutions to be cheating, you can use unary-encoded integers...

   alias sum='xargs -I{} sh -c "head -c {} < /dev/zero" | wc -c'

chubot• 13 years ago

Yeah I wrote my own sum utility in Python... the syntax is just sum 1 or sum 2 for the column, with a -d delimiter flag. In retrospect I guess it could have been a one line awk script. But yeah if you are doing this kind of data-processing, it makes sense to have a hg/git repo of aliases and tiny commands that you sync around from machine to machine. You shouldn't have to write the sum more than once.

Another useful one is "hist" which is sort | uniq -c | sort -n -r.

jbert4• 13 years ago

I've never aliased it, but yes I use your 'hist' a lot. Useful for things like "categorise log errors" etc.

Does everyone else edit command history, stacking up 'grep -v xxxx' in the pipeline to remove noise?

If I'm working on a new pipeline, my normal workflow is something like:

  head file     # See some representative lines
  head file | grep goodstuff
  head file | grep good stuff | grep -v badstuff
  head file | grep ... | grep ... | sed -e 's/cut out/bits/' -e 's/i dont/want/'
  head file | grep ... | grep ... | sed -e 's/cut out/bits/' -e 's/i dont/want/' | awk '{print $3}' # get a col
  head file | grep ... | grep ... | sed -e 's/cut out/bits/' -e 's/i dont/want/' | awk '{print $3}' | sort | uniq -c | sort -nr  # histogram as parent

Then I edit the 'head' into a 'cat' and handle the whole file. Basically all done with bash history editing (I'm a 'set -o vi' person for vi keybindings in bash, emacs is fine too :-)

whacker• 13 years ago

    paste -sd+|bc

atondwal• 13 years ago

    echo "1\n2\n3\n" | tr '\n' + | bc

FreeFull• 13 years ago

Doesn't work. By default, echo doesn't translate \n into a newline, so you have to add the -e flag. Then, bc doesn't like the extra plusses at the end, so you have to either add the -n flag to echo and remove the last \n, or somehow trim the newlines from the end beforehand.

codezero• 13 years ago

Please be very careful doing math with bash and awk...

  cat data.csv | awk -F "|" '{ sum += $4 } END { printf "%.2f\n", sum }'

From that command, it's unclear whether the sum will be accurate, it depends on the inputs and on the precision of awk. See (D.3 Floating-Point Number Caveats): http://www.delorie.com/gnu/docs/gawk/gawk_260.html

minimax2• 13 years ago

Please be very careful doing math with bash and awk...

I don't see how that's more true for awk than it is for any other programming language. Awk uses double precision floating point for all numeric values, which isn't a horrible choice for a catch-all numeric type.

jonjenk• 13 years ago

If you're interested in this general topic check out this Wikibook from John Rauser. He's a data scientist at Pinterest.

http://en.wikibooks.org/wiki/Ad_Hoc_Data_Analysis_From_The_U...

res0nat0r2• 13 years ago

If this interests you, you should check out Joyents new Manta service which lets you do this type of thing on your data via their infrastructure. It's really cool.

http://www.joyent.com/products/manta

jlgaddis2• 13 years ago

If I needed to do this type of thing on 10 TB of data, it would probably take me longer to get the data to them than it would to just run it on my own hardware.

Apparently there's a need for it, though, or it wouldn't exist.

mcavage2• 13 years ago

Disclaimer: I work at Joyent, on Manta.

This entire HN thread is a perfect example of why we built Manta. Lots of engineers/scientists/sysadmins/... already know how to (elegantly) process data using Unix and augmenting with scripts. Manta isn't about always needing to work on a 10TB dataset (you can), but about it being always available, and stored ready to go. I know we can't live without it for running our own systems -- all logs in the entire Joyent fleet are rotated and archived in Manta, and we can perform both recurring/automated and ad-hoc analysis on the dataset, without worrying about storage shares, or ETL'ing from cold storage to compute, etc. And you can sample as little as much or as much as you want. At least to us (and I've run several large distributed systems in my career), that has tremendous value, and we believe it does for others as well. And that's just one use case (log processing).

Like I said, disclaimers/bias/etc.

zeidrich• 13 years ago

If you need them, Windows also has most of those tools somehow replicated in Powershell. For instance, the initial example can be replicated with:

Get-Content .\data.csv | %{[int]$total+=$_.Split('|')[3]; } ; Write-Host "$total"

dredmorbius2• 13 years ago

Or you can actually use the Linux commands by installing Cygwin. Pretty much my first conscious action when I wake up stranded on a desert Windows system.

pseut• 13 years ago

Why Cygwin instead of MinGW? (I can't remember why I prefer MinGW, but at some point I had a reason).

minimax2• 13 years ago

AWK is worth learning completely. It hits a real sweet spot in terms of minimizing the number of lines of code needed to write useful programs in the world of quasi-structured (not quite CSV but not completely free form) data. You can learn the whole language and become proficient in an afternoon.

I recommend "The AWK Programming Language" by Aho, Kernighan, and Weinberger, though it seems to be listed for a hilariously high price on Amazon at the moment. Maybe try to pick up a used copy.

daemon13• 13 years ago

I recall there was a pointer to an old great AWK tutorial some time ago - smth along the lines 'how to approach awk language....' - anyone kept the link?

foobarbazqux3• 13 years ago

This is the first hit for awk tutorial and it's all you need.

http://www.grymoire.com/Unix/Awk.html

jnazario• 13 years ago

i wrote one a long time ago:

http://linuxgazette.net/67/nazario.html

i still use a buttload of awk for data science type uses.

kingmanaz• 13 years ago

I recommend "The AWK Programming Language" by Aho, Kernighan, and Weinberger

I concur with this recommendation. "The AWK Programming Language", at little over 100 pages, is a classic of programming language instruction. The book jumps right into use cases, it does not waste one's time. This book should be required reading for anyone contemplating writing a handbook on any programming language; my CS bookshelf would be several feet thinner and several times more informative.

cjg_• 13 years ago

Sadly it seems very expensive now, $95 on Amazon...

EvanKelly• 13 years ago

Here is Kernighan's personal help file on AWK:

http://www.cs.princeton.edu/courses/archive/spr08/cos333/awk...

It deals with things he forgets or needs to remind himself of.

If you're interested in his other personal tutorials, they are here:

http://www.cs.princeton.edu/courses/archive/spr08/cos333/tut...

username423• 13 years ago

Kernighan's personal help file is excellent. If you need more, you should switch to a more powerful language.

username423• 13 years ago

I advise not learning more than basic usage of awk and to spend the time on more versatile languages. You can do very neat tricks with sed and awk, but when the problems become more complex, it is a lot faster to use a smarter language. And if you know well this language, you will discover that it may also be very concise for relatively simpler tasks.

When Perl was created, one of its advertised goal was to avoid all the time lost trying to work around the limitations of awk, sed and shell.

pstuart• 13 years ago

Starts off with unnecessary use of cat, e.g., cat file | awk 'cmds'.

One can simply do awk 'cmds' file.

mds• 13 years ago

I know purists always complain about unnecessary cats, but I always find it useful to start with "head" or "tail" in the first position to figure out my pipeline, and then replace it with cat when it's all working.

And if the extra cat is actually making a measurable difference, maybe that's a good signal that it's time to rewrite it in C.

sleepydog2• 13 years ago

You can do with simple IO redirection. For example, the arbitrary pipeline

    $ cat data.txt | awk '{ print $2+$4,$0 }'|sort|sed '/^0/d'

can be written as

    $ <data.txt awk '{ print $2+$4,$0 }'|sort|sed '/^0/d'

_kst_• 13 years ago

A commenter on the article pointed out the "Useless use of cat".

What most users probably don't realize is that the redirection can be anywhere on the line, not just at the beginning. Putting an input redirection at the beginning of the command can make the data flow clearer: from the input file, through the command, to stdout:

    < data.csv awk -F "|" '{ sum += $4 } END { printf "%.2f\n", sum }'

(This only works for simple commands; you can't do `< file if blah; then foo; else bar; fi`)

alayne• 13 years ago

"Useless use of cat" is one of those boring pedantic comments that makes me cringe. Who cares? It's usually much more straightforward to build a pipeline from left to right, particularly for people who are just learning this stuff.

coolj• 13 years ago

It's usually much more straightforward to build a pipeline from left to right, particularly for people who are just learning this stuff.

True, however, people pointing out UUOC are in fact pointing out that you should not be building a pipeline at all. If you want to apply an awk / sed / wc / whatever command to a file, then you should just do that instead of piping it through a extraneous command.

Sure, as people always mention, in your actual workflow you might have a cat or grep already, and are building a pipeline incrementally; there's no reason to remove previous stuff to be "pure" or whatever. But if you're giving a canonical example, there's no reason to add unneeded commands.

thrownaway24242• 13 years ago

Actually useful data science tips for unix users.

  Make all your commands 3x faster:
   export LC_ALL=C

  Actually use the 32 CPUs you paid for:
   sort --parallel=32 ...
   xargs -P32 ...

gnosis4• 13 years ago

Could you expand on why

   export LC_ALL=C

would "make all your commands 3x faster"?

foobarbazqux3• 13 years ago

Gnu grep is or was very slow with the UTF-8 locale. Not sure about other commands, perhaps anything that processes text, awk and sed maybe?

barrkel• 13 years ago

If your text manipulation programs are locale-aware, they may be interpreting the input as a multibyte encoding, and need to do a lot more work in preprocessing to get semantically correct operation. For example, a Unicode-aware grep may understand more forms of equivalence, similarly for sorting. See e.g. http://en.wikipedia.org/wiki/Unicode_equivalence

With the C locale, text is more or less treated as plain bytes.

bcantrill• 13 years ago

Actually, it was more like 2000X[1] -- and I believe that it still stands as Brendan Gregg's biggest performance win.

[1] http://dtrace.org/blogs/brendan/2011/12/08/2000x-performance...

foobarbazqux3• 13 years ago

Also export MAKEOPTS=-j33

dahart• 13 years ago

Actually use the 32 CPUs you paid for:

gnu parallel FTW!

binarycrusader• 13 years ago

Note that the standard Solaris versions of many commands are substantially faster than their GNU equivalents in the 'C' and multi-byte locales so this advice doesn't necessarily apply.

That's part of why Solaris continues to use them in favour of GNU alternatives (although the GNU alternatives are available easily in /usr/gnu/bin).

xntrk• 13 years ago

I'm surprised there was no mention of cut -d. It's good for simple stuff where you don't need all of awk.

throwwiffle• 13 years ago

and paste - quick and simple for working with columns

westurner• 13 years ago

The "Text Processing" category of this list of unix utilities is also helpful: http://en.wikipedia.org/wiki/List_of_Unix_programs

BashReduce is a pretty cool application of many of these utilities.

dbbolton• 13 years ago

In some cases, (command line) Perl will actually process piped text faster than awk or even sed. I'm not sure about arithmetic though.

makmanalp• 13 years ago

Everyone forgets the brilliant and sometimes crazy BSD ones:

  - Column: Create columns / tables from input data
  - tr: substitute / delete chars
  - join: like a database join, but for text files
  - comm: like diff, but you can use it programmatically to choose if a       line is in one file, or another, or both.
  - paste: put file lines side-by-side
  - rs: reshape arrays
  - jot: generate random or sequence data
  - expand: replace tabs / spaces

wiredfool• 13 years ago

Join is really one of those awesome unknown commands.

mjn4• 13 years ago

Looks like 6 of those 8 are in GNU coreutils as well (and therefore can be assumed present on just about any modern Unix). 'rs' and 'jot' are the two missing from most default Linux installs. On Debian you can install them via the packages 'rs' and 'athena-jot'.

merlincorey• 13 years ago

'jot' is pretty sweet, especially for creating ranges for iteration and random numerical data for testing arguments and such.

Check out the man page for a few snippets: http://www.unix.com/man-page/FreeBSD/1/jot/

It is the older, more flexible uncle of gnu's 'seq' command: http://administratosphere.wordpress.com/2009/01/23/using-bsd...

mountaineer• 13 years ago

Nice overview. Some guys at my previous company used to show off stuff like this one line recommender (slide 6)

http://www.slideshare.net/strands/strands-presentation-at-re...

bch• 13 years ago

cat data.csv | awk -F "|" '{ sum += $4 } END { printf "%.2f\n", sum }'

"Don't pipe a cat".

My test doesn't show a speed improvement, but there are less processes running, and less memory consumed.

  bch:~ bch$ jot 999999999 2 99 > data.dat


  bch:~ bch$ time cat data.dat  | awk '{sum +=$1} END {printf "sum: %d\n", sum}'
  sum: 50499999412

  real 6m21.111s
  user 6m15.506s
  sys  0m5.711s

  PID    COMMAND      %CPU  TIME     #TH   #WQ  #PORTS #MREGS RPRVT  RSHRD  RSIZE  VPRVT  VSIZE  PGRP  PPID  STATE    UID  FAULTS    COW      MSGSENT     MSGRECV
  22342  awk          100.7 05:11.84 1/1   0    17     21     52K    212K   340K   17M    2378M  22341 22306 running  501  311       49       73          36
  22341  cat          1.1   00:03.94 1/1   0    17     21     272K   212K   548K   17M    2378M  22341 22306 running  501  268       51       73          36

==============

  bch:~ bch$ time awk '{sum +=$1} END {printf "sum: %d\n", sum}' ./data.dat
  sum: 50499999412

  real 6m24.023s
  user 6m13.828s
  sys  0m2.774s

  PID    COMMAND      %CPU  TIME     #TH   #WQ  #PORTS #MREGS RPRVT  RSHRD  RSIZE  VPRVT  VSIZE  PGRP  PPID  STATE    UID  FAULTS    COW      MSGSENT     MSGRECV
  22373  awk          100.0 00:30.16 1/1   0    17     21     276K   212K   624K   17M    2378M  22373 22306 running  501  256       46       73          36

aidos• 13 years ago

Sometimes I like to start with cat so I can easily swap for zcat when changing to gripped input.

nnnnni• 13 years ago

Hmm… Now we need a zawk.

jbert4• 13 years ago

Agree. Or actually I start with a 'head -100' so I don't handle too much data in my pipeline until it's ready.

trimbo• 13 years ago

Crush tools.

https://code.google.com/p/crush-tools/

sleepydog2• 13 years ago

One trick I like to do is to feed two files into awk; /dev/stdin and some other file I'm interested in. Here's an example: lookup the subject names of a list of serial numbers in an openSSL index.txt

    #!/bin/sh
    
    printf %s\\n "$@" | awk -F'\t' '
        FILE == "/dev/stdin" {
            needle[$0] = 1
            next
        }

        needle[$4] {
            print $NF
        }
    ' /dev/stdin /etc/pki/CA/index.txt

I find myself using this idiom (feeding data from a file to awk and selecting it with data from standard input) again and again. It's a great way to scale shell scripts to take multiple arguments while avoiding opening the same file N times, or doing clunky things with awk's -v flag.

samspenc• 13 years ago

Wow, I have to say I use all these commands - these are also particularly useful while testing Hadoop streaming jobs since you can test locally on your shell using "cat | map | sort | reduce" (replace cat with head if you want) and then actually run it in Hadoop.

snorkel• 13 years ago

Data science? Is that we're calling DBA work nowadays?

nfg• 13 years ago

Worth noting that:

    grep -A n -B n

is more easily written:

    grep -C n

If you think "C" for "context" this is easier to remember too.

DEinspanjer• 13 years ago

One of my favorite little tools that makes all these others better is pv -- PipeViewer.

Use it any place in a pipeline to see a progress meter on stderr. Very handy when grepping through a bunch of big log files looking for stuff. Here is a quick strawman example:

  pv /data/*.log.gz | zgrep -c 'hello world'
  241MiB 0:00:15 [15.8MiB/s] [==>       ]  2% ETA 0:12:12

pjungwir• 13 years ago

Shameless plug: here is a similar tool I wrote that prints not a progress bar but the contents flowing through the pipe, to help in debugging:

https://github.com/pjungwir/stutter

gwu784• 13 years ago

BSD has a progress meter utility. It's called progress(1).

   progress -zf /data/*.log.gz grep -c 'hello world'

   progress -f /data/*.log.gz zgrep -c 'hello world'

The second form will show the progress of the decompression process.

You can also adjust buffer size, set the length for the time estimate (otherwise we have to fstat the input), and display progress to stderr instead of stdout.

gaadd33• 13 years ago

Which BSD has that? I don't seem to find it in my FreeBSD installs.

noloqy• 13 years ago

This reminds me of page 213 (175 in the book's numbering) of the Unix Haters Handbook, found at http://web.mit.edu/~simsong/www/ugh.pdf

reyan• 13 years ago

A short and nice read is Unix for Poets by Kenneth Ward Church: http://www.stanford.edu/class/cs124/kwc-unix-for-poets.pdf

hafabnew• 13 years ago

I actually was about to post this -- this guide is great. As an undergraduate, this was what was given to us to help demonstrate above-introductory command line tools/pipes.

wasd2• 13 years ago

I've been using Ubuntu for about a year now and although I feel comfortable doing a lot of things with the CL, I'm not sure if I really know enough about *nix. I wish there was a was a website with the 20-30 most useful unix commands and very clear language as to what they do with examples. Although, I've used all the tools in this post, I still enjoyed the use of example.

bendmorris• 13 years ago

Software Carpentry provides a good overview with examples: http://software-carpentry.org/4_0/shell/index.html

wasd2• 13 years ago

Looks pretty solid but a bit on the simple side. Thanks for the link.

Rickasaurus• 13 years ago

"data science"... seriously?

kamaal• 13 years ago

>Writing a script in python/ruby/perl/whatever would probably take a few minutes and then even more time for the script to actually complete.

Thankfully you can also write a Perl one liner. Which most of the times is far powerful than awk.

dpatru• 13 years ago

sum the 0th field: perl -lane '$a += $F[0]; END{ print $a; }'

gwu784• 13 years ago

"Imagine you have a 4.2GB CSV file." "

All you need... is the sum of all values in one particular column."

In that case, if speed was paramount, I'd use Kona or kdb. Unquestionably, k is the best tool for that particular job.

lotsofcows2• 13 years ago

Really? In a recent test of a whole bunch of languages, scripting, compiled and JVM (but not Kona or kdb), our awk test was beaten only by C. awk was so far ahead, its run time beat other's compile + run time.

gwu784• 13 years ago

Yes, really. Nothing I know of beats the speed of k for this column-oriented type of task. Kernighan himself tested it against awk many years ago and if I recall it was generally faster even in that set of tests.

Where can I see your experimental design? I'd like to try to replicate your results.

Useful Unix commands for data science

Related Stories