The UNIX and Linux Forums  
Hello and Welcome from United States to the UNIX and Linux Forums! Thank You for Visiting and Joining Our Global Community.

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
adding calculated column adding calculated column
.
google unix.com

Forums Register Forum RulesLinksAlbums FAQ Members List Calendar Search Today's Posts Mark Forums Read


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Adding a new column in a text file snahata Shell Programming and Scripting 10 03-13-2009 07:00 AM
Adding a date as a first column figaro UNIX for Dummies Questions & Answers 2 05-21-2008 05:00 PM
FILE:Adding new column sandeep_hi Shell Programming and Scripting 2 06-09-2006 09:46 AM
Adding a column of numbers Khoomfire UNIX for Advanced & Expert Users 1 01-18-2006 04:55 AM
adding a column at the end of the record pavan_test UNIX for Dummies Questions & Answers 2 11-07-2005 11:39 AM

Closed Thread
Page 1 of 2 1 2
English Japanese Spanish French German Portuguese Italian Dutch Swedish Russian Norwegian Hungarian Hebrew Danish Powered by Powered by Google
 
LinkBack Thread Tools Search this Thread Rate Thread Display Modes
  #1 (permalink)  
Old 03-16-2009
shantanuo shantanuo is offline
Registered User
  
 

Join Date: Aug 2008
Location: Mumbai
Posts: 79
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 (permalink)  
Old 03-16-2009
vidyadhar85's Avatar
vidyadhar85 vidyadhar85 is offline Forum Staff  
Moderator(The Tutor)
  
 

Join Date: Jun 2008
Location: INDIA
Posts: 1,390
try this.. but your second column should contain no
Code:
awk -F"[ :]+" '{print $0" "$(NF-3)/60" "($2/($(NF-3)/60))}' filename
  #3 (permalink)  
Old 03-17-2009
shantanuo shantanuo is offline
Registered User
  
 

Join Date: Aug 2008
Location: Mumbai
Posts: 79
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 12:18 AM.. Reason: corrected typing mistake
  #4 (permalink)  
Old 03-17-2009
vidyadhar85's Avatar
vidyadhar85 vidyadhar85 is offline Forum Staff  
Moderator(The Tutor)
  
 

Join Date: Jun 2008
Location: INDIA
Posts: 1,390
Quote:
Originally Posted by shantanuo View Post
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 (permalink)  
Old 03-17-2009
shantanuo shantanuo is offline
Registered User
  
 

Join Date: Aug 2008
Location: Mumbai
Posts: 79
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 (permalink)  
Old 03-17-2009
summer_cherry summer_cherry is offline Forum Advisor  
Registered User
  
 

Join Date: Jun 2007
Location: Beijing China
Posts: 1,078
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 (permalink)  
Old 03-19-2009
shantanuo shantanuo is offline
Registered User
  
 

Join Date: Aug 2008
Location: Mumbai
Posts: 79
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?
Closed Thread
Page 1 of 2 1 2

Bookmarks

« Compare 2 folders... | What does the following command do? »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Hybrid Mode Hybrid Mode
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -4. The time now is 02:52 PM.

The UNIX and Linux Forums - Learn UNIX and UNIX Commands RSS Feeds - Contact Us - The UNIX and Linux Forums - Learn UNIX and UNIX Commands - Archive - Top

Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited. Language Translations Powered by .
vBCredits v1.4 Copyright ©2007 - 2008, PixelFX Studios
The UNIX and Linux Forums Content Copyright ©1993-2009. All Rights Reserved.Ad Management by RedTyger

Content Relevant URLs by vBSEO 3.2.0