2

I have a relatively small space delimited file which looks like this (but with many columns and rows):

file1:

Entry1
a
b
c
d

And another huge tab delimited file with many duplicated entries per row like this:

file2:

value       ID1   ID2   
1      a    aaaa1
1      a    aaaa2
1      b    bbbb1
1      b    bbbb2
1      b    bbbb3
1      d    aaaa4

I am trying to first print a tab delimited file of the same size as file1 specifying matches between each entry in file1 and ID1 field (column 2) in file2 like this this is a separate question:

Entry1
a
b
NoMatch
d

And also print a merged tab delimited file with the values of file1 and file2, this time with repeated entries if there are in file2, but keeping the NoMatch as well, like this:

value       ID1   ID2   
1      a    aaaa1
1      a    aaaa2
1      b    bbbb1
1      b    bbbb2
1      b    bbbb3
NoMatch NoMatch NoMatch
1      d    aaaa4

I have tried using the join command to give me a NoMatch message:

join -a1 -e "NoMatch" <(awk -F ' ' '{print $0}' file1.txt |tail -n +2|sort -k1,1) <(cat file2 | sort -k1,1) > out.txt

but I must be doing something wrong because this prints all of file1 for example, but if there is no match in file2 the rest of the fields are blank (so no message "NoMatch" appears), can you please help me understand what I am doing wrong?

Thanks so much!

#

Thank you gilles, I can't seem to answer your comment as I just registered... What you suggested:

join -a1 -e "NoMatch" -11 -22 --header -o2.1,2.2,2.3 file1 file2

solved the second query Output2, thank you! Can I ask how I can get the first Output1, with the same lines as the file1? Thanks again!!

With don_crissti's help, I can get the second output like this:

paste -d'\t' file2 <(awk 'FNR==NR{seen[$1]++; next}  {(FNR==1 || ($1 in seen)) || $1="NoMatch"};1'  file2  file1)
  • Your inputs seem to be sorted on the join fields apart from the header row: have you tried something like join -a1 -e "NoMatch" -11 -22 --header -o2.1,2.2,2.3 file1 file2? You can pipe the result through column -t to prettify the output. – steeldriver Dec 3 '15 at 22:30
  • I think doing this with a pure awk script and a single pass would be best. I don't have time to look at it very thoroughly right now, but if you use NR == FNR as the pattern to catch the first file, throw all of what you need from it into an array, and then pass through file2 comparing each line to the array as you go through and outputting what you need to file2-prime, then at the end output file1-prime...it's very doable, I just don't understand what exactly you're comparing. – Wildcard Dec 4 '15 at 3:00
  • @jamespower or user145913: On this page you asked "help me understand what I was doing wrong?" I analyzed your code and showed exactly what was wrong with it. Why did you post a completely different answer from another question? What do you think about my answer to this question? Was it a waste of my time and effort? – RobertL Dec 4 '15 at 9:37
2

Reformat the original example for readability:

join -a1 -e "NoMatch" \
 <(awk -F ' ' '{print $0}' file1.txt |tail -n +2|sort -k1,1) \
 <(cat file2 | sort -k1,1)

All of the problems are with file2 processing and with the join options.

  1. file2 is sorted on field 1, should be field 2.
  2. No -t '<tab>' option for join, file2 is tab delimited.
  3. Missing join option to join on field 2 of file2.
  4. Even with the correct join field for file2, the default output prints the join field first, so you have to specify the -o FORMAT option on join.
  5. file2 header is not removed.
  6. Not causing a problem, but unneeded processing: tail is unneeded on file1 because awk can skip the first line.
  7. Unneeded -F ' ' option to awk.

Correcting those issues, yields this:

#!/bin/bash
head -1 file2
join -t '   ' -2 2 -a 1 -e NoMatch -o 2.1,2.2,2.3 \
 <(awk 'NR==1{next} {print $0}' file1.txt | sort) \
 <(tail -n +2 file2 | sort -k2)

Which yields the following tab-delimited output:

value   ID1     ID2
1       a       aaaa1
1       a       aaaa2
1       b       bbbb1
1       b       bbbb2
1       b       bbbb3
NoMatch NoMatch NoMatch
1       d       aaaa4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.