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
    Commented Apr 18, 2020 at 13:45
  • Yes, that is exactly what I am looking for. Commented Apr 19, 2020 at 13:18

2 Answers 2

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. Commented Apr 19, 2020 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
    Commented Apr 19, 2020 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! Commented Apr 19, 2020 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 Commented Apr 19, 2020 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 !!! Commented Apr 19, 2020 at 14:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .