Project2

Electricity Consumption Across Different States and Regions

Bernimo Mbu

from AydemPerakende

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(extrafont)
Registering fonts with R
library(dslabs)
library(dplyr)
library(ggplot2)
library(ggthemes)
library(ggrepel)
library(readr)
library(ggfortify)
library(plotly)

Attaching package: 'plotly'

The following object is masked from 'package:ggplot2':

    last_plot

The following object is masked from 'package:stats':

    filter

The following object is masked from 'package:graphics':

    layout
library(scales)

Attaching package: 'scales'

The following object is masked from 'package:purrr':

    discard

The following object is masked from 'package:readr':

    col_factor
library(highcharter)
Registered S3 method overwritten by 'quantmod':
  method            from
  as.zoo.data.frame zoo 

Attaching package: 'highcharter'

The following object is masked from 'package:dslabs':

    stars
library(RColorBrewer) 
info <- read_csv('state_summary.csv')
Rows: 51 Columns: 18
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr  (3): state, state_name, census_region
dbl (15): residential_price_2010, residential_price_2019, residential_price_...

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

My topic for this 2nd project will be about the differing electricity bills in each state, the data being provided by the US Information Administration. I chose this topic because the electricity bill in my house (7 people) has usually been quite high for as long as I could remember, so I wish to research if this is uncommon compared to other states in the U.S. There are 18 variables in my dataset, 3 of which are categorical, and the other 15 are quantitative. For my project I will specifically being using the state, census region, residential price columns as well as the average monthly bill column.

  • When the dataset describes something “last 12 months”, its referring to this year. it is 2026 information, however specific year data includes 2010 and 2019

  • “bill_pct_change” is referring to the percent change from a time period to this year, however I won’t be using these columns

Checking out the data, there is no need to remove any missing values, the entire table is filled out, which is another reason I picked this dataset. The information from other columns also will not interfere with what I am analyzing. All I would need to do is use the select function (maybe filter) to allocate specific data for each visualization

head(info)
# A tibble: 6 × 18
  state state_name   census_region residential_price_2010 residential_price_2019
  <chr> <chr>        <chr>                          <dbl>                  <dbl>
1 DC    District of… South                           14.0                   13.0
2 CA    California   West                            14.8                   19.2
3 ME    Maine        Northeast                       15.7                   17.9
4 MD    Maryland     South                           14.3                   13.1
5 NY    New York     Northeast                       18.7                   17.9
6 NJ    New Jersey   Northeast                       16.6                   15.8
# ℹ 13 more variables: residential_price_last12mo <dbl>,
#   all_sectors_price_2019 <dbl>, all_sectors_price_last12mo <dbl>,
#   avg_monthly_bill_2019_usd <dbl>, avg_monthly_bill_last12mo_usd <dbl>,
#   avg_monthly_kwh_last12mo <dbl>, residential_pct_change_since_2019 <dbl>,
#   residential_real_pct_change_since_2019 <dbl>,
#   residential_pct_change_since_2010 <dbl>, bill_pct_change_since_2019 <dbl>,
#   commercial_sales_pct_change_since_2019 <dbl>, …
tail(info)
# A tibble: 6 × 18
  state state_name   census_region residential_price_2010 residential_price_2019
  <chr> <chr>        <chr>                          <dbl>                  <dbl>
1 ND    North Dakota Midwest                         8.13                   10.3
2 NE    Nebraska     Midwest                         8.94                   10.8
3 KS    Kansas       Midwest                        10.0                    12.7
4 AK    Alaska       West                           16.3                    22.9
5 IA    Iowa         Midwest                        10.4                    12.5
6 NV    Nevada       West                           12.4                    12  
# ℹ 13 more variables: residential_price_last12mo <dbl>,
#   all_sectors_price_2019 <dbl>, all_sectors_price_last12mo <dbl>,
#   avg_monthly_bill_2019_usd <dbl>, avg_monthly_bill_last12mo_usd <dbl>,
#   avg_monthly_kwh_last12mo <dbl>, residential_pct_change_since_2019 <dbl>,
#   residential_real_pct_change_since_2019 <dbl>,
#   residential_pct_change_since_2010 <dbl>, bill_pct_change_since_2019 <dbl>,
#   commercial_sales_pct_change_since_2019 <dbl>, …
info
# A tibble: 51 × 18
   state state_name  census_region residential_price_2010 residential_price_2019
   <chr> <chr>       <chr>                          <dbl>                  <dbl>
 1 DC    District o… South                          14.0                   13.0 
 2 CA    California  West                           14.8                   19.2 
 3 ME    Maine       Northeast                      15.7                   17.9 
 4 MD    Maryland    South                          14.3                   13.1 
 5 NY    New York    Northeast                      18.7                   17.9 
 6 NJ    New Jersey  Northeast                      16.6                   15.8 
 7 PA    Pennsylvan… Northeast                      12.7                   13.8 
 8 OH    Ohio        Midwest                        11.3                   12.4 
 9 WA    Washington  West                            8.04                   9.71
10 IL    Illinois    Midwest                        11.5                   13.0 
# ℹ 41 more rows
# ℹ 13 more variables: residential_price_last12mo <dbl>,
#   all_sectors_price_2019 <dbl>, all_sectors_price_last12mo <dbl>,
#   avg_monthly_bill_2019_usd <dbl>, avg_monthly_bill_last12mo_usd <dbl>,
#   avg_monthly_kwh_last12mo <dbl>, residential_pct_change_since_2019 <dbl>,
#   residential_real_pct_change_since_2019 <dbl>,
#   residential_pct_change_since_2010 <dbl>, …

SELECTING DATA FOR THE 1ST VISUALIZATION

vis1data <- info %>%
  select(state, state_name, avg_monthly_bill_last12mo_usd, avg_monthly_kwh_last12mo)
head(vis1data)
# A tibble: 6 × 4
  state state_name           avg_monthly_bill_last12mo_…¹ avg_monthly_kwh_last…²
  <chr> <chr>                                       <dbl>                  <dbl>
1 DC    District of Columbia                         153.                    630
2 CA    California                                   161.                    487
3 ME    Maine                                        156.                    532
4 MD    Maryland                                     199.                    945
5 NY    New York                                     167.                    587
6 NJ    New Jersey                                   158.                    664
# ℹ abbreviated names: ¹​avg_monthly_bill_last12mo_usd,
#   ²​avg_monthly_kwh_last12mo
linear1 <- lm(avg_monthly_bill_last12mo_usd ~ avg_monthly_kwh_last12mo, data = vis1data)
summary(linear1)

Call:
lm(formula = avg_monthly_bill_last12mo_usd ~ avg_monthly_kwh_last12mo, 
    data = vis1data)

Residuals:
    Min      1Q  Median      3Q     Max 
-49.256 -18.888   4.049  15.241  76.159 

Coefficients:
                          Estimate Std. Error t value Pr(>|t|)    
(Intercept)              1.437e+02  1.583e+01   9.072 4.61e-12 ***
avg_monthly_kwh_last12mo 7.667e-03  1.792e-02   0.428    0.671    
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 26.1 on 49 degrees of freedom
Multiple R-squared:  0.003723,  Adjusted R-squared:  -0.01661 
F-statistic: 0.1831 on 1 and 49 DF,  p-value: 0.6706

VISUALIZATION 1

visual_1 <- ggplot(vis1data,
                   aes(x = avg_monthly_kwh_last12mo,
                       y = avg_monthly_bill_last12mo_usd,
                       size = avg_monthly_kwh_last12mo,
                       color = factor(state_name),
                       text = paste("STATE:", state_name,
                                    "\nAverage KWH:", avg_monthly_kwh_last12mo))) +
  geom_point(alpha = 0.7) +
  scale_color_viridis_d(option = "turbo") + #gives every geom point a different color
  labs(title = "Average KWH Against the Average Bill (Monthly)",
       caption = "Provided by the U.S. Energy Information Administration",
       x = "Average Energy Used Monthly (KWH)",
       y = "Average Monthly Bill (USD)",
       color = "State") +
  theme_classic() +
  theme(legend.title = "element_text"(size = 8),
        legend.text = element_text(size = 5),
        legend.key.size = unit(0.3, "cm"))+
  theme(           
    legend.direction = "vertical",
    legend.box = "horizontal",
    legend.position = "right",
    legend.justification = c(0, 1)
  ) #flips the legend vertically so that it's non-intrusive

ggplotly(visual_1, tooltip = "text")

SELECTING DATA FOR THE 2ND VISUALIZATION

vis2data <- info %>%
  select(state, state_name, census_region, residential_price_rank_last12mo, residential_price_last12mo)
head(vis2data)
# A tibble: 6 × 5
  state state_name   census_region residential_price_ra…¹ residential_price_la…²
  <chr> <chr>        <chr>                          <dbl>                  <dbl>
1 DC    District of… South                             10                   24.2
2 CA    California   West                               2                   33.0
3 ME    Maine        Northeast                          4                   29.3
4 MD    Maryland     South                             14                   21.0
5 NY    New York     Northeast                          6                   28.4
6 NJ    New Jersey   Northeast                         12                   23.8
# ℹ abbreviated names: ¹​residential_price_rank_last12mo,
#   ²​residential_price_last12mo
linear2 <- lm(residential_price_rank_last12mo ~ residential_price_last12mo, data = vis2data)
summary(linear2)

Call:
lm(formula = residential_price_rank_last12mo ~ residential_price_last12mo, 
    data = vis2data)

Residuals:
    Min      1Q  Median      3Q     Max 
-9.7009 -6.0473 -0.9297  4.4501 23.6349 

Coefficients:
                           Estimate Std. Error t value Pr(>|t|)    
(Intercept)                 62.4416     3.1555   19.79   <2e-16 ***
residential_price_last12mo  -1.9553     0.1597  -12.24   <2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 7.448 on 49 degrees of freedom
Multiple R-squared:  0.7536,    Adjusted R-squared:  0.7486 
F-statistic: 149.9 on 1 and 49 DF,  p-value: < 2.2e-16

VISUALIZATION 2

Learned how to edit the data point hover from api.highcharts and stackoverflow

# Visualization 2
cols <- c("purple", "pink", "green", "blue") #had to assign speicifc colors otherwise it simply just would not render 
visual_2 <- highchart() |> 
  hc_add_series(
    data = vis2data,
    type = "scatter", #preferrable to line otherwise there would be a wild zig-zag going through every point
    hcaes(
      x = residential_price_last12mo,
      y = residential_price_rank_last12mo,
      name = state_name,
      group = census_region)) |>
  hc_colors(cols) |>
  hc_xAxis(title = list(text = "Residential Prices the Past 12 Months (Hundreds, USD)")) |>
  hc_yAxis(title = list(text = "Residential Price Rank"),reversed = TRUE)|> #reversed the y axis so that it can be read top to bottom as higher rank to lower rank 
  hc_plotOptions(series = list(marker = list(symbol = "circle"))) |>
  hc_legend(
    align = "right",
    verticalAlign = "top"
  ) |>
  hc_title(
    text = "The Residential Price Ranking by Region"
  ) |>
  hc_tooltip(
    pointFormat = paste0(
      "<b>State:</b> {point.name}<br/>",
      "<b>Residential Price:</b> ${point.x}<br/>",
      "<b>Price Ranking:</b> {point.y}")) #makes it so that state information is added to every point, without it you just have this strangely shaped graph that tells pretty much nothing

visual_2