CODEWITHSUNDEEP
pythonshellcsvnumpytranspose
SOCKet
SOCKet

Reputation: 191

transpose row to column in python

I am a newbie in python and I would like to transpose a CSV-formated file. The file I have is structured like this:

Country;Class;Number
UK;1;50
Germany;2;30
France;3;50
France;1;20

I need to transpose the class variable into columns, i.e.

          class1 class2

country      Number Number

Is it possible to do that in Shell or in Python?

I know that it's possible to transpose a matrix completely with zip() in Python but I just want to transpose the class-column. Is it possible to do that in Python or in a shellscript?

Upvotes: 1

Views: 5499

Answers (2)

Paul H
Paul H

Reputation: 68126

You want to pivot, not transpose the data:

import pandas
from io import StringIO # python 3
# from StringIO import StringIO # python 2

datafile = StringIO("""\
Country;Class;Number
UK;1;50
Germany;2;30
France;3;50
France;1;20
""")
df = pandas.read_csv(datafile, sep=';')
print(df.pivot(index='Country', columns='Class', values='Number'))

Class     1   2   3
Country            
France   20 NaN  50
Germany NaN  30 NaN
UK       50 NaN NaN

Upvotes: 1

supermitch
supermitch

Reputation: 2952

import csv

with open('test.csv', 'r') as f:
    reader = csv.DictReader(f, delimiter=';')
    # make a dict of empty lists, to contain our values
    transposed = {k:[] for k in reader.fieldnames}  # could use defaultdict here
    for row in reader:
        for k, v in row.items():
            transposed[k].append(v)
print(transposed)

This will give you a dictionary which looks like this:

{
    'Country': ['UK', 'Germany', 'France', 'France'],
    'Class': ['1', '2', '3', '1'],
    'Number': ['50', '30', '50', '20']
}

Bonus: You could also use a collections.defaultdict instead of using a dictionary comprehension to set up your transposed:

from collections import defaultdict
transposed = defaultdict(list)

Upvotes: 0

Related Questions