<jupyter_start><jupyter_text>DSTEP20 // Assignment 4 **(GGD: Total = 56 / 60 - VERY GOOD!)**assigned : **Jan 31, 2020**DUE : **Feb 6, 2020 11:59pm** Energy Consumption and Population Characteristics in Chicago--- **The Scenario:** You are working for a non-profit that is consulting with the City of Chicago on future planning for energy distribution needs.  Your team is large and consists of groups that study building characteristics and energy consumption as well as groups that study population predictions and growth estimates.  Taken together, they can create a model for future population growth and infrastructure, but they are missing a key piece of the puzzle: how usage characteristics of the population scales with social and economic factors.  The hypothesis is that there may be link between the two given the link between [energy consumption per person and GDP per capita](https://ourworldindata.org/grapher/energy-use-per-capita-vs-gdp-per-capita) on a global scale.**The Problem Statement:** Your job is to explore the economic and social dimension of the problem by combining recently released energy consumption data in Chicago with data from the Census on population characteristics by Census Tract (note, the consumption data exists at the census block level, but being well versed in the census, you know that census block estimates have significant uncertainty, and so the decision is made to focus on the Census Tract level).  As with the last assignment, **the deliverable for this assignment is a complete, end-to-end data science workflow similar to those we've seen before, including 1) Background information on the problem and data sets, 2) Data description and summary, 3) Analysis and visualizations, and 4) Summary of findings.****Useful Data Sets:**  Data sets to be used for this task include the [Energy Usage Data](https://data.cityofchicago.org/Environment-Sustainable-Development/Energy-Usage-2010/8yq3-m6wp), the [Census Tract shapes](https://data.cityofchicago.org/Facilities-Geographic-Boundaries/Boundaries-Census-Tracts-2010/5jrd-6zik) in Chicago, and the [2010 Census Data at American Fact Finder](https://factfinder.census.gov/faces/nav/jsf/pages/searchresults.xhtml?refresh=t).  **1) Background information on the problem and data sets****Relationship between energy consumtion and economic features:**As the city is developed based on the consumption of natural resources, environmental and social problems has been rapidly emerged as major problems. According to [Hackett, &Ltzenhiser(1991)](https://link.springer.com/article/10.1007/BF01114472), "the role of marginal price in ordering consumption" influenced feature of the consumption and social status, which result in change of the social structure. In economic theory,[Environmental Kuznets Curve (EKC)](https://www.intelligenteconomist.com/environmental-kuznets-curve/) graphs the hypoteheis that the ennvironment is detroyed when economies (per capita income) developed, however, as the economies are more developed then the money is invested back into the environment.Therefore, it is important to find the relationship between the conumption of natural resources and the economic features [(Eden et al., 1992; Bilgili, 2016; Soytas et al., 2007)](https://www.sciencedirect.com/science/article/abs/pii/S0921800906003430). Then, it is possible to determine what the stage will be in EKC.![Environmental Kuznets Curve](https://www.intelligenteconomist.com/wp-content/uploads/2018/03/Environmental-Kuznets-Curve.png)**Energy consumption and economic features in the city of chicago:**The City of Chicago has been developed as one of the big cities in United States. The [Cook County](https://en.wikipedia.org/wiki/Cook_County), where the Chicago is located, grew rapidly after 1945. The city was under control, however, it grew up as a financial and transportation hub in Illinois as well as United States. However, the exponential growth results in increasing pollution on the environment and health issues impacting citizens in the city [(Cumbler,2005)](https://books.google.com/books?id=GlF_yi-5lrcC&pg=PA139v=onepage&q&f=false). According to the [U.S, Energy Information Administration (EIA) ](https://www.eia.gov/state/?sid=IL), Illinos is the fifth-largest energy-consuming state in the nation, and its industrial sector uses the most energy of any sector. Also, Illinois ranked first in the nation in 2018 in net electricity generation (EIA data portal, 2020)  **Research question:**Based on the previous study, it is important to know the relationship between energy consumption and economic features in the City of Chicago in order to consider environmental protection and economic development in urban planning  ([Cumbler (2005)](https://books.google.com/books?id=GlF_yi-5lrcC&pg=PA139v=onepage&q&f=false); [Hackett, &Ltzenhiser (1991)](https://link.springer.com/article/10.1007/BF01114472)). Therefore, the research question in this study aims to find if the significant relationship between energy conumption per household and household income is found. **References:**<NAME>., <NAME>., & <NAME>. (2016). The dynamic impact of renewable energy consumption on CO2 emissions: a revisited Environmental Kuznets Curve approach. Renewable and Sustainable Energy Reviews, 54, 838-845.Cook County. Wikipedia. Retrieved 2/6/2020 from: https://en.wikipedia.org/wiki/Cook_CountyCumbler, J. (2005). Northeast and Midwest United States: An Environmental History. ABC-CLIO. p. 139.<NAME>., & <NAME>. (1992). Cointegration tests of energy consumption, income, and employment. Resources and energy, 14(3), 259-266.Environmental Kuznets Curve. Intelligent Economist. Retrieved 2/6/2020 from: https://www.intelligenteconomist.com/environmental-kuznets-curve/Hackett, B., & <NAME>. (1991, September). Social structures and economic conduct: interpreting variations in household energy consumption. In Sociological forum (Vol. 6, No. 3, pp. 449-470). Kluwer Academic Publishers-Plenum Publishers.Illinois. U.S. Energy Information Administration (EIA). Retrieved 2/6/2020 from: https://www.eia.gov/state/?sid=ILSoytas, U., <NAME>., & <NAME>. (2007). Energy consumption, income, and carbon emissions in the United States. Ecological Economics, 62(3-4), 482-489.  **2) Data description and summary**<jupyter_code># Data description

print("Data Description")
print("")

d = {1: ["Census Block", "Census Block number obtained in the address matching/geocoding algorithms", 'Number'],
     2: ["Total kWh", "(Electricity) Total 2010 kWh from ComEd accounts", 'Number'],
     3: ["Total Therms", "(Gas) Total 2010 Therms from People accounts", 'Number'],
     4: ["Total Population", "Total population from Census 2010 report", 'Number'],
     5: ["Total Units", "Total number of housing units from census 2010", 'Number'],
     6: ["GEO.id2", "GEOID", 'Number'],
     7: ["HC01_EST_VC01", "Estimated households", 'Number'],
     8: ["HC01_EST_VC15", "Estimated households of mean income", 'Number(Units: dollars)'],
     }

print ("{:<5} {:<20} {:<80} {:<10}".format('No.','Column Name','Description', 'Type'))
for k, v in d.items():
    lang, perc, change = v
    print ("{:<5} {:<20} {:<80} {:<10}".format(k, lang, perc, change))

import numpy as np
import pandas as pd
import statsmodels.formula.api as sm

!pip install geopandas
import geopandas as gp

import matplotlib.pyplot as plt
# Structure of Geospatial data

# Dataset1
fname1 = "https://data.cityofchicago.org/api/views/8yq3-m6wp/rows.csv?accessType=DOWNLOAD"
dscc = pd.read_csv(fname1)
nan = dscc[~dscc["CENSUS BLOCK"].isnull()]
print("Numner of NaNs in Energy Consumption Data: {0}".format(len(dscc)))
print("Number of NaNs in Energy Consumption Data: {0}". format(len(nan)))
print("Number of NaNs in CENSUS BLOCK: {0}".format(len(dscc)-len(nan)))

dscc = nan.fillna(0.0)
dscc.head()

# Dataset2
from google.colab import drive
drive.mount("/content/drive")
fname2 = "drive/My Drive/Colab Notebooks/aff_download_IL/ACS_10_5YR_S1901_with_ann.csv"
aff = pd.read_csv(fname2)
aff.head()

# Dataset3: Geodata
gdat = gp.read_file("https://data.cityofchicago.org/api/geospatial/5jrd-6zik?method=export&format=GeoJSON")
print(gdat.head())
gdat.plot()
gdat["commarea"] = gdat["commarea"].astype(float)
gdat["commarea_n"] = gdat["commarea_n"].astype(float)

# Make a CHOROPLETH
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=[7,7])
gdat.plot("commarea", cmap="Blues_r", ax=ax)
ax.axis("equal")
# Nan Zero
nan = dscc[~dscc["CENSUS BLOCK"].isnull()]
print("Numner of NaNs in Energy Consumption Data: {0}".format(len(dscc)))
print("Number of NaNs in Energy Consumption Data: {0}". format(len(nan)))
print("Number of NaNs in CENSUS BLOCK: {0}".format(len(dscc)-len(nan)))

dscc = nan.fillna(0.0)
print(dscc.head())
cols = dscc.columns [[0, 1, 2, 16, 31, 63, 64]]
dscc2 = dscc[cols]
dscc2.columns = ["area_n", "cblock", "building_t", "kwh_t", "therms_t", "pop_t", "units_t"]
print(gdat.head())
print("")
print(dscc2.head())
# Fix "cblock" as a type of "geoid"
# Then, combine "geoid10" and "cblock"

dscc2["cblock_id"] = dscc2["cblock"].astype(float)
dscc2["cblock_id2"] = dscc2["cblock_id"] / 10000
dscc2["cblock_id3"] = dscc2["cblock_id2"].astype(str)
dscc2["cblock_id4"] = dscc2["cblock_id3"].str.split(".")

lcblock = dscc2["cblock_id4"].to_list()
temp = pd.DataFrame(lcblock, columns=["cblock_id5", "block"])
temp2 = temp.groupby("cblock_id5").sum().reset_index()
temp2["cblock_id5"] = temp2["cblock_id5"].astype(str)
print(temp2.head())
dscc2["cblock_id5"] = temp2["cblock_id5"].astype(str)
dscc["GEOID10"] = dscc2["cblock_id5"]
print(dscc.head())
# dscc3 = dscc + dscc2(new CENSUS BLOCK)
dscc3 = dscc.groupby("GEOID10").sum().reset_index()
gdatm = gdat.merge(dscc3, left_on = "geoid10", right_on = "GEOID10")
# Step1: make independent varialbe (merge electricity and therms)
pd.set_option('display.max_columns', 100)
# Dependent variable: Energy consumption per household 
# Case1: ("Elec_h") = Total(kwh) / Total units 
# Case2: ("Thms_h") = Total (thms) / Total units 
# Case3: ("Energy_h") = {Total(kwh) + Total (thms)} / Total units 
cols2 = dscc3.columns [[0, 13, 27, 59]]
dscc_m = dscc[cols2]
dscc_m.columns = ["Geoid", "t_kwh", "t_thms", "t_housing"]
nan_m = dscc_m[~dscc_m["Geoid"].isnull()]
dscc_m = nan_m.fillna(0.0)
dscc_m2 = pd.DataFrame({"Geoid": dscc_m["Geoid"], 
                        "Elec_h": dscc_m["t_kwh"] / dscc_m["t_housing"],
                        "Thms_h": dscc_m["t_thms"] / dscc_m["t_housing"],
                        "Energy_h": (dscc_m["t_kwh"] + dscc_m["t_thms"]) / dscc_m["t_housing"]}, 
                       columns = ["Geoid", "Elec_h", "Thms_h", "Energy_h"])
dscc_t = dscc_m.merge(dscc_m2, left_on = "Geoid", right_on = "Geoid")

# Dependent variable: 
# Case1:Estimated households of mean income (HC01_EST_VC15): (Unit: dollars)
aff_m = pd.DataFrame(columns = ["GEO.id2", "HC01_EST_VC15"], data=aff)
aff_m["Geoid2"] = aff_m["GEO.id2"]
aff_m["h_incm"] = aff_m["HC01_EST_VC15"]
nan_m2 = aff_m[~aff_m["Geoid2"].isnull()]
aff_m = nan_m2.fillna(0.0)
aff_m = aff_m.replace("-",0.0)

final = dscc_t.merge(aff_m, left_on = "Geoid", right_on = "Geoid2")
final = final.replace(np.inf,0.0)
print(final.head())
print("")
final["h_incm"] = final["h_incm"].astype(float)
print(final.dtypes)
# Step 2: Data Statistics of Dependent Variable
print("range of electricity per household = [{0}, {1}]".format(np.min(final["Elec_h"]), np.max(final["Elec_h"])))
print("mean of electricity per household = {0}".format(np.mean(final["Elec_h"])))
print("median of electricity per household = {0}".format(np.median(final["Elec_h"])))
print("standard deviation of electricity per household = {0}".format(np.std(final["Elec_h"])))   
print("")

print("range of therm per household = [{0}, {1}]".format(np.min(final["Thms_h"]), np.max(final["Thms_h"])))
print("mean of therm per household = {0}".format(np.mean(final["Thms_h"])))
print("median of therm per household = {0}".format(np.median(final["Thms_h"])))
print("standard deviation of therm per household = {0}".format(np.std(final["Thms_h"])))   
print("")

print("range of energy consumption per household = [{0}, {1}]".format(np.min(final["Energy_h"]), np.max(final["Energy_h"])))
print("mean of energy consumption per household = {0}".format(np.mean(final["Energy_h"])))
print("median of energy consumption per household = {0}".format(np.median(final["Energy_h"])))
print("standard deviation of energy consumption per household = {0}".format(np.std(final["Energy_h"])))   
print("")

print("range of estimated households of mean income = [{0}, {1}]".format(np.min(final["h_incm"]), np.max(final["h_incm"])))
print("mean of estimated households of mean income  = {0}".format(np.mean(final["h_incm"])))
print("median of estimated households of mean income  = {0}".format(np.median(final["h_incm"])))
print("standard deviation of estimated households of mean income  = {0}".format(np.std(final["h_incm"])))   
print("")<jupyter_output>range of electricity per household = [0.0, 667899.6666666666]
mean of electricity per household = 1068.5967669434804
median of electricity per household = nan
standard deviation of electricity per household = 24162.245303764063

range of therm per household = [0.0, 34342.666666666664]
mean of therm per household = 73.65411603556043
median of therm per household = nan
standard deviation of therm per household = 1274.73930406325

range of energy consumption per household = [0.0, 667899.6666666666]
mean of energy consumption per household = 1094.3834575420594
median of energy consumption per household = nan
standard deviation of energy consumption per household = 23703.394806856093

range of estimated households of mean income = [0.0, 232353.0]
mean of estimated households of mean income  = 63736.333333333336
median of estimated households of mean income  = 54418.0
standard deviation of estimated households of mean income  = 33630.129534828506<jupyter_text>**3) Analysis and Visualizations**<jupyter_code>import statsmodels.formula.api as sm

# Case1: 
# independent variable (x) = Estimated households of mean income  
# dependent variable (y) = ("Elec_h") = Total(kwh) / Total units 
model1 = sm.ols("Elec_h ~ h_incm", data=final)
result1 = model1.fit()
print(result1.summary())
# Case2: y = ("Thms_h") = Total (thms) / Total units 
model2 = sm.ols("Thms_h ~ h_incm", data=final)
result2 = model2.fit()
print(result2.summary())
# Case3: y = ("Energy_h") = {Total(kwh) + Total (thms)} / Total units 
model3 = sm.ols("Energy_h ~ h_incm", data=final)
result3 = model3.fit()
print(result3.summary())
# Visualization

fig, ax = plt.subplots(figsize=[7,7])
gdatm.plot("TOTAL KWH", ax=ax, cmap="Blues_r")
ax.axis("equal")
fig, ax = plt.subplots(figsize=[7,7])
gdatm.plot("TOTAL THERMS", ax=ax, cmap="BrBG_r")
ax.axis("equal")
fig, ax = plt.subplots(figsize=[7,7])
gdatm.plot("TOTAL POPULATION", ax=ax, cmap="BuPu_r")
ax.axis("equal")
fig, ax = plt.subplots(figsize=[7,7])
gdatm.plot("TOTAL UNITS", ax=ax, cmap="RdYlGn_r")
ax.axis("equal")
fig, ax = plt.subplots(figsize=[7,7])
gdatm.plot("OCCUPIED HOUSING UNITS", ax=ax, cmap="Accent_r")
ax.axis("equal")
# -- now visualize

plt.style.use("default")

fig = plt.figure(figsize=[10,3])
ax  = fig.add_axes([0.5, 0.05, 0.4, 0.8])
gdatm.plot("TOTAL KWH", cmap="Blues_r", legend=True, ax=ax)
ax.axis("equal")
ax.axis("off")

ax2  = fig.add_axes([0.12, 0.05, 0.35, 0.4])
final.plot(y="t_kwh", use_index=True, color="lightblue", legend=False, ax=ax2)
ax2.set_xlabel("")
ax2.set_title("Total Energy Consumption per household", loc="left")
xlab = ax2.set_xlabel("Estimated household of mean income ($)")
ylab = ax2.set_ylabel("Energy Consumtion")

cbt = fig.text(0.9, 0.475, "Total Energy Conumption(kWh)", rotation=-90, fontsize=12, va="center", color="black")
header = fig.text(0.05, 0.95, "Total Energy Consumption in City of Chicago (2010)", fontsize=20, color="black", weight="bold")
subheader = fig.text(0.05, 0.85, 
         "The City of Chicago has released data on the total enegy \nconsumption " +
         "collected in each of he city's tract in 2010 \nand" + 
         "the results are shown as below in graph and map.\nUnits:" +
         "Esimtated household of mean income($)", color="grey", weight="bold", va="top")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14151407152513212, 'fraction_numerical': 0.04411945434435296, 'mean_word_length': 5.489234449760765, 'pattern_counts': {'":': 4, '<': 24, '<?xml version=': 0, '>': 16, 'https://': 18, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '86544', 'license': 'MIT', 'n_tokens_mistral': 5863, 'n_tokens_neox': 5300, 'n_words': 1623, 'path': 'Energy Consumption and Population Characteristics in Chicago.ipynb', 'repo_name': 'KLeeDE/Data-Science', 'size': '16303'}