Link to home
Start Free TrialLog in
Avatar of libertyforall2
libertyforall2Flag for United States of America

asked on

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
Avatar of parparov
parparov
Flag of United States of America image

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?
Avatar of libertyforall2

ASKER

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.
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?
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.
Since this requires several steps, the first step is the most critical. How would I convert the file to a text file?
SOLUTION
Avatar of tel2
tel2
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works great! Thanks.