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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Meir RivkinFull 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.