How to split values in a column into separate column

Question

My tab-delimited file looks like this:

  ID   Pop  snp1  snp2  snp3  snp4  snp5
  AD62  1  0/1   1/1   .    1/1   0/.
  AD75  1  0/0   1/1   .    ./0   1/0
  AD89  1  .     1/0   1/1  0/0   1/.

I want to separate the columns (starting from column 3) so that the values separated by the "/" character are delimited into a column of its own. However there are also columns whereby the values are missing (they only contain the "." character) and I want this to be treated as though it was "./." so that the two "." characters are then divided into their own columns. For example:

  ID   Pop  snp1     snp2     snp3     snp4     snp5
  AD62  1    0    1   1    1   .    .   1    1   0    .
  AD75  1    0    0   1    1   .    .   .    0   1    0
  AD89  1    .    .   1    0   1    1   0    0   1    .

Thanks

A.G. · Accepted Answer · 2012-07-10 15:08:00Z

1

You can use sed:

sed -e 's/ \. /\.\t\. /g' -e 's/\//\t/g' <your_file>

answered Jul 10, 2012 at 15:08

A.G.

1,3192 gold badges11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prince John Wesley Over a year ago

+1. You need to cover title as well: -e '1s;snp[0-9];&\t;g'

mtk · Accepted Answer · 2012-07-10 15:04:29Z

0

Tried this and works well, you can tweak this as per your requirement.

Assuming data is in data.txt file.

cat data.txt | sed 1d | tr '/' '\t'| sed 's/\./.\t./g'

This gives the output, but you need to get a work around for the spaces and tab that are getting messed up.

answered Jul 10, 2012 at 15:04

mtk

13.8k16 gold badges75 silver badges116 bronze badges

Comments

potong · Accepted Answer · 2012-07-10 15:16:38Z

0

This might work for you (GNU sed):

sed ''1s/\t/&&/3g;s/\t\.\t/\t.\t.\t/g;y/\//\t/' file

answered Jul 10, 2012 at 15:16

potong

59.3k6 gold badges55 silver badges92 bronze badges

Comments

Steve · Accepted Answer · 2012-07-11 06:57:35Z

0

A fairly robust way, using awk and a few if statements:

awk '{ for (i = 1; i <= NF; i++) if (i >= 3 && i < NF && NR == 1) printf "%s\t\t", $i; else if (i == NF && NR == 1) print $i; else if ($i == "." && NR >= 2) printf ".\t.\t", $i; else { sub ("/", "\t", $i); if (i == NF) printf "%s\n", $i; else { printf "%s\t", $i; } } }' file.txt

Broken out on multiple lines:

awk '{ for (i = 1; i <= NF; i++)
   if (i >= 3 && i < NF && NR == 1) printf "%s\t\t", $i;
   else if (i == NF && NR == 1) print $i;
   else if ($i == "." && NR >= 2) printf ".\t.\t", $i;
   else {
      sub ("/", "\t", $i);
      if (i == NF) printf "%s\n", $i;
      else {
         printf "%s\t", $i;
      }
   }
}' file.txt

HTH

answered Jul 11, 2012 at 6:57

Steve

55.1k13 gold badges94 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to split values in a column into separate column

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related