CSV File Transpose Column to Row in Python

Question

Ive been wrecking my head with this and I probably just need to step back.

I have a CSV file like this : ( dummy data - there could be 1-20 Parameters )

CAR,NAME,AGE,COLOUR

Ford,Mike,45,Blue

VW,Peter,67,Yellow

And need

CAR,PARAMETER,VALUE

Ford,NAME,Mike

Ford,AGE,45

Ford,COLOUR,BLUE

VW,NAME,Peter

VW,AGE,67

VW,COLOUR,Yellow

Im Looking at :

How to transpose a dataset in a csv file?

Python writing a .csv file with rows and columns transpose

But i think because I want to keep CAR column static , the Python zip function might not hack it..

Any thoughts on this Sunny Friday Gurus?

Regards!

score 2 · Accepted Answer · answered Sep 20 '19 at 14:45

2

Use pandas:

df_in = read_csv('infile.csv')
df_out = df_in.set_index('CAR').stack().reset_index()
df_out.columns = ['CAR', 'PARAMETER', 'VALUE']
df_out.to_csv('outfile.csv', index=False)

Input and output example:

>>> df_in
    CAR   NAME  AGE  COLOUR
0  Ford   Mike   45    Blue
1    VW  Peter   67  Yellow
>>> df_out
    CAR PARAMETER   VALUE
0  Ford      NAME    Mike
1  Ford       AGE      45
2  Ford    COLOUR    Blue
3    VW      NAME   Peter
4    VW       AGE      67
5    VW    COLOUR  Yellow

answered Sep 20 '19 at 14:45

Stef

28,728
2
24
52

Hi Stef , thank you so much for your input. I had wanted to try without using anything external to what i already had , I was able to use https://stackoverflow.com/questions/25005566/python-transpose-columns-to-rows-within-data-operation-and-before-writing-to-f just now and I am all set , thank you – Gripsiden Sep 20 '19 at 15:01

score 0 · Answer 2 · answered Sep 20 '19 at 15:04

I was able to use Python - Transpose columns to rows within data operation and before writing to file with some tweaks and all is working now well.

import csv

with open('transposed.csv', 'wt') as destfile:
    writer = csv.writer(destfile)
    writer.writerow(['car', 'parameter', 'value'])
    with open('input.csv', 'rt') as sourcefile:
        for d in csv.DictReader(sourcefile):
            car= d.pop('car')
            for parameter, value in sorted(d.items()):
                row = [car, parameter.upper(), value]
                writer.writerow(row)

CSV File Transpose Column to Row in Python

2 Answers2