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
  • 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
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.

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.

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.