Sample Electricity Account Analysis
Owen Ouyang
2019-06-12
Sample Analysis
This year, we conducted the Sample analysis with the most advance technology in R and by going through a systematical data science analysis process, we have the following findings from the data.
Load Data
The loaded data is from the database provided via bills. It contains each bill entry for all the Sample accounts in 2017.
Preprocess the Data
For this analysis, we are only focusing on the data from 2017 and I added the billing category (SPOT, RPP) based on the following assumptions.
Dataset is complete.
Since price structure is not provided, we assume that if there is global adjustment charge, the account is under Spot market, otherwise RPP.
Each account has its own unique account Id and we treated the accounts with the same address but different account Id as different accounts.
The scope of this analysis is limited to 2017.
The monthly data indicate a full month starting from the first calendar day of the month to the last.
Interactive Map
Initial View of the Data
For 2017, we received a total of 3,298 bill entries and each entry we acquire 7 columns of information. In this year, Sample has a total consumption of 52,639,457 kWh Usage, a total commodity cost of $5,626,872 , and an average overall Price of $106.89 per MWh.
Data Table for All the Data Entries in 2017
Data Structure
## Observations: 3,298
## Variables: 7
## $ account <chr> "Acount: 1", "Acount: 1", "Acount: 1", "Acount: 1", "A...
## $ address <chr> "Address: 1", "Address: 1", "Address: 1", "Address: 1"...
## $ month <chr> "January", "February", "March", "April", "May", "June"...
## $ meter <chr> "Meter: 1", "Meter: 1", "Meter: 1", "Meter: 1", "Meter...
## $ usage <dbl> 3.50256468, 2.06335942, 3.45351848, 3.38914670, 6.0552...
## $ energy <dbl> 0.394505002, 0.231774620, 0.377139304, 0.365948720, 0....
## $ GA <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...Data Summary
## account address month
## Length:3298 Length:3298 Length:3298
## Class :character Class :character Class :character
## Mode :character Mode :character Mode :character
##
##
##
## meter usage energy
## Length:3298 Min. : 0.0 Min. : -282.39
## Class :character 1st Qu.: 75.5 1st Qu.: 6.15
## Mode :character Median : 569.7 Median : 45.25
## Mean : 15961.0 Mean : 341.00
## 3rd Qu.: 3702.0 3rd Qu.: 213.14
## Max. :2173266.5 Max. :44381.20
## GA
## Min. : 0
## 1st Qu.: 0
## Median : 0
## Mean : 1365
## 3rd Qu.: 0
## Max. :196740Data Detail
## Skim summary statistics
## n obs: 280
## n variables: 7
##
## Variable type: character
## variable missing complete n min max empty n_unique
## account 0 280 280 9 11 0 280
## address 0 280 280 10 12 0 223
## price_structure 0 280 280 3 4 0 2
##
## Variable type: numeric
## variable missing complete n mean sd p0 p25
## price 0 280 280 96.45 12.13 65.7 89.75
## total_energy 0 280 280 4021.31 15078.8 1.24 132.99
## total_GA 0 280 280 16079.5 98707.08 0 0
## total_usage 0 280 280 187992.03 1103012.42 14.97 1516.87
## p50 p75 p100 hist
## 95.65 100.85 159.7 <U+2581><U+2583><U+2587><U+2582><U+2582><U+2581><U+2581><U+2581>
## 1058.9 2940.2 170977.47 <U+2587><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581>
## 0 0 1168201.54 <U+2587><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581>
## 12274.18 47066.43 1.3e+07 <U+2587><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581><U+2581>Accounts
For the period that we are examing, there are in total of 280 accounts.
Overall Summary
| Month | Usage | Energy Cost | Global Adjustment | Price |
|---|---|---|---|---|
| January | 5971440 | 159240.82 | 378911.2 | 90.1 |
| February | 4626898 | 131680.94 | 435238.2 | 122.5 |
| March | 3553596 | 123014.29 | 268063.3 | 110.1 |
| April | 5293227 | 85655.52 | 345728.4 | 81.5 |
| May | 4003684 | 38131.89 | 391038.4 | 107.2 |
| June | 3629851 | 57072.64 | 384081.5 | 121.5 |
| July | 4090797 | 74036.35 | 327606.9 | 98.2 |
| August | 3542718 | 83857.05 | 336055.8 | 118.5 |
| September | 4928149 | 124315.53 | 510467.7 | 128.8 |
| October | 4970202 | 69392.02 | 450288.6 | 104.6 |
| November | 4232567 | 78626.79 | 392716.5 | 111.4 |
| December | 3796328 | 99589.41 | 282062.1 | 100.5 |
| Overall Usage | Overall Energy Cost | Overall GA | Overall Price |
|---|---|---|---|
| 52639457 | 1124613 | 4502259 | 106.9 |
Price Structure vs Cost Analysis
Small Accounts(<250,000 kWh Annual Consumption)
Static Graph
Interactive Graph
3D Interactive Graph
Large Accounts(>=250,000 kWh Annual Consumption)
Static Graph
Interactive Graph
3D Interactive Graph
High Cost Accounts
The following 2 accounts are identified as high cost accounts. Since the costs are much higher than other accounts and both are under RPP, we would assume that the two accounts are under TOU and the two sites only use energy during On Peak and Mid Peak hours.
The annual data for the following two accounts are listed below.
| account | address | total_usage | total_energy | total_GA | price | price_structure |
|---|---|---|---|---|---|---|
| Acount: 255 | Address: 200 | 39902.62 | 6372.248 | 0 | 159.7 | RPP |
| Acount: 256 | Address: 201 | 497957.30 | 79414.100 | 0 | 159.5 | RPP |
Then, we dive into monthly bills.
Interactive Drilldown Graph
Borderline Accounts
The following 4 accounts are identified as Borderline accounts. Since energy rebate is not available for any account that have more than 250,000 kWh annual usage. The total cost difference for the 4 accounts is estimated to be 4,280.58.
| account | address | total_usage | total_energy | total_GA | price | price_structure | Cost_Difference |
|---|---|---|---|---|---|---|---|
| Acount: 111 | Address: 89 | 239803.3 | 3055.236 | 24568.16 | 115.2 | SPOT | 1463.00 |
| Acount: 118 | Address: 94 | 225250.6 | 4033.199 | 23295.27 | 121.3 | SPOT | 968.58 |
| Acount: 148 | Address: 117 | 278835.2 | 4636.996 | 27845.96 | 116.5 | SPOT | 1338.00 |
| Acount: 96 | Address: 76 | 255534.2 | 5059.364 | 25428.76 | 119.3 | SPOT | 511.00 |
Then, we dive into monthly bills.
Interactive Drilldown Graph