adding calculated column

Shell Programming and Scripting
1

Hi,
My text file looks like this...
I am not sure if it is tab separated or space separated.

SHANTNU 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55
test 342 12:53:00 AM 01:09:00 PM 144:43:00 07:44:00 AM 47 12:05:00 AM 470:51 1:21
new test 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52
john 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01
akbar 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 484:28 6:05

I have a text file where I want to select the second last column values (296, 470, 484) and devide them by 60, display the result in the next column and then devide it by second column (101, 342, 336).
The results will look like this....

SHANTNU 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55 4 25
test 342 12:53:00 AM 01:09:00 PM 144:43:00 07:44:00 AM 47 12:05:00 AM 470:51 1:21 7 48
new test 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52 8 42
john 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01 8 25
akbar 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 584:28 6:05 9 39

2

try this.. but your second column should contain no :stuck_out_tongue:

awk -F"[ :]+" '{print $0" "$(NF-3)/60" "($2/($(NF-3)/60))}' filename
3

Thanks for the code.
1) But it is replacing my first names column.
2) I want only the integer value after the division. So instead of 4.9333 and 7.8333, I want 4, 7
3) The second calculated column ($2/($(NF-3)/60))} seems to wrong. For e.g. for the first row, it should be 20 and not 0 (101/(296/60)) for the second row, it should be 43 and not 0 (342/(470/60))
4) I get division by zero error if the column has 0:00. Is there any way to avoid this?

# awk -F"[ :]+" '{print $0" "$(NF-3)/60" "($2/($(NF-3)/60))}' myathar.txt
4.93333 0K 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55
7.83333 0A 342 12:53:00 AM 01:09:00 PM 144:43:00 07:44:00 AM 47 12:05:00 AM 470:51 1:21
8.06667 0I 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52
8.13333 25.3279 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01
8.06667 0K 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 484:28 6:05
8.15 0 Pat 234 01:32:00 AM 08:43:00 AM 99:16:00 25:31:00 27 12:04:00 AM 489:05 1:25
8.61667 0T 372 12:55:00 AM 12:45:00 PM 136:39:00 30:04:00 60 12:08:00 AM 517:34 9:07
0.15 0Koth 4 01:41:00 AM 12:08:00 AM 02:14:00 AM 12:28:00 AM 1 12:00:00 AM 9:33 0:00
8.05 0l Te 347 12:56:00 AM 11:52:00 AM 140:34:00 06:47:00 AM 56 12:05:00 AM 483:19 4:59
awk: (FILENAME=myathar.txt FNR=10) fatal: division by zero attempted

4

i think this is what you want

 
fnsonlu1-/home/fnsonlu1> cat vv
SHANTNU 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55
test 342 12:53:00 AM 01:09:00 PM 144:43:00 07:44:00 AM 47 12:05:00 AM 470:51 1:21
new 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52
john 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01
akbar 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 484:28 6:05
fnsonlu1-/home/fnsonlu1> awk -F"[ :]+" 'var=$0,(var1=$(NF-3)/60)&&var2=($2/var1){printf "%s %d %d\n",var,var1,var2}' vv
SHANTNU 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55 4 20
test 342 12:53:00 AM 01:09:00 PM 144:43:00 07:44:00 AM 47 12:05:00 AM 470:51 1:21 7 43
new 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52 8 41
john 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01 8 25
akbar 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 484:28 6:05 8 44
5

1) The calculation is wrong:
The second row "test" should be 48 instead of 43 (342/7)
the third row "new" should be 42 instead of 41 (336/8)

2) My file is not ASCII text encoded. I guess that is why I get the garbage (repeated rows) output as shown below:
4 0SH ADEK 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55
7 0SH ADEK 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55
8 0SH ADEK 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55

# file newathar.txt
newathar.txt: Par archive data

# file vv
vv: ASCII text

6 
#!/usr/bin/perl
open $fh,"<","a.txt";
while(<$fh>){
	chomp;
	if(/^[^\d]*(\d+).* (\d+):\d+ \d+:\d+$/){
		my $tmp=int $2/60;
		print $_,"   ",$tmp,"  ",int $1/$tmp,"\n";
	}
}
7

Another issue that I found is that if I have surname as well as name, for e.g. SHANTANU OAK instead of SHANTANU, I get wrong results as shown below:

# awk -F"[ :]+" 'var=$0,(var1=$(NF-3)/60)&&var2=($2/var1){printf "%s %d %d\n",var,var1,var2}' vv
SHANTNU OAK 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55 4 0
SHANTNU OAK 101 12:40:00 AM 04:53:00 AM 219:40:00 04:23:00 AM 10 12:01:00 AM 296:18 1:55 7 43
new 336 12:57:00 AM 02:06:00 PM 128:02:00 27:23:00 60 12:13:00 AM 484:04 6:52 8 41
john 206 01:50:00 AM 07:14:00 AM 99:11:00 04:34:00 AM 22 12:06:00 AM 488:32 2:01 8 25
akbar 356 12:51:00 AM 12:40:00 PM 152:29:00 04:23:00 PM 50 12:07:00 AM 484:28 6:05 8 44

I tried the perl solution as well. I saved the code in a .pl file and tried to execute it.
/usr/bin/perl mycal.pl

I do not get any output. Am I doing it correct?

8

I wrote a shell script using the command mentioned by Vidyadhar.
Thanks a lot to both of you.