1

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.

4
  • What is your algorithm for mapping {west:popular, south:someWhatPopular, north:boring}? Nov 28, 2017 at 1:22
  • That is part of what I need assitance with, being able to find a way to map it that makes sense. I was thinking of looping through both files and finding contents in column 3 of File A and map them to file B, any examples or thoughts?
    – oddRas
    Nov 28, 2017 at 2:08
  • One sensible mapping is {north:boring, south:popular, west:someWhatPopular} since both the keys and the values are lexically sorted within their sets. Nov 28, 2017 at 2:20
  • Right on. Would you have some code snippet that I could build off of? Not too familiar with bash yet
    – oddRas
    Nov 28, 2017 at 2:25

3 Answers 3

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.

4
  • This is really great! I just need a brief explanation of what you are doing with the join -1 4 -2 2 I understand the rest of the code, and then I will mark as correct answer! :)
    – oddRas
    Nov 28, 2017 at 7:16
  • Nice. The unique key for fileA doesn't contribute anything though Nov 28, 2017 at 8:52
  • Not in this case but if you stick to good practice in keeping a uid in all tables then you will find it invaluable when you graduate to handling bigger and more complex data sets.
    – bu5hman
    Nov 28, 2017 at 10:22
  • @glennjackman Just realised i was talking rubbish. I explicitly put the fileA uid in so the results could be sorted back to the original order. It is the uid in the popularity file which are redundant in this instance. I must be getting old.
    – bu5hman
    Nov 28, 2017 at 10:46
0

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .