How do I use awk to properly extract lines from a file using numbers from another file?

Question

I'm using bash shell. If I have a file of line numbers

1
4
7
9

and another file of lines where the first element is a number followed by a string of text

1,Michael Jordan
2,Karl Malone,
3,Charles Barkley
4,Greg Anthony
5,Chris Mullen
6,Reggie Miller
7,Billy Owens
8,David Robinson
9,Shaquille O'Neal
10,John Stockton

How do I write an awk command to extract lines from the second file only if the first number belongs to the first file? In the above example, I would expect this outcome

1,Michael Jordan
4,Greg Anthony
7,Billy Owens
9,Shaquille O'Neal

I tried this

awk 'FNR==NR{a[$1];next} $1 in a' /tmp/first_file /tmp/second_file > /tmp/third_file

but it produces nothing in the third file.

Answer 1

Since your second file is comma-separated , you need to set the awk field separator to , - either globally using the -F command line option:

awk -F, 'FNR==NR{a[$1];next} $1 in a' /tmp/first_file /tmp/second_file > /tmp/third_file

or via the FS built in variable:

awk 'FNR==NR{a[$1];next} $1 in a' /tmp/first_file FS=, /tmp/second_file > /tmp/third_file

The latter method would allow you to deal with the case where the first file is not comma-delimited (and had more than a single field).

Answer 2

Using Raku (formerly known as Perl_6)

~$ raku -ne 'BEGIN my @a = "nbr_list.txt".IO.lines.map: *.Int;  \
             .put if $_.split(",")[0] == any(@a);'  file.csv

#OR

~$ raku -ne 'BEGIN my @a = "nbr_list.txt".IO.lines.map: *.Int;  \
             .put if $_.split(",")[0] ~~ any(@a);'  file.csv

Raku is a programming language in the Perl-family. As you BEGIN, take the list of numbers and store in an array, @a. The -ne flags then reads files off the command line without auto-printing (awk-like behavior).

Here, the line is read into topic variable ($_), which is split on commas, and the first element ([0]) is taken. These elements are compared using the == numerical equality operator (first code example), or Raku's ~~ smart-matching operator. On the RHS of the operator, the @a array is turned into an any() junction. The if conditional outputs the line if the condition is satisfied.

Sample Input:

1,Michael Jordan
2,Karl Malone,
3,Charles Barkley
4,Greg Anthony
5,Chris Mullen
6,Reggie Miller
7,Billy Owens
8,David Robinson
9,Shaquille O'Neal
10,John Stockton

Sample Output (using a nbr_list.txt file consisting of 1,4,7,9):

1,Michael Jordan
4,Greg Anthony
7,Billy Owens
9,Shaquille O'Neal

Junctions are interesting because they auto-thread. For the problem stated above, a one() junction also works, and might even be more efficient.

---

An alternative approach uses Sets: the line-numbers are turned into a Set of Ints. Data is read in linewise, each first column is coerced to an Int, and checked to see whether it is an (elem) element of the set. Note, either infix (elem) or infix ∈, (the Unicode symbol) can be used in the code below:

~$ raku -ne 'BEGIN my $set1 = Set.new("nbr_list.txt".IO.lines.map: *.Int);  \
             .put if $_.split(",").[0].Int (elem) $set1;'  file

Since Raku Sets can only contain unique values (i.e. they "unique-ify" input), the second example will discard duplicates in the "nbr_list.txt" file. Which indeed may be what the OP desires.

https://docs.raku.org/type/Junction
https://docs-stage.raku.org/type/Junction
https://raku.org

Answer 3

Such operation is called "join". There is a tool in coreutils called join for joining text files.

join -t, -j1 --nocheck-order first_file second_file
1,Michael Jordan
4,Greg Anthony
7,Billy Owens
9,Shaquille O'Neal

Explanation:

1. -t, - use comma as the field separator
2. -j1 - join on the first field
3. --nocheck-order - join requires the files to be sorted, but it does not like the numerical sorting. Thereby we prevent it to check for lexicographical sorting and complain about "9" prepending "10". It still works as long as the first columns in both files are sorted using the same algorithm.