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? Feb 8, 2021 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 Feb 8, 2021 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, 2021 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, 2021 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, 2021 at 16:08

1 Answer 1

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. Feb 8, 2021 at 20:38
  • Thank you. Yes, it's not necessary that the columns are unique.
    – lepomis8
    Feb 9, 2021 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.