CODEWITHSUNDEEP
pythoncsv
Gripsiden
Gripsiden

Reputation: 497

CSV File Transpose Column to Row in Python

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?

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!

<Python - Transpose columns to rows within data operation and before writing to file >>

Upvotes: 1

Views: 2256

Answers (2)

Gripsiden
Gripsiden

Reputation: 497

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)

Upvotes: 0

Stef
Stef

Reputation: 30679

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

Upvotes: 2

Related Questions