Solved

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

Posted on 2012-03-28
7
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 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:sedgwick
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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:sedgwick
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

726 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