Median and max of duplicate rows

Hi all,

plz help me with this, I want to to extract the duplicate rows (column 1) in a file which at least repeat 4 times. then I want to summarize them by getting the max , mean, median and min. The file is sorted by column 1, all the repeated rows appear together.

If number of elements is odd, median is middle one , eg 4th element among 7 sorted numbers ... element number (n+1)/2
If number of elements is even, it is the average of middle 2, eg. average of 4th and 5th element for set of 8 sorted numbers...average of n/2 + 1 and n/2

Inp

R1 1
R1 2
R1 3
R2 1
R2 2
R2 3
R2 100
R3 5


output

R2 100 26.25 2.5 1

I figured our uniq -d option will give me the duplicate lines, but how do I work with at least 4?

Also, I tried to find the mean and median, getting errors but trying to get this to work.

sort -n file | awk ' { a[i++]=$2;  N[$1]++}
    END { for (key in i) {
                        avg = sum[key] / N[key];}
x=int((i+1)/2); 
if (x < (i+1)/2)
 print (a[x-1]+a[x])/2 " " avg; 
else print a[x-1] " " avg; }'

Is it this that you are after?

sort file -k1,1 -k2,2n | awk '
{nbr[$1]++; a[$1]= a[$1] ? a[$1]"@"$2 : $2; sum[$1]+=$2}

END {
    for (key in a) {
        split(a[key], b, "@")
        len = length(b)
        for (i=1;i<=len;i++) {
            avg = sum[key] / nbr[key];
            if (nbr[key]%2) {
                median = b[(nbr[key]+1)/2]
            } else {
                median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2
            }
        }
        printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
    }
}
'

This works good for all rows...but how do I print rows only which repeat at least 4 times?
I tried the following modification but it prints out gibberish..

sort file -k1,1 -k2,2n | awk ' {nbr[$1]++; a[$1]= a[$1] ? a[$1]"@"$2 : $2; sum[$1]+=$2}  END {     for (key in a) {         split(a[key], b, "@")         len = length(b)         for (i=1;i<=len;i++) {             avg = sum[key] / nbr[key];             if (nbr[key]%2) {                 median = b[(nbr[key]+1)/2]             } else {                 median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2             }         }
        if (len >3) {  
        printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
         }
 } } '

Also my original files are quite large..eg 500mb each, each there a way to speed this up? Right now it takes forever to run

---------- Post updated at 11:54 AM ---------- Previous update was at 11:10 AM ----------

Update..this seems to run fine... but if anything can be done to speed up..please let me know..

sort testmed.txt -k1,1 -k2,2n | awk '
{nbr[$1]++; a[$1]= a[$1] ? a[$1]"@"$2 : $2; sum[$1]+=$2}

END {
    for (key in a) {
        split(a[key], b, "@")
        len = length(b)
        for (i=1;i<=len;i++) {
            avg = sum[key] / nbr[key];
            if (nbr[key]%2) {
                median = b[(nbr[key]+1)/2]
            } else {
                median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2
            }
        }
        if ( len > 3)
        {
        printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
        }
    }
}
'

Place your condition on the length higher in the code and also change the way to determine that length. Only marginal speed increase to be expected.

sort f -k1,1 -k2,2n | awk '
{nbr[$1]++; a[$1]= a[$1] ? a[$1]"@"$2 : $2; sum[$1]+=$2}

END {
  for (key in a) {
    len = nbr[key]
    if ( len > 3 ) {
      split(a[key], b, "@")
      for (i=1;i<=len;i++) {
        avg = sum[key] / nbr[key];
        if (nbr[key]%2) {
          median = b[(nbr[key]+1)/2]
        } else {
          median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2
        }
      }
      printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
    }
  }
}
'

thanks ripat !! but there seems to be an error in calculating median. It should be 0.00056 but showing 134.79100, also min should be 0

cat testiso_GRMZM2G074386

GRMZM2G074386 0.00000
GRMZM2G074386 0.00000
GRMZM2G074386 0.00000
GRMZM2G074386 0.00056
GRMZM2G074386 2.63247
GRMZM2G074386 112.58600
GRMZM2G074386 134.79100

 awk '
> {nbr[$1]++; a[$1]= a[$1] ? a[$1]"@"$2 : $2; sum[$1]+=$2}
>
> END {
>     for (key in a) {
>         split(a[key], b, "@")
>         len = length(b)
>         for (i=1;i<=len;i++) {
>             avg = sum[key] / nbr[key];
>             if (nbr[key]%2) {
>                 median = b[(nbr[key]+1)/2]
>             } else {
>                 median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2
>             }
>         }
>         if ( len > 3)
>         {
>         printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
>         }
>     }
> }
> ' testiso_GRMZM2G074386
GRMZM2G074386 134.79100 35.7157 134.79100 0.00056

ok, I see where the problem is. The ternary condition was not expecting to see zero values.

Try this:

{nbr[$1]++; a[$1]= (a[$1]!="") ? a[$1]"@"$2 : $2; sum[$1]+=$2} # NEW


END {
  for (key in a) {
    len = nbr[key]
    if ( len > 3 ) {
      split(a[key], b, "@")
      for (i=1;i<=len;i++) {
        avg = sum[key] / nbr[key];
        if (nbr[key]%2) {
          median = b[(nbr[key]+1)/2]
        } else {
          median = (b[(nbr[key]/2)+1] + b[nbr[key]/2])/2
        }
      }
      printf "%s %s %s %s %s\n", key, b[len], avg, median, b[1]
    }
  }
}
'