Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Sum the values in the column using date column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Sum the values in the column using date column
# 1  
Old 04-13-2017
Sum the values in the column using date column
I have a file which need to be summed up using date column.

I/P:

Code:
2017/01/01 a 10
2017/01/01 b 20
2017/01/01 c 40
2017/01/01 a 60
2017/01/01 b 50
2017/01/01 c 40
2017/01/01 a 20
2017/01/01 b 30
2017/01/01 c 40

2017/02/01 a 10
2017/02/01 b 20
2017/02/01 c 30
2017/02/01 a 10
2017/02/01 b 40
2017/02/01 c 60
2017/02/01 a 10
2017/02/01 b 5
2017/02/01 c 15

2017/03/01 a 5
2017/03/01 b 15
2017/03/01 c 20
2017/03/01 d 10
2017/03/01 a 20
2017/03/01 b 25
2017/03/01 c 30
2017/03/01 d 20
2017/03/01 a 5
2017/03/01 b 15
2017/03/01 c 20




O/P:
Code:
2017/01/01 a 90
2017/01/01 b 100
2017/01/01 c 120
2017/02/01 a 30
2017/02/01 b 65
2017/02/01 c 105
2017/03/01 a 30
2017/03/01 b 55
2017/03/01 c 50
2017/03/01 d 30


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!
Last edited by RudiC; 04-13-2017 at 03:59 PM.. Reason: Added CODE tags.
# 2  
Old 04-13-2017
Welcome to the forum.

Any attempts / ideas / thoughts from your side?
# 3  
Old 04-13-2017
It also helps us if you tell us what operating system and shell you're using whenever you post questions in this forum.

Are you sure that the output line:
Code:
2017/03/01 c 50

has the value that you want in the 3rd field?
# 4  
Old 04-13-2017
It worked for me.

Code:
awk '{a[$1" "$2]+=$3+$4}END{for (i in a){print i,a[i]}}'  filename

Last edited by Don Cragun; 04-13-2017 at 05:53 PM.. Reason: Add missing CODE tags.
# 5  
Old 04-13-2017
So - what (except for the strange, non-existing - but not hurting - $4) don't you like with your own approach?
This User Gave Thanks to RudiC For This Post:
Booo
# 6  
Old 04-13-2017
As RudiC said, it looks like:
Code:
awk '{a[$1" "$2]+=$3+$4}END{for (i in a){print i,a[i]}}'  filename

should work, but since there are only 3 fields in any of your input lines, you should get the same results with:
Code:
awk '{a[$1" "$2]+=$3}END{for (i in a){print i,a[i]}}'  filename

Note, however, that with the sample input you provided in post #1 in this thread, the output from the input lines:
Code:
2017/03/01 c 20
2017/03/01 c 30
2017/03/01 c 20

seems to me like it should be:
Code:
2017/03/01 c 70

instead of:
Code:
2017/03/01 c 50

This User Gave Thanks to Don Cragun For This Post:
Booo
# 7  
Old 04-13-2017
Thanks Don. It's a typo, I have tried without $4 and it worked as expected.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. UNIX for Dummies Questions & Answers

Match sum of values in each column with the corresponding column value present in trailer record

Hi All, I have a requirement where I need to find sum of values from column D through O present in a CSV file and check whether the sum of each Individual column matches with the value present for that corresponding column present in the trailer record. For example, let's assume for column D... (9 Replies)
Discussion started by: tpk
9 Replies

3. Shell Programming and Scripting

Sum column values matching other field

this is part of a KT i am going thru. i am writing a script in bash shell, linux where i have 2 columns where 1st signifies the nth hour like 00, 01, 02...23 and 2nd the file size. sample data attached. Desired output is 3 columns which will give the nth hour, number of entries in nth hour and... (3 Replies)
Discussion started by: alpha_1
3 Replies

4. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

5. UNIX for Dummies Questions & Answers

Unique values in a row sum the next column in UNIX

Hi would like to ask you guys any advise regarding my problem I have this kind of data file.txt 111111111,20 111111111,50 222222222,70 333333333,40 444444444,10 444444444,20 I need to get this file1.txt 111111111,70 222222222,70 333333333,40 444444444,30 using this code I can... (6 Replies)
Discussion started by: reks
6 Replies

6. Shell Programming and Scripting

Generate sum of a particular column date wise

Hi All, I have a file with below content 01/22/2014,23:43:00,1742.8, 01/22/2014,23:43:00,1742.8, 01/22/2014,23:44:00,1749.06666666667, 01/25/2014,23:45:00,2046.45, 01/25/2014,23:43:00,1742.8, 01/25/2014,23:44:00,1749.06666666667, 01/25/2014,23:45:00,2046.45, 01/25/2014,23:43:00,1742.8,... (4 Replies)
Discussion started by: villain41
4 Replies

7. Shell Programming and Scripting

Sum up the column values group by using some field

12-11-2012,PNL,158406 12-11-2012,RISK,4564 12-11-2012,VAR_1D,310101 12-11-2012,VAR_10D,310101 12-11-2012,CB,866 12-11-2012,STR_VAR_1D,298494 12-11-2012,STR_VAR_10D,309623 09-11-2012,PNL,1024106 09-11-2012,RISK,4565 09-11-2012,VAR_1D,317211 09-11-2012,VAR_10D,317211 09-11-2012,CB,985... (7 Replies)
Discussion started by: manas_ranjan
7 Replies

8. Shell Programming and Scripting

Getting a sum of column values

I have a file in the following layout: 201008005946873001846130058030701006131840000000000000000000 201008006784994001154259058033001009527844000000000000000000 201008007323067002418095058034801002418095000000000000000000 201008007697126001722141058029101002214158000000000000000000... (2 Replies)
Discussion started by: jclanc8
2 Replies

9. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

10. Shell Programming and Scripting

How to sum column 1 values

I have a file file like this. I want to sum all column 1 values. input A 2 A 3 A 4 B 4 B 2 Out put A 9 B 6 (3 Replies)
Discussion started by: suresh3566
3 Replies
Login or Register to Ask a Question