How to transpose a dataset in a csv file?

Question

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.

The word you are looking for is pivot, as opposed to transpose. — ocodo, Feb 01 '11 at 23:33
What have you tried? Is this homework? WHY do you want to transform it from something usual to something weird? — John Machin, Feb 01 '11 at 23:36
@zr: I'm with you. We need a "wish-it-was-homework" tag in SO. :-) — payne, Feb 01 '11 at 23:45
I don't think transpose is incorrect here, personally. See http://en.wikipedia.org/wiki/Transpose — chmullig, Feb 01 '11 at 23:51
@chmullig: ??? matrix transposition is EXACTLY what the OP wants. "pivot" is not the word anyone should be looking for. — John Machin, Feb 02 '11 at 00:13
@John Machin - I agree. I was disagreeing with @Slomojo. Should have phrased it better. — chmullig, Feb 02 '11 at 00:36
@chmullig: looks like I missed the "in" in incorrect ... I'm not usually fazed by double negatives; I'm the guy who writes `not not x` instead of `bool(x)` :-) — John Machin, Feb 02 '11 at 00:46
Excel calls this operation "transpose". And it is a reflection in the diagonal, top-left to bottom-right, line as Wikipedia states. Why isn't it a transposition? — Rikki, May 26 '16 at 08:12
Yes: it is transposition. But WHY is it going from row-orientation to column-orientation... and more importantly: why do all tutorials for CSV data perform this transpose... and then fail to tell you how to get ROWS of data into your model? I have spent 2 days trying to get CSV data into a model - it should not be so hard, or so obscure. — omatai, May 06 '20 at 06:21

Sven Marnach · Accepted Answer · 2011-02-01T23:43:03.843

45

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.

edited Feb 01 '11 at 23:43

answered Feb 01 '11 at 23:36

Sven Marnach

574,206
118
941
841

That's very slick. You might explain what you're doing though, since it's probably not intuitive to someone unfamiliar with izip and python iterables. – chmullig Feb 01 '11 at 23:39
@chmullig: Added and explanatory example. – Sven Marnach Feb 01 '11 at 23:45
2

If the whole file _doesn't_ fit in memory add more memory – John La Rooy Feb 01 '11 at 23:55
2

+1. BTW, it looks like the manual needs updating "If the syntax *expression appears in the function call, expression must evaluate to a sequence" ... csv.reader() doesn't evaluate to a sequence. – John Machin Feb 01 '11 at 23:55
I've never seen the * symbol being used this way in python before, any links where I can find documentation for it? – Tony Mar 29 '12 at 16:12
1

@Tony: In the [Python Tutorial](http://docs.python.org/tutorial/controlflow.html#unpacking-argument-lists). – Sven Marnach Mar 29 '12 at 16:18
1

@xApple: As long as all your lines have the same number of items, this will work. If you have lines with different numbers of columns, transposition is rather meaningless anyway. – Sven Marnach Jun 14 '13 at 10:04
After investigation, it was a buffering and cache issue with the file objects. Sorry. – xApple Jun 14 '13 at 10:35
Holy wow. Two years on, I learned more about unpacking and zip from reading this answer (found on a google search whildst trying to quick-hack around a stupid excel bug) than in all previous deliberate study. super-slick, much respect. – Paul Gowder Dec 10 '15 at 03:00
numpy with it transpose method – Indigo_heart Sep 10 '18 at 18:31

score 18 · Answer 2 · edited Mar 21 '19 at 18:56

18

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)

edited Mar 21 '19 at 18:56

Anton Tarasenko

8,099
11
66
91

answered Dec 24 '16 at 00:43

Da Qi

615
5
10

Excellent one-liner. – Aventinus Jul 30 '18 at 12:20
Saved the day, @anton-tarasenko ! I still wonder why `csv.writerows(my_list)` cannot do what I need it to. Instead I am having to do `csv.writerow(my_list)` then use your stransposing one-line to get my one row to become multiple rows. Crazy! – Alain Nov 27 '20 at 17:32

score 5 · Answer 3 · answered May 10 '19 at 12:24

5

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=',')))

answered May 10 '19 at 12:24

Joe

12,057
5
39
55

Needs `open(output_filename, 'w', newline='') as fw` or it's double spaced. – Bernd Wechner Jan 12 '21 at 03:58

score 2 · Answer 4 · edited Feb 08 '18 at 09:08

2

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=',')))

edited Feb 08 '18 at 09:08

Matt Wang

318
1
10

answered Feb 01 '11 at 23:44

nosklo

217,122
57
293
297

-1 If Python 2.x, should use binary mode on both files. If Python 3.x, should use newline='' on both files. Also: `delimiter=','` is the default. – John Machin Feb 02 '11 at 00:09

score 1 · Answer 5 · answered Feb 01 '11 at 23:36

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

score 0 · Answer 6 · edited May 08 '18 at 12:36

0

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]]

edited May 08 '18 at 12:36

Matt Wang

318
1
10

answered Nov 14 '17 at 20:23

score 0 · Answer 7 · answered Apr 10 '19 at 10:37

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)

How to transpose a dataset in a csv file?

7 Answers7

Linked

Related