Shell Programming and Scripting

How do I stack several columns of data into their own rows?

Code:

In:
A,B,C,d,e,f,g,H,I
1,2,3,4,5,6,7,8,9
 
Out:
A,B,C,X,Y,H,I
1,2,3,d,4,8,9
1,2,3,e,5,8,9
1,2,3,f,6,8,9
1,2,3,g,7,8,9

Mike

If you expect anyone to devote any time to this, you should put some effort into explaining the logic behind the transformation, instead of leaving it for us to deduce. It may be obvious to you, but different people may draw different conclusions and squander their valuable time. And, please, do mention if there are any constraints or special cases, since they can drastically affect the complexity of the solution.

Is the magic secret that lowercase headers and their corresponding values should be converted to columns while the values under uppercase headers are simply extended to fill the matrix?

What is the point of this? Homework assignment?

Regards,
Alister

Alister, stacking/unstacking is a standard term in data processing so I thought I would be understood. I guess it is more esoteric than I thought and I appologise. I usually do this in JSL or SAS but am looking for a shell script solution since a bunch of other processing is done in BASH. If there were any exceptions I would have mentioned them.

This is not homework as I am not a student. My actual data contains IP, so I use general/make-up data.

Example, Stacking the three columns in Red:

Code:

 
Name, Sex, Height, Weight, ID
Bob, M, 75, 220, 21345
Jill, F, 61, 120, 43563
Frank, M, 61, 300, 57864

Code:

 
Name, Label, Value, ID
Bob, Sex, M, 21345
Bob, Height, 75, 21345
Bob, Weight, 220, 21345
Jill, Sex, F, 43563
Jill, Height, 61, 43563
Jill, Weight, 120, 43563
Frank, Sex, M, 57864
Frank, Height, 61, 57864
Frank, Weight, 300, 57864

Mike

You didn't answer the key question. When we look at the first line in your input, how do we know which fields are headers and which fields are an indication are data from following lines are to be turned into headers. In your first example, you list headers (e, f, g, and h) and we're magically supposed to know to throw away those data values in the following lines. In your second example, you have the headers Label and Value and we're magically supposed to know to get column headers our of the following data.

Please give us a clear, concise statement of the transformation that is supposed to occur to produce the output you need from the input you have.

The two examples you have provided are COMPLETELY different problems.

I strongly agree with the others - YOU need to be more specific so that WE don't have to waste OUR time guessing and providing half-a** solutions that really don't fit.
Having said that.... here's my "guess" (based on your last example)
awk -f mic.awk myFile
mic.awk:

Code:

BEGIN { FS=OFS=","}
FNR==1 {
   n=split($0,hA,FS)
   print $1, "Label", "Value", $NF
   next
}
{
  for(i=2;i<NF;i++)
   print $1,hA[i],$i, $NF
}

I guess I could have been more clear in my first example, but the second example seems clear to me. The columns to stack are known ahead of time as are the stack parameter header label and value column label--no magic needed.

The desired transformation in the two examples is of the same type (note, I just fixed a small error in the first example, a missing value header).

Mike

hm..... I really don't see how the first sample/result relates to the second one. I (for one) could not understand the first one at all - maybe others could have guessed better given your last explanation - I cannot....
So was my "guess" good-enough?