Remove column and replace with other columns

Hi!
how do i to remove the date column and replace it with 24 other which represents the hours time filled with average hourly consumption over all days of the year for each city (name)?

the data set:

I think you have several options here, but it would be beneficial if you posted your data set (or an example).

I would start with looking at the node “Date&Time Difference”. You can use this node to calculate the difference, or the duration from a specified column, or previous row.

Alternatively, a solution could be using the GroupBy node and grouping your dataset by category, type, name, and date. In the aggregation settings choose minimum for the time column, and maximum from the time column. Take that output and again use the Date&Time Difference to calculate the number of hours/days/weeks (whatever you want) from the minimum & maximum columns created by the GroupBy node. After that, you could attach another GroupBy node to get the average at your set aggregation level.

Hopefully that makes sense. Again, feel free to post your dataset or a sample of it and someone can give you exactly the best solution.

1 Like

the dataset:

The date column is a string which i tried to convert with the string to date&time but it doesn’t include the time. If i proceed with the method you offered i will not be able to extract the time with Date&time Difference node.

Sorry to be picky… Can you upload just as a .csv or .zip? My computer can’t open a .bz2 file.

no problem.

Is this what you’re looking for?
image

This was done with the GroupBy node I mentioned in my post. It’s just a group by category and city (name), and averaging the energy consumption by date.

This is what I interpreted you wanting based off your initial post. If you want something else, I would probably need to see an example of how you expect the data to look after it’s gone through all the processing.

GroupBy Settings:


1 Like

Not really. The column date has to be removed and replaced with 24 columns representing each hour of the day, filled with the average energy consumption. I hope i made it a bit more understadable.

Thanks. So if I understand correctly, you want to pivot the information like this:

This is simply using the “Pivoting” node where the Group is name, the Pivots is time, and the Aggregation is Mean of energy

4 Likes

Oh this i didn’t know… Thank you so much!

2 Likes

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