I made my explanation precise in the CODE below.
I can do this manually. But is there a way to automate this?
If I give 4 or 10 or any number of inputs. It should calculate the CODE and print the different outputs with normalization value ?
some thing like script.sh input1 input2 input3 input4 >>output1 output2 output3 output4 ?
This should work provided your input files aren't too large (it reads everything into core). If you have large input files, then I'd suggest modifying this to make two passes over the input so that only the sums and counts need to be saved in core.
#!/usr/bin/env ksh
awk '
{
input[FILENAME,FNR] = $0; # original input line
ovalue[FILENAME,FNR] = $6; # original value in col 6
nr[FILENAME]++; # num rec current file
sum[FILENAME] += $6; # sum across current file
tsum += $6; # sum across all files
tnv++; # total number of values
if( !seen[FILENAME]++ )
order[oidx++] = FILENAME;
}
END {
tmean = tsum/tnv; # mean of values across all files
for( i = 0; i < oidx; i++ )
{
fn = order;
ofn = sprintf( "%s.out", fn );
for( j = 1; j <= nr[fn]; j++ )
{
nv = (ovalue[fn,j]/sum[fn]) * tmean;
printf( "%s %.3f\n", input[fn,j], nv ) >ofn; # write to output files
#printf( "%s: %s %.3f\n", fn, input[fn,j], nv ); # uncomment to write all to stdout
}
close( ofn );
}
}
' "$@"
This will accept the input files on the command line, and create output file names that are the <input-name>.out.
First of all thanks for your time and script.
Yes my files are very large. May contain 2-3millions of rows. I don't quiet understand "I'd suggest modifying this to make two passes over the input" ?
And also is it possible to make tab-delimited output ?
I almost assumed you'd have a huge dataset, but as soon as I'd have done that you wouldn't have
Here is the script with the few tweeks to make two passes over the data and the output records are tab separated.
#!/usr/bin/env ksh
awk '
{ # first pass to compute sums and total number of lines from all files
sum[FILENAME] += $6; # sum across current file
tsum += $6; # sum across all files
tnv++; # total number of values
seen[FILENAME] = 1;
}
END {
tmean = tsum/tnv; # mean of values across all files
for( fn in seen ) # for each of the original input files
{
ofn = sprintf( "%s.out", fn );
while( (getline < fn) > 0 ) # make second pass across the input file
{
nv = ($6/sum[fn]) * tmean;
gsub( "\t+", " " ); #maybe overkill, but ensure one tab between fields
gsub( " +", " " );
gsub( " ", "\t" );
printf( "%s\t%.3f\n", $0, nv ) >ofn; # write to output files
}
close( fn );
close( ofn );
}
}
' "$@"
In case you don't know, if you need higher precision, change the 3 in %.3f to a larger value.
Glad that worked. Small tweeks below to generate a summary file.
#!/usr/bin/env ksh
awk '
{ # first pass to compute sums and total number of lines from all files
sum[FILENAME] += $6; # sum across current file
tsum += $6; # sum across all files
tnv++; # total number of values
seen[FILENAME] = 1;
}
END {
statsf = "stats.out"; # stats output file name
tmean = tsum/tnv; # mean of values across all files
nin = 0; # number of input files
for( fn in seen )
{
printf( "%s sum = %.0f\n", fn, sum[fn] ) >statsf; # collect stats
ofn = sprintf( "%s.out", fn );
while( (getline < fn) > 0 ) # make second pass across the input file
{
nv = ($6/sum[fn]) * tmean;
gsub( "\t+", " " );
gsub( " +", " " );
gsub( " ", "\t" );
printf( "%s\t%.3f\n", $0, nv ) >ofn; # write to output files
}
close( fn );
close( ofn );
nin++;
}
printf( "mean across %d input files %.0f/%.0f = %.03f\n", nin, tsum, tnv, tsum/tnv ) >statsf;
}
' "$@"
The summary file will be created in the same directory and will have the format:
t31.data sum = 8
t31.data2 sum = 36
mean across 2 input files 44/11 = 4.000
I didn't list the filenames in the last file, just the count, as it could get unruely, and the files are listed before it anyway. I wasn't sure what you meant by $5 in your example. Did you mean the column that was used ($6)???
You should be able to add that to the printf() if you want to see that.
yes you are right it is $6 not $5. Great work. I like the code also. Very clean and understandable.
I just found a small mistake in my calculation of average. it is total number/number of inputs. so it would look like this. Could you please modify the above script. Really sorry for this correction.
And in the input (all) there are only 6 columns.
input1
Small changes below. I've assumed that regardless of the number of columns, the data to normalise is always in the next to last (NF-1) column. This handles the odd case of the "all" file without the need for a specific test.
I'm a bit confused with your new computation for "average." Your words say sum of all values divided by number of input files, but your example shows sum divide by 4. The code below computes the output based on your description and not the example and thus the output for the first record in the first sample file you gave is
a1 10 100 nameX 0 2 + 5.500
because 44 is divided by 2 input files, not 4. If that is wrong, where are you getting 4 from? It might be that in your testing you have two other input files that have all zeros in the n-1 column, and thus your example, and the code, is correct.
Small revisions....
#!/usr/bin/env ksh
awk '
{ # first pass to compute sums and total number of lines from all files
# we assume that data to snarf is always next to last column regardless
# of the number of columns in the input file.
if( !seen[FILENAME]++ ) # must now count input files here
nin++; # number of input files
sum[FILENAME] += $(NF-1); # sum across current file
tsum += $(NF-1); # sum across all files
tnv++; # total number of values
}
END {
statsf = "stats.out"; # stats output file name
#tmean = tsum/tnv; # mean of values across all files (unused)
tmean = tsum/nin; # not the mean anymore though we keep the original name
nin = 0; # number of input files
for( fn in seen ) # make second pass across the input files
{
printf( "%s sum = %.0f\n", fn, sum[fn] ) >statsf; # collect stats
ofn = sprintf( "%s.out", fn );
while( (getline < fn) > 0 )
{
nv = ($(NF-1)/sum[fn]) * tmean;
gsub( "\t+", " " );
gsub( " +", " " );
gsub( " ", "\t" );
printf( "%s\t%.3f\n", $0, nv ) >ofn; # write to output files
}
close( fn );
close( ofn );
}
printf( "mean across %d input files %.0f/%.0f = %.03f\n", nin, tsum, tnv, tsum/tnv ) >statsf;
}
' "$@"
exit
yes it should be 2 not 4. my mistake. the reason why ended up writing 4 is that my real data sets are 4.
---------- Post updated at 08:45 AM ---------- Previous update was at 08:40 AM ----------
And one personal question regarding awk. How come you write so well in awk ? How did you get in to awk and how did you practiced it ? I started with free online awk book. up to few chapters it was easy to follow and the very difficult to grasp the contents. Your suggestion could be really helpful to me. An thank you for the modifications!.
---------- Post updated at 09:12 AM ---------- Previous update was at 08:45 AM ----------
I think some thing wrong with mean in stats file. it should be
I started using awk at some point in 1990 or 91. I bought the O'Reilly Sed & Awk book and went from there. Awk takes a while to wrap your head around, so don't give up. A great way to improve your skills is to look at the posted solutions on this forum. Try to solve the problem yourself, and use the posted solution(s) as a way to "check your answer." Also, having the answer can help if you just don't see how to solve the problem. Do remember that there may be lots of different approaches so your solution might not look like what was posted, but may still work.
---------- Post updated at 10:30 ---------- Previous update was at 10:24 ----------
Oops. Yep missed that one, and another small mistake earlier
#!/usr/bin/env ksh
awk '
{ # first pass to compute sums and total number of lines from all files
# we assume that data to snarf is always next to last column regardless
# of the number of columns in the input file.
if( !seen[FILENAME]++ )
nin++; # number of input files
sum[FILENAME] += $(NF-1); # sum across current file
tsum += $(NF-1); # sum across all files
tnv++; # total number of values
}
END {
statsf = "stats.out"; # stats output file name
#tmean = tsum/tnv; # mean of values across all files (unused)
tmean = tsum/nin; # not the mean anymore, we keep the original name
for( fn in seen ) # make second pass across the input files
{
printf( "%s sum = %.0f\n", fn, sum[fn] ) >statsf; # collect stats
ofn = sprintf( "%s.out", fn );
while( (getline < fn) > 0 )
{
nv = ($(NF-1)/sum[fn]) * tmean;
gsub( "\t+", " " );
gsub( " +", " " );
gsub( " ", "\t" );
printf( "%s\t%.3f\n", $0, nv ) >ofn; # write to output files
}
close( fn );
close( ofn );
}
printf( "mean across %d input files %.0f/%.0f = %.03f\n", nin, tsum, nin, tmean ) >statsf;
}
' "$@"
exit
