Is there a command line utility to transpose a csv-file?

Question

Given a file like so

First,Last,Age
Cory,Klein,27
John Jacob,Smith,30

Is there a command line utility to transpose the contents so the output appears like so

First,Cory,John Jacob
Last,Klein,Smith
Age,27,30

Answer 1

From https://stackoverflow.com/a/2776078 :

$ apt-get install csvtool

And then convert

$ csvtool transpose input.csv > ouput.csv

Or in pipeline

$ ... | csvtool transpose - | ...

Answer 2

CSV parsing is not easily done with POSIX tools only, unless you are using a simplified CSV variant with no quoting (so that commas can't appear in a field). Even then, this task doesn't seem easy to do with awk or other text processing to tool. You can use Perl with Text::CSV, Python with csv, R with read.csv, Ruby with CSV, … (All of these are part of the standard library of the respective language except for Perl.)

For example, in Python:

import csv, sys
rows = list(csv.reader(sys.stdin))
writer = csv.writer(sys.stdout)
for col in xrange(0, len(rows[0])):
    writer.writerow([row[col] for row in rows])

Answer 3

ruby -rcsv -e 'puts CSV.parse(STDIN).transpose.map &:to_csv' < in.csv > out.csv

Answer 4

A quick & dirty bash solution :

c=1
file=file.txt
num_lines=$(wc -l < "$file")

for ((i=0; i<num_lines; i++)) {
    cut -d, -f$c "$file" | paste -sd ','
    ((c++))
}

Answer 5

Assuming that the CSV file is a simple CSV file, meaning no field contains embedded delimiters or newlines, you may use GNU datamash:

datamash transpose --field-separator=',' < input.csv

Answer 6

Given the suggested limitation (no quoting, no embedded commas), it is simple in awk (as it would be in perl not taking into account over a thousand lines in CSV.pm, 2300 lines in csv.rb — python has only 450 lines in csv.py).

Here is an example for awk:

#!/usr/bin/awk -f
BEGIN { width=0; }
{
    max = split($0, list, ",");
    # printf "%d:%s\n", NR, $0;
    if (width < max)
        width = max;
    for (n = 1; n <= max; ++n) {
        sub("^[     ]*","",list[n]);
        sub("[  ]*$","",list[n]);
        # printf "\t%d:%s\n", n, list[n];
        if ( columns[n] != "" ) {
            columns[n] = columns[n] ", ";
        }
        columns[n] = columns[n] list[n];
    }
}
END {
    # printf "%d columns\n", width;
    for (n = 1; n <= width; ++n) {
        printf "%s\n", columns[n];
    }
}

By the way: the given example had extra space which OP assumed would be removed; the other examples did not address this detail.

Answer 7

Using Raku (formerly known a Perl_6)

raku -MText::CSV -e 'my @a = csv(in => $*IN); @a = [Z] @a; csv(in => @a, out => $*OUT);'  

Adding this to the excellent list of suggestions already posted. It should be obvious to Perl-aficionados that the Raku ecosystem is somewhat similar in the way Modules get loaded at the command line (e.g. -MText::CSV).

Note, the first and third statements above are a bare-bones way to validate/output CSV files using Raku. So CSV output will contain double-quoted strings where necessary.

This question asks for transposition, and thus the extra (second) statement transposes rows/columns (representation within the @a array).

Sample Input:

First,Last,Age
Cory,Klein,27
John Jacob,Smith,30

Sample Output:

First,Cory,"John Jacob"
Last,Klein,Smith
Age,27,30

---

ADDENDUM: Code below for those who absolutely insist their CSV files don't need validation by a CSV-parser (same output as above, sans double-quotes):

raku -e 'my @a = lines.map(*.split(",")); .join(",").put for [Z] @a;' 

OR

raku -e '.join(",").put for [Z] lines.map: *.split(",");'

---

https://docs.raku.org/language/operators#index-entry-[]_(reduction_metaoperators)
https://github.com/Tux/CSV/blob/master/doc/Text-CSV.pdf
https://raku.org

Answer 8

You can transpose with an awk one-liner:

$ awk 'BEGIN{FS=OFS=","}{ for (i=1; i<=NF; i++) a[i]= (i in a?a[i] OFS :"")$i}END{ for (i=1; i<=NF; i++) print a[i] }' input_file
First,Cory,John Jacob
Last,Klein,Smith
Age,27,30