Manipulating Data on Linux

First, even tho you might have generated the data in your lab, you might not know what kind of data it is. While it’s not foolproof, a tool that may help is called file. It tries to answer the question: "What kind of file is it?" Unlike the Windows approach that maps file name endings to a particular type (filename.typ), file actually peeks inside the file to see if there are any diagnostic characteristics, so especially if the file has been renamed or name-mangled in translation, it can be very helpful.

ie

We’re going to use a tab-delimited compressed data file called red+blue_all.txt.gz. Download it (in Firefox) by right-clicking on the link and select Save Link As...

Save it to the $DDIR directory you created for this exercise as described above.

Or, get it directly with the very useful command wget, which does the above in one line.

We can get the total bytes with ls

Or in human-readable form with:

Now lets decompress it..

We can extract more information using wc (wordcount)

wc shows that it’s 385,239 lines 1,926,195 words and 26,213,442 characters

In some cases, you’ll want to use a spreadsheet application to review a spreadsheet. (For the few of you who don’t know how to use spreadsheets for data analysis (as opposed to just looking at columns of data), there is a Linux-oriented tutorial at the oft-referenced Software Carpentry site.)

While Excel is the acknowledged leader in this application area, there are some very good native free spreadsheets available on Linux that behave very similarly to Excel. There’s a good exposition on spreadsheet history as well as links to free and commercial spreadsheets for Linux here and a good comparison of various spreadsheets.

For normal use, I’d suggest one of:

The links will give you more information on them and the spreadsheet modules in each will let you view and edit most Excel spreadsheets. In addition, there are 2 Mac-native version of OpenOffice: one a port from the OpenOffice group called:

A NeoOffice-supplied comparison chart may be worth reviewing.

While both OpenOffice and MS Office have bulked up considerably in their officially stated capacity, often spreadsheets are not appropriate to the kind of data processing we want to do. So how to extract the data?

The first way is the most direct and may in the end be the easiest - open the files (oowriter for Word files, oocalc for Excel files) and export the data as plain text

For Word files:

For Excel files:

Or use the much faster method below.

The above method of extracting data from a binary MS file requires a fair amount of clicking and mousing. The Linux Way would be to use a commandline utility to do it in one line.

Common, free GUI editors that are a good choice for viewing such tabular data are nedit, jedit, and kate. All have unlimited horizontal scroll and rectangular copy and paste, which makes them useful for copying rectangular chunks of data. nedit and jedit are also easily scriptable and can record keystrokes to replay for repeated actions. nedit has a backlighting feature which can visually distinguish tabs and spaces, sometimes helpful in debugging a data file. nedit is also one of the easiest editors to customize by adding menu items or right-click menus to operate on the entire window or selection. jedit is written in Java so it’s portable across platforms and has tremendous support in the form of various plugins.

All these editors run on the server and export their windows to your workstation if you provide a mechanism to display X11 graphics. If you’re using a Mac or WinPC, use the free x2go client which will suffice.

And while it is despised and adored in equal measure, the xemacs editor can also do just about anything you want to do, if you learn enough about it. There is an optional free add-in for statistics call ESS (for the SPlus/R languages, as well as SAS and Stata). emacs in all its forms is as much a lifestyle choice as an editor.

cat (short for concatenate) is one of the simplest Linux text utilities. It simply dumps the contents of the named file (or files) to STDOUT, normally the terminal screen. However, because it dumps the file(s) to STDOUT, it can also be used to concatenate multiple files into one. If we have 2 files, greens.txt and blues.txt and we want to concatentate them…

These critters are called pagers - utilities that allow you to page thru text files in the terminal window. less is more than more in my view, but your mileage may vary. These pagers allow you to queue up a series of files to view, can scroll sideways, allow search by regular expression, show progression thru a file, spawn editors, and many more things. Here’s a Video example

These two utilities perform similar functions - they allow you view the beginning (head) or end (tail) of a file. Both can be used to select contiguous ends of a file and pipe it to another file or pager. tail -f can also be used to view the end of a file continuously (as when you have a program continuously generating output to a file and you want to watch the progress). These are also described in more detail near the end of the IBM DeveloperWorks tutorial.

A common requirement for munging is to extract all the file but the 1st few lines (which are often headers or comments).

The stanza below shows the beginning of the CyberT_C+E_DataSet file we used previously. Note that it contains 2 line at the top prefixed by 's. We COULD filter out these lines by using grep -v to filter them out: 'grep -v ^ CyberT_C+E_DataSet (and this example accentuates that you should always prefix comments with '#'s for this reason), but how would we do this if the lines weren’t prefixed like that?

One way is to use tail with its -n+ option. This passes the end of the file with the TOP n lines filtered out.

You can think of the grep family as a dicer which selects rows from data files. Below, we’ll consider the slicers, the utilities that select columns or fields. In combination, they form a majority of the operations for sub-selecting data out of text files that will be the majority of the data you’ll deal with until you transition to binary file formats such as HDF5, FITS, various image formats, etc.

The grep family is possibly the most used set of tools in the Unix/Linux world. These (elegant) utilities are used to search files for (ugly) patterns of text called regular expressions (aka regexes) and can select or omit a line based on the matching of that regex. The most popular of these is the basic grep and it along with some of its bretheren are described well in Wikipedia. Another variant which behaves similarly but with one big difference is agrep, or approximate grep which can search for patterns with variable numbers of errors, such as might be expected in a file resulting from an optical scan or with typo’s. Baeza-Yates and Navarro’s nrgrep is even faster, if not as flexible (or differently flexible), than agrep. Stay with the basic grep until you need something else.

A grep variant would be used to extract all the lines from a file that had a particular phrase or pattern embedded in it.

For example:

So the file has 600 lines. If we wanted only the lines that had the identifier mur in it, we’d use grep to filter those out.

Ughhh. Too many columns and the columns are too uneven and thus hard to see which is which. Let’s try to make this easier, by previewing that output in a columnizer, immediately below.

cols, column, & columns attempt to columnize the data fed into them to make the data more easily interpretable. While they are similar in intent, they have slightly different intents

Here’s an example (continued from above) with cols

Now you can get a better sense of how the data is arranged and select the columns you want since the column numbers are listed on the 1st line and extend out to the end of the file.

cut, scut, & awk are columnar slicing utilities, which allow you to slice vertical columns of characters (or fields) out of a file, based on character offset or delimiters (aka tokens).

To select columns 1, 2, 3, and 4 using awk, and viewing with cols, counting from 1 (--ch=1)

Note that awk numbers columns from 1; many other tools number from 0. Caveat Scripter.

paste can join 2 files side by side to provide a horizontal concatenation. ie:

Note that paste inserted a TAB character between the 2 files, each of which used spaces between each field. See also the IBM DeveloperWorks tutorial

scut and join are more powerful variant of columnizers that allow you to do relational joins between different files based on a common field. Both needs identical field values to do the join.

The scut manual provides this example of a join. Because scut does in-memory joins using hashes, it doesn’t require pre-sorting, but has lower capacity than join (both files have to fit into RAM).

join provides some of the functionality of both paste and scut. Here’s an example page.

For much more powerful relational operations, SQLite is a fully featured relational engine that can do this reasonably easily (see below). A good example of join is here

pr is actually a printing utility that is mentioned here because for some tasks especially related to presentation, it can join files together in formats that are impossible to do using any other utility. For example if you want the width of the printing to expand to a nonstandard width or want to columnize the output in a particular way or modify the width of tab spacing, pr may be able to do what you need.

As noted above, some applications can open such files directly, obviating the need to use external tools to extract or extend a data set in this format. However, for those time when you have to subset, extend, or otherwise modify such a dataset, there are a couple of tools that can make that job much easier. Since this document is aimed at the beginner rather than the expert, I’ll keep this section brief, but realize that these extremely powerful tools are available (and free!).

Some valuable tools for dealing with these formats:

Relational DBs are those that can relate data from one table (or data structure) to another. A relational DB is composed of tables which are internally related and can be joined or related to other tables with more distant information. For example, woodworker’s DB might be composed of DB tables that describe PowerTools, Handtools, Plans, Glues, Fasteners, Finishes, Woods, and Injuries, with interconnecting relationships among the tables. The Woods table definition might look like this:

See the Wikipedia entry on Relational Databases for more.

Octave is a near-clone of MATLAB in terms of functionality and is widely used and well-supported. It does not have a native GUI, altho there are externally developed GUI’s that provide some functionality to get you started. While it is largely compatible with MATLAB code, it is also considerably slower than MATLAB, especially for iterative code (loops, tests, etc). If you can vectorize your code, it approaches MATLAB speed.

SciLab is a large project founded by INRIA, supported by a consortium of ~18 academic and commercial companies. It is similar to MATLAB, but is not perfectly language-compatible. It does, however, come with a MATLAB to SciLab converter. It supports Mac/Win/Lin in 32/64bits, has a full GUI, and like MATLAB, has a very large number of toolboxes. The older versions had a tcl/tk-based interface; the most recent version (6.0) now has a more modern GTK-based GUI with some output based on scicos and JgraphX.

For MATLAB users wanting to try or convert to SciLab, the current (6.0) version of SciLab has a built-in MATLAB to SciLab conversion tool. And there is an extensive PDF document that describes using SciLab from a MATLAB user’s perspective.

(SciPy has incorporated a number of Python packages, including:

to create a very flexible programming system for Mathematics. NumPy is one of the more heavily developed math libraries in the OSS world and SciPy benefits strongly from that development.

Jupyter is a beneficial mutation of the above-mentioned IPython which allows a well-defined and supported notebook function (much like a Mathematica notebook) to be used by a very large number of mathematical, statistical, and general purpose languages, such as Python (of course), R, Julia, SAS, Ruby, Javascript, Scala, and all the rest

SAGE is a more formal system and is fairly well-funded to continue development. It too uses SciPy and NumPy for some of the underlying Math plumbing. It can be used either as a self-contained commandline-driven system or as a client, connecting to a webserver, where your notebook is hosted. This arrangement allows you to test-drive the system using SAGE’s own web service. Another non-trivial feature is that it is distributed as a complete system; you can install it on a thumb drive and take the entire system, including your work, with you as you travel from place to place. However, the Jupyter notebook also supports the SAGE system, so it’s probably best to try it as a Jupyter plug-in rather than a stand-alone system.

Python, iPython & NumPy. Python is a general purpose interpreted programming language and while not the most popular, it is quite suitable for research, especially when coupled with interactive iPython shell/debugger and the numerical module Numpy. Like Java (and unlike Perl) it is strongly object-oriented, which can make it easier to understand and extend. iPython is an interactive commandline debugger (there are also many free GUI debuggers such as eric), which has a number of features that make it very easy and powerful. The Numpy module is Python’s scientific numeric package which makes it very easy to deal with multidimensional arrays and their manipulation as well as wrapping libraries from other languages to include as Python modules - as an example, see HOWTO_Pythonize_FORTRAN.html. I’ve written a simple skeleton example in Python to show how Python does some useful things.

Perl & the Perl Data Language (PDL). Perl is also a powerful general purpose language, with probably the best integration of regular expressions. If parsing files, substituting strings, and manipulating text is your bag, then Perl is your sewing machine. The PDL is an additional set of routines that provides much the same functionality as Numpy does for Python. While both Perl and Python have their own set of self-updating and library-searching routines, Perl’s CPAN is more mature and better debugged than Python’s easy_install system, altho both are very good. Perl and Python are about equally well-supported when it comes to interacting with databases and network activity.

R as alluded to above is an interpreted language that is designed explicitly for data manipulation and analysis. It has significant graphing and database capabilities built in and if you are a biologist, the R-based BioConductor suite is a very powerful set of modules for analyses like microarray data, genomics data mining, and even sequence analysis. It is not a general purpose programming language but for a researcher (not a web programmer), it would be a very good choice.

Java is yet another interpreted, general purpose, object-oriented, programming language, but one which has great support in the area of web development and cross-platform programming. Most browsers have Java plug-ins which enable them to run Java code, so your applications can run in a browser, which can sometimes be advantageous. However, the language itself is not as intuitive or concise as Python or Perl and does not have the same strengths in numerical or scientific support, altho that could be my igorance showing.

processing.org is an odd and slightly misleading name of a very cool project to enable custom visualization using a simple programming language. It is an OSS Java visualization toolkit that makes it quite easy to write code to manipulate data in 3D. If you have a visualization need that is not met by any of the other tools, you may want to play with this toolkit to see if you can develop it yourself. The language is quite simple and intuitive and the code examples given are well-documented and are executed in the provided programming environment. There are some interesting and beautiful examples of what it can do shown here.

Note that almost all the tools described here are available in identical form for the Mac. If you aren’t using fink or MacPorts, you’re missing a huge chunk of functionality. I’m not an expert, but I’ve used both and both work very well. Tho they both provide Open Source packages to MacOSX, if there’s a philosophical difference, fink seems to be more tilted to GNU (the Mac is just another vector for OSS) and MacPorts seems to be tilted to Apple (how to make the Mac the best platform for OSS). Both are very good. but you should choose one and stick to it as the packages they provide will eventually conflict.

Despite Windows being the least open platform, many of the applications mentioned in this doc also exist it. There are many other Open Source apps that are available for Windows as well. Some popular applications are noted here, a much larger list is here, as is their list of Science-related OSS apps.

Both Linux and MacOSX have internal manuals called man pages that document how to use all the features of a particular command. Compared to a well-writ web page, they’re pretty awful. Compared to nothing, they’re pretty good. They are often written by the utility’s author so they tend to be terse, technical, tedious, and worst of all, lack examples. However, since they are reviewed by other Linux users, they do tend to be accurate. To read them just prefix the name of the utility with the word man (ie man join).

On the other hand, there’s Google, which has dramatically changed support issues in that once someone has resolved an issue, everyone can see how they did it (unless the hosting company benefits from keeping it secret - another benefit of Open Source Software vs Proprietary Software.

If you come from Windows or the Mac, you’re probably comfortable with using spaces in your file names: October Spectroscopy Data. While you can use spaces in Linux file names, it makes things more difficult as most shells (the program that tries to decide what you mean when you type something) use whitespace (spaces or tabs) to indicate the end of one file name and the beginning of another. therefore if your file names have embedded spaces, you’ll have to indicate that to the shell by escaping the spaces by prefixing them with a \. So when you are specifying a file, October Spectroscopy Data will have to be typed October\ Spectroscopy\ Data. Just replace the spaces with an underscore (_) and things will be easier.

Similarly, including the forward slash (/) should be avoided, as it indicates a directory in Linux, so a file name like good/bad stuff at Target should be named good_bad_stuff_at_Target.

In general, only use the standard alphanumeric characters a-zA-Z0-9 in Linux file names.

There are 2 kinds of tab completion, file name completion and executable tab completion.