Overview of Dataset:

This dataset, compiled by NREL using data from ABB, the Velocity Suite and the U.S. Energy Information Administration dataset 861, provides average residential, commercial and industrial electricity rates with likely zip codes for both investor owned utilities (IOU) and non-investor owned utilities and can be found here.

Note: The data includes average rates for each utility (not average rates per zip code).

Data comes in form of two csv files, one containing information for Investor Owned Utilities and the other for Non-Investor Owned Utilities. Moreover, each file contains an abbreviation for various states in USA and not the complete state name.

So, I first checked both files for invalid or duplicate values and then combined them together using UNION statement in SQL,script can be found here. After that I joined the combined table with another table, containing state abbreviations and their complete names as well. The final table used for Tableau Visualization can be found here. The table containing all state abbreviations and their names can be found here. This table has been simply copied from Wikipedia and saved as a csv file.

Before proceeding further, I want to take a moment to explain some jargons related to electricity rates and different services provided by the utilities. The electricity rates are categorized as commercial, industrial and residential. This is based on the type of area or place to which the electricity is being supplied.

• Commercial stands for places where any business is done with an aim to earn profit.
• Industrial stands for places where any kind of manufacturing industry is set up.
• Residential implies areas and places where people live.

Different service types are defined as below:

• Bundled : These services refer to bundling together of energy generation, transmission, and distribution services into a single package for customers by energy companies and electric utilities.
• Energy: This means that the utilities only market and sell electricity to the utilities which provide bundled services.
• Delivery: This means that the utilities provide only electricity delivery services to consumers directly.

Visualization Using Tableau

The dashboard has been uploaded to Tableau Public and can be found here.

This dashboard is kind of a look up table for getting electricity rates in USA for three categories, namely, Commercial, Industrial and Residential and compare it amongst different ownerships that provide them and the type of services they deliver. Some useful filters have also been added at the top, so that one can get drill down more and get the required information.

A practical demonstration for Lookup:

Suppose, I am a working as a residential housing planner for the state of California. Therefore, I need to look at the residential rates as I am responsible for planning electricity supply for that area. So, I click on California in the map and see that Cooperative utilities give the lowest average rate for electricity. So if I further filter on the cooperative, then it gives me only bundled, implying that it only provides bundled services. From a resident's point of view, it is good as he/she needs to pay only once, both for energy consumed and its delivery. Now as I have come to know about the cooperative utilities, I can go further on that by clicking on Click for more details pop up in the tooltip and then get to know about the cooperative utilities. Then the further steps for negotiations and finalizing contracts with one or more of these utilities can be taken.

One can also refer the video I have attached with this project, where I have shown the above explained demonstration.

Conclusion:

Limitation for this data is that it does not provide information at the detailed level of zip code. For all zip codes at which utilities supply power, it gives an overall average of the rates charged by them (overall average for each utility). More detailed data could have led to a more detailed drill down analysis as we would have got the city level information too, or in other words it would have been better if the data had accurate rates for individual zip codes as well, rather than having a single value which has been averaged over each utility.