How to transpose a dataset in a csv file?

For example, i would like to transform:

Name,Time,Score
Dan,68,20
Suse,42,40
Tracy,50,38

Into:

Name,Dan,Suse,Tracy
Time,68,42,50
Score,20,40,38

Edit: The original question used the term “transpose” incorrectly.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If the whole file contents fits into memory, you can use

import csv
from itertools import izip
a = izip(*csv.reader(open("input.csv", "rb")))
csv.writer(open("output.csv", "wb")).writerows(a)

You can basically think of zip() and izip() as transpose operations:

a = [(1, 2, 3),
     (4, 5, 6),
     (7, 8, 9)]
zip(*a)
# [(1, 4, 7),
#  (2, 5, 8),
#  (3, 6, 9)]

izip() avoids the immediate copying of the data, but will basically do the same.

Method 2

Transfer from input.csv to output.csv. Pandas can also help.

import pandas as pd
pd.read_csv('input.csv', header=None).T.to_csv('output.csv', header=False, index=False)

Method 3

Same answer of nosklo (all credits to him), but for python3:

from csv import reader, writer 
with open('source.csv') as f, open('destination.csv', 'w') as fw: 
    writer(fw, delimiter=',').writerows(zip(*reader(f, delimiter=',')))

Method 4

from itertools import izip
from csv import reader, writer

with open('source.csv') as f, open('destination.csv', 'w') as fw:
    writer(fw, delimiter=',').writerows(izip(*reader(f, delimiter=',')))

Method 5

If lines is the list of your original text than it should be

for i in range(1,len(lines)):
    lines[i] = lines[i].split(',')

new_lines = []
for i in range(len(lines[0])):
    new_lines.append("%s,%s,%s" % (lines[0][i], lines[1][i], lines[2][i]))

or use csv Python module – http://docs.python.org/library/csv.html

Method 6

The simplest way is:

import numpy as np
import pandas as pd

_mat = pd.read_csv("test.csv")
_mat = _mat[_mat.columns[0:3]].values
_t_mat = np.transpose(_mat)

Result:

• Input matrix is : [[1 2 3] [4 5 6]]
• the output is: [[1 4] [2 5] [3 6]]

Method 7

Read the CSV into pandas data frame, pandas has build in function for transpose which can be invoked as below.

import pandas as pd

csv = pd.read_csv("test.csv", skiprows=1)
# use skiprows if you want to skip headers
df_csv = pd.DataFrame(data=csv)
transposed_csv = df_csv.T
print(transposed_csv)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0