mjhjmtu

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.

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.

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

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