merge 1st column and 2nd column; 3rd and 4th column and so on with a separator

Question

I have a text file with 61000 columns and 173 rows. I would like to merge data on every 2 columns i.e., columns 1 and 2 should be merged, 3 and 4 should be merged, 5 and 6 should be merged and so on.

Sample input (tab separated):

Ind Pop scaffold1   X   scaffold1   X.1 scaffold3   X.2 scaffold4   X.3
a   antartica   1   1   1   1   2   2   1   1
b   antartica   1   1   1   1   2   1   1   2
c   antartica   1   1   1   1   2   1   1   1
d   antartica   1   1   1   1   2   1   1   2
e   antartica   1   1   1   1   2   1   1   2
f   arctic  1   1   1   1   2   1   1   1
g   arctic  1   1   1   2   2   1   1   1
h   arctic  1   1   1   1   2   1   1   1
I   arctic  1   1   1   1   2   1   1   1
j   arctic  1   1   1   1   2   1   1   1

desired output (tab separated):

Ind-Pop scaffold1-X scaffold2-X.1   scaffold3-X.2   scaffold4-X.3
a-antartica 1-1 1-1 2-2 1-1
b-antartica 1-1 1-1 2-1 1-2
c-antartica 1-1 1-1 2-1 1-1
d-antartica 1-1 1-1 2-1 1-2
e-antartica 1-1 1-1 2-1 1-2
f-arctic    1-1 1-1 2-1 1-1
g-arctic    1-1 1-2 2-1 1-1
h-arctic    1-1 1-1 2-1 1-1
I-arctic    1-1 1-1 2-1 1-1
j-arctic    1-1 1-1 2-1 1-1

I tried to do it with R using the unite function of tidyr package. I was able to manage to merge two columns at a time using the following command:

     unite(df, newcol, c(scaffold1, X), remove=TRUE)

Not sure how to do it for multiple columns.

Any R or perl or linux command line approaches will be appreciated!

Answer 1

sed -E 's/([^\t]+)\t([^\t]+)/\1-\2/g'

Explanation

• sed -E 's/foo/bar/g': run sed with -E extended regex, replacing foo with bar, multiple times per line /g.
• ([^\t]+)\t([^\t]+): match a non-tab character [^\t] that is one or more characters long +, and capture this in a group ([^\t]+). This is followed by a tab, then the non-tab characters again in another capturing group.
• \1-\2: replace this with the first capturing group, -, then the second capturing group. Essentially, replace the tab with -.

Why this works

sed is "greedy", i.e. tries to grab as many characters as it can. Hence the two capturing groups will try to be as long as possible. e.g. it will grab all of a antartica (replacing it with a-antartica). The next time the search is run, it has already passed by antartica, and starts searching again on the tab after this word. Hence, the next match will be 1 1, which it will replace with 1-1. This pattern will will then repeat for each pair of columns. The greedy + is important. If you omit it, the pattern will just modify every tab.

Answer 2

These are some of the methods you can use on your input "file.tsv":

$ perl -pe 's/\t/("-",$&)[$|--]/ge'  file.tsv

Here we are substituting every odd numbered tab with a dash.

$ sed -e '
    y/\t/\n/
    :a;s/\n/-/;s//\t/;ta
' file.tsv

This sed code will first change all tabs to newlines and then progressively change the odd ones to dashes and even to tabs.

$ perl -lpe 's/\t(.*?(?:\t|$))/-$1/g' file.tsv

$ perl -F'\t' -lane '
      push @A, join "-", splice @F,0,2 while @F;
      print join "\t", splice @A;
' file.tsv

$ perl -F'\t' -nae '($,,$")=("\t", "-");
   print map { "@F[2*$_,2*$_+1]" } 0..$#F/2;
' file.tsv