?
Solved

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

Posted on 2012-03-28
7
Medium Priority
?
323 Views
Last Modified: 2012-07-11
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
0
Comment
Question by:libertyforall2
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
Meir Rivkin earned 2000 total points
ID: 37781061
>>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
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 37781759
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
 

Author Comment

by:libertyforall2
ID: 37785251
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:libertyforall2
ID: 37785255
The .500 may refer for to 12 noon but the time is not needed and can be dropped.
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 37792887
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
 

Author Closing Comment

by:libertyforall2
ID: 38177044
Great
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Loops Section Overview

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question