Average for multiple keys


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Average for multiple keys
# 1  
Old 10-08-2013
Average for multiple keys

Hi,

I want to find row-wise average of multiple columns based on 2 columns.
I have 30k values in the matrix with 94 cols.


Code:
Example Input for cols 4 and 5 as keys

1 2 3 a 1
4 5 6 a 1
4 2 0 a 1
1 2 3 b 2
5 6 7 b 2
9 7 5 b 2

Output 

3 3 3 a 1
5 5 5 b 2

Here is what I have tried, works if I use a single column such as $1 or $2 but not for , but not for all ($0).


Code:
awk '{s[$4$5]+=$0;n[$1]++}END{for (i in s) print i,s[i]/n[i]}'

# 2  
Old 10-08-2013
Try:
Code:
awk '{f[$4" "$5]+=$1;s[$4" "$5]+=$2;t[$4" "$5]+=$3;n[$4" "$5]++}END{for (i in n) print f[i]/n[i],s[i]/n[i],t[i]/n[i],i}' file

This User Gave Thanks to bartus11 For This Post:
# 3  
Old 10-08-2013
thank you , but I have 94 columns..is there a way do all without specifying?
# 4  
Old 10-08-2013
Put this into "script.pl":
Code:
#!/usr/bin/perl
use strict;
open I, "$ARGV[0]";
my $n;
my %cnt;
my %sum;
while (chomp(my $line=<I>)) {
  my @F=split / /, $line;
  $n=$#F;
  for (my $i=0; $i<$n-1; $i++) {
    $sum{$F[$n-1] . " " . $F[$n]}[$i]+=$F[$i];
  }
  $cnt{$F[$n-1] . " " . $F[$n]}++;
}
for my $key (keys %sum) {
  for (my $i=0; $i<$n-1; $i++) {
    print $sum{$key}[$i]/$cnt{$key} . " ";
  }
  print "$key\n";
}

Then run:
Code:
perl script.pl file

This User Gave Thanks to bartus11 For This Post:
# 5  
Old 10-08-2013
An awk approach that might work:
Code:
awk '
        {
                for ( i = 1; i <= (NF-2); i++ )
                {
                        T[i" "$(NF-1)" "$NF] += $i
                        N[i" "$(NF-1)" "$NF]++
                        if ( !(R[i" "$(NF-1)" "$NF]) )
                        {
                                ++c
                                R[i" "$(NF-1)" "$NF]  = i" "$(NF-1)" "$NF
                                I[c] = i" "$(NF-1)" "$NF
                                P[c] = $(NF-1)" "$NF
                        }
                }
        }
        END {
                for ( k = 1; k <= c; k++ )
                        printf !(k % 3) ? T[I[k]] / N[I[k]] OFS P[k] RS : T[I[k]] / N[I[k]] OFS

        }
' file

This User Gave Thanks to Yoda For This Post:
# 6  
Old 10-09-2013
Thank you, the codes works for the sample data but doesnt seem to work for a minimal file, I have tried with
Code:
awk -F"\t" '

...the last two columns are the keys. can you tell me what is the problem?
The perl script produces a blank output. I have attached a sample minimal file.


......................................
Update..figured out I had the delimiter with an extra space.

works just fine !!! THANK YOU !!

Last edited by ritakadm; 10-09-2013 at 05:48 PM..
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Calculate average from a given set of keys and values

Hello, I am writing a script which expects as its input a hash with student names as the keys and marks as the values. The script then returns array of average marks for student scored 60-70, 70-80, and over 90. Output expected 50-70 1 70-90 3 over 90 0 The test script so far... (4 Replies)
Discussion started by: nans
4 Replies

2. Shell Programming and Scripting

Find All duplicates based on multiple keys

Hi All, Input.txt 123,ABC,XYZ1,A01,IND,I68,IND,NN 123,ABC,XYZ1,A01,IND,I67,IND,NN 998,SGR,St,R834,scot,R834,scot,NN 985,SGR0399,St,R180,T15,R180,T1,YY 985,SGR0399,St,R180,T15,R180,T1,NN 985,SGR0399,St,R180,T15,R180,T1,NN 2943,SGR?99,St,R68,Scot,R77,Scot,YY... (2 Replies)
Discussion started by: unme
2 Replies

3. Shell Programming and Scripting

Perl - multiple keys and merging two files

Hi, I'm not a regular coder but some times I write some basic perl script, hence Perl is bit difficult for me :). I'm merging two files a.txt and b.txt into c.txt: a.txt ------ x001;frtb70;xyz;109 x001;frvt65;sec;239 x003;wqax34;jul;659 x004;yhud43;yhn;760 b.txt ------... (8 Replies)
Discussion started by: Lokesha
8 Replies

4. UNIX for Dummies Questions & Answers

Sort with multiple keys

Please suggest a sort command to achieve the below task. Thanks. I want to sort a file considering multiple keys. Sort Keys: Field 2, Field4 and Field6 Input file vqrs,16,zzz,1235,eq,T abcd,11,zzz,1234,pq,F abcd,10,zzz,1235,pq,F lqrs,15,zzz,1235,eq,T pqrs,12,zzz,1234,eq,F... (3 Replies)
Discussion started by: pretty1234
3 Replies

5. Shell Programming and Scripting

[SOLVED] Sort on multiple keys

Can you guys pls take a look at this. I need to sort this list of numbers as follows: 2nd col first, then 1st col, then 3rd col, all in reverse (highest to lowest). I'm doing this: sort -k 2,2nr -k 1,1nr -k 3,3gr but, as you see, the 3rd col does not get sorted properly. Any idea... (0 Replies)
Discussion started by: mamboknave
0 Replies

6. Shell Programming and Scripting

Sorting problem: Multiple delimiters, multiple keys

Hello If you wanted to sort a .csv file that was filled with lines like this: <Ticker>,<Date as YYYYMMDD>,<Time as H:M:S>,<Volume>,<Corr> (H : , M, S: ) by date, does anybody know of a better solution than to turn the 3rd and 4th colons of every line into commas, sorting on four keys,... (20 Replies)
Discussion started by: Ryan.
20 Replies

7. Shell Programming and Scripting

Sum a column value based on multiple keys

Hi, I have below as i/p file: 5ABC 36488989 K 000010000ASB BYTRES 5PQR 45757754 K 000200005KPC HGTRET 5ABC 36488989 K 000045000ASB HGTRET 5GTH 36488989 K 000200200ASB BYTRES 5FTU ... (2 Replies)
Discussion started by: nirnkv
2 Replies

8. UNIX for Dummies Questions & Answers

Joining files based on multiple keys

I need a script (perl or awk..anything is fine) to join 3 files based on three key columns. The no of non-key columns can vary in each file. The columns are delimited by semicolon. For example, File1 Dim1;Dim2;Dim3;Fact1;Fact2;Fact3;Fact4;Fact5 ---- data delimited by semicolon --- ... (1 Reply)
Discussion started by: Sebben
1 Replies

9. Shell Programming and Scripting

Sorting with multiple numeric keys

Data I want to sort :- 1 10 jj Y 2 100 vv B 19 5 jj A 1 11 hq D 3 8 op X 44 78 ds GG 1 8 hq D and want to sort based on the first 2 columns - which hold numeric values. Am using : cat filename | sort -nk 1,2 But the result is :- 1 10 jj Y 1 11 hq D (1 Reply)
Discussion started by: sinpeak
1 Replies

10. UNIX for Advanced & Expert Users

sort on multiple keys

Hello, Say I have a file with plain text as shown below. Some columns may have multiple words (like "DESC 1", "DESC 1 2", "DESC 1 2 3"). Let's say the file below has 4 columns: 1st(AA), 2nd(BB), 3rd(DESC 1, ...), 4th(CC 1, ...). 1234567890123456789012345678901234567890 AA BB DESC 1... (1 Reply)
Discussion started by: teqmem
1 Replies
Login or Register to Ask a Question