glly glly - 4 months ago 10
Perl Question

Transpose CSV data



I have been looking around and I can see how to turn columns into rows but not vice versa.

Example A

What I am trying to achieve is the following:

Input data



Site Activity,MI Report Outage 14:30 - 15:30,03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,0,...
Promotional Activity,0,0,0,0,0,...
Fiscal week,18,18,18,18,18,...
Week,31,31,31,31,31,...
Date,31/07/2016,01/08/2016,02/08/2016,03/08/2016,04/08/2016,.....


This is a snippet of the input data the actual data has over 200 fields (x axis)

The output I am trying to achieve is as follows:

Site Activity, Promotional Activity, Fisclal Week, Week, Date
MI Report Outage 14:30 - 15:30, 0, 18, 31,31/07/2016
03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop, 0, 18, 31, 01/08/2016
03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop, 0, 18, 31, 02/08/2016
etc etc


My current code to return the input format is as follows:

#!/usr/local/bin/perl

open file, "EDCNDC-Daily-Volume-Forecast_Ops Forecast by Shift.csv"
or die $!; #Opens File or returns error thrown ($!)

while ( <file> ) {

if ( $. < 4 ) {
print "";
}
elsif ( $. > 8 ) {
print "";
}
elsif ( $_ =~ /^\,\,\,\,/ ) {
print substr( $_, 4 );
}
else {
print $_;
}
}

close file


I am having to transform CSV into a usable format hence why I am limiting the lines that are returned. (lines 8 and 11)

Answer

use strict;
use warnings 'all';
use feature 'say';

my @data = map {
    chomp;
    [ split /,/ ];
} <DATA>;

for my $i ( 0 .. $#{ $data[0] } ) {
    say join ', ', map { $_->[$i] } @data;
}

__DATA__
Site Activity,MI Report Outage 14:30 - 15:30,03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,0,...
Promotional Activity,0,0,0,0,0,...
Fiscal week,18,18,18,18,18,...
Week,31,31,31,31,31,...
Date,31/07/2016,01/08/2016,02/08/2016,03/08/2016,04/08/2016,.....

output

Site Activity, Promotional Activity, Fiscal week, Week, Date
MI Report Outage 14:30 - 15:30, 0, 18, 31, 31/07/2016
03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop, 0, 18, 31, 01/08/2016
03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop, 0, 18, 31, 02/08/2016
0, 0, 18, 31, 03/08/2016
0, 0, 18, 31, 04/08/2016
..., ..., ..., ..., .....