Splitting columns in TSV into CSV

Using any awk:

$ awk -F'[\t,]' -v OFS=',' '{for (i=2; i<=NF; i++) print $1, $i}' file
id1,col1
id1,col2
id1,col3
id2,col4
id2,col5

The above assumes your first column can't contain ,s and your 2nd can't contain tabs, both of which appear to be correct from the example and statements about your data format in your question, so it should work for your data.

$ mlr --t2c -N nest --evar , -f 2 file
id1,col1
id1,col2
id1,col3
id2,col4
id2,col5

This uses Miller (mlr) to read records consisting of tab-delimited header-less fields and write header-less CSV.

For each record (line), the nest operation of mlr is evaluated with the options --evar , -f 2, which means "explode the comma-delimited sub-fields ("nested fields") in field 2 into new records".

You can use awk with split function for second token:

awk  'BEGIN {OFS=","} {n=split($2,a,",");  for(i=1;i<=n;i++) print $1,a[i]} ' input_file

I don't know the TSV format, so assuming the input has the literal TAB character as the separator, not a '\' followed by a 't', then sed is an option:

sed -r ':loop;s:([^\t]+)(\t[^,]+),:\1\2\n\1>:;tloop;y:\t>:,,:' FILE

Output:

id1,col1
id1,col2
id1,col3
id2,col4
id2,col5

This seems fairly simple if you're willing to use python:

#!/usr/bin/env python

from __future__ import print_function
import sys

with open(sys.argv[1]) as f:
    for line in f:
        key, vals = line.strip().split('\t')
        for val in vals.split(','):
            print(key, val, sep=',')

The from __future__ line will make sure it works on both python2 and 3

Using perl, as a one-liner:

perl -nle 'my @f = split /\t/; print "$f[0],$_" for split /,/,$f[1]'

• -n: process input line by line and execute the code given for each line

• -l: remove end-of-line from input and add end-of-line to output

• -e: execute the provided code

• my @f = split /\t/;: split the line using \t as a delimiter, put the results in array @f

• print "$f[0],$_" for split /,/,$f[1]: split the second item from @f ($f[1]) using comma as a delimiter, and for each result, print the first item of @f and the current value.

There are probably a few more optimisations possible, my perl is rusty.

You could for instance remove the /\t/ and that would split on any white space, if that is applicable in your case.