Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Multiple Column Calculation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Multiple Column Calculation
# 1  
Old 09-17-2014
Multiple Column Calculation
Hallo Team,

I need you you help. I need to sum up all the columns in a .csv file. Lets call the file file1.csv and it looks like below:

Code:
[mind@util1-pkl MHG_201408]$ cat file1.csv
AccountCode,AccountName,TotalCalls,TotalCallDuration,TotalCost,TotalTelkomAsIf,ContactCalls,ContactCallDuration,ContactCallCost,ContactTotalTelkomAsIf,LocalCalls,LocalCallDuration,LocalCallCost,LocalTotalTelkomAsIf,NationalCalls,NationalCallDuration,NationalCallCost,NationalTotalTelkomAsIf,CellularCalls,CellularCallDuration,CellularCallCost,CellularTotalTelkomAsIf,CommunityCalls,CommunityCallDuration,CommunityCallCost,CommunityTotalTelkomAsIf,GlobalCalls,GlobalCallDuration,GlobalCallCost,GlobalTotalTelkomAsIf,FirstCallDate,LastCallDate
485554,Polmed - BLM,415,06:46:27,132.88,437.72,0,00:00:00,0,0,1,00:12:37,4.42,4.65,39,02:09:48,36.13,49.68,105,02:31:55,85.56,232.55,17,00:45:10,6.77,23.28,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
758495,Polmed -  Polokwane,587,13:40:35,235.73,713.02,0,00:00:00,0,0,1,00:04:15,1.49,1.57,75,02:41:39,46.64,66.23,200,05:01:50,167.98,435.59,42,02:10:49,19.62,69.94,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
661178,Cape Town,238878,14210:29:27,230762.11,558658.12,39819,4033:46:29,36303.97,122416,23486,997:24:45,15112.02,25513.91,55683,3462:23:34,57864.36,96828.61,83028,2844:30:26,94609.77,240402.11,36844,2870:38:01,26767.37,73344.1,18,01:46:12,104.6,154.3,22-JUL-2014,21-AUG-2014
668900,Polmed - Kimberly,224,03:36:33,72.23,237.76,0,00:00:00,0,0,0,00:00:00,0,0,19,00:58:44,15.36,26.43,51,01:26:46,48.94,119.05,18,00:52:53,7.93,27.68,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
631005,Polmed - Nelspruit,1426,38:15:09,1054.03,2695.12,0,00:00:00,0,0,1,00:00:10,0.06,0.5,220,08:26:01,129.18,204.46,1083,28:10:58,914.5,2412.22,52,01:08:42,10.3,42.36,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
822234, Pretoria,94738,7399:38:47,94198.51,273010.77,60709,5875:49:54,53006.06,178499.14,5142,222:23:39,3363.99,5571.01,3663,193:23:56,3216.58,4723.23,23787,1019:04:50,33809.97,81823.8,1437,88:56:28,801.9,2394.49,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
520704,Polmed - Umtata,267,13:21:46,273.79,693.19,0,00:00:00,0,0,0,00:00:00,0,0,26,01:53:45,34.43,52.04,146,05:35:57,187.43,452.07,61,05:46:12,51.93,173.78,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
155945,Vois - Durban,18988,999:46:49,18354.78,36190.84,0,00:00:00,0,0,3504,157:58:41,2384.77,3936.31,5216,285:28:38,4727.31,6449.08,6866,241:12:39,8096.79,20232.9,3402,315:06:51,3145.9,5572.56,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
781681,Polmed - DBN,486,18:29:35,516.74,1212.01,0,00:00:00,0,0,1,00:00:03,0.02,0.5,69,03:07:20,53.84,76.02,384,13:27:41,446.34,1085.64,31,01:50:15,16.54,56.21,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
298334, Durban,121,07:13:34,65.03,220.06,121,07:13:34,65.03,221.13,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,18-AUG-2014,21-AUG-2014
146536,Polmed - PE,907,19:02:36,423.68,1266.53,0,00:00:00,0,0,0,00:00:00,0,0,97,06:00:02,100.35,184.16,204,08:41:21,311.82,743.63,26,01:43:06,11.51,49.06,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
563416, Braamfontein,96329,7622:36:01,106670.89,264574.65,9063,673:10:03,6332.19,20613.26,39093,2521:42:32,36332.08,68009.39,3684,243:20:27,3939.92,5789.69,23616,916:25:09,30427.34,74614.67,20846,3266:09:43,29399.49,95251.26,27,01:48:07,239.87,298.45,22-JUL-2014,21-AUG-2014
995958,Polmed - Paarl,356,15:40:53,412.96,941.17,0,00:00:00,0,0,2,00:00:43,0.24,1,41,02:45:59,43.31,49.88,168,10:39:36,353.03,775.38,27,01:49:12,16.38,56.38,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
347012,Polmed - Mafikeng,522,13:34:18,370.33,949.97,0,00:00:00,0,0,1,00:01:28,0.51,0.54,64,02:46:03,48.38,69.75,287,09:36:14,316.66,785.55,18,00:32:57,4.78,16.35,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
616356, QUALSA,2,00:00:17,0,0.5,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,01-AUG-2014,14-AUG-2014

I would like to sum up all columns and please note that the time format is important and should be hhhh:mm:ss

This is how i would like the final output to be like below: lets call the output file file2.csv

cat file2.csv
Code:
Inbound-Final,344534,5581:43:20,357836.44,820051.9,,,,643499.06,142463,3899:48:53,114394.78,206074.11,137753,4215:25:56,140475.45,229088.84,279745,5106:25:22,339466.7,847997.77,125625,6557:30:19,120514.07,354131.62,90,3:34:19,344.47,452.75,22-Jul-14,21-Aug-14

Regards,

Pax
Last edited by rbatte1; 09-18-2014 at 12:05 PM.. Reason: moved start of CODE block
# 2  
Old 09-18-2014
Hello kekanap,

I have a few to questions pose in response first:-
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.


We're all here to learn and getting the relevant information will help us all.



Robin
# 3  
Old 09-18-2014
Use Excel or Calc from OpenOffice
Since the file is csv format that can be done with Excel or Calc. I would do like this in two steps:
1) Sort the columns to have time-cols together; save them to one file. Let's call it time_sheet.csv; save the other single number columns to another file. Let's call it other_sheet.csv
2) Open time_sheet.csv by selecting : as delimiter when you open. Use Macro within Excel next to the end of the data rows (Say C17).
Code:
=SUM(C2:C16)

Copy the formula (say C17) and paste to the rest of columns alike. Do a little massage to convert seconds to minutes and then minutes to hours.
Open other_sheet.csv as it is and do the same calculation. Combine the results and sort back to the original column order.

The trick to keep the original column order is to assign an extra header row to the original file. Say 1 ~ 32 whatever you like. The hh:mm::ss columns can be marked as 2a 2b 2c; 6a 6b 6c etc.
The functions you may need is sum, mod, trunc(). Check the help. The number based on your sample are:
Code:
 ,,454246,30382:12:47,453543.69,1141801.43,109712,10590:00:00,95707.25,321749.53,71232,162.4922801,57199.6,103039.38,68896,175.6430093,70255.79,114569.26,139925,212.7676157,169776.13,424115.16,62821,273.1877199,60260.42,177077.45,45,0.148831019,344.47,452.75,,

Good luck!
Last edited by yifangt; 09-18-2014 at 07:23 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to copy a column of multiple files and paste into new excel file (next to column)?

I have data of an excel files as given below, file1 org1_1 1 1 2.5 100 org1_2 1 2 5.5 98 org1_3 1 3 7.2 88 file2 org2_1 1 1 2.5 100 org2_2 1 2 5.5 56 org2_3 1 3 7.2 70 I have multiple excel files as above shown. I have to copy column 1, column 4 and paste into a new excel file as... (26 Replies)
Discussion started by: dineshkumarsrk
26 Replies

2. Shell Programming and Scripting

Assigning multiple column's value from Oracle query to multiple variables in UNIX

Hi All, I need to read values of 10 columns from oracle query and assign the same to 10 unix variables. The query will return only one record(row). I tried to append all these columns using a delimiter(;) in the select query and assign the same to a single variable(V) in unix. I thought I... (3 Replies)
Discussion started by: hkrishnan91
3 Replies

3. Shell Programming and Scripting

Select multiple column from multiple files

Hi Friends, $ cat test1.txt emeka:1438 shelley:1439 dmeyer:1440 kurtarn:1441 abdul:1442 $ cat test2.txt 1:a 2:b 3:c 4:d $ cat test3.txt cat:dog:bat man:hot:cold (5 Replies)
Discussion started by: Jewel
5 Replies

4. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

5. Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Dear fellows, I need your help. I'm trying to write a script to convert a single column into multiple rows. But it need to recognize the beginning of the string and set it to its specific Column number. Each Line (loop) begins with digit (RANGE). At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies

6. Shell Programming and Scripting

Calculation of column both horizontally and vertically

Hi ALL I have this data ail,UTT,id1_0,COMBO,21,24,21,19 al,UTHAST,id1_0,COMBO,342,390,361,361 dmo,UTST,id1_0,COMBO,21,15,22,23 vne,UAST,id1_0,COMBO,345,372,390,393 I wan the sum of column 5,6,7 & 8 both horizontal and vertical. There is one more prob the column keeps on increasing... (9 Replies)
Discussion started by: nikhil jain
9 Replies

7. Shell Programming and Scripting

one column into multiple column

I have a file with data as below. Each row has just one column. aaaa bbbb ccc ddd eeee ffff gggg hhhh iiii I need the output as aaaa bbbb ccc ddd eeee ffff gggg hhhh iiii for each 4 rows I want the output in 4 column. For all the data in the file. Can anyone please help... (4 Replies)
Discussion started by: sudgks
4 Replies

8. Shell Programming and Scripting

echo multiple variable with calculation

$total=500 echo "scale=2; $val1*100/$total" | bc echo "scale=2; $val2*100*100/$total" | bc echo "scale=2; $val3*100/$total" | bc I want to make the above code to be accomplish in a single echo line. For instance output:21.3, 44.2, 51.6 How to achieve that, some one please help, i just... (5 Replies)
Discussion started by: alvin0618
5 Replies

9. Shell Programming and Scripting

Calculation in Multiple files using awk

Hi All, I have some 10 files named samp1.csv, samp2.csv,... samp10.csv Each file having the same number of fields like, Count, field1, field2, field3. And a source.csv file which has three fields field1, field2, field3. Now, i want to find the total count by taking the field1,... (8 Replies)
Discussion started by: johnwilliams.sp
8 Replies

10. UNIX for Dummies Questions & Answers

Multiple column into single column

Hi , i have an output as follows... xxx yyyy aaaa bbb and i want it to be printed as xxx yyyy aaaa bbb Any help please... (3 Replies)
Discussion started by: appu2176
3 Replies
Login or Register to Ask a Question