0

I have two files that have some similar and some unique columns. I'd like to compare the first 3 columns that are similar and print if they match, along with each of their unique columns.

File1:

A cat pizza 34 24 56 
A dog tomato 304 959 030
B bird grape 4.4 939 33
C snake broccoli 89 28 273 

File2:

A cat pizza 55 85 328
B dog tomato 404 56 32
C snake sandwich 384 34 75

Which should make

File3:

A cat pizza 34 24 56 55 85 328

I've been trying

awk  'NR==FNR       {c[$1$2$3]++;next}
      c[$1$2$3] > 0 {print $0}'

but I'm not getting any matches.

6
  • Actually I do get matches. If I create file1 and file2 as above, and run your unchanged command with the two files as parameters, I do get A B C G H I. However, what output do you want to generate when there is more than one match, e.g. more than one line starts with A B C? – berndbausch Feb 8 at 4:15
  • 2
    I'd recommend you use c[$1,$2,$3] (with commas) -- that puts a separator between the strings so you won't get false positives, such as A A AA in file 1 and AA A A in file 2 – glenn jackman Feb 8 at 4:30
  • 1
    Are your files single line? If no, you should post a realistic sample. Do the files have equal line numbers? – guest_7 Feb 8 at 6:46
  • 1
    Your description is a bit unclear: So you want all columns but no double entries? Are columns really always the same or is it just about the headers? Please add better example files. – FelixJN Feb 8 at 8:31
  • There are so many ways to interpret this.... what if A B C doesn't appear in file2? What if it appears multiple times in either or both files? When you say I'd like to compare the first 3 columns that are similar do you mean you always want to compare only the first 3 columns or you literally want to compare the first 3 columns that are similar out of N that might be similar, and, by similar do you actually mean identical or something else, and does order matter such that A B C in file1 matches A C B in file2, etc.? Please edit your question to clarify your requirements. – Ed Morton Feb 8 at 16:08
0

I'd do something like

function join(start, end, sep,       str, i) {
    if (!sep) sep = OFS
    str = $start
    for (i = start + 1; i <= end; i++) str = str sep $i
    return str
}
 
          { key = $1 OFS $2 OFS $3 }
NR == FNR { f1[key] = join(4, NF); next }
key in f1 { print key, f1[key], join(4, NF) }

Run with

awk -f script.awk file1 file2
2
  • Note that, given "along with each of their unique columns", this does not ensure the subsequent columns are unique. It takes the common 3 columns, and all the rest of them appended, even if they are not strictly unique. I assumed this is what you wanted. – glenn jackman Feb 8 at 20:38
  • Thank you. Yes, it's not necessary that the columns are unique. – lepomis8 Feb 9 at 16:28

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.