Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

adding calculated column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting adding calculated column
# 1  
Old 03-16-2009
adding calculated column
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  
Old 03-17-2009
try this.. but your second column should contain no Smilie
Code:
awk -F"[ :]+" '{print $0" "$(NF-3)/60" "($2/($(NF-3)/60))}' filename

# 3  
Old 03-17-2009
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
Last edited by shantanuo; 03-17-2009 at 01:18 AM.. Reason: corrected typing mistake
# 4  
Old 03-17-2009
Quote:
Originally Posted by shantanuo
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
i think this is what you want
Code:
 
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  
Old 03-17-2009
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  
Old 03-17-2009
Code:
#!/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  
Old 03-19-2009
minor glitch
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?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Adding a column

Hello , My requirement is to add additional column sequentially to a text file based on the column value - i/p file id1|varchar id2|varchar id3|number id4|number id5|date id6|date --------------------------------------- o/p file colv1|id1 (if second column value is varchar then... (3 Replies)
Discussion started by: Pratik4891
3 Replies

2. Shell Programming and Scripting

Adding column

I have input CSV files as TS DS_WKLDNM InterfaceSpeed 29/07/2014 20:00:00 xxx112/1/18 10000000000 29/07/2014 09:00:00 xxx112/1/19 10000000000 29/07/2014 21:00:00 xxx112/1/2 10000000000 29/07/2014 20:00:00 xxx112/1/20 10000000000 29/07/2014... (10 Replies)
Discussion started by: Moon1234
10 Replies

3. Shell Programming and Scripting

Adding values of a column based on another column

Hello, I have a data such as this: ENSGALG00000000189 329 G A 4 2 0 ENSGALG00000000189 518 T C 5 1 0 ENSGALG00000000189 1104 G A 5 1 0 ENSGALG00000000187 3687 G T 5 1 0 ENSGALG00000000187 4533 A T 4 2 0 ENSGALG00000000233 5811 T C 4 2 0 ENSGALG00000000233 5998 C A 5 1 0 I want to... (3 Replies)
Discussion started by: Homa
3 Replies

4. UNIX for Dummies Questions & Answers

Adding Column Of Numbers

Hello. Trying to add a column of numbers and combine the 1st and 2nd fields as uniq with the new total. This works to add the numbers but can't figure an easy was to combine the 1st and 2nd column as the list is very long. awk '{s+=$3} END {print s}' bird dog 300 bird dog 100 cat clown 200... (1 Reply)
Discussion started by: jimmyf
1 Replies

5. UNIX for Dummies Questions & Answers

Adding column with values

Dear all, I need your help for my question please I have without header (space separated) and need to add two colomns at the beginning with values my file look like : rs1 a t 0.6 rs2 a c 0.3 rs3 t g 0.8 I need to a new file like: 1 100 rs1 a t 0.6 1 100 rs2 a c 0.3 1 100 rs3 t g... (3 Replies)
Discussion started by: biopsy
3 Replies

6. Shell Programming and Scripting

Adding column in file

File contains 2 fields with tab delimeter. i want added the one column first of the file and incrementing as like sequence Sample file: Fname lanme raj rajkumar rani ranikumar output file should be. name Fname lanme 1 raj rajkumar 2 rani ranikumar Please help... (1 Reply)
Discussion started by: bmk
1 Replies

7. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

8. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

9. Shell Programming and Scripting

Adding column

Hello, I'm struggling with the following problem in sh script: Adding a column to the right-end of a file ("master-file": non-constant column number, tab and linux formatted) where the column is the 4th one of file1 (space and DOS formatted) Changing the header of column 4 of file 1 at the... (4 Replies)
Discussion started by: lco
4 Replies

10. UNIX for Dummies Questions & Answers

adding a column

Hi everybody, I've got a file with 36074 fields. I need to insert an additional new columns 2,3,4 and 5 after the first field. The columns to insert are the same for all the lines: it's "1" doe the new field 2 and "0" for the rest. I did have a look in the forum and the suggested solution I... (7 Replies)
Discussion started by: zajtat
7 Replies
Login or Register to Ask a Question