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 join
ing 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.