在Perl中转换CSV数据 [英] Transposing CSV data in Perl
问题描述
我是一个Perl初学者,目前正在使用一个Perl脚本来自动化我们的一些任务。我正在处理的一个脚本涉及从我们的系统提取性能数据,将其存储在CSV文件中并生成Excel图形。在这个脚本上工作几天后,我已经设法将提取的数据转换为CSV,但现在,我很难尝试转置数据!我看到这个线程(感谢dalton的脚本): stackoverflow线程,但我不能
I am a Perl beginner and currently working on a Perl script to automate some of our tasks. One script that I'm working on involves extracting performance data from our system, storing it in CSV files and generating Excel graphs. After a few days of working on this script, I have managed to get the extracted data into CSV, but now, I am having hard time in trying to transpose the data! I have seen this thread (thanks to dalton for the script): stackoverflow thread, but I can't seem to apply it in my case.
基本上,我的CSV文件包含每行的每日数据,列为一天中的小时数(24小时):
Basically, my CSV file contains a daily data per row, with the columns as the hours of the day (24 hours):
29-Aug-2013,3.68,3.63,3.75,3.65,3.65,3.11,3.34,2.74,2.83,2.52,3.19,4.24,3.84,3.61,3.69,2.96,2.76,2.91,3.70,3.82,3.70,3.54,2.54,3.90
30-Aug-2013,3.46,2.97,3.83,3.55,3.41,3.47,3.32,2.81,2.80,2.32,3.17,3.60,3.63,3.83,3.67,2.92,2.34,3.21,3.45,3.51,3.57,3.46,3.52,4.19
31-Aug-2013,3.19,3.50,4.01,3.91,3.71,3.33,3.20,2.95,2.90,2.37,3.07,3.48,2.86,3.29,3.22,2.52,1.83,2.83,3.54,3.49,3.62,3.59,3.54,3.31
01-Sep-2013,2.88,3.16,2.79,2.90,3.78,3.18,3.26,2.84,3.21,2.50,3.35,3.78,3.30,4.04,3.80,3.07,3.23,3.54,3.30,3.43,3.56,3.48,3.60,3.78
02-Sep-2013,3.28,2.92,3.89,3.78,3.54,3.09,3.08,2.79,2.87,2.43,2.70,3.64,3.79,3.88,3.88,3.28,2.90,3.37,3.25,3.60,3.45,3.39,2.84,4.07
03-Sep-2013,3.31,2.54,3.59,3.59,3.50,3.10,2.98,2.63,3.20,2.53,2.92,3.42,3.76,3.07,3.41,2.42,2.12,3.19,3.32,3.08,3.63,3.50,3.71,3.75
04-Sep-2013,3.64,3.48,2.86,3.57,3.68,3.53,3.34,2.89,2.79,2.64,3.30,4.04,4.17,3.70,3.81,2.96,3.41,3.48,3.66,3.05,3.23,3.41,3.15,4.31
想要转置它,使我将写入一个新的CSV文件的结果数据看起来像这样:
Now, I want to transpose it so that the resulting data that I will write to a new CSV file will look something like this:
Time,29-Aug-2013,30-Aug-2013,1-Sep-2013,2-Sep-2013,3-Sep-2013,4-Sep-2013
01:00,3.68,3.46,3.19,2.88,3.28,3.31,3.64
02:00,3.63,2.97,3.50,3.16,2.92,2.54,3.48
03:00,3.75,3.83,4.01,2.79,3.89,3.59,2.86
...
现在,我的脚本看起来像这样:
Now, my script looks like this:
my @rows = ();
my @transposed = ();
open F1,"D:\\Temp\\perf_data.csv";
while(<F1>) {
chomp;
push @rows, split [ /,/ ];
}
#print @rows;
for my $row (@rows) {
for my $column (0 .. $#{$row}) {
push(@{$transposed[$column]}, $row->[$column]);
}
}
for my $new_row (@transposed) {
for my $new_col (@{$new_row}) {
print $new_col, ",";
}
print "\n";
}
我甚至不能得到这个结果!有人可以帮我给我一些提示,我怎么能这样做?提前感谢!
I can't even get a result from this already! Can someone help give me some hints on how I can do this? Thanks in advance!
推荐答案
您提出了一个简单但严重的错误。
You made one simple, but critical mistake.
split [ /,/ ]
/ p>
should be
[ split /,/ ]
$ b b
split
的语法为
The syntax for split
is
split /PATTERN/, EXPR, LIMIT
其中后两个是可选的。你正在做的是传递一个匿名数组ref作为 PATTERN
,这很可能被字符串化为 ARRAY(0x54d658)
。结果是该行不被拆分,整行被推到数组上。稍后,这将导致 $ row
的解引用失败,并显示错误
Where the latter two are optional. What you are doing is passing an anonymous array ref as PATTERN
, which most likely gets stringified into something like ARRAY(0x54d658)
. The result is that the line is not split, and the whole line is pushed onto the array. Later on, that will cause the dereference of $row
to fail with the error
Can't use string ("29-Aug-2013,3.68,3.63,3.75,3.65,"...) as an ARRAY ref while "
strict refs" in use at foo.pl line 18, <F1> line 7.
这篇关于在Perl中转换CSV数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!