{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Improving City-Business Collaboration: A Data Science Approach"},{"metadata":{},"cell_type":"markdown","source":"### Notebook by Carlos GIRONDA"},{"metadata":{},"cell_type":"markdown","source":"To enjoy a full viewing experience of <a href=\"https://nbviewer.jupyter.org/github/cgironda/CDP_prj/blob/main/CDP_final.ipynb\">this notebook</a>, you can also use nbviewer."},{"metadata":{},"cell_type":"markdown","source":"## Table of Contents <a name=\"TOC\"></a>\n1. [Required Python Libraries](#RPL)\n2. [Abstract](#abs)\n2. [Introduction](#intro)\n3. [Overview of the Cities Datasets](#overview_1)\n   - [Cities Disclosing and Cities Responses Datasets](#cdcrd)\n4. [Analyzing the Datasets](#ad)\n   - [Cities Disclosing Dataset](#cdd)\n   - [Map of the Organizations Locations](#mol)\n   - [Cities Responses Dataset](#crd)\n       - [Cities Responses by Year, Country and Question Number](#crgc)\n5. [Overview of the Corporations Datasets](#overview_2)\n   - [Corporations Disclosing and Responses Datasets](#cdrd)\n   - [Climate Change Corporations Datasets](#cccd)\n6. [Corporations: Analyzing Low-Carbon Energy Technologies](#alcet)\n7. [Cities: Analyzing Sources of Renewable Energy](#asre)\n8. [Conclusions](#conclusions)"},{"metadata":{},"cell_type":"markdown","source":"## Required Python Libraries <a name=\"RPL\"></a>\n[back to the top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"This notebook uses the following Python libraries, \n\n- Pandas: Provides a DataFrame structure to store data\n- NumPy: Provides a numerical array structure for data\n- Folium: Plotting library that provides a interactive Leaflet map\n- Matplotlib: Python 2D plotting library that produces quality figures\n- NLTK : It is a platform that help us to work with human language data\n- WordClouds: Online word cloud generator and tag cloud creator library\n- Scikit-Learn: Library that gives tools for predictive data analysis\n- Seaborn: Lybrary for data visualization to draw informative statistical graphics"},{"metadata":{},"cell_type":"markdown","source":"### Abstract <a name=\"abs\"></a>\n\nIn order to investigate the impact of low-carbon technology used by Corporations over the sources of renewable energy used by Cities in the US, datasets disclosed by the international non-profit organization CDP (Carbon Disclosure Project) were analyzed for the year 2018.\n\nTo perform this task, text preprocessing was applied, and found that Corporations tend to use Aeolic energy over other renewable sources of energy. However, cities have installed more renewable energy via photovoltaic systems."},{"metadata":{},"cell_type":"markdown","source":"## Introduction <a name=\"intro\"></a>\n[back to the top](#TOC)\n\nSeveral technologies based on sources of renewable energy, as well as operational approaches, can reduce cost-effectively energy consumption dramatically, and helps to avoid social problems providing equivalent or better quality of life and services.\n\nThese technologies can be enhanced by integrated systems of renewable energy that are less risky to implement in a natural environment by companies, but it needs information such as:\n\n   - a) The most low-carbon energy source technology used by corporations\n   - b) The amount of installed renewable energy in natural environments or cities' boundaries\n\nIf these two items can be answered, it is possible to create collaboration between cities and corporations taking account that in a periodic electric power demand cycle the pick demand of energy can be solved by the energy storage of renewable energy, such as photovoltaic systems, wind turbines, hydropower plants, etc.\n\nThe following sections explain how items a) and b) are reached, which reflect the underlying data."},{"metadata":{},"cell_type":"markdown","source":"## Overview of the Cities Datasets <a name=\"overview_1\"></a>\n[back to the top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"The code below reads the CSV files from the **Cities**, **Corporations**, and **Supplementary Data** <a href=\"https://www.kaggle.com/c/cdp-unlocking-climate-solutions/data\" target=\"_blank\">folders provided by the CDP</a>\n\nThese folders are in the folder `/kaggle/input/cdp-unlocking-climate-solutions`, and the CSV files are loaded into the `files_csv` list."},{"metadata":{"trusted":true},"cell_type":"code","source":"import glob2\n\npath = '/kaggle/input/cdp-unlocking-climate-solutions'\nfiles_csv = sorted(glob2.glob(path + '/*/**/*.csv'))\nfiles_csv","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cities Disclosing and Cities Responses Datasets<a name=\"cdcrd\"></a>\n[back to Overview of the Datasets](#overview)"},{"metadata":{},"cell_type":"markdown","source":"Let's see how the features of the `Cities_Disclosing` and `Cities Responses` csv format files are configurated for the year 2018:"},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\n\ndf_cidis18, df_cires18 = pd.read_csv(files_csv[0]), pd.read_csv(files_csv[4]) # Disclosing # Responses\nprint(df_cidis18.info())\nprint('\\n')\nprint(df_cires18.info())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now that we see what features these **CSV** files have, let's join the **Cities Disclosing** and **Cities Responses** files of 2018, 2019 and 2020."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Cities Disclosing Datasets\ndf_cidis = pd.concat([pd.read_csv(files_csv[i], encoding = \"utf-8\") for i in range(2+1)])\ncol_cidis = [*(df_cidis.columns[0:5+1]), *(df_cidis.columns[9:11+1])]\ndf_cidis = df_cidis[col_cidis].reset_index(drop=True)\n# Cities Responses Datasets\ndf_cires = pd.concat([pd.read_csv(files_csv[i], encoding = \"utf-8\", low_memory=False) for i in range(4, 6+1)])\ndf_cires = df_cires[df_cires.columns[1:15]].reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cidis.head(3)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"df_cires.head(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Analyzing the Datasets <a name=\"ad\"></a>\n[back to the top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"### Cities Disclosing Dataset<a name=\"cdd\"></a>\n[back to Analyzing the Datasets](#ad)"},{"metadata":{},"cell_type":"markdown","source":"In this section we analize the data to create a **map** that visually summarizes the information in the `df_cidis` DataFrame. The analysis starts cleaning the `City Location` feature and extract the **(latitude, longitude)** coordinates. There is no special reason to proceed in this way, however, it would be ideal to analize data that can be visualized in the aforementioned map."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Number of 'City Location' rows that does have NANs\ndf_cidis['City Location'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we insert **zero** as an string object type into the NAN places of the `df_cidis` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cidis.fillna(str(0), inplace=True) # We fill with 'zeros' the 'NAN' places\ndf_cidis.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `City Location` column is cleaned considering only elements different from **zero** string."},{"metadata":{"trusted":true},"cell_type":"code","source":"import numpy as np\nimport re\n\nlon_lat_index, lon_lat_list = ([] for l in range(2))\n\nfor idx_cl, j in enumerate(df_cidis['City Location']): # Remember that we need the 'indexes'\n    if j != str(0): # The 'City Location' column is given by 'Longitude' and 'Latitude'\n        lon_lat = tuple(map(float, re.sub(r'[POINT \\(\\)]', \" \", j).strip().split()))\n        lon_lat_list.append(lon_lat) # List of 'Longitude' and 'Latitude' - 2018, 2019, 2020\n        lon_lat_index.append(idx_cl) # List of indexes that have not NULL values in 'City Location' column","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the `City Location` feature the `longitude` and `latitude` are extracted and put them as columns into the `df_cidis_cl` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cidis_crd = df_cidis.copy() # We preserve the ORIGINAL DataFrame\ndf_lon_lat = pd.DataFrame(lon_lat_list, columns=['longitude', 'latitude'], index=lon_lat_index)\ndf_cidis_cl = pd.concat([df_cidis_crd.iloc[lon_lat_index], df_lon_lat], axis=1)\ndf_cidis_cl.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cidis_cl.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We sort values under the `'Acount Number'` column in the `df_cidis_cl` Dataframe to get the `'Population'` for every year in the `Year Reported to CDP` column that corresponds to the cities disclosure cycle survey year."},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"# 'df_cidis_cl' DataFrame has non-ZERO values in the 'City Location' column plus two additional columns\ndf_cidis_cl.sort_values(['Account Number', 'Year Reported to CDP'], inplace=True)\ndf_cidis_cl.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It must be noticed that some elements of the `City Location` column are repeated. One of the reasons is because, the `df_cidis` DataFrame is the result of joining the csv files of three consecutive years 2018, 2019 and 2020."},{"metadata":{},"cell_type":"markdown","source":"Let's find the indices of duplicate rows for the `longitude` and `latitude` columns of the `df_cidis_cl` DataFrame. We just need to keep ONE SINGLE pair `longitude` and `latitude` coordinates to visualize the city on the map.\n\nBut on the other hand, we need to know which duplicated indexes were erased, because we will use that indexes plus the non-deleted index to localize the `Year Reported to CDP` and `Population` columns that will pop-up at every `City Location` in the map."},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"# We extract the 'latitude' and 'longitude' columns from 'df_cidis_cl' DataFrame\nlat_lon_df = df_cidis_cl[['latitude', 'longitude']]\nlat_lon_df = lat_lon_df[lat_lon_df.duplicated(keep=False)] # The duplicated rows are identified\n# The following code extract the duplicated indexes of the 'latitude' and 'longitude'\nlat_lon_idx = lat_lon_df.groupby(list(lat_lon_df)).apply(lambda x: tuple(x.index)).tolist()\nlat_lon_idx[:5] # These are the FIRST 5 duplicated indexes of the 'latitude' and 'longitude'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Only the first index of every tuple is extracted and stored in the `idx0_lat_lon` list. This list is used to construct the `df_lat_lon` DataFrame from the `df_cidis_cl` DataFrame. The new DF have unique `latitude` and `longitude` rows."},{"metadata":{"trusted":true},"cell_type":"code","source":"idx0_lat_lon = [idx[0] for idx in lat_lon_idx] # Extract first indexes of every tuple in 'lat_lon_idx'\ndf_lat_lon = pd.DataFrame(df_cidis_cl[['latitude', 'longitude']].loc[idx0_lat_lon])\ndf_lat_lon.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_lat_lon.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `get_reverse_geocode_data` function used in the <a href=\"https://medium.com/@ericsalesdeandrade/how-to-call-rest-apis-with-pandas-and-store-the-results-in-redshift-2b35f40aa98f\" target=\"_blank\">article of Eric Sales</a>, helps to construct a new DataFrame that contains the addresses of their corresponding geographical coordinates.\n\nIt must be mentioned that the `get_reverse_geocode_data` function must be used with unique `latitude` and `longitud` values, contained in the `df_lat_lon` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"import requests, json, time\n\ndef get_reverse_geocode_data(row):\n    try:\n        YOUR_API_KEY = '3a4b154aa58257' # You should change to your own 'YOUR_API_KEY'\n        url = 'https://eu1.locationiq.org/v1/reverse.php?key=' + YOUR_API_KEY \\\n            + '&lat=' + str(row['latitude']) + '&lon=' + str(row['longitude']) + '&format=json'\n        response = (requests.get(url).text)\n        response_json = json.loads(response)\n        time.sleep(0.5)\n        return(response_json)\n    \n    except Exception as e:\n        raise e","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After we receive the JSON response, the `API_response` column is inserted into the `df_lat_lon` DataFrame. Once the `API_response` column is flatten into columns using the `pd.json_normalize()` command, we pick up the features that we need.\n\nUnfortuntely, the `INDEXES` are lost in this process when the `df_API` DataFrame is created but we recover these indexes from the `df_lat_lon` DataFrame later, then this `df_API` DataFrame is renamed as `df_api_clean` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_lat_lon['API_response'] = df_lat_lon.apply(get_reverse_geocode_data, axis=1)\ndf_API = pd.json_normalize(df_lat_lon['API_response']) # The `df_API` DataFrame is created\ndf_API = df_API[['lat', 'lon', 'display_name']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(df_API.head())\nprint('\\n')\nprint(df_API.tail())\nprint('\\n')\nprint(df_API.info())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The info above shows `544 non-null` elements from `560 rows`. This is because some `(latitude, longitude)` coordinates were not identified by the `get_reverse_geocode_data()` function.\n\nOn the other hand, the `lat` and `lon` columns are no longer numeric. So, the below process is applied, were indexes of `df_lat_lon` becomes indexes of `df_api`."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_API[['lat', 'lon']] = df_API[['lat', 'lon']].apply(pd.to_numeric) # 'lat', 'lon' columns are numeric\ndf_api = df_API.copy() # We preserve the original output of the `get_reverse_geocode_data` function\ndf_api.index = df_lat_lon.index # The original indexes of `df_lat_lon` are put into the `df_api`\ndf_api.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `NaN` values of the `df_api` DataFrame are expresed below together with its indexes and its total number of `NaN` values for each column."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_api_null = df_api[df_api.isnull().any(axis=1)]\nprint(df_api_null)\nprint('\\n')\nprint(df_api.isnull().sum())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Using the `idx0_lat_lon` and `df_api_null.index` lists of `NaNs` values, the `non_null_idx` is obtained to get the `df_api_clean` DataFrame which has `non-NaN` values."},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"non_null_idx = [idx for idx in idx0_lat_lon if idx not in df_api_null.index]\ndf_api_clean = df_api.loc[non_null_idx]\ndf_api_clean","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"df_api_clean.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `df_api_clean` DataFrame above is clean of `NaNs` elements. \n\nThe `lat_lon_list` list below, contains a list of `tuple` elements that are used to identify the cities in a `Folium map`. The `lat_lon_idx` is used to create the `df_citis_flt` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"lat_lon_list = list(df_api_clean.to_records())\nlat_lon_list[0:5] # These are the first five tuples in the 'lat_lon_list' list","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To clean the `df_citis_cl` DataFrame free of `NaN` elements in its `longitude` and `latitude` columns, the `df_citis_flt` is created.\n\nWe extract the `NaNs` indexes of tuples from the total `lat_lon_idx` list into the `idx_nan` list. \nFrom there, we create the `idx_nonan` list of tuples which is flatten to obtain the `df_citis_flt` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"idx_nan = [] # List of tuples of indexes that will contain rows with 'NaNs' elements\nfor i in lat_lon_idx:\n    for j in df_api_null.index: # We need the NaNs indexes of the 'df_api_null' DataFrame\n        if i[0] == j:\n            idx_nan.append(i)\n\nidx_nonan = [i for i in lat_lon_idx if i not in idx_nan] # List of tuples of indexes that have non-NaNs\nidx_flatten = [j for i in idx_nonan for j in i] # List of tuples of indexes that were flatten ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_citis_flt = df_cidis_cl.loc[idx_flatten]\ndf_citis_flt.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After a deep analysis over the `df_citis_flt` DataFrame, it shows that the `Account Number` and `City Location` columns are not correlated, i.e. the number of unique identifiers `Account Number` given to every city organisation that receives a request to complete a CDP questionnaire which is **551**, is not equal to the number of elements of the unique coordinates **544** of the `City Location` column as shown below using the `citis_flt()` function:"},{"metadata":{"trusted":true},"cell_type":"code","source":"def citis_flt(df, col1, col2):\n    years = 3 # The 'City Location' shows up to three times\n    col1_u, col2_u = df[col1].nunique(), df[col2].nunique()\n    df_g = df.groupby([col2]).filter(lambda x: len(x) > years)\n    df_nu = df_g[col2].nunique()\n    return([col1_u, col2_u, df_nu, df_g])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For the years 2018, 2019, 2020, the `City Location` coordinates should be shown up to **three times** unless that for the same location there are different unique identifiers in the `Account Number`. In this case, it appears **five** times as shown below."},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"citis_flt(df_citis_flt, 'Account Number', 'City Location')[0:2+1]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As shown below there are **26** unique identifiers in the `Account Number` that are not count in the rest of the analysis. It has been proceed in this way, because in some cases there is no information about the `Population` in some particular year for the `Year Reported to CDP`  column, or because the same **population** is used for differents identifiers in the `Account Number` column."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_citis_nou = citis_flt(df_citis_flt, 'Account Number', 'City Location')[3]\ndf_citis_nou.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After extracting the indexes from the `df_citis_nou` DataFrame, the `df_citis_u` unique DataFrame is obtained:"},{"metadata":{"trusted":true},"cell_type":"code","source":"idx_u = [i for i in df_citis_flt.index if i not in df_citis_nou.index]\ndf_citis_u = df_citis_flt.loc[idx_u]\ndf_citis_u.head() # Very last clean DataFrame","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_citis_u.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Map of the Organizations Locations <a name=\"mol\"></a>\n[back to Analyzing the Datasets](#ad)"},{"metadata":{},"cell_type":"markdown","source":"The following code extract the **Year Reported to CDP, City,** and **Population** features for a unique  **(latitude, longitude)** coordinates, from the `df_citis_u` DataFrame.\n\nEvery location in the map below shows its address (after hover the pointer of the mouse on it), thanks to the `get_reverse_geocode_data()` function that use an **API**, which is obtained after creating a FREE account in the `https://locationiq.com/` website. \n\nAfter that, if you make a **click** with the mouse over any location in the map, it also shows a **table** with the `Year Reported to CDP`, `City`, and `Population` features obtained from the `df_citis_u` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"# We group by 'longitude' and 'latitude' and print 'Year Reported to CDP', 'City', 'Population' columns\ndf_citis_flt_cp = df_citis_u.copy() # We preserve the 'df_citis_flt' DataFrame\nflt_value, flt_key = ([] for l in range(2))\nfor key, value in df_citis_flt_cp.groupby(by=['latitude', 'longitude']):\n    df_citis_mod = value[['Year Reported to CDP', 'City', 'Population']]\n    flt_value.append(df_citis_mod) # This list gives a DataFrame of the columns specified in the 'value' item\n    flt_key.append(key) # This list gives the (longitude, latitude) coordinate","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import folium, branca\nimport folium.plugins as fp\n\nmap_osm = folium.Map(location=[50.7128, 44.0060], zoom_start=2.49, tiles='Stamen Terrain'\n                     , max_bounds=True, scrollWheelZoom=False, no_wrap=True)\n\nmarker_cluster = fp.MarkerCluster().add_to(map_osm)\n\nloc_df = [(i, j) for i, j in zip(flt_key, flt_value)]\nfor crd in lat_lon_list:\n    for df in loc_df:\n        if crd[0] == df[1].index[0]:\n            address = str([k.lstrip() for k in crd[3].split(',')][:])[1:-1]\n            html = df[1].to_html(classes='table table-striped table-hover table-condensed table-responsive')\n            popup_op = folium.Popup(html, max_width='100%')\n            folium.Marker(location=[crd[1], crd[2]], popup=popup_op, tooltip=address).add_to(marker_cluster) \n            map_osm.add_child(folium.LatLngPopup(), folium.ClickForMarker(popup='Waypoint'))\nmap_osm","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The above geographical map does show the number of cities per region that answered the questions of the CDP."},{"metadata":{},"cell_type":"markdown","source":"The function below `year_nation_city_pop()` is used to obtain the DataFrames, that help us to get the 2D figures of the **twenty** most populated US cities in 2018, 2019, and 2020.\n\nUnlike the **map** shown above, the 2D graphics below show the same information in a more compact way."},{"metadata":{"trusted":true},"cell_type":"code","source":"def year_nation_city_pop(df, year, country, org, pop):\n    \n    year_CDP = df['Year Reported to CDP'] == year\n    nation = df['Country'] == country\n    df_u = df[year_CDP & nation]\n\n    org_pop_list = []\n    for i, j in df_u.groupby(by=pop):\n        if int(i) != 0: # 'Population' --> integers <> This is to avoid 'zero' rows\n            k = j[org].array[0]\n            org_pop_list.append(tuple([k, i]))\n    df_n = pd.DataFrame(org_pop_list, columns=[org, pop])\n\n    return(df_n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def df_of_df(df, year):\n    df_pop = year_nation_city_pop(df, year, 'United States of America', 'Organization', 'Population')\n    return(df_pop)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%matplotlib inline\nimport matplotlib.pyplot as plt\n\nplt.style.use('fivethirtyeight')\n\nplt.figure()\n\ndf_short_18 = df_of_df(df_citis_u, 2018).iloc[0:20]\nplt.subplot(2, 2, 1)\ndf_short_18.plot(x = 'Organization', y = 'Population', figsize = (15, 12), kind='barh', fontsize=14\n                , legend=False, title='20 Most Populated US Cities in 2018', color='#00a6d2'\n                , ax=plt.gca())\nplt.xlabel('Population', color='black')\nplt.ylabel('Organizations', color='black')\nfor i in range(len(df_short_18)):\n    plt.text(23000, i - 0.3, int(df_short_18['Population'][i]))\n## --------------------------\ndf_short_19 = df_of_df(df_citis_u, 2019).iloc[0:20]\nplt.subplot(2, 2, 2)\ndf_short_19.plot(x = 'Organization', y = 'Population', figsize = (15, 12), kind='barh', fontsize=14\n                 , legend=False, title='20 Most Populated US Cities in 2019', color='#f9c642'\n                 , ax=plt.gca())\nplt.xlabel('Population', color='black')\nplt.ylabel('Organizations', color='black')\nfor i in range(len(df_short_19)):\n    plt.text(30000, i - 0.3, int(df_short_19['Population'][i]))\n## --------------------------    \ndf_short_20 = df_of_df(df_citis_u, 2020).iloc[0:20]\nplt.subplot(2, 2, 3)\ndf_short_20.plot(x = 'Organization', y = 'Population', figsize = (15, 12), kind='barh', fontsize=14\n                 , legend=False, title='20 Most Populated US Cities in 2020', color='#4aa564'\n                 , ax=plt.gca())\nplt.xlabel('Population', color='black')\nplt.ylabel('Organizations', color='black')\nfor i in range(len(df_short_20)):\n    plt.text(33000, i - 0.3, int(df_short_20['Population'][i]))\n\nplt.tight_layout(pad=1.5)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cities Responses Dataset<a name=\"crd\"></a>\n[back to Analyzing the Datasets](#ad)"},{"metadata":{},"cell_type":"markdown","source":"In this section, we select from the `df_cires` DataFrame the `Question Number` feature and its corresponding row on the`Response Answer` feature based on its `Section` feature.\n\nRemember that:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cires.head(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's use the indexes of the `df_citis_u` DataFrame to clean the `df_cires` DataFrame based on the `Account Number` column:"},{"metadata":{"trusted":true},"cell_type":"code","source":"ac_citis_u = df_citis_u['Account Number'].unique() # Cleaning list\nac_cires = df_cires['Account Number'].unique()\nac_cires_u = [i for i in ac_cires if i in ac_citis_u] # List that contains unique numbers in 'Account Number'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"idx_ac_cires_u = [] # Lists of indexes of unique numbers in 'Account Number' column of 'df_cires' DataFrame\nfor idx, ide in enumerate(df_cires['Account Number']):\n    if ide in ac_cires_u:\n        idx_ac_cires_u.append(idx)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `df_cires_unique` DataFrame contains the identification numbers for the `Account Number` that are in the `df_cities_u`, which has been sorted according to the `Account Number` and `Year Reported to CDP` columns."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cires_unique = df_cires.loc[idx_ac_cires_u]\ndf_cires_unique.sort_values(['Account Number', 'Year Reported to CDP'], inplace=True)\ndf_cires_unique.info()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"df_cires_unique.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The last step is to clean the `df_cires_unique` DataFrame where the `Response Answer` column has rows of `NaN` values:"},{"metadata":{"trusted":true},"cell_type":"code","source":"idx_cires_u = df_cires_unique.index\nidx_ra = df_cires_unique[df_cires_unique['Response Answer'].isnull()].index\nidx_u = [i for i in idx_cires_u if i not in idx_ra]\ndf_cires_u = df_cires_unique.loc[idx_u]\ndf_cires_u.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cires_u.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This `df_cires_u` DataFrame was obtained from the indexes of the `df_citis_u` DataFrame based on its `Account Number` column."},{"metadata":{},"cell_type":"markdown","source":"#### Cities Responses by Year, Country and Question Number<a name=\"crgc\"></a>\n[back to Cities Responses Dataset](#crd)"},{"metadata":{},"cell_type":"markdown","source":"In the following analysis the `year_country()` function is build using the `df_citis_u` and `df_cires_u` DataFrames.\n\nThis function can be used to select any **country** inside those Dataframes for the 2018, 2019, and 2020 years."},{"metadata":{"trusted":true},"cell_type":"code","source":"cires_key, cires_value = ([] for l in range(2))\ngroup_cols = df_cires_u.columns[0:4+1].tolist() # Columns grouped from the 'df_cires_u' DataFrame\nshown_cols = [*df_cires_u.columns[1:2+1], *df_cires_u.columns[5:]] # Columns shown after grouping the columns\nfor key, value in df_cires_u.groupby(by=group_cols):\n    df_cires_mod = value[shown_cols]\n    cires_key.append(key)\n    cires_value.append(df_cires_mod)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `year_country()` function gives the `year_country()[0]` and `year_country()[1]` outputs, that helps to isolate the `Response Answer` columns to every `Organization` together with its `Account Number` for any of the 2018, 2019, and 2020 years."},{"metadata":{"trusted":true},"cell_type":"code","source":"def year_country(year, country, sort_column): # It allows to chose the 'year', 'country', and 'sort_column' \n    key_list, value_list = ([] for l in range(2))\n    for i, j in zip(cires_key, cires_value):\n        if i[0] == year and i[3] == country:\n            key_list.append((i[0], i[1], i[2]))\n            value_list.append(j.sort_values([sort_column])) # The DataFrame is sort by column\n    return([key_list, value_list])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def question_number(year, country, sort_column):\n    fn = year_country(year, country, sort_column)\n    df_list= []\n    for k in range(len(fn[1])):\n        for i, j in fn[1][k].groupby(by=[sort_column]):\n            df_j = j[fn[1][k].columns]\n            df_list.append(df_j)\n    df_q_n = pd.concat(df_list).sort_values([sort_column])\n    return(df_q_n)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In the following, let's do the analysis for the **US** in 2018 year, specifically for the `Question Number` column."},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"df_US_2018_res = question_number(2018, 'United States of America', 'Question Number')\nprint(df_US_2018_res.info())\nprint('\\n')\nprint(df_US_2018_res['Section'].unique())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Overview of the Corporations Datasets<a name=\"overview_2\"></a>\n[back to top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"### Corporations Disclosing and Responses Datasets<a name=\"cdrd\"></a>\n[back to Overview of the Corporations Datasets](#overview_2)"},{"metadata":{},"cell_type":"markdown","source":"The `files_csv` list of files below, is needed to concatenate the `Corporation Disclosing` Datasets for the 2018, 2019 and 2020 years, as well as, the `Corporation Responses` Datasets."},{"metadata":{"trusted":true},"cell_type":"code","source":"files_csv","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\n\n# Corporation Disclosing Datasets\n## The file_csv[11] --> 'Corporations_Disclosing_to_CDP_Data_Dictionary.csv'\ndf_codis = pd.concat([pd.read_csv(files_csv[i]) for i in range(8, 14+1) if i != 11]) # range(8, 14+1) --> 'files_csv'\ncol_codis = [*(df_codis.columns[0:3+1]), *(df_codis.columns[9:20+1])] # Some columns are not included\ndf_codis = df_codis[col_codis].reset_index(drop=True)\n\n# Corporation Responses Datasets\n## The file_csv[18] --> 'Full_Corporations_Response_Data_Dictionary copy.csv', is not included\ndf_cores = pd.concat([pd.read_csv(files_csv[i], low_memory=False) for i in range(16, 22+1) if i != 19])\ncol_cores = [*(df_cores.columns[0:2+1]), *(df_cores.columns[7:18+1])] # Some columns are not included\ndf_cores = df_cores[col_cores].reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(df_codis.info())\nprint('\\n')\nprint(df_cores.info())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Then, the `df_codis` corporation disclosing dataset and the `df_cores` corporation responses dataset are **merged** over the `account_number`, `organization`, and `survey_year` features."},{"metadata":{"trusted":true},"cell_type":"code","source":"merge_cols = ['account_number', 'organization', 'survey_year']\ndf_codisres = pd.merge(df_codis, df_cores, on=merge_cols, how='inner') # The indexes must be kept\ndf_codisres.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `df_codisres` DataFrame is sorted by the three columns below, such that the `df_codisres` DataFrame would be ordered in descending form ruled by the `question_number` column.\n\nIn this way, it is less time consuming extract at once the `response_value` for every question of the **Questionary** for 2018, 2019 and 2020."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_codisres.sort_values(['question_number', 'account_number', 'survey_year'], inplace=True)\ndf_codisres.head(3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_codisres.tail(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next, the `df_codisres` DataFrame is cleaned extracting the `NaNs` values of the `response_value` feature by using the `get_nonan_df()` function, and then obtaining the `df_codisres_u` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_nonan_df(df, colnan):\n    idx = df.index\n    idx_nan = df[df[colnan].isnull()].index\n    idx_u = [i for i in idx if i not in idx_nan]\n    df_u = df.loc[idx_u]\n    return(df_u)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_codisres_u = get_nonan_df(df_codisres, 'response_value')\ndf_codisres_u.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Climate Change Corporations Datasets<a name=\"cccd\"></a>\n[back to Overview of the Corporations Datasets](#overview_2)"},{"metadata":{},"cell_type":"markdown","source":"The `df_year_nation_glbissue()` function select the `df` DataFrame by `year`, `nation`, and **global issue**."},{"metadata":{"trusted":true},"cell_type":"code","source":"def df_year_nation_glbissue(df, year, nation, glbissue):\n    col_1, col_2, col_3 = 'survey_year', 'country', 'theme'\n    survey, country, glbissue = df[col_1] == year, df[col_2] == nation, df[col_3] == glbissue\n    df_res = df[survey & country & glbissue]\n    return(df_res)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In the following, the function above is used to obtain the `df_cc_2018` DataFrame for the US with `Climate Change` as global issue in 2018. *(Note.- Because of the time the analysis is narrowed to just 2018, however the code was written in such a way that can be used for any particular year, i.e., 2018, 2019, or 2020).*\n\nFrom this DataFrame the unique modules of the `module_name` column are obtained as shown below: "},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"# 'Climate Change' based DataFrame\ndf_cc_2018 = df_year_nation_glbissue(df_codisres, 2018, 'United States of America', 'Climate Change')\ndf_cc_2018['module_name'].unique() # From 'df_codisres_u.info()' above we select the 'module_name' column","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `module_of_name()` function below obtain the DataFrame for the **Energy** module from the `df_cc_2018` DataFrame."},{"metadata":{"trusted":true},"cell_type":"code","source":"def name_of_module(df, module, endstr): # module: columns's name, endstr: letters in which end the string\n    \n    module_key, module_value, name_module = ([] for l in range(3))   \n    for key, value in df.groupby(by=module):\n        module_key.append(key)\n        module_value.append(value)\n    for i, j in zip(module_key, module_value):\n        if i.endswith(endstr):\n            name_module.append(j) # 'name_module' has only ONE element --> [0]\n    \n    return(name_module[0]) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_energy18 = name_of_module(df_cc_2018, 'module_name', 'nergy')\ndf_energy18.head(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the `question_number` column of the `df_energy18` DataFrame shown below, inside the question `C8.2f` there is a question related to the **low carbon technology type**, as shown by the `column_name` column, that is extracted to its analysis."},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"print(df_energy18['question_number'].unique().tolist())\nprint('\\n')\nprint(df_energy18['column_name'].unique())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"With the help of the `df_carbon()` function below we obtain the `response_value` column for each of the the five `C8.2f` questions above.\n\nThe `response_value` column of the output `qC2` DataFrame obtained via the `df_carbon()` function will help to analize the question related to the **low carbon technology type** in the next section."},{"metadata":{"trusted":true},"cell_type":"code","source":"def df_carbon(df, que_num, que, col_name, analysis):\n    \n    for i, j in df.groupby(by=que_num): # Grouped by 'que_num' column\n        if i == que: # 'que' is the <<question>> in the 'que_num' column\n            j.sort_values(col_name, inplace=True) # Sorted by 'col_name' column\n    df_all = get_nonan_df(j, analysis) # 'get_nonan_df()' clean the 'analysis' column of the 'j' DataFrame\n\n    questions = df_all[col_name].unique().tolist()\n    carbon_questions = []\n    for q in questions:\n        for i, j in df.groupby(by=col_name):\n            if i == q:\n                carbon_questions.append(get_nonan_df(j, analysis))\n    \n    return(carbon_questions)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"qC1, qC2, qC3, qC4, qC5 = df_carbon(df_energy18, 'question_number', 'C8.2f', 'column_name', 'response_value')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see below, the `qC2` DataFrame is related with the **Low-Carbon Technology** question. "},{"metadata":{"trusted":true},"cell_type":"code","source":"df_qC2 = qC2[['column_name', 'response_value']]\ndf_qC2.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Corporations: Analyzing Low-Carbon Energy Technologies<a name=\"alcet\"></a>\n[back to the top](#TOC)"},{"metadata":{"trusted":true},"cell_type":"code","source":"import nltk, string\nfrom nltk.tokenize import word_tokenize # our tokenizer\nfrom nltk.corpus import stopwords # used for preprocessing\nfrom nltk.stem import WordNetLemmatizer # used for preprocessing\nnltk.download('stopwords')\nnltk.download('wordnet')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(df_qC2.info())\nprint('\\n')\nprint(df_qC2.shape)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In the **preprocessing text analysis** below, I used the `preprocessing()` function that I found in <a href=\"https://github.com/stgran/Coursework/blob/master/Practical%20Data%20Science/Preprocessing_Text_Data_in_Python.ipynb\" target=\"_blank\">this link</a>:"},{"metadata":{"trusted":true},"cell_type":"code","source":"# remove urls, handles, and the hashtag from hashtags, see the link below: \n#https://stackoverflow.com/questions/8376691/how-to-remove-hashtag-user-link-of-a-tweet-using-regular-expression\ndef remove_urls(text):\n    clean_text = ' '.join(re.sub(\"(@[A-Za-z0-9]+)|([^0-9A-Za-z \\t])|(\\w+:\\/\\/\\S+)\",\" \", text).split())\n    return(clean_text)\n\n# make all text lowercase\ndef text_lowercase(text): \n    return(text.lower())\n\n# remove numbers\ndef remove_numbers(text): \n    nonumbers = re.sub(r'\\d+', '', text) \n    return(nonumbers)\n\n# remove punctuation\ndef remove_punctuation(text): \n    nopunct = str.maketrans('','', string.punctuation)\n    return(text.translate(nopunct))\n\n# tokenize\ndef tokenize(text):\n    words = word_tokenize(text)\n    return(words)\n\n# remove stopwords\nstop_words = set(stopwords.words('english'))\ndef remove_stopwords(text):\n    words = [w for w in text if w not in stop_words]\n    return(words)\n\n# lemmatize\nlemmatizer = WordNetLemmatizer()\ndef lemmatize(text):\n    lem_text = [lemmatizer.lemmatize(token) for token in text]\n    return(lem_text)\n\ndef preprocessing(text):\n    text = text_lowercase(text)\n    text = remove_urls(text)\n    text = remove_numbers(text)\n    text = remove_punctuation(text)\n    text = tokenize(text)\n    text = remove_stopwords(text)\n    text = lemmatize(text)\n    text = ' '.join(text) # rejoins the list of tokens\n    return(text)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The **\"low carbon technology please specify\"** sentence appears frecuently in the `response_value` column of the `df_qC2` DataFrame. So the `unused_sentence()` function helps to erase it."},{"metadata":{"trusted":true},"cell_type":"code","source":"def unused_sentence(text):\n    sentence = re.sub(r'\\b\\w*(low carbon technology|please specify|renewable energy|including)\\w*\\b', '', text)\n    sentence = sentence.replace('solar pv', 'solarpv').strip()\n    return(sentence)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_qC2_copy = df_qC2.copy() # The original DataFrame is preserved\nresponse_clean = df_qC2_copy['response_value'].apply(lambda x: preprocessing(x)) \\\n                                              .apply(lambda x: unused_sentence(x))\ndf_qC2_copy['response_clean'] = response_clean\ndf_qC2_copy.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In order to verify if the preprocessing text analysis was done correctly, let's get a visual representation of the most common words used in the `response_clean` column to know if more preprocessing text is necessary.\n\nSo we need to put the `response_clean` column in a **single string** as shown below, after importing the `wordcloud` libfrary."},{"metadata":{"trusted":true},"cell_type":"code","source":"def word_cloud(df, col):\n    # Import the wordcloud library\n    from wordcloud import WordCloud\n\n    single_string = ','.join(df[col].tolist())\n    # Create a WordCloud object\n    wordcloud = WordCloud(background_color=\"white\", max_words=1000, contour_width=2, contour_color='steelblue')\n    wordcloud.generate(single_string)\n    return(wordcloud.to_image())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"word_cloud(df_qC2_copy, 'response_clean')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see from the image above, words like **wind** and **solar pv**, appears frequently in the text.\n\nLet's see how offen these and other words are common in the whole text."},{"metadata":{"trusted":true},"cell_type":"code","source":"def words_counts(df, col):\n    \n    from sklearn.feature_extraction.text import CountVectorizer\n\n    vectorizer = CountVectorizer(stop_words='english')\n    count_data = vectorizer.fit_transform(df[col].tolist())\n\n    words = vectorizer.get_feature_names()\n    counts = np.zeros(len(words))\n    for i in count_data:\n        counts += i.toarray()[0] # Extract the first element of every array\n\n    words_counts = zip(words, counts)\n    words_counts = sorted(words_counts, key=lambda x:x[1], reverse=True)[0: 7+1]\n    df_words_counts = pd.DataFrame(words_counts, columns=['words', 'counts'])\n    \n    return(df_words_counts)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"words_counts(df_qC2_copy, 'response_clean')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%matplotlib inline\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nplt.style.use('fivethirtyeight')\nplt.figure(figsize=(8,5))\nplt.title('Most Common Words for Low-Carbon Source of Energy in 2018')\ndf_w_c_co = words_counts(df_qC2_copy, 'response_clean')\nfor i in range(len(df_w_c_co)):\n    plt.text(220, i + 0.2, int(df_w_c_co['counts'][i]), weight='bold')\n\nsns.set(font_scale = 1.5)\nax = sns.barplot(y = \"words\", x = \"counts\", data = df_w_c_co)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After the preprocessing text analysis over the `response value` column of the `df_energy18` DataFrame, it can be seen that **wind, solar PV, and hydropower** are the most predominant words. In some cases, the Corporations support **PV solar power**, **wind power**, and **hydropower** energy generation at the same time as renewable energy sources."},{"metadata":{},"cell_type":"markdown","source":"## Cities: Analyzing Sources of Renewable Energy<a name=\"asre\"></a>\n[back to the top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"From [this sub section](#crgc), remember that the `question_number()` function was used to obtain the `df_US_2018_res` DataFrame:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_US_2018_res.head(3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(df_US_2018_res.info()) # It shows the info about the 'columns' of the DataFrame\nprint('\\n')\nprint(df_US_2018_res['Section'].unique()) # It shows the 'Sections' of the questionary\nprint('\\n')\nprint(df_US_2018_res['Question Number'].unique()) # It shows the 'Question Numbers' of the questionary","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `df_renewable_energy()` function below, helps to obtain the `Row Name` and `Response Answer` for the `9.1` question of the `Question Number` column inside `Section` column, which is related with the amount of **renewable energy** (in MW capacity) installed within the city boundary in the categories of **solar PV**, **solar thermal**, **ground or water source**, **wind** or other."},{"metadata":{"trusted":true},"cell_type":"code","source":"def df_renewable_energy(df, col1, col2, num):\n    for i, j in df.groupby(by=col1):\n        if i == 'Energy':\n            df_j = j[j[col2] == num]\n    return(df_j)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_energy_18 = df_renewable_energy(df_US_2018_res, 'Section', 'Question Number', '9.1')\ndf_e_18 = df_energy_18[['Row Name', 'Response Answer']]\ndf_e_18","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The `erase_sentence()` function below helps erase unnecessary text and transform text into a single string words like: **solar pv** into **solarpv**."},{"metadata":{"trusted":true},"cell_type":"code","source":"def erase_sentence(text):\n    sentence = re.sub(r'\\b\\w*(renewable district heat cooling)\\w*\\b', '', text)\n    sentence = sentence.replace('solar pv', 'solarpv').strip() #'solar pv' is a repeated word\n    sentence = sentence.replace('ground water source', 'GroundWaterSource').strip()\n    sentence = sentence.replace('solar thermal', 'SolarThermal').strip() #'solar thermal' is a repeated word\n    return(sentence)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The preprocessing text analysis is applied below:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_e_18_copy = df_e_18.copy() # The original DataFrame is preserved\nRow_Name_Clean = df_e_18_copy['Row Name'].apply(lambda x: preprocessing(x))\\\n                                         .apply(lambda x: erase_sentence(x))\ndf_e_18_copy['Row_Name_Clean'] = Row_Name_Clean\ndf_e_18_copy.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see, there are some empty spaces in the new `Row_Name_Clean` column.\n\nAs shown below, after deleting these empty spaces, the amount of **renewable energy (in MW)** by 'Wind' and 'Solar' sources are extrated."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Some some empty rows are deleted applying the code below.\ndf_e_18_copy = df_e_18_copy[df_e_18_copy['Row_Name_Clean'].str.strip().astype(bool)]\n# The 'Response Answer' column is transformed into a 'float' column\ndf_e_18_copy['Response Answer'] = df_e_18_copy['Response Answer'].apply(float)\n# The amount of 'renewable energy (in MW)' by 'Wind' and 'Solar' sources are extrated\ndf_e_18_MW_s = df_e_18_copy[df_e_18_copy['Response Answer'] > 500]\ndf_e_18_MW_w = df_e_18_copy[(df_e_18_copy['Response Answer'] > 0.0) & (df_e_18_copy['Response Answer'] < 1)]\nprint('Solar Sources:')\nprint(df_e_18_MW_s)\nprint('\\n')\nprint('Wind Sources:')\nprint(df_e_18_MW_w)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"According to the **most common words for installed sources of renewable energy** graphic below, there is a correlation with the amount of installed renewable energy via Solar Power Systems that cities have.\n\nAccording to the table above, there are more cities that have installed renewable energy via photovoltaic systems than via wind devices."},{"metadata":{"trusted":true},"cell_type":"code","source":"%matplotlib inline\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nplt.style.use('fivethirtyeight')\nplt.figure(figsize=(8,5))\nplt.title('Most Common Words for Installed Sources of Energy in the Cities 2018')\ndf_w_c_ci = words_counts(df_e_18_copy, 'Row_Name_Clean')\nfor i in range(len(df_w_c_ci)):\n    plt.text(52, i + 0.1, int(df_w_c_ci['counts'][i]), weight='bold')\n\nsns.set(font_scale = 1.5)\nax = sns.barplot(y = \"words\", x = \"counts\", data = df_w_c_ci)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Conclusions<a name=\"conclusions\"></a>\n[back to the top](#TOC)"},{"metadata":{},"cell_type":"markdown","source":"After analyzing the impact of low-carbon technology used by Corporations over the sources of renewable energy used by Cities in the US during 2018, has been found that,\n\n - a) The most low-carbon energy source technology used by Corporations are predominantly wind devices, photovoltaic systems that store solar energy, and hydropower energy.\n\n - b) The amount of installed renewable energy in natural environments or cities' boundaries is mainly solar energy and wind energy, as shown in the graphics of the last two sections.\n\nSo, it is possible to generate a collaboration bridge between corporations and cities, considering that the pick demand for electricity by the cities can be balanced by the energy storage of renewable energy given by the corporations. This also would reduce the cost-effectiveness of energy consumption in cities, giving a better quality of life in the society."}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}