Restructure csv file

Question

Here is an example of the data I'd like to modify:

John,,
bim,bam,boom
tim,tam,toom
lam,loom,lim
Mary,,
pam,pim,poom
dam,dim,doom*

I try to achieve this:

John,bim,bam,boom
John,tim,tam,toom
John,lam,loom,lim
Mary,pam,pim,poom
Mary,dam,dim,doom

When a name is alone on a line, it becomes a new column in all the rows below til the next name with the same pattern occurs.

I wonder if awk or another tool could help but I'm a bit lost.

I try to make a condition (if columns 2 and 3 are empty, do something) but that looks a bit complicated for me right now.

Answer 1

You are thinking on the right lines:

I try to make a condition (if columns 2 and 3 are empty, do something)

Specifically,

• if columns 2 and 3 are empty, save the value of column 1 and continue
• otherwise, prepend the saved value to the line and print it (there are several awkish ways to do this)

So for example

awk -F, '$2=="" && $3=="" {pfx=$1; next} {print pfx "," $0}' file.csv

or more idiomatically using the FS field separator variable

awk -F, '$2=="" && $3=="" {pfx=$1; next} {$0 = pfx FS $0; print}' file.csv

or (since the default action - when a rule evaluates true or 1 - is to print the record)

awk -F, '$2=="" && $3=="" {pfx=$1; next} {$0 = pfx FS $0} 1' file.csv
John,bim,bam,boom
John,tim,tam,toom
John,lam,loom,lim
Mary,pam,pim,poom
Mary,dam,dim,doom

Answer 2

Perl alternative:

$ perl -F',' -lane 'if(@F == 1){$prefix = sprintf("%s,",@F);next;};print $prefix . $_' input.csv
John,bim,bam,boom
John,tim,tam,toom
John,lam,loom,lim
Mary,pam,pim,poom
Mary,dam,dim,doom

This operates on the premise that each line is split into array of elements using , as operator, and if there's only one item in that array,we use that line as prefix and go to next line. Other lines that are longer than 1 element will get prefix appended to them. Naturally, the prefix changes if and only if the array is of length 1.

Or shorter, using shift as suggested by Glenn Jackman:

$ perl -F',' -lane 'if(@F == 1){$prefix = shift @F;next;};print $prefix . "," . $_' input.csv       

Or

$ perl -F, -lane '$,=","; if (1 == @F) {$name = shift @F} else {print $name, @F}' input.csv