Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Useful Unix commands for exploring data

datavu.blogspot.com

Post reply on HN
Print view View original

41–50 of 156 posts

Re: Useful Unix commands for exploring data

#41

Post by mbreese »

napD wrote: post #6
sheetjs wrote: post #2

caveat: delimiter-based commands are not quote-aware. For example, this is a CSV line with two fields: foo,"bar,baz" However, the tools will treat it as 3 columns: $ echo 'foo,"bar,baz"' | awk -F, '{print NF}' 3

Is there any workaround?

Don't use CSV files...

If I'm working with a datafile where I expect the delimiter to be in one of the fields, there is something wrong.

This is one reason why I always work with tab delimited files. Having an actual tab character isn't very common in free-text fields, at least in the data that I work with. Commas on the other hand, are quite common. Why one would select a field separator that was common in your data is beyond me (I know it's historical).

Your data files might be different, in which case, maybe you should select a different field separator.

Otherwise, no, there is no work around. If you have to quote fields, then you can't use the normal unix command line tools that tokenize fields.

Re: Useful Unix commands for exploring data

#42

Post by CGamesPlay »

For working with complex CSV files, I highly recommend checking out CSVKit https://csvkit.readthedocs.org/en/0.8.0/

I've just started using it, and the only limitation I've so far encountered has been that there's no equivalent to awk (i.e. I want a way to evaluate a python expression on every line as part of a pipeline).

Re: Useful Unix commands for exploring data

#43

Post by jason_slack »

The author states:

    uniq -u movies.csv > temp.csv 
    mv temp.csv movie.csv 

    **Important thing to note here is uniq wont work if duplicate records are not adjacent. [Addition based on HN inputs]
Would the fix here be to sort the lines first using the `sort` command first? Then `uniq`?

Re: Useful Unix commands for exploring data

#44

Post by smorrow »

gav wrote: post #30
emeraldd wrote: post #4

uniq also doesn't deal well with duplicate records that aren't adjacent. You may need to do a sort before using it. sort | uniq But that can screw with your header lines, so be careful there two.

> But that can screw with your header lines, so be careful there two. F=filename; (head -n 1 $F ; tail -n +2 $F | sort -u) | sponge $F To get counts of duplicates, you can use: sort filename | uniq -c | awk '$1 != 1'

If you were piping into that bracketed expression (instead of using a real file), you'd need "line", "9 read", "sh -c 'read ln; echo $ln'", or "bash -c 'read; echo $REPLY'" in place of the head since head, sed, or anything else, might use buffered I/O and bite off more than it chews. (and then a plain cat in place of the tail)

"line" will compile anywhere but I only know it to be generally available on Linux. I think it's crazy that such a pipe-friendly way to extract a number of lines, and no more than that, isn't part of some standard.

Re: Useful Unix commands for exploring data

#45

Post by michaelmior »

barrkel wrote: post #31
michaelmior wrote: post #13

It's good to note that `uniq -u` does remove duplicates, but it doesn't output any instances of a line which has been duplicated. This is probably not clear to a lot of people reading this.

`uniq` removes duplicates; `uniq -u` only shows unique lines.

Exactly. The point wasn't clear from reading the article.

Re: Useful Unix commands for exploring data

#46

Post by _delirium »

rmc wrote: post #26
daurnimator wrote: post #22

Earlier quoted context omitted.

The utility to do this is called sponge. http://linux.die.net/man/1/sponge uniq -u movies.csv | sponge movies.csv

sponge is cool. But on debian/ubuntu, it's packaged up in moreutils, which includes a few helpful tools. However a programme called parallel is in moreutils, and that's not as powerful as GNU's parallel. So I often end up uninstalling sponge/moreutils. :(

There's some attempt underway to fix that fwiw: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=749355

Re: Useful Unix commands for exploring data

#47

Post by _delirium »

sheetjs wrote: post #5
emeraldd wrote: post #4

uniq also doesn't deal well with duplicate records that aren't adjacent. You may need to do a sort before using it. sort | uniq But that can screw with your header lines, so be careful there two.

You can do this without sorting: awk '!x[$0]++'

That's usually faster where possible, but it may cause problems on large data sets, since it loads the entire set of unique strings (and their counts) into an in-memory hash table.

Re: Useful Unix commands for exploring data

#49

Post by pselbert »

barrkel wrote: post #39
zo1 wrote: post #32

"While dealing with big genetic data sets I often got stuck with limitation of programming languages in terms of reading big files." Hate to sound like Steve-Jobs here, but: "You're using it wrong." Let me elaborate. If you're coming across limitations of "too-big" or "too-long" in your language of choice: Then you're just a few searches away from both being enlightened on how to solve your task at hand and on how yo…

Most scripting languages aren't multithreaded, and some aren't pipeline oriented by default. For example, working with file lines naively in Ruby means reading the whole lot into a giant array and doing transformations an array at a time, rather than in a streaming fashion. The shell gives you fairly safe concurrency and streaming for free. Personally, if it's a complex task, I generally write a tool such that it can…

You don't have to read all of a file into memory in Ruby. There are a number of facilities for reading only a portion of a file, readpartial[1] for example. Additionally, you have access to all of the native pipe[2] functionality as well. There are plenty of reasons to favor shell tools over Ruby, but those aren't some of them.

[1]: http://www.ruby-doc.org/core-2.1.2/IO.html#method-i-readpart... [2]: http://www.ruby-doc.org/core-2.1.2/IO.html#method-c-popen

Re: Useful Unix commands for exploring data

#50

Post by vsbuffalo »

zo1 wrote: post #32

"While dealing with big genetic data sets I often got stuck with limitation of programming languages in terms of reading big files." Hate to sound like Steve-Jobs here, but: "You're using it wrong." Let me elaborate. If you're coming across limitations of "too-big" or "too-long" in your language of choice: Then you're just a few searches away from both being enlightened on how to solve your task at hand and on how yo…

> Hate to sound like Steve-Jobs here, but: "You're using it wrong."

I don't quite agree — say this individual needs to sort a file by two columns. Should they really load everything into memory to call Python's sorted()? With large genomics datasets this isn't possible. Trying to reimplement sort's on-disk merge sort would be unnecessary and treacherous.

It's easy to forget how much engineer went into these core utilities — which can be useful when working with big files.

Post reply on HN