Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

First_File, Second_File and Final_File awk magic

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting First_File, Second_File and Final_File awk magic
# 1  
Old 08-22-2014
First_File, Second_File and Final_File awk magic
Hallo Team,

I hope my explanation is clear. Please assist if you can.

max1.csv
Code:
AccountCode,AccountName,TotalCalls,TotalCallDuration,TotalCost,TotalTelkomAsIf
485554,MHG - Polmed - BLM,415,06:46:27,132.88,437.72
758495,MGH -  Polmed -  Polokwane,587,13:40:35,235.73,713.02
661178,MHG - Cape Town,238878,14210:29:27,230762.11,558658.12
668900,MHG - Polmed - Kimberly,224,03:36:33,72.23,237.76
631005,MHG - Polmed - Nelspruit,1426,38:15:09,1054.03,2695.12
822234,MHG - Pretoria,94738,7399:38:47,94198.51,273010.77
520704,MHG - Polmed - Umtata,267,13:21:46,273.79,693.19
155945,MHG- Vois - Durban,18988,999:46:49,18354.78,36190.84
781681,MHG - Polmed - DBN,486,18:29:35,516.74,1212.01
298334,MHG - Durban,121,07:13:34,65.03,220.06
146536,MHG - Polmed - PE,907,19:02:36,423.68,1266.53
563416,MHG - Braamfontein,96329,7622:36:01,106670.89,264574.65
995958,MHG - Polmed - Paarl,356,15:40:53,412.96,941.17
347012,MHG - Polmed - Mafikeng,522,13:34:18,370.33,949.97
616356,MHG - QUALSA-POLOKWANE,2,00:00:17,0,0.5

max2.csv
Code:
ContactCalls,ContactCallDuration,ContactCallCost,ContactTotalTelkomAsIf
0,00:00:00,0,0
0,00:00:00,0,0
39819,4033:46:29,36303.97,122416
0,00:00:00,0,0
0,00:00:00,0,0
60709,5875:49:54,53006.06,178499.14
0,00:00:00,0,0
0,00:00:00,0,0
0,00:00:00,0,0
121,07:13:34,65.03,221.13
0,00:00:00,0,0
9063,673:10:03,6332.19,20613.26
0,00:00:00,0,0
0,00:00:00,0,0
0,00:00:00,0,0

I would like to sum up all the values under max1 and max2
Code:
Max1_summed_up.csv
MHG - Inbound    454246    16171:43:20    453543.69    1141801.43

Code:
Max2_summed_up.csv
109712    10590:00:00    95707.25    321749.53

Now from here i would like to subtract Max2_summed_up.csv from Max1_summed_up.csv

So Final_File.csv should be as below

Code:
cat Final_File.csv
MHG - Inbound Final    344534    5581:43:20    357836.44    820051.9    0    0:00:00    0    0

I thank you in advance and regards,

Pax
Last edited by bartus11; 08-22-2014 at 12:42 PM.. Reason: Please use [code][/code] tags instead of [icode] and [quote].
# 2  
Old 08-22-2014
Try
Code:
awk     '               {for (i=0; i<=3; i++) S[i]+=$(ST+i)*SGN}
         END            {printf "MHG - Inbound Final\t"; for (i=0; i<=3; i++) printf "%s\t", S[i]; printf "\n"}
        ' CONVFMT="%.8g" FS="," ST=3 SGN=1 file1 ST=1 SGN=-1 file2
MHG - Inbound Final    344534    19787    357836.44    820051.9

I did it all in one go, and I didn't bother to resolve the time fields to e.g. seconds as you didn't seem to care for the times either (14210 + 7399 + 999 + 7622 (hours?) is definitely more that 16171 hours). And, I'm unable to deduce where the last 4 fields in your desired output come from.
Please adapt if need be.
This User Gave Thanks to RudiC For This Post:
kekanap
# 3  
Old 08-23-2014
Thank you RudiC,

How ever the time format is important. Is there a way we can keep it in the desired format hhhh:mm:ss ?

Thanking you for all the hard work and dedication.

Regards,

Pax

---------- Post updated at 02:01 PM ---------- Previous update was at 10:02 AM ----------

Rudic,

Can you show me a command that i can use to achieve results for
Max1_summed_up.csv, Max2_summed_up.csv and Final_File.csv. The time format is important.

Regards and thank you in advance.

Paxley
# 4  
Old 08-23-2014
Use split on $2/$4 to get at the hours/minutes and convert to seconds, sum those, and, before printing out, convert back to h/m/s.
Print out when the input file changes.
# 5  
Old 08-24-2014
How about this:

Code:
awk 'FNR==1{f++}
function tm2sec(ts) {
   split(ts,tms,":")
   return tms[1]*3600+tms[2]*60+tms[3]
}
function sec2tm(secs) {
  h=int(secs/3600)
  m=int((secs-h*3600)/60)
  s=secs-h*3600-m*60
  return sprintf("%02d:%02d:%02d",h,m,s)
}
FNR>1 {
  for (i=0; i<=3; i++)
   if (i==1) S[f,1]+=tm2sec($(ST+i))
   else S[f,i]+=$(ST+i)
}
END {
   printf "MHG - Inbound\t%.8g\t%s\t%.8g\n", S[1,0], sec2tm(S[1,1]), S[1,2]

   printf "%.8g\t%s\t%.8g", S[2,0], sec2tm(S[2,1]), S[2,2]

   printf "\nMHG - Inbound Final\t%.8g",S[1,0] - S[2,0]
   printf "\t%s", sec2tm(S[1,1]-S[2,1])
   printf "\t%.8g",S[1,2] - S[2,2]
   printf "\t0\t0:00:00\t0\t0\n"
}' FS="," ST=3 max1.csv ST=1 max2.csv

Output:

Code:
MHG - Inbound   454246  30382:12:47     453543.69
109712  10590:00:00     95707.25
MHG - Inbound Final     344534  19792:12:47     357836.44       0       0:00:00 0       0

# 6  
Old 08-25-2014
Chubler thats awesome brother.

I am sorry i have a question on top of what i asked.
i have another file called max3.csv and looks like below:
Code:
[mind@util1-pkl MHG_201408]$ cat max3.csv
LocalCalls,LocalCallDuration,LocalCallCost,LocalTotalTelkomAsIf,NationalCalls,NationalCallDuration,NationalCallCost,NationalTotalTelkomAsIf,CellularCalls,CellularCallDuration,CellularCallCost,CellularTotalTelkomAsIf,CommunityCalls,CommunityCallDuration,CommunityCallCost,CommunityTotalTelkomAsIf,GlobalCalls,GlobalCallDuration,GlobalCallCost,GlobalTotalTelkomAsIf
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
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
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
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
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
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
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
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
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
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,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
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
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
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
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
[mind@util1-pkl MHG_201408]$

How can i calculate the total for each column taking into account the time format. The desired output should be like below:
Code:
LocalCalls,LocalCallDuration,LocalCallCost,	LocalTotalTelkomAsIf,NationalCalls,NationalCallDuration,NationalCallCost,NationalTotalTelkomAsIf,CellularCalls,CellularCallDuration,CellularCallCost,CellularTotalTelkomAsIf,	CommunityCalls,CommunityCallDuration,CommunityCallCost,CommunityTotalTelkomAsIf,GlobalCalls,GlobalCallDuration,GlobalCallCost,GlobalTotalTelkomAsIf	

71232,3899:48:53,57199.6,103039.38,68896,4215:25:56,70255.79,114569.26,139925,5106:25:22,169776.13,424115.16,62821,6557:30:19,60260.42,177077.45,45,3:34:19,344.47,452.75

Regards,

Pax
Last edited by Franklin52; 08-25-2014 at 08:25 AM.. Reason: Please use CODE tags, not ICODE tags for code blocks. Thanks.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

sed magic

Hello all, I have an inherited an old ugly script I'm trying to clean up a bit. What I am currently working on is a line like the following: Complication=" and ta.secID = 011222 and upper(ta.proc_ctrl_no) != 'IMPACT'";; I just want to combine a search for lines that begin with... (5 Replies)
Discussion started by: blip42
5 Replies

2. What is on Your Mind?

I got a magic wand

My new magic wand just came. It looks great. Looking at it, it looks like it is carved by hand from a solid piece of wood. It could easily serve as a prop in a Harry Potter film. But it is actually a high tech appliance. A built-in accelerometer can detect how you are moving the wand. ... (2 Replies)
Discussion started by: Perderabo
2 Replies

3. Shell Programming and Scripting

sed magic

I want to become an expert @ using sed, but i do not have enough time... Atm I have a repository in my apt/sources.list but it is commented out since i do not want to install packages from it (backtrack repository) except the exploitdb package. I would like make a command, lets say... (5 Replies)
Discussion started by: redsolja
5 Replies

4. UNIX for Dummies Questions & Answers

magic 8ball

when i use this code for the script of the magic 8ball, i get an error message and it always displays the sam answer. What am i doing wrong or what am i missing thanks #!/bin/sh #< Magic eight ball! # KW 26/11/04 # Requires "rand" echo "Enter \"q\" followed by return to quit" function... (6 Replies)
Discussion started by: JamieMurry
6 Replies

5. Shell Programming and Scripting

sed magic!

Hi, I have this line in my script, and works perfect! tran= "$(sed = "$fname" | sed "/./N; s/\n/: /" | sed -n "${beg},${end}p")" $fname its a file, and gets multilines between beg and end.: Something like this: 1: line a 2: line b 3: line c But now, I want insert in the end of each... (9 Replies)
Discussion started by: vibra
9 Replies

6. Shell Programming and Scripting

AWK and Magic with file parsing

Hi, I have a input file like this TH2TH2867Y NOW33332106Yo You Baby TH2TH3867Y NOW33332106No Way Out TH2TH9867Y NOW33332106Can't find it TJ2TJ2872N WOW33332017sure thing alas TJ2TJ3872N WOW33332017the sky rocks TJ2TJ4872N WOW33332017nothing else matters TJ2TJ5872N WOW33332017you know... (1 Reply)
Discussion started by: devtakh
1 Replies

7. UNIX for Advanced & Expert Users

magic.h

Where can I find #defines for filetypes like the ELF etc and the magic.h file? CAn anyone advice. Thanks in advance. (1 Reply)
Discussion started by: vijlak
1 Replies

8. UNIX for Dummies Questions & Answers

magic.h

Where can I find #defines for filetypes like the ELF etc and the magic.h file? CAn anyone advice. Thanks in advance. (0 Replies)
Discussion started by: vijlak
0 Replies

9. UNIX for Advanced & Expert Users

what is magic file ?

i am working under this sysytem SunOS sparc SUNW,UltraAX-i2 Some system on this server creates a file named like this. Elem_ee.xml.gz Elem_ee.xml.gz.magic In order to look into Elem_ee.xml.gz.magic, i first renamed Elem_ee.xml.gz.magic to Elem_ee.xml.gz and tried to unzip it. but returns... (3 Replies)
Discussion started by: oppai
3 Replies

10. UNIX for Dummies Questions & Answers

Boot Magic 7

For a multi-boot setup (win2K, XP & SuSE) over 3 drives, where should Boot Magic be installed to? And does it matter which o/s installs it? (3 Replies)
Discussion started by: onestepto
3 Replies
Login or Register to Ask a Question