3

I have a TSV file with two columns in the format of

id1\tcol1,col2,col3
id2\tcol4,col5

I want to split the second column in combination with the first column to output

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

There are two problems:

  • The number of comma-separated values in the second column of TSV is not fixed
  • File is too big to be loaded into memory

The comma-separator values are clean without any ,. Thus no enclosing " is used. Therefore, we split at each and every comma.

4
  • 1
    @RomeoNinov I started by splitting the two columns with awk -F'\t', but have no idea how to split the second column by ,.
    – Googlebot
    Aug 17, 2022 at 5:40
  • Check my answer. I use split function inside awk Aug 17, 2022 at 5:43
  • 1
    And you do not need to use TAB , this is one of the default field separators in awk Aug 17, 2022 at 5:44
  • 2
    @RomeoNinov whether or not you need to specify tab as the FS depends on whether or not the fields can contain blanks. A line like foo bar\tcol name1,col name2 would break if you don't set FS to a tab, for example. So far the OP has just told us none of the fields contain commas but there could be blanks anywhere as far as we know so far.
    – Ed Morton
    Aug 17, 2022 at 16:24

6 Answers 6

9

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.

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

4

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
0
2

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
0

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

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .