Hello,
I want to create a monthy average data table, by day, using the data from a 60 minute data table for the month and I can't seem to get it to work.
Start Condition 1[2022]:1[5%1]:1:
Stop Condition 1[2022]:1[5%5]::
edate("yyyy mm dd";1;1;1),edate("hh nn";1;1),Avg(4;24)
4 = the 60 minute average Wind Speed
Trying to start out simple and when I get it working, do the entire data string.
I wind up with this:
2022 05 01 00 00
2022 05 01 01 00
2022 05 01 02 00
2022 05 01 03 00
2022 05 01 04 00
2022 05 01 05 00
2022 05 01 06 00
2022 05 01 07 00
2022 05 01 08 00
2022 05 01 09 00
2022 05 01 10 00
2022 05 01 11 00
2022 05 01 12 00
2022 05 01 13 00
2022 05 01 14 00
2022 05 01 15 00
2022 05 01 16 00
2022 05 01 17 00
2022 05 01 18 00
2022 05 01 19 00
2022 05 01 20 00
2022 05 01 21 00
2022 05 01 22 00
2022 05 01 23 00 5.7
Thanks,
Dave
It's been a while since I intended to follow up on this. I'm sorry. Everything is operating perfectly with the latest USB to serial adapter we have with the FTDI geometry dash lite chip! We appreciate JDavis's proposal.
If your goal is to create a daily or monthly average from 60-minute data, you need to first group the hourly data by day and then calculate the average for each day. Right now, your output is still hourly because the averaging function Avg(4;24) is likely being applied incorrectly to a single row at a time instead of a group of 24 rows (one full day).
Here’s a general approach:
Ensure your time column is recognized as a datetime. You should have columns like:
Date Hour WindSpeed
2022-05-01 00 5.2
2022-05-01 01 6.1
...
Group by day. Depending on your software or scripting language:
In Excel, you can add a helper column: =DATE(YEAR(A2),MONTH(A2),DAY(A2)) to extract the day.
In Python/pandas:
df['DateOnly'] = df['Datetime'].dt.date
daily_avg = df.groupby('DateOnly')['WindSpeed'].mean()
Average the 24-hour blocks:
Excel: use AVERAGEIF based on your helper column.
=AVERAGEIF(DateOnlyRange, "2022-05-01", WindSpeedRange)
Other tools: make sure your averaging function is applied after grouping, not row by row.
Output the daily/monthly table: you’ll get something like:
2022-05-01 5.7
2022-05-02 6.2
2022-05-03 5.9
...
Monthly average: Once daily averages are calculated, you can group again by month and take the mean of the daily averages.
💡 Key point: Your current function Avg(4;24) is averaging only each row’s 24-hour window sequentially, but to get a proper daily average, you need to group all 24 rows of the same day first, then take the mean.