bfdi533 has asked for the wisdom of the Perl Monks concerning the following question:

I have been looking for a good way to transpose the rows and columns in a CSV file that I am working with. It was shipped to me with the data columns on the left and the data across. This, as you may realize, is impossible to load into a database or work with it with any sort of meaningful data tools.

I have searched the site and the web and cannot find any reference to doing this. There are many ways to swap columns in a CSV file here but no transpose code. There was a reference to PDL and looking into it, the transpose would be very easy.

But, I cannot seem to find any way to load an array into a PDL. I can successfully get the CSV file into an array using Text::CSV::Simple. But, now how would I get this array into a PDL so I can do the transpose?

Replies are listed 'Best First'.
Re: using PDL->transpose on CSV file
by zentara (Cardinal) on Feb 08, 2006 at 16:51 UTC
    See Two handy tools for nested arrays: mapat and transpose and Transpose a matrix of chars for some ideas. I don't think you would need the number crunching power of PDL for a simple transpose. See Math-Matrix

    There are also some snippets laying around in old nodes, for taking slices from 2-d arrays, you could just take slices and use it to convert from rows to columns or vice-versa.

    Here is one I have handy( use Super Search)

    #!/usr/bin/perl # 2-d array-slice my @arr = (['a','b','c'], ['h','i','j'], ['x','y','z']); print map $_->[1], @arr; print "\n";

    I'm not really a human, but I play one on earth. flash japh

      Yes, I discovered pretty quickly that PDL is not the right tool.

      However, the Transpose a matrix of chars node was exactly what I needed.

      The other one you mentioned I did eventually find but it seemed more suited to numbers and the theory/explanation was way beyond my meager understanding of that stuff.

      Thanks again for the rapid and spot-on reply.

Re: using PDL->transpose on CSV file
by zentara (Cardinal) on Feb 08, 2006 at 17:33 UTC
    Here is an oldie but a goodie from Tachyon. Just have a data file called data.txt in the dir, and it will transpose it. It looks for strings separated by spaces, but you should be able to modify it to use with commas.(Or modify your data)
    #!/usr/bin/perl # maybe look at Math::Matrix # by Tachyon # The main requirement is that you have as much free disk space # for the temp files as the total file size. You will be limited # in the number of columns you can transpose by the number of open # file descriptors your Perl will let you have. It is very easy to # hack the logic to do N colunms per pass at the expense of 1 full # read of the input file per extra pass. Alternatively you could DBM # or tie a hash to a file and use the keys as pseudo file handles # and just append data to the values. # It should be really fast as we make a single pass through the input # data and then effectively just write it out (each temp file has one # full line in it). # transpose.pl #works but the temp files seems wasteful to me use strict; transpose90( "data.txt", "data-transpose.txt" ); sub transpose90 { my ( $infile, $outfile, $tmp ) = @_; $tmp ||= 'temp'; open IN, $infile or die "Can't read $infile $!\n"; # find number of columns and open a temp file for each local $_ = <IN>; chomp; my @data = split ' '; my $num_cols = $#data; my @fhs; open $fhs[$_], ">$tmp$_.txt" or die "Can't create temp file $tmp$_ + $!\n" for 0..$num_cols; print {$fhs[$_]} $data[$_], "\t" for 0..$num_cols; while( <IN> ) { chomp; @data = split ' '; print {$fhs[$_]} $data[$_], "\t" for 0..$num_cols; } close IN; close $fhs[$_] for 0..$num_cols; open OUT, ">$outfile" or die "Can't write $outfile $!\n"; for ( 0.. $num_cols ) { open IN, "$tmp$_.txt" or die "Can't read temp file $tmp$_ $!\n +"; print OUT scalar(<IN>), "\n"; close IN; unlink "$tmp$_.txt" } close OUT; }

    I'm not really a human, but I play one on earth. flash japh

      Thank you very much for the code; I will consider if it can be used to suite my needs. If not, I will hang on to it for a time when I can.

Re: using PDL->transpose on CSV file
by NiJo (Friar) on Feb 08, 2006 at 20:26 UTC
    I'd read it into a (tied) 2 dimensional array: a b c d And write it out exchanging inner and outer loop: a c b d