0

I have a large dataframe, in which one of the fields("ID") correspond to a unique identifier. However, there are multiple instances of that value, depending on the binary combination of additional measurements("strucureA" and "structureB"). I want to devise a way to filter the rows using awk to shows measurements associated with an ID that only has 4 instances in the initial dataframe.

I can envision doing this in two parts, first counting the number of instances using uniq -c or a awk array and saving to a file, and then using that new file to filter the initial dataframe with awk arrays and FNR==NR. But I was hoping that there may be a way for awk to tally the count and then print a group of rows that meet a criteria (e.g. an entry appearing X number of times ).

> Initial table
ID  structureA  structureB
sample_1    1   1
sample_2    1   1
sample_2    2   1
sample_2    1   2
sample_2    2   2
sample_3    1   1
sample_3    2   1
sample_3    3   1
sample_4    1   1
sample_4    2   1
sample_4    3   1
sample_4    4   1

> Desired table
ID  structureA  structureB
sample_2    1   1
sample_2    2   1
sample_2    1   2
sample_2    2   2
sample_4    1   1
sample_4    2   1
sample_4    3   1
sample_4    4   1
2
  • 1
    Let me see if I understand it: If the number of occurrences of $1 is exactly 4, you want to print those 4 lines? Is that all? – Quasímodo Apr 18 '20 at 13:45
  • Yes, that is exactly what I am looking for. – ricardo3889 Apr 19 '20 at 13:18
1

Thanks to Ed Morton for improvement of the awk script:

awk -v n=$1 '
NR==1
{a[$1]=($1 in a ? a[$1] RS : "") $0}
END{for(i in a){if(gsub(RS,"&",a[i])==n-1){print a[i]}}}
' "$2"

NR==1 prints the header. The last statement invokes split function just to check how many lines there are in each array element. If they are 4, then they are printed.

You call it with ./script 4 file (the 1st argument is the number of repetitions, the 2nd is the file name) after making it executable.

This answer does not impose that lines with same first field must be next to each other in the input file.

4
  • I generally understand the flow of your script, but I think you are missing something. The associative array a[$1] in your code is only working when NR==1, so the END statement will not work as you intended. – ricardo3889 Apr 19 '20 at 13:21
  • @ricardo3889 The NR==1 statement is equivalent to NR==1{print}. It does not apply to the line below, which is executed for every line of the input file, so the array is correctly populated and END works as expected. – Quasímodo Apr 19 '20 at 14:01
  • my bad, you are totally right. I tested the script and it works as expected. One Q, I am not familiar with the "?" in '$1 in a ? a[$1]' could you explain it a little more? Thanks a lot! – ricardo3889 Apr 19 '20 at 14:13
  • I just found it. That's a nice use of a conditional! 'x?y:z' means If x is true, then y; else z – ricardo3889 Apr 19 '20 at 14:15
1
$ awk -v n=4 '
    NR==1 { print; next }
    $1 != prev { if (cnt==n) printf "%s", buf; prev=$1; cnt=0; buf="" }
    { cnt++; buf=buf $0 ORS }
    END { if (cnt==n) printf "%s", buf }
' file
ID  structureA  structureB
sample_2    1   1
sample_2    2   1
sample_2    1   2
sample_2    2   2
sample_4    1   1
sample_4    2   1
sample_4    3   1
sample_4    4   1
1
  • 1
    Thank you Ed, this works !!! – ricardo3889 Apr 19 '20 at 14:19

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.