Is there a utility to transpose a .csv file?

Question

I have some csv files with too many columns for LibreOffice Calc to open, but only a few rows. If I can transpose the csv file, it should be ok as Calc can deal with very many more rows than columns.

Solution 1

A simple python program would do the job (and since this is not really tested: backup you csv file in advance!):

import csv
import sys
infile = sys.argv[1]
outfile = sys.argv[2]

with open(infile) as f:
    reader = csv.reader(f)
    cols = []
    for row in reader:
        cols.append(row)

with open(outfile, 'wb') as f:
    writer = csv.writer(f)
    for i in range(len(max(cols, key=len))):
        writer.writerow([(c[i] if i<len(c) else '') for c in cols])

You can save this into a file "my_csv_transposer.py" and call it from the commandline like this:

python my_csv_transposer.py <theinfilename> <theoutfilename>

Solution 2

You can use this script from the shell like this:

$ ./transpose_csv < theinfilename > theoutfilename

And here is the script:

#!/usr/bin/env python
import sys, csv, itertools
rows = itertools.izip(*csv.reader(sys.stdin, delimiter=','))
sys.stdout.writelines(','.join(row) + '\n' for row in rows)

Solution 3

For reference's sake, here is the same incantation in Ruby:

require 'CSV'

rows = CSV.new($stdin).read
puts rows.transpose.map { |x| x.join ',' }

Comments

[Mahesh]

I doubt in what way does this really relate to Ubuntu. Just an honest doubt

[drevicko]

nice, worked a treat, thanks (: I used xrange rather than range to run a bit faster (minor point!).

[gare]

nice script! there is extra ')' at the end, otherwise awesome.

[Janus Troelsen]

see also stackoverflow.com/a/4869245/309483

[Janus Troelsen]

for python3 with stdin/stdout i/o: python3 -c "import sys,csv; csv.writer(sys.stdout).writerows(zip(*csv.reader(sys.stdin))‌​)"