Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

combine the values from the first two columns within a file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers combine the values from the first two columns within a file
# 1  
Old 06-15-2011
combine the values from the first two columns within a file
Hello everybody,

I have a text file containing 10,000 rows and 5000 columns. The values are separated by a tab.
Ex.

file_ex.ped
Code:
1 mike 0 0 2 1 A A G G C T A G 
1 jack 0 0 2 2 T A G T C A A C
1 Mary 0 0 1 2 A T G C A T G C
...

I would like a out put file
Code:
1 mike 0 0 2 1 AA GG CT AG
1 jack 0 0 2 2 TA GT CA AC
1 Mary 0 0 1 2 AT GC AT GC 
...

So, I want to merge 7th and 8th into 7th.... and 9th and 10th column to 8th column...and so on.

I am using cygwin to run UNIX or perl commands. Your valuable inputs are highly appreciated!

Thanks a lot
Last edited by Franklin52; 06-16-2011 at 04:47 AM.. Reason: Please use code tags
# 2  
Old 06-15-2011
Try this. Here the output also will tab separated. Change \t to any other delimiter if you dont want tab separated output.
Code:
 
perl -lane 'BEGIN{$"="\t"}for($i=6;$i<=$#F;$i+=2){$arr[$i-6]=$F[$i].$F[$i+1];};print "@F[0..5]\t@arr\n"' input

This User Gave Thanks to getmmg For This Post:
Unilearn
# 3  
Old 06-15-2011
You can try this too (might be faster?Smilie)
Code:
perl -pe '/((\w+\t){6})(.*\n)/;$x=$1;$y=$3;$y=~s/(\w+)\t(\w+)(\t|$)/\1\2\3/g;$_="$x$y"' file

These 2 Users Gave Thanks to bartus11 For This Post:
getmmg Unilearn
# 4  
Old 06-16-2011
Hi getmmg,

Thanks for the code. It works fine. But after merging the 7th and 8th columns, it leaves the 8th, 10th, 12th etc. columns empty. For ex.

output:
Code:
1 mike 0 0 2 1 AA__GG__CT__AG
1 jack 0 0 2 2 TA__GT__CA__ AC
1 Mary 0 0 1 2 AT__GC __AT__GC

would it be possible to delete all these empty columns once and get the output like,
Code:
1 mike 0 0 2 1 AA GG CT AG
1 jack 0 0 2 2 TA GT CA AC
1 Mary 0 0 1 2 AT GC AT GC

That would be great!

Hi bartus11,
thanks for the code. But for some reason it is doing nothing. I get the empty output! any hints on that!
Thanks
Last edited by Franklin52; 06-16-2011 at 04:48 AM.. Reason: Please use code tags
# 5  
Old 06-16-2011
Quote:
Originally Posted by bartus11
You can try this too (might be faster?Smilie)
Code:
perl -pe '/((\w+\t){6})(.*\n)/;$x=$1;$y=$3;$y=~s/(\w+)\t(\w+)(\t|$)/\1\2\3/g;$_="$x$y"' file

Indeed. Nice way to do it.

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

Quote:
Originally Posted by Unilearn
Hi getmmg,

Thanks for the code. It works fine. But after merging the 7th and 8th columns, it leaves the 8th, 10th, 12th etc. columns empty. For ex.

output:
1 mike 0 0 2 1 AA__GG__CT__AG
1 jack 0 0 2 2 TA__GT__CA__ AC
1 Mary 0 0 1 2 AT__GC __AT__GC

would it be possible to delete all these empty columns once and get the output like,
1 mike 0 0 2 1 AA GG CT AG
1 jack 0 0 2 2 TA GT CA AC
1 Mary 0 0 1 2 AT GC AT GC

That would be great!

Hi bartus11,
thanks for the code. But for some reason it is doing nothing. I get the empty output! any hints on that!
Thanks

This should fix that.

Code:
 
perl -lane 'BEGIN{$"="\t"}for($i=6,$j=6;$i<=$#F;$i+=2,$j++){$arr[$i-$j]=$F[$i].$F[$i+1];};print "@F[0..5]\t@arr"' input

This User Gave Thanks to getmmg For This Post:
Unilearn
# 6  
Old 06-16-2011
Computer
Wow that works great getmmg!
Thanks a lot! Now only if I know how does it work!
# 7  
Old 06-16-2011
Unilearn,

Anyways i dont have work now, so here it goes Smilie

Code:
-lane

reads the file line by line, -a splits the line and store array @F.
Read perldoc to get more on this.

<CODE>$"="\t"</CODE> sets output field seprator as tab

Col7,Col8 ... ColN-1,ColN gets merged in this part and gets stored in array @arr

Code:
 
for($i=6,$j=6;$i<=$#F;$i+=2,$j++){
$arr[$i-$j]=$F[$i].$F[$i+1];
}

Code:
print "@F[0..5]\t@arr"

This prints the required output. First six cols from @F, remaining cols from @arr.
This User Gave Thanks to getmmg For This Post:
Unilearn
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Add values to file in 2 new columns

Columns 4 and 5 are X and Y coordinates, column 6 is the elevation I would like to add 2 new columns at the end of the file with values the distance between first(X)(Y) and last location (X)(Y), based in 2 rows the difference in elevation = ($6-prev6) How to calculate the requested values... (6 Replies)
Discussion started by: jiam912
6 Replies

2. UNIX for Beginners Questions & Answers

Awk: compare values in two columns of the same file

I'm trying to learn awk, but I've hit a roadblock with this problem. I have a hierarchy stored in a file with 3 columns: id name parentID 4 D 2 2 B 1 3 C 1 1 A 5 I need to check if there are any values in column 3 that are not represented anywhere in column 1. I've tried this: awk '{arr;}... (7 Replies)
Discussion started by: kaktus
7 Replies

3. Shell Programming and Scripting

Compare multiple files, identify common records and combine unique values into one file

Good morning all, I have a problem that is one step beyond a standard awk compare. I would like to compare three files which have several thousand records against a fourth file. All of them have a value in each row that is identical, and one value in each of those rows which may be duplicated... (1 Reply)
Discussion started by: nashton
1 Replies

4. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

5. Shell Programming and Scripting

Help needed with multiplying two values of two columns in a file

Hi, I am trying to multiply column#1 with column#2 using a shell script. How can I make a for-loop script using 1st column as "i" and the second column as "j" from the following file? Please feel free to share any alternative ways to multiplying column#1 with column#2. .06 5.0000 .49 ... (6 Replies)
Discussion started by: momin
6 Replies

6. 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

7. UNIX for Dummies Questions & Answers

Subtracting values from 2 columns in a file

Hello, I have a file with 5 columns that looks like this: A1BG chr19 + 58863335 58866549 A1BG chr19 - 58858171 58864865 A2LD1 chr13 - 101182417 101186056 A2LD1 chr13 - 101182417 101241046 A2M chr12 - 9220303 9268558 A2ML1 ... (5 Replies)
Discussion started by: wolf_blue
5 Replies

8. Shell Programming and Scripting

Math operations with file columns values.

Hello everybody, I have some large files containing statistical data. The data is stored in the following generic format: >cat my_file 1, 2, 3 1, 2, 3 1, 2, 3 > The values of columns no.2 and 3 are expressed in bytes. I would like to transform them in Megabytes, by dividing them with... (3 Replies)
Discussion started by: fabian23
3 Replies

9. Shell Programming and Scripting

How to combine 2 files into 1 file with 2 columns

Hi Guys, I want to combine 2 files and and put together in 1 file and make two columns out it. See below desired output. Any help will be much appreciated. inputfile1.txt 12345 67890 24580 inputfile2.txt AAAAA BBBBB CCCCC (11 Replies)
Discussion started by: pinpe
11 Replies

10. Shell Programming and Scripting

combine two values within a file

Can you guys help me, I have two files: File A 60|11111| 601|11111| 6012|11111| 6013|11111| 60142|11111| File B 642|11111| 659|11111| 852|11111| 6012|11111| 6013|11111| I want to combine the two file into: File C 60|642| 601|642| 6012|642| (2 Replies)
Discussion started by: lurak
2 Replies
Login or Register to Ask a Question