Useful Unix commands for exploring data • Hacker News Board

Re: Useful Unix commands for exploring data

#121

Post by collyw » Wed, Aug 27, 2014, 9:04 PM UTC

aks_c wrote: post #117

zo1 wrote: post #100

Earlier quoted context omitted.

As someone that deals with large datasets on a Database + Python daily, I'm not quite sure what you mean. You'll have to explain it to me what "not a good idea is", or "basic data exploring".

Consider I get 10 files of size 3 GB every week, which I am supposed to filter based on certain column using a reference index and forward to my colleague. Before filtering I also want to check how the file looks like: column names, first few records etc. I can use something like following to explore few rows and few columns. $$ awk '{print $1,$3,$5}' file | head -10 And then I can use something like sed with referen…

I have been doing Python the last few years, but went back to Perl for this sort of thing recently. You can start with a one liner, and if it gets complicated, just turn it into a proper script. As well as the Unix commands mentioned. Its just faster when you don't know what you are dealing with yet.

Re: Useful Unix commands for exploring data

#122

Post by ciupicri » Wed, Aug 27, 2014, 9:31 PM UTC

mprovost wrote: post #66

ngcazz wrote: post #55

No one gives a shit about cut. $ man 1 cut

I'm always surprised when people recommend awk for pulling delimited sections of lines out of a file, cut is so much easier to work with.

That's because cut sucks when fields can be separated by multiple space or tab characters.

    # printf '1 2\t3' | cut -f 2
    3
    # printf '1 2\t3' | awk '{print $2}'
    2
    # printf '1 2\t\t3' | cut -f 2
    
    # printf '1 2\t\t3' | awk '{print $2}'
    2

Re: Useful Unix commands for exploring data

#123

Post by CGamesPlay » Wed, Aug 27, 2014, 9:38 PM UTC

vdm wrote: post #111

CGamesPlay wrote: post #42

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).

Get words starting with "and" $ cat /usr/share/dict/words | py -fx 're.match(r"and", x)' | head -5 and andante andante's andantes andiron https://github.com/Russell91/pythonpy

Sorry, I meant: remove the characters "$" and "," from the 3rd column of a CSV file. Obviously the CSV file is quoted, since it has commas in the 3rd column, and so awk is no longer an acceptable solution.

Re: Useful Unix commands for exploring data

#124

Post by mzs » Wed, Aug 27, 2014, 9:59 PM UTC

icebraining wrote: post #56

jason_slack wrote: post #43

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`?

Yes, but not first, rather instead. "sort -u" both sorts and hides duplicates.

except when you need uniq -c

Re: Useful Unix commands for exploring data

#125

Post by laumars » Wed, Aug 27, 2014, 10:19 PM UTC

derekp7 wrote: post #105

epistasis wrote: post #38

Earlier quoted context omitted.

Yeah, I think calling him the "unenlightened" one is pretty off base here. For performing the tasks outlined by his examples, Unix utilities are easier for the user as well as executing faster than writing your own code in a general purpose programming language, unless one puts in the time to tune the implementation. One could rebuild AWK in C and get similar performance, but why not just use some extremely simple AW…

This is one area where I wish the Unix philosophy (reuse of tools) was taken a bit further. Too me, every command should be callable as a C library function. That way you wouldn't have to parse the human readable output through a pipe. Not only that, there needs to be both human-readable, as well as machine-readable output to all commands. For example I would love to be able to call "ps" from another script and easil…

Shell scripts can be pretty powerful if you know what you're doing, but I do agree that sometimes the shell script paradigm can be more of a hurdle than a help.

However your point about every command being a callable as a C library is kind of possible already. Some commands do have native language libraries (eg libcurl), but you could also fork out to those ELFs if you're feeling really brave (though in all practicality - it's little worse than writing a shell script to begin with). In fact there's times I've been known to cheat with Perl and run (for example):

    (my $hostname = `hostname`) =~ s/\n//g;

because it's quicker and easier to throw together than using the proper Perl libraries (yeah, it's pretty nasty from an academic perspective, but the additional footprint is minimal while the development time saved is significant.

Of course, any such code that's used regularly and/or depended on will be cleaned up as and when I have the time.

As for your XML or JSON parsing; the same theory as above could be applied:

    use JSON::Parse 'parse_json';
    my $json = `curl --silent http://birthdays.com/myfriends.json`;
    my $bdays = parse_json($json);
    print "derekp7's birthday is $bdays{derekp7}";

Obviously these aren't best practices, but if it only running locally (ie this isn't part of a CGI (etc) script that's web accessible) and gets the job done in a hurry then I can't see why I you shouldn't use that for ad hoc reporting.

Re: Useful Unix commands for exploring data

#126

Post by bmsherman » Thu, Aug 28, 2014, 12:04 AM UTC

I may as well plug my little program, which takes numbers read line-by-line in standard input and outputs a live-updating histogram (and some summary statistics) in the console!

https://github.com/bmsherman/LiveHistogram

It's useful if you want to, say, get a quick feeling of the distribution of numbers in some column of text.

Re: Useful Unix commands for exploring data

#127

Post by etrain » Thu, Aug 28, 2014, 1:09 AM UTC

Some more tips from someone who does this every day.

1) Be careful with CSV files and UNIX tools - most big CSV files with text fields have some subset of fields that are text quoted and character-escaped. This means that you might have "," in the middle of a string. Anything (like cut or awk) that depends on comma as a delimiter will not handle this situation well.

2) "cut" has shorter, easier to remember syntax than awk for selecting fields from a delimited file.

3) Did you know that you can do a database-style join directly in UNIX with common command line tools? See "join" - assumes your input files are sorted by join key.

4) As others have said - you almost invevitably want to run sort before you run uniq, since uniq only works on adjacent records.

5) sed doesn't get enough love: sed '1d' to delete the first line of a file. Useful for removing those pesky headers that interfere with later steps. Not to mention regex replacing, etc.

6) By the time you're doing most of this, you should probably be using python or R.

Re: Useful Unix commands for exploring data

#128

Post by vacri » Thu, Aug 28, 2014, 1:37 AM UTC

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. :(

moreutils for my usage contains 'chronic', which prepended to a command, stops cron from alerting on any non-error output. Big fan.

Re: Useful Unix commands for exploring data

#129

Post by [deleted] » Thu, Aug 28, 2014, 1:43 AM UTC

ciupicri wrote: post #122

mprovost wrote: post #66

Earlier quoted context omitted.

I'm always surprised when people recommend awk for pulling delimited sections of lines out of a file, cut is so much easier to work with.

That's because cut sucks when fields can be separated by multiple space or tab characters. # printf '1 2\t3' | cut -f 2 3 # printf '1 2\t3' | awk '{print $2}' 2 # printf '1 2\t\t3' | cut -f 2 # printf '1 2\t\t3' | awk '{print $2}' 2

[deleted]

Re: Useful Unix commands for exploring data

#130

Post by a3n » Thu, Aug 28, 2014, 2:45 AM UTC

jeroenjanssens wrote: post #77

zerop wrote: post #48

7 command-line tools for data science http://jeroenjanssens.com/2013/09/19/seven-command-line-tool... Useful Unix commands for data science http://www.gregreda.com/2013/07/15/unix-commands-for-data-sc...

First blog post was the inspiration for a book, which is almost finished: http://datascienceatthecommandline.com

O'Reilly is having a 50% sale on all ebooks through 9 September.

http://oreilly.com/

I just bought the early release of that exact book for $13.60, which was 60% off, because you get 60% off if you order $100 worth of prediscount ebooks.

http://shop.oreilly.com/product/0636920032823.do

When the book is finished you get the final version. It's mostly already finished.

"With Early Release ebooks, you get books in their earliest form — the author's raw and unedited content as he or she writes — so you can take advantage of these technologies long before the official release of these titles. You'll also receive updates when significant changes are made, new chapters as they're written, and the final ebook bundle."