AWK command to get distinct values in a column based on the values in another column that are the same

I want to find rows that have the same values in col1 and col2, and get their distinct third column

Say I have this data

City,Sunset,Anna
City,Sunset,Anna
City,Sunset,Ben
City,Sunset,Ben

My desired output is:

City,Sunset,"Anna,Ben"

So far, I have this code

cat file.txt | sed 's/,/|/2' | awk -F"," '{if (!($1 in a)) a[$1] = $2; else a[$1]=a[$1] "," $2 } END { for (key in a) print key, a[key] }' OFS=,

But Im only getting

City,Sunset,Anna,Anna,Ben,Ben

When the output should be

City,Sunset,"Anna,Ben"

Could someone help me with this one?

With GNU awk (for multi-dimensional arrays, output prefixed with -| ):

awk -F, '{ arr[$1 "," $2][$3] } END { for(i in arr) {
  s=""; for(j in arr[i]) s = s (s?",":"") j; print i ",\"" s "\""}}' Input_file
-| City,Sunset,"Ben,Anna"

• Declare the comma as input field separator (-F,).
• For each line add an entry in array arr with keys $1,$2 and $3.
• At the END loop over the 2 indexes, concatenate the second (j) index in string s, separated with commas, print first index (i), a comma and string s between double quotes.

With any POSIX-compliant awk the simplest is probably to sort the input file first, such that all lines with same first and second field are consecutive:

sort Input_file | awk -F, '
  function foo() {s=""; for(i in arr) s=s (s?",":"") i
    print key ",\"" s "\""; delete arr}
  {key=$1 "," $2} NR>1 && key!=prv {foo()} {prv=key; arr[$3]} END {foo()}'

We use the same principle but with a one-dimension array, and print when the two first fields change (key!=prv) or at the END, instead of printing everything at the END. Note the use of a function (foo) to factorize the printing code.

awk -F, -v OFS=, '
    !a[$0]++{
        b[$1","$2]=(b[$1","$2] ? b[$1","$2]","$3 : $3)
    }
    END{
        for(i in b) print  i,"\""b[i]"\""
    }
' file

1st solution: With your shown samples only please try following code. I am using uniq + awk combination here.

uniq Input_file | 
awk '
BEGIN{
  FS=OFS=","
  s1="\""
}
{
  arr[$1 FS $2]=(arr[$1 FS $2]?arr[$1 FS $2] OFS:"") $NF
}
END{
  for(i in arr){
    print i,s1 arr[i] s1
  }
}
'

2nd solution: Only awk solution. Where removing the Duplicates from 1 awk and passing it to main awk then as input.

awk '
BEGIN{
  FS=OFS=","
  s1="\""
}
{
  arr[$1 FS $2]=(arr[$1 FS $2]?arr[$1 FS $2] OFS:"") $NF
}
END{
  for(i in arr){
    print i,s1 arr[i] s1
  }
}
' <(awk '!arr[$0]++' Input_file)

Using any awk:

$ cat tst.awk
BEGIN { FS=OFS="," }
{
    key = $1 FS $2
    val = $3
}
key != prev {
    if ( NR > 1 ) {
        print prev, "\"" vals "\""
    }
    vals = sep = ""
    prev = key
    delete seen
}
!seen[val]++ {
    vals = vals sep val
    sep = OFS
}
END {
    print prev, "\"" vals "\""
}

<p>

$ awk -f tst.awk file
City,Sunset,"Anna,Ben"

That will work if you have multiple different values in the first 2 input fields, and regardless of the order of the 3rd field values.

It will output values in the order they appear in the input and only stores the values for one key pair at a time rather than storing the whole file.

For example, given this input:

$ cat file
City,Sunset,Anna
City,Sunset,Ben
City,Sunset,Sue
City,Sunset,Ben
Town,Sunrise,Ben
Town,Sunrise,Phil

it will produce what I assume is the expected output:

$ awk -f tst.awk file
City,Sunset,"Anna,Ben,Sue"
Town,Sunrise,"Ben,Phil"

The above script assumes your input is grouped by the values of the first 2 fields, if it isn't then run sort -t, -k1,2 on it first.

Given this example:

cat file
City,Sunset,Anna
City,Sunrise,Bob
City,Midday,Ellen
City,Sunset,Anna
City,Sunset,Ben
City,Sunset,Ben
City,Sunrise,Ben

Here is a Ruby to do that:

ruby -F, -lane 'BEGIN{h=Hash.new { |hash, key| hash[key] = Set.new() }}
h[$F[0..1].join(",")]<<$F[2]
END{puts h.map{|k,v| "#{k},\"#{v.join(",")}\""}}' file

Prints:

City,Sunset,"Anna,Ben"
City,Sunrise,"Bob,Ben"
City,Midday,"Ellen"