---
title: "disconnection"
output: 
  flexdashboard::flex_dashboard:
    orientation: rows
    social: menu
    source_code: embed
---

```{r setup, include=FALSE}

# Step 1: Read the spreadsheet
library(readxl)
library(dplyr)
library(openxlsx)
library(tidyr)
library(ggplot2)
library(lubridate)
library(flexdashboard)

##################################################################################################

################################PSCO dataset
data_disc_PS <- read_excel("C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG - Monthly Disconnection Data.xlsx", sheet = "Public Service", skip = 2,
                           col_names = c("Metrics","Fuel","January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
# Remove the first row
data_disc_PS <- slice(data_disc_PS, -1)

#removing the months we do not currently have data for 
#This will need to be changed as we get more data
columns_to_remove <- c("October", "November", "December")

# Remove the specified columns
data_disc_PS <- data_disc_PS[, !(names(data_disc_PS) %in% columns_to_remove)]


#making the data long form
df_disc_PS <- gather(data_disc_PS, key = "Month", value = "Value", -Metrics, -Fuel)

# Convert the Month column to a factor with correct ordering
df_disc_PS$Month <- factor(df_disc_PS$Month, levels = month.name)

#convert value to numerics
df_disc_PS$Value<-as.numeric(df_disc_PS$Value)

df_disc_PS<-df_disc_PS[complete.cases(df_disc_PS$Fuel), ]

#reading BH data
data_disc_BH <- read_excel("C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG - Monthly Disconnection Data.xlsx", sheet = "Black Hills", skip = 2,
                           col_names = c("Metrics","Fuel","January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
# Remove the first row
data_disc_BH <- slice(data_disc_BH, -1)

#removing the months we do not currently have data for 
#This will need to be changed as we get more data
columns_to_remove <- c("October", "November", "December")

# Remove the specified columns
data_disc_BH <- data_disc_BH[, !(names(data_disc_BH) %in% columns_to_remove)]


#making the data long form
df_disc_BH <- gather(data_disc_BH, key = "Month", value = "Value", -Metrics, -Fuel)

# Convert the Month column to a factor with correct ordering
df_disc_BH$Month <- factor(df_disc_BH$Month, levels = month.name)

#convert value to numerics
df_disc_BH$Value<-as.numeric(df_disc_BH$Value)

#removing NA from Fuel
df_disc_BH<-df_disc_BH[complete.cases(df_disc_BH$Fuel), ]



#reading CNG data
data_disc_CNG <- read_excel("C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG - Monthly Disconnection Data.xlsx", sheet = "CNG", skip = 2,
col_names = c("Metrics","Fuel","January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
# Remove the first row
data_disc_CNG <- slice(data_disc_CNG, -1)

#removing the months we do not currently have data for 
#This will need to be changed as we get more data
columns_to_remove <- c("October", "November", "December")

# Remove the specified columns
data_disc_CNG <- data_disc_CNG[, !(names(data_disc_CNG) %in% columns_to_remove)]


#making the data long form
df_disc_CNG <- gather(data_disc_CNG, key = "Month", value = "Value", -Metrics, -Fuel)

# Convert the Month column to a factor with correct ordering
df_disc_CNG$Month <- factor(df_disc_CNG$Month, levels = month.name)

#convert value to numerics
df_disc_CNG$Value<-as.numeric(df_disc_CNG$Value)

#removing NA from Fuel
df_disc_CNG<-df_disc_CNG[complete.cases(df_disc_CNG$Fuel), ]

#monthly data
data_monarr <- read_excel("C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG - Monthly Disconnection Data.xlsx", sheet = "Monthly Arrears")
data_monarr$Month<-data_monarr$...1
data_monarr$Company<-data_monarr$...2

data_monarr <- data_monarr[,-1]
data_monarr <- data_monarr[,-1]

data_monarr$Month<-as.Date(data_monarr$Month)
data_monarr$month1 <- month(data_monarr$Month)
data_monarr$year <- year(data_monarr$Month)

# Convert numeric month to letter month name
data_monarr$letter_month <- month.name[data_monarr$month1]

#Combine letter month and year
data_monarr$monthy <- paste(data_monarr$letter_month, data_monarr$year)

#convert value to numerics
data_monarr$Customers<-as.numeric(data_monarr$Customers)
data_monarr$Arrears<-as.numeric(data_monarr$Arrears)

data_monarr_psc<-data_monarr %>% filter(Company %in% c("Public Service"))
data_monarr_bhe<-data_monarr %>% filter(Company %in% c("BHE"))
data_monarr_bhcg<-data_monarr %>% filter(Company %in% c("BHCG"))
data_monarr_cng<-data_monarr %>% filter(Company %in% c("CNG"))


##################################################################################################

################################PSCO dataset
data_disc_PS_2023 <- read_excel("C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG - Monthly Disconnection Data.xlsx", sheet = "Public Service", skip = 2,
                                col_names = c("Metrics","Fuel","January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
# Remove the first row
data_disc_PS_2023 <- slice(data_disc_PS_2023, -1)

#removing the months we do not currently have data for 
#This will need to be changed as we get more data
columns_to_remove <- c("October", "November", "December")

# Remove the specified columns
data_disc_PS_2023 <- data_disc_PS_2023[, !(names(data_disc_PS_2023) %in% columns_to_remove)]


#making the data long form
df_disc_PS_2023 <- gather(data_disc_PS_2023, key = "Month", value = "Value", -Metrics, -Fuel)

# Convert the Month column to a factor with correct ordering
df_disc_PS_2023$Month <- factor(df_disc_PS_2023$Month, levels = month.name)

#convert value to numerics
df_disc_PS_2023$Value<-as.numeric(df_disc_PS_2023$Value)

df_disc_PS_2023<-df_disc_PS_2023[complete.cases(df_disc_PS_2023$Fuel), ]

###################################2022 data
# Specify the file path of the Excel spreadsheet
file_path <- "C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\23M-0042EG_PSCo 2022 Annual Disconnection Report Data File_Draft.xlsx"

# Read a specific page from the spreadsheet
sheet_name <- "A_B_H - Cust Cnt, Inv, Discnt"  # Replace "Sheet1" with the actual sheet name you want to read
xcel_disc_all<- read_excel(file_path, sheet = sheet_name, col_names = TRUE)
summary(xcel_disc_all)

# Step 5: Select specific columns for a new dataset
selected_columns <- c("MON_YEAR", "ZIP_CODE", "Customer Count (ALL)", "Customer Count (IQ)", "Disconnections for Non-Payment", "Disconnections for Non-Payment (IQ)")

new_data <- xcel_disc_all[, selected_columns]



# Step 3: Convert the date column to a date object
new_data$dates <- ymd(new_data$MON_YEAR)
new_data$month_column <- month(new_data$dates)
new_data$year_column <- year(new_data$dates)

# Specify the columns to remove
columns_to_remove <- c("MON_YEAR", "dates", "year_column")

# Remove the specified columns
new_data <- new_data[, !(colnames(new_data) %in% columns_to_remove)]

# Convert variables to the appropriate types
new_data$month_column <- as.factor(new_data$month_column)
new_data$ZIP_CODE<-as.factor(new_data$ZIP_CODE)

new_data$Disconnections_all<-rowSums(new_data[ , c("Disconnections for Non-Payment (IQ)", "Disconnections for Non-Payment")])

disconnections_2022<-new_data%>%
  group_by(month_column)%>%
  summarise(Disconnections_2022=sum(Disconnections_all, na.rm = TRUE))
            
# Read a specific page from the spreadsheet
sheet_name <- "G - Disconnect Notices"  # Replace "Sheet1" with the actual sheet name you want to read
new_data_dn<- read_excel(file_path, sheet = sheet_name, col_names = TRUE)


# Step 3: Convert the date column to a date object
new_data_dn$dates <- ymd(new_data_dn$MON_YEAR)
new_data_dn$month_column <- month(new_data_dn$dates)
new_data_dn$year_column <- year(new_data_dn$dates)

# Specify the columns to remove
columns_to_remove <- c("MON_YEAR", "dates", "year_column")

# Remove the specified columns
new_data_dn <- new_data_dn[, !(colnames(new_data_dn) %in% columns_to_remove)]

# Convert variables to the appropriate types
new_data_dn$month_column <- as.factor(new_data_dn$month_column)
new_data_dn$ZIP<-as.factor(new_data_dn$ZIP)

summary(new_data_dn)
#new_data_dn$Disconnectionnotice_all<-rowSums(new_data_dn[ , c("Total Count of Disconnect Notices", "Total Count of Disconnect Notices - (IQ)")])
new_data_dn$Disconnectionnotice_electric<-rowSums(new_data_dn[ , c("Count of disconnect notices Elec", "Count of disconnect notices Elec EA")])
new_data_dn$Disconnectionnotice_gas<-rowSums(new_data_dn[ , c("Count of disconnect notices Gasc", "Count of disconnect notices Gas EA")])
new_data_dn$Disconnectionnotice_combined<-rowSums(new_data_dn[ , c("Count of disconnect notices G&E", "Count of disconnect notices G&E EA")])

disconnectionnotice_2022<-new_data_dn%>%
  group_by(month_column)%>%
  summarise(Disconnectionnotice_electric_2022=sum(Disconnectionnotice_electric, na.rm = TRUE),
            Disconnectionnotice_gas_2022=sum(Disconnectionnotice_gas, na.rm = TRUE),
            Disconnectionnotice_combined_2022=sum(Disconnectionnotice_combined, na.rm = TRUE))
#Disconnectionnotice_all_2022=sum(Disconnectionnotice_all, na.rm = TRUE)
#restorations
# Read a specific page from the spreadsheet
sheet_name <- "I - Restored After Discnt"  # Replace "Sheet1" with the actual sheet name you want to read
new_data_rs<- read_excel(file_path, sheet = sheet_name, col_names = TRUE)


# Step 3: Convert the date column to a date object
new_data_rs$dates <- ymd(new_data_rs$MON_YEAR)
new_data_rs$month_column <- month(new_data_rs$dates)
new_data_rs$year_column <- year(new_data_rs$dates)

# Specify the columns to remove
columns_to_remove <- c("MON_YEAR", "dates", "year_column")

# Remove the specified columns
new_data_rs <- new_data_rs[, !(colnames(new_data_rs) %in% columns_to_remove)]

# Convert variables to the appropriate types
new_data_rs$month_column <- as.factor(new_data_rs$month_column)
new_data_rs$ZIP<-as.factor(new_data_rs$ZIP_CODE)

summary(new_data_rs)
new_data_rs$Restorations_all<-rowSums(new_data_rs[ , c("Residential Restored (Total)", "Residential Restored (IQ)")])

disconnection_restoration_2022<-new_data_rs%>%
  group_by(month_column)%>%
  summarise(Restoration_2022=sum(Restorations_all, na.rm = TRUE))


#######################################2022 BH data
# Specify the file path of the Excel spreadsheet
file_path <- "C:\\Users\\samathur\\Documents\\DORA\\Year 2 projects\\Disconnections\\BH_Disconnection_2022_clean.xlsx"

# Read a specific page from the spreadsheet
sheet_name <- "Total"  # Replace "Sheet1" with the actual sheet name you want to read
bh_total<- read_excel(file_path, sheet = sheet_name, col_names = TRUE)
sheet_name <- "IQ"  # Replace "Sheet1" with the actual sheet name you want to read
bh_IQ<- read_excel(file_path, sheet = sheet_name, col_names = TRUE)


#data cleaning for bh_total
#change column names for A - Number of Unique Residential customers by Class G - Total number of disconnection notices sent H - Total number of disconnections for nonpayment
new_column_names <- paste0("TotalRes_", 1:13)  # Generate the new column names
bh_total <- bh_total %>% rename_with(~new_column_names, starts_with("A - Number of Unique Residential customers by Class_"))

new_column_names <- paste0("DiscNotice_", 1:13)  # Generate the new column names
bh_total <- bh_total %>% rename_with(~new_column_names, starts_with("G - Total number of disconnection notices sent_"))

new_column_names <- paste0("Disc_", 1:13)  # Generate the new column names
bh_total <- bh_total %>% rename_with(~new_column_names, starts_with("H - Total number of disconnections for nonpayment"))

#select only the three variables data
selected_columns <- bh_total %>%
  select(starts_with("TotalRes_"), starts_with("DiscNotice_"), starts_with("Disc_"))

bh_total_f <- bh_total %>%
  select(all_of(c("ZC", colnames(selected_columns))))

bh_total_f <- bh_total_f %>%
  filter(ZC != "(blank)" & ZC != "Grand Total")

bh_total_trans <- bh_total_f %>%
  pivot_longer(cols = matches("^(TotalRes|DiscNotice|Disc)_\\d+$"),
               names_to = c("Variable", "Month"),
               names_pattern = "^(TotalRes|DiscNotice|Disc)_(\\d+)$") %>%
  filter(Month != "13") %>% #removes month 13 i.e. total
  pivot_wider(names_from = Variable, values_from = value,
              names_prefix = "")

#data cleaning for bh_IQ
#change column names for A - Number of Unique Residential customers by Class G - Total number of disconnection notices sent H - Total number of disconnections for nonpayment
new_column_names <- paste0("TotalRes_", 1:13)  # Generate the new column names
bh_IQ <- bh_IQ %>% rename_with(~new_column_names, starts_with("A - Number of Unique Residential customers by Class_"))

new_column_names <- paste0("DiscNotice_", 1:13)  # Generate the new column names
bh_IQ <- bh_IQ %>% rename_with(~new_column_names, starts_with("G - Total number of disconnection notices sent_"))

new_column_names <- paste0("Disc_", 1:13)  # Generate the new column names
bh_IQ <- bh_IQ %>% rename_with(~new_column_names, starts_with("H - Total number of disconnections for nonpayment"))

#select only the three variables data
selected_columns <- bh_IQ %>%
  select(starts_with("TotalRes_"), starts_with("DiscNotice_"), starts_with("Disc_"))

bh_IQ_f <- bh_IQ %>%
  select(all_of(c("ZC", colnames(selected_columns))))

bh_IQ_f <- bh_IQ_f %>%
  filter(ZC != "(blank)" & ZC != "Grand Total")

bh_IQ_trans <- bh_IQ_f %>%
  pivot_longer(cols = matches("^(TotalRes|DiscNotice|Disc)_\\d+$"),
               names_to = c("Variable", "Month"),
               names_pattern = "^(TotalRes|DiscNotice|Disc)_(\\d+)$") %>%
  filter(Month != "13") %>% #removes month 13 i.e. total
  pivot_wider(names_from = Variable, values_from = value,
              names_prefix = "")

bh_disc_all <- merge(bh_total_trans, bh_IQ_trans, by = c("ZC", "Month"))

# Rename the columns with suffix ".x" as "_t" and suffix ".y" as "_iq"
bh_disc_all <- bh_disc_all %>%
  rename_with(~gsub("\\.x", "_t", .), ends_with(".x")) %>%
  rename_with(~gsub("\\.y", "_iq", .), ends_with(".y"))

bh_disc_all <- bh_disc_all %>%
  filter(!is.na(ZC) & ZC != "(blank)")

# Convert variables to the appropriate types
bh_disc_all$Month <- as.factor(bh_disc_all$Month)
bh_disc_all$TotalRes_t <- as.numeric(bh_disc_all$TotalRes_t)
bh_disc_all$DiscNotice_t <- as.numeric(bh_disc_all$DiscNotice_t)
bh_disc_all$Disc_t <- as.numeric(bh_disc_all$Disc_t)
bh_disc_all$TotalRes_iq <- as.numeric(bh_disc_all$TotalRes_iq)
bh_disc_all$DiscNotice_iq <- as.numeric(bh_disc_all$DiscNotice_iq)
bh_disc_all$Disc_iq <- as.numeric(bh_disc_all$Disc_iq)


```

PSCO
=======================================================================

Row
-----------------------------------------------------------------------


```{r}
#disconnection notices

df_discnotice_PS <- df_disc_PS[df_disc_PS$Metrics == "Total number of disconnection notices sent (#)",]

ggplot(data = df_discnotice_PS, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +
  scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Disconnection Notices - PSCO",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()

```



```{r}

#Total number of disconnections for nonpayment (#)
df_disconnections_PS <- df_disc_PS[df_disc_PS$Metrics == "Total number of disconnections for nonpayment (#)", ]

ggplot(data = df_disconnections_PS, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Diconnections - PSCO",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()

```

Row
-----------------------------------------------------------------------


```{r}
#Total number of service restorations after disconnections for nonpayment (#)
df_restorations_PS <- df_disc_PS[df_disc_PS$Metrics == "Total number of service restorations after disconnections for nonpayment (#)", ]

ggplot(data = df_restorations_PS, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Restorations - PSCO",
       x = "Month",
       y = "Number if Customers") +
  theme_minimal()

```


```{r}
#Uncollectible accounts written off ($)
df_uncollectible_PS <- df_disc_PS[df_disc_PS$Metrics == "Uncollectible accounts written off ($)", ]

ggplot(data = df_uncollectible_PS, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Uncollectible accounts written off ($) - PSCO",
       x = "Month",
       y = "$") +
  theme_minimal()
```

PSCO arrearage - I
=======================================================================

Row
-----------------------------------------------------------------------


```{r}
#Total arrearages (sum all vintages)
# Filter rows with Metric 
filtered_data_arrdollar <- df_disc_PS %>% filter(Metrics %in% c("Dollar amount of arrearages by vintage (1-30 days) ($)",
                                                                 "Dollar amount of arrearages by vintage (31-60 days) ($)",
                                                                 "Dollar amount of arrearages by vintage (61-90 days) ($)",
                                                                 "Dollar amount of arrearages by vintage (91+ days) ($)"
                                                                 
      ))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrdollar$Value<-as.numeric(filtered_data_arrdollar$Value)
filtered_data_arrdollar_total<-
  filtered_data_arrdollar%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_no = sum(`Value`, na.rm = TRUE),
)

ggplot(data = filtered_data_arrdollar_total, aes(x = Month, y = total_arr_no, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total arrearages (sum all vintages)- PSCO",
       x = "Month",
       y = "$") +
  theme_minimal()
```




```{r}
#Total bills in arrears (sum all vintages)
# Filter rows with Metric 
filtered_data_arrcust <- df_disc_PS %>% filter(Metrics %in% c("Number of customers with an arrearage balance by vintage (1-30 days) (#)",
                                                                 "Number of customers with an arrearage balance by vintage (31-60 days) (#)",
                                                                 "Number of customers with an arrearage balance by vintage (61-90 days) (#)",
                                                                 "Number of customers with an arrearage balance by vintage (91+ days) (#)"
))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrcust$Value<-as.numeric(filtered_data_arrcust$Value)
filtered_data_arrcust_total<-
  filtered_data_arrcust%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_bill = sum(`Value`, na.rm = TRUE),
  )

ggplot(data = filtered_data_arrcust_total, aes(x = Month, y = total_arr_bill, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total bills in arrears (sum all vintages) - PSCO",
       x = "Month",
       y = "Customers") +
  theme_minimal()
```


Row
-----------------------------------------------------------------------




```{r}
filtered_data_arrdollar_elec <- filtered_data_arrdollar %>% filter(Fuel %in% c("Electric"))

ggplot(data = filtered_data_arrdollar_elec, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($) - Electric - PSCO",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```





```{r}
#gas
filtered_data_arrdollar_gas <- filtered_data_arrdollar %>% filter(Fuel %in% c("Gas"))

ggplot(data = filtered_data_arrdollar_gas, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($)- Gas - PSCO",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
  theme_minimal()+
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))
```

PSCO - II
=======================================================================


Row
-----------------------------------------------------------------------


```{r}
#combined
filtered_data_arrdollar_combined <- filtered_data_arrdollar %>% filter(Fuel %in% c("Combined"))

ggplot(data = filtered_data_arrdollar_combined, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($) - Combined - PSCO",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```





```{r}
#electric
filtered_data_arrcust_elec <- filtered_data_arrcust %>% filter(Fuel %in% c("Electric"))

ggplot(data = filtered_data_arrcust_elec, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#)- Electric - PSCO",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```


Row
-----------------------------------------------------------------------


```{r}

#gas
filtered_data_arrcust_gas <- filtered_data_arrcust %>% filter(Fuel %in% c("Gas"))

ggplot(data = filtered_data_arrcust_gas, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#) - Gas - PSCO",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```




```{r}
filtered_data_arrcust_combined <- filtered_data_arrcust %>% filter(Fuel %in% c("Combined"))

ggplot(data = filtered_data_arrcust_combined, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#) - Combined - PSCO",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```



Black Hills
=======================================================================

Row
-----------------------------------------------------------------------



```{r}
df_discnotice_BH <- df_disc_BH[df_disc_BH$Metrics == "Total number of disconnection notices sent (#)", ]

ggplot(data = df_discnotice_BH, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Diconnection Notices - BH",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```


```{r}
#Total number of disconnections for nonpayment (#)
df_disconnections_BH <- df_disc_BH[df_disc_BH$Metrics == "Total number of disconnections for nonpayment (#)", ]

ggplot(data = df_disconnections_BH, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Diconnections - BH",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```


Row
-----------------------------------------------------------------------



```{r}
df_restorations_BH <- df_disc_BH[df_disc_BH$Metrics == "Total number of service restorations after disconnections for nonpayment (#)", ]

ggplot(data = df_restorations_BH, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Restorations - BH",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```



```{r}
df_uncollectible_BH <- df_disc_BH[df_disc_BH$Metrics == "Uncollectible accounts written off ($)", ]

ggplot(data = df_uncollectible_BH, aes(x = Month, y = Value, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Uncollectible accounts written off ($) - BH",
       x = "Month",
       y = "$") +
  theme_minimal()
```

Black Hills Arrearage - I
=======================================================================

Column
-----------------------------------------------------------------------


```{r}
# Filter rows with Metric 
filtered_data_arrdollar <- df_disc_BH %>% filter(Metrics %in% c("Dollar amount of arrearages by vintage (1-30 days) ($)",
                                                                "Dollar amount of arrearages by vintage (31-60 days) ($)",
                                                                "Dollar amount of arrearages by vintage (61-90 days) ($)",
                                                                "Dollar amount of arrearages by vintage (91+ days) ($)"
                                                                
))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrdollar$Value<-as.numeric(filtered_data_arrdollar$Value)
filtered_data_arrdollar_total<-
  filtered_data_arrdollar%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_no = sum(`Value`, na.rm = TRUE),
  )

ggplot(data = filtered_data_arrdollar_total, aes(x = Month, y = total_arr_no, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total arrearages (sum all vintages) ($) - BH",
       x = "Month",
       y = "$") +
  theme_minimal()
```




```{r}
# Filter rows with Metric 
filtered_data_arrcust <- df_disc_BH %>% filter(Metrics %in% c("Number of customers with an arrearage balance by vintage (1-30 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (31-60 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (61-90 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (91+ days) (#)"
))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrcust$Value<-as.numeric(filtered_data_arrcust$Value)
filtered_data_arrcust_total<-
  filtered_data_arrcust%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_bill = sum(`Value`, na.rm = TRUE),
  )

ggplot(data = filtered_data_arrcust_total, aes(x = Month, y = total_arr_bill, color = Fuel, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total bills in arrears (sum all vintages) (#) - BH",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```


Black Hills Arrearage - II
=======================================================================


Row
-----------------------------------------------------------------------



```{r}
#by fuel by utility type for arrearage dollars
#electric
filtered_data_arrdollar_elec <- filtered_data_arrdollar %>% filter(Fuel %in% c("Electric"))

ggplot(data = filtered_data_arrdollar_elec, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($) - Electric - BH",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```




```{r}
#gas
filtered_data_arrdollar_gas <- filtered_data_arrdollar %>% filter(Fuel %in% c("Gas"))

ggplot(data = filtered_data_arrdollar_gas, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($) - Gas - BH",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```


Row
-----------------------------------------------------------------------



```{r}
#electric
filtered_data_arrcust_elec <- filtered_data_arrcust %>% filter(Fuel %in% c("Electric"))

ggplot(data = filtered_data_arrcust_elec, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#) - Electric - BH",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```




```{r}

#gas
filtered_data_arrcust_gas <- filtered_data_arrcust %>% filter(Fuel %in% c("Gas"))

ggplot(data = filtered_data_arrcust_gas, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#) - Gas - BH",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```







CNG
=======================================================================

Row
-----------------------------------------------------------------------



```{r}
df_discnotice_CNG <- df_disc_CNG[df_disc_CNG$Metrics == "Total number of disconnection notices sent (#)", ]

ggplot(data = df_discnotice_CNG, aes(x = Month, y = Value, group = Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Diconnection Notices - CNG",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```


```{r}
#Total number of disconnections for nonpayment (#)
df_disconnections_CNG <- df_disc_CNG[df_disc_CNG$Metrics == "Total number of disconnections for nonpayment (#)", ]

ggplot(data = df_disconnections_CNG, aes(x = Month, y = Value, group = Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Diconnections - CNG",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```


Row
-----------------------------------------------------------------------



```{r}
df_restorations_CNG <- df_disc_CNG[df_disc_CNG$Metrics == "Total number of service restorations after disconnections for nonpayment (#)", ]

ggplot(data = df_restorations_CNG, aes(x = Month, y = Value, group = Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Restorations - CNG",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```



```{r}
df_uncollectible_CNG <- df_disc_CNG[df_disc_CNG$Metrics == "Uncollectible accounts written off ($)", ]

ggplot(data = df_uncollectible_CNG, aes(x = Month, y = Value, group = Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Uncollectible accounts written off ($) - CNG",
       x = "Month",
       y = "$") +
  theme_minimal()
```





CNG Arrearage 
=======================================================================

Row
-----------------------------------------------------------------------



```{r}
# Filter rows with Metric 
filtered_data_arrdollar <- df_disc_CNG %>% filter(Metrics %in% c("Dollar amount of arrearages by vintage (1-30 days) ($)",
                                                                "Dollar amount of arrearages by vintage (31-60 days) ($)",
                                                                "Dollar amount of arrearages by vintage (61-90 days) ($)",
                                                                "Dollar amount of arrearages by vintage (91+ days) ($)"
                                                                
))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrdollar$Value<-as.numeric(filtered_data_arrdollar$Value)
filtered_data_arrdollar_total<-
  filtered_data_arrdollar%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_no = sum(`Value`, na.rm = TRUE),
  )

ggplot(data = filtered_data_arrdollar_total, aes(x = Month, y = total_arr_no, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total arrearages (sum all vintages) ($) - CNG",
       x = "Month",
       y = "$") +
  theme_minimal()
```




```{r}
# Filter rows with Metric 
filtered_data_arrcust <- df_disc_CNG %>% filter(Metrics %in% c("Number of customers with an arrearage balance by vintage (1-30 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (31-60 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (61-90 days) (#)",
                                                              "Number of customers with an arrearage balance by vintage (91+ days) (#)"
))

# Calculate the sum of values for Metric X, Y, and Z
filtered_data_arrcust$Value<-as.numeric(filtered_data_arrcust$Value)
filtered_data_arrcust_total<-
  filtered_data_arrcust%>%
  group_by(Fuel, Month)%>%
  summarise(total_arr_bill = sum(`Value`, na.rm = TRUE),
  )

ggplot(data = filtered_data_arrcust_total, aes(x = Month, y = total_arr_bill, group= Fuel)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Total bills in arrears (sum all vintages) (#) - CNG",
       x = "Month",
       y = "Number of Customers") +
  theme_minimal()
```




Row
-----------------------------------------------------------------------



```{r}
#by fuel by utility type for arrearage dollars

ggplot(data = filtered_data_arrdollar, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Arrearages by vintage ($) - CNG",
       x = "Month",
       y = "Dollar amount of arrearages ($)") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```




```{r}
ggplot(data = filtered_data_arrcust, aes(x = Month, y = Value, color = Metrics, group= Metrics)) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  labs(title = "Bills in arrears by vintage (#) - CNG",
       x = "Month",
       y = "Number of Customers with an arrearage balance") +
scale_color_discrete(labels=c('By vintage (1-30 days)', 'By vintage (31-60 days)','By vintage (61-90 days)','By vintage (91+ days)'))+
  theme_minimal()
```







Monthly Arrearage Data (PSCO)
=======================================================================


Row
-----------------------------------------------------------------------




```{r}
ggplot(data = data_monarr_psc, aes(x = month1, y = Customers, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears (Customers) - PSCO",
       x = "Month",
       y = "Monthly Arrears (#)",
       color = "Year") +
  theme_minimal()
```  

  


```{r}
ggplot(data = data_monarr_psc, aes(x = month1, y = Arrears, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears ($s) - PSCO",
       x = "Month",
       y = "Monthly Arrears ($)",
       color = "Year") +
  theme_minimal()
```






Monthly Arrearage Data (BH)
=======================================================================


Row
-----------------------------------------------------------------------




```{r}
ggplot(data = data_monarr_bhe, aes(x = month1, y = Customers, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears (Customers) - BHE",
       x = "Month",
       y = "Monthly Arrears (#)",
       color = "Year") +
  theme_minimal()
```  

  


```{r}
ggplot(data = data_monarr_bhe, aes(x = month1, y = Arrears, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears ($s) - BHE",
       x = "Month",
       y = "Monthly Arrears ($)",
       color = "Year") +
  theme_minimal()
```



Row
-----------------------------------------------------------------------




```{r}
ggplot(data = data_monarr_bhcg, aes(x = month1, y = Customers, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears (Customers) - BHCG",
       x = "Month",
       y = "Monthly Arrears (#)",
       color = "Year") +
  theme_minimal()
```  

  



```{r}
ggplot(data = data_monarr_bhcg, aes(x = month1, y = Arrears, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears ($s) - BHCG",
       x = "Month",
       y = "Monthly Arrears ($)",
       color = "Year") +
  theme_minimal()
```








Monthly Arrearage Data (CNG)
=======================================================================


Row
-----------------------------------------------------------------------




```{r}
ggplot(data = data_monarr_cng, aes(x = month1, y = Customers, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears (Customers) - CNG",
       x = "Month",
       y = "Monthly Arrears (#)",
       color = "Year") +
  theme_minimal()
```  

  



```{r}
ggplot(data = data_monarr_cng, aes(x = month1, y = Arrears, color = as.factor(year))) +
  geom_line() +   scale_y_continuous(labels = scales::comma_format(accuracy = 1)) +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Monthly Arrears ($s) - CNG",
       x = "Month",
       y = "Monthly Arrears ($)",
       color = "Year") +
  theme_minimal()

```