perl to do monthly averages on text file and delete all other columns.

Ok. I have a file with numerous columns. I want to create an output file that averages the data into monthly averages. Not sure how to do this in excel on a monthly basis. The first column is the date. The output should simply have the year and month YYMM in the first column and in the second column, the data should correspond to the data in column 5 (tsi_1au ) of the input but will be monthly averages instead of daily values. The other thing is that values of zero in the 5 column should not be included since it represents no data. So the number of days used to average for each month will vary if there are days with no data. All other columns will be deleted and the header information at the top is not needed. I will call the script by perl /path/script.pl The path to the input file will be called /path/2003irradiance.txt
2003irradiance.txt
libertyforall2Asked:
Who is Participating?
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
>>The first column is the date
so what's the .500 in the first column means?
i assume that 20030225.500 --> 25/02/2003, right? so what .500 refers to?
0
 
Meir RivkinFull stack Software EngineerCommented:
here's the perl script, change the file path of your data file.
i've attached the output of this script.

cheers

use warnings;  
use Time::Piece;

open (FH, 'c:\temp\perl\2003irradiance.txt') or die "Can't open $file for read: $!";
my @file = <FH>;
close FH or die "Cannot close $file: $!"; 

open (OUTPUTFILE, '>c:\temp\perl\output.txt');
  

my $year=0;
my $month=0;
my $counter=0;

foreach(@file){
 	if(substr($_, 0, 1) ne ';'){
		
		@tokens = split(/ /, $_);
		$date = $tokens[0];
		$date =~ s/.500//g;
		my $time = Time::Piece->strptime($date, "%Y%m%d %H:%M");
		if($year==0 and $month==0){
			$year = $time->year;
			$month = $time->month;
		}
		$sum = $tokens[4];
		if($sum ne "" and $sum > 0){
		
			if($month eq $time->month and $year eq $time->year){
				$sum = $sum + $tokens[4];	
				$counter++;
			}else{
				$avg = $sum/$counter;
				print OUTPUTFILE "$year\t$month\t$avg\n";
				
				$year = $time->year;
				$month = $time->month;
				$counter=1;				
			}
		}
	}
}

close (OUTPUTFILE); 

Open in new window

output.txt
0
 
libertyforall2Author Commented:
I must be lacking some module for that command.

uila% perl irradiance2003.pl
Can't locate Time/Piece.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at irradiance2003.pl line 2.
BEGIN failed--compilation aborted at irradiance2003.pl line 2.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
libertyforall2Author Commented:
The .500 may refer for to 12 noon but the time is not needed and can be dropped.
0
 
Meir RivkinFull stack Software EngineerCommented:
for start u need to install module Time::Piece:
http://perldoc.perl.org/Time/Piece.html
Time::Piece is a core module which was installed along with Strawberry Perl installation (open-source perl implementation for windows).

which IDE you use to run the perl scrips?
0
 
libertyforall2Author Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.