Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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.
summitdata.csv
0
libertyforall2
Asked:
libertyforall2
  • 4
  • 3
2 Solutions
 
parparovCommented:
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 ?

Right?
0
 
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.
0
 
parparovCommented:
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?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
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.
0
 
libertyforall2Author Commented:
Since this requires several steps, the first step is the most critical. How would I convert the file to a text file?
0
 
tel2Commented:
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.

Thanks.
tel2
0
 
parparovCommented:
Consider this code; no conversion from CSV to TXT is necessary, as tel2 correctly pointed.
#!/usr/bin/perl

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/;
	chomp;
	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]);
		next;
	}
	if ($current_hour >= $working_hour + 180 && @count_3 >= 3) {
		push(
			@avgs,
			[$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(" ",@{$_}),
	 @avgs)
), "\n";

Open in new window

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

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now