Difference of the same column when two other column matches and one column differs less than 1 hour


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Difference of the same column when two other column matches and one column differs less than 1 hour
# 1  
Old 04-30-2013
Difference of the same column when two other column matches and one column differs less than 1 hour

This is my input file :

Code:
# cat list 20130430121600, cucm, location,76,2 20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,80,8 20130430122000,cucm1,location1,90,8 20130430140000,cucm1,location1,87,11 20130430140000, cucm,location,67,9


This is the required output
Code:
20130430121600, cucm, location,76,2 20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,4,8 20130430122000,cucm1,location1,14,8 20130430140000,cucm1,location1,87,11 20130430140000, cucm,location,67,9

Requirements :
The first column represents YYYYMMDDHHMISS - the date along with the time . The first row, first column represents 2013 year Apr 30 12th hour and 16 min.
I should take delta of 4th column between consecutive rows when 2nd and 3rd column matches, and also the difference between the 1st column is not more than 1 hour

In my list file, first and third row matches in 2nd and 3rd column, and also the difference between the time column is not more than 1 hour, and so I take a diff of the fourth column : 80 -76 =4.

https://www.unix.com/shell-programmin...#post302794979

This gave me a solution without the timestamp column.
Can some one help me out.

Thanks in Advance

---------- Post updated at 04:07 AM ---------- Previous update was at 04:02 AM ----------

Sending it again, as the csv file was not proper.
This is my input file :
20130430121600,cucm,location,76,2
20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,80,8 20130430122000,cucm1,location1,90,8 20130430140000,cucm1,location1,87,11
20130430140000,cucm,location,67,9

This is the required output
20130430121600,cucm,location,76,2
20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,4,8 20130430122000,cucm1,location1,14,8 20130430140000,cucm1,location1,87,11
20130430140000,cucm,location,67,9

Requirements :
The first column represents YYYYMMDDHHMISS - the date along with the time . The first row, first column represents 2013 year Apr 30 12th hour and 16 min.
I should take delta of 4th column between consecutive rows when 2nd and 3rd column matches, and also the difference between the 1st column is not more than 1 hour

In my list file, first and third row matches in 2nd and 3rd column, and also the difference between the time column is not more than 1 hour, and so I take a diff of the fourth column : 80 -76 =4.

https://www.unix.com/shell-programmin...#post302794979

This gave me a solution without the timestamp column.
Can some one help me out.

Thanks in Advance
# 2  
Old 05-07-2013
You need to convert your date to something integer, like seconds since 1970, before you can determine differences.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

If pattern in column 3 matches pattern in column 2 (any row), print value in column 1

Hi all, I have searched and searched, but I have not found a solution that quite fits what I am trying to do. I have a long list of data in three columns. Below is a sample: 1,10,8 2,12,10 3,13,12 4,14,14 5,15,16 6,16,18 Please use code tags What I need to do is as follows: If a... (4 Replies)
Discussion started by: bleedingturnip
4 Replies

2. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

3. Shell Programming and Scripting

How to get difference of the same column between two files when other column matches?

File 1: 20130416,235800,10.78.25.104,BR2-loc,60.0,1624,50.0,0,50.0,0 20130416,235800,10.78.25.104,BR1-LOC,70.0,10,50.0,0,70.0,0 20130416,235800,10.78.25.104,Hub_None,60.0,15,60.0,0,50.0,0 File 2: 20130417,000200,10.78.25.104,BR2-loc,60.0,1626,50.0,0,50.0,0... (3 Replies)
Discussion started by: Lakshmikumari
3 Replies

4. Shell Programming and Scripting

awk - how to get difference of the same column when other column matches

I have a file like this : # cat list cucm, location,76,2 cucm1,location1,76,4 cucm,location,80,8 cucm1,location1,90,8 cucm1,location1,87,11 cucm,location,67,9 and I want output like this : cucm,location,76,2 cucm1,location1,76,4 cucm,location,80, 6 ===> (8-2 =6) cucm1,location1,90,4... (5 Replies)
Discussion started by: Lakshmikumari
5 Replies

5. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

6. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

7. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

8. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

9. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies

10. Shell Programming and Scripting

How to get the count only if two column matches?

Hi, I have a file with the contents as below, 10:23:10 GOOD 10.30.50.60 10:23:11 GOOD 10.30.50.62 10:23:12 Hello 10.30.50.60 10:23:12 BAD 10.30.50.60 10:23:13 GOOD 10.30.50.66 10:23:14 BAD 10.30.50.62... (3 Replies)
Discussion started by: gobinath
3 Replies
Login or Register to Ask a Question

Featured Tech Videos