Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Calculate 2nd Column Based on 1st Column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculate 2nd Column Based on 1st Column
# 1  
Old 11-22-2012
Calculate 2nd Column Based on 1st Column
Dear All,

I have input file like this.

input.txt

Code:
CE2_12-15          3950.00  589221.0 9849709.0   768.0   CE2_12_2012
CE2_12-15          3949.00  589199.0 9849721.0   768.0   CE2_12_2012
CE2_12-15          3948.00  589178.0 9849734.0   768.0   CE2_12_2012
CE2_12-52          1157.00  570361.0 9845167.0   850.9   CE2_12_2012
CE2_12-52          1158.00  570376.0 9845187.0   855.1   CE2_12_2012
CE2_12-52          1159.00  570391.0 9845207.0   859.3   CE2_12_2012
CE2_12-54          2396.00  579290.0 9854101.0   853.0   CE2_12_2012
CE2_12-54          2397.00  579305.0 9854121.0   855.0   CE2_12_2012
CE2_12-54          2398.00  579321.0 9854140.0   857.5   CE2_12_2012
....
....
....

I need to calculate 2nd column of input with different function, based on 1st column.
if colum 1st contain CE2_12-15 do (4086-$1)*2
if colum 1st contain CE2_12-52 do ($1-836)*2
if colum 1st contain CE2_12-54 do ($1-1088)*2
...
...


desired output:

output.txt

Code:
CE2_12-15           272.00  589221.0 9849709.0   768.0   CE2_12_2012
CE2_12-15           274.00  589199.0 9849721.0   768.0   CE2_12_2012
CE2_12-15           276.00  589178.0 9849734.0   768.0   CE2_12_2012
CE2_12-52           642.00  570361.0 9845167.0   850.9   CE2_12_2012
CE2_12-52           644.00  570376.0 9845187.0   855.1   CE2_12_2012
CE2_12-52           646.00  570391.0 9845207.0   859.3   CE2_12_2012
CE2_12-54          2616.00  579290.0 9854101.0   853.0   CE2_12_2012
CE2_12-54          2618.00  579305.0 9854121.0   855.0   CE2_12_2012
CE2_12-54          2620.00  579321.0 9854140.0   857.5   CE2_12_2012
....
....
....

I tried coding like this, but unsucesfull :

Code:
awk '{
if ( $1 =="CE2_12-15")
        print ((4086-$1)*2)
else if ($1 =="CE2_12-52")
    print (($1-846)*2)
else if ($1 =="CE2_12-54")
    print (($1-1088)*2)
}' input.txt > output.txt

Thanks for advance,

Attila
# 2  
Old 11-22-2012
Try

Code:
awk '$1=="CE2_12-15"{$2=(4086-$2)*2}
$1=="CE2_12-52"{$2=($2-836)*2}
$1=="CE2_12-54"{$2=($2-1088)*2}1' file

This User Gave Thanks to pamu For This Post:
attila
# 3  
Old 11-22-2012
Hi,

can you try with $2 instead of $1 in all the print functions-- print ((4086-$2)*2)
# 4  
Old 11-22-2012
Thanks pamu & msathees,

Solved
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. Shell Programming and Scripting

Calculate 5th percentile based on another column

I would like to have some help in calculating 5th percentile value of column 2 for each site, the input is like below:site val1 val2 002 10 25.3 002 20 25.3 002 30 25.3 002 40 20 002 50 20 002 60 20 002 70 20 002 80 30 002 90 30 002 100 30 002 120 30 003 20 30.3 003 20 30.3 003 30 20... (2 Replies)
Discussion started by: wuhuai
2 Replies

3. Linux

Print the 1st column and the value in 2nd or 3rd column if that is different from the values in 1st

I have file that looks like this, DIP-17571N|refseq:NP_651151 DIP-17460N|refseq:NP_511165|uniprotkb:P45890 DIP-17571N|refseq:NP_651151 DIP-19241N|refseq:NP_524261 DIP-19241N|refseq:NP_524261 DIP-17151N|refseq:NP_524316|uniprotkb:O16797 DIP-19588N|refseq:NP_731165 ... (2 Replies)
Discussion started by: Syeda Sumayya
2 Replies

4. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

5. Shell Programming and Scripting

Calculate the average of a column based on the value of another column

Hi, I would like to calculate the average of column 'y' based on the value of column 'pos'. For example, here is file1 id pos y c 11 1 220 aa 11 4333 207 f 11 5333 112 ee 11 11116 305 e 11 11117 310 r 11 22228 781 gg 11 ... (2 Replies)
Discussion started by: jackken007
2 Replies

6. Shell Programming and Scripting

Calculate difference in timestamps based on unique column value

Hi Friends, Require a quick help to write the difference between 2 timestamps based on a unique column value: Input file: 08/23/2012 12:36:09,JOB_5340,08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350,08/23/2012 12:36:26,JOB_5350 08/23/2012 13:08:51,JOB_5360,08/23/2012... (4 Replies)
Discussion started by: asnandhakumar
4 Replies

7. Shell Programming and Scripting

Transpose timestamp based on column values and calculate time difference

Hello Expert, I need to transpose Date-Timestamp based on same column values and calculate time difference. The input file would be as below and required output is mentioned in the bottom INPUT File ======== 08/23/2012 12:36:09 JOB_5340 08/23/2012 12:36:14 JOB_5340 08/23/2012... (2 Replies)
Discussion started by: asnandhakumar
2 Replies

8. Shell Programming and Scripting

1st column,2nd column on first line 3rd,4th on second line ect...

I need to take one column of data and put it into the following format: 1st line,2nd line 3rd line,4th line 5th line,6th line ... Thanks! (6 Replies)
Discussion started by: batcho
6 Replies

9. Shell Programming and Scripting

comparing column of two different files and print the column from in order of 2nd file

Hi friends, My file is like: Second file is : I need to print the rows present in file one, but in order present in second file....I used while read gh;do awk ' $1=="' $gh'" {print >> FILENAME"output"} ' cat listoffirstfile done < secondfile but the output I am... (14 Replies)
Discussion started by: CAch
14 Replies

10. Shell Programming and Scripting

split the file based on the 2nd column passing as a parameter

I am unable to spit the file based on the 2nd column passing as a parameter with awk command. Source file: “100”,”customer information”,”10000” “200”,”customer information”,”50000” “300”,”product information”,”40000” script: the command is not allowing to pass the parameters with the awk... (7 Replies)
Discussion started by: number10
7 Replies
Login or Register to Ask a Question