Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Generate sum of a particular column date wise

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Generate sum of a particular column date wise
# 1  
Old 01-30-2014
Generate sum of a particular column date wise
Hi All,
I have a file with below content
Code:
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,
01/25/2014,23:44:00,1749.06666666667,
01/29/2014,23:45:00,2046.45,
01/29/2014,23:43:00,1742.8,
01/29/2014,23:44:00,1749.06666666667,
01/29/2014,23:45:00,2046.45,

I want to get a command that actually sum up the 3rd column for each date wise and finally give me the result

say example like below (rough calculation done)
Code:
01/22/2014  4567
01/25/2014  8965
01/29/2014  9852

Last edited by Franklin52; 01-30-2014 at 07:13 AM.. Reason: Please use code tags
# 2  
Old 01-30-2014
Hello,

Following may help.

Code:
awk -F"," 'NR==FNR{a[$1]+=$3;next} a[$1]{print $1" "a[$1]}' get_total_3rd get_total_3rd | uniq

output will be as follows.

Code:
01/22/2014 5234.67
01/25/2014 11076.6
01/29/2014 7584.77

Where get_total_3rd is the input file name.


Thanks,
R. Singh
Last edited by RavinderSingh13; 01-30-2014 at 07:00 AM.. Reason: adding input file name
# 3  
Old 01-30-2014
Thanks a lot for ur response. I tried below stuff
Code:
Hostname:~ 905>cat two.txt
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,
01/25/2014,23:44:00,1749.06666666667,
01/29/2014,23:45:00,2046.45,
01/29/2014,23:43:00,1742.8,
01/29/2014,23:44:00,1749.06666666667,
01/29/2014,23:45:00,2046.45,
Hostname:~ 906>
Hostname:~ 907> awk -F"," 'NR==FNR{a[$1]+=$3;next} a[$1] {print $1" "a[$1]}' two.txt | uniq
Hostname:~ 908>

I am not getting any error but getting blank prompt comes up..
am I doing anything wrong here..plz help me

---------- Post updated at 03:12 AM ---------- Previous update was at 03:06 AM ----------

Its working..
I used the file names 2 times now.
I initially thought the file name is duplicated mistakenly in ur command.
Now I tried ur command, its 100% working, thanks a lot again forur help.

---------- Post updated at 03:19 AM ---------- Previous update was at 03:12 AM ----------

Hi,
The code is working awesome..
instead of exponential sum can I get the real integer number, plz

01/23/2014 5.04681e+06
01/24/2014 4.9229e+06
01/25/2014 4.26656e+06
01/26/2014 4.94703e+06
01/27/2014 6.0064e+06
01/28/2014 5.4583e+06
01/29/2014 5.22587e+06
01/30/2014 3931
Last edited by villain41; 01-30-2014 at 07:18 AM.. Reason: Please use code tags
# 4  
Old 01-30-2014
if order doesn't matter then use this

Code:
$ awk '{A[$1]+=$3}END{for(i in A)print i,A[i]}' FS="," file

@ Ravinder you can do like this also, provided sum is not zero
Code:
$ awk -F"," 'NR==FNR{a[$1]+=$3;next} a[$1]{print $1" "a[$1];a[$1]=""}' file file

for example :

Code:
[akshay@aix tmp]$ cat file
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,
01/25/2014,23:44:00,1749.06666666667,
01/29/2014,23:45:00,0,
01/29/2014,23:43:00,0,
01/29/2014,23:44:00,0,
01/29/2014,23:45:00,0,

[akshay@aix tmp]$ awk -F"," 'NR==FNR{a[$1]+=$3;next} a[$1]{print $1" "a[$1];a[$1]=""}' file file
01/22/2014 5234.67
01/25/2014 11076.6

This could be solution for above problem
Code:
[akshay@aix tmp]$ awk -F"," 'NR==FNR{a[$1]+=$3;next}($1 in a){print $1" "a[$1];delete a[$1]}' file file
01/22/2014 5234.67
01/25/2014 11076.6
01/29/2014 0

Last edited by Akshay Hegde; 01-30-2014 at 08:39 AM.. Reason: Note
This User Gave Thanks to Akshay Hegde For This Post:
RavinderSingh13
# 5  
Old 01-31-2014
Thank you for your timely help
Thanks a lot for the timely help guys. I love this forum.

---------- Post updated 01-31-14 at 01:39 AM ---------- Previous update was 01-30-14 at 08:59 PM ----------

Guys,
I am stuck again. Probably the reason was I did not understand it completely.
Now I have below scenario same as the initial one but a new column added

This time I would completely understand it so that whenever a new column is added I can still use the same command with small alteration.
Can u plz explain what modification in command required whenever a newer column added but I still want date-wise data for each column.

when thr is only one column for any date below comamnd works as mentioned above.


Code:
awk -F"," 'NR==FNR{a[$1]+=$3;next}($1 in a){print $1" "a[$1];delete a[$1]}' ff2.txt ff2.txt


Code:
[akshay@aix tmp]$ cat ff2.txt
01/22/2014,23:43:00,1742.8,199.8,
01/22/2014,23:43:00,1742.8,287.9
01/22/2014,23:44:00,1749.06666666667,764.8
01/25/2014,23:45:00,2046.45,656.3
01/25/2014,23:43:00,1742.8,786.1
01/25/2014,23:44:00,1749.06666666667,875.9
01/25/2014,23:45:00,2046.45,890.91
01/25/2014,23:43:00,1742.8,765.11
01/25/2014,23:44:00,1749.06666666667,678.98
01/29/2014,23:45:00,0,651.11
01/29/2014,23:43:00,0,781.13
01/29/2014,23:44:00,0,871.23
01/29/2014,23:45:00,0,893.19

Last edited by villain41; 01-31-2014 at 06:15 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sum the values in the column using date column

I have a file which need to be summed up using date column. I/P: 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... (6 Replies)
Discussion started by: Booo
6 Replies

2. Shell Programming and Scripting

Proper Column wise matching

My below code works fine if none of the columns has pipe as its content in it, If it has the pipe in any of the content then the value moves to the next column. I wanted my code to work fine even if the column has pipe in it apart from the delimiter. NOTE : If there is a pipe in the content... (6 Replies)
Discussion started by: nikhil jain
6 Replies

3. Shell Programming and Scripting

Date wise calculations?

POST_DATE CHECK_NUMBER TYPE LOGIN_NAME 2015.09.09 XXXXXXXXXX mark XXXXXXXXXX 2015.09.09 XXXXXXXXXX fsadf XXXXXXXXXX 2015.10.05 XXXXXXXXXX defaa XXXXXXXXXX 2015.10.05 XXXXXXXXXX dewe XXXXXXXXXX 2015.10.06 XXXXXXXXXX dqwe XXXXXXXXXX 2015.09.14 XXXXXXXXXX dt4e XXXXXXXXXX... (22 Replies)
Discussion started by: nikhil jain
22 Replies

4. Shell Programming and Scripting

Column wise text adding

Hi I have pasted sample data as below:- in data.txt Please suggest any way out: as the 3rd field is cat data.txt 22:37:34 STARTING abc 22:37:40 FAILURE sadn 00:06:42 STARTING asd 00:06:51 FAILURE ad 02:06:38 STARTING acs 02:06:46 FAILURE cz 04:06:35 STARTING xzc... (1 Reply)
Discussion started by: Gaurav198
1 Replies

5. Shell Programming and Scripting

Sum of column by group wise

Hello All , I have a problem with summing of column by group Input File - COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11 3010,21,1923D ,6,0,0.26,0,0.26,-0.26,1,200807 3010,21,192BI ,6,24558.97,1943.94,0,1943.94,22615.03,1,200807 3010,21,192BI... (8 Replies)
Discussion started by: jambesh
8 Replies

6. Solaris

delete files by date wise

Hi guys, I want to delete files from june 13 to june 30, using rm command can any one tell me the sintax to remove. I ahve hunderd of core files in my /var dir. so i want to clear last month core files. Thanks in Advance.:)) (2 Replies)
Discussion started by: kurva
2 Replies

7. Solaris

column wise substitution in a file

Hi, I have two files. Want to make an addition of the fifth column of from both the files and redirect it to a third file. Both files have same records except fifth field and same record should be inserted into new file having fifth field as addition of fifth fields of both files. for... (2 Replies)
Discussion started by: sanjay1979
2 Replies

8. Shell Programming and Scripting

Column wise file parsing.

Shell script for the below operation : File "A" contains : SEQ++1' MOA+9:000,00:ABC' RFF+AIK:000000007' FII+PH+0170++AA' NAD+PL+++XXXXXXXXXXX XXXXXXX XX++XXX XXXX XXXX X.X. XXXXXXXXX+++NL' SEQ++2' MOA+9:389,47:ABC' RFF+AIK:02110300000008' FII+PH+0PSTBNL2A:25:5+BB'... (5 Replies)
Discussion started by: navojit dutta
5 Replies

9. Shell Programming and Scripting

o/p column wise by nawk

hi i have file which hav following entries 1501,AAA,2.00 1525,AAA,2.00 1501,AAA,2.00 1525,AAA,2.00 1501,AAA,3.00 1525,AAA,3.00 1525,AAA,3.00 1501,AAA,3.00 1501,AAA,3.00 i want to have a o/p coloum wise like 1501,AAA,13 1525,AAA,10 here 13 comes as a sum of last colum value... (6 Replies)
Discussion started by: aaysa123
6 Replies

10. Shell Programming and Scripting

processing matrix column wise

I have a m X n matrix written out to file, say like this: 1,2,3,4,5,6 2,6,3,10,34,67 1,45,6,7,8,8 I want to calculate the column averages in the MINIMUM amount of code or processing possible. I would have liked to use my favorite tool, "AWK" but since it processes rowwise, getting the... (5 Replies)
Discussion started by: Abhishek Ghose
5 Replies
Login or Register to Ask a Question