awk to calculate difference of split and sum the difference


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to calculate difference of split and sum the difference
# 1  
Old 02-19-2018
awk to calculate difference of split and sum the difference

In the awk I am trying to subtract the difference $3-$2 of each matching $4 before the first _ (underscore) and print that value in $13.
I think the awk will do that, but added comments. What I am not sure off is how to add a line or lines that will add sum each matching $13 value and put it in the first line of each matching array[1] in $14. The file is tab-delimited and is a subset of the actual data, but there are all the same format. Thank you Smilie.

file
Code:
chr12	9220418	9220435	A2M_cds_0_0_chr12_9220419_r	0	-	.	-1	-1		.	0
chr12	9220778	9220820	A2M_cds_1_0_chr12_9220779_r	0	-	.	-1	-1		.	0
chr12	9241795	9241847	A2M_cds_14_0_chr12_9241796_r	0	-	.	-1	-1		.	0
chr12	9262909	9262930	A2M_cds_31_0_chr12_9262910_r	0	-	.	-1	-1		.	0
chr12	9264754	9264807	A2M_cds_32_0_chr12_9264755_r	0	-	.	-1	-1		.	0
chr12	53708877	53708924	AAAS_cds_11_0_chr12_53708878_r	0	-	.	-1	-1		.	0
chr12	53709510	53709566	AAAS_cds_13_0_chr12_53709511_r	0	-	.	-1	-1		.	0

desired output
Code:
chr12	9220418	9220435	A2M_cds_0_0_chr12_9220419_r	0	-	.	-1	-1		.	0	17	185
chr12	9220778	9220820	A2M_cds_1_0_chr12_9220779_r	0	-	.	-1	-1		.	0	42	
chr12	9241795	9241847	A2M_cds_14_0_chr12_9241796_r	0	-	.	-1	-1		.	0	52	
chr12	9262909	9262930	A2M_cds_31_0_chr12_9262910_r	0	-	.	-1	-1		.	0	21	
chr12	9264754	9264807	A2M_cds_32_0_chr12_9264755_r	0	-	.	-1	-1		.	0	53	
chr12	53708877	53708924	AAAS_cds_11_0_chr12_53708878_r	0	-	.	-1	-1		.	0	47	103
chr12	53709510	53709566	AAAS_cds_13_0_chr12_53709511_r	0	-	.	-1	-1		.	0	56

awk
Code:
awk '
BEGIN {FS = OFS = "\t"}  # define FS and OFS as tab
FNR==NR{                 # process same line
         split($4,array,"_");   # split $4 on _ and srore in array
         print $0,(array[1] in a) && print ($3-$2), $13  # print matching lines of array[1] and the difference of $3-$2 in $13 
         next  # process next line
       }' file


Last edited by cmccabe; 02-19-2018 at 09:44 AM.. Reason: fixed format
# 2  
Old 02-19-2018
Code:
awk '
BEGIN {FS = OFS = "\t"}  # define FS and OFS as tab
FNR==NR{                 # process same line
         split($4,array,"_");   # split $4 on _ and srore in array
         b[FNR]=$3-$2; a[array[1], array[2]]+=b[FNR];
         next  # process next line
}
{
   split($4,array,"_");
   print $0, b[FNR], ((!c[array[1], array[2]]++) ? a[array[1], array[2]] : "");
}
' file file

This User Gave Thanks to rdrtx1 For This Post:
# 3  
Old 02-22-2018
Thank you very much Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calculate time difference

I have time in a file in HH:MM:SS format as it contents(its not the file creation time). i need this to be converted to epoch time or time since 1970. The time is written into that file by a script, which i cannot modify. Im using AIX machine $ cat abc.txt 10:29:34 (2 Replies)
Discussion started by: gpk_newbie
2 Replies

2. Shell Programming and Scripting

Calculate date difference

Hi All Can you please help me with UNIX script code that will work with ksh shell on UNIX server My Requirement Time1: 09/17/13101536 Time2: 09/16/13101536 I want to calculate the difference in minutes for the dates with format given above. I have a requirement to wait for... (5 Replies)
Discussion started by: jimmyb
5 Replies

3. Shell Programming and Scripting

How to calculate difference:?

Experts, file1 : Want to find the difference of $3 field from next line's 3rd field, The difference to be calculated from next lines 3rd field, to current lines lines 3rd field. file1 : Jun24_2013.06242013 3301244928 3133059904 167370640 95% Jun25_1124.06252013 3301244928... (4 Replies)
Discussion started by: rveri
4 Replies

4. Shell Programming and Scripting

How to Calculate the difference between two dates?

I want the difference between two following date using scripts in terms of no.of days. How I can accomplish this. lastdate=Tue Nov 13 10:30:56 2012 currdate=Wed Dec 15 15:58:21 PAKST 2012 Ouput should be like this: Your Password will expire after = 32 Days on Wed Dec 15 15:58:21 PAKST... (1 Reply)
Discussion started by: m_raheelahmed
1 Replies

5. Shell Programming and Scripting

[Solved] sum up third and second columns by 0 difference

Hi Friends, I have the following file chr1 1 2 chr1 2 3 chr1 3 4 chr1 4 5 chr1 5 6 chr1 19 20 chr1 20 21 chr1 21 22 I want to compare the third column of record 1 to second column of next record and if the difference is zero, consider its third column and match it to next record... (4 Replies)
Discussion started by: jacobs.smith
4 Replies

6. Shell Programming and Scripting

Calculate age of a file | calculate time difference

Hello, I'm trying to create a shell script (#!/bin/sh) which should tell me the age of a file in minutes... I have a process, which delivers me all 15 minutes a new file and I want to have a monitoring script, which sends me an email, if the present file is older than 20 minutes. To do... (10 Replies)
Discussion started by: worm
10 Replies

7. Shell Programming and Scripting

Difference between cksum and sum in unix

i get 2 different values when i give cksum and sum command. Are they 2 different algorithms? can someone explain how this is different (2 Replies)
Discussion started by: nidhink
2 Replies

8. Shell Programming and Scripting

How to calculate the time difference.

Hi All, I've written a script which reads all the systems backup information and saves it in a log file. ssh -l ora${sid} ${primaryhost} "tail -1 /oracle/$ORACLE_SID/sapbackup/back$ORACLE_SID.log" | awk '{print $3,$4,$5,$6}' >> ${RESULTFILE} The output comes as below: 2008-09-30 06.00.01... (2 Replies)
Discussion started by: suri.tyson
2 Replies

9. Shell Programming and Scripting

How to calculate the time difference...

Hi All, I've written a script which reads all the systems backup information and saves it in a log file. ssh -l ora${sid} ${primaryhost} "tail -2 /oracle/$ORACLE_SID/sapbackup/back$ORACLE_SID.log" |head -1 | awk '{print echo "PREVIOUS:-- Start Date&Time: " $3,$4,echo "|| End Date&Time:... (1 Reply)
Discussion started by: suri.tyson
1 Replies

10. Shell Programming and Scripting

How to calculate this time difference

Hi, Please help me in calculating the time difference between below mentioned timestamps. a=07/17/2007 02:20:00 AM MST b=07/17/2007 02:07:46 AM MST Thanks (2 Replies)
Discussion started by: Prat007
2 Replies
Login or Register to Ask a Question