I'm attempting to do something that is clear in my head, but my fingers are not complying.
I have a dataframe with customer contract data over time. The structure looks like this:
Account Type MonthlyRate ContractStart ContractEnd
<chr> <chr> <dbl> <date> <date>
1 A W 50 2018-01-01 2018-12-31
2 A X 75 2018-03-15 2019-03-14
3 B W 60 2018-02-28 2018-09-30
4 B X 90 2018-05-12 2019-08-11
5 B Y 45 2018-02-28 2018-09-30
6 C Y 50 2018-07-31 2019-04-30
7 D W 65 2019-01-01 2019-03-31
8 D Y 50 2018-09-01 2019-05-31
9 D Z 110 2018-08-22 2019-12-31
10 E Z 100 2018-10-01 2019-09-30
I want to create a report of revenues by calendar month, with the reporting period being the last day of each month. I would like to group by the Type field.
I've written a function that checks to see if the reporting date is within the contract period and, if so, the revenue equals MonthlyRate. If the contract ends mid-month, the revenue is prorated. My function looks like this:
# Assumes reporting date is at end of month and that dates are in POSIX format
monthly_revenue <- function(reporting_date, monthly_rate, start, end) {
contract_int <- interval(start, end) # Contract interval
# Calculate interval ending the last day of the month of contract end
end_of_month <- end
day(end_of_month) <- days_in_month(end)
end_of_month_int <- interval(start, end_of_month)
# Check if reporting date is within contract interval
if(reporting_date %within% contract_int) {
val <- 1 # bill for entire month
# If not within interval, check if contract is in its last month
} else if (reporting_date %within% end_of_month_int) {
val <- day(end) / days_in_month(end) # prorate monthly charges
} else { # Not within contract
val <- 0 # zero revenue
}
return (val * monthly_rate)
}
Now for the fun part. I want to create a sequence of month-end dates and use them in a for loop to create new columns for each date. Each row of the column contains the monthly revenue for the respective service. I can then gather the columns and create my report/plot using group_by and summarize.
My code for this section is as follows:
reporting_date_seq <- seq(as.Date("2018-02-01"), length = 24, by = "1 month") -1
for(i in reporting_date_seq){
revenue_for_month <- revenue_for_month %>%
rowwise() %>%
# mutate(i = monthly_revenue(as.Date(i, origin = "1970-01-01"), MonthlyRate, ContractStart, ContractEnd))
mutate_at(c(as.Date(i, origin = "1970-01-01"), "MonthlyRate", "ContractStart", "ContractEnd"),
monthly_revenue)
}
This results in an error:
Error in mutate_impl(.data, dots) :
Evaluation error: argument "start" is missing, with no default.
I'm assuming the problem is due to my trying to use the for-loop index as a function argument, but I'm not sure. There's probably a simpler, more elegant way to do what I'm trying, but I just don't know what it is.
Any advice (other than "walk west until your hat floats") is welcomed.