4

I have the below data as input:

A 1,2
B 3,2,5
C 6,7
D 1,3,5,8

How can I get the below output using AWK?

A 1
A 2
B 3
B 2
B 5
C 6
C 7
D 1
D 3
D 5
D 8
  • 3
    This is a work order, not a question. Isn't any demonstrated effort required? – Peter Mortensen Jan 26 at 15:33
8
$ awk -F '[ ,]' '{ for (i = 2; i <= NF; ++i) print $1, $i }' file
A 1
A 2
B 3
B 2
B 5
C 6
C 7
D 1
D 3
D 5
D 8

This treats the lines as consisting of fields delimited by either spaces or commas. For each line, the awk program iterates over the second field onwards to the end of the line. For each field, it outputs the first field on the line together with the current field.

5
awk '{gsub(/,/,  "\n" $1 " "); print}' file

In this solution we are just replacing every "," by "\n$1 "

  • 1
    Non-obvious, but sweet and precise. – Paul_Pedant Jan 27 at 0:57
1

Using sed with the extended regex engine enabled we can do as shown:

$ sed -re '
   s/^((\S+\s+)[^,]+),/\1\n\2/
   P;D
' file

With Perl we can do as :

$ perl -F'\s+|,' -lane '
   print join $", splice @F, 0, 2, $F[0] while @F > 1;
' file

Split the current record on either a run of whitespace or comma and store in zero indexed array @F.

Splice the first two elements of the array and join them with single space $" and print them. Also at the same time replace the two removed elements with the first. Repeat this process till only a single element remains.

  • 2
    But the question said AWK, not sed or Perl(?). – Peter Mortensen Jan 26 at 15:36
  • Yes that is correct. But many good awk solutions were already provided. So what do you want me to do , take down the answer? – Rakesh Sharma Jan 26 at 16:12
  • "Many" apparently means "two" awk solutions: one excellent and very clear, and one mediocre. Everything else is a masterpiece of unmaintainable obscurity. – Paul_Pedant Jan 27 at 0:53
1

If sed is an option, you could do:

sed -E ':a s/^([^ ]* )(.*),([^,]*$)/\1\2\n\1\3/; ta' infile

considering below input:

B 2,3,5,6
C 6,7
D 1,3,5,8
  1. This ([^ ]* ) capture first column (assuming space is a delimiter); it will capture B (B followed by a space).
  2. This (.*), capture everything until last comma seen; it will capture 2,3,5
  3. This ([^,]*$) captures the rest of the line (ie: last field after last comma every time); it will capture 6

    • so \1\2\n\1\3 would result as below at first loop-run for first line:

      sed -E ':a s/^([^ ]* )(.*),([^,]*$)/\1\2\n\1\3/;q ;ta' infile 
      B 2,3,5
      B 6
      
    • next loop-run it would result as below:

      B 2,3
      B 5
      B 6
      
    • next run ...
    • finally in last loop-run for first line will output as below:

      sed -E ':a s/^([^ ]* )(.*),([^,]*$)/\1\2\n\1\3/ ;ta ;q' infile
      B 2
      B 3
      B 5
      B 6
      
    • now read next line and do the same process until all lines proceed and finished.

-1

I tried the below method, and it worked fine.

Command

for i in `awk '{print $1}' filename`; do sed -n '/'$i'/p' filename| awk -v i="$i" -F "," '{gsub(i," ",$0)}{for(j=1;j<=NF;j++)print i,$j}'; done

Output

A   1
A   2
B   3
B   2
B   5
C   6
C   7
D   1
D   3
D   5
D   8
  • 1
    It does pretty much what the first answer does, except for running two processes for every line in the input file, and reading the whole of the input file for every line in it, and being very obfuscated. Hence the down-vote. – Paul_Pedant Jan 27 at 0:49

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.