Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Average columns based on header name
Homework and Emergencies Emergency UNIX and Linux Support Average columns based on header name Post 302969075 by RudiC on Thursday 17th of March 2016 06:39:18 PM
Old 03-17-2016
As I said, I'm braindead. I missed the average, sorry. Try printing SUM/(L[i+1]-L[i]). That's not OK for the very first entry as the first field is summed, too, as its value is zero, so the element count is too high. But - I'm scared to touch it, either.

No one liner. Whereever a oneliner can be accommodated, a multiliner will fit, too. Use a function. Source a file.

---------- Post updated at 22:40 ---------- Previous update was at 22:28 ----------

Try this one - the field 1 error should be removed. Please perform a careful validation of the results and come back:
Code:
awk '
NR == 1         {print "Gene    Average_10%     Average_20%     Average_30%     Average_40%     Average_50%     Average_60%"\
                        "       Average_70%     Average_80%     Average_90%     Average_100%"
                 ST   = B
                 i    = 2
                 C    = 1
                 L[C] = i
                 gsub ("%", _)
                 while (i <= NF)        {while ($i < ST) i++ 
                                         if ($i == ST)   i++
                                         L[++C] = i

                                         ST += B
                                         if (ST > 100)  {H[++D] = C - 1
                                                         ST = B
                                                        }
#                                                               print i, $i, ST, L[C], $L[C], D, H[D]
                                        }
                 next
                }

                {printf "%s", $1
                 i = D = 1
                 while (i < C)  {SUM = 0
#                                                               print "--->", i, L[i], L[i+1]
                                 for (j=L[i]; j<L[i+1]; j++) SUM+=$j
                                 printf "%s%8.5f", OFS, SUM / (L[i+1]-L[i])   
                                 if (i == H[D]) {printf RS
                                                 D++
                                                }   
                                 i++
                                }   
                }
' FS="\t" OFS="\t" B=10 /tmp/gene.txt
Gene    Average_10%     Average_20%     Average_30%     Average_40%     Average_50%     Average_60%     Average_70%     Average_80%     Average_90%     Average_100%
Gene1    0.00855         0.00291         0.01258         0.01164         0.01844         0.00503         0.01067         0.00201         0.01067         0.02960
         0.01366         0.01788         0.00634         0.01459         0.01129         0.00537         0.01976         0.01464         0.01082         0.02353
         0.01647         0.01369         0.00795         0.01205         0.00110         0.01590         0.00822         0.00795         0.00712         0.01753
         0.02016         0.01944         0.00787         0.00711         0.01849         0.01672         0.02133         0.01721         0.00664         0.03888
         0.01044         0.02701         0.00385         0.01006         0.01589         0.01099         0.01059         0.01978         0.01430         0.02331
         0.00687         0.02251         0.00687         0.01501         0.01015         0.01465         0.01633         0.01511         0.01633         0.04193
         0.01573         0.02513         0.00442         0.01470         0.01517         0.01770         0.01470         0.01622         0.01944         0.03224
         0.00955         0.01799         0.00521         0.00628         0.02343         0.00998         0.01046         0.01562         0.01339         0.01130
         0.01012         0.00975         0.00361         0.00279         0.01045         0.01806         0.04250         0.02457         0.01951         0.02230
         0.01170         0.01787         0.00536         0.00752         0.00423         0.02340         0.01457         0.00829         0.01552         0.03432
         0.00518         0.01885         0.00748         0.01885         0.00887         0.02358         0.01220         0.02415         0.01996         0.03882
         0.02561         0.01468         0.00830         0.01201         0.01201         0.00830         0.02202         0.01453         0.00734         0.02402
         0.01636         0.01465         0.01441         0.01427         0.01052         0.02726         0.02516         0.03194         0.02554         0.02516

---------- Post updated at 23:39 ---------- Previous update was at 22:40 ----------

Actually, transposing the file like
Code:
awk '{gsub ("\t", RS); print > "FILE" NR}' gene.txt
paste FILE1 FILE2

simplified the logics seriously. Piping the paste result into
Code:
|  awk '
#NR > 1000      {exit}
NR == 1         {print; ST = B; F = 2; next}
$1+0 < ST       {SUM+=$2; next}
$1+0 == ST      {SUM+=$2; getline}
                {print ST, SUM, SUM/(NR-F)
                 SUM = $2
                 ST = ST%100 + B
                 F = NR}
' B=10
Gene    Gene1
10 0.230957 0.00855398
20 0.0815144 0.00291123
30 0.339643 0.0125794
40 0.326058 0.0116449
50 0.516258 0.0184378
60 0.135857 0.00503175
70 0.298886 0.0106745
80 0.0543429 0.0020127
90 0.298886 0.0106745
100 0.82873 0.0295975
10 0.368873 0.013662
20 0.500614 0.0178791
30 0.171263 0.00634306
40 0.408395 0.0145855
50 0.316177 0.011292
60 0.144914 0.0053672
70 0.55331 0.0197611
80 0.395221 0.0146378
90 0.303003 0.0108215
100 0.658702 0.0235251
10 0.444711 0.0164708
20 0.383372 0.0136918
30 0.214688 0.00795141
40 0.337367 0.0120488
50 0.0306697 0.00109535
60 0.429376 0.0159028
70 0.230023 0.00821511
80 0.214688 0.00795141
90 0.199353 0.00711976
100 0.490716 0.0175256
10 0.544259 0.0201577
20 0.544259 0.0194378
30 0.212394 0.00786644
40 0.199119 0.0071114
50 0.51771 0.0184896
60 0.451337 0.0167162
70 0.597357 0.0213342
80 0.464611 0.0172078
90 0.185845 0.0066373
100 1.08852 0.0388756
10 0.281795 0.0104369
20 0.756398 0.0270142
30 0.103819 0.00384516
40 0.281795 0.0100641
50 0.44494 0.0158907
60 0.296627 0.0109862
70 0.296627 0.0105938
80 0.533928 0.0197751
90 0.400446 0.0143016
100 0.652579 0.0233064
.
.
.

gives you an idea - results may still need a bit polishing.
This User Gave Thanks to RudiC For This Post:
jacobs.smith
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk based script to find the average of all the columns in a data file

Hi All, I need the modification for the below mentioned code (found in one more post https://www.unix.com/shell-programming-scripting/27161-script-generate-average-values.html) to find the average values for all the columns(but for a specific rows) and print the averages side by side. I have... (4 Replies)
Discussion started by: ks_reddy
4 Replies

2. Shell Programming and Scripting

Average of columns with values of other column with same name

I have a lot of input files that have the following form: Sample Cq Sample Cq Sample Cq Sample Cq Sample Cq 1WBIN 23.45 1WBIN 23.45 1CVSIN 23.96 1CVSIN 23.14 S1 31.37 1WBIN 23.53 1WBIN 23.53 1CVSIN 23.81 1CVSIN 23.24 S1 31.49 1WBIN 24.55 1WBIN 24.55 1CVSIN 23.86 1CVSIN 23.24 S1 31.74 ... (3 Replies)
Discussion started by: isildur1234
3 Replies

3. Shell Programming and Scripting

Average, min and max in file with header, using awk

Hi, I have a file which looks like this: FID IID MISS_PHENO N_MISS N_GENO F_MISS 12AB43131 12AB43131 N 17774 906341 0.01961 65HJ87451 65HJ87451 N 10149 906341 0.0112 43JJ21345 43JJ21345 N 2826 906341 0.003118I would... (11 Replies)
Discussion started by: kayakj
11 Replies

4. Shell Programming and Scripting

Extract columns based on header

Hi to all, I have two files. File1 has no header, two columns: sample1 A sample2 B sample3 B sample4 C sample5 A sample6 D sample7 D File2 has a header, except for the first 3 columns (chr,start,end). "sample1" is the header for the 4th ,5th ,6th columns, "sample2" is the header... (4 Replies)
Discussion started by: aec
4 Replies

5. Shell Programming and Scripting

Make copy of text file with columns removed (based on header)

Hello, I have some tab delimited text files with a three header rows. The headers look like, (sorry the tabs look so messy). index group Name input input input input input input input input input input input... (9 Replies)
Discussion started by: LMHmedchem
9 Replies

6. Shell Programming and Scripting

Average across multiple columns group by

Hi experts, I want to group by average, for multiple columns starting column $7 until NF, group by ($1-$5), please help For just 7th column, I can do awk ' NR>1{ arr += $7 count += 1 } END{ for (a in arr) { print a, arr/count ... (10 Replies)
Discussion started by: ritakadm
10 Replies

7. UNIX for Beginners Questions & Answers

Keep only columns in first two rows based on partial header pattern.

I have this code below that only prints out certain columns from the first two rows (doesn't affect rows 3 and beyond). How can I do the same on a partial header pattern “G_TP” instead of having to know specific column numbers (e.g. 374-479)? I've tried many other commands within this pipe with no... (4 Replies)
Discussion started by: aachave1
4 Replies

8. Shell Programming and Scripting

Find columns in a file based on header and print to new file

Hello, I have to fish out some specific columns from a file based on the header value. I have the list of columns I need in a different file. I thought I could read in the list of headers I need, # file with header names of required columns in required order headers_file=$2 # read contents... (11 Replies)
Discussion started by: LMHmedchem
11 Replies

9. Shell Programming and Scripting

Average of a columns from three files

hello, I have three files in the following order ==> File1 <== 1 20977000 20977000 A C 1.00 0,15 15 45 1 115829313 115829313 G A 0.500 6,7 13 99 ==> File2 <== 1 20977000 20977000 A C 1.00 0,13 13 39 1 115829313 ... (5 Replies)
Discussion started by: nans
5 Replies

10. UNIX for Beginners Questions & Answers

Average of columns

I have files that have the following columns chr pos ref alt sample 1 sample 2 sample 3 chr2 179644035 G A 1,107 0,1 58,67 chr7 151945167 G T 142,101 100,200 500,700 chr13 31789169 CTT CT,C 6,37,8 0,0,0 15,46,89 chr22 ... (3 Replies)
Discussion started by: nans
3 Replies
All times are GMT -4. The time now is 09:35 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy