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

test.txt (270 Bytes, 221 views)

Your original data only has 2 columns??

Also, with a file that small, why not just do it manually?

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.


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.

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

Thanks for that David ... I keep forgetting to do the quote thing to see the pattern

And of course awk does it better.

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

Cheers David