bash - Reshaping from wide to long format

Question

I am trying to use unix to transform a tab delimited file from a short/wide format to long format, in a similar way as the reshape function in R. I hope to create three rows for each row in the starting file. Column 4 currently contains 3 values separated by commas. I hope to keep columns 1, 2, and 3 the same for each starting row, but have column 4 be one of the values from the initial column 4. This example probably makes it more clear than I can describe verbally:

current file:  
A1  A2  A3  A4,A5,A6  
B1  B2  B3  B4,B5,B6  
C1  C2  C3  C4,C5,C6  

goal:  
A1  A2  A3  A4  
A1  A2  A3  A5  
A1  A2  A3  A6  
B1  B2  B3  B4  
B1  B2  B3  B5  
B1  B2  B3  B6  
C1  C2  C3  C4  
C1  C2  C3  C5  
C1  C2  C3  C6  

As someone just becoming familiar with this language, my initial thought was to use sed to find the commas replace with a hard return

sed 's/,/& /' data.frame

I am really not sure how to include the values for columns 1-3. I had low hopes of this working, but the only thing I could think of was to try inserting the column values with {print $1, $2, $3}.

sed 's/,/& {print $1, $2, $3}/' data.frame

Not to my surprise, the output looked like this:

A1  A2  A3  A4  
{print $1, $2, $3}  A5  
{print $1, $2, $3}  A6  
B1  B2  B3  B4  
{print $1, $2, $3}  B5  
{print $1, $2, $3}  B6  
C1  C2  C3  C4  
{print $1, $2, $3}  C5  
{print $1, $2, $3}  C6  

It seems like an approach might be to store the values of columns 1-3 and then insert them. I am not really sure how to store the values, I think that it may involve using an adaptation of the following script, but I am having a hard time understanding all of the components.

NR==FNR{a[$1, $2, $3]=1}

Thanks in advance for your thoughts on this.

Answer 1

You can a write simple read loop for this and use brace expansion for parsing the comma delimited field:

#!/bin/bash

while read -r f1 f2 f3 c1; do
  # split the comma delimited field 'c1' into its constituents
  for c in ${c1//,/ }; do
     printf "$f1 $f2 $f3 $c
"
  done
done < input.txt

Output:

A1 A2 A3 A4
A1 A2 A3 A5
A1 A2 A3 A6
B1 B2 B3 B4
B1 B2 B3 B5
B1 B2 B3 B6
C1 C2 C3 C4
C1 C2 C3 C5
C1 C2 C3 C6