Combining two files

On this page, you add information from one table to another: the genus of each bacterial strain, from the strain metadata, to each plant in the growth data. Combining two tables by a shared name is called a join. It is one of the most common jobs in bioinformatics, for example when you add sample information to a table of read counts, or a taxonomy to a list of sequences. It is also a job where small mistakes give a wrong result without any error message, so a large part of this page is about checking the result.

You use the same folder as on the previous pages. Besides awk_data/Growth_Data.txt, you need two versions of the strain metadata: mmc5_metadata_fixed.txt, without Windows line endings, from the sed page, and mmc5_metadata_named.txt, with two strains renamed, from the page More useful AWK. If you do not have them anymore, make them again:

sed 's/\r$//' awk_data/mmc5_metadata.txt > mmc5_metadata_fixed.txt
sed 's/^R129_E/129E/; s/^R13_C/13C/' mmc5_metadata_fixed.txt > mmc5_metadata_named.txt

Look at both files first

The metadata table has 12 columns. Look at the first six, and count the fields per line:

head -3 mmc5_metadata_fixed.txt | cut -f1-6
awk -F'\t' '{ print NF }' mmc5_metadata_fixed.txt | sort | uniq -c

This prints:

strain  Order   Family  Genus   compartment host
Root123D2   Rhizobiales Bradyrhizobiaceae   Afipia  Root    Arabidopsis
Root381 Rhizobiales Bradyrhizobiaceae   Bosea   Root    Arabidopsis

     28 12

Each line describes one strain, and all 28 lines (the header and 27 strains) have 12 fields. Column 1 is the name of the strain, column 4 its genus.

Why `-F'\t'` matters here too

Some values in this table contain a space, for example the medium 50% TSB or the column name Taxonomy group. Check what happens if you don’t use -F'\t', and see how AWK splits the lines at these spaces:

awk '{ print NF }' mmc5_metadata_fixed.txt | sort | uniq -c

This prints:

     15 12
     12 13
      1 14

You see that 13 lines now have more than 12 fields, so the NF check shows that something is off.

But the 15 lines with 12 fields are not all right either. If you open the table in a spreadsheet program such as Excel, you see three strains with an empty cell in the Genus column: Root154, Root700 and Root77. All three grew on 50% TSB, a value with a space. You can also find the empty cells with a command, as on AWK on a table:

awk -F'\t' '$4 == ""' mmc5_metadata_fixed.txt | cut -f1,4,10

This prints:

Root154     50% TSB
Root700     50% TSB
Root77      50% TSB

Without -F'\t', that line loses one field (the empty genus) and gains one (the space in 50% TSB), so the count looks fine while the table itself is actually not what you want:

# cat -A marks the end of the line with $, so you can see an empty value
awk -F'\t' '$1 == "Root154" { print $4 }' mmc5_metadata_fixed.txt | cat -A
awk '$1 == "Root154" { print $4 }' mmc5_metadata_fixed.txt | cat -A

This prints:

$

Root$

With -F'\t', column 4 of Root154 is empty (only the $ for the end of the line), which is correct. Without it, column 4 is Root, the value from column 5. So always set the field separator, also when a table looks simple.

The names in both files

The two tables share the names of the bacteria: column 2 of the growth data, and column 1 of the metadata. First look at the names in the growth data. Then search the metadata for the numbers of these two bacteria, 129 and 13:

# The bacteria names in the growth data
cut -f2 awk_data/Growth_Data.txt | sort | uniq -c

# Names in the metadata that contain 129 or 13
cut -f1 mmc5_metadata_fixed.txt | grep '129'
cut -f1 mmc5_metadata_fixed.txt | grep '13'

This prints:

    210 129E
    175 13C
      1 Condition
    209 mock

R129_E
R129_N

R13_A
R13_C

You can see that bacteria 129E and 13C are the names used in the growth data, but in the metadata these bacteria are called R129_E and R13_C. These are the same strains, written in two ways. AWK compares names letter by letter, so 129E and R129_E do not match. Before you can combine the tables, the names must be the same.

Make the names match

This happens all the time: one person writes Sample-01, another Sample_01, and the sequencing center sends back S01. A good habit is to choose one way of naming your samples at the start of a project, for example short names without spaces or special characters, and to use it everywhere: in your lab notes, in file names and in every table. Fixing names afterwards is extra work, and it is easy to get wrong.

You already made the names match at the start of the page More useful AWK. There you renamed the two strains in the metadata, the smaller of the two tables, with one explicit change per name, and saved the result as mmc5_metadata_named.txt. The diff showed that exactly two lines changed, lines 11 and 14. That is a good habit to keep: whenever you use a command to change names, look at all names after the change, not only at the ones you wanted to change.

Tip: why not one general rule? (optional)

Another idea to change names is to change all names at once, for example: remove the R at the start, and the underscore. ^R means “an R at the start of the line”:

cut -f1 mmc5_metadata_fixed.txt | sed 's/^R//; s/_//' | grep '^1'
cut -f1 mmc5_metadata_fixed.txt | sed 's/^R//; s/_//' | head -4

This prints:

129E
129N
13A
13C

strain
oot123D2
oot381
USDA61

129E and 13C look right now, and a join would work. But every strain whose name starts with Root lost its R too: 17 strains are now called oot.... A general rule works for the names you had in mind, but it is easy to forget what it does to the other names in the file. You only notice this if you look at the other names as well.

Add the genus: `NR == FNR`

The most advanced part of this tutorial

Combining two files with AWK is harder than anything before it. If it does not make sense on the first read, that is normal: take your time, and run each step yourself. What matters most is that you recognize the NR == FNR pattern when you see it, for example in code from an LLM, and that you know the checks in “Check the result” below. These checks work no matter which command did the combining.

AWK can read two files, one after the other. The command below first reads the metadata and remembers the genus of each strain. Then it reads the growth data and adds the remembered genus to each line:

awk -F'\t' -v OFS='\t' '
  NR == FNR  { genus[$1] = $4; next }
             { print $0, genus[$2] }
' mmc5_metadata_named.txt awk_data/Growth_Data.txt > growth_genus.txt

# Check
wc -l growth_genus.txt
head -3 growth_genus.txt
awk -F'\t' '$2 == "129E"' growth_genus.txt | head -2
awk -F'\t' '$2 == "13C"' growth_genus.txt | head -2

This prints:

595 growth_genus.txt

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

low_P   129E    ex61    86.1    5.5 Rhizobium
low_P   129E    ex61    58.4    7.5 Rhizobium

low_P   13C ex61    54.3    1.56    Rhizobium
low_P   13C ex61    81  4   Rhizobium

595 lines, as many as in the growth data. The plants with 129E and 13C now have Rhizobium in a new column 6. The mock plants have an empty column 6, because no strain is called mock.

With two lines of AWK, all 595 plants now carry the genus of their bacterium. With a metadata table of 1,000 strains and a count table of 10,000 samples, the command stays exactly the same.

You will see NR == FNR in many AWK commands that combine two files, so it is worth taking it apart.

The command has two parts, each with its own pattern and action: NR == FNR { genus[$1] = $4; next } and { print $0, genus[$2] }. For each line, AWK checks the parts from top to bottom, and runs the action of every part whose pattern matches. A part without a pattern runs on every line. next stops this: AWK skips the remaining parts and goes on to the next line. Below, you look at each piece in turn. next is not a pattern like END or BEGIN. It does not decide when an action runs. It is a step inside an action, and it tells AWK to stop working on this line and move on to the next one.

NR and FNR. You know NR from AWK on a table: the line number. When AWK reads several files, NR keeps counting across all of them. FNR is the line number within the current file, and starts at 1 again in each new file. FILENAME holds the name of the current file. You can see the difference where AWK moves from the first file to the second (sed -n '27,30p' prints lines 27 to 30):

awk '{ print FILENAME, NR, FNR }' mmc5_metadata_named.txt awk_data/Growth_Data.txt | sed -n '27,30p'

This prints:

mmc5_metadata_named.txt 27 27
mmc5_metadata_named.txt 28 28
awk_data/Growth_Data.txt 29 1
awk_data/Growth_Data.txt 30 2

In the first file, NR and FNR are the same. From the second file on, they differ. So the pattern NR == FNR is true only while AWK reads the first file.

The first part, NR == FNR { genus[$1] = $4; next }, therefore runs only on the metadata. Here, genus is an array, a variable that holds one value per name, as on Calculate with AWK. Take line 11 of the metadata, now that you renamed the strain:

129E    Rhizobiales Rhizobiaceae    Rhizobium   Root    Arabidopsis FALSE   Rhizobium spp.  2   TY  TRUE    TRUE

Here $1 is 129E and $4 is Rhizobium. So genus[$1] = $4 means genus["129E"] = "Rhizobium": store the genus from column 4 under the name of the strain in column 1. You use the strain as the name, because the strain is what you want to look up later in the growth data. The genus is the value, because that is what you want back. Then next makes AWK go on to the next line, so the second part does not run on the metadata.

You can look at the values in the array after AWK has read the whole metadata table:

awk -F'\t' '
  { genus[$1] = $4 }
  END {
    print "129E:", genus["129E"]
    print "Root154:", genus["Root154"]
    print "mock:", genus["mock"]
  }
' mmc5_metadata_named.txt

This prints:

129E: Rhizobium
Root154:
mock:

Root154 has an empty value, because this strain has no genus in the table. mock is not in the array at all, and AWK gives you empty text for it, without an error. In the result, both look the same: an empty field.

The second part, { print $0, genus[$2] }, has no pattern, so it runs on every line that gets this far: only the lines of the growth data. Take this line:

low_P   129E    ex61    86.1    5.5

Now $2 is the strain, 129E, because in the growth data the strain is in column 2. So genus[$2] is genus["129E"], the value stored in the first part, and AWK prints:

low_P   129E    ex61    86.1    5.5 Rhizobium

Note that $1, $2 and so on always mean the columns of the line AWK is reading at that moment. In the metadata, the strain is $1 and the genus $4. In the growth data, the strain is $2. So you choose the column numbers for each file separately.

The order of the files matters. The table you look things up in comes first, the table you add to comes second. If you swap them, AWK remembers the growth data and adds a column to the metadata instead. The result has 28 lines, and the new column is empty on every line. AWK gives no error, but a quick wc -l shows that something is wrong: you expected 595 lines.

Give the new column a name

The result has the right number of lines, but it is not finished yet. Look at the end of the first two lines with cat -A:

head -2 growth_genus.txt | cat -A

This prints:

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

Both lines end with a tab and nothing after it: an empty column 6. For the mock plant, that is expected. For the header it is not: the new column has no name. The header went through the second part like any other line. Column 2 of the header is Condition, and Condition is not a name in the array.

To give the new column a name, add a new part between the two others. Its pattern, FNR == 1, matches line 1 of a file. The metadata never gets this far, because the first part skips every metadata line to the next line with next. So the new part only runs on the header of the growth data:

awk -F'\t' -v OFS='\t' '
  NR == FNR  { genus[$1] = $4; next }
  FNR == 1   { print $0, "Genus"; next }
             { print $0, genus[$2] }
' mmc5_metadata_named.txt awk_data/Growth_Data.txt > growth_genus.txt

# Check
wc -l growth_genus.txt
head -3 growth_genus.txt

This prints:

595 growth_genus.txt

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

The new part also ends with next. It makes sure that the header is not printed a second time by the last part, { print $0, genus[$2] }.

Forget next, and the first file ends up in the result

Without next in the first part, the other parts also run on the lines of the metadata. You get no error:

awk -F'\t' -v OFS='\t' '
  NR == FNR  { genus[$1] = $4 }
  FNR == 1   { print $0, "Genus"; next }
             { print $0, genus[$2] }
' mmc5_metadata_named.txt awk_data/Growth_Data.txt > growth_genus_nonext.txt

wc -l growth_genus_nonext.txt
head -3 growth_genus_nonext.txt | cut -f1,13

This prints:

623 growth_genus_nonext.txt

strain  Genus
Root123D2
Root381

You get 623 lines instead of 595: first the 28 lines of the metadata, each with an extra column, and then the 595 lines you wanted. (cut -f1,13 shows the strain and column 13, the new column after the 12 columns of the metadata.) The line count shows the problem right away.

Check the result

The line count tells you that no lines were lost or added. It does not tell you whether each line got the right genus. Two more checks help.

Check 1: lines without a genus. Count the empty fields in column 6, as on AWK on a table, and look at which strains they belong to:

awk -F'\t' '$6 == ""' growth_genus.txt | wc -l
awk -F'\t' '$6 == "" { print $2 }' growth_genus.txt | sort | uniq -c

This prints:

209
    209 mock

209 lines without a genus, all of them mock plants. That is expected: these plants got no bacteria. If other names show up here, a name did not match.

Check 2: strains that were not used. This time, read the growth data first and remember which strains it has. Then go through the metadata, and print the strains that were not seen in the growth data:

awk -F'\t' '
  NR == FNR                 { seen[$2] = 1; next }
  FNR > 1 && !($1 in seen)  { print $1 }
' awk_data/Growth_Data.txt mmc5_metadata_named.txt | wc -l

awk -F'\t' '
  NR == FNR                 { seen[$2] = 1; next }
  FNR > 1 && !($1 in seen)  { print $1 }
' awk_data/Growth_Data.txt mmc5_metadata_named.txt | head -3

This prints:

25

Root123D2
Root381
USDA61

Taken apart:

seen[$2] = 1: store the value 1 under the name of the strain in column 2 of the growth data. The value itself does not matter, any value would do. What matters is that after the first file, every strain of the growth data is a name in the array seen.
($1 in seen): true if the strain in column 1 of the metadata is a name in seen. in only looks at the names, not at the values.
! means “not”, so !($1 in seen) is true for the strains that were not seen. The brackets keep the test together, so that ! turns the whole test around.
FNR > 1 skips the header of the metadata.

25 of the 27 strains do not appear in the growth data. That is fine here, because the experiment used only two of them. In your own data, an entry you expected to be used may point to a name that is written differently in the two files. Without the !, the same command prints the strains that were used:

awk -F'\t' '
  NR == FNR                 { seen[$2] = 1; next }
  FNR > 1 && ($1 in seen)   { print $1 }
' awk_data/Growth_Data.txt mmc5_metadata_named.txt

This prints:

129E
13C

What the checks do not catch. Run the same join and checks with the general rule from the tip above (s/^R//; s/_//, which removes the R at the start and the underscore):

sed 's/^R//; s/_//' mmc5_metadata_fixed.txt > mmc5_metadata_wrong.txt

awk -F'\t' -v OFS='\t' '
  NR == FNR  { genus[$1] = $4; next }
  FNR == 1   { print $0, "Genus"; next }
             { print $0, genus[$2] }
' mmc5_metadata_wrong.txt awk_data/Growth_Data.txt > growth_genus_wrong.txt

# Check 1, and a comparison with the correct join
awk -F'\t' '$6 == "" { print $2 }' growth_genus_wrong.txt | sort | uniq -c
cmp growth_genus_wrong.txt growth_genus.txt && echo 'identical to the correct join'

# Check 2
awk -F'\t' '
  NR == FNR                 { seen[$2] = 1; next }
  FNR > 1 && !($1 in seen)  { print $1 }
' awk_data/Growth_Data.txt mmc5_metadata_wrong.txt | head -3

This prints:

    209 mock
identical to the correct join
oot123D2
oot381
USDA61

The join is exactly the same as with the correct names, because 129E and 13C still match. But the metadata is now broken: 17 strains lost their R. Check 1 cannot see this. Check 2 shows it, but only if you read the names it prints.

The checks give you numbers and names. Whether they show a problem, you decide: 209 mock plants without a genus is expected, a strain called oot123D2 is not.

Tip: joins in other tools (optional)

AWK is not the only way to combine two tables. In bash, there is also the join command, and in R you would use left_join() from the dplyr package, or merge(). An LLM may suggest any of these. They look different, but they do the same job: match the lines of two tables by a shared name.

So the checks on this page still apply. Make the names match first, then count the lines, look at the lines without a match and see which names they belong to, and look for entries that were not used. Tools differ in what they do with a name that has no match: some leave an empty field, as AWK does, and others leave out the line. Then the line count is the check that catches it.

Exercise

The mock plants got an empty field in the new column. But their genus is not unknown: they got no bacteria at all. Write a second command that puts the text none into column 6 of the mock plants, and leaves all other lines as they are. Use growth_genus.txt as input, save the result as growth_genus_none.txt, and check it.

Hint: on the sed page, you changed column 2 on some lines only, with a pattern, an action and { print }.

Before you check, predict: how many lines should get none, and how many empty fields should be left in column 6? Which checks would show whether you are right?

Click me to see an answer

The pattern $2 == "mock" picks the mock plants, and the action puts none into column 6. The second part, { print }, prints every line, changed or not. -v OFS='\t' keeps the tabs in the lines that changed:

awk -F'\t' -v OFS='\t' '$2 == "mock" { $6 = "none" } { print }' growth_genus.txt > growth_genus_none.txt

# Check
wc -l growth_genus_none.txt
head -3 growth_genus_none.txt
awk -F'\t' '$6 == ""' growth_genus_none.txt | wc -l
cut -f2,6 growth_genus_none.txt | sort | uniq -c

This prints:

595 growth_genus_none.txt

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

0

    210 129E    Rhizobium
    175 13C Rhizobium
      1 Condition   Genus
    209 mock    none

You can predict both numbers from check 1: the 209 lines without a genus were all mock plants. So 209 lines should get none, and 0 empty fields should be left. Counting the empty fields and counting the values per condition test exactly these two numbers.

No empty fields are left, and each condition got the value you expected. Note that the command only changes the mock plants. A strain whose name does not match still gets an empty field, so check 1 still finds it. If you filled in every empty field instead, a name that did not match would look the same as a mock plant.

Windows line endings in a join

On the sed page, you removed the hidden \r from the metadata before you used it. Here is what happens if you skip that step. The \r sits at the end of each line, so it is part of the last column, column 12 (Colonization). Add this column to the growth data, once from the clean file and once from a renamed copy of the original Windows file:

# The Windows file, with the two names changed, but without removing the \r
sed 's/^R129_E/129E/; s/^R13_C/13C/' awk_data/mmc5_metadata.txt > mmc5_metadata_named_windows.txt

# Add column 12, from the clean file and from the Windows file
awk -F'\t' -v OFS='\t' '
  NR == FNR  { colon[$1] = $12; next }
  FNR == 1   { print $0, "Colonization"; next }
             { print $0, colon[$2] }
' mmc5_metadata_named.txt awk_data/Growth_Data.txt > growth_colon.txt

awk -F'\t' -v OFS='\t' '
  NR == FNR  { colon[$1] = $12; next }
  FNR == 1   { print $0, "Colonization"; next }
             { print $0, colon[$2] }
' mmc5_metadata_named_windows.txt awk_data/Growth_Data.txt > growth_colon_windows.txt

# Count the plants with TRUE in the new column, and look at one line
awk -F'\t' '$6 == "TRUE"' growth_colon.txt | wc -l
awk -F'\t' '$6 == "TRUE"' growth_colon_windows.txt | wc -l
awk -F'\t' '$2 == "129E"' growth_colon_windows.txt | head -1 | cat -A

This prints:

210
0
low_P^I129E^Iex61^I86.1^I5.5^ITRUE^M$

The \r (^M) came along with the value, so the next command that looks for TRUE finds nothing. If you add column 4 (the genus) from the Windows file instead, the result is exactly the same as from the clean file, because the \r is not in column 4. So a file with Windows line endings can work fine in one join and quietly break the next one. Remove the \r before you combine files, as on the sed page.

Exercise

Add two columns from the metadata to the growth data in one command: the genus (column 4) and Colonization (column 12). Give both new columns a name in the header. Save the result as growth_meta.txt.
Before you check, predict: how many lines and how many fields per line do you expect? Then check the result: the number of lines, the number of fields per line, and which values of Colonization each condition got.

Click me to see an answer

Use one array per column you want to add:

# 1. Two arrays, and two new columns
awk -F'\t' -v OFS='\t' '
  NR == FNR  { genus[$1] = $4; colon[$1] = $12; next }
  FNR == 1   { print $0, "Genus", "Colonization"; next }
             { print $0, genus[$2], colon[$2] }
' mmc5_metadata_named.txt awk_data/Growth_Data.txt > growth_meta.txt

# 2. Check
wc -l growth_meta.txt
head -3 growth_meta.txt
awk -F'\t' '{ print NF }' growth_meta.txt | sort | uniq -c
cut -f2,7 growth_meta.txt | sort | uniq -c

This prints:

595 growth_meta.txt

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

    595 7

    210 129E    TRUE
    175 13C NA
      1 Condition   Colonization
    209 mock

You can predict both numbers: a join adds columns, not lines, so you expect the 595 lines of the growth data, and 5 + 2 = 7 fields per line. All 595 lines have 7 fields. 129E got TRUE, 13C got the text NA, and the mock plants got nothing. Note that the table now holds two kinds of missing values: the text NA from the metadata, and empty fields for the mock plants. When you calculate with such a column, you need the rule from AWK on a table for both.

What you learned

A join adds information from one table to another, using a name that both tables share.
Before a join, look at the names in both files. They must match letter by letter.
Make names match with explicit changes (s/^R129_E/129E/), and check all names afterwards, not only the ones you changed. Choose one naming scheme early in a project, so you need this less often.
NR == FNR { genus[$1] = $4; next } { print $0, genus[$2] } combines two files: the table you look things up in first, the table you add to second. FNR == 1 gives the new column a name.
NR counts lines across all files, FNR within the current file. next skips the rest of the command for the current line.
A missing name gives an empty field, not an error. After a join, count the lines, count the empty fields and see which names they belong to, and look for entries that were not used. Read the names these checks print, not only the counts.
A Windows line ending travels into your result when you add the last column. Remove it before the join.

The next two pages are optional: AWK and sed on FASTA files uses AWK and sed on sequence files, and AWK on FASTQ files uses AWK on sequencing reads. After them, Checking code from an LLM puts the checks from all pages together.

Look at both files first

Why -F'\t' matters here too

The names in both files

Make the names match

Add the genus: NR == FNR

Give the new column a name

Check the result

Windows line endings in a join

What you learned

Why `-F'\t'` matters here too

Add the genus: `NR == FNR`