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

You must log in to answer this question.

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