1

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.

  • 1
    Welcome to Unix Stackexchange! You can take the tour first and the learn How to Ask a good question. That makes it easier for us to help you. – andcoz Feb 7 '17 at 14:19
  • Please, give us some example of what you tried so far. – andcoz Feb 7 '17 at 14:26
  • Of course. I tested the pattern with – Plouf Feb 7 '17 at 14:28
  • Of course. Here it is. I tested the pattern with: awk -F, '/,,/ {print $1}' file1 and now I try to put use each of these $1 to make a new column in the following lines until the pattern reappears. But I struggle to do this. Maybe a while condition ("while I have not this /,,/ on a line"). – Plouf Feb 7 '17 at 14:35
5

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
  • 1
    I would use the OFS awk -F, -v OFS=, '... {print pfx, $0}' – glenn jackman Feb 7 '17 at 21:20
  • @steeldriver thanks, it's very helpful to me! – Plouf Feb 8 '17 at 5:27
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
  • @Theophrastus Fixed. Thank you for pointing out the mistake – Sergiy Kolodyazhnyy Feb 7 '17 at 21:23
  • 1
    A little more succinct: perl -F, -lane '$,=","; if (1 == @F) {$name = shift @F} else {print $name, @F}' file – glenn jackman Feb 7 '17 at 21:24
  • My first suggestion misses the first comma. – glenn jackman Feb 7 '17 at 21:25
  • @glennjackman interesting, so @F in (1 == @F) is automatically evaluated as scalar ? – Sergiy Kolodyazhnyy Feb 7 '17 at 21:26
  • 2
    A list in scalar context is taken as the number of elements: perl -le '@l=(1,2,3,4); print @l; print 0+@l' -- note that can harm readability: for production code I would use scalar() – glenn jackman Feb 7 '17 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.