You could try something like:
#!/bin/ksh
IAm=${0##*/} # Save basename of the current script.
tempf="$IAm.$$" # Set temp filename to be used by sort.
awk -v debug="$debug" -v tempf="$tempf" '
# Usage: median()
# DESCRIPTION: Compute the median for the 1st lc values in f3[] and print the
# lc entries in the arrays f1[], f2[], and f3[] and the computed
# median. Set id to compare to following input lines, and clear
# lc.
function median( high, i, low, m, x) {
# Save the 1st field on the current line to compare against subsequent
# lines.
id = $1
# If lc is zero, we do not have any data to process. (This should
# only happen when we are looking at the 1st line after the header
# line.)
if(lc == 0) return
# Close the file produced by sorting the field 3 values for the
# previous id.
close(sortcmd)
# Compute the input lines from the sorted list needed to determine the
# median value in the array f3[].
if(lc % 2) {
# For odd numbers of lines, we just need the middle line.
low = high = (lc + 1) / 2
} else {# For even number of lines, we need the average of the middle
# two lines.
low = lc / 2
high = low + 1
}
if(debug) printf("low=%d, high=%d, ", low, high)
# Compute the median...
m = 0
for(i = 1; i <= high; i++) {
# Read the 1st high lines from the sorted file.
getline x < tempf
if(i == low || i == high) {
# Add values from the middle line or lines.
m += x
if(debug) printf("median before averaging=%d, ", m)
}
}
# Close the sorted input file.
close(tempf)
# Compute the median.
if(low != high) m /= 2
if(debug) print "median=" m
# Print the original data (reformatted) and add the median to each line.
for(i = 1; i <= lc; i++) {
if(debug) printf("lc=%d, i=%d, ", lc, i)
printf("%s\t%s\t%s\t%s\n", f1, f2, f3, m)
}
# Clear the arrays.
lc = 0
}
NR == 1 {
# We have the 1st input line. Print the header.
printf("%s\t%s\t%s\tmedian\n", $1, $2, $3)
# Initialize the command to sort field 3 values.
sortcmd = "sort -n -o \"" tempf "\""
next
}
{ # We are not on the header line. If the id field has changed since the
# previous line, compute the median and print the accumulated data and
# median for the previous id.
if(id != $1) median()
# Save data from the current line to print when we find the next id.
f1[++lc] = $1
f2[lc] = $2
f3[lc] = $3
# Sort the values from the 3rd field for the current id.
print $3 | sortcmd
}
END { # Compute the median and print the accumulated data and median for the
# last id.
median()
}' file
# Save the exit code from awk.
ec=$?
# Remove the temp file used to sort the 3rd field for each id.
rm -f "$tempf"
# Exit with awk's exit code.
exit $ec
This was written and tested using the Korn shell on Mac OS X. If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk . If you have gawk , you could use its built-in sorting capabilities to sort a copy of the f3[] array instead of using the external sort command to sort that field. If your input was sorted with the primary key being the 1st field (in increasing or decreasing order) and with the secondary key being the 3rd field (sorted as an increasing or decreasing numeric field) you wouldn't need to sort the field 3 data at all. You could just change the line:
# Compute the median...
to:
# Compute the median�
m = (f3[low] + f3[high]) / 2
and remove all of the code shown in red above. (Of course the header could not be sorted unless the sort key used and the data in the file sorted the header to be the 1st line of the input file.) This is why the way you sort your input matters!
If you save this script in a file named add_median and have the following data in a file named file :
id col2 col3
fly 0 10
fly 1 10
fly 2 0
fly 3 3
fly 4 0
emu 0 10
emu 1 10
emu 2 0
emu 3 3
emu 4 0
emu 5 0
dog 0 0
dog 1 4
dog 2 3
cat 0 2
cat 1 4
bee 0 6
bee 1 3
and invoke the script as:
./add_median
you'll get the output:
id col2 col3 median
fly 0 10 3
fly 1 10 3
fly 2 0 3
fly 3 3 3
fly 4 0 3
emu 0 10 1.5
emu 1 10 1.5
emu 2 0 1.5
emu 3 3 1.5
emu 4 0 1.5
emu 5 0 1.5
dog 0 0 3
dog 1 4 3
dog 2 3 3
cat 0 2 3
cat 1 4 3
bee 0 6 4.5
bee 1 3 4.5
If you want to see the debugging output so you can more easily following what the script is doing, invoke the script with:
debug=1 ./add_median
and you'll see the output:
id col2 col3 median
low=3, high=3, median before averaging=3, median=3
lc=5, i=1, fly 0 10 3
lc=5, i=2, fly 1 10 3
lc=5, i=3, fly 2 0 3
lc=5, i=4, fly 3 3 3
lc=5, i=5, fly 4 0 3
low=3, high=4, median before averaging=0, median before averaging=3, median=1.5
lc=6, i=1, emu 0 10 1.5
lc=6, i=2, emu 1 10 1.5
lc=6, i=3, emu 2 0 1.5
lc=6, i=4, emu 3 3 1.5
lc=6, i=5, emu 4 0 1.5
lc=6, i=6, emu 5 0 1.5
low=2, high=2, median before averaging=3, median=3
lc=3, i=1, dog 0 0 3
lc=3, i=2, dog 1 4 3
lc=3, i=3, dog 2 3 3
low=1, high=2, median before averaging=2, median before averaging=6, median=3
lc=2, i=1, cat 0 2 3
lc=2, i=2, cat 1 4 3
low=1, high=2, median before averaging=3, median before averaging=9, median=4.5
lc=2, i=1, bee 0 6 4.5
lc=2, i=2, bee 1 3 4.5