Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Evaluate 2 columns, add sum IF two columns match on two rows
Top Forums Shell Programming and Scripting Evaluate 2 columns, add sum IF two columns match on two rows Post 302841455 by mtucker6784 on Wednesday 7th of August 2013 11:58:14 AM
Old 08-07-2013
Code Evaluate 2 columns, add sum IF two columns match on two rows
Hi all, I know this sounds suspiciously like a homework course; but, it is not.

My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I added together to obtain the sum. I've tried a lot of commands I found on the forum (mainly using awk) and tried piecing my own answer together but can't seem to quite get it. I have One file that looks equivalent to:

ID#,Date,Minutes
100,5/20/2013,22
101,7/21/2013,33
101,7/21/2013,73
101,7/21/2013,73
101,7/23/2013,26
102,7/24/2013,43

The net result I'd like to achieve is:

ID#,Date,Minutes
100,5/20/2013,22
101,7/21/2013,179
101,7/23/2013,26
102,7/24/2013,43

I have two separate Perl scripts which take different files, chops up the columns from those files and combines them to this one file, but I don't know where to go from here to achieve the results I'm looking for. The closest command I've come across is:

Code:
awk '{last=$2}{if(last == $3) getline;print}' f.csv

Which sounds KIND of like what I'm trying to do, to compare the rows/columns to each other and print the results, but I don't understand how to produce the sum if the condition is true.

Is using awk even appropriate? I sure would appreciate any help to go in the right direction.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

2. Shell Programming and Scripting

Sum of range of rows and columns in matrix

Hi all, I have a large matrix of 720 x 25. I want to get sum of range of rows and columns. Like, I need sum of all columns and row number 2 to 21, then leaving 22nd row, again sum of all columns and row number 23 to 42 again leaving 43rd row and then sum of 44th to 63. Means I want to add all... (4 Replies)
Discussion started by: CAch
4 Replies

3. Shell Programming and Scripting

Get the SUM of TWO columns SEPARATELY by doing GROUP BY on other columns

My File looks like: "|" -> Field separator A|B|C|100|1000 D|E|F|1|2 G|H|I|0|7 D|E|F|1|2 A|B|C|10|10000 G|H|I|0|7 A|B|C|1|100 D|E|F|1|2 I need to do a SUM on Col. 5 and Col.6 by grouping on Col 1,2 & 3 My expected output is: A|B|C|111|11100 (2 Replies)
Discussion started by: machomaddy
2 Replies

4. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

5. Shell Programming and Scripting

Rows to Columns with match criteria

Hello Friends, I have a input file having hundreds of rows. I want them to translate in to columns if column 1 is same. Input data: zp06 xxx zp06 rrr zp06 hhh zp06 aaa zp06 ggg zp06 qwer zp06 ser zl11 old3 zl11 old4 zl11 old5 zl11 old6 zl11 old7 zm14 luri zm14 body zm14 ucp (9 Replies)
Discussion started by: suresh3566
9 Replies

6. Shell Programming and Scripting

Add sum of columns and max as new row

Hi, I am a new bie i need some help with respect to shell onliner; I have data in following format Name FromDate UntilDate Active Changed Touched Test 28-03-2013 28-03-2013 1 0.6667 100 Test2 28-03-2013 03-04-2013 ... (1 Reply)
Discussion started by: gangaraju6
1 Replies

7. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

8. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All, I'm embedding SQL query in Script which gives following output: Assignee Group Total ABC Group1 17 PQR Group2 5 PQR Group3 6 XYZ Group1 10 XYZ Group3 5 I have saved the above output in a file. How do i sum up the contents of this output so as to get following output: ... (4 Replies)
Discussion started by: Khushbu
4 Replies

9. UNIX for Dummies Questions & Answers

Merge rows into one if first 2 columns match

Hi, I wanted to merge the content and below is input and required output info. Input: /hello,a,r /hello,a,L /hello,a,X /hi,b,v /hi,b,c O/p: /hello,a,r:L:X /hi,v,:v:c Use code tags, thanks. (6 Replies)
Discussion started by: ankitas
6 Replies

10. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

LEARN ABOUT DEBIAN

torrus_snmpfailures

TORRUS_SNMPFAILURES(8)						      torrus						    TORRUS_SNMPFAILURES(8)

NAME

       snmpfailures - Displays SNMP collector failures.

SYNOPSIS

       torrus snmpfailures --tree=TREENAME [options...]

OPTIONS

       --details
	   In addition to failure counters, list the failed SNMP hosts and the time stamps of failure events.

       --help
	   Displays a help message.

DESCRIPTION

       This utility prints the SNMP collector failure information in JSON format.  Without --details option, it prints only the failure counters.

       Upon collector startup or after the tree re-compilation, the failure counters are reset to zero.

       The output is very convenient for further automatic processing in any scripting language.

       The top level of the output is a JSON object with the following name/value pairs:

       total_unreachable: NUMBER
	   Displays the number SNMP hosts that are currently unreachable.  The number adds up across multiple collector instances for a given
	   tree.  If a host becomes reachable again, the number is decreased.

       total_deleted: NUMBER
	   Displays the number SNMP hosts that are completely removed from SNMP collection for the life cycle of the collector process. This
	   happens when a host is unreachable for too long time and the collector gives up to reach it again.  The number adds up across multiple
	   collector instances for a given tree.

       total_mib_errors: NUMBER
	   Displays the number of MIB errors (noSuchObject, noSuchInstance, and endOfMibView) during the collector life cycle.	The number adds up
	   across multiple collector instances for a given tree.

       detail_unreachable: OBJECT, detail_deleted: OBJECT
	   If the option --details is specified, these objects contain the host names and timestamps of the failures.  The keys are
	   contactenations of SNMP host, UDP port, and SNMP community separated by "|".  The values are objects representing the UNIX timestamp
	   and a human-readable time string.

       detail_mib_errors: OBJECT
	   If the option --details is specified, this object displays the MIB error details: for each SNMP host, it lists the datasource leaves
	   which had these errors and the event timestamps.

EXAMPLES

       The following example illustrates an SNMP host unreachable:

	torrus failures --tree=main --details
	{
	   "detail_deleted" : {},
	   "detail_mib_errors" : {},
	   "detail_unreachable" : {
	      "217.101.101.101|161|public" : {
		 "time" : "Fri Jul 23 14:15:10 2010",
		 "timestamp" : 1279887310
	      }
	   },
	   "total_deleted" : 0,
	   "total_mib_errors" : 0,
	   "total_unreachable" : 1
	}

       The following example illustrates a MIB error:

	torrus failures --tree=main --details
	{
	   "detail_deleted" : {},
	   "detail_mib_errors" : {
	      "217.101.102.102|161|public" : {
		 "count" : 1,
		 "nodes" : {
		    "/Routers/CMTS3/Temperature_Sensors/sensor_01" : {
		       "time" : "Fri Jul 23 15:26:14 2010",
		       "timestamp" : 1279891574
		    }
		 }
	      }
	   },
	   "detail_unreachable" : {},
	   "total_deleted" : 0,
	   "total_mib_errors" : 1,
	   "total_unreachable" : 0
	}

SEE ALSO

       torrus(8),

NOTES

       See more documentation at Torrus home page: http://torrus.org

AUTHOR

       Stanislav Sinyagin <ssinyagin@yahoo.com>

torrus 2.03							    2013-07-26						    TORRUS_SNMPFAILURES(8)
All times are GMT -4. The time now is 11:42 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy