Mister Muffin Blog

I recently got a csv that was exported from openoffice spreadsheet with data arranged in rows and not columns as gnuplot likes it. It seems that gnuplot (intentionally) lacks the ability to parse data in rows instead of columns. Hence I had to switch rows and columns (transpose) my input csv such that gnuplot likes it.

Transposing whitespace delimetered text can be done with awk but csv is a bit more complex as it allows quotes and escapes. So a solution had to be found which understood how to read csv.

This turned out to be so simple and minimalistic that I had to post the resulting oneliner that did the job for me:

python -c'import csv,sys;csv.writer(sys.stdout,csv.excel_tab).writerows(map(None,*list(csv.reader(sys.stdin))))'

This will read input csv from stdin and output the transpose to stdout. The transpose is done by using:

map(None, *thelist)

Another way to do the transpose in python is by using:

zip(*thelist)

But this solution doesnt handle rows of different length well.

In addition the solution above will output the csv tab delimetered instead of using commas as gnuplot likes it by using the excel_tab dialect in the csv.writer.

The solution above is problematic when some of the input values inbetween are empty. It is not problematic because the csv would be transposed incorrectly but because gnuplot collapses several whitespaces into one. There are several solutions to that problem. Either, instead of an empty cell, insert "-" in the output:

python -c'import csv,sys; csv.writer(sys.stdout, csv.excel_tab).writerows(map(lambda *x:map(lambda x:x or "-",x),*list(csv.reader(sys.stdin))))'

Or output a comma delimetered cvs and tell gnupot that the input is comma delimetered:

python -c'import csv,sys;csv.writer(sys.stdout).writerows(map(None,*list(csv.reader(sys.stdin))))'

And then in gnuplot:

set datafile separator ","