Calculateing daily average from hourely value

Hi .
Help needed
As shown below I have a table with “date & time” and “value” for each hour and i would like to calculate the daily average (sum of all hourly values divided by 24 hour) on a new column. can you please help?

Existing data
Date & time value
2016-04-22T17 12
2016-04-22T18 -3
2016-04-22T19 12
2016-04-22T20 -10
2016-04-22T21 28

I want to create such table

Date & time Daily Average
2016-04-22 11
2016-04-23 -3
2016-04-24 0
2016-04-25 7
2016-04-26 22

Hi @Shalombr -

Assuming your date and time values are already in KNIME Date&Time Format, you can use the Extract Date&Time Fields node to identify the day of month. Then, use the GroupBy node to calculate your average value over the day of month.

Here’s a simple workflow example:

2018-05-14%2012_27_40-KNIME%20Analytics%20Platform

ExtractDateTimeAndAverage.knwf (8.7 KB)
TestDateTimeAvg.xlsx (10.7 KB)

2 Likes

It worked perfectly thank you very much ScottF

One more question please.
Based on the above method i now can see the daily average for the 30 days but it won’t help me to put the value in one column for each instances for comparison .
I have 3 years data (3x12 months) With in a day i may have hundreds of instances, I would like to create a new column that shows the daily average on each row (instance) then on another new column using rule engine node i would like to compare (actual value vs daily average) and i want to assign if the actual value is above the daily average i want to call it “High” below daily average “Low” equal to average " Expected" .
But in order to do that i must have column with fixed value for each month (the average value) for each month (total of 36 different months).

I want to create some thing like the below sample data for nearly 800,000 rows of data

Date and time Actual value Average Compared to average
31Oct 2016 22 20 12 High
31Oct 2016 23 10 12 Low
31Oct 2016 00 6 12 Low
1Nov 2016 01 15 15 Expected
2Nov 2016 02 35 15 High
32Nov 2016 02 -5 15 Low

Thank you again

I think this is what you’re asking for, but let me know if it’s not.

ExtractDateTimeAndAverage2.knwf (14.0 KB)
TestDateTimeAvg2.xlsx (10.8 KB)

Wow! I really appreciate your support …That was what i was asking for. Trying to use the workflow in my data I got an error message though.

Using the “String to Date/Time” Node I have converted to “Date” Column in to Knime date_time format (screen copy attached) but when i try to connect the node to the " Extract Date/Time (Legacy)" node, I got the error message on the attached picture (no column in spec compatible to “LocalDateValue” … .
What did i miss?

dateformaterror

You probably don’t want to be using any legacy nodes at all. Those are provided for backwards compatibility with previous versions of KNIME. Try using the Extract Date&Time Fields node instead.

i tried both directly connecting the data to “Extract Date&Time Fields” or " Extract Date/Time (Legacy)"but in both cases i am getting the same error.

1 Like

Is it possible to post a workflow with some sample data?

Sampledailyaverage.knwf (8.3 KB)sample2.xlsx (11.4 KB)

@Shalombr,

do you mind to share the CSV file instead of the XLSX since your workflow uses the file reader node.

Kind regards,

Patrick

Hi Patrick
I was trying to upload the CSV file but i got the following error message i there any other way i can send it ?
“Sorry, the file you are trying to upload is not authorized (authorized extensions: jpg, jpeg, png, gif, knar, knwf, zip, xls, tiff, tif, xlsx, txt, log, lsm, doc, docx, json, xml).”

That did the trick…

2 Likes

Hey @Shalombr,

I usually ZIP the files before upload. That’s an allowed file format and reduces upload time…

:slight_smile:

Ok,
It worked well, Thank you very much for your support Patrick1974 and ScottF.

2 Likes

you’re welcome! Mark my post as “correct answer” so other people will find it more easy.

Cheers,

Patrick

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.