Capstone Project
Brandon Nelson
2025-05-28
Project Overview
Topic: Rooftop Solar
Question: Which areas of the US are best for investing in residential rooftop solar installation?
Scenario: The client is a national solar panel company seeking to expand residential rooftop installation. A key selling point of this type of solar is that it can offset customer’s electric bills, especially as rates continue to rise year over year. Solar efficiency can also vary by location because of cloud cover and sun angle due to latitude. The client wants to know which regions would be best for new investment with the most impressive numbers to show customers how much it could offset electric rates.
Data Gathering and Cleaning
Electricity Rate Data:
- Data source
- Files were divided into Investor-Owned and Non-Investor-Owned Utilities, combined each year into a single sheet within a Google Sheets document.
- Copied to a new file to preserve original data, then created a script to delete extra columns so the data only has the basics needed: zip code, state, and average residential rate.
- Downloaded as a .csv file for use in RStudio
- While combining data so each zip code only has a single row, discovered the data does not have all 41,704 zip codes, the resulting tables have varying numbers of rows, a little over 39,000. Some remote zip codes may not have power generation or utilities may not have provided data to the federal database. It is a relatively small percentage missing, so will continue with available data.
- Created a list with one entry per zip to analyze changes year over year, but after further review of the data found some zips have large discrepancies in rates between 2 different electrical utility providers. Will use 2023 data for current rates and use multiple rates per zip to get accurate analysis of highest electrical rates rather than using mean rates per zip for the main analysis.
Sunroof Data:
- Data Source
- Created a simplified table (removing extraneous columns) with an SQL query and downloaded as a .csv file for use in RStudio.
- Kaggle also had the same data, including state-level which may be useful for additional analysis. Downloaded that and simplified columns in Sheets.
- Much of the Sunroof data specifically involves roof potential like the area of rooftops, which is not relevant for the scope of this project, especially because it seems the numbers include commercial rooftops, causing some zip codes with large commercial buildings to have significant outliers in solar potential of median rooftops. This project is concerned specifically with average sunlight potential. This relates to the “yearly_sunlight_kwh_kw_threshold_avg” data field, which has the average annual kWh output PER kw of solar panels installed based on average solar exposure, taking into account historical weather patterns for the area.
- This data has 11,516 rows, so about 1/4th of ZIPs, which limits analysis but likely applies to the most populous zip codes, will check that using some code in R with population data from US census by ZIP.
Solar Install Cost Data:
- Data Source
- Removed tax credit column because those could change soon and are not fully relevant to the current project
- Added column for State abbreviations because currently Rate data uses abbreviations and Sunroof data has full state names. Including a data frame into the RStudio project with state names and abbreviations to standardize the datasets.
Other:
Electric rates by state: Data Source - most recent update was 5/19/25 when downloaded. Main analysis is by zip but some state-level analysis will also be useful.
ZIP population data: from 2020 US census. Data Source - used to verify limited ZIPs of Sunroof data covers sufficient population:
sr_pop_check <- inner_join(sunroof, zip_pop_data, by = join_by(region_name == ZIP))
sum(sr_pop_check$Population)## [1] 254732187- This is approximately 77% of total US population.
Processing and Analysis
- The following codes is to get average rate of change in electric rates year-to-year over 10 years, by ZIP code. It is not necessarily elegant, but it works.
x <- reduce(rateDFs, inner_join, by = "zip")
rate_changes <- data.frame(zip = str_pad(x$zip, 5, pad= "0"), state = x$state.x, rate1 = x$res_rate.x, rate2 = x$res_rate.y, rate3 = x$res_rate.x.x, rate4 = x$res_rate.y.y, rate5 = x$res_rate.x.x.x, rate6 = x$res_rate.y.y.y, rate7 = x$res_rate.x.x.x.x, rate8 = x$res_rate.y.y.y.y, rate9 = x$res_rate.x.x.x.x.x, rate10 = x$res_rate.y.y.y.y.y)
rate_changes <- rate_changes %>% mutate(avg_percent_change =
+ ( ((rate10 - rate9) / rate9) +
+ ((rate9 - rate8) / rate8) +
+ ((rate8 - rate7) / rate7) +
+ ((rate7 - rate6) / rate6) +
+ ((rate6 - rate5) / rate5) +
+ ((rate5 - rate4) / rate4) +
+ ((rate4 - rate3) / rate3) +
+ ((rate3 - rate2) / rate2) +
+ ((rate2 - rate1) / rate1)) / 9)- This did reveal some of the data had a rate of 0 listed for 2014, removed those, and created a data frame of the highest increases, 307 zip codes with an average 10% increase or more over 10 years:
rate_changes <- rate_changes[!(rate_changes$rate1 == 0),]
highest_increase <- rate_changes[(rate_changes$avg_percent_change > 0.1),] %>% arrange(desc(avg_percent_change)) %>% mutate(avg_percent_change = formattable::percent(avg_percent_change))| zip | state | avg_percent_change |
|---|---|---|
| 99841 | AK | 21.01% |
| 94130 | CA | 18.97% |
| 99801 | AK | 15.71% |
| 99824 | AK | 15.71% |
| 92025 | CA | 15.43% |
| 92026 | CA | 15.43% |
| 92027 | CA | 15.43% |
| 92029 | CA | 15.43% |
| 92069 | CA | 15.43% |
| 92078 | CA | 15.43% |
Checking the top result (zip 99841 in Alaska) reveals an outlier, as one year (2019) rates jumped nearly 400% then returned to normal. Most of the other high rates are in California, and seem to represent steady high increases. This part of the analysis is supplemental, to cross-reference with areas with high solar value, some may need to be checked manually for outliers but for now this data seems usable.
Joining state data first to begin with more simple calculations to test metrics and get initial insights. On ZIP-level data, only the ‘threshold’ yearly sunlight is useful because some ZIP codes have median roof size data skewed by commercial rooftops and this project is interested in residential. On a statewide level though the impact of large commercial roofs is diluted enough that the direct annual value of a median roof could be of interest.
state_data <- inner_join(solar_costs, inner_join(sunroof_by_state, state_elec_rates, by = join_by(state, state_abbrev)), join_by(State == state, 'State Code' == state_abbrev)) %>% mutate(avg_roof_annual_value = yearly_sunlight_kwh_median * res_rate, solar_value = res_rate * yearly_sunlight_kwh_kw_threshold_avg) %>% arrange(desc(solar_value))| State | Avg Res Elec Rate | Solar Value |
|---|---|---|
| Hawaii | 42.34 | 53869.67 |
| California | 30.55 | 39006.64 |
| Massachusetts | 31.22 | 30571.40 |
| Connecticut | 28.16 | 28017.08 |
| Maine | 26.29 | 25939.08 |
| Rhode Island | 25.31 | 25194.57 |
| New York | 24.37 | 23977.64 |
| New Hampshire | 23.62 | 23046.08 |
| Vermont | 22.29 | 21182.19 |
| Arizona | 15.20 | 20948.97 |
Hawaii and California stand out immediately in solar value when using state-wide averages.
An additional state-level question involves solar costs. That is joined above but not directly used yet. There is not significant variance in cost-per-watt, but integrating that into the value analysis can confirm if the top rankings change or not:
state_data <- state_data %>% mutate(cost_adjusted_value = solar_value / as.numeric(str_remove_all(state_data$`Average Cost per Watt`, "\\$"))) %>% arrange(desc(cost_adjusted_value))| State | Cost Per Watt | Adjusted Solar Value |
|---|---|---|
| Hawaii | $3.13 | 17210.757 |
| California | $3.33 | 11713.706 |
| Massachusetts | $3.12 | 9798.527 |
| Connecticut | $2.93 | 9562.142 |
| Maine | $3.10 | 8367.445 |
| Rhode Island | $3.04 | 8287.688 |
| New Hampshire | $2.97 | 7759.624 |
| Vermont | $2.79 | 7592.182 |
| Arizona | $2.79 | 7508.592 |
| New York | $3.33 | 7200.493 |
The only change in the top 10 states is New York moved down slightly from rank 7 to 10, the others remain unchanged.
The same calculations are applied to the ZIP code data. Some ZIPs have 2 electric companies, so a question arises: take the mean of multiple rates in a given ZIP, or analyze each local rate independently? For creating a map of solar value, the average is more useful, the utility-specific rates are more meaningful when doing a list of highest values.
sv_by_utility <- inner_join(sunroof, rates_2023, join_by(region_name == zip), relationship = "many-to-many") %>% mutate(solar_value = res_rate * yearly_sunlight_kwh_kw_threshold_avg) %>% group_by(utility_name) %>% summarise(avg_solar_value = mean(solar_value), avg_rate = mean(res_rate)) %>% arrange(desc(avg_solar_value)) %>% left_join(utility_states, by="utility_name")| Utility | Avg Solar Value | Avg Rate | State |
|---|---|---|---|
| Maui Electric Co Ltd | 575.5062 | 0.4376639 | HI |
| Hawaii Electric Light Co Inc | 549.2299 | 0.4651928 | HI |
| Hawaiian Electric Co Inc | 548.9109 | 0.4322474 | HI |
| City of Moreno Valley - (CA) | 501.1074 | 0.3651722 | CA |
| San Diego Gas & Electric Co | 468.7397 | 0.3625885 | CA |
| Bear Valley Electric Service | 467.4137 | 0.3251913 | CA |
| City of Glendale - (CA) | 352.3287 | 0.2507589 | CA |
| City of Pasadena - (CA) | 339.1705 | 0.2413939 | CA |
| Pacific Gas & Electric Co. | 331.9175 | 0.2708457 | CA |
| Southern California Edison Co | 327.9508 | 0.2412293 | CA |
| City of Colton - (CA) | 324.7716 | 0.2286579 | CA |
| Los Angeles Department of Water & Power | 322.9479 | 0.2298631 | CA |
| Merced Irrigation District | 302.9477 | 0.2452918 | CA |
| City of Azusa | 282.6303 | 0.2011532 | CA |
| Otero County Electric Coop Inc | 271.3404 | 0.1914961 | NM |
| Fitchburg Gas & Elec Light Co | 268.7910 | 0.2754571 | MA |
| Alameda Municipal Power | 257.9562 | 0.2189595 | CA |
| City of Norwich - (CT) | 257.1614 | 0.2544515 | CT |
| City of Lodi - (CA) | 254.4226 | 0.2077174 | CA |
| Consolidated Edison Co-NY Inc | 251.3198 | 0.2548815 | NY |
- Hawaii and California definitely dominate in solar value, driven in part by high electricity costs. Outside of those 2 states, one utility each in New Mexico, Massachusetts, Connecticut, and New York have spots in the top 20. An analysis of mean electric rates by ZIP code is also done:
sv_by_zip <- inner_join(sunroof, rates_2023, join_by(region_name == zip), relationship = "many-to-many") %>% group_by(region_name) %>% summarise(avg_res_rate = mean(res_rate), avg_sunlight = mean(yearly_sunlight_kwh_kw_threshold_avg), state=first(state)) %>% mutate(solar_value = avg_res_rate * avg_sunlight) %>% arrange(desc(solar_value))| ZIP | State | Solar Value |
|---|---|---|
| 96732 | HI | 575.5062 |
| 96753 | HI | 575.5062 |
| 96761 | HI | 575.5062 |
| 96779 | HI | 575.5062 |
| 96793 | HI | 575.5062 |
Only displaying the top 5 here, Hawaii has the top 34 spots. ZIP codes are small enough that this data is not as useful in table form, but should lend itself to creating map visualizations. R has the capability of creating plots based on ZIP codes but Tableau is better suited, so this data will be exported to create visualizations there.
A US map with data at the ZIP level may not be the most useful, so the data will be divided into states to create a map of zip-level solar value for each of the top 5 states. A few initial insights are presented here, an interactive dashboard to filter specific states will be available on Tableau Public here
The visualizations immediately show something that is fairly well-known about the US, that 77% of the population covered by ZIP codes with available data does not cover the majority of the land geographically. The nature of the electricity rates clustering by utility provider does also create areas of similar solar value. The map for Hawaii, for example:
- This shows western Maui to be the most valuable area for solar installation. In terms of real numbers, there is a relatively small difference in value between there and the other islands for which data is available. Our metric ranges from 548 to 575.5 here, where the next highest value outside of Hawaii is 470.
Southern California, especially around San Diego, has the next highest solar value. As can be clearly seen though, there are some areas close by with much lower solar value. Much of this region has similar weather, so the main driver of these differences is variation in the cost of electricity.
In testing the visualization, realized it may be beneficial to have more information in a tooltip when hovering over a zip, or to have a side-by-side view of a region showing the rate change trends calculated above.
sv_by_zip <- left_join(sv_by_zip, rate_changes[,c(1,13)],join_by(region_name == zip)) %>% arrange(desc(avg_percent_change))| ZIP | State | Solar Value | Avg Rate Change |
|---|---|---|---|
| 94130 | CA | 235.3584 | 0.1896504 |
| 92025 | CA | 312.9260 | 0.1543100 |
| 92026 | CA | 312.9260 | 0.1543100 |
| 92027 | CA | 312.9260 | 0.1543100 |
| 92029 | CA | 312.9260 | 0.1543100 |
- Several of he highest rate increases found earlier aren’t in the available ZIP codes in the sunroof data, but the highest rate increases over the last 10 years don’t correspond to current highest solar value. But if rate changes continue on pace, value rankings could change, so these highest rate increase areas could still be good areas to investigate and invest in solar.
Insights
The best places to invest in residential rooftop solar are Hawaii and southern California, at least in terms of sun exposure relative to electricity rates. Installation costs may vary, but cost-per-watt for panel installation does not vary enough to affect most rankings.
The visualizations on Tableau can be used to compare different regions and states. For example, looking at Nevada, zip code 89135 in Las Vegas has significantly higher solar value compared to neighboring zip codes, and the region around Las Vegas has better solar value than the region around Carson City. Comparing Minnesota and Wisconsin reveals that while Minneapolis and Rochester areas are the best places for solar in Minnesota, Madison and Milwaukee areas have better solar value than either, and south-eastern Wisconsin has a wide area with good solar value for the region. Adding Illinois to the same map shows much lower solar values south of that border, with the region around Chicago having about 60% the solar value of the region around Milwaukee.
Hovering over different ZIP codes in the Tableau visualization shows average electric rates as of 2023 as well as the average electric rate change over the 10 years prior. A possible future enhancement would be to create a slider to set future years and see how the map changes if those local rate trends continue unchanged. This is outside of the current scope of the project but could be the next step if stakeholders are interested, this could especially be useful for marketing materials in areas with high rate of change.