Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract info and do algebra on it by sed or awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract info and do algebra on it by sed or awk
# 1  
Old 02-04-2013
Extract info and do algebra on it by sed or awk
Hello everyone,
I need to extract some information from a csv file and further need to do some algebraic calculations on those information and then to throw the result in a new file.
Here is a sample from my data.csv file;
Code:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
a,3,501,0.75E+03,0.62E+02,0.27E-01,0.33E+01
b,5,502,0.58E+02,0.44E+01,0.79E+03,0.93E-01

What I need to do is the following;
If Col1 in a row has a value "a" and if Col1 in the NEXT row has value "b" calculate
Sqrt{(Col4 on 1st row + Col4 on the next row)^2 +
(Col5 on 1st row + Col5 on the next row)^2 +
(Col6 on 1st row + Col6 on the next row)^2 +
(Col7 on 1st row + Col7 on the next row)^2}
then the result should be written in a new file on a column.

Thanks a lot, let me know if you need any other information.

Any help will be highly appreciated.
# 2  
Old 02-04-2013
Code:
awk -F, 'NR>1&&$1=="a"{f=1;split($0,A,",");}f==1&&$1=="b"{ print sqrt(((a[4]+$4)^2)+((a[5]+$5)^2)+((a[6]+$6)^2)+((a[7]+$7)^2));f=0;}' filename

This User Gave Thanks to Yoda For This Post:
hayreter
# 3  
Old 02-04-2013
thanks bipi, this is very nice.

---------- Post updated at 12:53 PM ---------- Previous update was at 12:29 PM ----------

Hi bipi,
I noticed that the numbers are not coming out correct, I am trying to find out what is wrong. Could you please check the code as well. thanks.
# 4  
Old 02-04-2013
Wrong in what way?

What do you get, and what were you expecting?
# 5  
Old 02-04-2013
I think, the code does not pick the correct rows !!!
The algebraic calculation has to be done on two consecutive rows
satisfying the given conditions. what do you think?

---------- Post updated at 01:20 PM ---------- Previous update was at 01:06 PM ----------

Ok let me give you the real deal Smilie
here is a real sample from my file;
Code:
"83586",21,-1,0,0,501,502,0.00000000000E+00,0.00000000000E+00,0.40493970075E+03,0.40493970075E+03,0.00000000000E+00,0.,-1.
"83587",21,-1,0,0,502,503,0.00000000000E+00,0.00000000000E+00,-0.13690511920E+04,0.13690511920E+04,0.00000000000E+00,0.,1.
"83588",6,1,1,2,501,0,0.16602324784E+02,0.21173242262E+03,0.83772415324E+02,0.28584624109E+03,0.17200000000E+03,0.,1.
"83589",-6,1,1,2,0,503,0.21296508725E+03,-0.34724062240E+03,-0.11031327863E+04,0.11884511443E+04,0.17200000000E+03,0.,1.
"83590",25,1,1,2,0,0,-0.22956741204E+03,0.13550819978E+03,0.55248879711E+02,0.29969350735E+03,0.12530000305E+03,0.,0.
"83591",21,-1,0,0,503,502,0.00000000000E+00,0.00000000000E+00,0.23112128409E+03,0.23112128409E+03,0.00000000000E+00,0.,-1.
"83592",21,-1,0,0,501,503,0.00000000000E+00,0.00000000000E+00,-0.60107765286E+03,0.60107765286E+03,0.00000000000E+00,0.,-1.
"83593",6,1,1,2,501,0,-0.17819622689E+03,-0.96491143735E+02,-0.31619254612E+03,0.41306919798E+03,0.17200000000E+03,0.,1.
"83594",-6,1,1,2,0,502,0.15029873573E+03,-0.61138110558E+01,0.36878029061E+02,0.23145426692E+03,0.17200000000E+03,0.,1.
"83595",25,1,1,2,0,0,0.27897491154E+02,0.10260495479E+03,-0.90641851705E+02,0.18767547204E+03,0.12530000305E+03,0.,0.

according to this sample I changed the code as this;
Code:
awk -F, 'NR>1&&$2==6{f=1;split($0,A,",");}f==1&&$2==-6{ print sqrt((a[8]+$8)^2+(a[9]+$9)^2+(a[10]+$10)^2+(a[11]+$11)^2);f=0;}' filename

for this part of file, code gives 2 numbers 1671.9 and 278.493 respectively, but if you calculate yourself
the numbers come out as 710.446 and 1797.58. The numbers that I get are from rows "83588","83589" and "83593","83594"
# 6  
Old 02-04-2013
Quote:
Originally Posted by bipinajith
Code:
awk -F, 'NR>1&&$1=="a"{f=1;split($0,A,",");}f==1&&$1=="b"{ print sqrt(((a[4]+$4)^2)+((a[5]+$5)^2)+((a[6]+$6)^2)+((a[7]+$7)^2));f=0;}' filename

It looks like there might be a typo in the above command:
Try making the change shown in red below:
Code:
awk -F, 'NR>1&&$1=="a"{f=1;split($0,a,",");}f==1&&$1=="b"{ print sqrt(((a[4]+$4)^2)+((a[5]+$5)^2)+((a[6]+$6)^2)+((a[7]+$7)^2));f=0;}' filename

These 2 Users Gave Thanks to Don Cragun For This Post:
hayreter Yoda
# 7  
Old 02-04-2013
Sorry, I need to correct a number,
my calculation reveals the following numbers; 710.446 and 1812.1

---------- Post updated at 01:32 PM ---------- Previous update was at 01:30 PM ----------

That's it. Thanks Don Cragun, changing "A" to "a" worked.
now it works perfect. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with awk to extract additional info

Hi I use multipath linux command to get LUNs info and find out if any failed. # multipath -ll >/tmp/mpfail # cat /tmp/mpfail multipath.conf line 109, invalid keyword: user_friendly_names multipath.conf line 153, invalid keyword: user_friendly_names multipath.conf line 193, invalid... (4 Replies)
Discussion started by: prvnrk
4 Replies

2. Shell Programming and Scripting

Awk/sed HTML extract

I'm extracting text between table tags in HTML <th><a href="/wiki/Buick_LeSabre" title="Buick LeSabre">Buick LeSabre</a></th> using this: awk -F "</*th>" '/<\/*th>/ {print $2}' auto2 > auto3 then this (text between a href): sed -e 's/\(<*>\)//g' auto3 > auto4 How to shorten this into one... (8 Replies)
Discussion started by: p1ne
8 Replies

3. Shell Programming and Scripting

Extract a substring using SED/AWK

Hi All, I have a log file in which name and version of applications are coming in the following format name It may look like following, based on the name of the application and version: XYZ OR xyz OR XyZ OR xyz I want to separate out the name and version and store them into variables.... (4 Replies)
Discussion started by: bhaskar_m
4 Replies

4. Shell Programming and Scripting

Extract word from text (sed,awk, etc...)

Hello, I need some help extracting the number after the RBA e.g 15911688 from the below block of text (e.g: grep RBA |sed .......). The code should be valid for blocks if text generated at different times as well and not for the below text only. ... (2 Replies)
Discussion started by: drbiloukos
2 Replies

5. Shell Programming and Scripting

Using AWK BEGIN to extract file header info into variables

Hi Folks, I've searched for this for quite a while, but can't find any solution - hope someone can help. I have various files with standard headers. eg. <HEADER> IP: 1.2.3.4 Username: Joe Time: 12:00:00 Date: 23/05/2010 </HEADER> This is a test and this part can be any size... (6 Replies)
Discussion started by: damoske
6 Replies

6. UNIX for Dummies Questions & Answers

Using awk/sed to extract text between Strings

Dear Unix Gurus, I've got a data file with a few hundred lines (see truncated sample)... BEGIN_SCAN1 TASK_NAME=LA48 PDD Profiles PROGRAM=ArrayScan 1.00 21.220E+00 2.00 21.280E+00 END_DATA END_SCAN1 BEGIN_SCAN2 TASK_NAME=LA48 PDD Profiles 194.00 2.1870E+00 ... (5 Replies)
Discussion started by: tintin72
5 Replies

7. Shell Programming and Scripting

[sed/awk] find info and replace

Hi :) I have some problems with "FOR"... I have a text file in this format: name1 www.link1/random_number name2 www.link2/random_number name3 www.link3/random_number ... (Names and info changes) Now, I need: (4 Replies)
Discussion started by: aspire
4 Replies

8. Shell Programming and Scripting

how to extract info from a file using awk

Dear all I have a file call interfaces.txt Filename: interfaces.txt How can I extract the information at below? ABC_DB_001 hostname1 20901 ABC_DB_002 hostname2 20903 ABC_DB_003 hostname3 20905 Currently I am using a very stupid method grep ^ABC interfaces.txt > name.txt grep... (3 Replies)
Discussion started by: on9west
3 Replies

9. Shell Programming and Scripting

Extract some characters with SED or AWK

Hi, I have the following example string: today_is_a_good_day.txt The character "_" inside the string can sometimes be more or less. The solution for every string equal the count of "_" should be alway the rest after the last underline character. Result: day.txt I want to use awk... (5 Replies)
Discussion started by: climber
5 Replies

10. Shell Programming and Scripting

extract using sed/awk - need help? Please!!

Need help..not sure how to use with awk or sed I want to take data from the notification.$$ file and assign the data to variable "group". Not sure how to do it. The data I want to extract from the notification.$$ is on the first line of the file ..right after the (notice): NetWorker... (5 Replies)
Discussion started by: gzs553
5 Replies
Login or Register to Ask a Question