convert a csv file to a text file average minute averages to 3 hourly averages using perl or shell

If I have a file attached that looks the one attached. It may have EITHER minute or hourly averages AND ignoring NEGATIVE values OR values 999 and -999. I want it converted to three hourly averages in the output text file.
Who is Participating?
Consider this code; no conversion from CSV to TXT is necessary, as tel2 correctly pointed.

use strict;
use warnings FATAL => 'all';

my $file = shift;

open(F, $file);
my @count_3 = ();
my $working_date = '';
my $working_time = '';
my $working_hour = 0;
my @avgs = ();
use Data::Dumper;
use List::Util qw(sum);

sub convert_hour($$) {
	my $hour = shift;
	my $minutes = shift;
	return 60 * $hour + $minutes;

while (<F>) {
	next unless /\S/;
	my (@data) = split(/\,/);
	next unless $data[1] =~ m|(\d+/\d+/\d+) (\d+)\:(\d\d)|;
	my $current_date = $1;
	my $current_time = "$1 $2:$3";
	my $current_hour = convert_hour($2,$3);
	next if $data[2] < 0 || $data[2] == 999;
	if ($current_date ne $working_date) {
		$working_date = $current_date;
		$working_time = $current_time;
		$working_hour = $current_hour;
		@count_3 = ($data[2]);
	if ($current_hour >= $working_hour + 180 && @count_3 >= 3) {
			[$data[0], $working_time, sum(@count_3)/@count_3],
		@count_3 = ();
		$working_time = $current_time;
		$working_hour = $current_hour;
	push(@count_3, $data[2]);
print join(
	"\n", map(
		join(" ",@{$_}),
), "\n";

Open in new window

Let me clarify:
You want to convert values for every date to three average:
One between 00:00 and 07:59
One between 08:00 and 15:59
One between 16:00 and 23:59
Ignoring negative values and 999 ?

libertyforall2Author Commented:
I want values for 3 hour increments. 00:00 to 02:59, 03:00 to 05:59, 6:00 to 7:59 and so on until 21:00 to 23:59. If only hours are given, it will be 0 -2, 3-5, 6-8, and so on until 21 to 23. If there are less than three values because a negative value , 999, or -999 is removed, the average we be the next 3, i.e., if there is 0 1, & 3 hours because 2 is removed, then average 0, 1, & 3. If there are fewer than 3 for the final average then you can ingore that value.
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

and what would the average hour refer to? The first one in the list? What happens if the three are not 0,1,3 but 0,9,10?
libertyforall2Author Commented:
Average the first three beginning with zero or the first data point per day. If less than three, ignore that data pint. If the first three are 0, 9, & 10 then average them then start the next three with 11, 12, & 13 or whatever the next three are as long as there are at least three.
libertyforall2Author Commented:
Since this requires several steps, the first step is the most critical. How would I convert the file to a text file?
Hi lfa2,

How would I convert the file to a text file?
A CSV (Comma Separated Values) file is a type of text file.  If you open it with Notepad, Wordpad, etc, you should see the text.  However, your particular CSV file starts with a few chars which are not plain text, and if you don't require them, the script could just ignore them.  I expect CSV will be a good format for the Perl script to work directly from, without any need to convert it first.

Are you with me?

Regarding averages, I think the kinds of questions being asked have come up before for you recently lfa2.  I suggest you make these things clear up front in future, so experts can spend less time getting the spec's out of you, and more on giving you a solution.  In the long run you'll save yourself time, and get your answer faster.

libertyforall2Author Commented:
Works great! Thanks.
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.