Calculate with AWK

On this page, you learn how to do math with AWK with the growth data: you add a new column, and you calculate sums, counts and means, first for the whole table and then per group. You use the same folder as on the last page, with the data in awk_data/.

You will notice that the commands on this page are longer than the ones on the last page. You could do the same thing in R or Python, and you might wonder why even learn them in AWK? The reason is that in bioinformatics you often only need a quick look at your data before you decide to continue with your analysis: what is the total number of reads you work with, how many plants are in each group, and do the means look sensible? AWK gives you that answer in the terminal, without starting another program or loading the table first. And because AWK reads a file one line at a time, it also works on files that are too large to open comfortably in R or Excel. For statistics and plots, R or Python remain the better tools. AWK is for the quick check before, or for cleaning up a table before you load it there.

Calculations are where empty fields do the most damage, so keep the rule from AWK on a table in mind: skip empty fields with $4 != "" before you compare or calculate.

Add a column

AWK can calculate with fields. For example, the shoot weight per mm of root length is $5 / $4. To add it as a new column at the end of each line, print the whole line $0 and then the result:

awk -F'\t' -v OFS='\t' 'NR > 1 { print $0, $5 / $4 }' awk_data/Growth_Data.txt > shoot_per_root.txt

wc -l shoot_per_root.txt

This prints:

awk: cmd. line:1: (FILENAME=awk_data/Growth_Data.txt FNR=123) fatal: division by zero attempted

121 shoot_per_root.txt

This didn’t turn out as expected. If you look at the error message it tells you that AWK stopped at line 123: the first row with an empty root length. The error happens because an empty field is treated as a 0, and a division by 0 is not possible. You also see that the file shoot_per_root.txt was generated, but it has only 121 lines instead of 594. If you missed the error message and only looked at head, the file would look fine. This is why you want to make it a habit to count lines after each step.

To fix this, you need to skip rows with an empty root length or shoot weight, and also print a header for the new column. The command now has two parts, each with its own pattern and action.

A command with several parts gets long on one line. AWK lets you spread the part between the single quotes over several lines, so you can put each pattern and its action on a line of its own. The command does exactly the same, it is only easier to read. You can copy it into your terminal as it is: the terminal waits for the closing quote before it runs the command.

# Part 1: on line 1, print the current header and add a name for the new column
# Part 2: on the other lines with both values, print the line plus the result
awk -F'\t' -v OFS='\t' '
  NR == 1                          { print $0, "shoot_per_root" }
  NR > 1 && $4 != "" && $5 != ""   { print $0, $5 / $4 }
' awk_data/Growth_Data.txt > shoot_per_root.txt

# Check the result
head -3 shoot_per_root.txt
wc -l shoot_per_root.txt

This prints:

Nutrient    Condition   Experiment  Rootlength  FW_shoot_mg shoot_per_root
low_P   mock    ex61    40.4    6.6 0.163366
low_P   mock    ex61    38  6.8 0.178947

572 shoot_per_root.txt

Your file now has 572 lines: the header plus 571 plants. You want to make it a habit to check, whether this adds up: 594 plants, minus 15 without a root length, minus 8 without a shoot weight, is 571. No plant misses both values, so no row is subtracted twice.

Keep the opening brace on the same line as its pattern

When you spread a command over several lines, the opening { of an action must stay on the same line as its pattern. If you move the { to the next line, AWK reads the pattern on its own (which prints every matching line) and the action on its own (which runs on every line). You get no error, only a different result.

The same rule holds for END, which you meet in the next section. If its { is on the next line, you do get an error:

awk: cmd. line:3: END blocks must have an action part

Here “action part” means the { … } after END. Move the { back up to the line with END.

Sum and mean of a column: variables and `END`

Start with one number: the total shoot weight of all plants. For this, AWK needs a variable, a name that holds a value. You do not need to create it first: AWK makes the variable the first time you use it, and it starts at 0.

awk -F'\t' 'NR > 1 && $5 != "" { sum += $5 } END { print sum }' awk_data/Growth_Data.txt

This prints:

9715.23

Taken apart:

sum += $5: add the shoot weight of this line to the variable sum. += means “add to the value that is already in sum”.
END { print sum }: END is a special pattern. Its action runs once, after AWK has read the last line. By then, sum holds the total.

You use END when you want one answer about the whole file, such as a total, a count or a mean. AWK only knows that answer after it has read every line. When you want a result for each line, such as the filtered rows or the new column above, you don’t need END.

The name sum is your own choice. You could call it total or weight instead, and get the same result. Names such as NR, NF and END are different: they belong to AWK and have a fixed meaning.

To calculate the mean, you need a second variable that counts the number of plants measured. Here it is called n (short for “number of plants”), and n++ adds 1 to it each time the action runs, so once for each line that matches the pattern (here n++ does the same as if you would write n += 1). The ; separates two steps inside one action:

awk -F'\t' '
  NR > 1 && $5 != "" { sum += $5; n++ }
  END                { print n, sum, sum / n }
' awk_data/Growth_Data.txt

This prints the number of plants, the total and the mean:

586 9715.23 16.5789

When you run such calculations, you want to check if 586 adds up: 594 plants, minus the 8 without a shoot weight, is 586.

Now leave out the check for empty fields, and then also NR > 1 to see how slight differences in the code can make a difference:

# Without $5 != ""
awk -F'\t' '
  NR > 1 { sum += $5; n++ }
  END    { print n, sum, sum / n }
' awk_data/Growth_Data.txt

# Also without NR > 1
awk -F'\t' '
  { sum += $5; n++ }
  END { print n, sum, sum / n }
' awk_data/Growth_Data.txt

This prints:

594 9715.23 16.3556

595 9715.23 16.3281

The total is the same all three times. AWK adds an empty field as 0, and the header text FW_shoot_mg as 0 too, so the sum does not change. But n still counts these lines and because of that the mean drops. This means that a mean that looks right does not mean that it is actually right. This is a good example to carefully think about what your code is doing since the code runs without any error but the output is still incorrect. In this case the difference is small, but depending on your data, such silent errors can have a large effect.

Round the results: `printf`

The means above have more decimals than the measurements. To round them, use printf instead of print. With printf, you write the line you want to print as a template in double quotes, with placeholders where the numbers go. After the template, you list the values, separated by commas:

awk -F'\t' '
  NR > 1 && $5 != "" { sum += $5; n++ }
  END                { printf "n: %d\tmean: %.1f\n", n, sum / n }
' awk_data/Growth_Data.txt

This prints:

n: 586  mean: 16.6

Taken apart: printf "n: %d\tmean: %.1f\n", n, sum / n

"n: %d\tmean: %.1f\n" is the template. Everything in it is printed as it is, except the placeholders and the two special characters:
- %d is a placeholder for a whole number.
- %.1f is a placeholder for a number with one decimal. The number after the dot sets the decimals: %.2f gives two, %.0f none.
- \t is a tab, and \n a new line.
n, sum / n are the values. The first value fills the first placeholder, the second value the second. So n goes into %d, and sum / n into %.1f.

Unlike print, printf does not start a new line by itself, and it ignores OFS. Leave out the \n, and the prompt of your terminal ends up on the same line as the result. The tab between the values is there only because \t is in the format.

Placeholders and values must match. AWK fills the placeholders in order and does not check what you meant. If you swap the values, you get n: 16 mean: 586.0 and no error. %d also cuts off the decimals instead of rounding: 16.58 becomes 16. Use %.0f to round to a whole number.

Smallest and largest value

Before you calculate with a column, it helps to know its smallest and largest value. A typo such as 404 instead of 40.4 shows up at once as the largest value, while a mean would hide it.

awk -F'\t' '
  NR > 1 && $4 != "" {
    if (n == 0)   { min = $4; max = $4 }
    if ($4 < min) { min = $4 }
    if ($4 > max) { max = $4 }
    n++
  }
  END { print n, min, max }
' awk_data/Growth_Data.txt

This prints the number of plants with a root length, the smallest and the largest root length:

579 28.73 135.49

The action has several steps, so it is spread over several lines too. As before, the { stays on the line with the pattern.

Taken apart:

if (...) { ... }: AWK runs the step in the braces only when the test in the brackets is true.
if (n == 0) { min = $4; max = $4 }: n counts the rows with a root length. It is 0 only on the first of them, so this line gives min and max their start value from that row.
if ($4 < min) { min = $4 }: if this root length is smaller than the smallest one so far, it becomes the new smallest. The next line does the same for the largest.
n++: count this row. It comes after the if lines, so n is still 0 while AWK reads the first row.

Now leave out the line if (n == 0) .... Before you run it, predict what min and max will be.

awk -F'\t' '
  NR > 1 && $4 != "" {
    if ($4 < min) { min = $4 }
    if ($4 > max) { max = $4 }
    n++
  }
  END { print n, min, max }
' awk_data/Growth_Data.txt

This prints:

579  135.49

Look closely: there are two spaces between the numbers, because the smallest value is missing. min never gets a value, because no root length is smaller than a variable that was never set: AWK treats it as 0 here. print then shows it as empty text. The largest value is still right, because every root length is larger than 0. There is no error, so give min and max a start value from the data, not 0.

Count and mean per group: arrays

sum and n are single variables: each holds one value for the whole file. You can do more than that, for example count the plants per nutrient level. For this, you need one count per group. You get this by using an array, a variable that holds one value per name. n["low_P"] is the count for low_P, and n["high_P"] the count for high_P. Just like a single variable, AWK makes the value for a new name the first time you use it, and it starts at 0.

awk -F'\t' '
  NR > 1 { n[$1]++ }
  END    { for (group in n) { print group, n[group] } }
' awk_data/Growth_Data.txt

This prints:

low_P 300
high_P 294

Taken apart:

NR > 1 { n[$1]++ }: for each row of data, add 1 to the count of its nutrient level (in column 1 the levels are: low_P or high_P). Piece by piece::
- n is the array
- [$1]says which count to use: the one named after the text in column 1. On a low_P row that is n["low_P"], on a high_P row n["high_P"]
- ++ adds 1 to that count
- AWK does not need to know the levels in advance. The first time it sees a new text in column 1, it makes a new count for it, starting at 0. So the different values in column 1 become your groups. After the first low_P row, n["low_P"] is 1, after the second it is 2, and so on.
END { ... }: as before, this runs once, after the last line
for (group in n) { print group, n[group] }: go through all names in n. For each name, put it into the variable group, and print the name and its count

You can check the result with tools from the CLI workshop:

cut -f1 awk_data/Growth_Data.txt | sort | uniq -c

This prints:

      1 Nutrient
    294 high_P
    300 low_P

To calculate the mean per group, you do the same as for the whole file, but with two arrays: sum for the sum per group and n for the count per group. sum[$1] += $4 adds the root length to the sum of its group.

# Without the rule for empty fields
awk -F'\t' '
  NR > 1 { sum[$1] += $4; n[$1]++ }
  END    { for (group in sum) { print group, n[group], sum[group] / n[group] } }
' awk_data/Growth_Data.txt

# With the rule: skip empty root lengths
awk -F'\t' '
  NR > 1 && $4 != "" { sum[$1] += $4; n[$1]++ }
  END                { for (group in sum) { print group, n[group], sum[group] / n[group] } }
' awk_data/Growth_Data.txt

This prints:

low_P 300 57.9494
high_P 294 88.9501

low_P 290 59.9477
high_P 289 90.489

Without the rule, AWK adds 0 for each empty root length and still counts the plant. This is another silent error: both means come out too low, and nothing warns you.

Exercise

Start from the command with the rule $4 != "". Change only the END part, and before each run predict what you will see:

Replace print group, n[group], sum[group] / n[group] with print group. What does group hold?
Now try print group, sum[group]. What is the difference between group and sum[group]?
Change for (group in sum) to for (group in n). Does the output change? Why, or why not?

Click me to see an answer

group holds the names in the array, one per round: low_P, then high_P.
sum[group] is the value stored under that name, the summed root length: low_P 17384.8 and high_P 26151.3. group is the name, sum[group] what is stored under it.
No. Each row adds to sum and to n with the same name, so both arrays hold the same names. It does not matter which one the loop goes through.

group is a name you choose, just like sum. You can call it x instead, but then change it everywhere in the END part. If you change it only in for (...), AWK still uses the old group in print, which is empty here, and you get an error.

Mean per nutrient and condition

To group data by more than one column, you can glue the columns together into one name: $1"-"$2 is column 1, a dash and column 2, for example low_P-mock. The dash goes in double quotes, because it is text here. A dash appears in none of the values, so you can split the name again later if you need to. Store this name in a variable, here called key, and use it in both arrays.

awk -F'\t' '
  NR > 1 && $4 != "" { key = $1"-"$2; sum[key] += $4; n[key]++ }
  END                { for (group in sum) { printf "%s\t%d\t%.1f\n", group, n[group], sum[group] / n[group] } }
' awk_data/Growth_Data.txt | sort

This prints:

high_P-129E 103 98.4
high_P-13C  85  97.7
high_P-mock 101 76.4
low_P-129E  99  69.9
low_P-13C   86  64.2
low_P-mock  105 47.1

One command gave you the count and the mean for all six groups, sorted. In Excel, this would be a pivot table that you build by clicking, with no record of how you made it. Here the command is the record.

Taken apart:

printf with the format "%s\t%d\t%.1f\n": the group name, a tab, the count as a whole number, a tab, and the mean with one decimal. %s is new: it stands for text, here the group name.
| sort: AWK goes through the names in an array in no fixed order. Sort the result, so that it is easy to read and to compare between runs.

Forget the quotes, and the dash becomes a minus

If you write key = $1-$2 without the quotes, AWK reads the dash as a minus and subtracts column 2 from column 1. Text that does not start with a number counts as 0, so low_P and high_P both become 0, and 129E becomes 129. The command runs without an error, but prints this:

-129    202 84.4
-13 171 80.8
0   206 61.5

Three groups instead of six, and low_P and high_P plants mixed together. A quick check catches it: you expect 2 nutrient levels × 3 conditions = 6 groups, so count the lines of the output, and look at the group names.

Without the rule $4 != "", four of the six means are lower. For example, low_P with 129E gives 64.7 instead of 69.9, because the 8 plants without a root length count as 0 mm.

Exercise

Calculate the mean shoot weight per Condition, with one decimal. How many plants are in each group?
Before you run it, predict: how many groups do you expect, and what should the counts add up to? Check the output against both numbers.

Click me to see an answer

Condition is column 2, and the shoot weight is column 5. Skip the empty shoot weights:

awk -F'\t' '
  NR > 1 && $5 != "" { sum[$2] += $5; n[$2]++ }
  END                { for (group in sum) { printf "%s\t%d\t%.1f\n", group, n[group], sum[group] / n[group] } }
' awk_data/Growth_Data.txt | sort

This prints:

129E    208 17.6
13C 173 15.3
mock    205 16.6

You expect 3 groups, one per condition. The counts should add up to the 594 plants minus the 8 without a shoot weight: 586, the same number as in the mean for the whole file. And 208 + 173 + 205 = 586. If the counts add up to more, the command did not skip the empty fields or the header.

Exercise

Calculate per nutrient and condition what percentage of plants has no root length. You need two counts per group: all plants, and the plants with an empty root length. You can do this with two parts, each with its own pattern.
Before you run it, predict: what should the counts of plants without a root length add up to?
In the END part, does it matter whether the loop goes through the array with all plants or the array with the missing ones? Try both.

Click me to see an answer

awk -F'\t' '
  NR > 1               { key = $1"-"$2; n[key]++ }
  NR > 1 && $4 == ""   { key = $1"-"$2; missing[key]++ }
  END                  { for (group in n) { printf "%s\t%d\t%d\t%.1f\n", group, n[group], missing[group], 100 * missing[group] / n[group] } }
' awk_data/Growth_Data.txt | sort

This prints the group, the number of plants, the number without a root length, and the percentage:

high_P-129E 103 0   0.0
high_P-13C  87  2   2.3
high_P-mock 104 3   2.9
low_P-129E  107 8   7.5
low_P-13C   88  2   2.3
low_P-mock  105 0   0.0

The missing counts add up to 15, the number of empty root lengths in the table, and the group sizes add up to all 594 plants. With 7.5%, low_P with 129E misses more root lengths than the other groups.

It does matter which array the loop goes through. With for (group in missing), you get only four lines:

high_P-13C  87  2   2.3
high_P-mock 104 3   2.9
low_P-129E  107 8   7.5
low_P-13C   88  2   2.3

missing only gets a name when AWK finds a plant without a root length in that group. The two groups without missing values never get one, so the loop skips them, again without an error. In the exercise before, the choice did not matter, because each row added to both arrays. So loop through an array that holds every group.

What you learned

AWK can calculate with fields: print $0, $5 / $4 adds a new column. A command can stop halfway with an error and still leave a file, so count the lines after each step.
A long AWK command can be spread over several lines, one pattern and action per line. Keep the opening { on the same line as its pattern.
A variable (sum += $5) and END give a total or a mean for the whole file. An array (sum[$1] += $5) gives one per group.
printf prints with a format that you choose, for example %.1f for one decimal. It needs \n for a new line.
if (...) { ... } runs a step only when a test is true. Give min and max a start value from the first row with data, not 0.
An array only holds the names AWK has added to it. In END, loop through an array that holds every group.
To group by two columns, glue them together with text in quotes: $1"-"$2. Without the quotes, the dash is a minus.
Skip empty fields before you calculate, and check that the counts add up to the number of rows you expect.

On the next page, sed basics, you change text in a file: search and replace, delete lines, and clean up a file with Windows line endings.

Add a column

Sum and mean of a column: variables and END

Round the results: printf

Smallest and largest value

Count and mean per group: arrays

Mean per nutrient and condition

What you learned

Sum and mean of a column: variables and `END`

Round the results: `printf`