0

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!

  • Yes, sorry about that – biobudhan Aug 30 at 8:31
5
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.

  • Thanks! That works like a charm!! – biobudhan Aug 30 at 8:34
  • No worries; glad to help! – Sparhawk Aug 30 at 8:35
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.