Matching Data Across Files
Data is king. Corporations, businesses, and households alike run on it. But data stored in different files and collated by different people is a pain. In addition to knowing which files to open to find the information you want, the layout and format of the files are likely to be different.
You also have to deal with the administrative headache of which files need to be updated, which need to be backed up, which are legacy, and which can be archived.
Plus, if you need to consolidate your data or conduct some analysis across an entire data set, you’ve got an additional problem. How do you rationalize the data across the different files before you can do what you need to do with it? How do you approach the data preparation phase?
The good news is if the files share at least one common data element, the Linux join command can pull you out of the mire.
The Data Files
All the data we’ll use to demonstrate the use of the join command is fictional, starting with the following two files:
The following is the contents of file-1.txt:
We have a set of numbered lines, and each line contains all the following information:
A number A first name A surname An email address The person’s sex An IP Address
The following is the contents of file-2.txt:
Each line in file-2.txt contains the following information:
A number A surname An email address The person’s sex A region of New York A dollar value
The join command works with “fields,” which, in this context, means a section of text surrounded by whitespace, the start of a line, or the end of a line. For join to match up lines between the two files, each line must contain a common field.
Therefore, we can only match a field if it appears in both files. The IP address only appears in one file, so that’s no good. The first name only appears in one file, so we can’t use that either. The surname is in both files, but it would be a poor choice, as different people have the same surname.
You can’t tie the data together with the male and female entries, either, because they’re too vague. The regions of New York and the dollar values only appear in one file, too.
However, we can use the email address because it’s present in both files, and each is unique to an individual. A quick look through the files also confirms the lines in each correspond to the same person, so we can use the line numbers as our field to match (we’ll use a different field later).
Note there are a different number of fields in the two files, which is fine—we can tell join which field to use from each file.
However, watch out for fields like the regions of New York; in a space-separated file, each word in the name of a region looks like a field. Because some regions have two- or three-word names, you’ve actually got a different number of fields within the same file. This is okay, as long as you match on fields that appear in the line before the New York regions.
The join Command
First, the field you’re going to match must be sorted. We’ve got ascending numbers in both files, so we meet that criteria. By default, join uses the first field in a file, which is what we want. Another sensible default is that join expects the field separators to be whitespace. Again, we’ve got that, so we can go ahead and fire up join.
As we’re using all the defaults, our command is simple:
join considers the files to be “file one” and “file two” according to the order in which they’re listed on the command line.
The output is as follows:
The output is formatted in the following way: The field the lines were matched on is printed first, followed by the other fields from file one, and then the fields from file two without the match field.
Unsorted Fields
Let’s try something we know won’t work. We’ll put the lines in one file out of order so join won’t be able to process the file correctly. The contents of file-3.txt are the same as file-2.txt, but line eight is between lines five and six.
The following is the contents of file-3.txt:
We type the following command to try to join file-3.txtto file-1.txt:
join reports that the seventh line in file-3.txt is out of order, so it’s not processed. Line seven is the one that begins with the number six, which should come before eight in a correctly sorted list. The sixth line in the file (which begins with “8 Odell”) was the last one processed, so we see the output for it.
You can use the –check-order option if you want to see whether join is happy with the sort order of a files—no merging will be attempted.
To do so, we type the following:
join tells you in advance there’s going to be a problem with line seven of file file-3.txt.
Files with Missing Lines
In file-4.txt, the last line has been removed, so there isn’t a line eight. The contents are as follows:
We type the following and, surprisingly, join doesn’t complain and processes all the lines it can:
The output lists seven merged lines.
The -a (print unpairable) option tells join to also print the lines that couldn’t be matched.
Here, we type the following command to tell join to print the lines from file one that can’t be matched to lines in file two:
Seven lines are matched, and line eight from file one is printed, unmatched. There isn’t any merged information because file-4.txt didn’t contain a line eight to which it could be matched. However, at least it still appears in the output so you know it doesn’t have a match in file-4.txt.
We type the following -v (suppress joined lines) command to reveal any lines that don’t have a match:
We see that line eight is the only one that doesn’t have a match in file two.
Matching Other Fields
Let’s match two new files on a field that isn’t the default (field one). The following is the contents of file-7.txt:
And the following is the contents of file-8.txt:
The only sensible field to use for joining is the email address, which is field one in the first file and field two in the second. To accommodate this, we can use the -1 (file one field) and -2 (file two field) options. We’ll follow these with a number that indicates which field in each file should be used for joining.
We type the following to tell join to use the first field in file one and the second in file two:
The files are joined on the email address, which is displayed as the first field of each line in the output.
Using Different Field Separators
What if you have files with fields that are separated by something other than whitespace?
The following two files are comma-delimited—the only whitespace is between the multiple-word place names:
We can use the -t (separator character) to tell join which character to use as the field separator. In this case, it’s the comma, so we type the following command:
All the lines are matched, and the spaces are preserved in the place names.
Ignoring Letter Case
Another file, file-9.txt, is almost identical to file-8.txt. The only difference is some of the email addresses have a capital letter, as shown below:
When we joined file-7.txt and file-8.txt, it worked perfectly. Let’s see what happens with file-7.txt and file-9.txt.
We type the following command:
We only matched six lines. The differences in upper- and lowercase letters prevented the other two email addresses from being joined.
However, we can use the -i (ignore case) option to force join to ignore those differences and match fields that contain the same text, regardless of case.
We type the following command:
All eight lines are matched and joined successfully.
Mix and Match
In join, you have a powerful ally when you’re wrestling with awkward data preparation. Perhaps you need to analyze the data, or maybe you’re trying to massage it into shape to perform an import to a different system.
No matter what the situation is, you’ll be glad you have join in your corner!
