Adding columns with values dependent on existing columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding columns with values dependent on existing columns
# 1  
Old 02-03-2013
Adding columns with values dependent on existing columns

Hello


I have a file as below
Code:
chr1    start    ref    alt        code1    code2
chr1    18884    C    CAAAA    2    0
chr1    135419    TATACA    T    2    0
chr1    332045    T    TTG    0    2
chr1    453838    T    TAC    2    0
chr1    567652    T    TG    1    0
chr1    602541    TTTA    T    2    0

on which I want to add new columns such that:
if ref is a string >1 (i.e line 2) then I generate 2 new columns where:

first new column = start-1
second new column = start+(length of string in ref)+1

therefore, for line 2 output would look like:
chr1 135419 TATACA T 2 0 135418 135426

or:
if length of string in ref = 1 and alt=string of length>1 (i.e. line 1) then

first new column = start
second new column = start+2

so, output for line 1 would be:
chr1 18884 C CAAAA 2 0 18884 18886

Anybody able to help with an approach to doing this?
Would awk be the best way to do this? My perl is non-existent otherwise I imagine that would be a good way to do it
# 2  
Old 02-03-2013
Try this code:
Code:
awk 'NR==1 {
        print;
  } NR>1 {
        if((length($3)==1)&&(length($4)>1)) {
                print;
                print $1,$2,$3,$4,$2,$2+2;
        }
        else if((length($3)>1)) {
                print;
                print $1,$2,$3,$4,$2-1,$2+length($3)+1;
        }
        else
                print;
}' OFS='\t' filename

# 3  
Old 02-05-2013
perl or python

How about below perl code?

Code:
while(<DATA>){
    chomp;
	s/(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)/
	  (length($3)>1)?$_." ".($2-1)." ".($2+length($3)+1):$_." ".$2." ".($2+2)
	/ex;
	print $_,"\n";
}
__DATA__
chr1    18884    C    CAAAA    2    0
chr1    135419    TATACA    T    2    0
chr1    332045    T    TTG    0    2
chr1    453838    T    TAC    2    0
chr1    567652    T    TG    1    0
chr1    602541    TTTA    T    2    0

or below python code:
Code:
with open("a.txt","r") as f:
 for line in f:
  line=line.replace("\n","")
  words=line.split(" ")
  if len(words[2])==1:
   words.append(words[1])
   words.append(str(int(words[1])+2))
  else:
   words.append(str(int(words[1])-1))
   words.append(str(int(words[1])+len(words[2])+1))
  print(" ".join(words))


Last edited by summer_cherry; 02-05-2013 at 03:56 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Adding columns from 2 files with variable number of columns

I have two files, file1 and file2 who have identical number of rows and columns. However, the script is supposed to be used for for different files and I cannot know the format in advance. Also, the number of columns changes within the file, some rows have more and some less columns (they are... (13 Replies)
Discussion started by: maya3
13 Replies

2. Shell Programming and Scripting

Add new columns based on existing columns

Hi all, I am kind of stuck with printing my desired output. Please help me if you know how it can work. My input file(tab separated): NW_0068.1 41,16 100,900 NW_0699.1 4,2,19 200,700,80 My Output file (desired): NW_0068.1 41,16 100,900 100 - 141 NW_0068.1 41,16 100,900 ... (3 Replies)
Discussion started by: sam_2921
3 Replies

3. Shell Programming and Scripting

Help with ask adding columns

Hello, I am using AWK in UBUNTU 12.04. I have a dataset as follows: 1 2 12 1 4 1 4 1 7 9 4 6 1 2 4 5 7 8 45 7 4 5 7 5 What I want to do is to add the values of some columns to each other and print it in the same file as the new column while omitting the previous two columns to have... (3 Replies)
Discussion started by: Homa
3 Replies

4. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

5. Shell Programming and Scripting

Get values from different columns from file2 when match values of file1

Hi everyone, I have file1 and file2 comma separated both. file1 is: Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10 Code7,,,,,,,,, Code5,,,,,,,,, Code3,,,,,,,,, Code9,,,,,,,,, Code2,,,,,,,,,file2... (17 Replies)
Discussion started by: cgkmal
17 Replies

6. UNIX for Dummies Questions & Answers

Add columns to an existing excel sheet

Hi All, i have an excel sheet as below: day ----- monday tuesday wenesday thursday friday i need to append the two more columns in this existing file below: insert date should be todays date--- day insert date insert user ---- ---------- ... (3 Replies)
Discussion started by: arunmanas
3 Replies

7. Shell Programming and Scripting

Adding a new column in a file with other existing columns

Hi All , Kindly help me with this soln awk '{printf "%s %7s \n", $1,$c}' infile where value of variable c I am externally giving input But executing the above command shows all the columns of infile where as I want only 1st column of infile and 2nd column should print value c (8 Replies)
Discussion started by: Pratik4891
8 Replies

8. Shell Programming and Scripting

adding columns

Hey everyone! I have a need to add 2 files together as columns. For instance, I have one file that has several rows of data and I want to take data from another file and add Line 1 to the end of Line1 in the first file file1 line1.........file2 line1 file1 line2.........file2 line2... (12 Replies)
Discussion started by: Kelam_Magnus
12 Replies

9. Shell Programming and Scripting

Need Help for Adding Three new columns in existing file from fatching data from file

not required this time (36 Replies)
Discussion started by: Sandeep_Malik
36 Replies

10. UNIX for Dummies Questions & Answers

Adding columns to a file

I want to select the first column from a daily file called foo.csv. The result is written to file foo.txt. Currently the following script is used for that: cut -d, -f 1 foo.csv > foo.txt A typical result would yield : A12 A45 B11 B67 What needs to happen in addition is that two columns... (5 Replies)
Discussion started by: figaro
5 Replies
Login or Register to Ask a Question

Featured Tech Videos