Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to add letters to a column and add in a new column subtracting from another column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to add letters to a column and add in a new column subtracting from another column
# 1  
Old 01-27-2011
Need to add letters to a column and add in a new column subtracting from another column
So I have this input
Code:
1    10327    rs112750067    T    C    .    PASS    DP=65;AF=0.208;CB=BC,NCBI
1    10469    rs117577454    C    G    .    PASS    DP=2055;AF=0.020;CB=UM,BC,NCBI
1    10492    rs55998931    C    T    .    PASS    DP=231;AF=0.167;CB=BC,NCBI
1    10583    rs58108140    G    A    .    PASS    DP=1557;AF=0.162;CB=UM,BI,BC,NCBI
1    11508    .    A    G    .    PASS    DP=23;AF=0.720;CB=BI,BC
1    11565    .    G    T    .    PASS    DP=45;AF=0.000;CB=BI,BC
1    12783    .    G    A    .    PASS    DP=4010;AF=0.56

And I want this output - basically add "chr" to the first column, then have a new 2nd column where the data is -1 from column 3. i.e.
Code:
chr1   10326    10327    rs112750067    T    C    .    PASS    DP=65;AF=0.208;CB=BC,NCBI
chr1   10468    10469    rs117577454    C    G    .    PASS    DP=2055;AF=0.020;CB=UM,BC,NCBI
chr1   10491    10492    rs55998931    C    T    .    PASS    DP=231;AF=0.167;CB=BC,NCBI
chr1   10582    10583    rs58108140    G    A    .    PASS    DP=1557;AF=0.162;CB=UM,BI,BC,NCBI
chr1   11507    11508    .    A    G    .    PASS    DP=23;AF=0.720;CB=BI,BC
chr1   11564    11565    .    G    T    .    PASS    DP=45;AF=0.000;CB=BI,BC
chr1   12782    12783    .    G    A    .    PASS    DP=4010;AF=0.56Please help!

Thank you

P.S. This file is pretty massive (25,000,000 lines)


Moderator's Comments:
Mod Comment Please use code tags when posting data and code samples!
Last edited by Franklin52; 01-28-2011 at 03:23 AM..
# 2  
Old 01-27-2011
Add col and letters
Code:
cat $1 |awk '{
   c1="chr" $1
   c2=$2-1
   c3=$2
   cut_lng=(length($1 $2)+3)
   ln_l=length($0)
   c4=(substr($0,cut_lng,ln_l-cut_lng))
   printf("%s %s %s %s\n",c1,c2,c3,c4)
}'

kellywilliams - Sorry My first code did not retain the input format I.E. spacing between the characters / fields in the output.
The new code below should resolve the output problem and retain the input spacing format.
Code:
cat $1 |awk '{
   c1="chr" $1
   c2="    " $2-1
   c3="    " $2
   z=(sprintf("%s    %s\n",$1,$2))
   cut_lng=(length(z))
   ln_l=length($0)
   c4=(substr($0,cut_lng,ln_l-cut_lng))
   printf("%s %s %s %s\n",c1,c2,c3,c4)
}'

Moderator's Comments:
Mod Comment Please use code tags when posting data and code samples, thank you.
Last edited by aix-guy; 01-28-2011 at 10:09 AM.. Reason: Output format did not retain
This User Gave Thanks to aix-guy For This Post:
kellywilliams
# 3  
Old 01-27-2011
Code:
awk '{$1="chr"$1; $2=($2-1)FS$2; print}' infile

This User Gave Thanks to Chubler_XL For This Post:
kellywilliams
# 4  
Old 01-27-2011
Code:
awk '{$1="chr" $1 FS $2-1}1' infile

This User Gave Thanks to rdcwayx For This Post:
kellywilliams
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to add a column of another data from another column?

Hola Como hacer: C:\System\SystemRun.4gl C:\System\SystemRunPrint.4gl C:\System\SystemViews.4gl Resultado: SystemRun.4gl C:\System\SystemRun.4gl SystemRunPrint.4gl C:\System\SystemRunPrint.4gl SystemViews.4gl C:\System\SystemViews.4gl... (0 Replies)
Discussion started by: tlaloc
0 Replies

2. UNIX for Beginners Questions & Answers

Add column and multiply its result to all elements of another column

Input file is as follows: 1 | 6 2 | 7 3 | 8 4 | 9 5 | 10 Output reuired (sum of the first column $1*$2) 1 | 6 | 90 2 | 7 | 105 3 | 8 | 120 4 |9 | 135 5 |10 | 150 Please enclose sample input, sample output, and code... (5 Replies)
Discussion started by: Sagar Singh
5 Replies

3. Shell Programming and Scripting

Add Column base on other Column Data

HI Guys, I want add one extra Column base on 3rd Column . Input :- M204 MS204_154 :vsDataUeMe M204 MS204_154 es:sMeasure 0 M204 MS204_154 es:90ilterCoe 9 M204 MS204_154 es:searchE9090ortTime 40 M204 MS204_154 es:servOrPrioI90HoTimer 4000 M204 MS204_154 es:ueMeajllls154545 TRUE... (5 Replies)
Discussion started by: pareshkp
5 Replies

4. Shell Programming and Scripting

Add column base on same name of second column

HI Guys, I have input file A.txt 1 AAA 1 BBB 1 1SW 1 2SW 1 3SW 1 BBB 1 PPP 1 1SW 1 PPP 1 1WS 1 1AS 1 P1P 1 AAA (1 Reply)
Discussion started by: pareshkp
1 Replies

5. Shell Programming and Scripting

Add all 4 column entries for similar column ids

Hi, I want to write a script which will add the entries in all columns for the same column id. I can do it in excel, but I need to do this for 384 columns which will come down to 96 (384/4). How can I do this iteratively A A A A B B B B C C C C 1 0 1 0 2 1 4 5 3 4 5 6 2 0 0 2 3 5 70 100 1... (7 Replies)
Discussion started by: Diya123
7 Replies

6. Shell Programming and Scripting

Add a character C in a column if that column is blank

I have some files that look as follows. I need to add a character 'C' in the fifth column if that column is blank. I prefer in-place editing. 1 1 B M 0 0 203 0, 0.0 0, 0.0 0, 0.0 0, 0.0 0.000 360.0 360.0 360.0 141.9 15.4 28.8 66.1 2 2 B A ... (21 Replies)
Discussion started by: thejitha
21 Replies

7. Shell Programming and Scripting

Add extra column if no column are less

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 File A.txt 11 LAL01A_1 1213 12 0 0 7 30 2 -122 1 300 14854 5000 2 221 LAL01A_1 1313 14 0 0 7 30 1 -122 2 300 14854 5000 2 12 LAL01A_1 1234 15 0 0 7 30 0 -122 1 300 14854 5000 2 I have file A.txt now i want to use if condition : if thr... (3 Replies)
Discussion started by: pareshkp
3 Replies

8. Shell Programming and Scripting

ADD Column Btw two column

I want to add column btw to column Input ABCDE2012 120 21.6 ABCDE2012 126 0.9 PLKJHN2012 128 20.2 UNHYT2012 1210 -0.3 Output// Column 1 Column 2 Cl 3 Cl4 ABCDE2012 ABCDE2012120 120 21.6 ABCDE2012 ABCDE2012126 126 0.9 PLKJHN2012 PLKJHN2012128 128 20.2... (3 Replies)
Discussion started by: pareshkp
3 Replies

9. Shell Programming and Scripting

to add special tag to a column based on column condition

Hi All, I have following html code <TR><TD>9</TD><TD>AR_TVR_TBS </TD><TD>85000</TD><TD>39938</TD><TD>54212</TD><TD>46</TD></TR> <TR><TD>10</TD><TD>ASCV_SMY_TBS </TD><TD>69880</TD><TD>33316</TD><TD>45698</TD><TD>47</TD></TR> <TR><TD>11</TD><TD>ARC_TBS ... (9 Replies)
Discussion started by: ckwan
9 Replies

10. Shell Programming and Scripting

Parse 1 column and add 2nd column

I'm racking my brain on this one! :( I have a list like this: Paul 20 Paul 25 Paul 30 Frank 10 Julie 15 Julie 13 etc, etc... I've been trying to figure out a way to have the output display the name in the first column ONCE and add the numbers in the second column and display that... (2 Replies)
Discussion started by: sdlennon
2 Replies
Login or Register to Ask a Question