LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 03-12-2012, 10:55 AM   #1
linuxon
LQ Newbie
 
Registered: Jan 2012
Posts: 3

Rep: Reputation: Disabled
Smile how to merge multiple columns into one column


Hello,

I am not sure if it is reposted, but I could not find the same thread.

I would like to merge multiple columns into one column, for example,

col1 col2 col3 col4 col5
NM_175491.4 237782
NM_001085440.1 237782
NM_009171.2 20425
NM_001164244.1 212627
NM_001164242.1 212627
NM_001164243.1 212627
NM_144806.2 212627
please also see the attached file for the data format, I don't know why it all messed up after done with the editing.

I would like to merge column 2 to 5 and generate file like this:

NM_175491.4 237782
NM_001085440.1 237782
NM_009171.2 20425
NM_001164244.1 212627
NM_001164242.1 212627
NM_001164243.1 212627
NM_144806.2 212627

Thanks a lot,
emily
Attached Files
File Type: txt test.txt (270 Bytes, 71 views)

Last edited by linuxon; 03-12-2012 at 11:04 AM. Reason: file format was messed up
 
Old 03-12-2012, 11:47 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,565

Rep: Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901
Your original data only has 2 columns??
 
Old 03-12-2012, 11:58 AM   #3
weibullguy
ReliaFree Maintainer
 
Registered: Aug 2004
Location: Kalamazoo, Michigan
Distribution: Slackware-current, Cross Linux from Scratch, Gentoo
Posts: 2,812
Blog Entries: 1

Rep: Reputation: 259Reputation: 259Reputation: 259
Also, with a file that small, why not just do it manually?
 
Old 03-13-2012, 02:57 PM   #4
David the H.
Bash Guru
 
Registered: Jun 2004
Location: Osaka, Japan
Distribution: Debian + kde 4 / 5
Posts: 6,837

Rep: Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981
Please use [code][/code] tags around your code and data, to preserve formatting and to improve readability. Please do not use quote tags, colors, or other fancy formatting.

Code:
#input

col1            col2    col3    col4    col5
NM_175491.4	237782			
NM_001085440.1		237782		
NM_009171.2	20425			
NM_001164244.1				212627
NM_001164242.1		212627		
NM_001164243.1			212627	
NM_144806.2	212627		

#desired output:

NM_175491.4	237782			
NM_001085440.1	237782		
NM_009171.2	20425			
NM_001164244.1	212627
NM_001164242.1	212627		
NM_001164243.1	212627	
NM_144806.2	212627

With proper code tags we can now see that the columns are tab-delimited, with most fields being empty. It seems that each line only has two fields, whatever the column number?

Your description says "merge column 2 and 5", but your example shows a merging of column 1 with whatever the second column is. So what is it exactly?

In any case, there are many possible solutions, but assuming the example, one possibility is with sed.

Code:
sed -r '1d ; s/([^[:blank:]]+)[[:blank:]]+([^[:blank:]]+).*/\1\t\2/' file.txt
"1d" deletes the first line, then the second regex extracts two blank-delimited strings from the line and combines them into the final output.

Here are a few useful sed references.
http://www.grymoire.com/Unix/Sed.html
http://sed.sourceforge.net/grabbag/
http://sed.sourceforge.net/sedfaq.html
http://sed.sourceforge.net/sed1line.txt

Please clarify your requirements further if this doesn't satisfy you.
 
Old 03-14-2012, 01:12 AM   #5
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,565

Rep: Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901
Thanks for that David ... I keep forgetting to do the quote thing to see the pattern
Code:
awk '{print $1"\t"$2}' file

# OR

awk '$1 = $1' OFS="\t" file
 
Old 03-14-2012, 11:45 AM   #6
David the H.
Bash Guru
 
Registered: Jun 2004
Location: Osaka, Japan
Distribution: Debian + kde 4 / 5
Posts: 6,837

Rep: Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981Reputation: 1981
And of course awk does it better.

But don't forget that we need to skip the first line, too.

Code:
awk 'NR > 1 {print $1"\t"$2}' file
 
Old 03-14-2012, 12:17 PM   #7
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,565

Rep: Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901Reputation: 2901
Cheers David
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
merge columns from multiple files vijay_babu1981 Linux - Newbie 21 06-24-2014 07:59 AM
merge multiple files each with two columns. 11st col same but may have difft values newbie271 Linux - Newbie 2 01-10-2012 07:03 PM
sort multiple columns + replace another column cedance Linux - Newbie 1 03-29-2011 06:59 AM
How to get parts of one column and prints all columns ? nasra2002 Linux - Software 2 12-13-2010 10:36 PM
Is there a command to merge two files as two columns of one file? davee Linux - General 2 07-19-2005 11:52 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 04:20 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration