0

I have a data looks like this, for each SNP, it should repeat 5 times with different beta. But for SNP rs11704961, it only repeat twice, so I want to delete SNP rows that repeat less than 5 times. I tried to use sort -k 1 | uniq -c, but it considers the whole line for checking duplicates, not the first column.

SNP R K BETA 
rs767249 1 1 0.1065 
 rs767249 1 2 -0.007243 
 rs767249 1 3 0.02771 
 rs767249 1 4 -0.008233 
 rs767249 1 5 0.05073 
 rs11704961 2 1 0.2245 
 rs11704961 2 2 0.009203 
 rs1041894 3 1 0.1238 
 rs1041894 3 2 0.002522 
 rs1041894 3 3 0.01175
 rs1041894 3 4 -0.01122 
 rs1041894 3 5 -0.009195
Jeff Schaller
  • 67,283
  • 35
  • 116
  • 255
  • 2
    There are a lot of similar questions on this site; I expect there is an exact duplicate somewhere but in the meantime this and this may help get you started. – Wildcard Nov 11 '16 at 23:53

2 Answers2

0

Using Miller which is awk-like but handles the header lines intrinsically:

$ cat snp.mlr
@records[$SNP][NR] = $*; # retain records
@counts[$SNP] += 1;

end { # conditionally emit
  for (snp in @records) {
    if (@counts[snp] == 5) {
      emit @records[snp];
    }
  }
}

$ mlr --csvlite --fs space put -q -f snp.mlr snp.dat
SNP R K BETA
rs767249 1 1 0.1065
rs767249 1 2 -0.007243
rs767249 1 3 0.02771
rs767249 1 4 -0.008233
rs767249 1 5 0.05073
rs1041894 3 1 0.1238
rs1041894 3 2 0.002522
rs1041894 3 3 0.01175
rs1041894 3 4 -0.01122
rs1041894 3 5 -0.009195

Note this uses features new since the latest 4.5.0 release.

John Kerl
  • 186
  • 5
0

Using awk:

$ cat snp.awk
NR == 1 { # header line
  print $0
}
NR > 1 { # data line
  snp = $1;
  retain[snp][NR] = $0;
  counts[snp]++;
}
END {
  for (snp in retain) {
    if (counts[snp] == 5) {
      for (i in retain[snp]) {
        print retain[snp][i];
      }
    }
  }
}

$ awk -f snp.awk snp.dat
SNP R K BETA
rs1041894 3 1 0.1238
rs1041894 3 2 0.002522
rs1041894 3 3 0.01175
rs1041894 3 4 -0.01122
rs1041894 3 5 -0.009195
rs767249 1 1 0.1065
rs767249 1 2 -0.007243
rs767249 1 3 0.02771
rs767249 1 4 -0.008233
rs767249 1 5 0.05073

but note that awk arrays don't preserve insertion order so in this case your outputs aren't in the same order as they were in the input.

John Kerl
  • 186
  • 5