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

getcol

getcol(1)						      General Commands Manual							 getcol(1)

Name
       getcol - Extract specified columns from an ASCII table file

Synopsis
       getcol [-amv][-n num][-r lines][-s num] filename [column number range]

Description
       Extract specified columns from an ASCII table file

Options
       filename
	      Name  of a ASCII table file.  At least one of these must be present for any values to be printed.  If it is stdin or STDIN, an ASCII
	      table is expected as standard input.  If there is no input file, standard input is assumed.

       @filename
	      Name of a file containing a list of ASCII table files.  If this is present, any other  file  names  on  the  command  line  will	be
	      ignored.

       field range
	      Print  value  of	these  columns for the number of lines of the table specified by the -n argument after the skippiing the number of
	      lines specified by the -s argument.  A value of 0 causes the entire input line to be printed.

       -a     Sum all numeric columns selected, printing the sum on the line following the result.  Columns with  no  sum  are	filled	with  ___.
	      (Added in version 2.6.9)

       -b     Input is bar-separate table file

       -c     Add count of number of lines in each column at end

       -d <number>
	      Number of decimal places in f.p. output

       -e     Compute medians of selected columns

       -f     Print range of values in selected columns

       -h     Print Starbase tab table header

       -i     Input is tab-separate table file

       -k     Print number of columns on first line

       -l <number>
	      Number of lines to add to each line

       -m     Compute the means of all numeric columns selected, printing the mean on the line following the result (or the line following the sum
	      if -a is used).  Columns with no mean are filled with ___.  (Added in version 2.6.9)

       -n num Print selected columns for this many lines.  If not specified, all lines will be read after the number of lines specified by -s have
	      been skipped.

       -o     OR conditions insted of ANDing them

       -p     Print only sum, mmean, sigma, median, or range, not entries

       -r @listfile
	      -r  line	range  Print  columns from the lines specified as either the first nonzero number on each line of the file listfile or the
	      comma- and hyphen- delimitied range; i.e. 1-5,10-12 will print values from lines 1, 2, 3, 4, 5, 10, 11, and 12.  (added  in  version
	      2.6.12)

       -s num Skip this many line before starting to print values.  If not specified, no lines will be skipped.

       -t     Starbase (tab-separated) table output

       -v     Print more information about process.

       Web Page
	      http://tdc-www.harvard.edu/software/wcstools/getcol.html

Author
       Doug Mink, SAO (dmink@cfa.harvard.edu)

8 November 2001 						     WCSTools								 getcol(1)
All times are GMT -4. The time now is 10:55 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy