sort File B based on column 3 of File A without changing contents of File A

Question

I have the following file:

cat fileA.txt

seattle    1991  west
atlanta    1993  west
turlock    1998  west
marysville 2004  south
newyork    2007  north
canada     2004  west

And the second file looks like this:

cat fileB.txt

popular
someWhatPopular
boring
popular
popular
popular

I would like to get the following output on fileB.txt:

popular popular popular someWhatPopular boring popular

So essentially I'm trying to sort fileB.txt to fileA.txt third column

I tried the following code:

   #!/bin/bash
   sort -s -k3,3 fileA.txt fileB.txt

But it didn't work. Any suggestions? I'm pretty open to anything that doesn't require hardcoding. Bash/awk/sed, etc.

Answer 1

This is a data structure issue more than linux one. You need a common entry ( key) in both tables to link them, the same as in any 'database' and it is good practice to keep a unique key in the first column of any data table. Then you can sort and link to your hearts content.

Taking something like @glennjackman mapping , you define the mapping key as being north, south etc

1 south somewhatPopular
2 west popular
3 north boring
4 east unexplored

in a file called file popularity. Amend fileA to include a unique key

1 seattle    1991  west
2 atlanta    1993  west
3 turlock    1998  west
4 marysville 2004  south
5 newyork    2007  north
6 canada     2004  west

then you can manipulate these files by joining them on your selected key (in your case column 2 in popularity maps to column 4 in fileA) but join needs both files to be sorted on the keyfield, so

join -1 4 -2 2 <(sort -k4 fileA) <(sort -k2 popularity) | sort -k2 | awk '{print $6}'

popular
popular
popular
somewhatPopular
boring
popular

A bit of a sledgehammer approach but it gives you most flexibility.

Break the above command at each pipe and you will see what each step does.

Edit: Explanation of join -1 4 -2 2 # its in the man pages

This tells join to look at the 4th column in table 1 (-1 4) and find matching values in the 2nd column of table 2 (-2 2).

join then composes columns from the two tables into single table but only includes the key column (north etc) once. Look at the output from

join -1 4 -2 2 <(sort -k4 fileA) <(sort -k2 popularity)

and it should be clearer

Because we had to sort the data tables for the join to work, we then

| sort -k2

the combined table to put them back in their original order.

The column you want is column 6 in the combined table so we just

| awk '{print $6}'

to stdout.

Answer 2

You might try to paste the two "table" files together, pipe the output to sort, then cut to retain only the fourth column.

Untested (cell phone right now) attempt would be something like

paste fileA fileB | sort -s -k3,3 | cut -f4

Answer 3

You can get the alphabetical mapping with

paste <(awk '{print $NF}' fileA.txt | sort -u) <(sort -u fileB.txt)

north   boring
south   popular
west    someWhatPopular

And then a form of your desired output can be produced with awk:

awk '
    NR==FNR {map[$1] = $2; next} 
    {print map[$NF]}
' <(paste <(awk '{print $NF}' fileA.txt | sort -u) <(sort -u fileB.txt)) fileA.txt

someWhatPopular
someWhatPopular
someWhatPopular
popular
boring
someWhatPopular