Excel (CSV) - transform header data to rows mapping with repeating rows
Question: I have Excel based data set which needs transformation. I would request a
Python based solution as I am learning Python and can read/modify the code
thereafter. I am OK with either an Excel or CSV based input/output.

**This is what my data looks like**

**Channel** **Condition** **Value1** **Value2** **Value3** _(Header)_  
Channel A Condition B Live Live Pilot

Channel A Condition B Live Pilot Live

Channel B Condition C Pilot Pilot Pilot

Channel C Condition D Live Live Live

**This is the output I want:**

**Channel** **Condition** **Value(all)** *_Status_ * _(Header. I am OK if this
does not show up on output)_

Channel A Condition B Value1 Live

Channel A Condition B Value2 Live

Channel A Condition B Value 3 Pilot

Channel A Condition B Value 1 Live

Channel A Condition B Value 2 Pilot

Channel A Condition B Value 3 Live...

Basically it is a repetition of the Channel and Condition for each of the
"Values" which should be fetched from Column header and the dataset it self
(Live/Pilot).

I would appreciate some assistance as I have about 1000 rows of such
transformation to do

Here is an Image representing what I want ![enter image description
here](http://i.stack.imgur.com/Hg4Z4.jpg)

Edit 2: There's a type on the screenshot. the Last 3 rows should read Channel
B, not Channel A.


Answer: Something like that should to the job.

    
    
    import csv
    
    transformed = []
    with open('excel.csv', newline='') as csvfile:
        r = csv.reader(csvfile, delimiter=' ', quotechar='|')
        for row in r:
            channel, condition, *vals = row
            for val in vals:
                transformed.append([channel, condition, val])
    
    with open('transformed.csv', 'w', newline='') as csvfile:
        w = csv.writer(csvfile, delimiter=' ',
                                quotechar='|', quoting=csv.QUOTE_MINIMAL)
        for row in transformed:
            w.writerow(' '.join(row))
    

