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!

Stef · Accepted Answer · 2019-09-20 14:45:50Z

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, 2019 at 14:45

Stef

30.9k3 gold badges34 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gripsiden Over a year ago

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 stackoverflow.com/questions/25005566/… just now and I am all set , thank you

Gripsiden · Accepted Answer · 2019-09-20 15:04:27Z

0

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)

answered Sep 20, 2019 at 15:04

Gripsiden

4972 silver badges15 bronze badges

Collectives™ on Stack Overflow

CSV File Transpose Column to Row in Python

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related