Split a row by a delimiter

Question

I have a space-separated text file, e.g.

text1a text2a id1 text4a text5a
text1b text2b id2 text4b text5b
text1c text2c id3,id4 text4c text5c
text1d text2d id5,id6,id7 text4d,text4di text5d

The file is about 1.5 million lines long.

Some lines have two ids, separated by a comma, e.g. line 3 in the example. This is causing issues when attempting to join the file with another file in which the id could either be id3 or id4.

I would like to find all instances of column 3 in which a comma is present, and separate whatever is on either side into separate lines, e.g the above file would turn into.

text1a text2a id1 text4a text5a
text1b text2b id2 text4b text5b
text1c text2c id3 text4c text5c
text1c text2c id4 text4c text5c
text1d text2d id5 text4d,text4di text5d
text1d text2d id6 text4d,text4di text5d
text1d text2d id7 text4d,text4di text5d

There are rows that contain 3 or more comma-separated ids. Commas can appear in other columns but they should stay as they are. The order does not matter, e.g. whether id3 or id4 come first in the file.

I'm fairly inexperienced with awk, sed etc, which I assume is the best tool for this job.

Would anyone be able to point me in the right direction please?

Answer 1

$ awk 'split($3,f,/,/)>1{for (i=1; i in f; i++) {$3=f[i]; print} next } 1' file
text1a text2a id1 text4a text5a
text1b text2b id2 text4b text5b
text1c text2c id3 text4c text5c
text1c text2c id4 text4c text5c
text1d text2d id5 text4d,text4di text5d
text1d text2d id6 text4d,text4di text5d
text1d text2d id7 text4d,text4di text5d

The above preserves the order of the ids listed in $3, if that's not desirable then you can do for (i in f) instead of for (i=1; i in f; i++).

Only executing the block containing the loop where $3 is assigned if split() returned more than 1 is more efficient than doing the assignment unconditionally because every time you assign to a field you force awk to reconstruct the current record replacing all FSs with OFSs.

Answer 2

Since the ordering of the split-up rows does not matter, we could do it like the following with awk:

$ awk '{ split($3,a,","); for (i in a) { $3 = a[i]; print } }' file
text1a text2a id1 text4a text5a
text1b text2b id2 text4b text5b
text1c text2c id3 text4c text5c
text1c text2c id4 text4c text5c
text1d text2d id5 text4d,text4di text5d
text1d text2d id6 text4d,text4di text5d
text1d text2d id7 text4d,text4di text5d

For each line, this spits the 3rd field on commas, creating the array a. This array may possibly contain only a single element if there was no comma in the field.

The code then loops over the indexes of the a array (this may end up being done in any order, depending on how your awk implements arrays), modifying the 3rd field by setting it to the current element from the a array, and then printing the full (possibly modified) record.

Answer 3

Another aproach could be :

cat fun.awk

$3~/^id[0-9]+,/ {
    split($3, store, ",");
    for (i = 1; i in  store; i++)
        print $1, $2, store[i], $4, $5

    next
}

{ print $0 }

If the third column contains a comma ($3~/^id[0-9]+,/) then split it and print a line for each, then go the next cicle. Else print the whole line ($0).