Skip to content

Instantly share code, notes, and snippets.

@chelseatroy

chelseatroy/investigating_esg_ratings_and_stock_performance.ipynb

Last active February 19, 2018 02:44
Show Gist options
  • Save chelseatroy/3df3c176a998bb2a9fec87c3f5dd6313 to your computer and use it in GitHub Desktop.
Save chelseatroy/3df3c176a998bb2a9fec87c3f5dd6313 to your computer and use it in GitHub Desktop.
Download ZIP
Investigating ESG Ratings and Stock Performance
Display the source blob
Display the rendered blob
Raw
investigating_esg_ratings_and_stock_performance.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Correlating KLD ESG Ratings to Stock Performance, 1990-2005\n",
"\n",
"Let's determine whether we notice any correlation between companies' environmental, social, and governmental ratings and their stock performance. \n",
"\n",
"### First, we pull in the ESG data. \n",
"\n",
"These come from KLD and are now distributed by MSGI. I pulled them from an academic database. Don't rerun this notebook because I didn't push the actual data to Github. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"y91 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1991 HistoricalSpreadsheet_STATS.xls')\n",
"y92 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1992 HistoricalSpreadsheet_STATS.xls')\n",
"y93 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1993 HistoricalSpreadsheet_STATS.xls')\n",
"y94 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1994 HistoricalSpreadsheet_STATS.xls')\n",
"y95 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1995 HistoricalSpreadsheet_STATS.xls')\n",
"y96 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1996 HistoricalSpreadsheet_STATS.xls')\n",
"y97 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1997 HistoricalSpreadsheet_STATS.xls')\n",
"y98 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1998 HistoricalSpreadsheet_STATS.xls')\n",
"y99 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1999 HistoricalSpreadsheet_STATS.xls')\n",
"\n",
"nineties = [y91, y92, y93, y94, y95, y96, y97, y98, y99]"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"y00 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2000 HistoricalSpreadsheet_STATS.xls')\n",
"y01 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2001 HistoricalSpreadsheet_STATS.xls')\n",
"y02 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2002 HistoricalSpreadsheet_STATS.xls')\n",
"y03 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2003 HistoricalSpreadsheet_STATS.xls')\n",
"y04 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2004 HistoricalSpreadsheet_STATS.xls')\n",
"y05 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2005 Historical Spreadsheet_STATS.xls') #wth KLD\n",
"y06 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2006 Historical Spreadsheet_STATS.xls')\n",
"y07 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2007 HistoricalSpreadsheet_STATS.xls')\n",
"y08 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2008 HistoricalSpreadsheet_STATS.xls')\n",
"y09 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2009 HistoricalSpreadsheet_STATS.xls')\n",
"\n",
"\n",
"two_thousands = [y00, y01, y02, y03, y04]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"I wanted to run 1990-2009, but evidently starting in 2005 these spreadsheets no longer represent whether a company was in the S&P500 in the same way. That's okay: we can do this for a fifteen-year period wrangle in more data later if we would like to see a longer period of time.\n",
"\n",
"Now let's pull out the companiess that belong to the S&P 500. We'll begin by examining their ESG scores on four metrics: employment policy strengths, employment policy concerns, environmental impact strengths, and environmental impact concerns. These companies get a score of zero (0) or one (1) each year on each of several sub-metrics. For example, employment policy strengths include specific scores for workplace safety, compensation, union management, et cetera. \n",
"\n",
"We're going to sum up all of the sub-metrics for each metric per company, and then we're going to sum that company's total scores in that metric across our fifteen year time span. We'll end up with a company score of cumulative strengths and concerns in employment and environmental practices over the course of the fifteen years. "
]
},
{
"cell_type": "code",
"execution_count": 247,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>ALC-con-#</th>\n",
" <th>ALC-con-A</th>\n",
" <th>ALC-con-X</th>\n",
" <th>BMS</th>\n",
" <th>CGOV-con-#</th>\n",
" <th>CGOV-con-B</th>\n",
" <th>CGOV-con-F</th>\n",
" <th>CGOV-con-G</th>\n",
" <th>CGOV-con-H</th>\n",
" <th>CGOV-con-I</th>\n",
" <th>...</th>\n",
" <th>Russell2000</th>\n",
" <th>SP500</th>\n",
" <th>TOB-con-#</th>\n",
" <th>TOB-con-A</th>\n",
" <th>TOB-con-X</th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>True</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>ABT</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>True</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>AMT</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>True</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>AMD</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>True</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>AET</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>True</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>NR</td>\n",
" <td>AHM</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 127 columns</p>\n",
"</div>"
],
"text/plain": [
" ALC-con-# ALC-con-A ALC-con-X BMS CGOV-con-# CGOV-con-B CGOV-con-F \\\n",
"0 0 0 NR NaN 0 0 0 \n",
"1 0 0 NR NaN 0 0 0 \n",
"3 0 0 NR NaN 0 0 0 \n",
"4 0 0 NR NaN 0 0 0 \n",
"6 0 0 NR NaN 0 0 0 \n",
"\n",
" CGOV-con-G CGOV-con-H CGOV-con-I ... Russell2000 SP500 TOB-con-# \\\n",
"0 NR NR NR ... NaN True 0 \n",
"1 NR NR NR ... NaN True 0 \n",
"3 NR NR NR ... NaN True 0 \n",
"4 NR NR NR ... NaN True 0 \n",
"6 NR NR NR ... NaN True 0 \n",
"\n",
" TOB-con-A TOB-con-X Ticker EMP-str-sum EMP-con-sum ENV-str-sum \\\n",
"0 0 NR ABT 0 0 0 \n",
"1 0 NR AMT 0 0 0 \n",
"3 0 NR AMD 1 0 0 \n",
"4 0 NR AET 1 0 0 \n",
"6 0 NR AHM 0 0 0 \n",
"\n",
" ENV-con-sum \n",
"0 1 \n",
"1 0 \n",
"3 1 \n",
"4 0 \n",
"6 0 \n",
"\n",
"[5 rows x 127 columns]"
]
},
"execution_count": 247,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"twenty_years = nineties + two_thousands\n",
"\n",
"def filter_500(df):\n",
" return df[df['SP500'] == True]\n",
"\n",
"sp500_90s = [] \n",
"for data in twenty_years:\n",
" sp500_90s.append(filter_500(data))\n",
"\n",
"aggregate_data = pd.concat(sp500_90s)\n",
"aggregate_data['EMP-str-sum'] = aggregate_data[['EMP-str-A','EMP-str-B','EMP-str-C','EMP-str-D','EMP-str-F','EMP-str-G']].sum(axis=1)\n",
"aggregate_data['EMP-con-sum'] = aggregate_data[['EMP-con-A','EMP-con-B','EMP-con-C','EMP-con-D']].sum(axis=1)\n",
"aggregate_data['ENV-str-sum'] = aggregate_data[['ENV-str-A','ENV-str-B','ENV-str-C','ENV-str-D','ENV-str-F','ENV-str-G']].sum(axis=1)\n",
"aggregate_data['ENV-con-sum'] = aggregate_data[['ENV-con-A','ENV-con-B','ENV-con-C','ENV-con-D','ENV-con-E','ENV-con-F']].sum(axis=1)\n",
"aggregate_data.head()"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"def sum_scores_for(dataframe, esg_marker):\n",
" grouping = dataframe.groupby(['Ticker'])[esg_marker].sum()\n",
" return pd.DataFrame({esg_marker : grouping}).reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAL</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>ABC</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum\n",
"0 A 6\n",
"1 AA 19\n",
"2 AAL 1\n",
"3 AAPL 7\n",
"4 ABC 0"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"esg_marker_data = sum_scores_for(aggregate_data, 'EMP-str-sum')\n",
"esg_marker_data.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Ah, these sums look like what we would expect to see!"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"esg_marker_data['EMP-con-sum'] = sum_scores_for(aggregate_data, 'EMP-con-sum')['EMP-con-sum']\n",
"esg_marker_data['ENV-str-sum'] = sum_scores_for(aggregate_data, 'ENV-str-sum')['ENV-str-sum']\n",
"esg_marker_data['ENV-con-sum'] = sum_scores_for(aggregate_data, 'ENV-con-sum')['ENV-con-sum']"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>150</th>\n",
" <td>CAT</td>\n",
" <td>16</td>\n",
" <td>23</td>\n",
" <td>1</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>847</th>\n",
" <td>UNP</td>\n",
" <td>4</td>\n",
" <td>18</td>\n",
" <td>2</td>\n",
" <td>28</td>\n",
" </tr>\n",
" <tr>\n",
" <th>649</th>\n",
" <td>PD</td>\n",
" <td>0</td>\n",
" <td>17</td>\n",
" <td>0</td>\n",
" <td>26</td>\n",
" </tr>\n",
" <tr>\n",
" <th>501</th>\n",
" <td>LPX</td>\n",
" <td>1</td>\n",
" <td>17</td>\n",
" <td>3</td>\n",
" <td>17</td>\n",
" </tr>\n",
" <tr>\n",
" <th>443</th>\n",
" <td>IP</td>\n",
" <td>0</td>\n",
" <td>16</td>\n",
" <td>15</td>\n",
" <td>36</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum\n",
"150 CAT 16 23 1 10\n",
"847 UNP 4 18 2 28\n",
"649 PD 0 17 0 26\n",
"501 LPX 1 17 3 17\n",
"443 IP 0 16 15 36"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"esg_marker_data.sort_values(by=['EMP-con-sum'], ascending=False).head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Second, we pull in stock performance data.\n",
"\n",
"This data contains stock returns by quarter for S&P500 companies dating back to 1979. We'll pull the columns for the '90s for now."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"price_data = pd.read_excel('../stockproject/Cleaned_Researcher_Dataset.xlsx')"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>Company Name</th>\n",
" <th>1989-12-31 00:00:00</th>\n",
" <th>1990-03-31 00:00:00</th>\n",
" <th>1990-06-30 00:00:00</th>\n",
" <th>1990-09-30 00:00:00</th>\n",
" <th>1990-12-31 00:00:00</th>\n",
" <th>1991-03-31 00:00:00</th>\n",
" <th>1991-06-30 00:00:00</th>\n",
" <th>1991-09-30 00:00:00</th>\n",
" <th>...</th>\n",
" <th>2002-09-30 00:00:00</th>\n",
" <th>2002-12-31 00:00:00</th>\n",
" <th>2003-03-31 00:00:00</th>\n",
" <th>2003-06-30 00:00:00</th>\n",
" <th>2003-09-30 00:00:00</th>\n",
" <th>2003-12-31 00:00:00</th>\n",
" <th>2004-03-31 00:00:00</th>\n",
" <th>2004-06-30 00:00:00</th>\n",
" <th>2004-09-30 00:00:00</th>\n",
" <th>2004-12-31 00:00:00</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>A</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>13.06</td>\n",
" <td>17.96</td>\n",
" <td>13.15</td>\n",
" <td>19.55</td>\n",
" <td>22.11</td>\n",
" <td>29.24</td>\n",
" <td>31.63</td>\n",
" <td>29.28</td>\n",
" <td>21.57</td>\n",
" <td>24.1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AA</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>75</td>\n",
" <td>64.75</td>\n",
" <td>63.75</td>\n",
" <td>62.63</td>\n",
" <td>57.63</td>\n",
" <td>65.5</td>\n",
" <td>67.5</td>\n",
" <td>63.88</td>\n",
" <td>...</td>\n",
" <td>19.3</td>\n",
" <td>22.78</td>\n",
" <td>19.38</td>\n",
" <td>25.5</td>\n",
" <td>26.16</td>\n",
" <td>38</td>\n",
" <td>34.69</td>\n",
" <td>33.03</td>\n",
" <td>33.59</td>\n",
" <td>31.42</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAL</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AAL (Alex)</td>\n",
" <td>Alexander &amp; Alexander</td>\n",
" <td>30.63</td>\n",
" <td>26.75</td>\n",
" <td>24.38</td>\n",
" <td>17.88</td>\n",
" <td>23.13</td>\n",
" <td>26.25</td>\n",
" <td>22.5</td>\n",
" <td>20.88</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>AAP</td>\n",
" <td>Advance Auto Parts Inc</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 63 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker Company Name 1989-12-31 00:00:00 \\\n",
"1 A Agilent Technologies Inc. NaN \n",
"2 AA Alcoa Inc 75 \n",
"3 AAL American Airlines Group NaN \n",
"4 AAL (Alex) Alexander & Alexander 30.63 \n",
"5 AAP Advance Auto Parts Inc NaN \n",
"\n",
" 1990-03-31 00:00:00 1990-06-30 00:00:00 1990-09-30 00:00:00 \\\n",
"1 NaN NaN NaN \n",
"2 64.75 63.75 62.63 \n",
"3 NaN NaN NaN \n",
"4 26.75 24.38 17.88 \n",
"5 NaN NaN NaN \n",
"\n",
" 1990-12-31 00:00:00 1991-03-31 00:00:00 1991-06-30 00:00:00 \\\n",
"1 NaN NaN NaN \n",
"2 57.63 65.5 67.5 \n",
"3 NaN NaN NaN \n",
"4 23.13 26.25 22.5 \n",
"5 NaN NaN NaN \n",
"\n",
" 1991-09-30 00:00:00 ... 2002-09-30 00:00:00 \\\n",
"1 NaN ... 13.06 \n",
"2 63.88 ... 19.3 \n",
"3 NaN ... NaN \n",
"4 20.88 ... NaN \n",
"5 NaN ... NaN \n",
"\n",
" 2002-12-31 00:00:00 2003-03-31 00:00:00 2003-06-30 00:00:00 \\\n",
"1 17.96 13.15 19.55 \n",
"2 22.78 19.38 25.5 \n",
"3 NaN NaN NaN \n",
"4 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"\n",
" 2003-09-30 00:00:00 2003-12-31 00:00:00 2004-03-31 00:00:00 \\\n",
"1 22.11 29.24 31.63 \n",
"2 26.16 38 34.69 \n",
"3 NaN NaN NaN \n",
"4 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"\n",
" 2004-06-30 00:00:00 2004-09-30 00:00:00 2004-12-31 00:00:00 \n",
"1 29.28 21.57 24.1 \n",
"2 33.03 33.59 31.42 \n",
"3 NaN NaN NaN \n",
"4 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"\n",
"[5 rows x 63 columns]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"new_header = price_data.iloc[0] #grab the first row for the header\n",
"content = price_data[1:] #take the data less the header row\n",
"content.columns = new_header #set the header row as the df header\n",
"content.head()\n",
"\n",
"tickers = content.iloc[:,0:2]\n",
"tickers.columns = list(new_header)[0:2]\n",
"\n",
"dates = content.iloc[:,45:106]\n",
"dates.columns = list(new_header)[45:106]\n",
"\n",
"result = pd.concat([tickers, dates], axis=1)\n",
"result.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Third, we translate these stock prices into returns.\n",
"\n",
"I want a metric that I can use to compare all the companies that a) belonged to the S&P 500 and b) earned some kind of KLD scores during the 1990-2005 period. Some companies only belong to the S&P500 for a subset of the years in question. We want a metric that will not penalize companies based on having spent less time in the S&P500, so a cumulative score won't work for us. I decided to calculate quarterly returns based on the stock prices. This fairly compares each company's stock performance during the period that an index-matching ETF would have held it, however long or short that was.\n",
"\n",
"This is also nice because our mean function will only consider, for each company, those cells that have a number. So we don't have to do as much data skullduggery to get the equation functions to spit out something meaningful."
]
},
{
"cell_type": "code",
"execution_count": 97,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"//anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:19: SettingWithCopyWarning: \n",
"A value is trying to be set on a copy of a slice from a DataFrame.\n",
"Try using .loc[row_indexer,col_indexer] = value instead\n",
"\n",
"See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"End of dataframe reached\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>quarter_starting_12/31/1990_roi</th>\n",
" <th>quarter_starting_03/31/1991_roi</th>\n",
" <th>quarter_starting_06/30/1991_roi</th>\n",
" <th>quarter_starting_09/30/1991_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_06/30/2002_roi</th>\n",
" <th>quarter_starting_09/30/2002_roi</th>\n",
" <th>quarter_starting_12/31/2002_roi</th>\n",
" <th>quarter_starting_03/31/2003_roi</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>A</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>0.552220</td>\n",
" <td>1.375191</td>\n",
" <td>0.732183</td>\n",
" <td>1.486692</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AA</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>0.863333</td>\n",
" <td>0.984556</td>\n",
" <td>0.982431</td>\n",
" <td>0.920166</td>\n",
" <td>1.136561</td>\n",
" <td>1.030534</td>\n",
" <td>0.94637</td>\n",
" <td>1.007827</td>\n",
" <td>...</td>\n",
" <td>0.582202</td>\n",
" <td>1.180311</td>\n",
" <td>0.850746</td>\n",
" <td>1.315789</td>\n",
" <td>1.025882</td>\n",
" <td>1.452599</td>\n",
" <td>0.912895</td>\n",
" <td>0.952148</td>\n",
" <td>1.016954</td>\n",
" <td>0.935397</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAL</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AAL (Alex)</td>\n",
" <td>Alexander &amp; Alexander</td>\n",
" <td>0.873327</td>\n",
" <td>0.911402</td>\n",
" <td>0.733388</td>\n",
" <td>1.293624</td>\n",
" <td>1.134890</td>\n",
" <td>0.857143</td>\n",
" <td>0.92800</td>\n",
" <td>0.981801</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>AAP</td>\n",
" <td>Advance Auto Parts Inc</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 62 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker Company Name quarter_starting_12/31/1989_roi \\\n",
"1 A Agilent Technologies Inc. NaN \n",
"2 AA Alcoa Inc 0.863333 \n",
"3 AAL American Airlines Group NaN \n",
"4 AAL (Alex) Alexander & Alexander 0.873327 \n",
"5 AAP Advance Auto Parts Inc NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"1 NaN NaN \n",
"2 0.984556 0.982431 \n",
"3 NaN NaN \n",
"4 0.911402 0.733388 \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi quarter_starting_12/31/1990_roi \\\n",
"1 NaN NaN \n",
"2 0.920166 1.136561 \n",
"3 NaN NaN \n",
"4 1.293624 1.134890 \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_03/31/1991_roi quarter_starting_06/30/1991_roi \\\n",
"1 NaN NaN \n",
"2 1.030534 0.94637 \n",
"3 NaN NaN \n",
"4 0.857143 0.92800 \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_09/30/1991_roi ... \\\n",
"1 NaN ... \n",
"2 1.007827 ... \n",
"3 NaN ... \n",
"4 0.981801 ... \n",
"5 NaN ... \n",
"\n",
" quarter_starting_06/30/2002_roi quarter_starting_09/30/2002_roi \\\n",
"1 0.552220 1.375191 \n",
"2 0.582202 1.180311 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_12/31/2002_roi quarter_starting_03/31/2003_roi \\\n",
"1 0.732183 1.486692 \n",
"2 0.850746 1.315789 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"1 1.130946 1.322479 \n",
"2 1.025882 1.452599 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"1 1.081737 0.925703 \n",
"2 0.912895 0.952148 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi \n",
"1 0.736680 1.117293 \n",
"2 1.016954 0.935397 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"\n",
"[5 rows x 62 columns]"
]
},
"execution_count": 97,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def quarter_return(start, end):\n",
" if start == 0 or end == 0:\n",
" return 0\n",
" return end / start\n",
"\n",
"#WARNING: This has to go column by column because the sequence in time matters.\n",
"#Such an iterative operation takes longer than async-per-column pandas operations.\n",
"#Expect this block of code to take several seconds to run.\n",
"raw_stock_prices = result\n",
"returns_df = raw_stock_prices[['Ticker', 'Company Name']]\n",
"for column_name in raw_stock_prices.iloc[:,2:]:\n",
" loc = raw_stock_prices.columns.get_loc(column_name)\n",
" this_column = raw_stock_prices.iloc[:,loc]\n",
" next_col = loc + 1\n",
" try:\n",
" next_column = raw_stock_prices.iloc[:, next_col]\n",
"\n",
" temp_df = pd.concat([this_column, next_column], axis=1)\n",
" temp_df.columns = ['a', 'b']\n",
" \n",
" returns_df['quarter_starting_' + column_name.strftime('%m/%d/%Y') + '_roi'] = (\n",
" temp_df.apply(lambda row: quarter_return(row['a'], row['b']), axis=1))\n",
" except:\n",
" print('End of dataframe reached')\n",
"\n",
"returns_df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Fourth, we combine the data into one dataframe.\n",
"\n",
"We find all the companies for which we have both stock price data and ESG data, and we put the information together."
]
},
{
"cell_type": "code",
"execution_count": 98,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"(751, 62)"
]
},
"execution_count": 98,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"tickers = list(esg_marker_data[\"Ticker\"]) #get all the company tickers for which we have esg data\n",
"prices_for_esg_companies = returns_df[returns_df[\"Ticker\"].isin(tickers)] #get the stock data from companies in that list\n",
"prices_for_esg_companies.shape"
]
},
{
"cell_type": "code",
"execution_count": 117,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"relevant_esgs = esg_marker_data[esg_marker_data[\"Ticker\"].isin(list(prices_for_esg_companies[\"Ticker\"]))]\n",
"relevant_esgs = relevant_esgs.fillna(0.0) #no esg score to zero esg score"
]
},
{
"cell_type": "code",
"execution_count": 118,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"all_data = pd.concat([relevant_esgs, prices_for_esg_companies.iloc[:,1:]], axis = 1) #put the esg and stock data in one dataframe"
]
},
{
"cell_type": "code",
"execution_count": 122,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_06/30/2002_roi</th>\n",
" <th>quarter_starting_09/30/2002_roi</th>\n",
" <th>quarter_starting_12/31/2002_roi</th>\n",
" <th>quarter_starting_03/31/2003_roi</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6.0</td>\n",
" <td>4.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19.0</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>37.0</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>0.552220</td>\n",
" <td>1.375191</td>\n",
" <td>0.732183</td>\n",
" <td>1.486692</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAL</td>\n",
" <td>1.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>0.863333</td>\n",
" <td>0.984556</td>\n",
" <td>0.982431</td>\n",
" <td>0.920166</td>\n",
" <td>...</td>\n",
" <td>0.582202</td>\n",
" <td>1.180311</td>\n",
" <td>0.850746</td>\n",
" <td>1.315789</td>\n",
" <td>1.025882</td>\n",
" <td>1.452599</td>\n",
" <td>0.912895</td>\n",
" <td>0.952148</td>\n",
" <td>1.016954</td>\n",
" <td>0.935397</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>ABC</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 66 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"0 A 6.0 4.0 0.0 0.0 \n",
"1 AA 19.0 0.0 15.0 37.0 \n",
"2 AAL 1.0 0.0 0.0 0.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"4 ABC 0.0 0.0 0.0 0.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"0 NaN NaN \n",
"1 Agilent Technologies Inc. NaN \n",
"2 Alcoa Inc 0.863333 \n",
"3 American Airlines Group NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 0.984556 0.982431 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"0 NaN ... \n",
"1 NaN ... \n",
"2 0.920166 ... \n",
"3 NaN ... \n",
"4 NaN ... \n",
"\n",
" quarter_starting_06/30/2002_roi quarter_starting_09/30/2002_roi \\\n",
"0 NaN NaN \n",
"1 0.552220 1.375191 \n",
"2 0.582202 1.180311 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_12/31/2002_roi quarter_starting_03/31/2003_roi \\\n",
"0 NaN NaN \n",
"1 0.732183 1.486692 \n",
"2 0.850746 1.315789 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"0 NaN NaN \n",
"1 1.130946 1.322479 \n",
"2 1.025882 1.452599 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"0 NaN NaN \n",
"1 1.081737 0.925703 \n",
"2 0.912895 0.952148 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi \n",
"0 NaN NaN \n",
"1 0.736680 1.117293 \n",
"2 1.016954 0.935397 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
"[5 rows x 66 columns]"
]
},
"execution_count": 122,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"all_data.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"OK, time to look at the data! "
]
},
{
"cell_type": "code",
"execution_count": 123,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import matplotlib.pyplot as plt\n",
"import random\n",
"import seaborn as sns\n",
"%matplotlib inline "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here's where we make our target column: average roi. We calculated the return on investment of every stock, every quarter. Now for each stock we want to know, for every quarter when it had a return, what was the average of those returns?"
]
},
{
"cell_type": "code",
"execution_count": 139,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_12/31/2002_roi</th>\n",
" <th>quarter_starting_03/31/2003_roi</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" <th>avg_roi</th>\n",
" <th>avg_quarterly_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6.0</td>\n",
" <td>4.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19.0</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>37.0</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>0.732183</td>\n",
" <td>1.486692</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" <td>0.990271</td>\n",
" <td>0.990271</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAL</td>\n",
" <td>1.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>0.863333</td>\n",
" <td>0.984556</td>\n",
" <td>0.982431</td>\n",
" <td>0.920166</td>\n",
" <td>...</td>\n",
" <td>0.850746</td>\n",
" <td>1.315789</td>\n",
" <td>1.025882</td>\n",
" <td>1.452599</td>\n",
" <td>0.912895</td>\n",
" <td>0.952148</td>\n",
" <td>1.016954</td>\n",
" <td>0.935397</td>\n",
" <td>1.007726</td>\n",
" <td>1.007726</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>ABC</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>ABI</td>\n",
" <td>11.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>ABK</td>\n",
" <td>6.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>Apple Inc.</td>\n",
" <td>1.141844</td>\n",
" <td>1.111801</td>\n",
" <td>0.648045</td>\n",
" <td>1.482759</td>\n",
" <td>...</td>\n",
" <td>0.986741</td>\n",
" <td>1.347949</td>\n",
" <td>1.087093</td>\n",
" <td>1.031371</td>\n",
" <td>1.265325</td>\n",
" <td>1.203402</td>\n",
" <td>1.190842</td>\n",
" <td>1.661935</td>\n",
" <td>1.060814</td>\n",
" <td>1.060814</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>ABS</td>\n",
" <td>9.0</td>\n",
" <td>8.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>ABT</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>19.0</td>\n",
" <td>AmerisourceBergen Corp.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>0.966673</td>\n",
" <td>1.320952</td>\n",
" <td>0.779380</td>\n",
" <td>1.038853</td>\n",
" <td>0.973820</td>\n",
" <td>1.093270</td>\n",
" <td>0.898461</td>\n",
" <td>1.092534</td>\n",
" <td>0.995946</td>\n",
" <td>0.995946</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>ABX</td>\n",
" <td>4.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>3.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10 rows × 68 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"0 A 6.0 4.0 0.0 0.0 \n",
"1 AA 19.0 0.0 15.0 37.0 \n",
"2 AAL 1.0 0.0 0.0 0.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"4 ABC 0.0 0.0 0.0 0.0 \n",
"5 ABI 11.0 0.0 0.0 0.0 \n",
"6 ABK 6.0 0.0 0.0 0.0 \n",
"7 ABS 9.0 8.0 0.0 0.0 \n",
"8 ABT 0.0 0.0 0.0 19.0 \n",
"9 ABX 4.0 3.0 0.0 3.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"0 NaN NaN \n",
"1 Agilent Technologies Inc. NaN \n",
"2 Alcoa Inc 0.863333 \n",
"3 American Airlines Group NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"6 Apple Inc. 1.141844 \n",
"7 NaN NaN \n",
"8 AmerisourceBergen Corp. NaN \n",
"9 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 0.984556 0.982431 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"6 1.111801 0.648045 \n",
"7 NaN NaN \n",
"8 NaN NaN \n",
"9 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"0 NaN ... \n",
"1 NaN ... \n",
"2 0.920166 ... \n",
"3 NaN ... \n",
"4 NaN ... \n",
"5 NaN ... \n",
"6 1.482759 ... \n",
"7 NaN ... \n",
"8 NaN ... \n",
"9 NaN ... \n",
"\n",
" quarter_starting_12/31/2002_roi quarter_starting_03/31/2003_roi \\\n",
"0 NaN NaN \n",
"1 0.732183 1.486692 \n",
"2 0.850746 1.315789 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"6 0.986741 1.347949 \n",
"7 NaN NaN \n",
"8 0.966673 1.320952 \n",
"9 NaN NaN \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"0 NaN NaN \n",
"1 1.130946 1.322479 \n",
"2 1.025882 1.452599 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"6 1.087093 1.031371 \n",
"7 NaN NaN \n",
"8 0.779380 1.038853 \n",
"9 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"0 NaN NaN \n",
"1 1.081737 0.925703 \n",
"2 0.912895 0.952148 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"5 NaN NaN \n",
"6 1.265325 1.203402 \n",
"7 NaN NaN \n",
"8 0.973820 1.093270 \n",
"9 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi avg_roi \\\n",
"0 NaN NaN NaN \n",
"1 0.736680 1.117293 0.990271 \n",
"2 1.016954 0.935397 1.007726 \n",
"3 NaN NaN NaN \n",
"4 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"6 1.190842 1.661935 1.060814 \n",
"7 NaN NaN NaN \n",
"8 0.898461 1.092534 0.995946 \n",
"9 NaN NaN NaN \n",
"\n",
" avg_quarterly_roi \n",
"0 NaN \n",
"1 0.990271 \n",
"2 1.007726 \n",
"3 NaN \n",
"4 NaN \n",
"5 NaN \n",
"6 1.060814 \n",
"7 NaN \n",
"8 0.995946 \n",
"9 NaN \n",
"\n",
"[10 rows x 68 columns]"
]
},
"execution_count": 139,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Calculate average roi\n",
"returns = all_data[all_data.columns.difference(['EMP-str-sum', 'EMP-con-sum','ENV-str-sum','ENV-con-sum','Ticker','Company Name'])]\n",
"all_data['avg_quarterly_roi'] = returns.mean(axis=1, skipna=True)\n",
"all_data.head(10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Time to make some pictures! Let's plot our ESG score sums against average roi and see if we notice any trends."
]
},
{
"cell_type": "code",
"execution_count": 140,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"<matplotlib.collections.PathCollection at 0x120ec04a8>"
]
},
"execution_count": 140,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXIAAAEUCAYAAAA2ib1OAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3XucXHV9//HX7Ozs7G12s5vdQEKAaC5f8ALkwk2ExBi8\n9CHVn2hpI1JvVWtt7Y96+YFAqw9rS6XgDbXQRislFRVpgYJYCChEUYEECJdvSEISQxKzl8ned2Z2\ndn5/zJnN7O6Zy+7OzJ6zeT8fDx5k5sye+cycz/mc7/l+v+dMIJVKISIi/lU12wGIiMjMqJCLiPic\nCrmIiM+pkIuI+JwKuYiIz6mQi4j4XPVsByBgjEkBO4DkhEXvstbuNcacB/wDMJ/0wfd3wKettc9l\nreNDwMeACBAG9gDXWGt/neM9PwD8JekcqAZ+BfyNtbanhB8tL2PMOuB+wAIpIACMAF+w1t5jjPk7\noM1a+8kC6/kZsNFa22mM+QhQY6391hRj2QF80lr7SI7lfwl8HTjfWvv4VNbtsq6PA/Ostf84zb8f\n+7wTnl/Hse8zIwI8D3zAWttVYL3XAU9ba/97OnHJ7FEh9443TdwxAYwxYeBe4C3W2qec5y4H7jfG\nvMpamzTGfBm4CPgja+0+5zXrgXuNMauttfsnrPNs4DpgjbW22xgTBG4Gvg1sLONndLPbWntWVmxn\nAluNMa+awjouzvr3G0kfFEvt48DtwF8DfzyTFVlrvzPDWC7Os2zi9xkE7gQ+DVxVYL3rSRd98RkV\ncu+rB+YBjVnP3Q70AkFjTBvp4rLUWnso8wJr7RZjzJVAg8s6F5Ju2dcD3c7B4DrgtQATW8LZj40x\njwBPAucBC4BbgBOBtc57/ZG19lljzLuBa4BR0mcan7HW/qLQh7XWPm2MGQROzX7eGPNa4Jukz0pS\nwD9ba79vjPmu85KHjTH/BPwhcLExZshae7Mx5vPApc7n3Qt8wlp70BjzGmCT8x28mON7yrz3OqAV\n+Cyw2xhzsrX2d86yZc56WoFDpM8q/sNa+z1jzNXAO4E6Z/2fttbeNeH73At8D3gzcArwfWvttcaY\nRuC7wHLnO3yS9BnXv2V93j/IxJFHE9AObHXibQa+BrweCAEPAZ9x1r0G+IoxJunEvcNae4Pzd9/L\nPHZi/jVwBnA1cNNUPoO1drRAzDJF6iP3joeNMduz/rsLwFobJV1AfmqM2WOMuQ34IPCgtTYOnA+8\nkF3EM6y1t1lrX3B5r/tJ79h7jTFPGWO+CZwNPFJkrEustRcAlwP/BDxirV0D/JR0dw3AV0gXzTXA\ntcC6YlbsHABGyWoZGmOqgbuBb1hrzwDeDnzZGHO+tfaDzsveZK29zXndTU4Rv4J0wTrHaaXeB/yr\n8/rbgVud9X2NCQeOCT4B3G6tPQhsAbK7em4D/tNa+zrgr0hvD4wxpwIbgHXOe3we+GKO9Tdaay8E\n3gB82jkb+T9AxIn7bOd1r57wed2K+FInf54zxhwhXajvBr7qLL8JeNJauxpYCbQBV1prbwaeIH3A\nvSvPd5Gxw1p7etZri/4MRaxbpkgtcu9w7VoBsNbeaIy5lXSr9yLgc8DnjDHnkG4Bjt1nwRgTAR51\nHjYCP7TWXj1hfQngfcaYzwBvctb776R3+suKiPUnzv93O///adbjdc6/fwDcZYz5H+B/SRd8N0uN\nMdudf4dI9/+/01o7aIzJvGYFUGut/YkT/0FjzJ3A20j37efyDuAc4AlnXUGg3hgzn3Rr8vvO+rY6\nfeSTGGNOBN4FrHae+nfgO8aYLwI1zvovctbzgjHmIeff+5wDyfucVvt5jD+ryvbfzt+84hTfVuAx\n0gerR0h/f1+11u7K81kzxrpWjDEfBL4M/MjZ5mPfiTHmw87juiLW6ebRCY9L+RlkitQi9zhjzAXG\nmM9Ya/ustfdaaz9LugskRbqv9NfAaU5xwnndWc7O/B+kT60nrvNDxpg/tNYetNbebq39KLAKeK/T\nVZMZeMyombCKWPaDrCKR/dznSfdXPwF8AMjVrbI7E6+19rXW2re5DNAGyTpYOapIF/58gsD1Wd/H\nGuCCrOXZn3Ekxzr+zHnve5wuhRtIf6d/mvU32etJAhhjVpE+yDQBPwOun/C6bENZ/04BAWvty8Ay\n0oPcTcCDxphLcn1QN9ba75Jujf/IOauB9Hfy3qzv5FzGn2GMiyPr8cQc6K/EZ5DiqJB7XwdwjTHm\njVnPLQSagWed0/2vkd5ZT8m8wDm1v4DJM2Eg3XVxvTFmcdZzrwX2AVHnPVcbYwJOC/8dUwnYGFPt\nFL16Z2DvE8AZzsDtdLwIJJxuF4wxi0j3e/+vszzJsaI+kvXvB4CPGGMyB7MvArc5szeeBD7irG8V\n6S6YiZ8jSLqQf9xau8T57xTSrdxPkS5mW0l3deF0J7yZdCG7CHjCWnsj8HPSrfpgsR/YGPPnpPuX\nf2at/ZzzWVa5fN5CPgecDPyF8/gB4P862zZMutBnCnn2d9dB+sCX+b7XFht7kZ9BSkhdK97xsDPI\nlO1qa+19xph3kT5FXQwMAz3AB621FtKtX2PM+4DNxpgG0kU+Srp74+aJb+QMxNUD9zk7cwrYCbzV\nGfi8nXQ/9EvAK6QLUa7W5CTW2hFjzF878SRIHzg+ZK2NFfjTXOtLON/B152Bwmrgi9bah52X/Aj4\nuVPo7wdudLpSrgdOAh436Sme+0mfHQD8CfBdp9jsAtzGEt5BurFz+4TnbyJdyP8AuAL4N2PMJ0h/\nVy8Dg6S7my41xrzgrONeoNU5MBbj+6S7qZ53Bn/3k57+OO7zWmvzztCx1h41xnwOuMkY8wPS/fhf\nA54lXbQf5Fi3193APxhjaoBvALcbYyzpQeItRcZd7GeQEgroNrYi0+fMirnTWvuiMyPkGeDt1lpN\n45OKUYtcZGZ2AncYY0ZJ70//qCIulaYWuYiIz2mwU0TE51TIRUR8ToVcRMTnVMhFRHxOhVxExOdU\nyEVEfE6FXETE51TIRUR8ToVcRMTnVMhFRHxOhVxExOdUyEVEfE6FXETE51TIRUR8ToVcRMTnVMhF\nRHxOhVxExOcq/lNvHR19Rf0kUUtLPdHoYLnDKSu/fwY/xt/eHin6R6JLrVBu++X79Euc4J9YSxFn\nvtz2bIu8ujo42yHMmN8/g9/j9xq/fJ9+iRP8E2u54/RsIRcRkeKokIuI+JwnC3kskeRQ5wCxRHK2\nQxEpCeW0lFPFBzvzSY6OcseWXWzb2UF3X4zWSJiVK9q5bP0yglWePOaI5KWclkrwVCG/Y8suHnzi\nwNjjrt7Y2OONG1bMVlgi06aclkrwTJMglkiybWeH67JtOzt1Siq+o5yWSsnbIjfGhIBNwBIgDHzJ\nWnt31vJLgOuAEWCTtfbW6QbS0x+juzfmuizaN0xPf4wFLfXTXb1IxSmnpVIKtcgvB7qstRcCbwe+\nmVngFPmbgLcAa4GPGmNOnG4gzY1hWpvCrstaIrU0N7ovE/Eq5bRUSqE+8h8BP856PJL179OBXdba\nKIAx5jHgQudvcmppqc85Of6CM0/i7kf3uDy/iMWL5hUI1Zva2yOzHcKM+D3+SnLLbb/mtJ+2u19i\nLWeceQu5tbYfwBgTIV3Qr8la3AT0ZD3uA5oLvWG+y1QvOf8UBofibNvZSbRvmJZILStXtHHJ+afQ\n0dFXaNWe094e8WXcGX6MfzZ3arfc9mNO+2m7+yXWUsSZL7cLzloxxpwM3AV8y1q7OWtRL5C95ghw\ndJoxAhCsqmLjhhVcunYpwZoQyXiCcMgfl+CKuFFOSyUUGuw8AfgZ8Elr7UMTFr8ALDfGtAL9wEXA\nDaUIKhwK0t7W4IsjrUgxlNNSToVa5FcDLcC1xphrneduBRqstbcYY64EHiA9aLrJWvtK+UIVERE3\nhfrIPwV8Ks/ye4B7Sh2UiIgUzzMXBImIyPSokIuI+JwKuYiIz6mQi4j4nAq5iIjPqZCLiPicCrmI\niM+pkIuI+JwKuYiIz6mQi4j4nAq5iIjPqZCLiPicCrmIiM+pkIuI+JwKuYiIz6mQi4j4nAq5iIjP\nqZCLiPicCrmIiM+pkIuI+JwKuYiIz6mQi4j4nAq5iIjPVRfzImPMucD11tp1E56/Evgw0OE89TFr\nrS1phCIiklfBQm6M+SzwfmDAZfEq4Apr7ZOlDkxERIpTTNfKbuDdOZatBq4yxjxmjLmqdGGJiEix\nAqlUquCLjDFLgB9Ya8+b8PzfAjcDvcBdwLettffmW9fISDJVXR2cdsAiBQRm642V21JmOXO7qD5y\nN8aYAPBVa22P8/h/gJVA3kIejQ4Wtf729ggdHX3TDc8T/P4Z/Bh/e3tk1t67UG775fv0S5zgn1hL\nEWe+3J52IQeagB3GmNNJ95+vBzbNYH0iIjINUy7kxpiNQKO19hZjzNXAw0AMeMhae1+pAxQRkfyK\nKuTW2r3Aec6/N2c9fxtwW1kiExGRouiCIBERn1MhFxHxueOikMcSSY5EB4klkrMdihynYokkhzoH\nlINSFjOZteJ5ydFR7tiyi207O+jujdHaFGblinYuW7+MYNVxcQyTWTYuB/titEaUg1J6c7qQ37Fl\nFw8+cWDscVdvbOzxxg0rZissOY4oB6US5myTIJZIsm1nh+uybTs7dYorZacclErxZCEvRX9iT3+M\n7t6Y67Jo3zA9/e7LREpFOSiV4qmulVL2JzY3hmltCtPlsiO1RGppbgyXKmwRV82NYVoiNXT3xSct\nm9cYVg5KyXiqRZ7pT+zqjZFKHetPvGPLrimvKxwKsnJFu+uylSvaCId0cyMpr3AoSENdjeuyhrqQ\nclBKxjOFvBz9iZetX8aGNYuZ31RLVQDmN9WyYc1iLlu/bKbhihQUSyQZHE64LhscThSd05o+K4V4\npmulmP7EBS31U1pnsKqKjRtWcOnapfT0x2huDKsVJBWTP6djBXNa02elWJ7JhubGMOEa9yJbEwoW\n3Z/o1noJh4IsaKlXEZeKyozTuClmnGZcVyMz62qUuc0zLfK0wj9ykYtaL+I14VCQs5a38dCTr0xa\ndtby+XkbFoW6Gi9du1QNExnjmQrX0x9jOD7qumw4niw4VUutF/GieNK9XzvX8xmauihT4ZlCXug0\nsy5cnXPARxdeiBfFEkke3X7Yddmj2w/nzcuZdsvI8cUzXSv9g5Pn2mb72397nKMDI7RGalhlFozr\nMinFQGkskZz2gOhM/lbmrr2HegouN6e0ui7LTJ/Nvrw/Q9NnZSLPFPLnXu7Ku/zowAgA3X1xHnzi\nAKOpFJdfbICZXfwzk771cvTLux0UdKDwpwMdA3mXv7AvypKFzTm3aWaa7LadnUT7hmmJ1LJyRZum\nz8okninkJ8xvmNLrtz5ziPeuW0Y4FJxy6yW7MN75893TvqlRKW+I5HZQOHN5GwFg+0udGsD1oRML\nnAXevXUfW589zMoV7bzrwlfTPxgfd7DW9FkplmcK+ZITm6b0+lhilFc6+misq6G5MTzWSnnKdhDt\ni9ESCbPKtI9rvbgVy/4h9y6dp2yH68yAzEGgLlydt1/+kjcsYaRzgGQiSTgULNiqdjsobJkw26GS\nd87L3O8mE79MXSpQ+DWZbfro0weJJUZpiYRZbcYfrDPTZ0Vy8Uwhn45v/GQHvf1xWpvCnLW8jRQQ\ncHaegMtO5FYsc+mecMFG5iDwlD1Cd1+c5oYQPQPuV+119Q7zt5t+Q09/nJamMA21IQaG4kT74uNa\n1SPJVMGDgpvMgWIoNlLyVprun106+w/n7yPPFkukZ2xF+9KFfXR0lPe+aflYfpRjW8vc4ZlCvu9Q\n75T/pqc/3Zru6o1Nmqub3Xq94HUn8Kvnfs9vnjtY9LqrAumZMhn/+dBL41rIuYp4xlEntu7e8QOx\nmbjs/qMMDifo7o3R3Fgz9vpiZB8oSt3dovtnl85gbPqzpbY8dTDdpdYXpyoAoylcB/pFwEOF/MV9\n3WVZ74NPHHDtOy9kNAWHOgd4tqeLVy2M8MtnD5U0rt8d6R/791SK+MS/yRTaVCrF+5zB3+kqNI1z\n4llAqWf6zLVB3e6+mc31ztw1cTR17PGDTxxgOD7C+a85kcULGonUu9+US44vninkB7vzj/DPhn/c\nvG22Qyja1mcP8R5n8DdjqoUx3zTOcd1FkRoa6mrGzijcuotyvafbOEWmW2ybPUK0P0FLY4jVp53g\n+5bn4a7y5PRjzxzmsWfS89MXttZz1ftX0lineeXHM88Ucrs3//TD40VzYw29Tt96XTjIKx2DRf3d\ncHyUjqNDLG5vnPK0yOwB3FB1gPiI+60SxrqL+uLj7rHt1l2U6z3dum4mdotF+xM8+MQBRkZHueIt\np7nG6odW+97D5W+cHOoe5K+/vpX1qxe7bt+J35efvj8pXlGF3BhzLnC9tXbdhOcvAa4DRoBN1tpb\npxtIz9D077Myl2T6/aO9Maba2fT7rkHa59UVPaVyYsFvidTkLOLFyO4uyn7PzPS5qQ7qPrr9IJe9\naTnhUFD30sljNMWk7eu2bXOdRRX7/ekg4F0FC7kx5rPA+4GBCc+HgJuAs51lW40x91hr3a9JlimZ\nTjm9+b92ML8pzECOe2BPvNnSxNax2y/ZzNRjzxwaKyZTHdRNjsIrHX28etE8DcIWIXvKrNu2dTuL\ngsLfnw6i3lfMVtgNvNvl+dOBXdbaqLU2DjwGXFjK4GTqunpz33ws+2ZL+QY2S2k4nhy7kdl0BnX7\nh0Z0L50iZabMTmXbFvP96YZ03lewRW6tvdMYs8RlUROQPVG2D2gutL6Wlnqqq3VaNhva5tWxdMl8\namuqOdQ5MONZFZWw5nWLGBweyRlrtG+YYE2I9rapXRlcDrOd21VVsHjRvLzf10TdvcN0DSQwpzZS\nW3OsHLS3RwAYjo/wzG738atndnfxsUvrxv3dbMjE6nXljHMmW6AXyI4sAhwt9EfRqPvgXX0IBvNP\nzZYZOmPpfPp6hugDkokkrRH3+9O4aayrJhyqJto3zLzGMA11IQaHE85VtLXU11aP6yMvpKUxTM9A\n+m9jiRH6h0YmvWbxggbiQ/G8sbZEaknGE3R09AGzu1Pnyu1KGR2FAwePpu89VOS2DQTg2u/8clx3\nyYknNI99n0eig3REh1z/tvPoELv3ds3qVaft7ZGxWL2sFHHmy+2ZFPIXgOXGmFagH7gIuGG6KzOn\ntrBtV3QG4fhbgOL7xdNFNUh3b4xA4Ng842y1NUHqw9Uc7Y+53mwp3/1pGuuqGRweYTSVvjDqpPZG\nPn/FKlKpQM4ZENXBgNOP2jlW7AdjIwzHJ5+2z2+q5boPrBmbkx4IpPj77z/FKx39k96zUKxevhPg\nq05s4OUKzFzJmN8UHts2ub6viTK5k91n/qk/WT22fCY3pJPKmXIhN8ZsBBqttbcYY64EHiDd177J\nWjv5p1CKdOL8BphCIa+vDfKZPz6L377Qwdmnt/OVzdsYzNE3XE5vWrWI819zAlufPcw5py9g030v\nFtUSmt9Uy5V/dAYvH+rDnDKPSENoUjFb2FYPBDjUOZCzqD7wm/08vG3yFatvPGNhwZst5bu73uDw\nCH3xUSI1VeMuOslufU28B8jEGzxNnD2TsXJFG5H6mnHr/cKHzqFvMM6BI/2uF7r48U6Ap54YqWgh\nX7mifWw7T/y+ss+i8jUAtu3sZDh+7OzIrwfR400glarstL+Ojj7XN7zvV3v58c/3FLWOxrpqrv/z\n86mrCY09NxRP8Llv/8r1FL1UFi9oYHAoQXdf3PVy6SPRQa76l8eLallvWLPYdbaAWzHLV+COzSiY\nXOAKXZyTkWta2UxPB/PFVsrb/GZrb48Ucauq8piY2z98+CV++uvfTWtdrZEaXre0lZ37ezkSHRw7\nkJ/QUs9n3reS+361r6jv1W0e+Z5XerjhB9td87QqAN/5fxuoTh1rFJVjO5bKcda1kjO3PVPI7f5u\nrt+8Peffffydr2VkZBRzyjzmN9flfF1XzxB2/1FetTDCjT98uqjWcW1NkIba6rH+3jOXzz92+9gJ\nN47KVxxjiSTX3Pq463tWBSCVgtam8uwE7t0cM5suVqqdpJLzj71UyHfs6eTGHz5T1N8GAnDmq+fz\nxxuWA4z7rnIdyKf7vebL0/lNtXznqjfT1zO5X9yL88hVyNM8c2XnkoX5J7ycuay407j5zXW84fXp\nQl9sP2Guboj3rFs26blgFTkHd/Kdhq5deRJvPfvksu0E2d0cmx/c6ak518frbViXn9xS9GtTKdi+\nu4u2lrpJ2yhSX8PpSyb/ktB0v9dC3SW1NdW4lZzjdTv6gWdm84dDQS5audB12UUrF06r+F22fhkb\n1ixmflMtVYH0YNDJCxppjYSdx7VsWJO+tDmTpNnv4/bc1N+z1ulGWT7ldU2H5lx7RzgU5MKzTpzS\n31RqG+XKUy+POUhunmmRA7z/YkNNMMgTL/yeowMJ5jWEWHP6CdNOrly/sFLOU8Ts9wzWhEjGExU9\nDS3F75dK6VzxltMIV1fz2+cP0zM4QnN9Na99dRu/3OF+AXSltpF+fWhu8VQhz1cEZ1J8J54SVuIU\nMRwK0t7WUPH+O00X8xa3nAaw+6NT3kblaICou2Ru8FQhz8gugrrPw9Roupg3TTywT2UbaR+QQjxZ\nyLPpZklT58c518ebqWwj7QNSiKcLeaGBO7cfRxb1f/pBsdtI+4AUw9PnZcUM3HlV5lfoZ3OWyHRm\n3Uh55MqHQtvIz/uAVI6nW+R+HLjTr9BLtpnmgx/3Aak8T1eWzMCdG68O3I27d3NK924+3s00H/y4\nD0jlebqQg78uXNDFOJKtVPngp31AZoenu1bAXwN3uhhHspUqH/y0D8js8HyLPMMPA3eZ/kw36s88\n/pQ6H/ywD8js8E0h9wP1Z0o25YNUiue7VvxGF+NINuWDVIIKeYnN9k2zxFuUD1IJ6lopk3AoyMK2\nBu20AigfpLxUyEVEfE6FXETE5yr+m50iIlJaapGLiPicCrmIiM+pkIuI+JwKuYiIz6mQi4j4nAq5\niIjPqZCLiPicCrmIiM+pkIuI+JwKuYiIz6mQi4j4nAq5iIjPqZCLiPicCrmIiM+pkIuI+JwKuYiI\nz6mQi4j4nAq5iIjPqZCLiPhcdaXfsKOjr6gfCW1pqScaHSx3OGXl98/gx/jb2yOB2XrvQrntl+/T\nL3GCf2ItRZz5ctuzLfLq6uBshzBjfv8Mfo/fa/zyffolTvBPrOWO07OFXEREiqNCLiLic54s5LFE\nkkOdA8QSydkORaQklNNSThUf7MwnOTrKHVt2sW1nB919MVojYVauaOey9csIVnnymCOSl3JaKsFT\nhfyOLbt48IkDY4+7emNjjzduWDFbYYlMm3JaKiFvITfGhIBNwBIgDHzJWnt31vJLgOuAEWCTtfbW\n6QYSSyTZtrPDddm2nZ1cunYp4ZA/RqhFQDktlVPo3O5yoMtaeyHwduCbmQVOkb8JeAuwFvioMebE\n6QbS0x+juzfmuizaN0xPv/syEa9STkulFCrkPwKuzXo8kvXv04Fd1tqotTYOPAZcON1AmhvDtDaF\nXZe1RGppbnRfJuJVymmplLxdK9bafgBjTAT4MXBN1uImoCfrcR/QXOgNW1rqc06Ov+DMk7j70T0u\nzy9i8aJ5hVbtSe3tkdkOYUb8Hn8lueW2X3PaT9vdL7GWM86Cg53GmJOBu4BvWWs3Zy3qBbIjiwBH\nC60v32Wql5x/CoNDcbbt7CTaN0xLpJaVK9q45PxT6OjoK7Rqz2lvj/gy7gw/xj+bO7Vbbvsxp/20\n3f0SaynizJfbhQY7TwB+BnzSWvvQhMUvAMuNMa1AP3ARcMNMAg1WVbFxwwouXbuUYE2IZDyhwSDx\nNeW0VEKhFvnVQAtwrTEm01d+K9Bgrb3FGHMl8ADpvvZN1tpXShFUOBSkva3BF0dakWIop6WcCvWR\nfwr4VJ7l9wD3lDooEREpni4tExHxORVyERGfUyEXEfE5FXIREZ9TIRcR8TkVchERn1MhFxHxORVy\nERGfUyEXEfE5FXIREZ9TIRcR8TkVchERn1MhFxHxORVyERGfUyEXEfE5FXIREZ9TIRcR8TkVchER\nn1MhFxHxORVyERGfUyEXEfE5FXIREZ9TIRcR8TkVchERn6su5kXGmHOB66216yY8fyXwYaDDeepj\n1lpb0ghFRCSvgoXcGPNZ4P3AgMviVcAV1tonSx2YiIgUp5iuld3Au3MsWw1cZYx5zBhzVenCEhGR\nYgVSqVTBFxljlgA/sNaeN+H5vwVuBnqBu4BvW2vvzbeukZFkqro6OO2ARQoIzNYbK7elzHLmdlF9\n5G6MMQHgq9baHufx/wArgbyFPBodLGr97e0ROjr6phueJ/j9M/gx/vb2yKy9d6Hc9sv36Zc4wT+x\nliLOfLk97UIONAE7jDGnk+4/Xw9smsH6RERkGqZcyI0xG4FGa+0txpirgYeBGPCQtfa+UgcoIiL5\nFVXIrbV7gfOcf2/Oev424LayRCYiIkXRBUEiIj7n60IeSyQ5Eh0klkjOdigiecUSSQ51DihXpSxm\nMtg5a5Kjo9yxZRfbdnbQ3RujtSnMyhXtXLZ+GcEqXx+bZI4Zl6t9MVojylUpPV8W8ju27OLBJw6M\nPe7qjY093rhhxWyFJTKJclUqwXdNglgiybadHa7Ltu3s1KmreIZyVSrFd4W8pz9Gd2/MdVm0b5ie\nfvdlIpWmXJVK8WQhzzcw1NwYprUp7Pp3LZFamhvdl4lUmnJVKsVTfeTFDAyFQ0FWrmgf1++YsXJF\nG+GQ7nUh3hAOBTlzeRtbnnxl0rIzl89XrkrJeKqQFzswdNn6ZUC6nzHaN0xLpJaVK9rGnhfxilx3\nOZq1O3vJnOSZQl5oYOjStUvHWjDBqio2bljBpWuX0tMfo7kxrNaNeE4skWT7S52uy7a/1MV71iWV\nt1ISnukjn87AUDgUZEFLvXYG8SQNdkqleKaQNzeGCde4F+SaUFADQ+I7pcppXcEshXimawUgmXRP\n1FzPi3jd6Kh77o6Ojhb8W13BLMXyTDb09MfI1eBIJNFpqPhOT3+M+Ij7svhIqmBOZwb/u3pjpDg2\n+H/Hll2V3UlAAAAOQklEQVSlD1Z8zTOFPJnM30IptFzEa4JV+eem5Fuuq0JlKjxTyJ/fF53R8rlM\nfaT+tPdw/p/2yrc830Bpd+8we17pyZkPypfjj2f6yBtqQzNaPlfEEsmxKZXVwYD6SP2s0O+a51me\nuSq0y6WYBwJwww+2T8qHfH3qI8mUpurOYZ4p5Kee0Dij5RnZhTCTsG7PTXdd5eK2E9bXhvjdkf6x\n1+jOef6yqK0+7/JwTRWxhPtc8nxXMI86B4CJ+ZDrgjq7/yiDwwk1BuYwzxTyg535f4H8YOcgC9ty\nF/NMIXzKHqG7L05rpIaVK9pJAU+/1DmlJJ6N2QJuO6FbawwmXyAl3tRxdDjv8ht/+AytkRpWmQWu\nufWeda/G7j/KKx39Y8XbzbadHVzyhiU5+9T91BioZONpLvFMIY/2D81o+X8+9NK4e1p098V5aMI9\nLjJJHE+McO7pJ7J4QSOR+ppJ6/rBQy+N+9vM36VSKd53sZn0+pkmX76BLTeZi0kWtBxr8WkH8J7h\nXFNWsnT3xXMW1h8/smdcEc6lqzfGgSP9OfvU3VSiMTCVnNRUy5nxTCHviOYv1C8f7OGnj+/jzGXz\nJ7XMY4kkv3z2UNHv9YunD/OLpw9TFYCT2hv5/BWrSKUC9PTHqAtXs/XZw65/t/XZw7xn3bKxpHQ7\nC8i0rkaSKQ51DpB0Tp3zJXW+gS032XfO0w7gXcUU4YxfP3eIpvoQq1e0s7CtkVgiyZO2+IN7VVWA\n5oZqjg4UPniAe2OgVKaSk5n94oHf7OfhbQfHnvf6mYPXeKaQH+nO37Xyq+c7+dXznfzwkd1UBwPc\n+JcXsOdAD1ueeoUzXj2f4fjUpyeOptI726dv/iXBqgA9Awka6qoZjruP9g/Hk+z63VF6BuKYU+Zx\n/2/2TzoLePCJA7y4L8rAUJxof4J5DdVEGmrpG4xztD/OvMYa1py2YNwAVF24OufAlpvsO+fpF2i8\nK9qfv2slW99Qkp/84mV+8ouXCQDzGkNE+xNF//31m7dNKbbmxjDxRDJnH33fYJwDR/pZvCDdaMr8\nO/sMNvs1kfqasaJ836/38YvtxxpWmZwcGh7h8rcawqHgpGKfy/HSjTjTM2rPFPIdLx8t+rUjyRR/\n9dXHxh4/s6d7Ru/dP3SsFTMwlL9F888/fLrg+g50DIz9++jACEcHjrXMjvani/0Le7sZjifHWiy1\n4WqguEIeIL3hO6KDPJVzrnHHcbEDeNk2e2Raf5eCKRXx6Yj2xbhu029paazhNUtaueSCJXT1DFNf\nV83nb3mcQy4Nq8wZ7Gc2nsVXNm8f67sPAA111dSEgnmL8tYdh3l+bxenL5lPTSjAI9sKn0V39Zbv\nzMELSnVGXVQhN8acC1xvrV034flLgOuAEWCTtfbW4j/CeMfbjNdXsgZ30y3x4rtWHnv2kHPP9njO\n13T1xub0DuAHQ+WtxSUR7Y+zdcdhtu5w707MljmDvfIbWxlJHht9TeE0hgo0gtLvl+CXRbxXtv96\ndA+Xv/U06sOeaXeWTKnOqAuWfGPMZ4F/BWonPB8CbgLeAqwFPmqMObHod5Zpi8VH8xZxSLee6uZg\n4svsyy7ilfD480f49M2PsfnBnSSLuEeNX5Ty6t1i2u67gXe7PH86sMtaG7XWxoHHgAuLfmcpq9EU\nDMWKG/gS8brh+Oicu89MKW9zXLDJZq290xizxGVRE9CT9bgPaC60vpaWeqqr1W9bbu3zalm6ZD61\nNTNrlbe3R0oUUWHD8RGivTFamsIzjns2KLfL75ndXXzs0rpx+VHJHJ2JiXFGmutob6njiMuMvbZ5\ndVPaf2eyt/QC2ZFFgIIjltFo/tkpUhpnLmujr2eI/Hf7yK+9PUJHx0zWUJxSTqGczZ36eMntYBXM\n1j3sOo8OsXtv19jYT6VydKZyxXnG0vmuV++esXT+pP03X27PpJC/ACw3xrQC/cBFwA3TXVlLI0SL\nn3brezWhAI21NRztj9ESqeX1y1rZdaCHgx0DjKbSfdxVVYEp9UcGAtDqw98vnatTKOdHqujqmzt9\nuhlvPGMRew72Tpq1MpIcdZ0GvHhBA51Hh12n9a5buYhEYpQX9nUXHPeB8ddQzAWl+v3hKRdyY8xG\noNFae4sx5krgAdJ97ZustZN/LrxIKxa38esX3X/fcDasX7WIt597Knb/Udc54xmNtdUMxEZIpdKF\n9KT2BpYtbubpnZ1E++PUVAeIj0wuxhedeZLrb45mz80N11Tx999/amyHqQpAfbia/uHJfd9rz1rI\n28891XdXdk7lt1r95uzTFvLT3057l/CsHXu6+dKfnUs8kRzL1ZpQkGtufZzh+OR+3aHhJF/+6Hnc\n+chuXtwfJdoXG1ewglVVY/Ooa0JV/PiRPTxpjxBLTD4orFzR5tt8cFOq3x8uqpBba/cC5zn/3pz1\n/D3APVN+V7dAguXZOI211YRrqon2DVMTqsrZYhgcPtY/uyrrtP4Nr68D4E/evJyqQICnbIeTiGFW\nmfTrBodHJl0w0ffGOH3xUeqrA9z3+D7XI26wqmrS9MBIfQ2nL2kde/yFD50zrrjX11Y7V5NOjsOP\nV3IWM+Dj1ymUiRLM7ggG0jlxdCBOVSA9iB0K4vojLOFQFfHEKC2RMGetaCMAbH+pk67e2NjfBnC/\n6WJLY5g/fZuhtSnM/b85wHN7OukddJ8/mb1dMrl6JDqYdzvGE0k+/I7X5LzwJfP7uwAfecdr2Hjx\ncjb/70u8uC86dtY6saUaSyTHXT3tZ9mffzo8M6L0+mVtbH3u9zmX14VgOAHzGmsYiiddT9OCVQFG\nR1OkwPXy+8b6EP/16MvOHOwYrZHib/OZ78iZXXzH9fdmvccXPnw2/YOJaR1xJxb3UhzBvSLf7Vr9\nfhp97mtO4KGnDhZ+YR4p4MrLzqQmFKQuXM1QbCQrj8c3Dt514asm5dh71i0bu3p4KDbCA7/9HQ8/\nNfksYfVp7ZyxrA2Aqz94Dnv2dfF3m35L1GXmhNt2KXY7Fluw6sMhPpKj8Ofax/zamCkFzxTyRfPz\nb9y/2biaxtoQzY1h7vz5btcBgjetOolL3rDE9XLiTPLkKoLBKopKsEKJWKn+3pkewb0i3+1a/X4a\n3dSQ/yB0wetO5NJ1S9l/uI9/f8AS7XMvgu0t9eMaDZA7j+vD4+/bn50nkfoaNm5YTrAqULBPNlJf\nw+rTit8u5dqObnk+V8dUZsIzhby9pZ5gANzORoMBOKmtcSwZ8g0QBKuqxrVe3ZSrCM7l/t5yKtWA\nj9c0N4Zpqquid2hyd16ktmrsviPzloVZvbd7ykVwOnk8lT7ZqW6XSmxH7WPuPFPIw6Ega1ed5Dqg\nuHbVSeM2TqkGCEptLvf3lpNXt+dMhUNBznntItcCfe7rFo37jJU+mBVzEJjqdqnEdtQ+5s4zhRzy\nDyi68Vr3wlzu760Er23PUii2QHv5YDbV7VLO7ah9zJ2nCnl2MgdrQiTjiZIkc6V+dGEu9/fK9Ew1\np0tRBOfyj4xoH3PnqUKeEQ4FaW9rmPEVW7Pxowtztb9XZqZUOZ3P8fIjI9rHJgukUpW9k1lHR19R\nb1iKS283P7jT9ci9Yc3iso9uxxLJkp5VzAa/XP6crb09Epit9y6U2+X+PkuV737Z7n7ax0rxnebL\n7blzmJ6glLeInI5wKMjCtgbPJ5jMDbOd77NB+9gxnizkmSu2ZpJ8pbxFpMhMlSKn81G+H9881Ude\nyiu2NLotXlCpqxCV78c3T7XIM1dsdfXGSKWOXbE1nZvJZ0a33RzPo9tSWaXM6XyU78c3zxTycvTx\nXbZ+GRvWLGZ+Uy1VAZjfVMuGNYuP69FtqZxK91sr349fnulaKccVW16+yELmvkpfhah8P355pkWe\n6eNzM9M+vsxFFkpqqaRy5nQ+yvfjj2cKufr4ZK5RTkuleKZrBXTFlsw9ymmpBE8V8nLda0Vktiin\npRI807WSTVdsyVyjnJZy8mQhFxGR4qmQi4j4XMXvfigiIqWlFrmIiM+pkIuI+JwKuYiIz6mQi4j4\nnAq5iIjPqZCLiPicpy7RBzDGVAHfAs4EYsBHrLWlvQt/mRhjzgWut9auM8YsA74HpIAdwF9Ya0dn\nM75cjDEhYBOwBAgDXwKexyfxe5nX89lv294YswB4ErgYGMG7cV4F/CFQQ3r7/5wyxurFFvm7gFpr\n7fnA/wP+eZbjKYox5rPAvwK1zlM3AtdYay8EAsA7Zyu2IlwOdDmxvh34Jv6K38u8ns++2fbOQedf\ngCHnKa/GuQ54A3ABsBY4mTLH6sVC/kbgpwDW2seBNbMbTtF2A+/Oerya9FEY4H5gQ8UjKt6PgGuz\nHo/gr/i9zOv57KdtfwPwHeCg89ircb4VeBa4C7gHuJcyx+rFQt4E9GQ9ThpjPNcFNJG19k4gkfVU\nwFqbuWy2D2iufFTFsdb2W2v7jDER4MfANfgofo/zdD77ZdsbYz4AdFhrH8h62nNxOtpIH7DfC3wc\nuB2oKmesXizkvUAk63GVtXZktoKZgez+rwhwdLYCKYYx5mTgYeA2a+1mfBa/h3k+n32y7T8EXGyM\neQQ4C/g+sCBruVfiBOgCHrDWxq21FhhmfOEueaxeLORbgT8AMMacR/oUxY+2OX1lkO57fHQWY8nL\nGHMC8DPgc9baTc7Tvonf4zydz37Z9tbai6y1a62164DtwBXA/V6L0/EY8DZjTMAYswhoAB4qZ6ye\nOcXLchfpI+8vSQ8KfHCW45muvwFuNcbUAC+QPm31qquBFuBaY0ymv/RTwNd9Er+XeT2f/bztPbmP\nWWvvNcZcBPyGdGP5L4CXKWOsuvuhiIjPebFrRUREpkCFXETE51TIRUR8ToVcRMTnVMhFRHxOhVxE\nxOdUyEVEfE6FXETE5/4/vCOoZASWEjcAAAAASUVORK5CYII=\n",
"text/plain": [
"<matplotlib.figure.Figure at 0x121c4b4e0>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row')\n",
"f.suptitle('ESG Sums Plotted Against Returns')\n",
"ax1.scatter(x=all_data['EMP-str-sum'], y=all_data['avg_quarterly_roi'])\n",
"ax2.scatter(x=all_data['EMP-con-sum'], y=all_data['avg_quarterly_roi'])\n",
"ax3.scatter(x=all_data['ENV-str-sum'], y=all_data['avg_quarterly_roi'])\n",
"ax4.scatter(x=all_data['ENV-con-sum'], y=all_data['avg_quarterly_roi'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"let's look at the general trends of the returns. They look pretty flat to me: I don't see an upward or downward trend in quarterly ROI based on any of the 4 ESG metrics.\n",
"\n",
"Let's dig a little deeper and see if the numbers themselves support that."
]
},
{
"cell_type": "code",
"execution_count": 252,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"esg_sum_zero = all_data[all_data['EMP-str-sum'] == 0.0]\n",
"twenty_examples = esg_sum_zero.sample(20)\n",
"\n",
"esg_sum_above_zero = all_data[all_data['EMP-str-sum'] > 0.0]\n",
"\n",
"emp_str_sum = pd.concat([esg_sum_above_zero, twenty_examples])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The thing about a lot of our data is that it skews heavily toward one side or the other. For example, there are a lot more companies with low employment policy strength scores than high ones. How do we account for this when we do our aggregations? In the code below, I try to separate strata of ESG scores into buckets of similar size. That means a lot of the higher scores get bucketed together, while zero (0) or one(1) get their own buckets."
]
},
{
"cell_type": "code",
"execution_count": 253,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" <th>avg_roi</th>\n",
" <th>avg_quarterly_roi</th>\n",
" <th>env_con_sum_grouping</th>\n",
" <th>emp_str_sum_grouping</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6.0</td>\n",
" <td>4.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>6-7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19.0</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>37.0</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" <td>0.990271</td>\n",
" <td>0.990271</td>\n",
" <td>Above 21</td>\n",
" <td>Above 11</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAL</td>\n",
" <td>1.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>0.863333</td>\n",
" <td>0.984556</td>\n",
" <td>0.982431</td>\n",
" <td>0.920166</td>\n",
" <td>...</td>\n",
" <td>1.025882</td>\n",
" <td>1.452599</td>\n",
" <td>0.912895</td>\n",
" <td>0.952148</td>\n",
" <td>1.016954</td>\n",
" <td>0.935397</td>\n",
" <td>1.007726</td>\n",
" <td>1.007726</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>6-7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>ABI</td>\n",
" <td>11.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>Above 11</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 70 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"0 A 6.0 4.0 0.0 0.0 \n",
"1 AA 19.0 0.0 15.0 37.0 \n",
"2 AAL 1.0 0.0 0.0 0.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"5 ABI 11.0 0.0 0.0 0.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"0 NaN NaN \n",
"1 Agilent Technologies Inc. NaN \n",
"2 Alcoa Inc 0.863333 \n",
"3 American Airlines Group NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 0.984556 0.982431 \n",
"3 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"0 NaN ... \n",
"1 NaN ... \n",
"2 0.920166 ... \n",
"3 NaN ... \n",
"5 NaN ... \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"0 NaN NaN \n",
"1 1.130946 1.322479 \n",
"2 1.025882 1.452599 \n",
"3 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"0 NaN NaN \n",
"1 1.081737 0.925703 \n",
"2 0.912895 0.952148 \n",
"3 NaN NaN \n",
"5 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi avg_roi \\\n",
"0 NaN NaN NaN \n",
"1 0.736680 1.117293 0.990271 \n",
"2 1.016954 0.935397 1.007726 \n",
"3 NaN NaN NaN \n",
"5 NaN NaN NaN \n",
"\n",
" avg_quarterly_roi env_con_sum_grouping emp_str_sum_grouping \n",
"0 NaN 0 6-7 \n",
"1 0.990271 Above 21 Above 11 \n",
"2 1.007726 0 1 \n",
"3 NaN 0 6-7 \n",
"5 NaN 0 Above 11 \n",
"\n",
"[5 rows x 70 columns]"
]
},
"execution_count": 253,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def buckets(score):\n",
" if score == 0.0:\n",
" val = '0' \n",
" if 0.0 < score < 2.0:\n",
" val = '1' \n",
" if 1.0 < score < 4.0:\n",
" val = '2-3' \n",
" if 3.0 < score < 6.0:\n",
" val = '4-5' \n",
" if 5.0 < score < 8.0:\n",
" val = '6-7'\n",
" if 7.0 < score < 11.0:\n",
" val = '8-10'\n",
" if 10.0 < score:\n",
" val = 'Above 11' \n",
" return val\n",
"\n",
"emp_str_sum['emp_str_sum_grouping'] = emp_str_sum['EMP-str-sum'].apply(buckets)\n",
"emp_str_sum.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"And here are our initial results for employment policy strength scores and average quarterly ROIs!"
]
},
{
"cell_type": "code",
"execution_count": 267,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>emp_str_sum_grouping</th>\n",
" <th colspan=\"2\" halign=\"left\">avg_quarterly_roi</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>count</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>15</td>\n",
" <td>1.015053</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>39</td>\n",
" <td>1.015095</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2-3</td>\n",
" <td>41</td>\n",
" <td>1.022955</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>4-5</td>\n",
" <td>36</td>\n",
" <td>1.025518</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>6-7</td>\n",
" <td>27</td>\n",
" <td>1.018502</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>8-10</td>\n",
" <td>30</td>\n",
" <td>1.010588</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>Above 11</td>\n",
" <td>39</td>\n",
" <td>1.016971</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" emp_str_sum_grouping avg_quarterly_roi \n",
" count mean\n",
"0 0 15 1.015053\n",
"1 1 39 1.015095\n",
"2 2-3 41 1.022955\n",
"3 4-5 36 1.025518\n",
"4 6-7 27 1.018502\n",
"5 8-10 30 1.010588\n",
"6 Above 11 39 1.016971"
]
},
"execution_count": 267,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"grouping = emp_str_sum.groupby(['emp_str_sum_grouping']).agg({'avg_quarterly_roi': ['count','mean']}).reset_index()\n",
"grouping.sort_values('emp_str_sum_grouping')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"My buckets are not perfectly the same sizes, but the means are not orders of magnitude apart. They're all in the 1.01-1.02 range, whether the company had a very high employment policy strength score or a sorry zero. \n",
"\n",
"We'll check for statistical significance in the next installment. In the meantime, though, our representation of these numbers happens to suppress differences. That's because we're looking at _quarterly_ ROI, which is a very short period of time for stock returns in which relatively little compounding will have taken place. It's more common for folks to evaluate and measure stocks on their _annual_ ROI. We can go back and find annual ROI means in our data, but honestly it's sort of a pain with the way this data is represented. So instead, we'll get an approximation by taking the quarterly ROI and raising it to the fourth power, since there are four quarters in a year."
]
},
{
"cell_type": "code",
"execution_count": 268,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>index</th>\n",
" <th>emp_str_sum_grouping</th>\n",
" <th>quarterly_mean_roi</th>\n",
" <th>extrapolated_annual_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1.015053</td>\n",
" <td>1.061587</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1.015095</td>\n",
" <td>1.061760</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>2-3</td>\n",
" <td>1.022955</td>\n",
" <td>1.095032</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>4-5</td>\n",
" <td>1.025518</td>\n",
" <td>1.106044</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>4</td>\n",
" <td>6-7</td>\n",
" <td>1.018502</td>\n",
" <td>1.076085</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>5</td>\n",
" <td>8-10</td>\n",
" <td>1.010588</td>\n",
" <td>1.043027</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>6</td>\n",
" <td>Above 11</td>\n",
" <td>1.016971</td>\n",
" <td>1.069633</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index emp_str_sum_grouping quarterly_mean_roi extrapolated_annual_roi\n",
"0 0 0 1.015053 1.061587\n",
"1 1 1 1.015095 1.061760\n",
"2 2 2-3 1.022955 1.095032\n",
"3 3 4-5 1.025518 1.106044\n",
"4 4 6-7 1.018502 1.076085\n",
"5 5 8-10 1.010588 1.043027\n",
"6 6 Above 11 1.016971 1.069633"
]
},
"execution_count": 268,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"emp_str = grouping['emp_str_sum_grouping']\n",
"qtrly_roi = grouping['avg_quarterly_roi']['mean']\n",
"emp_str_ann_roi = grouping['avg_quarterly_roi']['mean']**4\n",
"annual_view = pd.concat([emp_str, qtrly_roi, emp_str_ann_roi], axis=1).reset_index()\n",
"annual_view.columns = ['index','emp_str_sum_grouping', 'quarterly_mean_roi', 'extrapolated_annual_roi']\n",
"annual_view"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": []
},
{
"cell_type": "code",
"execution_count": 269,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" <th>avg_roi</th>\n",
" <th>avg_quarterly_roi</th>\n",
" <th>env_con_sum_grouping</th>\n",
" <th>emp_con_sum_grouping</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6.0</td>\n",
" <td>4.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>3-4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>5 and up</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>ABS</td>\n",
" <td>9.0</td>\n",
" <td>8.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>5 and up</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>ABX</td>\n",
" <td>4.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>3.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>3-4</td>\n",
" <td>3-4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>ACY</td>\n",
" <td>2.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>13.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Above 12, below 21</td>\n",
" <td>3-4</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 70 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"0 A 6.0 4.0 0.0 0.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"7 ABS 9.0 8.0 0.0 0.0 \n",
"9 ABX 4.0 3.0 0.0 3.0 \n",
"16 ACY 2.0 3.0 0.0 13.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"0 NaN NaN \n",
"3 American Airlines Group NaN \n",
"7 NaN NaN \n",
"9 NaN NaN \n",
"16 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"0 NaN NaN \n",
"3 NaN NaN \n",
"7 NaN NaN \n",
"9 NaN NaN \n",
"16 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"0 NaN ... \n",
"3 NaN ... \n",
"7 NaN ... \n",
"9 NaN ... \n",
"16 NaN ... \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"0 NaN NaN \n",
"3 NaN NaN \n",
"7 NaN NaN \n",
"9 NaN NaN \n",
"16 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"0 NaN NaN \n",
"3 NaN NaN \n",
"7 NaN NaN \n",
"9 NaN NaN \n",
"16 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi avg_roi \\\n",
"0 NaN NaN NaN \n",
"3 NaN NaN NaN \n",
"7 NaN NaN NaN \n",
"9 NaN NaN NaN \n",
"16 NaN NaN NaN \n",
"\n",
" avg_quarterly_roi env_con_sum_grouping emp_con_sum_grouping \n",
"0 NaN 0 3-4 \n",
"3 NaN 0 5 and up \n",
"7 NaN 0 5 and up \n",
"9 NaN 3-4 3-4 \n",
"16 NaN Above 12, below 21 3-4 \n",
"\n",
"[5 rows x 70 columns]"
]
},
"execution_count": 269,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"esg_sum_zero = all_data[all_data['EMP-con-sum'] == 0.0]\n",
"eighteen_examples = esg_sum_zero.sample(18)\n",
"\n",
"esg_sum_above_zero = all_data[all_data['EMP-con-sum'] > 0.0]\n",
"\n",
"emp_con_sum = pd.concat([esg_sum_above_zero, eighteen_examples])\n",
"\n",
"def buckets(score):\n",
" if score == 0.0:\n",
" val = '0' \n",
" if 0.0 < score < 3.0:\n",
" val = '1-2' \n",
" if 2.0 < score < 5.0:\n",
" val = '3-4'\n",
" if 4.0 < score:\n",
" val = '5 and up'\n",
" return val\n",
"\n",
"emp_con_sum['emp_con_sum_grouping'] = emp_con_sum['EMP-con-sum'].apply(buckets)\n",
"emp_con_sum.head()"
]
},
{
"cell_type": "code",
"execution_count": 270,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>emp_con_sum_grouping</th>\n",
" <th colspan=\"2\" halign=\"left\">avg_quarterly_roi</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>count</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>9</td>\n",
" <td>1.027691</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1-2</td>\n",
" <td>55</td>\n",
" <td>1.017798</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>3-4</td>\n",
" <td>41</td>\n",
" <td>1.019816</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>5 and up</td>\n",
" <td>39</td>\n",
" <td>1.008384</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" emp_con_sum_grouping avg_quarterly_roi \n",
" count mean\n",
"0 0 9 1.027691\n",
"1 1-2 55 1.017798\n",
"2 3-4 41 1.019816\n",
"3 5 and up 39 1.008384"
]
},
"execution_count": 270,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"grouping = emp_con_sum.groupby(['emp_con_sum_grouping'])[['avg_quarterly_roi']].agg(['count', 'mean']).reset_index()\n",
"grouping.sort_values('emp_con_sum_grouping')"
]
},
{
"cell_type": "code",
"execution_count": 271,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>index</th>\n",
" <th>emp_con_sum_grouping</th>\n",
" <th>quarterly_mean_roi</th>\n",
" <th>extrapolated_annual_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1.027691</td>\n",
" <td>1.115452</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>1-2</td>\n",
" <td>1.017798</td>\n",
" <td>1.073116</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>3-4</td>\n",
" <td>1.019816</td>\n",
" <td>1.081651</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>5 and up</td>\n",
" <td>1.008384</td>\n",
" <td>1.033961</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index emp_con_sum_grouping quarterly_mean_roi extrapolated_annual_roi\n",
"0 0 0 1.027691 1.115452\n",
"1 1 1-2 1.017798 1.073116\n",
"2 2 3-4 1.019816 1.081651\n",
"3 3 5 and up 1.008384 1.033961"
]
},
"execution_count": 271,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"emp_con = grouping['emp_con_sum_grouping']\n",
"qtrly_roi = grouping['avg_quarterly_roi']['mean']\n",
"emp_con_ann_roi = grouping['avg_quarterly_roi']['mean']**4\n",
"annual_view = pd.concat([emp_con, qtrly_roi, emp_con_ann_roi], axis=1).reset_index()\n",
"annual_view.columns = ['index','emp_con_sum_grouping', 'quarterly_mean_roi', 'extrapolated_annual_roi']\n",
"annual_view"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Especially uneven bucketing here, unfortunately. I could take a random sampling of the larger buckets. Maybe in the next round.\n",
"\n",
"Anyway, this is employment concerns (scores for treating workers poorly)."
]
},
{
"cell_type": "code",
"execution_count": 272,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" <th>avg_roi</th>\n",
" <th>avg_quarterly_roi</th>\n",
" <th>env_con_sum_grouping</th>\n",
" <th>env_str_sum_grouping</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19.0</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>37.0</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" <td>0.990271</td>\n",
" <td>0.990271</td>\n",
" <td>Above 21</td>\n",
" <td>9 and up</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>1-3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>AEP</td>\n",
" <td>1.0</td>\n",
" <td>0.0</td>\n",
" <td>2.0</td>\n",
" <td>21.0</td>\n",
" <td>Analog Devices Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>1.091901</td>\n",
" <td>1.200684</td>\n",
" <td>1.051698</td>\n",
" <td>0.980629</td>\n",
" <td>0.823704</td>\n",
" <td>0.952037</td>\n",
" <td>0.979946</td>\n",
" <td>0.979946</td>\n",
" <td>Above 12, below 21</td>\n",
" <td>1-3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>AES</td>\n",
" <td>6.0</td>\n",
" <td>0.0</td>\n",
" <td>3.0</td>\n",
" <td>6.0</td>\n",
" <td>Archer-Daniels-Midland Co.</td>\n",
" <td>0.983571</td>\n",
" <td>1.126593</td>\n",
" <td>0.916894</td>\n",
" <td>0.968085</td>\n",
" <td>...</td>\n",
" <td>1.018648</td>\n",
" <td>1.160946</td>\n",
" <td>1.108410</td>\n",
" <td>0.994665</td>\n",
" <td>1.011919</td>\n",
" <td>1.313899</td>\n",
" <td>1.009521</td>\n",
" <td>1.009521</td>\n",
" <td>5-7</td>\n",
" <td>1-3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>31</th>\n",
" <td>AGN</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" <td>1-3</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 70 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"1 AA 19.0 0.0 15.0 37.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"25 AEP 1.0 0.0 2.0 21.0 \n",
"26 AES 6.0 0.0 3.0 6.0 \n",
"31 AGN 0.0 0.0 3.0 0.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"1 Agilent Technologies Inc. NaN \n",
"3 American Airlines Group NaN \n",
"25 Analog Devices Inc. NaN \n",
"26 Archer-Daniels-Midland Co. 0.983571 \n",
"31 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"1 NaN NaN \n",
"3 NaN NaN \n",
"25 NaN NaN \n",
"26 1.126593 0.916894 \n",
"31 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"1 NaN ... \n",
"3 NaN ... \n",
"25 NaN ... \n",
"26 0.968085 ... \n",
"31 NaN ... \n",
"\n",
" quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi \\\n",
"1 1.130946 1.322479 \n",
"3 NaN NaN \n",
"25 1.091901 1.200684 \n",
"26 1.018648 1.160946 \n",
"31 NaN NaN \n",
"\n",
" quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi \\\n",
"1 1.081737 0.925703 \n",
"3 NaN NaN \n",
"25 1.051698 0.980629 \n",
"26 1.108410 0.994665 \n",
"31 NaN NaN \n",
"\n",
" quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi \\\n",
"1 0.736680 1.117293 \n",
"3 NaN NaN \n",
"25 0.823704 0.952037 \n",
"26 1.011919 1.313899 \n",
"31 NaN NaN \n",
"\n",
" avg_roi avg_quarterly_roi env_con_sum_grouping env_str_sum_grouping \n",
"1 0.990271 0.990271 Above 21 9 and up \n",
"3 NaN NaN 0 1-3 \n",
"25 0.979946 0.979946 Above 12, below 21 1-3 \n",
"26 1.009521 1.009521 5-7 1-3 \n",
"31 NaN NaN 0 1-3 \n",
"\n",
"[5 rows x 70 columns]"
]
},
"execution_count": 272,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"esg_sum_zero = all_data[all_data['ENV-str-sum'] == 0.0]\n",
"twenty_examples = esg_sum_zero.sample(20)\n",
"\n",
"esg_sum_above_zero = all_data[all_data['ENV-str-sum'] > 0.0]\n",
"\n",
"env_str_sum = pd.concat([esg_sum_above_zero, twenty_examples])\n",
"\n",
"def buckets(score):\n",
" if score == 0.0:\n",
" val = '0' \n",
" if 0.0 < score < 4.0:\n",
" val = '1-3' \n",
" if 3.0 < score < 10.0:\n",
" val = '4-9'\n",
" if 9.0 < score:\n",
" val = '9 and up'\n",
" return val\n",
"\n",
"env_str_sum['env_str_sum_grouping'] = env_str_sum['ENV-str-sum'].apply(buckets)\n",
"env_str_sum.head()"
]
},
{
"cell_type": "code",
"execution_count": 273,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>env_str_sum_grouping</th>\n",
" <th colspan=\"2\" halign=\"left\">avg_quarterly_roi</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>count</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>12</td>\n",
" <td>1.024696</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1-3</td>\n",
" <td>42</td>\n",
" <td>1.017919</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>4-9</td>\n",
" <td>44</td>\n",
" <td>1.016173</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>9 and up</td>\n",
" <td>23</td>\n",
" <td>1.014847</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" env_str_sum_grouping avg_quarterly_roi \n",
" count mean\n",
"0 0 12 1.024696\n",
"1 1-3 42 1.017919\n",
"2 4-9 44 1.016173\n",
"3 9 and up 23 1.014847"
]
},
"execution_count": 273,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"grouping = env_str_sum.groupby(['env_str_sum_grouping'])[['avg_quarterly_roi']].agg(['count', 'mean']).reset_index()\n",
"grouping.sort_values('env_str_sum_grouping')"
]
},
{
"cell_type": "code",
"execution_count": 276,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>index</th>\n",
" <th>env_str_sum_grouping</th>\n",
" <th>quarterly_mean_roi</th>\n",
" <th>extrapolated_annual_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1.024696</td>\n",
" <td>1.102505</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>1-3</td>\n",
" <td>1.017919</td>\n",
" <td>1.073625</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>4-9</td>\n",
" <td>1.016173</td>\n",
" <td>1.066279</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>9 and up</td>\n",
" <td>1.014847</td>\n",
" <td>1.060724</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index env_str_sum_grouping quarterly_mean_roi extrapolated_annual_roi\n",
"0 0 0 1.024696 1.102505\n",
"1 1 1-3 1.017919 1.073625\n",
"2 2 4-9 1.016173 1.066279\n",
"3 3 9 and up 1.014847 1.060724"
]
},
"execution_count": 276,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"env_str = grouping['env_str_sum_grouping']\n",
"qtrly_roi = grouping['avg_quarterly_roi']['mean']\n",
"env_str_ann_roi = grouping['avg_quarterly_roi']['mean']**4\n",
"annual_view = pd.concat([env_str, qtrly_roi, env_str_ann_roi], axis=1).reset_index()\n",
"annual_view.columns = ['index','env_str_sum_grouping', 'quarterly_mean_roi', 'extrapolated_annual_roi']\n",
"annual_view"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here we have environmental impact strength. I could stratify these buckets more to get a more granular view. "
]
},
{
"cell_type": "code",
"execution_count": 277,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Ticker</th>\n",
" <th>EMP-str-sum</th>\n",
" <th>EMP-con-sum</th>\n",
" <th>ENV-str-sum</th>\n",
" <th>ENV-con-sum</th>\n",
" <th>Company Name</th>\n",
" <th>quarter_starting_12/31/1989_roi</th>\n",
" <th>quarter_starting_03/31/1990_roi</th>\n",
" <th>quarter_starting_06/30/1990_roi</th>\n",
" <th>quarter_starting_09/30/1990_roi</th>\n",
" <th>...</th>\n",
" <th>quarter_starting_03/31/2003_roi</th>\n",
" <th>quarter_starting_06/30/2003_roi</th>\n",
" <th>quarter_starting_09/30/2003_roi</th>\n",
" <th>quarter_starting_12/31/2003_roi</th>\n",
" <th>quarter_starting_03/31/2004_roi</th>\n",
" <th>quarter_starting_06/30/2004_roi</th>\n",
" <th>quarter_starting_09/30/2004_roi</th>\n",
" <th>avg_roi</th>\n",
" <th>avg_quarterly_roi</th>\n",
" <th>env_con_sum_grouping</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>A</td>\n",
" <td>6.0</td>\n",
" <td>4.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AA</td>\n",
" <td>19.0</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>37.0</td>\n",
" <td>Agilent Technologies Inc.</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>1.486692</td>\n",
" <td>1.130946</td>\n",
" <td>1.322479</td>\n",
" <td>1.081737</td>\n",
" <td>0.925703</td>\n",
" <td>0.736680</td>\n",
" <td>1.117293</td>\n",
" <td>0.990271</td>\n",
" <td>0.990271</td>\n",
" <td>Above 21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAL</td>\n",
" <td>1.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>Alcoa Inc</td>\n",
" <td>0.863333</td>\n",
" <td>0.984556</td>\n",
" <td>0.982431</td>\n",
" <td>0.920166</td>\n",
" <td>...</td>\n",
" <td>1.315789</td>\n",
" <td>1.025882</td>\n",
" <td>1.452599</td>\n",
" <td>0.912895</td>\n",
" <td>0.952148</td>\n",
" <td>1.016954</td>\n",
" <td>0.935397</td>\n",
" <td>1.007726</td>\n",
" <td>1.007726</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>7.0</td>\n",
" <td>7.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>American Airlines Group</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>ABC</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 69 columns</p>\n",
"</div>"
],
"text/plain": [
" Ticker EMP-str-sum EMP-con-sum ENV-str-sum ENV-con-sum \\\n",
"0 A 6.0 4.0 0.0 0.0 \n",
"1 AA 19.0 0.0 15.0 37.0 \n",
"2 AAL 1.0 0.0 0.0 0.0 \n",
"3 AAPL 7.0 7.0 2.0 0.0 \n",
"4 ABC 0.0 0.0 0.0 0.0 \n",
"\n",
" Company Name quarter_starting_12/31/1989_roi \\\n",
"0 NaN NaN \n",
"1 Agilent Technologies Inc. NaN \n",
"2 Alcoa Inc 0.863333 \n",
"3 American Airlines Group NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 0.984556 0.982431 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_09/30/1990_roi ... \\\n",
"0 NaN ... \n",
"1 NaN ... \n",
"2 0.920166 ... \n",
"3 NaN ... \n",
"4 NaN ... \n",
"\n",
" quarter_starting_03/31/2003_roi quarter_starting_06/30/2003_roi \\\n",
"0 NaN NaN \n",
"1 1.486692 1.130946 \n",
"2 1.315789 1.025882 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_09/30/2003_roi quarter_starting_12/31/2003_roi \\\n",
"0 NaN NaN \n",
"1 1.322479 1.081737 \n",
"2 1.452599 0.912895 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_03/31/2004_roi quarter_starting_06/30/2004_roi \\\n",
"0 NaN NaN \n",
"1 0.925703 0.736680 \n",
"2 0.952148 1.016954 \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" quarter_starting_09/30/2004_roi avg_roi avg_quarterly_roi \\\n",
"0 NaN NaN NaN \n",
"1 1.117293 0.990271 0.990271 \n",
"2 0.935397 1.007726 1.007726 \n",
"3 NaN NaN NaN \n",
"4 NaN NaN NaN \n",
"\n",
" env_con_sum_grouping \n",
"0 0 \n",
"1 Above 21 \n",
"2 0 \n",
"3 0 \n",
"4 0 \n",
"\n",
"[5 rows x 69 columns]"
]
},
"execution_count": 277,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"env_con_sum = all_data\n",
"\n",
"def buckets(score):\n",
" val = score\n",
" if score == 0.0:\n",
" val = '0' \n",
" if score == 1.0:\n",
" val = '1' \n",
" if score == 2.0:\n",
" val = '2' \n",
" if 2.0 < score < 5.0:\n",
" val = '3-4'\n",
" if 4.0 < score < 8.0:\n",
" val = '5-7'\n",
" if 7.0 < score < 13.0:\n",
" val = '8-12'\n",
" if 12.0 < score < 22.0:\n",
" val = 'Above 12, below 21'\n",
" if 21.0 < score:\n",
" val = 'Above 21'\n",
" return val\n",
"\n",
"env_con_sum['env_con_sum_grouping'] = env_con_sum['ENV-con-sum'].apply(buckets)\n",
"env_con_sum.head()"
]
},
{
"cell_type": "code",
"execution_count": 278,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>env_con_sum_grouping</th>\n",
" <th colspan=\"2\" halign=\"left\">avg_quarterly_roi</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>count</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>254</td>\n",
" <td>1.026470</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>17</td>\n",
" <td>1.021410</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>12</td>\n",
" <td>1.022847</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3-4</td>\n",
" <td>22</td>\n",
" <td>1.013718</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>5-7</td>\n",
" <td>27</td>\n",
" <td>1.020387</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>8-12</td>\n",
" <td>22</td>\n",
" <td>1.022177</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>Above 12, below 21</td>\n",
" <td>26</td>\n",
" <td>1.006532</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>Above 21</td>\n",
" <td>19</td>\n",
" <td>1.003404</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" env_con_sum_grouping avg_quarterly_roi \n",
" count mean\n",
"0 0 254 1.026470\n",
"1 1 17 1.021410\n",
"2 2 12 1.022847\n",
"3 3-4 22 1.013718\n",
"4 5-7 27 1.020387\n",
"5 8-12 22 1.022177\n",
"6 Above 12, below 21 26 1.006532\n",
"7 Above 21 19 1.003404"
]
},
"execution_count": 278,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"grouping = env_con_sum.groupby(['env_con_sum_grouping'])[['avg_quarterly_roi']].agg(['count', 'mean']).reset_index()\n",
"grouping.sort_values('env_con_sum_grouping')"
]
},
{
"cell_type": "code",
"execution_count": 279,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>index</th>\n",
" <th>env_con_sum_grouping</th>\n",
" <th>quarterly_mean_roi</th>\n",
" <th>extrapolated_annual_roi</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1.026470</td>\n",
" <td>1.110161</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1.021410</td>\n",
" <td>1.088431</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>1.022847</td>\n",
" <td>1.094566</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>3-4</td>\n",
" <td>1.013718</td>\n",
" <td>1.056012</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>4</td>\n",
" <td>5-7</td>\n",
" <td>1.020387</td>\n",
" <td>1.084076</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>5</td>\n",
" <td>8-12</td>\n",
" <td>1.022177</td>\n",
" <td>1.091704</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>6</td>\n",
" <td>Above 12, below 21</td>\n",
" <td>1.006532</td>\n",
" <td>1.026384</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>7</td>\n",
" <td>Above 21</td>\n",
" <td>1.003404</td>\n",
" <td>1.013685</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index env_con_sum_grouping quarterly_mean_roi extrapolated_annual_roi\n",
"0 0 0 1.026470 1.110161\n",
"1 1 1 1.021410 1.088431\n",
"2 2 2 1.022847 1.094566\n",
"3 3 3-4 1.013718 1.056012\n",
"4 4 5-7 1.020387 1.084076\n",
"5 5 8-12 1.022177 1.091704\n",
"6 6 Above 12, below 21 1.006532 1.026384\n",
"7 7 Above 21 1.003404 1.013685"
]
},
"execution_count": 279,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"env_con = grouping['env_con_sum_grouping']\n",
"qtrly_roi = grouping['avg_quarterly_roi']['mean']\n",
"env_con_ann_roi = grouping['avg_quarterly_roi']['mean']**4\n",
"annual_view = pd.concat([env_con, qtrly_roi, env_con_ann_roi], axis=1).reset_index()\n",
"annual_view.columns = ['index','env_con_sum_grouping', 'quarterly_mean_roi', 'extrapolated_annual_roi']\n",
"annual_view"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, we have environmental concerns. In this one, the zero category is WAY bigger than the other categories. We could take a sample of this to alleviate that, if we wanted to. "
]
},
{
"cell_type": "code",
"execution_count": 296,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[<matplotlib.lines.Line2D at 0x121ac1be0>]"
]
},
"execution_count": 296,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAEUCAYAAADHgubDAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xd4HNXZ9/HvrlZa9b6ybMmSZVk+7k1y78Zg0zsJEBwI\nJZDypLyppDxPcj1vCGmk8pJACB2CAwZMsQ24V9wtWdaxJVkusmX1ZnVp3z92ZYRR165mV3t/rsuX\ntbOjmXtXs7+dOXPmjMlutyOEEMI3mI0uQAghxOCR0BdCCB8ioS+EED5EQl8IIXyIhL4QQvgQCX0h\nhPAhFqMLEH2jlLIDWUDrZU/dpLUuUErNAR4DYnB8qZ8Bvqe1PtphGV8BvgqEAVYgH/ip1npPF+u8\nF/gmju3FAuwC/o/WusqFL61bSqklwAeABuyACWgBfqG1XquU+h8gVmv9jR6WswG4S2tdqpR6AAjQ\nWj/Zx1qygG9orTd38fw3gT8Dc7XWu/uy7E6W9TAQqbX+dT9//9LrvWz6Ej59P9uFAdnAvVrrsh6W\n+3PgsNb67f7UJYwjoe+dll7+IQZQSlmBd4GrtNYHnNO+BHyglErRWrcqpX4FLALu0Fqfcs6zDHhX\nKZWutT592TJnAj8HMrTW5UopP+BvwP8D7nLja+xMntZ6WofapgI7lFIpfVjGlR1+XoDjC9TVHgZe\nBr4NfHEgC9JaPzXAWq7s5rnL308/4A3ge8CPe1juMhxfEMLLSOgPLcFAJBDaYdrLQDXgp5SKxRFE\nqVrr8+0zaK03KqW+C4R0sszhOI4YgoFy5xfHz4GJAJfvYXd8rJTaDOwH5gBxwD+AeGCxc113aK0z\nlVK3AD8F2nAcwXxfa721pxertT6slKoDkjtOV0pNBP6K42jHDvxea/2CUupfzlk2KaV+A9wAXKmU\nqtda/00p9RPgVufrLQC+prU+p5SaADzrfA9yunif2te9BIgGfgDkKaVGaq3POJ8b41xONHAex9HK\nS1rr55RSjwI3AkHO5X9Pa73msvezAHgOuAJIAl7QWv9MKRUK/AtIc76H+3Ecyf2zw+u9pr2OboQD\nNmCHs94I4E/AZMAf+Bj4vnPZGcBvlVKtzrqztNa/c/7ec+2PnTXvAaYAjwJP9OU1aK3beqhZ9JG0\n6XunTUqpQx3+rQHQWlfgCJt1Sql8pdSLwH3AR1rrJmAucKxj4LfTWr+otT7Wybo+wBECBUqpA0qp\nvwIzgc29rHWU1no+8CXgN8BmrXUGsA5HkxHAb3EEbAbwM2BJbxbs/LJoo8Mep1LKArwD/EVrPQW4\nGviVUmqu1vo+52xLtdYvOud7whn4q3CE2yzn3u/7wDPO+V8GnnYu709c9iVzma8BL2utzwEbgY7N\nTS8Cr2qtJwH/hePvgVIqGVgOLHGu4yfAL7tYfqjWeiEwD/ie8yjnZiDMWfdM53yjL3u9nQV+qnP7\nOaqUKsYR6u8Af3Q+/wSwX2udDkwHYoHvaq3/BuzD8eW8ppv3ol2W1np8h3l7/Rp6sWzRR7Kn7506\nbd4B0Fr/QSn1NI696UXAD4EfKqVm4dizvDTuhlIqDNjmfBgKvK61fvSy5TUDdyulvg8sdS73eRwB\n8YVe1Pqm8/885//rOjxe4vz5NWCNUuo94EMcXw6dSVVKHXL+7I/jfMWNWus6pVT7PGOBQK31m876\nzyml3gBW4jgX0ZXrgFnAPuey/IBgpVQMjr3UF5zL2+Fs0/8cpVQ8cBOQ7pz0PPCUUuqXQIBz+Yuc\nyzmmlPrY+fMp55fO3c6jgTl89mito7edv1PoDOpoYDuOL7bNON6/P2qtc7t5re0uNe8ope4DfgWs\ndv7NL70nSqn7nY+DerHMzmy77LErX4PoI9nTH0KUUvOVUt/XWtdord/VWv8ARzOMHUfb7h5gnDPI\ncM43zfnBfwnH4f3ly/yKUuoGrfU5rfXLWuuHgBnA7c7movaTqu0CLltEY8cHHQKl47Sf4Ghf3wfc\nC3TVtJPXXq/WeqLWemUnJ5/96PDF5mTG8SXRHT/g8Q7vRwYwv8PzHV9jSxfLeNC57rXOZo3f4XhP\nv9zhdzoupxVAKTUDxxdSOLABePyy+Tqq7/CzHTBprU8CY3CcwA8HPlJKXd/VC+2M1vpfOPbyVzuP\nlsDxntze4T2ZzWePXD5TR4fHl28DtYPxGkTvSOgPLSXAT5VSCzpMGw5EAJnOJoc/4fhgJ7XP4Gxe\nmM/newSBo/nkcaVUYodpE4FTQIVznelKKZPzyOG6vhSslLI4AzLYedLya8AU50np/sgBmp1NPyil\nRuBop//Q+Xwrn34BtHT4eT3wgFKq/Yvvl8CLzl4s+4EHnMubgaMZ6PLX4Ycj9B/WWo9y/kvCsff8\nLRzBtwNHcxvOJo0rcITeImCf1voPwBYcRwt+vX3BSqlHcLSHb9Ba/9D5WmZ08np78kNgJPB15+P1\nwHecf1srji+F9tDv+N6V4PiSbH+/F/e29l6+BuFC0rzjnTY5T6B19KjW+n2l1E04DpMTgQagCrhP\na63BsVetlLobeEUpFYLjC6ECRxPL3y5fkfMkYzDwvvODbweOAyucJ3VfxtFufgIoxBFaXe2lfo7W\nukUp9W1nPc04vmS+orVu7OFXu1pes/M9+LPzJKgF+KXWepNzltXAFueXwgfAH5zNOY8DCcBu5egW\nexrHUQfAncC/nMGUC3R27uM6HDtRL182/QkcoX8NsAr4p1Lqazjeq5NAHY4mr1uVUsecy3gXiHZ+\nifbGCziayrKdJ7ZP4+gy+pnXq7XutqeS1rpSKfVD4Aml1Gs4zjv8CcjEEfAf8WnT2zvAY0qpAOAv\nwMtKKY3jBPjGXtbd29cgXMgkQysLMTicvYPe0FrnOHvGHAGu1lpL10cxaGRPX4jBcxz4t1KqDcdn\n79cS+GKwyZ6+EEL4EDmRK4QQPkRCXwghfIiEvhBC+BAJfSGE8CES+kII4UMk9IUQwodI6AshhA+R\n0BdCCB8ioS+EED5EQl8IIXyIhL4QQvgQCX0hhPAhEvpCCOFDJPSFEMKHSOgLIYQPkdAXQggfIqEv\nhBA+xKNvl1hSUtPlbb2iooKpqKgbzHJcQuoeXN3VbbOF9foG7q7mrdu2J9cGnl3fYNbW3bbttXv6\nFouf0SX0i9Q9uLyxbk+u2ZNrA8+uz1Nq89rQF0II0XcS+kII4UMk9IXwEI3NrXz/yR28uemE0aWI\nIUxCXwgP4Wc20dTSxn82nqCpudXocsQQJaEvhIew+JlZNHUENXXN7Mm+YHQ5YoiS0BfCgyydnoDZ\nbOLj/Wex27vs1SlEv0noC+FBosMDmTMpntPFteQWVhldjhiCJPSF8DDXzR8NwMf7zxpciRiKJPSF\n8DCTUmNIsIWwX5dQUdNodDliiJHQF8LDmEwmrpiRSGubnS2HCo0uRwwxEvpCeKA5E4cRZLWw5dA5\nWlrbjC5HDCES+kJ4oMAACwunDKfqYhP7dLHR5YghREJ/CGiz29mdXcQbW/JobpGLeoaKpTMSANi4\nX5p4hOt49NDKont2u53DeWW8uSWfsyW1ALS0tvGFZWkGVyZcYVhUMJNHx5CZX8apohqS48OMLkkM\nAbKn76X06Qoee+kAf/7PEQpLapk3KZ5h0cFs+OQMx05VGF2ecJEr0hMB6b4pXEdC38ucKqrhD/8+\nxOOvHCS3sIrpabH88v5ZPHDdBB68bgImk4l/vpdNXUOz0aUKF5g0Opq4qCB2Z1+gtl7+pmLgJPS9\nxPmyizz5Vha/eG4vWSfLGZ8cxU9WpfPNW6eQYAsFYPSIcG6YP4ry6kZe+vC4wRULVzCbTCybkUhL\naxtbD58zuhwxBPSqTV8pNRt4XGu9pJPngoEPgfu11jlKKTPwJDAVaAQe0FrnKqXmAH8CWoANWutf\nuOg1DGnl1Q28vf0kOzKLaLPbSRkezq2LRzNhVHSn8187L5kj+WXsPnqBaWNimTV+2CBXLFxtweR4\n3tyax6YDZ1k5Kwmz2bC7PIohoMc9faXUD4BngMBOnssAtgKpHSbfBARqrecCPwJ+75z+FHAXsACY\nrZSaMbDSh7bquiZe/egEP/r7LrYdOU98TDBfv3kyP12V3mXgA/iZzTx43QQC/M28sE5TXt0wiFUL\ndwgO9GfexHjKqhs5lFtqdDnCy/WmeScPuKWL56zAzUBOh2kLgHUAWuvdQIZSKhywaq3ztNZ2YD1w\nRb+rHsLqGlp4a1s+P3xqFx/uO0NkqJX7rx3PL78yi3Rlw2TqeS9vWHQwX7wijbrGFv753jHaZLRG\nr7dMTugKF+mxeUdr/YZSalQXz+0AUEp1nBwOdBwesNU5rbrDtBpgdE/rjooK7vZmwjabd3Zh66zu\nxuZW3tt+kv9sPE5NXTORYVbuvXYCK+Yk49+PGyrftlxx7HQle7MvsCenhBsWpfb8S/2o2xt4Yt19\n3bZttjAmp8aSmVdKQxuMHGbca/LE97MjT67PE2pzRz/9aqDjKzN3Mi0MqOxpQRUVdV0+Z7OFUVJS\n088SjXN53S2tbWzPPM/aHQVU1DQSZLVwy6LRXJkxEmuAH5XdvAc9ueuKNHIKyvnXu9kkxQZfOuHr\nirq9RXd1G/kB7M+2vWhKPJl5paz+SHPPVaqT33Q/T98OPLm+waytu23bHb13dgDXADhP3mZqrauB\nJqVUqlLKBKwAtrlh3V6j/Sranz6zhxfWaS7WN3PNnGR+88hcrps3CmtA3/fuLxcREsC9V4+jpbWN\np9dmyxguXm5aWixRYVZ2ZhZR19BidDnCS/V5T18pdRcQqrX+RxezrAGuVErtBEzAfc7pDwMvA344\neu/s6Ue9Xs9ut3M4t5Q3t+ZzprgWP7OJZTMSuG7eKCJDrS5f3/Q0G4umDmfr4fO8te0kty0ZeDOP\nMIaf2czS6Qm8uTWfHVnnuTJjpNElCS9k8uRbspWU1HRZnCcfxnWlsKSWVz7O5VhBOSZgzsR4blyY\nQlxkkFvXW9/Ywv/86xNKKxv44d0zGDsyss/L8Mb3G3ps3jGs72N/t+3quia+97cdxEQE8X8fnI25\nFyf2XcnTtwNPrm+Qm3e63DDk4qxB0tTcyl/ezORYQTnT02L5xf2zePD6CW4PfIAgq4UHr58IJnh6\nbTb1jdI04K3CgwOYNX4YF8rryC4oN7oc4YUk9AfJu7sKKK6o58ZFqXzz1ikkDuCkan+MSYjg2rmj\nKKtu4JWP5Gpdb3ZpPJ590n1T9J2E/iAoLKnlg92niQm3cvfKcYbVccP8USTHh7Ejs4j9Mka710oZ\nHs7oEeEcySujuLLe6HKEl5HQd7M2u53n12la2+zcfZUiyGrcaNYWPzMPXT+BAIuZ59dpKmvl/qve\n6or0ROzA5gMy1r7oGwl9N9t66By5hVVkKBvTxsQaXQ7DY0K4fekYauubefb9Y3jyiXzRtQwVR3iw\nP9uOnKOxWW6cI3pPQt+NKmsbWb05jyCrH3cuH2t0OZcsm5HApJRosvLL2XRQ9hS9kb/FzKJpCVxs\naGFP9gWjyxFeRELfjV796AT1jS3ctjiVqDDX98HvL5PJxH3XjCck0MLrG3M5X3bR6JJEPyydnoDZ\nZOKjfWfliE30moS+mxzJK2VvTjGpCeEsnp5gdDmfExVm5csrx9HUIlfrequoMCszlI2zJbWcOFvV\n8y8IgYS+WzQ2tfLi+uP4mU18ecW4Qb+AprcyxsUxf1I8BUU1rN1RYHQ5oh+Wy+iboo8k9N3g7e0n\nKatuYMWsJBLjBrc/fl/duXwsMeGBvLurgNxC2Vv0NmmJESTaQtmvS6iokd5YomcS+i52+kING/ae\nwRYZyA3zRxldTo+CAy08eP0EsMMza7NpaJKrdb2JyWTiivQE2ux2NstJedELEvou1NZm57kPcmiz\n21m1YhwB/gMfKXMwjB0Zyco5SRRX1vPaxyeMLkf00ZyJ8QRbLWw5VEhzi5ybEd2T0Hehjw+cpaCo\nhjkThzExpetbGnqimxeOZmRcKFsPn+fgiRKjyxF9YPX3Y+HU4VTXNbNPrrQWPZDQd5Hy6gbe3JpP\nSKCFLy5LM7qcPmu/WtfiZ+a5D3KouthkdEmiD5bOSMQEbJQTuqIHEvou8vKHx2lsauWOpWMIDwkw\nupx+SbCFctuSVGrqmnlOrtb1KnGRQUxJjSHvXDUnz1f3/At9dLGhmbU7TvLX1YekCcnLSei7wH5d\nwsETpaiRkSyYMtzocgZkeUYi45OjOJxXxtbD54wuR/RB++ibrtzbr6ptZPWmXL735E7WbDvJ+t2n\npPnPy0noD1B9YwuvfHQci5+JVSsVJg/tk99bZpOJ+68dT7DVwmsf53JhAPfoFYNrQko0w6KD2XOs\nmOq6gTXPlVbW8+IGzff/3y4+2HOaQH8/rp6TBMD2zPOuKFcYREJ/gN7ckk9FTSPXzElmeEyI0eW4\nRHR4IPesUDQ2t/L02mxa2+Rw3huYTY5bb7a0trGtn0dphaUXeebdbH70991sOlBIZGgAq1YofvPI\nXG5fMgaVHMXRk+VyTYAXk9AfgPxz1Ww8cJb46GCunTvK6HJcavaEYcyZMIz8c9W8t/OU0eWIXlow\neTjWAD82HSzs05f1yfPV/PXNTH72zB52ZhUxPCaYB6+fwGNfncOS6Qn4Wxzdj6+YmYTdDruOFrnr\nJQg3M25wdy/X0trG8+tysANfXqnwtwy978+7rxqLPlPJOzsKWJg+kqgg2Vw8XZDVwrxJ8Ww6UMih\nE6Wkq7gu57Xb7ejTlby3q4CjBRWA4wYt181NZmpabKfDhyyclsDTb2Wy/ch5rp6d5PXNmb6oV59i\npdRs4HGt9ZLLpl8P/BxoAZ7VWj+tlPoRsNI5SyQQr7WOV0p9F7gfaD8L9FWttXbBazDEh/vOcKa4\nlgVThqOSoowuxy1CAv154Nrx/Pa1Q/zhlf38dFUGVi+54MyXLZuRyKYDhXy8/2ynoW+32zmcV8Z7\nuwrIK3T09BmfHMW1c5MZnxzVbZCHBvkzY6yNPdkXyD9XTWpChLtehnCTHkNfKfUD4B7g4mXT/YEn\ngJnO53YopdZqrX8N/No5z7vAD52/MgNYpbXe77ryjVFSWc/b204SFuzPHUvHGF2OW40fFc1VM0ey\nYe8Z3tl+ktuH+OsdChJiQxifHMWxUxUUltSS4Lwfc2tbG3tzinl/12nOltQCMD0tlmvmJpM6ovfh\nPX9yPHuyL7Aj87yEvhfqzZ5+HnAL8OJl08cDuVrrCgCl1HZgIbDa+fgWoEJrvd45fzrwY6VUPPCe\n1vqxnlYcFRWMxdL1nqXNFtaL8l3Lbrfz17eyaGpp45t3TCMlqe9X3hpR90A8eMsUDueVsX7vGVbO\nH81oL/uge+L77e5t++alaRx77hN2Zhfz4E1xbNx3hjec904wm2DJjERuW5ZG8vDwPi97UUYyz6/T\n7M0p5htfnOFxR3+e+Pdu5wm19Rj6Wus3lFKjOnkqHOg4LGMN0DENfgzc2eHxa8DfgGpgjVLqOq31\nu92tu6Kb7oI2WxglJTXdF+8Ge7IvcCCnmIkp0UwYGdHnGoyqe6C+dttU/vsfu3jC2cxjNntHW253\n77eRH0B3b9uj40KICbfy8d7T7Mo8R2VtExY/E0umjWDlnGTiIoMA+rX9lpfVMmfCMN7bdYoNO/OZ\nMyF+QLW6kid/vgaztu627YGcfawGOi45DKgEUEpNACq11rnOxybgj1rrUq11E/AeMH0A6zbExYZm\nXv3oOP4WM/dcNdanTmLNUHHMnTiMgqIaGbvdC5jNJpbNSKSppY36xlZWzkri8YfnsWrluEuBPxDz\nJzsuQtyRKb14vM1AumMcA9KUUtFALbAI+J3zueXABx3mDQeylFLjcbT/LwOeHcC6DbF6Ux7Vdc3c\nung0cVHBRpcz6L5wRRpH8sp4c2s+M8baiIkINLok0Y2rZo0kLioYlRRJaJC/S5cdHx3MmIQIsk+W\nU17dQHS4bAveos97+kqpu5RSD2mtm4HvAuuBXTh677QP6K2A/Pbf0VpXAY8Cm4BtwFGt9fsDLX4w\nHT9TydbD50iwhbBiVpLR5RgiPDiAL16RRmNzKy9u0DI2j4fzM5tJVzaXB367+ZPjsSN99r1Nr/b0\ntdYFwBznz690mL4WWNvJ/F/vZNqLfP5ksFdobnH0yTcBX145Dovf0OuT31vzJsWzM6uII3ll7NMl\nzBzXdT9wo11saGbfzpNMTYm6dHGRcJ2Z44bxykcn2H7kPNfMSfap5k5v5rvp1Qcf7DnF+bI6lkxP\nYIyX9VxxNZPJMcaQv8XMyx8e52JDs9Eldaq1rY0n12Tx5BtHOHneM0/sebvgQAvpY21cqKi/1N9f\neD4J/R4Uldfx7s5TRIQGcOviVKPL8QjDooK5Yf4oqi828Z/NeUaX06k3tuRz7FQFsyfGMybRt7+o\n3an9hK4MwuY9JPS7YbfbeWFdDi2tbdy9fCzBgTIMQbsVs5JIsIWw5dA5jp+pNLqcz9iXU8y6PacZ\nFh3Md+6c0elwAsI1xidHER1uZW/OBRqbW40uR/SChH43dmYVkXO6kqmpMaQrm9HleBSLn5l7V47D\nBDy/LsdjbqxxrvQi/3z/GFZ/P75x8yRC3HQSUziYzSbmTYqnvrGVg8dlnH1vIKHfhZq6Jv69MRer\nvx9fusr7x8l3h9SECJbNSOR8WR3v7zZ+JM76xhb++mYmjU2t3HfNuEvDDwj3mj9Jmni8iYR+F/69\nMZfa+mZuWpgi/dG7ccvi0USFWXlvVwHnSi/2OL+72O12/vneMYrK61gxaySzxg8zrBZfMyw6mDGJ\nERwrqKCsqsHockQPJPQ7kV1Qzs6sIpKHhbE8I9HocjxakNXC3VeOpaXVcf6jzaC+++/vPsWB4yWM\nS4rktiVywn2wLZg8HDuwU/rsezwJ/cs0NbfywnqNyQRfvlrhZ5a3qCczxtqYMdbG8bNV/b5j00Ac\nLSjnza35RIVZefjGSfI3M8DMcXEEWMzsyDwvF+15OK/sjpJ1soyDHx6nvr6ZNrsdu91xeG+385nH\nbZemd/zZ+Rxgb/v89PqmFkoqG7gyYySj4vs+AqGvuvvKsWQXlLN6Ux7TxsQSEWodlPWWVtXz97eP\n4mc28bWbJxEeEjAo6xWfFWS1kK5s7Dp6gRNnqxg7MtLokkQXvDL09x4rZtuR/p80MuG4yMhkcvxv\nNjv/N4EJE+OSIrl5UYrrCvYBUWFWbluSyksbjvPqxyd4+MZJbl9nc0srf1uTRW19M6tWqD6NCS9c\nb/7k4ew66hhnX0Lfc3ll6H/56nHce8MkKsovOkPbEeDm9iDns4/bnzeZTJcCX7jekukJ7DpaxCfH\nipk7sZSpY2Ldti673c6LG45zqqiGBZOHs3jaCLetS/TOuEt99ou5a/lYrAEy9IUn8srGT7PJRFxU\nMNHhgUSFWYkICSA8OIDQIH9CAv0JDrQQZLVgDfAjwN8Pi58ZP7PZ+SUgge8uZpOJL68ch5/ZxEsb\nNA1NLW5b15bD59h+5DzJw8L4ko8Nc+2pzCYT8yYNp6GplQPSZ99jeWXoC8+VaAvl6jlJlFU38ta2\nk25ZR/65al758DghgRa+fvMkAjzszk2+bMFkxw1VpM++55LQFy53/bxRDIsK4sN9Zzh53rUDcVVf\nbOJvazJpbbPz8I2TiHXBDUGE68RFBTM2MYJjpyoorao3uhzRCQl94XL+Fj9WrRyH3Q7Pf5BDa5tr\nhmhobWvjqbezqKhp5JZFo5mY0vf7Ewv3ax+EbWeW9Nn3RBL6wi3GJ0exYPJwThfX8uFe19xe8Y3N\n+eScrmR6WizXzEl2yTKF62WMiyPAX/rseyoJfeE2dywbQ1iwP29ty6ekcmCH+ntziln3iWPkzAeu\nmyAnbj1YkNVC+tg4SiobOHG2yuhyxGUk9IXbhAb5c+cVaTS1tPHi+v7fXrGw9CLPvuccOfOWyQRZ\nvbKnsU9ZMMU5CNsArqcR7iGhL9xq9oRhTEqJJutkOXuyL/T59+sanCNnNrfylWvHkxAb4oYqhaup\npEhiwgPZm1Ps1q67ou96FfpKqdlKqc2dTL9eKbVXKbVLKfWgc5pJKVWolNrs/PdYV/OKoc9kMnHP\nCkWAxcyrH5+gtr73t1dss9v553vZXCivY+WsJI++H6/4LLPJxPzJ8TQ2t7JfS599T9Jj6CulfgA8\nAwReNt0feAK4ClgMPKSUigdSgQNa6yXOfz/uZl7hA2yRQdy0cDQ1dc28vjG317/3we5THDxRyrik\nSG5dMtqNFQp3mDfJ8RHfIX32PUpv9vTzgFs6mT4eyNVaV2itm4DtwEIgHUhQSm1SSr2vlFLdzCt8\nxJUzE0mKC2V75nmOFZT3OH/WyTIZOdPLxUUFM3ZkJDmnKwd8Il+4To9nxLTWbyilRnXyVDjQ8dR8\nDRABHAce01qvVkotAF4CvtPFvN2KigrGYun6akubLaynRXgkX63723fN4Ht/2spLH53gL99birWL\nK2kvlNfx9Nps/MxmfnLfLFKTB9Yf3xPfb2/etvtS29XzUjj+74Mczi/nzhXj3FjVp4bKe+cuA+kG\nUQ10fAVhQCWwD2gB0FpvV0ol4Aj5zubtVkVFXZfP2WxhlJTU9L1qg/ly3ZGBFq5IH8mH+87w3DuZ\n3LLo8zc7aWpu5bGXDlBT18yqlYroYP8Brbe7uo38AHrrtt3X2lRCGFZ/PzbsOcWy6SPcfpP6ofTe\nDXRdXRnIMfMxIE0pFa2UCgAWAbuA/wa+DaCUmgqcBrK7mFf4mJsXpRATbuWD3ac5W1L7mefsdjsv\nbTjOqQs1LJgynMVTZeRMbxcYYCFD2SitauDEmR7388Qg6HPoK6XuUko9pLVuBr4LrMcR4M9qrQuB\nXwOLlVJbgD8A93Yzr/AxgQEWvnSVorXNzvMffPb2ilsOnWN75nmS48O4R0bOHDLah2WQPvueoVfN\nO1rrAmCO8+dXOkxfC6y9bN4K4NpOlvG5eYVvmjomlpnj4tibU8zmg4Usm5FIXmEVL394nNAgf75+\n8yT8u2nvFt5lbFIksRGB7NMl3H1VC4EBcnGdkaRLhDDEXcvTCLJa+M/mPE4V1fDkW1m02e189caJ\nxEbIyJls1v+3AAAZ0klEQVRDiaPP/nAam1vZlyN99o0moS8MERFq5Y6lqTQ0tfK/L+z7dOTMUTJy\n5lAkffY9h4S+MMzCqSMYmxhBa5udGWNtMnLmEGaLDGJcUiT6TCXF0mffUBL6wjBmk4mv3jiJO5aO\n4f5rx8uJ2yHu0jj7srdvKAl9YaioMCsrZyfJyJk+IEPFYQ3wY0dm0Wd6bYnBJaEvhBgU1gA/Zqo4\nyqob0Kelz75RJPSFEINm/mQ5oWs0CX0hxKBJGxmJLTKQfbqY+kYZZ98IEvpCiEFjNpmYP2k4Tc1t\n7MspNrocnyShL4QYVPOkicdQEvpCiEEVGxHE+OQojp+t4kI3o40K95DQF0IMuk9P6BYZXInvkdAX\nQgy69LGOPvuHc0uNLsXnyBUxQohBZw3w45EbJ9Lc0mZ0KT5HQl8IYYgpqbFGl+CTpHlHCCF8iIS+\nEEL4EJNdBj4SQgifIXv6QgjhQyT0hRDCh0joCyGED5HQF0IIHyKhL4QQPkRCXwghfIiEvhBC+BAJ\nfSGE8CES+kII4UMk9IUQwodI6AshhA+R0BdCCB8ioS+EED5EQl8IIXyIhL4QQvgQCX0hhPAhEvpC\nCOFDJPSFEMKHSOgLIYQPsRhdQHdKSmq6vIFvVFQwFRV1g1mOS0jdg6u7um22MNMgl3OJt27bnlwb\neHZ9g1lbd9u21+7pWyx+RpfQL1L34PLGuj25Zk+uDTy7Pk+pzWtDXwghRN9J6AvhQTLzyyivbjC6\nDDGESegL4SHqG1v44+uH+T9/2kppZb3R5YghSkJfCA8RZLVwy+LRlFbW85tXD8oev3ALCX0hPMi1\nc0dx14pxlFY18JtXD1JR02h0SWKIkdAXwsN88cqxXDcvmeKKen776kGqLjYZXZIYQiT0hfAwJpOJ\nmxeOZuXsJIrK6/jdqweprpPgF64hoS+EBzKZTNy+JJXlGYkUll7kd68eora+2eiyxBDglaFfW99M\nUdlFo8sQwq1MJhN3XpHG0ukJnC2p5fevHaKuQYJfDMyAQl8pNVsptbmL54KVUjuUUuOcj81KqaeU\nUruUUpuVUmP6u96XNmi+9puNnDhb2d9FCOEVTCYTd181lkVTh3PqQg2///dh6htbjC5LeLF+h75S\n6gfAM0BgJ89lAFuB1A6TbwICtdZzgR8Bv+/vuhdOHUFrm52/vJFJsYeOsyGEq5hNJlatHMf8SfGc\nPF/NE68fpqFJgl/0z0D29POAW7p4zgrcDOR0mLYAWAegtd4NZPR3xRNHRfPILVOorW/mj6uPcFEO\necUQZzaZuO+a8cyZMIzcwir+uPoIjU2tRpclvJDJbu9ysL8eKaVGAa9pred08fxm4GGtdY5S6hng\nDa31B87nTgOjtdZd7rK0tLTauxuk6F9rj/Lm5lwmp8byi4fm4m/xylMUwjiGjbLZ07bdldbWNn77\n0n52HDnH1LRYfnb/HKz+njGQl/AoXW7bgzm0cjUQ1uGxubvAB7odhtRmC+Oa2SM5da6K/cdL+P2L\ne/nKteMxmQz7HPeKzRZGSUmN0WX02VCs22YL63T6YOhp2+7uvf7yirHU1Tdx8EQp//P3nXzz1sn4\nD9IIjp6+HXhyfYNZW3fb9mDuGu8ArgFQSs0BMge6QLPJxAPXTyBleBg7sop4d9epgS5SCI9n8TPz\n8I2TmJIaQ9bJcp5ck0VLa5vRZQkv4bLQV0rdpZR6qJtZ1gANSqmdwBPAd1yxXqu/H/916xRiwq2s\n2ZrP7uwiVyxWCI/mbzHz9ZsnMTElmsN5ZTz19lEJftErA2rTd7fu7i50+aHS2ZJaHntpP80tdr5/\n5zTSEiMHpca+8uTDz+4Mxbo99c5ZfXmvm5pb+dN/jnDsVAUzx8Xx0A0T8DO79gC+uLKezLwyjuSV\n0dJm55EbJxIa5O/SdbiKJ2+ng9y84xFt+m6VaAvlazdN5onXD/OXNzL56ap04qKCjS5LCLcKcB7p\nPvH6IfbmFOPnZ+KBaydgNvf/+6y5pY3jZysvBX1R+WfPP7y7s4AvXpE20NKFQYZM6ANMTInmnhVj\neX6d5o+rj/DoPekeu0cihKtYA/z41u1T+cPrh9h99AJ+Zkf3TnMfOjWUVzdwJL+MzLwysgsqaGx2\ndAe1+vsxPS2WyakxjE+O4onVR9h44CxXpCdiiwxy10syXFubneLKeoZFBXl855C+GlKhD7B4WgIX\nKupZt+c0T67J5LtfmIbFT7pyiqEtyGrhO7dP4/f/PsiOzCL8zGZWrVRdBn9Laxt5hVWXgv5syafD\nmsRHBzMlNYbJqTGMTYz8TFfoe64ez+9f3s+bW/P56g0T3f66BpPdbudMcS27jhaxO/sCVbVN3Hv1\nOBZNHWF0aS415EIf4LYlqZRU1rNfl/D8Bzle0ZVTiIEKDrTw3S9M47evHmTr4XNY/EzcfeXYS9t+\nVW0jmfnlHMkv4+jJ8kvDOfhbzEweHeMI+tHR3TaLLpqWwH8+Ps6e7AtcNXMkKcPDB+W1uVNZVQO7\ns4vYffQChaWOL7+QQAsmYFdWkYS+NzCbTDxw3QTKqw+yI6uIuOhgrp83yuiyhHC7kEB/vvfF6fzm\nlQNsPFBIW5udsOAAjuSXcaro05OIsRGBzJ04jCmpMaikqF5f4GU2m/jC0jH85tWDvL4xlx/cNd0r\nd6jqGprZp0vYlVWEPuMYw8viZyZD2Zg7MZ5Jo2P47WsHOX6mkqqLTUSEBBhcsesMydCH9q6ck/nf\nF/azZms+tshA5kyIN7osIdwuNMgZ/K8eZPOhcwD4mU2MT45iSqpjjz4+OrjfYT0uOYqpqTEczivj\ncG4Z09JiXVm+2zS3tJGZX8auo0Uczi2lpdXRgUqNjGTupHgylI3gwE/PAWaMtZF7toqDx0tYMj3B\nqLJdbsiGPkBEqJVv3z6FX720n2ffyyEmPNBju3IK4UrhIQF8/87pbNx/luT4MMYnRxFkdd3H/bal\nYziSX8bqzblMTo12eTdRV2mz28k9W8Xuo0XszSnmYoOjSWtEbAhzJw5jzoR4YiI+N2YkADOUjdc2\n5rJfF0voe5ME6copfFRESAA3LxrtlmUnxIawaOoIthw6x7bD5z0uFM+XXXSckD16gdIqxw3mI0ID\nWDFrJHMnxjMyLrTHI53YiCBGxYeRc7qS2vrmIdMTcMiHPkhXTiHc4aYFKew+eoG3tp9k9oRhLj2S\n6I+qi03sPJbHh3tOXTp/YQ3wY/6keOZMimd8UlSfr19IVzYKimo4dKKUBVOGu6PsQeeZx2RusHha\nwqV7jj65JlMuWRdigCJCraycnUT1xSbWf3La0FoKSy/y6D928czbWZy5UMuU1Bi+esNE/vjNBdx/\n3QQmjoru1wVrGSoOgH262NUlG8Yn9vTbSVdOIVxrxayRbD5YyLpPTrN4WgJRYdZBr6GxqZUn12RS\n39jKPVePJz0thvBg1/S2GRYdTKItlOwCRxdXo49mXMFn9vTh066cKcPDHaNy7iwwuiQhvFpggIUb\nF6bQ1NzG29vzB339drudF9ZrzpfVsTwjkTuWj3VZ4LfLUDZaWu0czi116XKN4lOhD5925YwJD2TN\ntpMyKqcQA7RwynCGxwSz7ch5CktqB3Xd246cZ9fRIlKGh3PH0n7fdrtb6coGwH5d4pblDzafC334\ntCtnkNWPZ987JjdYF2IA/Mxmbl86BrsdVm/OG7T1nimu5eUPjxMSaOGRGye6bbiVEbEhxEcHk5lf\nNiRuUemToQ+fduVsa0NusC7EAE1NjWFcUiRH8so4VlDu9vXVN7bw5JpMmlvauP+6CcS6cfA3k8lE\nurLR5Ly4y9v5bOjDp10522+wXlsvN1gXoj9MJhN3LHM0r7y+KY82N96nw2638/y6HC5U1LNydhLT\nxrj/iuCh1IvHp0MfHF05r5aunEIM2Kj4cOZMGMapCzXsyb7gtvVsOljIJ8eKGZMYwS1uuvjscknD\nQomNCORwXhnNLd7dxOPzoQ9w65JU0pWNnNOVPP9BDp58NzEhPNkti0Zj8TPx5pY8t4RjQVE1r318\ngtAgfx6+wX3t+JczmUxkqDgam1o5erJiUNbpLhL6fL4r5xtb8qlzjtEhhOi92MgglqePpKy6kY/2\nn3XpsusamnlyTRatrXYevH4C0eGdj5njLp/24vHuJh4JfServx//ddsUYsIDeX/3Kb7152389tWD\nrP/k9OduFyeE6Nq185IJCbTw7s5TLjtPZrfbefb9HEqrGrh2XjKTR8e4ZLl9kTIinKgwK4dyS726\nGVhCv4OIkAAevSedmxamkDQsjGOnKvj3xlwe/cdufvz3Xbz28QmOFZR79R9cCHcLCfTn+nmjqG9s\nYe2OApcs88N9ZzlwvIRxSZHcuCDFJcvsK7PJxIyxNi42tJBz2nubeLz/mmIXiwqzcsP8FG6Yn0JV\nbSNH8ss4kltGVkE5G/aeYcPeMwRZ/ZiYEsNU5y3lXH0FoBDebumMRD7af9Z5P92EAY1sm1dYxepN\nuYSHBPDQDRMNHcY5Q9n4eP9Z9uWUMCll8I82XEFCvxsRoVYWThnBwikjaG5p4/iZSg7nlnIot5R9\nOcXsyynGBIxOCGdqaixTx8SSaAuR8XyEz/O3mLl1cSp/f+cob2zJ55GbJvVrObX1zTz1dhZtbXa+\nev0EIkMHf2yfjtISIwkP9ufgiRJWrVD9GsTNaBL6veRvMTMxJZqJKdHcuTyN82V1HM4r5XBuGbln\nq8grrObNrflEh1udXwAxjEuKIqCXt6ETYqiZNT6ODXtPszenmKvOVZE6IqJPv99mt/PMu9mUVTdy\n08IUxo+KdlOlvWc2O5p4Nh86x/EzlYxLjjK6pD6T0O8Hk8nEiNgQRsSGcPXsZGrrm8k66WgGyswv\nY9PBQjYdLCTAYmZ8chRTx8QyJTVm0HsbCGEkk8nEHUvH8Pgrjvvp/ujuGX06Cl6/5zRH8sqYOCqK\n6+aOcl+hfZSu4th86Bz7dYmEvq8KDfJnzoR45kyIp7WtjbzCag7nljruIer8B5AUF8rsycMZERVE\nakKE3MhFDHkqKYppY2I5lFvKwROlzBhr69XvHT9TyRtb8okMDeDB6yd6VDOKSookJNDC/uPF3Hll\nGmYva86V0HcxP7OZsSMjGTsyktuXjqG4sp4jzi8AfbqC1R+fuDRvfHQwYxIiSE0IZ0xCBMNjQ7xu\nAxKiJ7cvTeVIXhmrN+cxJTWmxwuqquuaeOrtLAAevnES4SGe1VHC4mdmepqN7ZnnyT9XzZiEvjVb\nGU1C383iIoNYnjGS5RkjqW9soaS2iQPZReQWVpF/rprtmefZnnkegCCrhdQR4aQmRDAmIYLRI8KH\nxE0bhG8bHhPComkj2HywkG2Hz7F0RmKX87a12Xl6bTaVtU3ctiSVsSMjB7HS3ktXjtDfl1MsoS+6\nFmS1kJ4YRVKMo/taW5udwtKL5BVWkVtYRV5hFVkny8k66Ril0ASMsIU4jgZGRDAmMYJhUUHSO0h4\nnRsXpLDraBFvbz/JnInxXe7MvLurgKMny5mSGsPK2UmDW2QfTBgVTZDVj/26hC8sG+NVn0kJfQOZ\nzSZGxoUyMi6UJdMTAMehbX5hNXnnqsg9W8XJomoKSy6y5dA5wHH+oP1oIDUhgtHDw7EGSA8h4dki\nQgK4enYSb207yQd7Tnc6UNqxgnLe3n6S6HArD1w3waObOv0tZqamxrI7+wKnLtQwKj7c6JJ6TULf\nw4QHBzAtLZZpaY7hYlta2zhbUkteYfWlo4GOJ4fNJhOJcY6jgSmpMUxJdf8ws0L0x4qZSWw6WMiG\nT06zdPpn76dbVdvI39dmYzaZeOTGSV7RySFd2didfYH9ukRCX7iOxc/MqPhwRsWHc0W6oy20sraR\nvMKqS18EBUU1nL5Qy8YDhfz5Wwu94gMjfI81wI+bF47muQ9yWLMtn69cMx5wNHP+/Z2jVF9s4ovL\nxpDqJW3kk0bHEOBvZl9OMbcsGu01TTwS+l4oMtRKuooj3Xljh+aWNk5fqKG5pU0CX3i0BZOH8+He\nM+w4cp6rMkaSGBfKW9tPknO6kulpsVw5c6TRJfaa1d+PKaNj2KdLKCy5SGJcqNEl9YoMuDYE+FvM\npCZEeOWFIsK3mM0mbl+aih14fXMuWfllvLezgNiIQO6/drzX7C23S/fCO2pJ6AshBtXk0TGMT44i\nK7+cv72VhZ+fiUdumkRwoPcdpbZfd7D/eInRpfSahL4QYlC1D88A0NjUyheWpZEy3HtOhHYUZLUw\nKSWawpKLXnPfDQl9IcSgS44P487lady8MIVlMxKMLmdAvO2OWnIiVwhhiCszvOekbXempcXiZzax\nT5dwrQcNDNcV2dMXQogBCAn0Z3xyFKeKaiiprDe6nB4NaE9fKTUbeFxrveSy6dcDPwdagGe11k8r\npUzAWaB9xLFdWusfD2T9QgjhCdKVjayT5ezXJR49fAQMIPSVUj8A7gEuXjbdH3gCmOl8bodSai0Q\nChzQWl/f/3KFEMLzTB9r44X1mv3Hi4du6AN5wC3Ai5dNHw/kaq0rAJRS24GFOJqSEpRSm4B64Dta\na93dCqKigrFYuh5XxmYL63/1BpK6B5cn1u3N27Yn1wbG1GcDJo2OJTOvFHOAhZiIoM7n84D3rt+h\nr7V+Qyk1qpOnwoGqDo9rgAjgOPCY1nq1UmoB8BKOo4EuVVR03QXKZgujpKSmr2UbTuoeXN3VbeQH\n0Fu3bU+uDYytb8roaDLzSvlwV8GlIVM6Gszautu23XEitxrouMYwoBLYB7wNoLXejmOv37suvxNC\niC603xXM07tuuiP0jwFpSqlopVQAsAjYBfw38G0ApdRU4LTW2u6G9QshxKCLCrMyJiECfaaS6otN\nRpfTJZeFvlLqLqXUQ1rrZuC7wHocYf+s1roQ+DWwWCm1BfgDcK+r1i2EEJ4gXdmw2+HACc8dlmFA\nXTa11gXAHOfPr3SYvhZYe9m8FcC1A1mfEEJ4svSxNv69MZf9uoQl0zzzSmO5OEsIIVwkNjKI5Pgw\nck5VcLGh2ehyOiWhL4QQLpShbLS22Tl0otToUjoloS+EEC6U0T7Gfo5n9uKR0BdCCBcaFh1Moi2E\nowXl1De2GF3O50joCyGEi6WrOFpa7RzO87wmHgl9IYRwsU/H2Pe8rpsS+kII4WIJsSEMiw4mM7+M\nxuZWo8v5DAl9IYRwMZPJRIay0dTcRlZ+mdHlfIaEvhBCuMGlXjwe1sQjoS+EEG6QNCyU2IhADueW\n0tzSZnQ5l0joCyGEG5hMJtKVjYamVo4WlBtdziUS+kII4SbpziYeTxpuWUJfCCHcZPSIcKLCrBw6\nUUpLq2c08UjoCyGEm5hNJmaMtXGxoYXMXM+4UEtCXwgh3CjDeaHWjiPnDK7EQUJfCCHcKC0xkvBg\nf/ZkFXlEE4+EvhBCuJHZbCJ9XByVtY388rm96NMVxtZj6NqFEMIH3LoolatmJ3O25CKPv3KQf6w9\nSmVtoyG1DOh2iUIIIXoWHGjhm3dMY6aK5aUNx9l99AKHTpRy04IUlqUnYvEbvP1v2dMXQohBkjoi\ngp+tymDVCoWf2cRrG3P5xSA3+UjoCyHEIDKbTSyZnsCvHprDoqkjONfe5PPOUSpq3N/kI807Qghh\ngLDgAO69ehyLp43gxfWa3dkXOJhbyo3zU1ie4b4mH9nTF0IIA6UMD+enqzJYtVJhMZt4fVMu//Ov\nvRw75Z4mHwl9IYQwmNlsYsm0BB776lyWTBvB+dKL/PbVgzz1dpbLm3ykeUcIITxEaJA/q1aOY+HU\nEby04TifHCvmcG4ZN8wfxZUzR7qkyUf29IUQwsOkDA/nJ6vSuffqcfhbzKzenMd/P/sJ2S4YollC\nXwghPJDZZGLR1BH86qE5LJ2eQFFZHb977RBPvpVFeXVD/5frwhqFEEK4WGiQP/esUPz83pmkjghn\nX04xjz69m82HCvu1PAl9IYTwAsnxYfz4nnTuu2YcARY/Ptp3tl/LkRO5QgjhJcwmEwunjGD2+GG0\ntNr7tQwJfSGE8DIB/n4E+Pfvd6V5RwghfIiEvhBC+BCT3d6/diEhhBDeR/b0hRDCh0joCyGED5HQ\nF0IIHyKhL4QQPkRCXwghfIiEvhBC+BCvuyJXKWUGngSmAo3AA1rrXGOr6plSyh94FhgFWIH/1Vq/\nY2hRfaCUigP2A1dqrXOMrqc3lFI/Bm4AAoAntdb/NLikLnn6du0N268nb6OetC16457+TUCg1nou\n8CPg9wbX01tfAsq01guBq4G/GlxPrzk/8H8H6o2upbeUUkuAecB8YDEw0tCCeubp27VHb7+evI16\n2rbojaG/AFgHoLXeDWQYW06vrQZ+1uFxi1GF9MPvgKeAc0YX0gcrgExgDbAWeNfYcnrk6du1p2+/\nnryNetS26I2hHw5UdXjcqpTy+GYqrXWt1rpGKRUG/Af4qdE19YZS6l6gRGu93uha+igWR3DeDjwM\nvKyUMhlbUrc8erv25O3XC7ZRj9oWvTH0q4GwDo/NWmtP2+volFJqJLAJeFFr/YrR9fTSV4ArlVKb\ngWnAC0qpeGNL6pUyYL3WuklrrYEGwGZwTd3x+O3ag7dfT99GPWpb9Jg9iT7YAVwPvK6UmoPjsMnj\nKaWGARuAb2itPza6nt7SWi9q/9n5oXpYa11kXEW9th34llLqD8BwIATHh89TefR27cnbrxdsox61\nLXpj6K/B8a2+EzAB9xlcT289CkQBP1NKtbeNXq219rgTT0OB1vpdpdQi4BMcR7Rf11q3GlxWdzx9\nu5btt588bVuUUTaFEMKHeGObvhBCiH6S0BdCCB8ioS+EED5EQl8IIXyIhL4QQvgQCX0hhPAhEvpC\nCOFDJPSFEMKH/H/hwcGJgBR/5AAAAABJRU5ErkJggg==\n",
"text/plain": [
"<matplotlib.figure.Figure at 0x124749588>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row')\n",
"f.suptitle('ESG Sums Plotted Against Returns')\n",
"ax1.plot(emp_str_ann_roi)\n",
"ax2.plot(emp_con_ann_roi)\n",
"ax3.plot(env_str_ann_roi)\n",
"ax4.plot(env_con_ann_roi)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"OK, so that's our initial data aggregation step. The plots above zoom into the differences from bucket to bucket. Next time, we'll analyze what this data might mean."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.1"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment