Part 4: Advanced text manipulation

Example data files

For some of the discussions below, we'll use some files in your ~/data directory.

The ~/data/walrus_sounds.tsv file () lists the types of sounds made by several well-known walruses, and the length of each occurrence. tab-delimited fields are:

• column 1 - walrus name

• column 2 - sound type

• column 3 - length of sound

Take a look at the first few lines of this file:

The .tsv filename extension stands for tab separated values, indicating that the field separator (the character separating fields) is tab. We can verify this using the handy hexdump alias we defined for you as discussed at Intro Unix: What is text? 

The output looks like this, where the hexadecimal 0x09 character is a Tab.

Open

We will also use two data files from the GSAF's (Genome Sequencing and Analysis Facility) automated processing that delivers sequencing data to customers. These files have information about customer Samples (libraries of DNA molecules to sequence on the machine), grouped into sets assigned as Jobs, and sequenced on GSAF's sequencing machines as part of sequencer Runs.

Open

The files are also in your ~/data directory:

• - contains job name/sample name pairs, tab-delimited, no header

  • the "JAnnnnn" items in the 1st column are Jobs

  • the "SAnnnnn" items in the 2nd column are Runs

    • several Jobs can be associated with the same Run

• - contains information about all samples run on a particular run, along with the job each belongs to.

  • columns (tab-delimited) are job_name, job_id, sample_name, sample_id, date_string

  • column names are in an initial header line

Take a look at the first few lines of these files also:

Exercise 3-1

What field separators are used in ~/data/joblist.txt and ~/data/sampleinfo.txt?

How many lines to these sample files have?

Cut, sort, uniq

cut

The cut command lets you isolate ranges of data from its input lines (from files or standard input):

• cut -f <field_number(s)> extracts one or more fields (-f) from each line

  • the default field delimiter is tab

    • use -d <delim> to change the field delimiter

• cut -c <character_number(s)> extracts one or more characters (-c) from each line

• the <numbers> can be

  • a comma-separated list of numbers (e.g. 1,4,7)

  • a hyphen-separated range (e.g. 2-5)

  • a trailing hyphen says "and all items after that" (e.g. 3,7-)

• cut does not re-order fields, so cut -f 5,3,1 acts like -f 1,3,5

Examples:

Exercise 3-2

How would you extract the first 5 job_name and sample_name fields from ~/data/sampleinfo.txt without including the header? Recall that job_name and sample_name are fields 1 and 3 of ~/data/sampleinfo.txt.

sort

sort sorts its input lines using an efficient algorithm

• by default sorts each line lexically (as strings), low to high

  • use -n sort numerically (-n)

  • use -V for Version sort (numbers with surrounding text)

  • use -r to reverse the sort order

• use one or more -k <start_field_number>,<end_field_number> options to specify a range of "keys" (fields) to sort on

  • use this option when you want to preserve all data on the input lines, but sort on part(s) of the line

  • e.g. -k1,1 -2,2nr  to sort field 1 lexically then field 2 as a number high-to-low

• by default, fields are delimited by whitespace – one or more spaces or tabs

  • use -t <delim> to change the field delimiter (e.g. -t "\t" for tab)

Examples:

Here we use cut to isolate text we want to sort:

But we can also sort lines based on one or more fields specified by the -k option:

Exercise 3-3

Which walruses make the longest sounds?

Which walruses make the shortest grunts?

Here's an example of using the -V (Version sort) option to sort numbers-with-text:

Note: In order to be meaningful, all the strings should have the same text suffix (x above).

uniq

uniq takes sorted input and collapses adjacent groups of identical values

• uniq -c says also report a count of the number of members in each group (before collapsing)

Examples:

“Piping a histogram” with cut/sort/uniq -c

One of my favorite "Unix tricks" is combining sort and uniq calls to produce a histogram-like ordered list of count/value pairs.

Since each line of this output consists of a number then a sound name, separated by whitespace (one or more spaces or tabs), we can use sort's -k1,1nr option to sort it by numerical count, highest to lowest:

We affectionately refer to this "cut | sort | uniq -c | sort -k1,1nr" idiom as "piping a histogram".

Exercise 3-4

How many different walruses are represented in the ~/data/walrus_sounds.tsv file?

Which walrus has the most recorded sounds?

Job names are in column 1 of the ~/data/sampleinfo.txt file. Create a histogram of Job names showing the count of samples (lines) for each, and show Jobs with the most samples first.

The Run names in joblist.txt start with SAyy where yy are year numbers. Report how many runs occurred in each year.

Which Run in joblist.txt has the most jobs?

Ensuring uniqueness of field combinations

Sometimes you'll get a table of data that should contain unique values of a certain field, or a certain combination of fields. Using cut sort uniq wc -l can help verify this, and to find which are duplicates.

Example: Are all the Job names in joblist.txt unique?

Exercise 3-5

Are all combinations of Job/Run in joblist.txt unique?

Introducing awk

awk is a powerful scripting language that is easily invoked from the command line. It is especially useful for handling tabular data.

One way of using it:

• awk '<script>' - the '<script>'  is applied to each line of input (generally piped in)

  • always enclose '<script>' in single quotes to inhibit shell evaluation

  • awk has its own set of metacharacters that are different from the shell's

A basic awk script has the following form, of three clauses, all of which are optional

awk example 1

Here’s how to output the first few lines of joblist.txt with the Run in column 1 and the Job in column 2:

Notes:

• The { <body expression(s)> } are executed for every line of input.

• awk refers to fields with $N, where N is the field/column number.

• awk's default input field delimiter is whitespace (one or more spaces or a tab)

  • can be changed in the BEGIN block (e.g. BEGIN{FS="\t" }"

    • or on the command line (e.g. awk -F '\t')

• Write output in awk with the print function

  • followed by a comma-separated list of fields, strings or variable names, e.g.

    • print "field 2 is:",$2

    • BEGIN{ total=0 } {total= total + $1} END{ print "total:",total }

    • enclose text and special characters in double quotes ( " ) inside awk scripts

• awk's default output field delimiter is a single space

  • can be changed in the BEGIN block (e.g. BEGIN{OFS="\t" }

• spaces are generally optional in clauses

• Any BEGIN or END blocks are executed once

  • before any input is processed, and then when there is no more input data

Since the original joblist.txt file was tab-separated, we modify our script to do the same, changing the output field delimiter:

awk example 2

An awk script to add two numbers on a line:

Notes:

• Once the single quote ( ' ) to start the script is seen on the 1st line, there is no need for special line-continuation.

  • Just enter the script text then finish with a closing single quote when done.

• Multiple expressions can appear on the same line if separated by a semicolon ( ; )

• print "" just prints an empty line

  • print with no arguments would print the entire input line

awk example 3

Here's an awk script that takes the average of the numbers passed to it, using the seq function to generate numbers from 1-10, each on a line.

Notes:

• Once the single quote ( ' ) to start the script is seen on the 1st line, there is no need for special line-continuation.

  • Just enter the script text then finish with a closing single quote when done.

• Multiple expressions can appear on the same line if separated by a semicolon ( ; )

• The BEGIN and END clauses are optional, and are executed only once, before and after input is processed, respectively

• BEGIN {<expressions>}  –  use to initialize variables before any script body lines are executed

  • Script variables ct and sum are initialized to 0 in the BEGIN block above

  • Some important built-in variables you man want to initialize:

    • FS (input Field Separator) - used to delimit input data fields

      • default is whitespace -- one or more spaces or a tab

      • e.g. FS=":" to specify a colon

    • OFS (Output Field Separator) - use to delimit output fields

      • default is a single space

      • e.g. OFS="\t" to specify a tab

• The body expressions are executed for each line of input.

  • Each line is parsed into fields based on the specified input field separator 

  • Fields can then be access via build-in variables $1 (1st field), $2 (2nd field) and so forth.