Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How do I create a row for every hour between two dates?

Highlighted

How do I create a row for every hour between two dates?

New Contributor

I have daily data that looks like this:

Date Hour Name Transaction_Count

1/1 1 Joe 2

1/1 12 Joe 1

1/1 17 Joe 5

...

I want to explode the data to look like this such that missing Hour observatons with a value of '0' are inserted:

1/1 1 Joe 2

1/1 2 Joe 0

1/1 3 Joe 0

1/1 4 Joe 0

1/1 5 Joe 0

1/1 6 Joe 0

1/1 7 Joe 0

1/1 8 Joe 0

1/1 9 Joe 0

1/1 10 Joe 0

1/1 11 Joe 0

1/1 12 Joe 1

1/1 13 Joe 0

1/1 14 Joe 0

1/1 15 Joe 0

1/1 16 Joe 0

1/1 17 Joe 5

1/1 18 Joe 0

1/1 19 Joe 0

1/1 20 Joe 0

1/1 21 Joe 0

1/1 22 Joe 0

1/1 23 Joe 0

1/1 24 Joe 0


What is the best way to insert missing records for a time series?