ESG Portfolio Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bloomberg BQuant Webinar Series: <br> Improving Your Portfolio's ESG Score\n",
    "This is a companion notebook to the \"Seeing Green: Improving Your Portfolio's ESG Score\" webinar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bql\n",
    "import pandas as pd\n",
    "from collections import OrderedDict \n",
    "import numpy as np\n",
    "import bqport\n",
    "import bqviz as bqv\n",
    "from bqwidgets import DataGrid\n",
    "import heatmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "bq = bql.Service()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create, Save , And Load A Portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>Weights</th>\n",
       "      <th>Positions</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1COV GR Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.085080</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>1COV GR Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8TRA GR Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.124584</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>8TRA GR Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A2A IM Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.055466</td>\n",
       "      <td>3132.905216</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>A2A IM Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAK SS Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.047288</td>\n",
       "      <td>253.730928</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>AAK SS Equity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAL LN Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.279366</td>\n",
       "      <td>1270.522368</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>AAL LN Equity</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE   Weights    Positions     ORIG_IDS              ID\n",
       "ID                                                                           \n",
       "1COV GR Equity 2019-10-01  0.085080   183.000000  BE500 INDEX  1COV GR Equity\n",
       "8TRA GR Equity 2019-10-01  0.124584   500.000000  BE500 INDEX  8TRA GR Equity\n",
       "A2A IM Equity  2019-10-01  0.055466  3132.905216  BE500 INDEX   A2A IM Equity\n",
       "AAK SS Equity  2019-10-01  0.047288   253.730928  BE500 INDEX   AAK SS Equity\n",
       "AAL LN Equity  2019-10-01  0.279366  1270.522368  BE500 INDEX   AAL LN Equity"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req = bql.Request(bq.univ.members('BE500 Index').as_of('2019-10-01'),{'ID':bq.data.ID()})\n",
    "BE500_df = bq.execute(req)[0].df()\n",
    "BE500_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>Weights</th>\n",
       "      <th>Positions</th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>ID</th>\n",
       "      <th>Demo_Port_ Wght</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1COV GR Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.085080</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>1COV GR Equity</td>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8TRA GR Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.124584</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>8TRA GR Equity</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A2A IM Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.055466</td>\n",
       "      <td>3132.905216</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>A2A IM Equity</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAK SS Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.047288</td>\n",
       "      <td>253.730928</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>AAK SS Equity</td>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAL LN Equity</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>0.279366</td>\n",
       "      <td>1270.522368</td>\n",
       "      <td>BE500 INDEX</td>\n",
       "      <td>AAL LN Equity</td>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     DATE   Weights    Positions     ORIG_IDS              ID  \\\n",
       "ID                                                                              \n",
       "1COV GR Equity 2019-10-01  0.085080   183.000000  BE500 INDEX  1COV GR Equity   \n",
       "8TRA GR Equity 2019-10-01  0.124584   500.000000  BE500 INDEX  8TRA GR Equity   \n",
       "A2A IM Equity  2019-10-01  0.055466  3132.905216  BE500 INDEX   A2A IM Equity   \n",
       "AAK SS Equity  2019-10-01  0.047288   253.730928  BE500 INDEX   AAK SS Equity   \n",
       "AAL LN Equity  2019-10-01  0.279366  1270.522368  BE500 INDEX   AAL LN Equity   \n",
       "\n",
       "                Demo_Port_ Wght  \n",
       "ID                               \n",
       "1COV GR Equity         0.205761  \n",
       "8TRA GR Equity         0.000000  \n",
       "A2A IM Equity          0.000000  \n",
       "AAK SS Equity          0.205761  \n",
       "AAL LN Equity          0.205761  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(seed=1)\n",
    "BE500_df['Demo_Port_ Wght'] = np.random.choice([0,1,2],BE500_df.shape[0])\n",
    "BE500_df['Demo_Port_ Wght'] = BE500_df['Demo_Port_ Wght'] / BE500_df['Demo_Port_ Wght'].sum() * 100\n",
    "BE500_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>security</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019-10-01</th>\n",
       "      <th>1COV GR Equity</th>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAK SS Equity</th>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAL LN Equity</th>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABF LN Equity</th>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC FP Equity</th>\n",
       "      <td>0.205761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             weight\n",
       "date       security                \n",
       "2019-10-01 1COV GR Equity  0.205761\n",
       "           AAK SS Equity   0.205761\n",
       "           AAL LN Equity   0.205761\n",
       "           ABF LN Equity   0.205761\n",
       "           AC FP Equity    0.205761"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port_df = BE500_df.loc[:,['DATE','Demo_Port_ Wght']].reset_index() #idd \n",
    "port_df.columns =['security','date','weight']\n",
    "port_df = port_df.set_index(['date','security'])\n",
    "port_df.drop(port_df[port_df['weight'] == 0].index,inplace=True)\n",
    "port_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': 'PORTFOLIO:31:-11591334:18', 'name': 'jktest3'},\n",
       " {'id': 'PORTFOLIO:31:-11591334:19', 'name': 'SWPM deals'},\n",
       " {'id': 'PORTFOLIO:31:-11591334:10', 'name': 'JWJ_ETFS'},\n",
       " {'id': 'PORTFOLIO:31:-11591334:11', 'name': 'jk6'},\n",
       " {'id': 'PORTFOLIO:31:-11591334:12', 'name': 'Upload Test'}]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolios = bqport.list_portfolios()\n",
    "portfolios[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "port_name = \"Demo_ESG_Portfolio\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfoliio loaded\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    port = bqport.load_portfolio(name=port_name)\n",
    "    \n",
    "except:\n",
    "    print('Portfoliio not found \\n Creating porfolio')\n",
    "    new_port = bqport.new_portfolio(from_=port_df, type_=bqport.positions.PositionType.FIXED_WEIGHT,name=port_name)\n",
    "    new_port.save()\n",
    "    port = bqport.load_portfolio(name=port_name)\n",
    "\n",
    "print('Portfoliio loaded')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>weight</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>fx_rate</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>security</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019-10-01</th>\n",
       "      <th>UBSG SW Equity</th>\n",
       "      <td>0.411523</td>\n",
       "      <td>36846.653843</td>\n",
       "      <td>11.130</td>\n",
       "      <td>CHF</td>\n",
       "      <td>1.003462</td>\n",
       "      <td>411523.011665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US IM Equity</th>\n",
       "      <td>0.205761</td>\n",
       "      <td>77861.814639</td>\n",
       "      <td>2.421</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.091550</td>\n",
       "      <td>205760.944386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SMIN LN Equity</th>\n",
       "      <td>0.411523</td>\n",
       "      <td>21536.165596</td>\n",
       "      <td>1563.000</td>\n",
       "      <td>GBp</td>\n",
       "      <td>0.012226</td>\n",
       "      <td>411522.883471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMS SM Equity</th>\n",
       "      <td>0.205761</td>\n",
       "      <td>2912.600041</td>\n",
       "      <td>64.720</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.091550</td>\n",
       "      <td>205760.967774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HUH1V FH Equity</th>\n",
       "      <td>0.205761</td>\n",
       "      <td>5175.823451</td>\n",
       "      <td>36.420</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.091550</td>\n",
       "      <td>205760.984600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              weight      quantity     price currency  \\\n",
       "date       security                                                     \n",
       "2019-10-01 UBSG SW Equity   0.411523  36846.653843    11.130      CHF   \n",
       "           US IM Equity     0.205761  77861.814639     2.421      EUR   \n",
       "           SMIN LN Equity   0.411523  21536.165596  1563.000      GBp   \n",
       "           AMS SM Equity    0.205761   2912.600041    64.720      EUR   \n",
       "           HUH1V FH Equity  0.205761   5175.823451    36.420      EUR   \n",
       "\n",
       "                             fx_rate          value  \n",
       "date       security                                  \n",
       "2019-10-01 UBSG SW Equity   1.003462  411523.011665  \n",
       "           US IM Equity     1.091550  205760.944386  \n",
       "           SMIN LN Equity   0.012226  411522.883471  \n",
       "           AMS SM Equity    1.091550  205760.967774  \n",
       "           HUH1V FH Equity  1.091550  205760.984600  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port.to_dataframe().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PORTFOLIO:31:-11591334:56\n",
      "11591334-56\n"
     ]
    }
   ],
   "source": [
    "# test_fld = {'Wgt':bq.data.ID()['weights'].group(bq.data.gics_sector_name()).sum()}\n",
    "print(port.id)\n",
    "portfolio = port.id.split(':-')[1].replace(':','-')\n",
    "print(portfolio)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyse Portfolio And Benchmark ESG Characteristics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "esg_factors = OrderedDict()\n",
    "\n",
    "esg_factors['Tot CO2/Sales'] = bq.data.TOT_GHG_CO2_EM_INTENS_PER_SALES(fpt = 'A',fpo='-1',currency='USD')\n",
    "esg_factors['Fair ReNum'] = bq.data.FAIR_REMUNERATION_POLICY(fpt = 'A',fpo='-1').replacenonnumeric(replaceval = '0') * 100\n",
    "esg_factors['Ind Dir'] = bq.data.INDEPENDENT_LEAD_DIRECTOR(fpt = 'A',fpo='-1').replacenonnumeric(replaceval = '0') * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def esg_characteristic(factors, univ, grp):\n",
    "    \n",
    "    fld = OrderedDict()\n",
    "    cols = []\n",
    "    \n",
    "    wgts = bq.data.ID()['weights'].group(grp)\n",
    "    \n",
    "    for key,value in factors.items():\n",
    "        cols.append(key)\n",
    "        fld[key] = value.group(grp).wavg(wgts)\n",
    "    \n",
    "    fld['Weight'] = wgts.sum()\n",
    "    \n",
    "    req = bql.Request(univ,fld)\n",
    "    resp = bq.execute(req)\n",
    "    \n",
    "    dfs =[]\n",
    "    \n",
    "    for r in resp:\n",
    "        dfs.append(r.df())\n",
    "    \n",
    "    df = pd.concat(dfs,axis=1)\n",
    "    cols = ['Weight'] + cols\n",
    "    df = df.loc[:,cols].round(2)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Weight</th>\n",
       "      <th>Tot CO2/Sales</th>\n",
       "      <th>Fair ReNum</th>\n",
       "      <th>Ind Dir</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>7.41</td>\n",
       "      <td>28.30</td>\n",
       "      <td>11.11</td>\n",
       "      <td>41.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>11.93</td>\n",
       "      <td>51.15</td>\n",
       "      <td>5.17</td>\n",
       "      <td>36.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>10.29</td>\n",
       "      <td>73.40</td>\n",
       "      <td>14.00</td>\n",
       "      <td>38.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>3.29</td>\n",
       "      <td>408.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>37.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>19.14</td>\n",
       "      <td>5.68</td>\n",
       "      <td>7.53</td>\n",
       "      <td>48.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>5.56</td>\n",
       "      <td>34.99</td>\n",
       "      <td>3.70</td>\n",
       "      <td>14.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>19.34</td>\n",
       "      <td>80.11</td>\n",
       "      <td>3.19</td>\n",
       "      <td>32.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>4.12</td>\n",
       "      <td>33.82</td>\n",
       "      <td>15.00</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>8.02</td>\n",
       "      <td>955.02</td>\n",
       "      <td>2.56</td>\n",
       "      <td>41.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>4.32</td>\n",
       "      <td>107.77</td>\n",
       "      <td>19.05</td>\n",
       "      <td>42.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>6.58</td>\n",
       "      <td>650.21</td>\n",
       "      <td>6.25</td>\n",
       "      <td>34.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Weight  Tot CO2/Sales  Fair ReNum  Ind Dir\n",
       "ID                                                                \n",
       "Communication Services    7.41          28.30       11.11    41.67\n",
       "Consumer Discretionary   11.93          51.15        5.17    36.21\n",
       "Consumer Staples         10.29          73.40       14.00    38.00\n",
       "Energy                    3.29         408.70        0.00    37.50\n",
       "Financials               19.14           5.68        7.53    48.39\n",
       "Health Care               5.56          34.99        3.70    14.81\n",
       "Industrials              19.34          80.11        3.19    32.98\n",
       "Information Technology    4.12          33.82       15.00    15.00\n",
       "Materials                 8.02         955.02        2.56    41.03\n",
       "Real Estate               4.32         107.77       19.05    42.86\n",
       "Utilities                 6.58         650.21        6.25    34.38"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port_esg = esg_characteristic(esg_factors,port_univ,bq.data.gics_sector_name())\n",
    "port_esg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Weight</th>\n",
       "      <th>Tot CO2/Sales</th>\n",
       "      <th>Fair ReNum</th>\n",
       "      <th>Ind Dir</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>5.26</td>\n",
       "      <td>38.30</td>\n",
       "      <td>6.24</td>\n",
       "      <td>38.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>10.38</td>\n",
       "      <td>29.83</td>\n",
       "      <td>10.65</td>\n",
       "      <td>34.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>14.14</td>\n",
       "      <td>52.93</td>\n",
       "      <td>33.60</td>\n",
       "      <td>59.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>5.81</td>\n",
       "      <td>317.77</td>\n",
       "      <td>1.08</td>\n",
       "      <td>61.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>17.51</td>\n",
       "      <td>4.81</td>\n",
       "      <td>6.04</td>\n",
       "      <td>40.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>12.96</td>\n",
       "      <td>31.01</td>\n",
       "      <td>17.23</td>\n",
       "      <td>21.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>14.47</td>\n",
       "      <td>83.84</td>\n",
       "      <td>2.10</td>\n",
       "      <td>31.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>5.75</td>\n",
       "      <td>23.76</td>\n",
       "      <td>6.77</td>\n",
       "      <td>12.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>6.16</td>\n",
       "      <td>969.31</td>\n",
       "      <td>3.11</td>\n",
       "      <td>45.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>1.97</td>\n",
       "      <td>60.70</td>\n",
       "      <td>6.46</td>\n",
       "      <td>23.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>5.59</td>\n",
       "      <td>1121.02</td>\n",
       "      <td>2.88</td>\n",
       "      <td>28.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Weight  Tot CO2/Sales  Fair ReNum  Ind Dir\n",
       "ID                                                                \n",
       "Communication Services    5.26          38.30        6.24    38.52\n",
       "Consumer Discretionary   10.38          29.83       10.65    34.41\n",
       "Consumer Staples         14.14          52.93       33.60    59.00\n",
       "Energy                    5.81         317.77        1.08    61.34\n",
       "Financials               17.51           4.81        6.04    40.79\n",
       "Health Care              12.96          31.01       17.23    21.25\n",
       "Industrials              14.47          83.84        2.10    31.54\n",
       "Information Technology    5.75          23.76        6.77    12.27\n",
       "Materials                 6.16         969.31        3.11    45.62\n",
       "Real Estate               1.97          60.70        6.46    23.50\n",
       "Utilities                 5.59        1121.02        2.88    28.75"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bench = bq.univ.members('BE500 Index')\n",
    "bench_esg = esg_characteristic(esg_factors,bench,bq.data.gics_sector_name())\n",
    "bench_esg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Port Weight</th>\n",
       "      <th>Bench Weight</th>\n",
       "      <th>Weight Diff</th>\n",
       "      <th>Port Tot CO2/Sales</th>\n",
       "      <th>Bench Tot CO2/Sales</th>\n",
       "      <th>Tot CO2/Sales Diff</th>\n",
       "      <th>Port Fair ReNum</th>\n",
       "      <th>Bench Fair ReNum</th>\n",
       "      <th>Fair ReNum Diff</th>\n",
       "      <th>Port Ind Dir</th>\n",
       "      <th>Bench Ind Dir</th>\n",
       "      <th>Ind Dir Diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>7.41</td>\n",
       "      <td>5.26</td>\n",
       "      <td>2.15</td>\n",
       "      <td>28.30</td>\n",
       "      <td>38.30</td>\n",
       "      <td>-10.00</td>\n",
       "      <td>11.11</td>\n",
       "      <td>6.24</td>\n",
       "      <td>4.87</td>\n",
       "      <td>41.67</td>\n",
       "      <td>38.52</td>\n",
       "      <td>3.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>11.93</td>\n",
       "      <td>10.38</td>\n",
       "      <td>1.55</td>\n",
       "      <td>51.15</td>\n",
       "      <td>29.83</td>\n",
       "      <td>21.32</td>\n",
       "      <td>5.17</td>\n",
       "      <td>10.65</td>\n",
       "      <td>-5.48</td>\n",
       "      <td>36.21</td>\n",
       "      <td>34.41</td>\n",
       "      <td>1.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>10.29</td>\n",
       "      <td>14.14</td>\n",
       "      <td>-3.85</td>\n",
       "      <td>73.40</td>\n",
       "      <td>52.93</td>\n",
       "      <td>20.47</td>\n",
       "      <td>14.00</td>\n",
       "      <td>33.60</td>\n",
       "      <td>-19.60</td>\n",
       "      <td>38.00</td>\n",
       "      <td>59.00</td>\n",
       "      <td>-21.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>3.29</td>\n",
       "      <td>5.81</td>\n",
       "      <td>-2.52</td>\n",
       "      <td>408.70</td>\n",
       "      <td>317.77</td>\n",
       "      <td>90.93</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.08</td>\n",
       "      <td>-1.08</td>\n",
       "      <td>37.50</td>\n",
       "      <td>61.34</td>\n",
       "      <td>-23.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>19.14</td>\n",
       "      <td>17.51</td>\n",
       "      <td>1.63</td>\n",
       "      <td>5.68</td>\n",
       "      <td>4.81</td>\n",
       "      <td>0.87</td>\n",
       "      <td>7.53</td>\n",
       "      <td>6.04</td>\n",
       "      <td>1.49</td>\n",
       "      <td>48.39</td>\n",
       "      <td>40.79</td>\n",
       "      <td>7.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>5.56</td>\n",
       "      <td>12.96</td>\n",
       "      <td>-7.40</td>\n",
       "      <td>34.99</td>\n",
       "      <td>31.01</td>\n",
       "      <td>3.98</td>\n",
       "      <td>3.70</td>\n",
       "      <td>17.23</td>\n",
       "      <td>-13.53</td>\n",
       "      <td>14.81</td>\n",
       "      <td>21.25</td>\n",
       "      <td>-6.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>19.34</td>\n",
       "      <td>14.47</td>\n",
       "      <td>4.87</td>\n",
       "      <td>80.11</td>\n",
       "      <td>83.84</td>\n",
       "      <td>-3.73</td>\n",
       "      <td>3.19</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1.09</td>\n",
       "      <td>32.98</td>\n",
       "      <td>31.54</td>\n",
       "      <td>1.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>4.12</td>\n",
       "      <td>5.75</td>\n",
       "      <td>-1.63</td>\n",
       "      <td>33.82</td>\n",
       "      <td>23.76</td>\n",
       "      <td>10.06</td>\n",
       "      <td>15.00</td>\n",
       "      <td>6.77</td>\n",
       "      <td>8.23</td>\n",
       "      <td>15.00</td>\n",
       "      <td>12.27</td>\n",
       "      <td>2.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>8.02</td>\n",
       "      <td>6.16</td>\n",
       "      <td>1.86</td>\n",
       "      <td>955.02</td>\n",
       "      <td>969.31</td>\n",
       "      <td>-14.29</td>\n",
       "      <td>2.56</td>\n",
       "      <td>3.11</td>\n",
       "      <td>-0.55</td>\n",
       "      <td>41.03</td>\n",
       "      <td>45.62</td>\n",
       "      <td>-4.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>4.32</td>\n",
       "      <td>1.97</td>\n",
       "      <td>2.35</td>\n",
       "      <td>107.77</td>\n",
       "      <td>60.70</td>\n",
       "      <td>47.07</td>\n",
       "      <td>19.05</td>\n",
       "      <td>6.46</td>\n",
       "      <td>12.59</td>\n",
       "      <td>42.86</td>\n",
       "      <td>23.50</td>\n",
       "      <td>19.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>6.58</td>\n",
       "      <td>5.59</td>\n",
       "      <td>0.99</td>\n",
       "      <td>650.21</td>\n",
       "      <td>1121.02</td>\n",
       "      <td>-470.81</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2.88</td>\n",
       "      <td>3.37</td>\n",
       "      <td>34.38</td>\n",
       "      <td>28.75</td>\n",
       "      <td>5.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Port Weight  Bench Weight  Weight Diff  \\\n",
       "ID                                                               \n",
       "Communication Services         7.41          5.26         2.15   \n",
       "Consumer Discretionary        11.93         10.38         1.55   \n",
       "Consumer Staples              10.29         14.14        -3.85   \n",
       "Energy                         3.29          5.81        -2.52   \n",
       "Financials                    19.14         17.51         1.63   \n",
       "Health Care                    5.56         12.96        -7.40   \n",
       "Industrials                   19.34         14.47         4.87   \n",
       "Information Technology         4.12          5.75        -1.63   \n",
       "Materials                      8.02          6.16         1.86   \n",
       "Real Estate                    4.32          1.97         2.35   \n",
       "Utilities                      6.58          5.59         0.99   \n",
       "\n",
       "                        Port Tot CO2/Sales  Bench Tot CO2/Sales  \\\n",
       "ID                                                                \n",
       "Communication Services               28.30                38.30   \n",
       "Consumer Discretionary               51.15                29.83   \n",
       "Consumer Staples                     73.40                52.93   \n",
       "Energy                              408.70               317.77   \n",
       "Financials                            5.68                 4.81   \n",
       "Health Care                          34.99                31.01   \n",
       "Industrials                          80.11                83.84   \n",
       "Information Technology               33.82                23.76   \n",
       "Materials                           955.02               969.31   \n",
       "Real Estate                         107.77                60.70   \n",
       "Utilities                           650.21              1121.02   \n",
       "\n",
       "                        Tot CO2/Sales Diff  Port Fair ReNum  Bench Fair ReNum  \\\n",
       "ID                                                                              \n",
       "Communication Services              -10.00            11.11              6.24   \n",
       "Consumer Discretionary               21.32             5.17             10.65   \n",
       "Consumer Staples                     20.47            14.00             33.60   \n",
       "Energy                               90.93             0.00              1.08   \n",
       "Financials                            0.87             7.53              6.04   \n",
       "Health Care                           3.98             3.70             17.23   \n",
       "Industrials                          -3.73             3.19              2.10   \n",
       "Information Technology               10.06            15.00              6.77   \n",
       "Materials                           -14.29             2.56              3.11   \n",
       "Real Estate                          47.07            19.05              6.46   \n",
       "Utilities                          -470.81             6.25              2.88   \n",
       "\n",
       "                        Fair ReNum Diff  Port Ind Dir  Bench Ind Dir  \\\n",
       "ID                                                                     \n",
       "Communication Services             4.87         41.67          38.52   \n",
       "Consumer Discretionary            -5.48         36.21          34.41   \n",
       "Consumer Staples                 -19.60         38.00          59.00   \n",
       "Energy                            -1.08         37.50          61.34   \n",
       "Financials                         1.49         48.39          40.79   \n",
       "Health Care                      -13.53         14.81          21.25   \n",
       "Industrials                        1.09         32.98          31.54   \n",
       "Information Technology             8.23         15.00          12.27   \n",
       "Materials                         -0.55         41.03          45.62   \n",
       "Real Estate                       12.59         42.86          23.50   \n",
       "Utilities                          3.37         34.38          28.75   \n",
       "\n",
       "                        Ind Dir Diff  \n",
       "ID                                    \n",
       "Communication Services          3.15  \n",
       "Consumer Discretionary          1.80  \n",
       "Consumer Staples              -21.00  \n",
       "Energy                        -23.84  \n",
       "Financials                      7.60  \n",
       "Health Care                    -6.44  \n",
       "Industrials                     1.44  \n",
       "Information Technology          2.73  \n",
       "Materials                      -4.59  \n",
       "Real Estate                    19.36  \n",
       "Utilities                       5.63  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_esg = pd.concat([port_esg.add_prefix('Port '),bench_esg.add_prefix('Bench ')],axis=1)\n",
    "\n",
    "cols = port_esg.columns\n",
    "re_ordered_cols = []\n",
    "\n",
    "for c in cols:\n",
    "    comp_esg[c+' Diff'] = port_esg[c] - bench_esg[c]\n",
    "    re_ordered_cols = re_ordered_cols + ['Port '+c,'Bench '+c,c+' Diff'] \n",
    "\n",
    "comp_esg = comp_esg[re_ordered_cols]\n",
    "comp_esg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "37910de99f234213bfeefa0784fb7628",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "GridBox(children=(Figure(animation_duration=500, axes=[Axis(color='white', grid_color='#3c3c3c', grid_lines='d…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bqv.BarPlot(comp_esg[['Port Tot CO2/Sales','Bench Tot CO2/Sales','Tot CO2/Sales Diff']]).set_style().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Score An Investable Universe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "eqy_univ = bq.univ.equitiesuniv([\"ACTIVE\",\"PRIMARY\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "mkt_cap = bq.data.cur_mkt_cap(currency='USD') > 1000000000\n",
    "\n",
    "sector = bq.data.gics_sector_name().len() > 0\n",
    "\n",
    "west_eur = ['AD','AT','BE','CS','DK','FO','FI','FR','DE','GI','GR','GL','GG',\n",
    "            'VA','IS','IE','IM','IT','JE','LI','LU','MT','MC','NL','NO','PT','CH','SM','ZB','ES','SE','GB']\n",
    "\n",
    "country = bq.data.cntry_of_risk().in_(west_eur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "investable_univ = bq.univ.filter(eqy_univ,mkt_cap)\n",
    "investable_univ = bq.univ.filter(investable_univ,sector)\n",
    "investable_univ = bq.univ.filter(investable_univ,country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "invest_req = bql.Request(investable_univ,{'count':bq.data.ID().group(bq.data.gics_sector_name()).count()})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIG_IDS</th>\n",
       "      <th>GICS_SECTOR_NAME()</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>None</td>\n",
       "      <td>Communication Services</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>None</td>\n",
       "      <td>Consumer Staples</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>None</td>\n",
       "      <td>Energy</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>None</td>\n",
       "      <td>Financials</td>\n",
       "      <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>None</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>None</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>None</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>None</td>\n",
       "      <td>Materials</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>None</td>\n",
       "      <td>Real Estate</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>None</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       ORIG_IDS      GICS_SECTOR_NAME()  count\n",
       "ID                                                            \n",
       "Communication Services     None  Communication Services     80\n",
       "Consumer Discretionary     None  Consumer Discretionary    141\n",
       "Consumer Staples           None        Consumer Staples     83\n",
       "Energy                     None                  Energy     38\n",
       "Financials                 None              Financials    211\n",
       "Health Care                None             Health Care     96\n",
       "Industrials                None             Industrials    253\n",
       "Information Technology     None  Information Technology     82\n",
       "Materials                  None               Materials     92\n",
       "Real Estate                None             Real Estate    108\n",
       "Utilities                  None               Utilities     60"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.execute(invest_req)[0].df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "E_factors = OrderedDict()\n",
    "\n",
    "E_factors['Tot GHG'] = bq.data.TOT_GHG_CO2_EM_INTENS_PER_SALES(fpt = 'A',fpo='-1',currency='USD')\n",
    "E_factors['Energy Int'] = bq.data.ENERGY_INTENSITY_PER_SALES(fpt = 'A',fpo='-1',currency='USD')\n",
    "E_factors['Waste Gen'] = bq.data.WASTE_GENERATED_PER_SALES(fpt = 'A',fpo='-1',currency='USD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "def universe_stats(factors,univ,grp):\n",
    "    flds = OrderedDict()\n",
    "    \n",
    "    flds['Count'] = bq.data.ID().group(grp).count()\n",
    "    \n",
    "    for key,value in factors.items():\n",
    "        flds['Cov '+key] = value.group(grp).count() / flds['Count'] * 100\n",
    "        flds['Avg '+key] = value.group(grp).avg()\n",
    "        flds['Std '+key] = value.group(grp).std()\n",
    "        \n",
    "    req = bql.Request(univ,flds)\n",
    "    resp = bq.execute(req)\n",
    "    \n",
    "    dfs =[]\n",
    "\n",
    "    for r in resp:\n",
    "        dfs.append(r.df())\n",
    "\n",
    "    univ_df = pd.concat(dfs,axis=1)\n",
    "\n",
    "    univ_df = univ_df.loc[:,[k for k in flds.keys()]]\n",
    "    \n",
    "    return univ_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "univ_stats_df = universe_stats(E_factors,investable_univ,bq.data.gics_sector_name())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Count</th>\n",
       "      <th>Cov Tot GHG</th>\n",
       "      <th>Avg Tot GHG</th>\n",
       "      <th>Std Tot GHG</th>\n",
       "      <th>Cov Energy Int</th>\n",
       "      <th>Avg Energy Int</th>\n",
       "      <th>Std Energy Int</th>\n",
       "      <th>Cov Waste Gen</th>\n",
       "      <th>Avg Waste Gen</th>\n",
       "      <th>Std Waste Gen</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>80</td>\n",
       "      <td>63.75</td>\n",
       "      <td>26.92</td>\n",
       "      <td>35.26</td>\n",
       "      <td>52.50</td>\n",
       "      <td>93.94</td>\n",
       "      <td>100.07</td>\n",
       "      <td>38.75</td>\n",
       "      <td>1.51</td>\n",
       "      <td>3.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>141</td>\n",
       "      <td>67.38</td>\n",
       "      <td>42.53</td>\n",
       "      <td>79.00</td>\n",
       "      <td>48.94</td>\n",
       "      <td>173.56</td>\n",
       "      <td>305.81</td>\n",
       "      <td>36.88</td>\n",
       "      <td>16.48</td>\n",
       "      <td>26.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>83</td>\n",
       "      <td>60.24</td>\n",
       "      <td>75.54</td>\n",
       "      <td>131.12</td>\n",
       "      <td>50.60</td>\n",
       "      <td>273.46</td>\n",
       "      <td>411.95</td>\n",
       "      <td>44.58</td>\n",
       "      <td>23.28</td>\n",
       "      <td>33.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>38</td>\n",
       "      <td>68.42</td>\n",
       "      <td>649.73</td>\n",
       "      <td>1045.01</td>\n",
       "      <td>55.26</td>\n",
       "      <td>1833.18</td>\n",
       "      <td>1986.41</td>\n",
       "      <td>50.00</td>\n",
       "      <td>23.78</td>\n",
       "      <td>39.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>211</td>\n",
       "      <td>51.66</td>\n",
       "      <td>5.34</td>\n",
       "      <td>17.68</td>\n",
       "      <td>41.71</td>\n",
       "      <td>24.38</td>\n",
       "      <td>69.69</td>\n",
       "      <td>27.96</td>\n",
       "      <td>7.03</td>\n",
       "      <td>37.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>96</td>\n",
       "      <td>41.67</td>\n",
       "      <td>47.00</td>\n",
       "      <td>68.31</td>\n",
       "      <td>35.42</td>\n",
       "      <td>117.59</td>\n",
       "      <td>133.65</td>\n",
       "      <td>34.38</td>\n",
       "      <td>8.14</td>\n",
       "      <td>12.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>253</td>\n",
       "      <td>67.19</td>\n",
       "      <td>156.18</td>\n",
       "      <td>595.41</td>\n",
       "      <td>53.36</td>\n",
       "      <td>359.69</td>\n",
       "      <td>1080.74</td>\n",
       "      <td>37.94</td>\n",
       "      <td>306.00</td>\n",
       "      <td>2487.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>82</td>\n",
       "      <td>40.24</td>\n",
       "      <td>21.81</td>\n",
       "      <td>39.88</td>\n",
       "      <td>30.49</td>\n",
       "      <td>47.51</td>\n",
       "      <td>61.88</td>\n",
       "      <td>18.29</td>\n",
       "      <td>1.38</td>\n",
       "      <td>1.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>92</td>\n",
       "      <td>70.65</td>\n",
       "      <td>894.04</td>\n",
       "      <td>1525.76</td>\n",
       "      <td>65.22</td>\n",
       "      <td>2988.90</td>\n",
       "      <td>2980.45</td>\n",
       "      <td>63.04</td>\n",
       "      <td>1218.17</td>\n",
       "      <td>4344.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>108</td>\n",
       "      <td>43.52</td>\n",
       "      <td>51.70</td>\n",
       "      <td>77.33</td>\n",
       "      <td>37.04</td>\n",
       "      <td>8398.23</td>\n",
       "      <td>50466.10</td>\n",
       "      <td>22.22</td>\n",
       "      <td>23.98</td>\n",
       "      <td>29.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>60</td>\n",
       "      <td>68.33</td>\n",
       "      <td>945.31</td>\n",
       "      <td>1415.74</td>\n",
       "      <td>56.67</td>\n",
       "      <td>4090.38</td>\n",
       "      <td>6589.33</td>\n",
       "      <td>51.67</td>\n",
       "      <td>232.13</td>\n",
       "      <td>609.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Count  Cov Tot GHG  Avg Tot GHG  Std Tot GHG  \\\n",
       "ID                                                                     \n",
       "Communication Services     80        63.75        26.92        35.26   \n",
       "Consumer Discretionary    141        67.38        42.53        79.00   \n",
       "Consumer Staples           83        60.24        75.54       131.12   \n",
       "Energy                     38        68.42       649.73      1045.01   \n",
       "Financials                211        51.66         5.34        17.68   \n",
       "Health Care                96        41.67        47.00        68.31   \n",
       "Industrials               253        67.19       156.18       595.41   \n",
       "Information Technology     82        40.24        21.81        39.88   \n",
       "Materials                  92        70.65       894.04      1525.76   \n",
       "Real Estate               108        43.52        51.70        77.33   \n",
       "Utilities                  60        68.33       945.31      1415.74   \n",
       "\n",
       "                        Cov Energy Int  Avg Energy Int  Std Energy Int  \\\n",
       "ID                                                                       \n",
       "Communication Services           52.50           93.94          100.07   \n",
       "Consumer Discretionary           48.94          173.56          305.81   \n",
       "Consumer Staples                 50.60          273.46          411.95   \n",
       "Energy                           55.26         1833.18         1986.41   \n",
       "Financials                       41.71           24.38           69.69   \n",
       "Health Care                      35.42          117.59          133.65   \n",
       "Industrials                      53.36          359.69         1080.74   \n",
       "Information Technology           30.49           47.51           61.88   \n",
       "Materials                        65.22         2988.90         2980.45   \n",
       "Real Estate                      37.04         8398.23        50466.10   \n",
       "Utilities                        56.67         4090.38         6589.33   \n",
       "\n",
       "                        Cov Waste Gen  Avg Waste Gen  Std Waste Gen  \n",
       "ID                                                                   \n",
       "Communication Services          38.75           1.51           3.64  \n",
       "Consumer Discretionary          36.88          16.48          26.55  \n",
       "Consumer Staples                44.58          23.28          33.03  \n",
       "Energy                          50.00          23.78          39.11  \n",
       "Financials                      27.96           7.03          37.87  \n",
       "Health Care                     34.38           8.14          12.73  \n",
       "Industrials                     37.94         306.00        2487.88  \n",
       "Information Technology          18.29           1.38           1.65  \n",
       "Materials                       63.04        1218.17        4344.12  \n",
       "Real Estate                     22.22          23.98          29.83  \n",
       "Utilities                       51.67         232.13         609.06  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "univ_stats_df.round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Score A Portfolio And Benchmark Based On An Investable Universe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "def port_bench_comp(port,bench,factors,grp,univ_stats):\n",
    "    \n",
    "    univs = OrderedDict()\n",
    "    univs = {'Port':port,'Bench':bench}\n",
    "    \n",
    "    port_bench =[]\n",
    "    \n",
    "    for univ_name,univ in univs.items():\n",
    "        \n",
    "        flds = OrderedDict()\n",
    "        wgts = bq.data.ID()['weights'].group(grp)\n",
    "        \n",
    "        for name, factor in factors.items():\n",
    "            flds[name] = factor.group(grp).wavg(wgts)\n",
    "            \n",
    "        req = bql.Request(univ,flds)\n",
    "        resp = bq.execute(req)\n",
    "\n",
    "        dfs =[]\n",
    "\n",
    "        for r in resp:\n",
    "            dfs.append(r.df())\n",
    "\n",
    "        univ_df = pd.concat(dfs,axis=1)\n",
    "        univ_df = univ_df.loc[:,[k for k in factors.keys()]]\n",
    "        \n",
    "        for c in univ_df.columns:\n",
    "            univ_df[c] = (univ_df[c] - univ_stats[\"Avg \"+c]) / univ_stats[\"Std \"+c]\n",
    "        \n",
    "        univ_df['Avg'] = univ_df.mean(axis=1) \n",
    "        univ_df = univ_df.add_prefix(univ_name+\" \")\n",
    "        \n",
    "        port_bench.append(univ_df)\n",
    "        \n",
    "    comp_df = pd.concat(port_bench,axis=1)\n",
    "    \n",
    "    re_ordered_cols=[]\n",
    "   \n",
    "    for k in factors.keys():\n",
    "            re_ordered_cols.append('Port ' + k)\n",
    "            re_ordered_cols.append('Bench ' + k)\n",
    "    \n",
    "    re_ordered_cols.append('Port Avg')\n",
    "    re_ordered_cols.append('Bench Avg')\n",
    "    \n",
    "    return comp_df[re_ordered_cols]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Port Tot GHG</th>\n",
       "      <th>Bench Tot GHG</th>\n",
       "      <th>Port Energy Int</th>\n",
       "      <th>Bench Energy Int</th>\n",
       "      <th>Port Waste Gen</th>\n",
       "      <th>Bench Waste Gen</th>\n",
       "      <th>Port Avg</th>\n",
       "      <th>Bench Avg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>-0.208440</td>\n",
       "      <td>0.124125</td>\n",
       "      <td>-0.169600</td>\n",
       "      <td>0.224074</td>\n",
       "      <td>-0.272187</td>\n",
       "      <td>-0.156856</td>\n",
       "      <td>-0.216742</td>\n",
       "      <td>0.063781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>-0.230647</td>\n",
       "      <td>-0.317666</td>\n",
       "      <td>-0.257044</td>\n",
       "      <td>-0.355982</td>\n",
       "      <td>-0.144687</td>\n",
       "      <td>-0.208586</td>\n",
       "      <td>-0.210793</td>\n",
       "      <td>-0.294078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>-0.016273</td>\n",
       "      <td>-0.172423</td>\n",
       "      <td>-0.246688</td>\n",
       "      <td>-0.110067</td>\n",
       "      <td>-0.208710</td>\n",
       "      <td>-0.076594</td>\n",
       "      <td>-0.157224</td>\n",
       "      <td>-0.119694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>-0.127753</td>\n",
       "      <td>-0.121496</td>\n",
       "      <td>-0.162996</td>\n",
       "      <td>-0.179709</td>\n",
       "      <td>-0.111824</td>\n",
       "      <td>-0.109766</td>\n",
       "      <td>-0.134191</td>\n",
       "      <td>-0.136990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Communication Services</th>\n",
       "      <td>0.039162</td>\n",
       "      <td>0.322883</td>\n",
       "      <td>-0.142556</td>\n",
       "      <td>0.228755</td>\n",
       "      <td>-0.255989</td>\n",
       "      <td>-0.207513</td>\n",
       "      <td>-0.119794</td>\n",
       "      <td>0.114708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>0.109001</td>\n",
       "      <td>-0.160839</td>\n",
       "      <td>0.004828</td>\n",
       "      <td>-0.220195</td>\n",
       "      <td>-0.319787</td>\n",
       "      <td>-0.415612</td>\n",
       "      <td>-0.068653</td>\n",
       "      <td>-0.265548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>-0.175834</td>\n",
       "      <td>-0.234080</td>\n",
       "      <td>0.094121</td>\n",
       "      <td>-0.194249</td>\n",
       "      <td>0.058579</td>\n",
       "      <td>-0.158299</td>\n",
       "      <td>-0.007711</td>\n",
       "      <td>-0.195543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>0.019423</td>\n",
       "      <td>-0.030034</td>\n",
       "      <td>-0.005610</td>\n",
       "      <td>-0.115278</td>\n",
       "      <td>0.014554</td>\n",
       "      <td>-0.133906</td>\n",
       "      <td>0.009456</td>\n",
       "      <td>-0.093073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>0.039970</td>\n",
       "      <td>0.049333</td>\n",
       "      <td>0.076888</td>\n",
       "      <td>-0.064969</td>\n",
       "      <td>0.004596</td>\n",
       "      <td>0.857592</td>\n",
       "      <td>0.040485</td>\n",
       "      <td>0.280652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>0.300966</td>\n",
       "      <td>0.048880</td>\n",
       "      <td>0.334393</td>\n",
       "      <td>0.079865</td>\n",
       "      <td>0.030572</td>\n",
       "      <td>-0.244155</td>\n",
       "      <td>0.221977</td>\n",
       "      <td>-0.038470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>0.725136</td>\n",
       "      <td>0.116368</td>\n",
       "      <td>-0.157092</td>\n",
       "      <td>-0.158963</td>\n",
       "      <td>0.121132</td>\n",
       "      <td>0.251142</td>\n",
       "      <td>0.229725</td>\n",
       "      <td>0.069516</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Port Tot GHG  Bench Tot GHG  Port Energy Int  \\\n",
       "ID                                                                     \n",
       "Utilities                  -0.208440       0.124125        -0.169600   \n",
       "Energy                     -0.230647      -0.317666        -0.257044   \n",
       "Consumer Staples           -0.016273      -0.172423        -0.246688   \n",
       "Industrials                -0.127753      -0.121496        -0.162996   \n",
       "Communication Services      0.039162       0.322883        -0.142556   \n",
       "Consumer Discretionary      0.109001      -0.160839         0.004828   \n",
       "Health Care                -0.175834      -0.234080         0.094121   \n",
       "Financials                  0.019423      -0.030034        -0.005610   \n",
       "Materials                   0.039970       0.049333         0.076888   \n",
       "Information Technology      0.300966       0.048880         0.334393   \n",
       "Real Estate                 0.725136       0.116368        -0.157092   \n",
       "\n",
       "                        Bench Energy Int  Port Waste Gen  Bench Waste Gen  \\\n",
       "ID                                                                          \n",
       "Utilities                       0.224074       -0.272187        -0.156856   \n",
       "Energy                         -0.355982       -0.144687        -0.208586   \n",
       "Consumer Staples               -0.110067       -0.208710        -0.076594   \n",
       "Industrials                    -0.179709       -0.111824        -0.109766   \n",
       "Communication Services          0.228755       -0.255989        -0.207513   \n",
       "Consumer Discretionary         -0.220195       -0.319787        -0.415612   \n",
       "Health Care                    -0.194249        0.058579        -0.158299   \n",
       "Financials                     -0.115278        0.014554        -0.133906   \n",
       "Materials                      -0.064969        0.004596         0.857592   \n",
       "Information Technology          0.079865        0.030572        -0.244155   \n",
       "Real Estate                    -0.158963        0.121132         0.251142   \n",
       "\n",
       "                        Port Avg  Bench Avg  \n",
       "ID                                           \n",
       "Utilities              -0.216742   0.063781  \n",
       "Energy                 -0.210793  -0.294078  \n",
       "Consumer Staples       -0.157224  -0.119694  \n",
       "Industrials            -0.134191  -0.136990  \n",
       "Communication Services -0.119794   0.114708  \n",
       "Consumer Discretionary -0.068653  -0.265548  \n",
       "Health Care            -0.007711  -0.195543  \n",
       "Financials              0.009456  -0.093073  \n",
       "Materials               0.040485   0.280652  \n",
       "Information Technology  0.221977  -0.038470  \n",
       "Real Estate             0.229725   0.069516  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_score = port_bench_comp(port_univ,bench,E_factors,bq.data.gics_sector_name(),univ_stats_df)\n",
    "comp_score.sort_values('Port Avg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "056deddc30b3490bbdcec3eaaf7e7683",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Figure(axes=[Axis(grid_lines='none', scale=OrdinalScale(), side='top'), Axis(grid_lines='none', orientation='v…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "heatmap.plot_heat_map(comp_score)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Screen For Securities That Would Improve The Porfolio's ESG Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "sector = bq.data.gics_sector_name() == 'Real Estate'\n",
    "\n",
    "sector_univ = bq.univ.filter(eqy_univ,mkt_cap)\n",
    "sector_univ = bq.univ.filter(sector_univ,sector)\n",
    "sector_univ = bq.univ.filter(sector_univ,country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "flds = E_factors\n",
    "flds['e_score'] = ((bq.data.TOT_GHG_CO2_EM_INTENS_PER_SALES(fpt = 'A',fpo='-1',currency='USD').groupzscore(bq.data.gics_sector_name()) / 3) + \n",
    "                    (bq.data.ENERGY_INTENSITY_PER_SALES(fpt = 'A',fpo='-1',currency='USD').groupzscore(bq.data.gics_sector_name()) / 3) +\n",
    "                    (bq.data.WASTE_GENERATED_PER_SALES(fpt = 'A',fpo='-1',currency='USD').groupzscore(bq.data.gics_sector_name())) / 3) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "req = bql.Request(sector_univ,flds)\n",
    "resp = bq.execute(req)\n",
    "\n",
    "dfs =[]\n",
    "\n",
    "for r in resp:\n",
    "    dfs.append(r.df())\n",
    "    \n",
    "sector_df = pd.concat(dfs,axis=1)\n",
    "sector_df = sector_df.loc[:,[k for k in flds.keys()]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1d41d9fd53b1430d8ebdbd77c1e80aba",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "DataGrid(column_defs=[{'width': 200, 'field': 'ID', 'headerName': 'Security'}, {'width': 100, 'field': 'Tot GH…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "format_df = sector_df.dropna().sort_values('e_score').reset_index().round(2)\n",
    "cols = format_df.columns\n",
    "dgrid_col_width = 100\n",
    "\n",
    "DataGrid(data=format_df,\n",
    "          column_defs = [{'headerName': 'Security', 'field': cols[0], 'width': 200},\n",
    "                         {'headerName': cols[1], 'field' : cols[1], 'width' : dgrid_col_width},\n",
    "                         {'headerName': cols[2], 'field' : cols[2], 'width' : dgrid_col_width},\n",
    "                         {'headerName': cols[3], 'field' : cols[3], 'width' : dgrid_col_width},\n",
    "                         {'headerName': cols[4], 'field' : cols[4], 'width' : dgrid_col_width}],\n",
    "         layout = {'width':'650px','height':'500px'})\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (sandboxed)",
   "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}