If you’re coming from Windows, the world of the Linux command line can be perplexing - you have to know what you want before you can do anything - there’s nothing to click, no wizards, few hints. So let me supply a few…
I assume you’ve been forced to the Linux shell prompt somewhat against your will and you have no burning desire to learn the cryptic and agonizing commands that form the basis of xkcd and other insider jokes. You want get your work done and you want to get it done fast.
However, there are some very good reasons for using the commandline for doing your data processing. With more instruments providing digital output and new technologies providing terabytes of digital data, trying to handle this data with Excel is just not going to work. And Comma Separated Value (CSV) files are probably not going to be much help either in a bit. But we can deal with all of these on Linux using some fairly simple, free utilities (the overwhelming majority of Linux tools are free, another reason to use it).
There are also some good proprietary tools that have been ported to Linux (MATLAB, Mathematica, SAS, etc), but I’m going to mostly ignore these for now. This is also not to say that you can’t do much of this on Windows, using native utilities and applications, but it’s second nature on Linux and it works better as well. The additional point is that it will ALWAYS work like this on Linux. No learning a new interface every 6 months because Microsoft or Apple need to bump their profit by releasing yet another pointless upgrade that you have to pay for in time and money.
Note that most of the applications noted exist for MacOSX and many exist for Windows. See below
There is a great, free, self-paced tutorial called Software Carpentry that examines much of what I’ll be zooming thru in better detail. The title refers to the general approach of Unix and Linux: use of simple, well-designed tools that tend to do one job very well (think saw or hammer). Unlike the physical tools, tho, the output of one can be piped into the input of another to form a suprisingly effective (if simple) workflow for many needs.
Showmedo is another very useful website, sort of like YouTube for Computer tools. It has tutorial videos covering Linux, Python, Perl, Ruby, the bash shell, Web tools, etc. And especially for beginners, it has a section related specifically to the above-referenced Software Carpentry series
OK, let’s start.
2. Getting your data to and from the Linux host.
This has been covered in many such tutorials, and the short version is to use WinSCP on Windows and Cyberduck on the Mac. I’ve written more on this, so go and take a look if you want the short and sweet version or the longer, more sophisticated version. Also, see the MacOSX note above - you can do all of this on the Mac as well, altho there are some Linux-specific details that I’ll try to mention.
sshfs: The other very convenient approach, if the server kernel allows it, is to use sshfs to semi-permanently connect your Desktop file system to your $HOME directory on the server. The process is described here.
OK, you’ve got your data to the Linux host… Now what..?
3. Simple data files - examination and slicing
To figure out how to use a command beyond the simple examples below, try the man pages.
3.1. What kind of file is it?
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.
bash > file /home/hjm/z/netcdf.tar.gz /home/hjm/z/netcdf.tar.gz: gzip compressed data, from Unix, last modified: Thu Feb 17 13:37:35 2005 # now I'll copy that file to one called 'anonymous' bash > cp /home/hjm/z/netcdf.tar.gz anonymous bash > file anonymous anonymous: gzip compressed data, from Unix, last modified: Thu Feb 17 13:37:35 2005 # see - it still works.
I’m assuming that you’re logged into a bash shell on a Linux system with most of the usual Linux utilities installed as well as R. You should create a directory for this excercise - name it anything you want, but I’ll refer to it as $DDIR for DataDir. You can as well by assigning the real name to the shell variable DDIR:
Shell commands are prefixed by bash > and can be moused into your own shell to test including the embedded comments (prefixed by #; they will be ignored.) Do not, of course include the bash > prefix.
Also, all the utilities described here will be available on the interactive BDUC cluster nodes at UC Irvine. Unless otherwise stated, they are also freely available for any distribution of Linux.
3.2. How big is the file?
We’re going to use a 25MB tab-delimited data file called red+blue_all.txt. Download it (in Firefox) by right-clicking on the link and select Save Link As... Save it to the $DDIR directory you’ve created for this excercise.
We can get the total bytes with ls
bash > mkdir ~/where/you/want/the/DDIR # make the DDIR bash > export DDIR=/where/you/made/the/DDIR # create a shell variable to store it bash > cd $DDIR # cd into that data dir bash > ls -l red+blue_all.txt -rw-r--r-- 1 hjm hjm 26213442 2008-09-10 16:49 red+blue_all.txt ^^^^^^^^
or in human-readable form with:
bash > ls -lh red+blue_all.txt # ^ -rw-r--r-- 1 hjm hjm 25M 2008-09-10 16:49 red+blue_all.txt # ^^^ (25 Megabytes)
We can get a little more information using wc (wordcount)
bash > wc red+blue_all.txt 385239 1926195 26213442 red+blue_all.txt
wc shows that it’s 385,239 lines 1,926,195 words and 26,213,442 characters
3.3. Native Spreadsheet programs for Linux
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:
OpenOffice calc (type oocalc to start)
[[libreoffice]] * http://www.libreoffice.org/features/calc/[LibreOffice calc] (type 'libreoffice' to start)
Gnumeric (type gnumeric to start).
NB: 'LibreOffice' is the latest fork of 'OpenOffice'. Due to IP entanglements, development of 'OpenOffice' has stopped but development of 'LibreOffice' continues actively. If both are available, 'LibreOffice' is a better choice.
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.
3.4. Extracting data from MS Excel and MS Word files files
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:
File Menu > Save as... > set Filter to text (.txt), specify directory and name > [OK]
For Excel files:
File Menu > Save As... > set Filter: to text CSV (.csv), specify directory and name > [OK] > set 'Field delimiter' and 'Text delimiter' > [OK]
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.
3.4.1. Converting a Word file to text
For MS Word documents, there is a utility, appropriately called… antiword
antiword does what the name implies; it takes a Word document and inverts it - turns it into a plain text document:
bash > time antiword some_MS_Word.doc > some_MS_Word.txt real 0m0.004s user 0m0.004s sys 0m0.000s # only took 0.004s!
(the > operator in the example above redirects the STDOUT output from antiword to a file instead of spewing it to console. Try it without the > some_MS_Word.txt. See the note on STDIN, STDOUT, & STDERR.
Timing your programs
The time prefix above returns the amount of time that the command that followed it used. There are actually 2 time commands usually available on Linux; the one demo’ed above is the internal bash timer. If you want more info, you can use the system timer which is usually /usr/bin/time and has to be explicitly called:
bash > /usr/bin/time antiword some_MS_Word.doc > some_MS_Word.txt 0.00user 0.00system 0:00.00elapsed 80%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+16outputs (0major+262minor)pagefaults 0swaps # or even more verbosely: bash > /usr/bin/time -v antiword some_MS_Word.doc > some_MS_Word.txt Command being timed: "antiword some_MS_Word.doc > some_MS_Word.txt" User time (seconds): 0.00 System time (seconds): 0.00 Percent of CPU this job got: 0% Elapsed (wall clock) time (h:mm:ss or m:ss): 0:00.00 Average shared text size (kbytes): 0 Average unshared data size (kbytes): 0 Average stack size (kbytes): 0 Average total size (kbytes): 0 Maximum resident set size (kbytes): 0 Average resident set size (kbytes): 0 Major (requiring I/O) page faults: 0 Minor (reclaiming a frame) page faults: 263 Voluntary context switches: 1 Involuntary context switches: 0 Swaps: 0 File system inputs: 0 File system outputs: 16 Socket messages sent: 0 Socket messages received: 0 Signals delivered: 0 Page size (bytes): 4096 Exit status: 0
3.4.2. Extracting an Excel spreadsheet
There’s an excellent Excel extractor called py_xls2csv, part of the free python-excelerator package (on Ubuntu). It works similarly to antiword:
bash > py_xls2csv BodaciouslyHugeSpreadsheet.xls > BodaciouslyHugeSpreadsheet.csv
py_xls2csv takes no options and saves output with commas as the only separator, which is generally what you want.
If you want to do further data mangling in Python, the xlrd and xlwt modules are very good Excel modules for reading and writing Excel files, but they’re libs, not a standalone utility (tho there are lots of examples.
If you are Perl-oriented and have many Excel spreadsheets to manipulate, extract, spindle and mutilate, the Perl Excel-handling modules are also very good. Here is a good description of how to do this.
4. Viewing and Manipulating the data
Whether the file has been saved to a text format, or whether it’s still in a binary format, you will often want to examine it to determine the columnar layout if you haven’t already. You can do this either with a commandline tool or via the native application, often a spreadsheet. If the latter, the OpenOffice spreadsheet app oocalc is the most popular and arguably the most capable and compatible Linux spreadsheet application. It will allow you to view Excel data in native format so you can determine which cols are relevant to continued analysis.
If the file is in text format or has been converted to it, it may be easier to use a text-mode utility to view the columns. There are a few text-mode spreadsheet programs available, but they are overkill for simply viewing the layout. Instead, consider using either a simple editor or the text mode utilities that can be used to view it.
4.1. Editors for Data
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 that is sometimes helpful in debugging a data file, which can visually differ between tabs and spaces. 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 it can display X (see above
Also, 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, of course) 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.
4.2. Text-mode data manipulation utilities
STDIN, STDOUT, STDERR
Automatically available to your programs in Linux and all Unix work-alikes (including MacOSX are the 3 channels noted above: Standard IN (STDIN, normally the keyboard), Standard OUT (STDOUT, normally the terminal screen), and Standard Error (STDERR, also normally the terminal screen). These channels can be intercepted, redirected, and piped in a variety of ways to further process, separate, aggregate, or terminate the processes that use them. This is a whole topic by itself and is covered well in this Software Carpentry tutorial.
4.2.1. The grep family
Possibly the most used utilities in the Unix/Linux world. These elegant utilities are used to search files for patterns of text called regular expressions (aka regex) and can select or omit a line based on the matching of the 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 typo’s. Baeza-Yates and Navarro’s nrgrep is even faster, if not as flexible (or differently flexible), as agrep.
A grep variant would be used to extract all the lines from a file that had a particular phrase or pattern embedded in it.
bash > wc /home/hjm/FF/CyberT_C+E_DataSet 600 9000 52673 /home/hjm/FF/CyberT_C+E_DataSet # so the file has 600 lines. If we wanted only the lines that had the # identifier 'mur', followed by anything, we could extract it: # (passed thru 'scut' (see below) to trim the extraneous cols.) bash > grep mur /home/hjm/FF/CyberT_C+E_DataSet | scut --c1='0 1 2 3 4' b0085 murE 126.96.36.199 0.000193129 0.000204041 b0086 murF+mra 188.8.131.52 0.000154382 0.000168569 b0087 mraY+murX 184.108.40.206 1.41E-05 1.89E-05 b0088 murD 220.127.116.11 0.000117098 0.000113005 b0090 murG 2.4.1.- 0.000239323 0.000247582 b0091 murC 18.104.22.168 0.000245371 0.00024733 # if we wanted only the id's murD and murG: bash > grep mur[DG] /home/hjm/FF/CyberT_C+E_DataSet | scut --c1='0 1 2 3 4' b0088 murD 22.214.171.124 0.000117098 0.000113005 b0090 murG 2.4.1.- 0.000239323 0.000247582
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.
bash > cat greens.txt turquoise putting sea leafy emerald bottle bash > cat blues.txt cerulean sky murky democrat delta # now concatenate the files bash > cat greens.txt blues.txt >greensNblues.txt # and dump the concatenated file bash > cat greensNblues.txt turquoise putting sea leafy emerald bottle cerulean sky murky democrat delta
4.2.3. more & less
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. Video example
4.2.4. head & tail
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 .
4.2.5. cut & scut
These are columnar slicing utilities, which allow you to slice vertical columns of characters or fields out of a file, based on character offset or column delimiters. cut is on every Linux system and works very quickly, but is fairly primitive in its ability to select and separate data. scut is a Perl utility which trades some speed for much more flexibility, allowing you to select data not only by character column and single character delimiters, but also by data fields identified by any delimiter that a regular expression (aka regex) can define. It can also re-order columns and sync fields to those of another file, much like the join utility see below. See this link for more info.
cols is a very simple, but arguably useful utility that allows you to view the data of a file aligned according to fields. Especially in conjunction with less, it’s useful if you’re manipulating a file that has 10s of columns especially if those columns are of disparate widths. cols is explained fairly well here and the cols code is available here.
paste can join 2 files side by side to provide a horizontal concatenation. ie:
bash > cat file_1 aaa bbb ccc ddd eee fff ggg hhh iii jjj kkk lll bash > cat file_2 111 222 333 444 555 666 777 888 999 bash > paste file_1 file2 aaa bbb ccc ddd 111 222 333 eee fff ggg hhh 444 555 666 iii jjj kkk lll 777 888 999
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
join is a more powerful variant of paste that acts as a simple relational join based on common fields. join needs identical field values to finish the join. See that scut described above can also do this type of operation. For much more powerful relational operations, SQLite is a fully featured relational database 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.
5. Large ASCII Flat Files
These are typically files containing self-generated data or (increasingly) High Throughput Sequencing (HTS) data in FASTA or FASTQ format. In the latter case, these sequence file should be kept compressed. Almost all released tools for analyzing them can deal with the compressed form.
In the former case, take some care to design your output so that it’s easily parseable and searchable. If the data is entirely numeric, consider writing it as an HDF5 file or if it’s fairly simple, in binary format (the float 23878.349875935 takes 15-30 bytes to represent in a text format; 8 bytes in double precision floating point). Integers take even less.
In the worst case, if you MUST use ASCII text, write your output in delimted tables rather than line by line:
# example of line by line (666 bytes of data) AGENT 723, t = 0 len(agentpool)=996 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 4 AGENT 917, t = 0 len(agentpool)=995 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 5 AGENT 775, t = 0 len(agentpool)=994 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 6 AGENT 675, t = 0 len(agentpool)=993 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 7 AGENT 546, t = 0 len(agentpool)=992 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 8 AGENT 971, t = 0 len(agentpool)=991 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 9 AGENT 496, t = 0 len(agentpool)=990 totaledge_G = 100348 totaledge_H = 100348 reconnectamt = 10
The same data in (poorly designed but) delimited format:
#agent|t|len|totaledge_G|totaledge_H|reconnectamt (182 bytes: 27% of the above) 723|0|996|100348|100348|4 917|0|995|100348|100348|5 775|0|994|100348|100348|6 675|0|993|100348|100348|7 546|0|992|100348|100348|8 971|0|991|100348|100348|9 496|0|990|100348|100348|10
If you need to view this data in aligned or titled format for verification, use the utilities scut and cols.
6. Complex Binary Data storage and tools
While much data is available in (or can be converted to) text format, some data is so large (typically, >1 GB) that it demands special handling. Data sets from the following domains are typically packaged in these formats:
Global Climate Modeling
Stock exchange transactions
Satellite and other terrestrial scans
Microarray and other genomic data
There are a number of specialized large-data formats, but I’ll discuss a popular large-scale data format called HDF5, which has now been merged with the netCDF data format. These can be thought of as numeric databases, tho they have significant functional overlap with relational databases. One advantage is that they have no requirement for a database server, an advantage they share with SQLite, below. As the wiki pages describe in more detail, these Hierarchical Data Formats are self-describing, somewhat like XML files, which enable applications to determine the data structures without external references. HDF5 and netCDF provide sophisticated, compact, and especially hierarchical data storage, allowing an internal structure much like a modern filesystem. A single file can provide character data in various encodings (ASCII, UTF, etc), numeric data in various length integer, floating point, and complex representation, geographic coordinates, encoded data such as base+offsets for efficiency, etc. These files and the protocols for reading them, assure that they can be read and written using common functions without regard to platform or network protocols, in a number of languages.
These files are also useful for very large data as they have parallel Input/Output (I/O) interfaces. Using HDF5 or netCDF4, you can read and write these formats in parallel, increasing I/O dramatically on parallel filesystems such as Lustre, PVFS2, and GFS. Many analytical programs already have interfaces to the HDF5 and netCDF4 format, among them MATLAB, Mathematica, R, VISIT, and others.
6.1. Tools for HDF and netCDF
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:
nco, a suite of tools written in C/C++ mainly by UCI’s own Charlie Zender. they were originally written to manipulate netCDF 3.x files but have been updated to support netCDF4 (which uses HDF5 format). They are described in detail here, with examples. They are portable to all current Unix implementations and of course Linux. They are extremely well-debugged and their development is ongoing.
PyTables is another utility that is used to create, inspect, modify, and/or query HDF5 tables to extract data into other HDF or text files. This project also have very good documentation and even has a couple video introductions on ShowMeDo. They can be reached from here. In addition, PyTables also has a companion graphical data browser called ViTables.
Databases (DBs) are data structures and the supporting code that allow you to store data in a particular way. Some DBs are used to store only numbers (and if this is the case, it might be quicker and compact to store that data in an HDF5 file). If you have lots of different kinds of data and you want to query that data on complex criteria (give me the names of all the people who lived in the 92655 area code and have spent more than $500 on toothpicks in the last 10 years), using a DB can be extremely useful and in fact using a DB may be the only way to extract the data you need in a timely manner.
As implied above, there are a number of different types of DBs, delineated not only by the way they store and retrieve data, but by the way the user interacts with the DB. The software can be separated into Desktop DBs (which are meant to serve mostly local queries by one person - for example, a research DB that a scientist might use to store his own data) and DB Servers, which are meant to answer queries from a variety of clients via network socket.
The latter are typically much more complex and require much more configuration to protect their contents from network mischief. Desktop DBs typically have much less security and are less concerned about answering queries from other users on other computers, tho they can typically do so. Microsoft Access and SQLite are examples of Desktop DBs.
Especially if you are using the names of flat files as the index of their contents ( such as s=2.0_tr=48_N=1000.out, the use of an DB is strongly suggested. In this way, you can append to a common DB such that you can rapisdly query the results over millions of entries (see here for an example where this approach was used).
7.1. Relational databases
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:
TABLE Woods ( id INTEGER PRIMARY KEY, # the entry index origin VARCHAR(20), # area of native origin now_grown VARCHAR (100), # areas where now grown local BOOLEAN, # locally available? cost FLOAT, # cost per board foot density FLOAT, # density in lbs/board ft. hardness INT, # relative hardness scaled 1-10 sanding_ease INT, # relative ease to sand to finish color VARCHAR(20), # string descriptor of color range allergy_propensity INT, # relative propensity to cause allergic reaction 1-10 toxicity INT, # relative toxicity scaled 1-10 strength INT, # relative strength scaled 1 (balsa) to 10 (hickory) appro_glue VARCHAR(20), # string descriptor of best glue warnings VARCHAR(200), # any other warnings notes VARCHAR (1000), # notes about preparation, finishing, cutting <etc> );
See the Wikipedia entry on Relational Databases for more.
SQLite (website, wikipedia) is an amazingly powerful, ACID-compliant, astonishingly tiny DB engine (could fit on a floppy disk) that can do much of what much larger DB engines can do. It is public domain, has a huge user base, has good documentation (including a book) and is well suited to using for the transition from flat files to relational database. It has support for almost every computer language, and several utilities (such as graphical browsers like sqlitebrowser and knoda to ease its use. The sqlite3 program that provides the native commandline interface to the DB system is fairly flexible, allowing generic import of TAB-delimited data into SQLite tables. Ditto the graphical sqlitebrowser programs. Here is a well-documented example of how to use SQLite in a Perl script (about 300 lines including comments).
OpenOffice comes with it’s own DB and DB interaction tools, which are extremely powerful, tho they have not been well-documented in the past. The OpenOffice DB tools can be used not only with its own DB, but with many others including MySQL, PostgreSQL, SQLite, MS Access, and any DB that provides an ODBC interface.
MySQL is a hugely popular, very fast DB server that provides much of the DB needs of the WWW, both commercial and non-profit. Some of the largest, most popular web sites in the world use MySQL to keep track of the web intereactions, as well as their data. The UC Santa Cruz Genome DB uses MySQL with a schema of >300 tables to keep one of the most popular Biology web sites in the world running.
PostgreSQL is similarly popular and has a reputation for being even more robust and full-featured. It also is formally an Object-Relational database, so its internal storage can be used in a more object oriented way. If your needs require storing Geographic Information, PostgreSQL has a parallel development called PostGIS, which is optimized for storing Geographical Information and has become a de facto standard for GIS DBs. It supports the popular Mapserver software
Firebird is the Open Source version of a previously commercial DB called Interbase from Borland. Since its release as OSS, it has undergone a dramatic upswing in popularity and support.
Others. There are a huge number of very good relational DBs, many of them free or OSS. Wikipedia has a page that names a number of them and briefly describes some differences, altho unless you are bound by some external constraint, you would be foolish not to choose one of SQLite, MySQL, or PostgreSQL due to the vast user-generated support and utilities.
8. Mathematical Modeling Systems
There are a wide variety of mathematical packages available for Linux, ranging from the well-known proprietary ones such as MATLAB and Mathematica to a number of very well-implemented OSS ones. For those looking for an OSS package with MATLAB-like language compatibility, Octave is a very good fit. For a symbolic package like Mathematica, well… there’s Mathematica (which is available for Linux). Note that there is much cross-over among the packages mentioned in this section and those mentioned in the Visualization section. Many of the OSS Visualization packages are used to display the results of the Modeling packages and many of the Visualization packages actually have significant analytical muscle as well, especially VISIT and R.
However, there are a number of other OSS packages that can help you with manipulating your data. Here are a few of the best ones.
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 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 (5) 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 (5.3) 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.
Python-based Mathematics systems
There are a surprising number of good systems developed using Python. SAGE and Pylab are two of the better known. Both are commandline-only, but have good-to-excellent graphic output, including interactive plots. Altho you don’t type Python code directly in either, both systems are extensible using the underlying Python. Both have good documentation and even introductory videos showing how to use the systems. Both can be run on Mac/Win/Linux.
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.
9. Data visualization
You almost always want to visualize your data. It’s one thing to page thru acres of spreadsheet or numeric data, but nothing can give you a better picture of your data than … a picture. This is an area where Linux shines, tho it’s not without scrapes and scratches. I’m going to break this into 2 arbitrary sections. The first is Simple the second Complex. "Simple" alludes to that both the process and data are relatively simple; "Complex" implies that both the visualization process and data are more complex.
9.1. Simple Data Visualization
qtiplot is "a fully fledged plotting software similar to the OriginLab Origin software". It also is multiplatform, so it can run on Windows as well as MacOSX. This is probably what researchers coming from the Windows environment would expect to use when they want a quick look at their data. It has a fully interactive GUI and while it takes some getting used to, it is fairly intuitive and has a number of useful descriptive statistics features as well. Highly recommended.
gretl is not a plotting package per se but a fully graphical statistical package that incorporates gnuplot and as such is worth knowing about. It is quite intuitive and the statistical functions are an added bonus for when you want to start applying them to your data. It was originally developed with its own statistics engine but can start and use R as well. Highly recommended.
quickplot is a more primitive graphical plotting program but with a very large capacity if you want to plot large numbers of point (say 10s or 100s of thousands of points.) It can also read data from a pipe so you can place it at the end of a data pipeline to show the result.
gnuplot is one of the most popular plotting applications for Linux. If you spend a few minutes with it, you’ll wonder why. If you persist and spend a few hours with it, you’ll understand. It’s not a GUI program, altho qgfe provides a primitive GUI (but if you want a GUI, try qtiplot above. gnuplot is really a scripting language for automatically plotting (and replotting) complex data sets. To see this in action, you may first have to download the demo scripts and then have gnuplot execute all of them with gnuplot /where/the/demo/data/is/all.dem. Pretty impressive (and useful, as all the demo examples ARE the gnuplot scripts) It’s an extremely powerful plotting package, but it’s not for dilettantes. Highly recommended (if you’re willing to spend the time).
pyxplot is a graphing package sort of like gnuplot in that it relies on an input script, but instead of the fairly crude output of gnuplot, the output is truly publication quality and the greater the complexity of the plot, the more useful it is. Recommended if you need the quality or typesetting functionality.
9.1.7. R’s plot packages
The R statistical language has some very good plotting facilities for both low and high dimensional data. Like the Gretl program immediately above, R combines impressive statistical capabilities with graphics, although R is an interpreted language that uses commands to create the graphs which are then generated in graphics windows. There are some R packages that are completely graphical and there are moves afoot to put more effort into making a completely graphical version of R, but for the most part, you’ll be typing, not clicking. That said, it’s an amazingly powerful language and one of the best statistical environments available, commercial or free.
Notable is the ggplot2 package which produces elegant plots fairly simply by providing excellent defaults - highly recommended. While software is free, and the internal documentation is fairly good, the author has written an entire book on it. While the entire book is not free, the introductory chapter Getting started with qplot is, along with all the code mentioned in it.
9.2. Visualization of Multivariate Data
ggobi is on the border of "simple" and "complex", but since it can be started and used relatively easily and has some very compelling abilities. I have to admit to being a ggobi fan for many years - it has some features that I haven’t seen anywhere else which, if your data is multivariate, really helps you to understand it. It can be run by itself, but its real power is obvious when you use it inside of R. That interface also obviates the grotacious requirement to code all your data into XML (R does it for you automatically). The ggobi has a very good website and documentation, and also has some very compelling demos and videos showing off its abilities. For an extended example of how to use it and some output, please check out the ggobi part of An R Cheat Sheet Highly recommended.
ncview is a viewer for netCDF files. Since such files are typically used to map variables onto a physical space, this application provides a quick visualization of the data in a netCDF file mapped to the geographical or time coordinates.
Integrated Data Viewer (IDV) from Unidata is a Java™-based software framework for analyzing and visualizing geoscience data. The IDV brings together the ability to display and work with satellite imagery, gridded data, surface observations, balloon soundings, NWS WSR-88D Level II and Level III radar data, and NOAA National Profiler Network data, all within a unified interface." It supports a fantastically wide set of data and can even be run (slowly) without installing it via Java’s Web Start. It can also interactively query remote data servers to provide filtered data from much larger data sets. It is strongly tailored to mapping geophysical data onto geographic maps, so is more of a GIS tool than a strictly multivariate visualizationj tool.
ifrit, named for "Ionization FRont Interactive Tool" for that domain, has now become a much more general data visualization tool. It is used to visualize 4 main types of data: - Scalar data: several scalar variables in 3D space. - Vector field data: a 3D field of vectors. - Tensor field data: a symmetric 3x3 tensor in 3D space. - Particle data: a set of particles (points) with several optional attributes (numbers that distinguish particles from each other) per particle. In order to supply data to ifrit, you will probably have to do some editing of the data file header to tell ifrit the layout.
VISIT is a very sophisticated fully graphical visualization (and analytical) tool that can provide plotting of just about every dataset that you can supply, and do so in very high quality (including making animations). It supports the HDF and netCDF formats mentioned above as well about 40 others. To provide this visualization capability, it’s interface is fairly complex as well, but if your data visualization needs are very sophisticated, this is the application for you. The web site includes a gallery of visualizations made with VISIT.
9.3. Genomic Visualization Tools
9.3.1. Sequence Variant Analyzer
SVA is "designed to annotate, visualize, and analyze the genetic variants identified through next-generation sequencing studies, including whole-genome sequencing (WGS) and exome sequencing studies." It uses a fully Graphical Interface written in Java and therefore supposedly runs on any platform, but due to it’s memory requirements, it’s effectively limited to those machines which have more than ~10GB RAM available.
"Gephi is an interactive visualization and exploration platform for all kinds of networks and complex systems, dynamic and hierarchical graphs." Written in Java and with a full Graphical User Interface, it runs on all platforms and can be used to cluster, analyze, and visualize complex network-like data. Well worth a look for any kind of analysis of networks whether they be social, biological, internet, or chemical.
Circos produces many of those beautiful circular interaction maps that have decorated so many science and popular mags. It can be used to generate multi-resolution maps to display things from database schemas to genomic maps. While the output is static, the information density is startling. The interface is strictly commandline and file-based tho, so you’ll have to dig a bit to figure out how it works. If your data is fairly simple, there is an online version of it so you can submit your data for processing without investing too much effort.
9.3.4. Interactive Genomics Viewer
IGV "is a high-performance visualization tool for interactive exploration of large, integrated datasets. It supports a wide variety of data types including sequence alignments, microarrays, and genomic annotations." Also a (multiplatform) interactive Java app for viewing high-density genomic information. Can be run from your desktop or started remotely via Sun’s Webstart technology.
Gaggle is another Java application that can assemble (if not quite completely integrate) the inputs from a number of independent applications and/or databases, including:
10. Some Inspiration and Examples
Many Eyes is IBM’s very cool site for tuning complex datasets into beautiful graphics.
The FlowingData is a visualization blog and info clearing house for many visualization-related trends and software. For example, this, which announces that Streamgraph code for Processing is open source (see below). particularly good example of this kind of active graphic at the New York Times
http://www.visualisingdata.com/ Another data viz blog
11. Programmatic manipulation
Sometimes your data is of a complexity that you’ll need to reduce or filter it before it can be used as input for statistical processing or visualization. If this is the case, you may be headed into the realm of actual programming. I refer you again to the excellent Software Carpentry tutorials and related videos on ShowMeDo.
For further data manipulation, I would suggest learning a scripting (or interpreted) language, such as Python, Perl, R, or Java for the following reasons.
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.
11.3. The R Statistical Language
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.
12. Version Control for Code and Documents
This is a bit beyond the original scope of the document, but since I brought up writing your own code, version control is something you should know about. This Software Carpentry page describes what it is and why it’s important. READ IT!!
Versioning tools can, unsurprisingly, help you keep track of file versions. The two such OSS systems now in widespread and growing use are git and Subversion (aka svn). git was writ by a major software developer (Linus Torvalds, he of Linux fame and name) for a major software project (tracking the development of Linux itself). As such, it is very fast, efficient, and was written to encourage branching and merging of versions by a widespread network of asynchronously online group of developers. As such it is more like a peer-to-peer repository. svn was written with the same goals (developed by the team that previously wrote CVS, one of the most successful versioning systems before Subversion), but it is harder to branch and merge versions and has some different architectural features that lend it to a server implementation.
Note that these repositories can be used not only for code but for anything based on text - notes, grants, small amounts of data, email, etc. I know a few people who use subversion as their entire backup system. One in particular who uses Linux and Open Source tools for everything (including writing papers in TeX) has very few binary format files to back up so everything goes into svn.
Both the Software Carpentry site and Showmedo have pages dedicated to version control but mostly subversion. Setting up a subversion repository is not trivial, but learning how to use it can be tremendously valuable if you need or appreciate versioned files. Google Video has a couple of tech Talks dedicated to git, one by Linus Torvalds which is very entertaining but is not much of a HOWTO and another by Randall Schwartz which is more useful and describes more of what git can be used for.
13. Some Further Notes and Warnings
13.1. FLOSS on Mac and Windows
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.
13.2. The Unix Man Pages
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.
13.3. Linux File names
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.
13.4. Tab Completion
There are 2 kinds of tab completion, file name completion and executable tab completion.
13.4.1. File name Tab Completion
Many shells will try to help you out via tab-completion, providing file name completion or suggesting file names if you’ve typed enough of a file to identify it uniquely:
The following renames October Spectroscopy Data to October_Spectroscopy_Data. If the file is the only one in the directory that begins with October, then mv Octo<TAB> will complete the name, automatically inserting the required backslashes.
mv October\ Spectroscopy\ Data
and then wait for you to provide the destination name.
If there were several files beginning with October, then typing mv Oct<TAB> will complete the filename to the point where they diverge:
$ mv Oct<TAB> $ mv October\ # and another <TAB><TAB> will provide all possible choices: $ mv October\ <TAB><TAB> October Centrifuge Report October Journal Club notes October Spectroscopy Data $ mv October\ # and then waits for you to provide more of the name: (You type an 'S', then another <TAB>, which allows the shell to complete the rest of the now-unique name). $ mv October\ S<TAB> # and you type the destination name: $ mv October\ Spectroscopy\ Data October_Spectroscopy_Data
13.4.2. Executable Tab Completion
If you are at a bare prompt ($) and you hit the <TAB> key 2x, the 1st time you’ll get an alert (a beep or a flash) and the second time you’ll get the offer to show you all several thousand executables in your execution path. Typically, the latter will not be useful, but if you enter the 1st few letters of the command followed by <TAB><TAB>, you’ll get all the executables that begin with those letters:
$ et<TAB><TAB> etags etags.emacs etex etherape ether-wake ethtool $ eth<TAB><TAB> etherape ether-wake ethtool
If the above doesn’t explain things, there’s an animated example at Wikipedia
14. Further Reading
There is a similar, if less complete (but possibly more articulate) tutorial called Simplify data extraction using Linux text utilities from the IBM DeveloperWorks series.
For a quick introduction to more sophisticated data analysis with R that builds on this document, please refer to An R Cheatsheet and the references therein.
List of applications noted here:
file - what kind of file is this?
OpenOffice.org - native GUI MS Office work-alike Word Processor (oowriter), Spreadsheet (oocalc), Database Tools (part of oocalc), Drawing (oodraw), Presentation (ooimpress) apps
Gnumeric - native, GUI Spreadsheet
NeoOffice - native MacOSX fork of OpenOffice
nedit, jedit, kate, xemacs - GUI editors for data files
cat - dump a file to STDOUT
more, less - text file pagers
head, tail - view or slice rows from teh top or bottom of a data file
cut, scut - how to slice columns out of a data file
cols - columnize delimited data files
paste - glue 2 files together side by side
Simple Data Plots
Complex Data Visualization
Programmatic Data Manipulation