AWK on a table

On this page, you use AWK on a real table with plant growth measurements. The same commands work on any table in plain text, and many bioinformatics tools write their results that way, for example BLAST, featureCounts or HMMER. Here, you look at the table, choose columns, and keep the rows you need. Along the way, you meet the most common trap when you work with tables: missing values that quietly change your results.

Get the data: curl and tar

All example files for this tutorial come in one file, awk_data.tar.gz. A .tar.gz file packs many files into one and compresses them, similar to a .zip file.

# Go to your home folder, and make a folder for this tutorial
cd ~
mkdir awk_tutorial
cd awk_tutorial

# Download the data
# -L: if the link sends you on to another address, follow it
# -O: save the file under its own name, awk_data.tar.gz
curl -L -O "https://raw.githubusercontent.com/ndombrowski/AWK_tutorial/main/data/awk_data.tar.gz"

# Unpack it. Used options:
# -x extract, -z it is compressed (.gz), -f the file name follows
tar -xzf awk_data.tar.gz

# Check: you should see awk_data and awk_data.tar.gz
ls

# Check: you should see 16 files
ls awk_data
ls awk_data | wc -l

On this page you only need Growth_Data.txt. The other files are 16S sequences, sequencing reads, a table with information on bacterial strains, and a second version of the growth table, which you use on later pages.

Look at the table first

Before you run any command on a table, you want to look at it first. You at least want to know: how many lines it has, and what separates the columns?

# The first 5 lines
head -5 awk_data/Growth_Data.txt

# The number of lines
wc -l awk_data/Growth_Data.txt

This prints:

Nutrient    Condition   Experiment  Rootlength  FW_shoot_mg
low_P   mock    ex61    40.4    6.6
low_P   mock    ex61    38  6.8
low_P   mock    ex61    74.7    8.1
low_P   mock    ex61    34.4    8.3

595 awk_data/Growth_Data.txt

You see that the table has a header, a first line with the column names followed by 594 rows of measurements. Each row is one plant that was measured, and the table has the following columns:

• Nutrient: the plant grew with low or high phosphorus (low_P, high_P)
• Condition: the plant was inoculated with nothing (mock) or with one of two bacteria (129E, 13C)
• Experiment: the experiment the plant was part of (ex61, ex64, ex65)
• Rootlength: root length in mm
• FW_shoot_mg: shoot fresh weight in mg

The columns look like they are separated by spaces. However, you cannot see what is really there, because spaces and tabs look the same on screen. To see what really separates your columns you can use cat -A, which shows the invisible characters:

head -3 awk_data/Growth_Data.txt | cat -A

This prints:

Nutrient^ICondition^IExperiment^IRootlength^IFW_shoot_mg$
low_P^Imock^Iex61^I40.4^I6.6$
low_P^Imock^Iex61^I38^I6.8$

Here, ^I is a tab, and $ marks the end of each line. So this tells you that you work with a tab-separated table. You need to know this for the next steps.

TipTip: cat -A on a Mac (optional)

On a Mac, cat -A may not work and you can try cat -et instead.

The shape of an AWK command

Every AWK command on this page has the same shape:

awk -F'\t' 'pattern { action }' file

• -F'\t' tells AWK that the columns are separated by tabs. -F stands for field separator. A field is one value in a line, like one cell in a spreadsheet row.
• 'pattern { action }' is the AWK program, it always needs to be between single quotes.
  • The pattern says which lines to work on.
  • The action, between curly brackets, says what to do with them.
• file is the file AWK reads.

AWK reads the file one line at a time. For each line, it checks the pattern. If the line matches, AWK runs the action. You can leave out either part: without a pattern, the action runs on every line. Without an action, AWK prints the lines that match.

print: choose columns

AWK gives each field of a line a number: $1 is the first field, $2 the second, and so on. $0 is the whole line. To print the first column:

# Print column 1 of every line, and show the first 3 lines of the result
awk -F'\t' '{ print $1 }' awk_data/Growth_Data.txt | head -3

This prints:

Nutrient
low_P
low_P

To print several columns, separate the fields you want with a comma:

# Print column 1 (Nutrient) and column 4 (Rootlength)
awk -F'\t' '{ print $1, $4 }' awk_data/Growth_Data.txt | head -3

This prints:

Nutrient Rootlength
low_P 40.4
low_P 38

AWK by default puts a space between the printed columns. To keep the result a tab-separated table, tell AWK to use a tab in the output too. -v OFS='\t' sets the output field separator:

awk -F'\t' -v OFS='\t' '{ print $1, $4 }' awk_data/Growth_Data.txt | head -3

This prints:

Nutrient    Rootlength
low_P   40.4
low_P   38

You can do the same thing with cut -f1,4 (something you might have seen in the CLI workshop). However, you will see why AWK becomes more useful in the next sections.

NF: work with the number of fields

Before that, you learn a quick check that is useful for any table you work with: count the fields in each line to make sure that all rows have the same number of columns. Here, NF is the number of fields in the current line:

# Count the fields per line, then count how often each number occurs
awk -F'\t' '{ print NF }' awk_data/Growth_Data.txt | sort | uniq -c

This prints:

    595 5

This tells you that all 595 lines have 5 fields. If some lines had 4 or 6, for example because a text cell contained a tab, this would immediately tell you that something in the table would be off. So this is a quick command to check that your data has the right structure.

QuestionExercise

1. Print the columns Experiment and FW_shoot_mg, separated by a tab. Before you run it, predict: will the header line be in the output? Then look at the first 4 lines of the result.

AnswerClick me to see an answer

Experiment is column 3 and FW_shoot_mg is column 5:

awk -F'\t' -v OFS='\t' '{ print $3, $5 }' awk_data/Growth_Data.txt | head -4

This prints:

Experiment  FW_shoot_mg
ex61    6.6
ex61    6.8
ex61    8.1

The header is in the output. That is because the command has no pattern, so AWK runs the action on every line, the header too. The next section shows how to leave it out.

NR: work with the header line

NR is the number of the record, the line AWK is reading now. You can use it in a pattern to choose lines by their position:

# Print only line 1, the header
awk -F'\t' 'NR == 1' awk_data/Growth_Data.txt

# Print all lines after the header: show the first 2, and count them
awk -F'\t' 'NR > 1' awk_data/Growth_Data.txt | head -2
awk -F'\t' 'NR > 1' awk_data/Growth_Data.txt | wc -l

This prints:

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

594

These commands have only a pattern and no action, so AWK prints the lines that match. NR > 1 skips the header and keeps all 594 rows of data. You need it often, as you will see in the next sections.

Subset rows by text

Sometimes you only care about part of the data. For example, you might want to keep only the plants grown with low phosphorus. For this, you use the pattern $1 == "low_P", which checks every line and keeps it only if column 1 is exactly low_P. The first command shows the first few matching lines. The second counts how many rows you now work with:

awk -F'\t' '$1 == "low_P"' awk_data/Growth_Data.txt | head -3
awk -F'\t' '$1 == "low_P"' awk_data/Growth_Data.txt | wc -l

This prints:

low_P   mock    ex61    40.4    6.6
low_P   mock    ex61    38  6.8
low_P   mock    ex61    74.7    8.1

300

Two details matter here:

• Text goes between double quotes: "low_P". Without quotes, AWK reads low_P as the name of a variable, a name that holds a value, like a label on a box. You never gave low_P a value, so it is empty, and no row matches.
• To compare, use two equal signs: ==. If you use only one equal sign, this command does something else and instead puts a value into a field. Note here how AWK gives you no error message. Only by looking at the output, and counting the number of lines of the output, do you see that the command does something different from what you wanted:

# Wrong command and using one = instead of two ==
awk -F'\t' '$1 = "low_P"' awk_data/Growth_Data.txt | head -3
awk -F'\t' '$1 = "low_P"' awk_data/Growth_Data.txt | wc -l

This prints:

low_P Condition Experiment Rootlength FW_shoot_mg
low_P mock ex61 40.4 6.6
low_P mock ex61 38 6.8

595

You see that with this command every line is kept, but column 1 of every line now says low_P, even in the header. A quick wc -l shows the problem: 595 lines, the same as the whole file.

Why not use grep?

If you have followed the CLI workshop you might remember grep, which keeps lines that contain a word. If you wanted to only work with the plants with bacterium 13C, both tools give the same count:

grep '13C' awk_data/Growth_Data.txt | wc -l
awk -F'\t' '$2 == "13C"' awk_data/Growth_Data.txt | wc -l

This prints:

175
175

But there is an important difference: grep searches the whole line, not one column. This can easily result in problems, for example if you would have searched for 13 instead of 13C:

grep '13' awk_data/Growth_Data.txt | wc -l
grep '13' awk_data/Growth_Data.txt | head -3

This prints:

211

low_P   mock    ex61    55.3    13.3
low_P   mock    ex61    48.7    13.9
low_P   mock    ex64    46.93   13.52

In total this command gives you 36 extra lines. That is because 13 also appears in shoot weights such as 13.3. You see this in the first lines grep finds that are not even 13C plants, but mock plants. In contrast, AWK compares only column 2, so this cannot happen. This makes the AWK command much safer for filtering tables.

Subset rows by a number

Comparisons also work with numbers: > (greater than), < (less than), >= and <= (greater or less than, or equal). For example, you could only keep plants with roots longer than 50 mm:

awk -F'\t' '$4 > 50' awk_data/Growth_Data.txt | head -3
awk -F'\t' '$4 > 50' awk_data/Growth_Data.txt | wc -l

This prints:

Nutrient    Condition   Experiment  Rootlength  FW_shoot_mg
low_P   mock    ex61    74.7    8.1
low_P   mock    ex61    66.5    8.9

476

Important: Note how the header is in the result? This might seem weird, since column 4 of the header is the word Rootlength, not a number. However, when a value is not a number, AWK compares it as text, and in that comparison Rootlength counts as larger than 50. To avoid this, add NR > 1 to skip the header. To combine patterns, use &&, which means “and”: both conditions must be true.

awk -F'\t' 'NR > 1 && $4 > 50' awk_data/Growth_Data.txt | wc -l

This prints:

475

You sometimes might also need ||, which means “or”: use this if you want at least one of the conditions to be true.

# Low phosphorus and no bacteria: show the first 3 lines
awk -F'\t' '$1 == "low_P" && $2 == "mock"' awk_data/Growth_Data.txt | head -3

# Either of the two bacteria: show the first 3 lines
awk -F'\t' '$2 == "129E" || $2 == "13C"' awk_data/Growth_Data.txt | head -3

This prints:

low_P   mock    ex61    40.4    6.6
low_P   mock    ex61    38  6.8
low_P   mock    ex61    74.7    8.1

low_P   13C ex61    54.3    1.56
low_P   13C ex61    81  4
low_P   13C ex61    64  5.7

Why we care: With one line you just answered “which plants grew with low phosphorus and no bacteria?”. The same line works on a table with 50 rows or with a million, and you can save it and run it again next month.

QuestionExercise

1. How many plants grew with high phosphorus and bacterium 13C? Before you run it, predict roughly: the table has 594 plants, in 2 nutrient levels × 3 conditions = 6 groups. About how many plants do you expect in one group?
2. How many plants have a shoot weight above 20 mg?

AnswerClick me to see an answer

# 1. Two text conditions with &&
awk -F'\t' '$1 == "high_P" && $2 == "13C"' awk_data/Growth_Data.txt | wc -l

# 2. NR > 1 keeps the header out
awk -F'\t' 'NR > 1 && $5 > 20' awk_data/Growth_Data.txt | wc -l

This prints:

87

140

So 87 plants grew with high phosphorus and 13C, and 140 plants have a shoot weight above 20 mg.

For question 1, 594 / 6 gives about 99 plants per group, so 87 is close to what you expect and we also now know that the groups are not exactly the same size. A result such as 0, or all 594 plants, would tell you at once that the command is wrong.

Working with missing values: empty fields

The next example shows why it is important to check your data carefully. Here, you look for plants with very short roots, below 10 mm:

awk -F'\t' 'NR > 1 && $4 < 10' awk_data/Growth_Data.txt | wc -l
awk -F'\t' 'NR > 1 && $4 < 10' awk_data/Growth_Data.txt | head -3

This prints:

15

high_P  mock    ex61        18.2
high_P  mock    ex64        22.4
high_P  mock    ex64        26

This tells you that there seem to be 15 plants with short roots. However, if you look at the table, you notice that none of them actually has a root length below 10. Instead, if you look closely, you see that these rows have no root length at all. If you use cat -A you can see exactly what is going on:

awk -F'\t' 'NR > 1 && $4 < 10' awk_data/Growth_Data.txt | head -3 | cat -A

This prints:

high_P^Imock^Iex61^I^I18.2$
high_P^Imock^Iex64^I^I22.4$
high_P^Imock^Iex64^I^I26$

After the column with the experiment number, you see two tabs side by side, ^I^I, with nothing between them: this is an empty field and also how a missing value often looks, for example in a table saved from Excel with empty cells. Now count how many empty fields each column has:

# Empty root length (column 4) and empty shoot weight (column 5)
awk -F'\t' '$4 == ""' awk_data/Growth_Data.txt | wc -l
awk -F'\t' '$5 == ""' awk_data/Growth_Data.txt | wc -l

This prints:

15
8

Altogether, this tells you that AWK did not give an error, but it did something you didn’t really want. It compared the empty field with 10 as text, and empty text counts as smaller than anything. So all 15 plants without a root length passed the filter $4 < 10. In calculations, AWK will treat an empty field as 0, as you see on the next page.

ImportantImportant: skip empty fields before you compare or calculate

Before you compare or calculate with a column, keep only the rows that have a value in it, with $4 != "". != means “is not equal to”, and "" is empty text, so $4 != "" means “column 4 is not empty”:

awk -F'\t' 'NR > 1 && $4 != "" && $4 < 10' awk_data/Growth_Data.txt | wc -l

This prints:

0

No plant in this table has roots shorter than 10 mm.

Why -F'\t' matters

AWK can also run without -F'\t'. It then splits lines at any run of spaces and tabs. For most lines in this table, the result is the same. But AWK then treats the two tabs around an empty field as one separator, and the values move one column to the left. Line 123 is the first row with an empty root length:

# View line 123, and its column 4 with -F'\t'
awk -F'\t' 'NR == 123' awk_data/Growth_Data.txt

# Column 4 of line 123 is empty, so awk prints an empty line
# cat -A shows a $ where each line ends, so you can see the empty line
awk -F'\t' 'NR == 123 { print $4 }' awk_data/Growth_Data.txt | cat -A

# View column 4 of the same line without -F'\t'
awk 'NR == 123 { print $4 }' awk_data/Growth_Data.txt | cat -A

# View the number of fields per line without -F'\t'
awk '{ print NF }' awk_data/Growth_Data.txt | sort | uniq -c

This prints:

high_P  mock    ex61        18.2

$

18.2$

     23 4
    572 5

The first command prints the whole of line 123: high_P, mock, ex61, an empty Rootlength, and 18.2 for FW_shoot_mg. With -F’, column 4 is empty, as it should be: cat -A shows only the $ for the end of the line, with nothing before it. Without -F'\t', column 4 becomes 18.2. That is the shoot weight, which has moved one column to the left into the place of the root length. If you calculated a mean root length now, this plant would count with a root length of 18.2 mm, and AWK would not warn you.

The last command shows that this happens in 23 lines. That is the 15 rows with an empty root length plus the 8 rows with an empty shoot weight from above. Without -F'\t', each of these lines has only 4 fields. The NF check you did above shows the problem right away, so it is worth running on every new table.

The count tells you that 23 lines are different, but not which ones. To find them, use NF as a pattern: NF != 5 keeps only the lines that do not have 5 fields. print NR, $0 prints the line number in front of each line:

# Which lines do not have 5 fields without -F'\t'?
awk 'NF != 5 { print NR, $0 }' awk_data/Growth_Data.txt | head -3

This prints:

33 low_P    mock    ex61    49.6
34 low_P    mock    ex61    53.6
123 high_P  mock    ex61        18.2

Lines 33 and 34 have no shoot weight, and line 123 has no root length. Together, the 15 empty root lengths and 8 empty shoot weights make the 23 lines. On a new table, the line numbers tell you where to look, for example with awk 'NR == 33'.

QuestionExercise

1. How many plants have a shoot weight below 5 mg? First write the command without skipping empty fields, and run it. Before you add the check that skips empty fields, predict: how many plants will this check remove, and why? Then add the check and compare the result with your prediction.

AnswerClick me to see an answer

# Without the check
awk -F'\t' 'NR > 1 && $5 < 5' awk_data/Growth_Data.txt | wc -l

# With the check: skip empty shoot weights
awk -F'\t' 'NR > 1 && $5 != "" && $5 < 5' awk_data/Growth_Data.txt | wc -l

This prints:

10
2

Only 2 plants have a shoot weight below 5 mg. The rule removes 8 plants: the 8 empty shoot weights you found above. The first command keeps them, because an empty field passes $5 < 5.

TipTip: fill empty fields with NA (optional)

Empty fields are easy to miss. One way to make them visible is to fill each empty field with NA (not available) and save the result as a new file. In this table, only column 4 and column 5 have empty fields, so you fill those two:

# If column 4 is empty, put NA into it.
# If column 5 is empty, put NA into it.
# Then print every line.
# print with nothing after it prints the whole line. It is a short form of print $0.
# The command is spread over several lines to make it easier to read
# (more on this on the next page, "Calculate with AWK").
awk -F'\t' -v OFS='\t' '
  $4 == "" { $4 = "NA" }
  $5 == "" { $5 = "NA" }
  { print }
' awk_data/Growth_Data.txt > Growth_Data_NA.txt

# Check: same number of lines, and line 123 now has NA
wc -l Growth_Data_NA.txt
awk -F'\t' 'NR == 123' Growth_Data_NA.txt

This prints:

595 Growth_Data_NA.txt
high_P  mock    ex61    NA  18.2

Also here -v OFS='\t' matters. When you change a field, AWK rebuilds the whole line and puts the output separator between the fields. Without -v OFS='\t', that is a space. Only the changed lines get spaces, the others keep their tabs, so the file now mixes both. cat -A shows this for line 123 (changed) and line 2 (not changed) of such a file:

high_P mock ex61 NA 18.2$
low_P^Imock^Iex61^I40.4^I6.6$

If you do not know which columns have empty fields, or there are many columns, you can let AWK go through all fields with a loop. You do not need to remember this form. Keep it in your notes, or ask an LLM for it and check the result as shown here. In this table it gives exactly the same file:

# For each field in the line (i = 1 up to the number of fields NF):
# if the field is empty, put NA into it. Then print the line.
awk -F'\t' -v OFS='\t' '
  {
    for (i = 1; i <= NF; i++) {
      if ($i == "") { $i = "NA" }
    }
    print
  }
' awk_data/Growth_Data.txt

Now the missing values are visible when you look at the file. Also, the columns no longer shift in tools that split at spaces and tabs, such as AWK without -F'\t', or join and column -t with their default settings.

But when you do this, it’s important to know that NA does not make comparisons safe. NA is text too, and in a text comparison it counts as larger than 50:

awk -F'\t' 'NR > 1 && $4 > 50' Growth_Data_NA.txt | wc -l
awk -F'\t' 'NR > 1 && $4 != "NA" && $4 > 50' Growth_Data_NA.txt | wc -l

This prints:

490
475

So with NA, the rule stays the same, only with $4 != "NA" instead of $4 != "".

What you learned

• An AWK command has the shape awk -F'\t' 'pattern { action }' file.
• $1, $2, … are the fields of a line, $0 the whole line, NF the number of fields, NR the line number.
• Compare with ==, !=, >, <, and combine with && (and) and || (or). Text goes in double quotes.
• Look at the table first: cat -A for tabs and empty fields, NF for the number of fields per line.
• Empty fields cause errors without a warning: skip them with $4 != "" before you compare or calculate.
• After each step, check the result: count the lines, and look at it with head.

You do not need to learn these commands by heart. Instead, keep your own notes: a text file or notebook with the commands you have checked, each with one line on what it does and which trap it avoids (for example “skips empty fields in column 4”). A checked command in your notes is worth more than one you half remember, and more than a new one from an LLM that you still have to check.

On the next page, Calculate with AWK, you add a new column, and calculate sums, counts and means per group.