Sponsored Content
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
Devel::Cover::Tutorial(3)				User Contributed Perl Documentation				 Devel::Cover::Tutorial(3)

NAME
Devel::Cover::Tutorial - An introduction to code coverage VERSION
version 1.03 TUTORIAL
Here's part of a message I sent to perl-qa about code coverage metrics. 1.0 Introduction It is wise to remember the following quote from Dijkstra, who said: Testing never proves the absence of faults, it only shows their presence. In particular, code coverage is just one weapon in the software engineer's testing arsenal. Any discussion of code coverage metrics is hampered by the fact that many authors use different terms to describe the same kind of coverage. Here, I shall provide only a brief introduction to some of the most common metrics. 2.0 Metrics 2.1 Statement coverage This is the most basic form of code coverage. A statement is covered if it is executed. Note that statement != line of code. Multiple statements on a single line can confuse issues - the reporting if nothing else. Where there are sequences of statements without branches it is not necessary to count the execution of every statement, just one will suffice, but people often like the count of every line to be reported, especially in summary statistics. However it is not clear to me that this is actually useful. This type of coverage is fairly weak in that even with 100% statement coverage there may still be serious problems in a program which could be discovered through other types of metric. It can be quite difficult to achieve 100% statement coverage. There may be sections of code designed to deal with error conditions, or rarely occurring events such as a signal received during a certain section of code. There may also be code that should never be executed: if ($param > 20) { die "This should never happen!"; } It can be useful to mark such code in some way and flag an error if it is executed. Statement coverage, or something very similar, can be called statement execution, line, block, basic block or segment coverage. I tend to favour block coverage which does not attempt to extend its results to each statement. 2.2 Branch coverage The goal of branch coverage is to ensure that whenever a program can jump, it jumps to all possible destinations. The most simple example is a complete if statement: if ($x) { print "a"; } else { print "b"; } In such a simple example statement coverage is as powerful, but branch coverage should also allow for the case where the else part is missing: if ($x) { print "a"; } Full coverage is only achieved here if $x is true on one occasion and false on another. 100% branch coverage implies 100% statement coverage. Branch coverage is also called decision or all edges coverage. 2.3 Path coverage There are classes of errors that branch coverage cannot detect, such as: $h = undef; if ($x) { $h = { a => 1 }; } if ($y) { print $h->{a}; } 100% branch coverage can be achieved by setting ($x, $y) to (1, 1) and then to (0, 0). But if we have (0, 1) then things go bang. The purpose of path coverage is to ensure that all paths through the program are taken. In any reasonably sized program there will be an enormous number of paths through the program and so in practice the paths can be limited to a single subroutine, if the subroutine is not too big, or simply to two consecutive branches. In the above example there are four paths which correspond to the truth table for $x and $y. To achieve 100% path coverage they must all be taken. Note that missing elses count as paths. In some cases it may be impossible to achieve 100% path coverage: a if $x; b; c if $x; 50% path coverage is the best you can get here. Loops also contribute to paths, and pose their own problems which I'll ignore for now. 100% path coverage implies 100% branch coverage. Path coverage and some of its close cousins, are also known as predicate, basis path and LCSAJ (Linear Code Sequence and Jump) coverage. 2.4 Expression coverage When a boolean expression is evaluated it can be useful to ensure that all the terms in the expression are exercised. For example: a if $x || $y The expression should be exercised with ($x, $y) set to (0, 0) (required for branch coverage), (0, 1) and (1, 0) (to ensure that $x and $y are independent) and possibly with (1, 1). Expression coverage gets complicated, and difficult to achieve, as the expression gets complicated. Expressions which are not directly a part of a branching construct should also be covered: $z = $x || $y; a if $z; Expression coverage is also known as condition, condition-decision and multiple decision coverage. 3.0 Other considerations In order to get people to actually use code coverage it needs to be simple to use. It should also be simple to understand the results and to rectify any problems thrown up. Finally, if the overhead is too great it won't get used either. So there's a basic tutorial on code coverage, or at least my version of it. Typing a few of these terms into google will probably provide a basis for future research. LICENCE
Copyright 2001-2013, Paul Johnson (paul@pjcj.net) This software is free. It is licensed under the same terms as Perl itself. The latest version of this software should be available from my homepage: http://www.pjcj.net perl v5.16.3 2013-05-20 Devel::Cover::Tutorial(3)
All times are GMT -4. The time now is 09:56 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy