How to expand rows of date by month to data by hour

I have economic data by year/month that I am preparing to join with other data by year/month/day/hour, so I would like to expand the economic data rows to impute the same economic data for the year/month to every year/month/day/hour of the other table

I did find “Create Date&Time Range” and was able to to create all of the days and hours in the days for the yyyy:MM ranges that I have filtered to. That’s as far as I’ve been able to get.

EconomicDataImportPrep.knwf (14.7 KB)
Montgomery_Bucks_Chester_Metro.xls (156 KB) .

Hello @WilliamStewart,

I have checked your flow and data but not sure how would your desired output look like. You want to have same data for each hour of the day or missing values or?

Br,
Ivan

I would like to add rows to fill out every hour of the months within the range, then impute the economic data of that month to every hour of that month. So if a month had 3.9% unemployment, then every hour of every day for that month would have 3.9% unemployment. I went down the path of creating an empty datetime object with the datetimes in the range I’m looking for, though I don’t know if that is a deadend or not.

Hi @WilliamStewart

Here is my solution. Essentially for each date, create 24 rows then counter generation 0 through to 23 and then shift the time from 00:00:00 for each hour (counter generated). I’m sure there is a cleaner way of doing this though but I’ve not been around for a while and fancied a quick challenge :smile:

EconomicDataImportPrep.knwf (36.5 KB)

P.S my spelling in the workflow is terrible, apologies! :crazy_face:

3 Likes

Hello @WilliamStewart,

if I got it right I would extract year and month from both input data and Create Date&Time Range node followed by left outer join in Joiner (Labs) node. Take a look at attached example:
EconomicDataImportPrep_ipazin.knwf (62.4 KB)

Additionally I suggest to keep (smaller) data within workflow folder and use workflow relative paths.

Br,
Ivan

2 Likes

@Matt_D , splendid problem solving! This gives me new ways and perspectives on how to approach tricky challenges like this. Well done!

2 Likes

@ipazin, this is an elegant solution, showing the depth of capability of Knime in nodes I had not yet discovered. Thank you!

Update: It seems I cannot give Solution status to more than one solution, but know that I appreciate the skillful and resourceful input!

2 Likes

Glad you find it useful @WilliamStewart. And don’t worry about Solution button :slight_smile:
Ivan

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