Calculateing daily average from hourely value

[Shalombr]

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
…

[ScottF]

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:

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

[Shalombr]

It worked perfectly thank you very much ScottF

[Shalombr]

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

[ScottF]

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)

[Shalombr]

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?

[ScottF]

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.

[Shalombr]

@ScottF

Extract Date&Time Fields

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.

[ScottF]

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

[Shalombr]

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

[Patrick1974]

@Shalombr,

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

Kind regards,

Patrick

[Shalombr]

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).”

[Patrick1974]

That did the trick…

[Patrick1974]

Hey @Shalombr,

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

[Shalombr]

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

[Patrick1974]

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

Cheers,

Patrick