awk to average matching lines in file


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers awk to average matching lines in file
# 1  
awk to average matching lines in file

The awk below executes and is close (producing the first 4 columns in desired). However, when I add the sum of $7, I get nothing returned. Basically, I am trying to combine all the matching $4 in f1 and output them with the average of $7 in each match. Thank you Smilie.

f1
Code:
chr9	135804143	135804145	chr9:135804143-135804145	TSC1	1	128
chr9	135804143	135804145	chr9:135804143-135804145	TSC1	2	125
chr9	135819919	135819922	chr9:135819919-135819922	TSC1	1	0
chr9	135819919	135819922	chr9:135819919-135819922	TSC1	2	0
chr9	135819919	135819922	chr9:135819919-135819922	TSC1	3	0
chr16	2097885	2097890	chr16:2097885-2097890	TSC2	1	249
chr16	2097885	2097890	chr16:2097885-2097890	TSC2	2	245
chr16	2097885	2097890	chr16:2097885-2097890	TSC2	3	243
chr16	2097885	2097890	chr16:2097885-2097890	TSC2	4	237
chr16	2097885	2097890	chr16:2097885-2097890	TSC2	5	237

desired tab-delimeted
Code:
chr9	135804143	135804145 	TSC1	126
chr9	135819919	135819922 	TSC1	0
chr16	2097885	2097890 	TSC2	242

awk
Code:
awk '
# print from stored values
function prt(){
  print p1 ":" (p6start==1 ? p2 : p2+p6start) "-" p2+p6, "\t" p5
}
($4!=p4 || $6!=p6+1) {
# new sequence, print the previous sequence
  if (NR>1) prt()
  p6start=$6  
}
{
# store the values that for later
  p1=$1
  p2=$2
  p4=$4
  p5=$5
  p6=$6
}
END { prt() }
' f1 | awk -F"[:-]" ' { print $1 "\t" $2 "\t" $3 "\t" $4}' | awk -v N=7 '{ sum += $N } END { if (NR > 0) print sum / NR }' > out

# 2  
How far would this get you:
Code:
awk '{TMP[$4]=$5; VAL[$4]+=$7; CNT[$4]++} END {for (t in TMP) {i = t; gsub (/[:-]/, OFS, i); print i, TMP[t], VAL[t]/CNT[t]}}' OFS="\t" file
chr16    2097885    2097890    TSC2    242.2
chr9    135804143    135804145    TSC1    126.5
chr9    135819919    135819922    TSC1    0

This User Gave Thanks to RudiC For This Post:
# 3  
Thank you very much Smilie.
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #203
Difficulty: Medium
Perl is not an interpreted language.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to average field if matching string in another

In the awk below I am trying to get the average of the sum of $7 if the string in $4 matches in the line below it. The --- in the desired out is not needed, it is just to illustrate the calculation. The awk executes and produces the current out. I am not sure why the middle line is skipped and the... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. Shell Programming and Scripting

awk to combine matching lines in file

I am trying to combine all matching lines in the tab-delimited using awk. The below runs but no output results. Thank you :). input chrX 110925349 110925532 ALG13 chrX 110925349 110925532 ALG13 chrX 110925349 110925532 ALG13 chrX 47433390 47433999 SYN1... (3 Replies)
Discussion started by: cmccabe
3 Replies

3. Shell Programming and Scripting

awk - matching on 2 columns for differents lines

Given this file (I separated them in block to make my explanation clearer): 92157768877;Sof_deme_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 92157768877;Sof_trav_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 91231838895;Sof_deme_faible_Email_am;EMAIL;26/01/2015;1 0;0... (1 Reply)
Discussion started by: Andy_K
1 Replies

4. UNIX for Dummies Questions & Answers

awk - Print lines if only matching key is found

I am looking to move matching lines (01 - 07) from File1 and 77 tab the matching string from File2, to File3.txt. I am almost done but - Currently, script is not printing lines to File3.txt in order. Thanks a lot. Any help is appreciated. Script I am using: awk 'FNR == NR && ! /^]*$/ {... (9 Replies)
Discussion started by: High-T
9 Replies

5. Shell Programming and Scripting

Help With AWK Matching and Re-printing Lines

Hi All, I'm looking to use AWK to pattern match lines in XML file - Example patten for below sample would be /^<apple>/ The sample I wrote out is very basic compared to what I am actually working with but it will get me started I would like to keep the matched line(s) unchanged but have them... (4 Replies)
Discussion started by: rhoderidge
4 Replies

6. Shell Programming and Scripting

How to add lines of a file and average them

I'm reading in numbers from a file and trying to add them together. Here is the code so far. I know the 1+2+3.... part is wrong. The file has five numbers in it with each number on its own line. The numbers are decimals if that matters. Thanks. while read EachLine do echo $EachLine done <... (6 Replies)
Discussion started by: AxlVanDamme
6 Replies

7. Shell Programming and Scripting

awk file comparison, x lines after matching as output

Hello, I couldn't find anything on the Forum that would help me to solve this problem. Could any body help me process below data using awk? I have got two files: file1: Worker1: Thomas Position: Manager Department: Sales Salary: $5,000 Worker2: Jason Position: ... (5 Replies)
Discussion started by: killerbee
5 Replies

8. Shell Programming and Scripting

awk print non matching lines based on column

My item was not answered on previous thread as code given did not work I wanted to print records from file2 where comparing column 1 and 16 for both files find rows where column 16 in file 1 does not match column 16 in file 2 Here was CODE give to issue ~/unix.com$ cat f1... (0 Replies)
Discussion started by: sigh2010
0 Replies

9. Shell Programming and Scripting

Finding lines matching the Pattern and their previous lines in a file

Hi, I am trying to locate the occurences of certain pattern like 'Possible network disconnect' in a text file. I can get the actual lines matching the pttern using: grep -w 'Possible network disconnect' file_name. But I am more interested in getting the timing of these events which are... (7 Replies)
Discussion started by: sagarparadkar
7 Replies

10. Shell Programming and Scripting

Print lines matching value(s) in other file using awk

Hi, I have two comma separated files. I would like to see field 1 value of File1 exact match in field 2 of File2. If the value matches, then it should print matched lines from File2. I have achieved the results using cut, paste and egrep -f but I would like to use awk as it is efficient way and... (7 Replies)
Discussion started by: SBC
7 Replies

Featured Tech Videos