Solved

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

Posted on 2012-03-28
7
316 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now