background
I have 3 long-formatted datasets. Each shares the same columns: dates, multiple grouping columns like country+state+version+item, and a value column. Each dataset is unique in that the dates are recorded at a different time granularity (hourly, daily, monthly) and tracks different items.
goal
My desire is to create three new datasets with all items in all granularities -- but my trouble is with the breaking down of monthly/daily files to the hourly level.
example data.tables
Here is a simplified example of 2 months with only one group column, "country".
library(data.table)
library(lubridate)
dat_hourly <- as.data.table(expand.grid(dates=seq(ymd_h("2020-07-01 0"), ymd_h("2020-08-31 23"),"hour"),
country = state.abb[1:2],
item = c("visits","clicks")))
dat_daily <- as.data.table(expand.grid(dates=seq(ymd("2020-07-01"), ymd("2020-08-31"),"day"),
country = state.abb[1:2],
item = c("chats","conversations")))
dat_monthly <- as.data.table(expand.grid(dates=c(ymd("2020-07-01"), ymd("2020-08-01")),
country = state.abb[1:2],
item = c("deals")))
set.seed(101)
dat_hourly[,value:=rnorm(.N, 10,2)]
dat_daily[,value:=runif(.N, 10,100)]
dat_monthly[,value:=rpois(.N,50)]
Rolling up hourly to daily + monthly, or daily to monthly is easy in data.table language with DT[,sum(value),by=list(floor_date(dates,"day"))] or floor_date(dates,"month")
However, to bring monthly and daily to hourly level, is harder. Assumptions must be made. In this case, I would like to assume that "chats", "conversations" and "deals" can be apportioned out to hourly using the hourly "visits" item. Here i save the variable at the hourly, daily and monthly granularity.
visits_hourly <- dat_hourly[item=="visits"]
visits_daily <- dat_hourly[item=="visits", sum(value), by = .(dates=floor_date(dates,"day"), country, item)]
visits_monthly <- dat_hourly[item=="visits", sum(value), by = .(dates=floor_date(dates,"month"), country, item)]
The math for a breaking a monthly item down to hourly would be to do (monthly_deals / monthly visits) * hourly visits = hourly deals and for daily, (daily_chats / daily visits) * hourly visits = daily chats
However, I can't achieve this at scale. This is the function working for a single month-country-item combination. My dataset has hundreds, with more columns to group than just country.
what I’ve done so far: manually doing one breakdown
#(AK July monthly deals / AK July monthly visits) * AK July hourly visits = AK July hourly deals
AK_monthly_deals_jul <- dat_monthly[country=="AK" & dates==ymd("2020-07-01"),value]
AK_monthly_visits_jul <- visits_monthly[country=="AK" & dates==ymd("2020-07-01"),V1]
AK_hourly_visits_jul <- visits_hourly[country=="AK" & floor_date(dates,"month")==ymd("2020-07-01")]
AK_hourly_deals_jul_value <- (AK_monthly_deals_jul / AK_monthly_visits_jul) * AK_hourly_visits_jul$value
AK_hourly_deals_jul <- AK_hourly_visits_jul # copy the hourly frame over
AK_hourly_deals_jul[,c("item","value") := .("deals",AK_hourly_deals_jul_value)] # but update item+value
# will be equal
AK_hourly_deals_jul[,sum(value)]
AK_monthly_deals_jul
what I imagine is possible
I could loop this for every country-item-month combo, and then again for every country-item-day combo, but it seems inefficient and clunky.
Perhaps storing the monthly and daily scalers (i.e, (monthly_items / monthly visits)) upfront in long format alongside values would be the best way, but then I can't figure out again how best to use DT with keys to join and do math across different data.tables.
How might I proceed with this? Open to a better title for this question.