library (readr) library(reshape2) library(lubridate) # Read in Sense Apr 19 to Now download data Sense <- read_csv( "~/Documents/New Sense/1-hour data from Jan 01, 2019 to Jan 01, 2020.csv", col_types = cols_only( DateTime = col_datetime(format = "%Y-%m-%d %H:%M:%S"), Name = col_character(), kWh = col_number() ), locale = locale(tz = "US/Pacific"), skip = 1 ) attr(Sense$DateTime, "tzone") <- "US/Pacific" # Do a completeness check on raw exported data check <- seq( from = as.POSIXct(Sense$DateTime[1]), by = "hour", to = as.POSIXct(Sense$DateTime[nrow(Sense)]) ) MissingSenseHours <- check[!check %in% as.POSIXct(Sense$DateTime)] totals <- c("Solar Production", "Total Usage") Sense <- Sense[Sense$Name %in% totals, ] colnames(Sense) <- c("DateTime", "variable", "value") Sense <- dcast(Sense, DateTime ~ variable, sum, fill = 0) colnames(Sense) <- c("DateTime", "Solar", "Usage") Sense$Net <- Sense$Usage + Sense$Solar # Read in Billing Periods BillingCycle <- read_csv("~/Documents/New Sense/pgebillingcycle.csv", locale = locale()) BillingCycle$BillTo <- as.POSIXct (BillingCycle$BillTo, format = "%m/%d/%y", TZ = "PDT") # Calculate the number of days in each billing period BillingCycle$Days <- c(0, date(BillingCycle$BillTo[2:(nrow(BillingCycle))]) - date(BillingCycle$BillTo[1:(nrow(BillingCycle) - 1)])) # Align the billing periods to the incoming Sense data start <- which(Sense$DateTime[1] <= BillingCycle$BillTo) [1] end <- (which(Sense$DateTime[nrow(Sense)] < BillingCycle$BillTo) [1]) # Mark the start and finish months as partials if the # Sense start date is not equal to the the day after the previous billto date if (!date(Sense$DateTime[1]) == date(BillingCycle$BillTo[start - 1]) + 1) { BillingCycle$BillMonth[start] <- 'Partial Start' } # Sense end date is not equal to the billto date if (!date(Sense$DateTime[nrow(Sense)]) == date(BillingCycle$BillTo[end])) { BillingCycle$BillMonth[end] <- 'Partial End' } # Tag each hour in Sense with it's associated billing cycle name Sense$BillMonth <- 'Empty' for (i in start:end) { Sense$BillMonth[date((Sense$DateTime)) > date(BillingCycle$BillTo[i - 1]) & (date(Sense$DateTime) <= date(BillingCycle$BillTo[i]))] <- BillingCycle$BillMonth[i] } # Tag each hour with Weekend selector Sense$Weekend <- (weekdays(Sense$DateTime) == "Saturday" | weekdays(Sense$DateTime) == "Sunday") # Read in PGE Holidays and convert to POSIXct Holidays <- read_csv( "~/Documents/PGE Billing/pgeholidays.csv", col_names = TRUE, locale = locale() ) Holidays$DATE <- as.Date (Holidays$DATE, format = "%m/%d/%y", TZ = "PDT") Sense$Weekend[date(Sense$DateTime) %in% date(Holidays$DATE)] <- TRUE # Read in ToU/Tier Rate Schedules RatesDB <- readRDS("~/Documents/New Sense/Rates.rds") RatesDB$rateName <- gsub(";", "-", gsub(" ", "-", RatesDB$rateName)) Results <- data.frame(BillMonth=character, Period=integer(), Net=numeric(), Days=integer(), Cost=numeric(), RateName=character()) # Run through every rate schedule for (i in 1:nrow(RatesDB)) { # Create the base calculating block Calc <- Sense Calc$Period <- -1 # Elaborate the billing period each hour for (j in 1:nrow(Calc)) { if (Calc$Weekend[j]) { Calc$Period[j] <- RatesDB$energyWeekendSched[[i]][month(Calc$DateTime[j]), (hour(Calc$DateTime[j]) + 1)] } else { Calc$Period[j] <- RatesDB$energyWeekdaySched[[i]][month(Calc$DateTime[j]), (hour(Calc$DateTime[j]) + 1)] } } # Aggregate each billing period by ToU period Bills <- aggregate(Net ~ BillMonth + Period, Calc, FUN = sum) # Add the number of days in the billign cycle Bills <- merge (Bills, BillingCycle[, c(1, 3)]) # Apply the pricing tables to the each entry Table <- RatesDB$energyRateStrux[[i]]$energyRateTiers Bills$Cost <- 0 if (max(Bills$Period) + 1 == length(Table)) {print('Periods match rate table')} else {print('Periods and rate table mismatch')} # # Use simple calculation if the tier table only has one tier, ELSE, Do the ugly calculation if (nrow(Table[[1]])== 1) { print('Simple Calculation') for (j in 1:nrow(Bills)) { Price <- Table[[Bills$Period[j]+1]]$rate Bills$Cost[j] <- Price * Bills$Net[j] } } else { # Iterate through all the periods for each billing cycle, pulling out the tier table for the appropriate period # Set up the tier table for the entire billing period by multiplying by days for (j in 1:nrow(Bills)) { Days <- Bills$Days[j] TierSub <- Bills$Period[j] + 1 Max <- Days * Table[[TierSub]]$max Rates <- Table[[TierSub]]$rate # Replace the NA in the tier table with an articially large number so the vector algoritm will work Max [length(Max)] <- 10000000 Min <- c(0, Max[1:(length(Max) - 1)]) kWh <- rep(Bills$Net[j], times = length(Max)) # Calculate the amount of energy used in each tier EachTier <- pmax(pmin(kWh, Max) - Min, 0) Prices <- Rates * EachTier print(paste('Writing', sum(Prices), ' to Bills', j)) Bills$Cost[j] <- sum(Prices) } } # Aggregate the billing table # write_csv( dcast( Bills, BillMonth ~ Period, sum, value.var = 'Cost', margins = TRUE), paste("~/Documents/New Sense/CostTables/", RatesDB$rateName[i],".csv", sep = ""), na = "0") Bills$RateName <- RatesDB$rateName[i] Results <- rbind(Results,Bills) }