Python CSV transpose data in one column to rows

Question

I have a CSV file containing data only in the first column,

enter image description here

I want to use python to transpose every 4 rows to another empty CSV file, for example, row 1 to row 4 transposed to the first row; then row 5 to row 8 transposed to the second row,...etc, and finally we can get a 5 * 4 matrix in the CSV file.

enter image description here

How to write a script to do this？ Please give me any hint and suggestion, thank you.

I am using python 2.7.4 under Windows 8.1 x64.

update#1

I use the following code provided by thefortheye,

import sys, os
os.chdir('C:\Users\Heinz\Desktop')
print os.getcwd()

from itertools import islice
with open("test_csv.csv") as in_f, open("Output.csv", "w") as out_file:
    for line in ([i.rstrip()] + map(str.rstrip, islice(in_f, 3)) for i in in_f):
        out_file.write("\t".join(line) + "\n")

the input CSV file is,

enter image description here

and the result is,

enter image description here

This is not what I want.

thefourtheye · Accepted Answer · 2014-04-22 14:06:26Z

1

You can use List comprehension like this

data = range(20)
print data
# [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]
print[data[i:i + 4] for i in xrange(0, len(data), 4)]
# [[0, 1, 2, 3], [4, 5, 6, 7], [8, 9, 10, 11], [12, 13, 14, 15], [16, 17, 18,19]]

Instead of 4, you might want to use 56.

Since you are planing to read from the file, you might want to do something like this

from itertools import islice
with open("Input.txt") as in_file:
    print [[int(line)] + map(int, islice(in_file, 3)) for line in in_file]

Edit As per the updated question,

from itertools import islice
with open("Input.txt") as in_f, open("Output.txt", "w") as out_file:
    for line in ([i.rstrip()] + map(str.rstrip, islice(in_f, 3)) for i in in_f):
        out_file.write("\t".join(line) + "\n")

Edit: Since you are looking for comma separated values, you can join the lines with ,, like this

        out_file.write(",".join(line) + "\n")

edited Apr 22, 2014 at 14:06

answered Apr 22, 2014 at 8:55

thefourtheye

241k53 gold badges466 silver badges505 bronze badges

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

4 Comments

Heinz Over a year ago

Could I just transpose data and get a resulting matrix-form data contained in the CSV file?

thefourtheye Over a year ago

@Heinz What is your expected output?

thefourtheye Over a year ago

@Heinz Can you please reduce the data to a small dataset and produce the expected output?

thefourtheye Over a year ago

@Heinz I think you need out_file.write(",".join(line) + "\n")

Kei Minagawa · Accepted Answer · 2014-04-22 09:46:30Z

0

You can use List comprehension and double-loop like this.

>>> M = 3
>>> N = 5
>>> a = range(M * N)
>>> o = [[a[i * N + j] for j in xrange(N)] for i in xrange(M)]
>>> print o
[[ 0,  1,  2,  3,  4],
 [ 5,  6,  7,  8,  9],
 [10, 11, 12, 13, 14]]

answered Apr 22, 2014 at 9:46

Kei Minagawa

4,5515 gold badges31 silver badges43 bronze badges

Collectives™ on Stack Overflow

Python CSV transpose data in one column to rows

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related