<jupyter_start><jupyter_text>**Problem Statement**Glass factory float lines generate significant amounts of wasted heat, which represents an opportunity for heat reclamation. The problem lies in converting the heat to useful electricity with an economically viable waste heat recovery system. Glass factories currently source electricity from the grid at competitive rates for industrial consumption. For our solution to be cost-competitive with electricity generated from fossil fuel plants, it must supply electricity at a lower comparative price.We intend to explore methods by which we can supply electricity at a lower comparative price to fossil fuel plants. The first method is based on the price that we can generate electricity with our thermophotovoltaic (TPV) waste heat recovery system. Some estimates show electricity rates for TPVs at around USD 0.06/kwh - which is already half the price of fossil fuel-generated electricity (quoted at an average of USD 0.12/kwh). However, TPVs are expensive to make and the price of the system would factor into the price of electricity produced by the system. We intend to explore the cost-competitiveness of our system using datasets on state-level electrical prices compared to our best-case-scenario for TPV electricity rates (USD 0.06/kwh). **How will the problem be tackled and solved?**We will generate maps of electrical price, float line location, and float line number at each glass site. These maps will rely on geopandas and geoplot for visualization. We will perform K-means cluster analysis on the maps to identify trends in glass factory locations. We will use visualization methods like a histogram of electrical price for float glass plants to identify what we can charge in each geographical area. The primary assumption is that float glass plants are paying the state-wide industrial rate for electricity and not more or less. **What are the parameters around your problem statement to make it simpleenough to solve but not trivial?**- The first parameter around our problem statement is cleaning and filtering out-of-scope glass plants from our datasets to constrain our analysis to US-based plants. - The next parameter is ensuring the dataset value types are correct for the purpose of processing in our model - e.g. each column has a list of properly formatted strings or floats for the purpose of geolocation. - The final parameter is actually performing the geolocation and cluster analysis on our datasets and creating clear visualizations from the results. These parameters are complex but achievable and require using various libraries to simplify the solution. The solution is non-trivial in that it provides us with new insights into electricity price geographics and how they associate with float glass plant location. **What is the core business or research problem you are solving? Why is itimportant? This should be concise and clear.**The core business problem we are trying to solve is *to determine how much we can charge for electricity at each float glass line we install our waste heat recovery system in*.Because our waste heat recovery system generates electricity for the glass plants, we want to charge them for electricity at a rate lower than what they currently pay. In order to know how much we can charge to remain competitive with on-grid electrical prices, we need to find out how much is currently being paid at each float glass plant; this depends on the geographic location of each plant, since electricity price varies by state. **Have you annotated your notebook with clear explanations to walk the readerthrough, step by step?**All operations are commented and any referenced code is included above each operation. **Does your annotation communicate alternative paths considered in the analysisand decisions made for why particular directions were chosen?**The rationale for most operations has been explained in the comments. Comments alternatively explain what the operation aims to achieve.<jupyter_code>#Importing relevant libraries for data framing
import numpy as np
import pandas as pd
#Importing csv module
#Reference code: https://www.geeksforgeeks.org/working-csv-files-python/
import csv<jupyter_output><empty_output><jupyter_text>**Is the data collected and loaded appropriately?**Data collection is from official World of Glass datasets. Loading is done through the csv module in python. Datasets will be converted to csv files and read into the working environment as matrices. The original matrices will be cleaned and filtered for addition into pandas dataframes and subsequent addition into geopandas geo-dataframes. **Does dataset have the capacity to address the question posed in the problemStatement**The datasets include all major US-based float glass lines and the city they are located in plus industrial electricity prices for each state in 2019. These datasets are sufficient to answer the problem posed. **How were the data initially collected? Units? Any metadata should be linked or mentioned.**Data was collected from three sources: World of Glass dataset on glass plant information globally; Electrical Price rates from the US Electricity Information Administration; lat-lon coordinates from the Nominatim OpenStreetMap web database were pulled using reverse geocoding functions from the geopandas library.**Datasets are as follows:**- Electrical Prices: https://www.eia.gov/electricity/monthly/xls/table_5_06_a.xlsx- Glass Plant Locations: https://members.glass.org/cvweb/cgi-bin/msascartdll.dll/ProductInfo?productcd=WOGFLOAT- Lat-Lon Coordinates: https://nominatim.openstreetmap.org/ui/search.html<jupyter_code>#Declaring World of Glass dataset file name
WOG = "WoG_float_061220_fin.csv"
#Declaring Electrical Rate Average dataset file name
ERA = "Average_Price_of_Electricity.csv"
#Creating header list and row list for datasets
"""World of Glass Matrix"""
WOGhead = []
WOGrows = []
"""Electrical Rate Average Matrix"""
ERAhead = []
ERArows = []<jupyter_output><empty_output><jupyter_text>**Was data ingested, sufficiently cleaned, in a format that makes it amenable forvisualization, model building and analysis?**Datasets were properly ingested, cleaned, loaded, and formatted for the purpose of visualization, model building, and analysis. Specifically, the data is loaded below as csv files and processed into dataframes and eventually geo-dataframes. Irrelevant classes are discarded for the purpose of simplifying the datasets. New index values are contributed to the dataframes to supplement the analysis (e.g. adding “ ,US” to each city to constrict adding lat-long coordinates for only US-based cities).<jupyter_code>#Read in csv file
"""Assembling WOG Matrix Header and Rows"""
with open(WOG, 'r') as csvfile: 
    # creating a csv reader object 
    csvreader = csv.reader(csvfile) 
      
    # extracting header names through first row 
    WOGhead = next(csvreader)
  
    # extracting each data row one by one 
    for row in csvreader: 
        WOGrows.append(row) 
            
"""Assembling ERA Matrix Header and Rows"""
with open(ERA, 'r') as csvfile: 
    # creating a csv reader object 
    csvreader = csv.reader(csvfile) 
      
    # extracting header names through first row 
    ERAhead = next(csvreader)
  
    # extracting each data row one by one 
    for row in csvreader: 
        ERArows.append(row)
#Transpose WOG rows to columns
#Reference code: https://note.nkmk.me/en/python-list-transpose/
WOGcolsnp = np.array(WOGrows).T
"""Convert to list for input into DataFrame"""
WOGcols = WOGcolsnp.tolist()
#Creating WOG DataFrame
"""Initializing DataFrame"""
WOGDF = pd.DataFrame()
for i in range(len(WOGhead)):
    WOGDF[WOGhead[i]] = WOGcols[i]
#Transpose ERA rows to columns
ERAcolsnp = np.array(ERArows).T
"""Convert to list for input into DataFrame"""
ERAcols = ERAcolsnp.tolist()
#Creating ERA DataFrame
"""Initializing DataFrame"""
ERADF = pd.DataFrame()
for i in range(len(ERAhead)):
    ERADF[ERAhead[i]] = ERAcols[i]
#ERADF
#Filtering WOG DataFrame for relevant classes
#Relevant classes: Company Name, Country, City, State, Number of Lines
#Relevant classes index: 0, 6, 7, 8, 11
RelIndex = [0,6,7, 8, 11]
"""Initializing Filtered DataFrame"""
WOGDFF = pd.DataFrame()
for i in RelIndex:
    WOGDFF[WOGhead[i]] = WOGcols[i]
#WOGDFF<jupyter_output><empty_output><jupyter_text>**How were missing data handled**Missing data did not exist between relevant rows and so we did not need to perform any interpolation or extrapolation of values. There was empty data past a certain row in our World of Glass dataset, but this was due to the original csv format. The empty data was simply dropped from the dataframe.<jupyter_code>#Removing unpopulated rows beyond index 237
for i in range(237,len(WOGDFF[WOGhead[0]])-1):
    WOGDFF = WOGDFF.drop([i])
WOGDFF
#Filtering ERA DataFrame for relevant classes
#Relevant classes: ï»¿ (State), Industrial 12/1/2020
#Relevant classes index: 0, 5
RelIndex = [0,5]
"""Initializing Filtered DataFrame"""
ERADFF = pd.DataFrame()
for i in RelIndex:
    ERADFF[ERAhead[i]] = ERAcols[i]
ERADFF
#Correct ERA and WOG Column Header Names
WOGDFF.columns = ['Company Name', 'Country', 'City', 'State', 'Number of Lines']
WOGDFF
ERADFF.columns = ['State', 'Electrical Price']
ERADFF
#State vector for filter
#Source Code: https://gist.github.com/JeffPaine/3083347
state_names = ["AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DC", "DE", "FL", "GA", 
          "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", 
          "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", 
          "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", 
          "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY"]
#Filtering on rows for US States in WOGDFF
WOGGDFF_states = pd.DataFrame()
for i in state_names:
    is_state = WOGDFF['State'] == i
    addstate = WOGDFF[is_state]
    WOGDFF_states = WOGDFF_states.append(addstate)
WOGDFF_states
FloatLinesCount = WOGDFF_states['Number of Lines'].tolist()
FloatLinesCount = [int(i) for i in FloatLinesCount]
TotalFloatLines = sum(FloatLinesCount)
TotalFloatLines
#State vector for filter
#Source Code: https://gist.github.com/norcal82/e4c7e8113f377db184bb
state_names_ERA = ["Alaska", "Alabama", "Arkansas", "American Samoa", "Arizona", "California", "Colorado", "Connecticut", "District ", "of Columbia", "Delaware", "Florida", "Georgia", "Guam", "Hawaii", "Iowa", "Idaho", "Illinois", "Indiana", "Kansas", "Kentucky", "Louisiana", "Massachusetts", "Maryland", "Maine", "Michigan", "Minnesota", "Missouri", "Mississippi", "Montana", "North Carolina", "North Dakota", "Nebraska", "New Hampshire", "New Jersey", "New Mexico", "Nevada", "New York", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Puerto Rico", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Virginia", "Virgin Islands", "Vermont", "Washington", "Wisconsin", "West Virginia", "Wyoming"]
#Filtering on rows for US States in ERADFF
ERADFF_states = pd.DataFrame()
for i in state_names_ERA:
    is_state = ERADFF['State'] == i
    addstate = ERADFF[is_state]
    ERADFF_states = ERADFF_states.append(addstate)
ERADFF_states
#Finding out directory path
import os
os.getcwd()
#Storing our cleaned and filtered dataframes
WOGDFF_states.to_csv(r'C:\\Users\\Sleepwalk\\1 Data Analytics Final Project Data\WOGDFF_states.csv', index = False)
ERADFF_states.to_csv(r'C:\\Users\\Sleepwalk\\1 Data Analytics Final Project Data\ERADFF_states.csv', index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08608582574772432, 'fraction_numerical': 0.00658864325964456, 'mean_word_length': 5.097251585623678, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 7, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '118733', 'license': 'Apache-2.0', 'n_tokens_mistral': 3307, 'n_tokens_neox': 3096, 'n_words': 1453, 'path': 'CleanData_FilterData.ipynb', 'repo_name': 'Farisalq/Data_Analytics_EM293_Sp2021', 'size': '11535'}