{"cells":[{"metadata":{},"cell_type":"markdown","source":"# About this Notebook"},{"metadata":{},"cell_type":"markdown","source":"### In this notebook, I propose new KPI (Key Performance Indicator) system \"FRoG\", ---Functional Regressive optimized Goals (of CO2-reduction)---. which measures how the porformance of corporations and cities are from 2 aspects, socially and environmentally, and find the best effective point that every cities and companies need to target, by quantitative measuraments.\n"},{"metadata":{},"cell_type":"markdown","source":"# Agenda\n## ・ Data Cleaning and EDA\n## ・ Explanations of KPI (FRoG)\n## ・ Implementation Example of FRoG\n## ・ Comparison to past KPIs\n## ・ Conclusion\n## ・ Reference"},{"metadata":{},"cell_type":"markdown","source":"# Data Cleaning & EDA (Exploratory Data Analysis)"},{"metadata":{},"cell_type":"markdown","source":"### At first, I'll show and clean current tables in order to make the most of existing data, because some of the corporations/cities response include NaN(Not a Number), and we need to extract/devide numerical responses and text responses to quantitatively measure how impacts of both cities and corporations have changed in recent years, socially and environmentally. \n![](https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F1314380%2F6f0f4d334e5b094bfcf002c4d2e931f6%2FCDP_dataset.png?generation=1603468553539656&alt=media)\n### Except supplementaly data, data mainly consists of Corporations/Cities Disclosure, Questionnaires, and Responses. At first let's see current situation of both Corporations and Cities from these data."},{"metadata":{},"cell_type":"markdown","source":"# Response rate"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\nimport warnings\n\nwarnings.filterwarnings('ignore')\n\nimport numpy as np\nimport pandas as pd\nimport os\nimport matplotlib.pyplot as plt\n\ndata = {}\n\nfor year in range(2018,2021):\n    \n    data[year] = []\n    \n    # Corporations ---Climate ChangeI’m \n    \n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/{year}_Corporates_Disclosing_to_CDP_Climate_Change.csv'))\n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/{year}_Full_Climate_Change_Dataset.csv'))\n    \n    # Corporations ---Water Security\n    \n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/{year}_Corporates_Disclosing_to_CDP_Water_Security.csv'))\n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/{year}_Full_Water_Security_Dataset.csv'))\n    \n    # Cities\n    \n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/{year}_Cities_Disclosing_to_CDP.csv'))\n    data[year].append(pd.read_csv(f'../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/{year}_Full_Cities_Dataset.csv'))\n    \n    \n\nanswering_rate = {'Corporations Climate Change':[], 'Corporations Water Security':[], 'Cityies':[]}\n\nfor year in range(2018,2021):\n    \n    print('year', year)\n    print()\n\n    # Corporations ---Climate Change\n\n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n\n    # Corporations ---Water Security\n\n    Corporations_disclosing_Water = data[year][2]\n    Corporations_response_Water = data[year][3]\n\n    # Cities\n\n    Cities_disclosing = data[year][4]\n    Cities_response = data[year][5]\n    \n    n_answered_account = []\n    n_total_account = []\n    \n    n_answered_account.append(len(np.unique(Corporations_response_Climate['account_number'].values)))\n    n_total_account.append(len(np.unique(Corporations_disclosing_Climate['account_number'].values)))\n    \n    n_answered_account.append(len(np.unique(Corporations_response_Water['account_number'].values)))\n    n_total_account.append(len(np.unique(Corporations_disclosing_Water['account_number'].values)))\n    \n    n_answered_account.append(len(np.unique(Cities_response['Account Number'].values)))\n    n_total_account.append(len(np.unique(Cities_disclosing['Account Number'].values)))\n    \n    \n    for n, name in enumerate(['Corporations Climate Change', 'Corporations Water Security', 'Cityies']):\n        \n        answering_rate[name].append(100*n_answered_account[n]/n_total_account[n])\n        print(n_answered_account[n], '/', n_total_account[n], 'accounts of', name, 'responded')\n    \n    print()\n    \nimport matplotlib.pyplot as plt\n\nfig = plt.figure(figsize=(9,6))\n\nfor name in answering_rate.keys():\n    \n    plt.plot(np.array(list(range(2018,2021))).astype(str), answering_rate[name], label=name)\n    \nplt.legend()\nplt.xlabel('year')\nplt.ylabel('%')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### We can see response rate of Corporations are always 100% for all years, and response rate of Cities increased every year, became 100% at 2020.\n### Nextly, I'll show and create clean dataframe for each Climate Change, Water Security, and City. In order to quantitatively compare how impact of organizations have changed, clean dataframe extracts only organizations included for all 2018-2020."},{"metadata":{},"cell_type":"markdown","source":"# Climate Change"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\nsets = {}\nsets_qname = {}\n\nprint('Climate Change')\n\nprint()\n\nprint('number of questions')\n\nprint()\n\nfor year in range(2018, 2021):\n    \n    #Corporations ---Climate Change\n\n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    \n    vc = Corporations_response_Climate.account_number.value_counts()\n    \n    longest = 0\n    \n    a = []\n    for i in range(len(vc)):\n        an = vc.index[i]\n        v = len(np.unique(Corporations_response_Climate[Corporations_response_Climate.account_number==an].question_number.value_counts().index))\n        a.append(v)\n        \n        if v>longest:\n            sets[year] = set(Corporations_response_Climate[Corporations_response_Climate.account_number==an].question_number.values)\n            sets_qname[year] = set(Corporations_response_Climate[Corporations_response_Climate.account_number==an].question_unique_reference.values)\n            longest = v\n        \n    print(year, max(a))\n    \nduplicate = sets[2018]&sets[2019]&sets[2020]\nduplicate_questions = sets_qname[2018]&sets_qname[2019]&sets_qname[2020]\n\nprint()\nprint(len(duplicate_questions), ' questions are included for all 2018-2020.')\n\nprint()\nprint('number of questions')\nprint()\n\nfrom tqdm import tqdm\n\nsets_an = []\nfor year in range(2018, 2021):\n    \n    #Corporations ---Climate Change\n    \n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    \n    print(year, Corporations_response_Climate.account_number.value_counts().shape[0])\n    \n    sets_an.append(set(Corporations_response_Climate.account_number.value_counts().index.values))\n    \nprint()\nprint(len(sets_an[0]&sets_an[1]&sets_an[2]), 'of account_number are contained for all 2018-2020')\n\nprint()\nprint('unique questions')\nprint()\n\nfrom textblob import TextBlob\n\nfor n, q in enumerate(np.unique(list(duplicate_questions))):\n    \n    blob = TextBlob(q)\n    print('Question', n, q)\n    #print(blob.translate(to='ja'))\n    \n    #print(Corporations_response_Climate[Corporations_response_Climate['question_unique_reference']==q].response_value.value_counts().shape[0])\n    \n\ndf_climate = pd.DataFrame()\ndf_climate['account_number'] = list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2])))*3\n\n# CO2 per revenue\n\nq = 'Describe your gross global combined Scope 1 and 2 emissions for the reporting year in metric tons CO2e per unit currency total revenue and provide any additional intensity metrics that are appropriate to your business operations.'\nvalue_q = []\n\nfor year in range(2018,2021):\n    \n    #Corporations ---Climate Change\n    \n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    \n    #print(year)\n    for an in list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2]))):\n        if year!=2020:\n            value_q.append(float(Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)&(Corporations_response_Climate['table_columns_unique_reference']=='C6.10_c2-Metric numerator (Gross global combined Scope 1 and 2 emissions)')]['response_value'].values[0]))\n        else:\n            value_q.append(float(Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)&(Corporations_response_Climate['table_columns_unique_reference']=='C6.10_c2-Metric numerator (Gross global combined Scope 1 and 2 emissions, metric tons CO2e)')]['response_value'].values[0]))\n        \ndf_climate['CO2/revenue'] = value_q\n\n# Scope 1: CO2 when fuels are burned\n\nq = 'What were your organization’s gross global Scope 1 emissions in metric tons CO2e?'\nvalue_q = []\nvalue_year = []\n\nfor year in range(2018,2021):\n    \n    #Corporations ---Climate Change\n    \n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    \n    #print(year)\n    for an in list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2]))):\n        value_q.append(float(Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)&(Corporations_response_Climate['table_columns_unique_reference']=='C6.1_c1-Gross global Scope 1 emissions (metric tons CO2e)')]['response_value'].values[0]))\n        value_year.append(year)\n        \ndf_climate['year'] = value_year\ndf_climate['CO2 emissions Scope1 [tons]'] = value_q\n\n#  Scope 2: CO2 when electricities are used\n\nq = 'What were your organization’s gross global Scope 2 emissions in metric tons CO2e?'\n\nvalue_q = []\nvalue_year = []\n\nfor year in range(2018,2021):\n    #Corporations ---Climate Change\n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    #print(year)\n    for an in list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2]))):\n        \n        dd = Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)].table_columns_unique_reference.values\n        if 'C6.3_c1-Scope 2, location-based' in dd:\n            value_q.append(float(Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)&(Corporations_response_Climate['table_columns_unique_reference']=='C6.3_c1-Scope 2, location-based')]['response_value'].values[0]))\n        else:\n            value_q.append(float(np.nan))\n        \ndf_climate['CO2 emissions Scope2 [tons]'] = value_q\n\ndf_climate['total CO2 [tons]'] = df_climate['CO2 emissions Scope1 [tons]'].values + df_climate[\"CO2 emissions Scope2 [tons]\"].values\n\nq = 'Select the currency used for all financial information disclosed throughout your response.'\n\nvalue_q = []\nvalue_year = []\n\nfor year in range(2018,2021):\n    #Corporations ---Climate Change\n    Corporations_disclosing_Climate = data[year][0]\n    Corporations_response_Climate = data[year][1]\n    #print(year)\n    for an in list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2]))):\n        \n        dd = Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)].table_columns_unique_reference.values\n        value_q.append(Corporations_response_Climate[(Corporations_response_Climate['question_unique_reference']==q)&(Corporations_response_Climate['account_number']==an)]['response_value'].values[0])\n        \n        \ndf_climate['currency'] = value_q\n\n# calcurate revenue [USD], use 2018-2020 mean of currency [USD]\nconverter = {'USD':1,\n             'CAD':0.756111,\n             'CNY':0.147014,\n             'GBP':1.296685,\n             'JPY':0.009194,\n             'TWD':0.033720,\n             np.nan:np.nan}\ndf_climate['currency2USD'] = df_climate.currency.apply(lambda x: converter[x]).values\n\ncols = [\n         'organization','country','authority_types','activities','sectors',\n         'industries','primary_activity','primary_sector','primary_industry',\n         'primary_questionnaire_sector','tickers'\n       ]\ndisclosing = []\nfor an in df_climate.account_number.values:\n    disclosing.append(Corporations_disclosing_Climate[Corporations_disclosing_Climate.account_number==an][cols].values)\ndisclosing = np.concatenate(disclosing,0)\nfor col in cols:\n    df_climate[col] = 0\n    \ndf_climate.loc[:,cols] = disclosing\n\n# Add Annual Financial Data For Hybrid Metrics from Takahiro Kubo (https://www.kaggle.com/takahirokubo0) to measure economical information\n\nfin = pd.read_csv('../input/annual-financial-data-for-hybrid-cdp-kpi/cdp_financial_data.csv')\nfinancial_df = []\nidxs = []\ndf_climate_tickers = df_climate.tickers.apply(lambda x: str(x).split()[0].replace('nan','')).values\nfor i in range(len(df_climate)):\n    an, year = df_climate.loc[i,['account_number','year']].values\n    ticker = df_climate_tickers[i]\n    financial_ticker_year = fin[(fin.Ticker.values==ticker)&(fin['Fiscal Year'].values==year)]\n    if len(financial_ticker_year)==1:\n        idxs.append(i)\n        financial_df.append(financial_ticker_year)\nfinancial_df = pd.concat(financial_df,0)\nfin_cols = ['Ticker','Revenue','Cost of Revenue','Operating Income (Loss)','Operating Expenses','Depreciation & Amortization','EBITDA']\nfor col in fin_cols:\n    df_climate[col] = np.nan\n    \ndf_climate.loc[np.array(idxs),fin_cols] = financial_df[fin_cols].values\n\ncols = []\nfor col in df_climate.columns:\n    if col=='revenue':\n        cols.append('revenue[USD]')\n    else:\n        cols.append(col)\ndf_climate.columns = cols\n\nprint('Data cleaning for climate-change completed.')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Water Security"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\nprint('Water Security')\nprint()\n\nprint('number of questions')\nprint()\n\nsets = {}\n\nfor year in range(2018, 2021):\n    \n    # Corporations ---Water Security\n    \n    Corporations_disclosing_Water = data[year][2]\n    Corporations_response_Water = data[year][3]\n    \n    vc = Corporations_response_Water.account_number.value_counts()\n    \n    longest = 0\n    \n    a = []\n    for i in range(len(vc)):\n        an = vc.index[i]\n        v = len(np.unique(Corporations_response_Water[Corporations_response_Water.account_number==an].question_number.value_counts().index))\n        a.append(v)\n        \n        if v>longest:\n            sets[year] = Corporations_response_Water[Corporations_response_Water.account_number==an].question_number.values\n            longest = v\n    \n    print(year, max(a))\n    \nprint()\nprint(len(sets), 'unique questions are included for all 2018-2020.')\nprint()\n\nprint('number of accounts')\nprint()\n\nsets_an = []\n\nfor year in range(2018, 2021):\n    \n    #Corporations ---Water Security\n    \n    Corporations_disclosing_Water = data[year][2]\n    Corporations_response_Water = data[year][3]\n    \n    print(year, Corporations_response_Water.account_number.value_counts().shape[0])\n    \n    sets_an.append(set(Corporations_response_Water.account_number.value_counts().index.values))\n    \nprint()\nprint(len(sets_an[0]&sets_an[1]&sets_an[2]), 'of account_number are contained for all 2018-2020')\nprint()\nprint('unique questions')\n\nfrom textblob import TextBlob\n\nfor n, q in enumerate(np.unique(list(duplicate_questions))):\n    \n    blob = TextBlob(q)\n    print('Question', n, q)\n    #print(blob.translate(to='ja'))\n    \n    #print(Corporations_response_Climate[Corporations_response_Climate['question_unique_reference']==q].response_value.value_counts().shape[0])\n    \n\n    \n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# City"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"sets = {}\nsets_qname = {}\n\nprint('City')\nprint()\n\nprint('number of questions')\nprint()\n\nfor year in range(2018, 2021):\n    \n    #Corporations ---City\n\n    Corporations_disclosing_City = data[year][4]\n    Corporations_response_City = data[year][5]\n    \n    vc = Corporations_response_City['Account Number'].value_counts()\n    \n    longest = 0\n    \n    a = []\n    for i in range(len(vc)):\n        an = vc.index[i]\n        v = len(np.unique(Corporations_response_City[Corporations_response_City['Account Number']==an]['Question Number'].value_counts().index))\n        a.append(v)\n        \n        if v>longest:\n            sets[year] = set(Corporations_response_City[Corporations_response_City['Account Number']==an]['Question Number'].values)\n            sets_qname[year] = set(Corporations_response_City[Corporations_response_City['Account Number']==an]['Question Name'].values)\n            longest = v\n        \n    print(year, max(a))\n    \nduplicate = sets[2018]&sets[2019]&sets[2020]\nduplicate_questions = sets_qname[2018]&sets_qname[2019]&sets_qname[2020]\n\nprint()\nprint(len(duplicate_questions), 'unique questions are included for all 2018-2020.')\nprint()\n\nprint('number of accounts')\nprint()\n\nsets_an = []\nfor year in range(2018, 2021):\n    \n    #Corporations ---City\n    \n    Corporations_disclosing_City = data[year][4]\n    Corporations_response_City = data[year][5]\n    \n    print(year, Corporations_response_City['Account Number'].value_counts().shape[0])\n    \n    sets_an.append(set(Corporations_response_City['Account Number'].value_counts().index.values))\n    \nprint(len(sets_an[0]&sets_an[1]&sets_an[2]), 'of account_number are contained for all 2018-2020')\nprint()\n\nprint('unique questions')\nprint()\n\n#from textblob import TextBlob\n\nfor n, q in enumerate(np.unique(list(duplicate_questions))):\n    \n    #blob = TextBlob(q)\n    print('Question', n, q)\n    #print(blob.translate(to='ja'))\n    \n    #print(Corporations_response_City[Corporations_response_City['Question Name']==q]['Response Answer'].value_counts().shape[0])\n    \ndf_city = pd.DataFrame()\ndf_city['Account Number'] = list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2])))*3\ndf_city['year'] = [2018]*len(sets_an[0]&sets_an[1]&sets_an[2])+[2019]*len(sets_an[0]&sets_an[1]&sets_an[2])+[2020]*len(sets_an[0]&sets_an[1]&sets_an[2])\n\n# city's KPI and goals\n\nq = 'Please describe the main goals of your city’s adaptation efforts and the metrics / KPIs for each goal.'\nvalue_q = []\nfor year in range(2018,2021):\n    #Corporations ---City\n    Corporations_disclosing_City = data[year][4]\n    Corporations_response_City = data[year][5]\n    #print(year)\n    for an in list(np.sort(list(sets_an[0]&sets_an[1]&sets_an[2]))):\n        try:\n            value_q.append(Corporations_response_City[(Corporations_response_City['Question Name']==q)&(Corporations_response_City['Account Number']==an)]['Response Answer'].values[0])\n        except:\n            value_q.append(float(np.nan))\ndf_city['KPI'] = value_q\n\ncols = ['Organization','City','Country','CDP Region','Population','City Location']\n\ndisclosing = []\nfor an in df_city['Account Number'].values:\n    disclosing.append(Corporations_disclosing_City[Corporations_disclosing_City['Account Number']==an][cols].values)\ndisclosing = np.concatenate(disclosing,0)\n\nfor col in cols:\n    df_city[col] = 0\n    \ndf_city.loc[:,cols] = disclosing\n\nprint('Data cleaning for city completed.')\n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Below is the plot of average CO2 emissions for each viewpoint (primary_industry-emission, Revenue-emission). distribution of CO2 emissions can change by corporations' income, industry, and any other multiple factors, and it made quantitative assessment difficult. FRoG can solve this problem by taking all these features into account."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = plt.figure(figsize=(16,6))\nax1 = fig.add_subplot(1, 2, 1)\nax2 = fig.add_subplot(1, 2, 2)\nax1.scatter(df_climate.groupby('primary_industry').mean()['total CO2 [tons]'].index, df_climate.groupby('primary_industry').mean()['total CO2 [tons]'].values)\nax1.set_xticklabels(labels=df_climate.groupby('primary_industry').mean()['total CO2 [tons]'].index, Rotation= -40)\nax1.set_xlabel('primary_industry')\nax1.set_ylabel('total CO2 [tons]')\nax2.scatter(df_climate.groupby('Revenue').mean()['total CO2 [tons]'].index, df_climate.groupby('Revenue').mean()['total CO2 [tons]'].values)\nax2.set_xlabel('Revenue')\nax2.set_ylabel('total CO2 [tons]')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" # Explanations of KPI"},{"metadata":{},"cell_type":"markdown","source":"### In order to measure how  large impacts of cities &  corporate ambitions are, I propose new KPI system, \n## \"FRoG\" ,  Functional Regressive optimized Goals (of CO2-reduction)"},{"metadata":{},"cell_type":"markdown","source":"## Overview of FRoG"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import cv2\nim = cv2.imread('../input/frog-diagram/FRoG.jpg')\nim = cv2.cvtColor(im, cv2.COLOR_BGR2RGB)\nfig = plt.figure(figsize=(12,12))\nplt.imshow(im)\nplt.axis(\"off\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### FRoG proposes underlying extra-wastsed amount of CO2 for some companies and shows how corporations have possibility to reduce CO2 emissions, by using regression. Functional regression (for example, linear regression, like below image) enables stable and statistical assessment of CO2-value for each (statistical) conditions of corporations, and by adding population based penalty, we can assess corporations & city from 2 aspects, environmentally and socially."},{"metadata":{},"cell_type":"markdown","source":"![](http://i.imgur.com/DT4H1Yk.jpg)"},{"metadata":{},"cell_type":"markdown","source":"### Merit to use Regression function for FRoG\n### FRoG uses functional regression, which is the technology used in the field of Machine Learning. by using regression function, we extract statistical distribution & importance of input values to estimate essential value (FRoG value) more precisely. Function of regression can also be other useful machine-learning models (like RandomForest, GradientBoostingDicisionTree, NeuralNetwork, etc...), better estimation make us more precisely evaluate potential wasted-CO2, and why I decided linear regression for this time was because it's often used in the fields of both ML & statistics."},{"metadata":{},"cell_type":"markdown","source":"# Implementation Example of FRoG"},{"metadata":{},"cell_type":"markdown","source":"## FRoG without social-dimension penalty\n### At first, label data encoding (like data['primary_industry']=='Survices' into numerical values). This time I use simple columns ['primary_industry','Revenue'], as input values, ['total CO2 [tons]'] as a target to train & define FRoG. (use 2018 data for training, 2019 data for evaluation). any other columns can be added for more precise function fitting, and any function to regress target can be used, this time using linear regression to simplize."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"converter = {'Services': 0,\n 'Manufacturing': 1,\n 'Materials': 2,\n 'Retail': 3,\n 'Food, beverage & agriculture': 4,\n 'Biotech, health care & pharma': 5,\n 'Transportation services': 6,\n 'Infrastructure': 7,\n 'Fossil Fuels': 8,\n 'Power generation': 9,\n 'Hospitality': 10,\n 'Apparel': 11,\n 'International bodies': 12}\ndf_encode = df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0][['year']+['primary_industry','Revenue']+['total CO2 [tons]']]\nonehot = np.eye(len(converter))[df_encode.primary_industry.apply(lambda x: converter[x]).values]\nfor c in converter:\n    df_encode[c] = 0\ndf_encode.loc[:,list(converter.keys())] = onehot\n\ninput_cols = ['Revenue']+list(converter.keys())\ntarget_cols = ['total CO2 [tons]']\n\ntrain_X = df_encode[df_encode.year==2018][input_cols]\ntrain_y = df_encode[df_encode.year==2018][target_cols]\neval_X = df_encode[df_encode.year==2019][input_cols]\neval_y = df_encode[df_encode.year==2019][target_cols]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Nextly Let's fit FRoG function"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from sklearn import linear_model\nclf = linear_model.LinearRegression()\nclf.fit(train_X, train_y)\neval_pred = clf.predict(eval_X)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Result\n### Raw FRoG scores are below"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = plt.figure(figsize=(9,6))\nplt.plot(eval_y.head(69)['total CO2 [tons]'].values,label='real value')\nplt.plot(eval_pred[:69],label='FRoG value')\nplt.legend()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### We can see some companies are over-wasting CO2 based on FRoG (blue line > orange line), and some companies are saving CO2 (orange line > blue line), based on functional based regression. some specific examples are like below."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\nfig = plt.figure(figsize=(9,6))\n\nidx = [ 1,  5,  6, 16, 22, 23, 34, 38, 39, 40, 48]\ndec = df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0][df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0].year==2019]\nplt.bar(dec.iloc[idx]['organization'].values, dec.iloc[idx]['total CO2 [tons]'].values,label='real value')\nplt.bar(dec.iloc[idx]['organization'].values, eval_pred.flatten()[idx],label='FRoG value')\nflag_r = True\nflag_b = True\nfor i in range(len(idx)):\n    x = dec.iloc[idx]['organization'].values[i]\n    real, pred = dec.iloc[idx]['total CO2 [tons]'].values[i], eval_pred.flatten()[idx][i]\n    margin = real-pred\n    if margin>0:\n        plt.bar([x],[real],color='#1f77b4')\n        plt.bar([x],[pred],color='#ff7f0e')\n    else:\n        plt.bar([x],[pred],color='#ff7f0e')\n        plt.bar([x],[real],color='#1f77b4')\nfor i in range(len(idx)):\n    x = dec.iloc[idx]['organization'].values[i]\n    real, pred = dec.iloc[idx]['total CO2 [tons]'].values[i], eval_pred.flatten()[idx][i]\n    margin = real-pred\n    if margin>0:\n        if flag_r:\n            plt.vlines([x],ymin=pred,ymax=real,color='r',label='Wasted CO2')\n            flag_r = False\n        else:\n            plt.vlines([x],ymin=pred,ymax=real,color='r')\n    else:\n        if flag_b:\n            plt.vlines([x],ymin=real,ymax=pred,color='b',label='Saved CO2')\n            flag_b = False\n        else:\n            plt.vlines([x],ymin=real,ymax=pred,color='b')\n            \nplt.legend()\nplt.xticks(rotation=-50)\nplt.xlabel('organization name')\nplt.ylabel('CO2[tons]')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## FRoG with social-dimension penalty (city's population)\n![](http://wattsupwiththat.files.wordpress.com/2016/05/clip_image002_thumb1.png?resize=625%2C409)\n### From history, we can see there's almost linear relationship between population and CO2 ppm. if population increase, CO2 emissions from the city increase proportionately, and we need to take this into account.\n### We add linear penalty into FRoG, by population based penalty, corporations in the city with more population need to reduce more CO2, and we're able to take social-impact of CO2 emissions into account.\n### FRoG = FRoG(functional regression estimation) - α * population. (this time using α=1.0 for example.)\n### Results are like below"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\norganization2city = {'The AES Corporation':'Arlington','American Airlines Group Inc':'Fort Worth',\n                     'American Electric Power Company, Inc.':'Columbus','CSX Corporation':'Jacksonville',\n                     'Exelon Corporation':'Chicago','Fluor Corporation':'Irving',\n                     'NRG Energy Inc':'Houston','PG&E Corporation':'San Francisco',\n                     'Pinnacle West Capital Corporation':'Phoenix','PPL Corporation':'AllenTown',\n                     'TransAlta Corporation':'Calgary'}\nidx = [ 1,  5,  6, 16, 22, 23, 34, 38, 39, 40, 48]\ndec = df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0][df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0].year==2019]\ncity_names = dec.iloc[idx]['organization'].apply(lambda x: organization2city[x])\npopulations = []\nfor name in city_names:\n    if len(df_city[(df_city.City==name)&(df_city.year==2019)])==0:\n        populations.append(np.nan)\n    else:\n        populations.append(df_city[(df_city.City==name)&(df_city.year==2019)]['Population'].values[0])\n\n# Penalty -alpha * (population), this time alpha = 1e+12\n        \n\nfig = plt.figure(figsize=(9,6))\n\nidx = [ 1,  5,  6, 16, 22, 23, 34, 38, 39, 40, 48]\ndec = df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0][df_climate[df_climate[['primary_industry','Revenue']+['total CO2 [tons]']].isna().sum(1)==0].year==2019]\nplt.bar(dec.iloc[idx]['organization'].values, dec.iloc[idx]['total CO2 [tons]'].values,label='real value')\nplt.bar(dec.iloc[idx]['organization'].values, eval_pred.flatten()[idx],label='FRoG value')\nflag_r = True\nflag_b = True\nalpha = 1\nfor i in range(len(idx)):\n    x = dec.iloc[idx]['organization'].values[i]\n    real, pred = dec.iloc[idx]['total CO2 [tons]'].values[i], eval_pred.flatten()[idx][i]\n    margin = real-pred\n    if margin>0:\n        plt.bar([x],[real],color='#1f77b4')\n        plt.bar([x],[pred],color='#ff7f0e')\n    else:\n        plt.bar([x],[pred],color='#ff7f0e')\n        plt.bar([x],[real],color='#1f77b4')\nfor i in range(len(idx)):\n    x = dec.iloc[idx]['organization'].values[i]\n    real, pred = dec.iloc[idx]['total CO2 [tons]'].values[i], eval_pred.flatten()[idx][i]\n    margin = real-pred\n    if margin>0:\n        if flag_r:\n            plt.vlines([x],ymin=pred,ymax=real,color='r',label='Wasted CO2')\n            plt.bar([x],bottom=pred-(alpha * np.array(populations))[i],height=(alpha * np.array(populations))[i], label='Penalty',color='g')\n            flag_r = False\n        else:\n            plt.vlines([x],ymin=pred,ymax=real,color='r')\n            plt.bar([x],bottom=pred-(alpha * np.array(populations))[i],height=(alpha * np.array(populations))[i], color='g')\n    else:\n        if flag_b:\n            plt.vlines([x],ymin=real,ymax=pred,color='b',label='Saved CO2')\n            plt.bar([x],bottom=pred-(alpha * np.array(populations))[i],height=(alpha * np.array(populations))[i],color='g')\n            flag_b = False\n        else:\n            plt.vlines([x],ymin=real,ymax=pred,color='b')\n            plt.bar([x],bottom=pred-(alpha * np.array(populations))[i],height=(alpha * np.array(populations))[i],color='g')\n            \nplt.legend()\nplt.xticks(rotation=-50)\nplt.xlabel('organization name')\nplt.ylabel('CO2[tons]')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Comparison to Past KPIs\n### Lastly, let's see what's the advantage of FRoG compared to other past KPIs of cities.\n### Past KPIs of cities are like below (displaying only America/Canada 2020, not NaN response, total 64).\n### We can see every cities are making their own absolute KPI by using their own past data, but almost all of them don't mention about country-wide relative CO2 reduction metric which is compared to other cities in whole country. In this sense, FRoG has advantage as relative metric which regress essential CO2 emissions and also consider about other multiple fuctors of cities and corporations to measure how their CO2 emissions are from social&environmental standpoints."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"sets_an = []\nfor year in range(2018, 2021):\n    \n    #Corporations ---City\n    \n    Corporations_disclosing_City = data[year][4]\n    Corporations_response_City = data[year][5]\n    \n    sets_an.append(set(Corporations_response_City['Account Number'].value_counts().index.values))\n    \nyear = 2020\nCorporations_disclosing_City = data[year][4]\nCorporations_response_City = data[year][5]\n\ncount = 0\n\nfor i in range(302):\n    \n    an = list((sets_an[0]&sets_an[1]&sets_an[2]))[i]\n    q = 'Please describe the main goals of your city’s adaptation efforts and the metrics / KPIs for each goal.'\n    city = Corporations_disclosing_City[Corporations_disclosing_City['Account Number']==an].City.values[0]\n    country = Corporations_disclosing_City[Corporations_disclosing_City['Account Number']==an].Country.values[0]\n    response = Corporations_response_City[(Corporations_response_City['Account Number']==an)&(Corporations_response_City['Question Name']==q)&(Corporations_response_City['Column Name']=='Description of metric / indicator used to track goal')]['Response Answer'].values[0]\n    if (country in ['United States of America','Canada'])&(type(response)==str):\n        print('city name :',city, country)\n        print()\n        print('response :',response)\n        print()\n        count += 1","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Conclusion\n### In this notebook I proposed new KPI system FRoG, which uses machine-learning technics into evaluation of CO2 emissions, from standpoints of both environment and society. Using machine-learning technologies enables us estimating potential wasted CO2 precisely, and it can be used for any other factors, not only revenue/industry of corporations, but also other conditions of companies can be used for estimation."},{"metadata":{},"cell_type":"markdown","source":"# Reference\n### [1] Visualization of CDP files from Jared Savage at https://www.kaggle.com/c/cdp-unlocking-climate-solutions/data\n### [2] Financial informations of corporations from Takahiro Kubo https://www.kaggle.com/takahirokubo0/annual-financial-data-for-hybrid-cdp-kpi\n### [3] Explanation image of Linear Regression http://i.imgur.com/DT4H1Yk.jpg\n### [4] Relationship between world population and global CO2 https://wattsupwiththat.com/2016/05/17/the-correlation-between-global-population-and-global-co2/"},{"metadata":{},"cell_type":"markdown","source":"# Thank you for watching!"}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}