split file by awk

Question

Here is my file which contains three columns and separate by "\t", and the delimiter of the second column is a blank

       1   a b    tom
       2   a b c  sim
       3   a      mary
       4   o l    shey   
       5   c      bob

I want to get the first file which the second column contains multiple elements

       1   a b    tom
       2   a b c  sim
       4   o l    shey 

and then I want to get the second file like this

       1    a     tom
       1    b     tom
       2    a     sim
       2    b     sim
       2    c     sim
       4    o     shey
       4    l     shey

Actually, i have tried that

awk -F\\t 'BEGIN {OFS=FS} {n=split($2,aa," ");for (i=1;i<=n;i++) {$2=aa[i]; printf "%s\n" $0 }}'

but it looks like didn't work. Can you give me some advice? thanks.

Answer 1

$ awk 'NF>3' file
       1   a b    tom
       2   a b c  sim
       4   o l    shey

$ awk -v OFS='\t' 'NF>3{for (i=2;i<NF;i++) print $1, $i, $NF}' file
1       a       tom
1       b       tom
2       a       sim
2       b       sim
2       c       sim
4       o       shey
4       l       shey

As for why your code didn't work - the most obvious problem is that split($2,aa,",") is trying to split $2 at commas when you said and show that it's separated by blanks.

Answer 2

Alternative solution using miller for the second step and the idea of selecting records based on a space in field 2 (assumes that spaces are only present in cases with multiple elements in field 2):

awk -F'\t' '$2 ~ / /' |
tee out1 | 
mlr --tsv -N nest --explode --values --across-records --nested-fs " " -f 2 >out2