16

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
6
ruby -rcsv -e 'puts CSV.parse(STDIN).transpose.map &:to_csv' < in.csv > out.csv
  • Given the age of this question I will justify my changing to this as the accepted: a) This answer is much more succinct than Gilles' python, b) ruby is no less portable than python, and c) this also shows how to pass the input/output files. Bravo @luikore, and welcome to Unix & Linux. Please do stick around. – Cory Klein Nov 27 '18 at 2:07
  • one caveat, in the csv, fields must be quoted – yosefrow Dec 24 '19 at 12:37
  • @yosefrow No need quoting. I tested the command before I posted this answer. – luikore Dec 25 '19 at 5:33
  • ok should have said "may" then. It didnt work for me until I quoted all fields. Might have to do with my data content – yosefrow Dec 26 '19 at 7:43
16

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

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

$ apt-get install csvtool

And then convert

$ csvtool transpose input.csv > ouput.csv

Or in pipeline

$ ... | csvtool transpose - | ...
  • 1
    beautiful. Only ... | csvtranspose | ... would beat that, syntax-wise. – masterxilo Feb 19 '19 at 10:15
3

A quick & dirty 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++))
}
  • what does /tmp/l represent? Additionally, would it not be simpler to loop through the columns rather than lines, something along the lines of for ((i=1; i<=$num_cols; ++i)); do paste -s -d, <(cut -f$i -d, file.txt); done – iruvar Jan 8 '13 at 13:29
  • Note that this works for OP's input, but only because their data has the same number of rows and columns, which usually is not the case. – tokland Mar 14 '16 at 13:24
  • csv has specification regarding dpuble quotes , i.e. this "is" example cell is encoded "this ""is"" example" I am not convinced if this solution handles such cases properly – Grzegorz Wierzowiecki Oct 5 '16 at 12:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.