Find unique pairs in table

user_11072883 0
Viewed 92

I have the following table:

hitA    queryA
hitB    queryA
hitC    queryB
hitC    queryC
hitD    queryD

and I would like to filter out rows that have a duplicate in either column 1 or 2. My expected output is:

hitD    queryD

because hitD only occurs once in column 1 and queryD only occurs once in column 2.

I tried the following:

sort -k 2,2 -u
sort -k 1,1 -u

but this only gives me unique values in either of the columns.

awk bash
3 Answers

You may use this awk:

awk 'FNR==NR{++fq1[$1]; ++fq2[$2]; next} fq1[$1]==1 && fq2[$2]==1' file file

hitD    queryD
user_5482250

Could you please try following in awk. Reading Input_file one time.

awk '
{
  first[$1]++
  second[$2]++
  line[FNR]=$0
  firstInd[FNR]=$1
  secondInd[FNR]=$2
}
END{
  for(i=1;i<=FNR;i++){
    if(first[firstInd[i]]==1 && second[secondInd[i]]==1){
      print line[i]
    }
  }
}' Input_file

Explanation: Adding detailed explanation for above.

awk '                     ##Starting awk program from here.
{
  first[$1]++             ##Creating array first with index of $1 and keep increasing its value with 1.
  second[$2]++            ##Creating array second with index of $2 and keep increasing its value with 1.
  line[FNR]=$0            ##Creating array line with index of current line number and its value is current line value.
  firstInd[FNR]=$1        ##Creating firstInd array with index of FNR and value of 1st field.
  secondInd[FNR]=$2       ##Creating secondInd array with index of FNR and value of 2nd field.
}
END{                      ##Starting END block of this awk program from here.
  for(i=1;i<=FNR;i++){    ##Running loop from value 1 to till value of FNR here.
    if(first[firstInd[i]]==1 && second[secondInd[i]]==1){   ##checking condition if first and second array have value 1(with their respective indexes) then do following.
      print line[i]       ##Printing line with index of i here.
    }
  }
}' file1                  ##Mentioning Input_file name here.
user_58665800

You can use Python:

def findOccurance(rows):
    column0 = column1 =[]
    [ column0.append(item[0]) for item in rows ]
    [ column1.append(item[1]) for item in rows ]
    result = []
    for item in rows:
        if column0.count(item[0]) == 1 and column1.count(item[1]) == 1:
            result.append(item)
    return result

rows = [('hitA','queryA'), ('hitB','queryA'), ('hitC','queryB'), ('hitC','queryC'), ('hitD','queryD')]
print (findOccurance(rows))
user_131183270
Related