{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CROM: Climate-Related Opportunity Metrics\n\nTo triage climate-related opportunities is necessary because climate change is an urgent problem.  \nWe have to allocate the limited budget to feasible and effective ones. It is the common problem for cities and companies. \n\n![top_image](https://i.imgur.com/kDxyLOU.png)\n\nI show the **CROM: Climate-Related Opportunity Metrics** to evaluate the climate-related opportunities from qualitative and quantitive aspects.\n\n* Qualitative: The opportunities should be feasible enough to be integrated into the corporate business strategy.\n* Quantitative: The opportunities should be effective enough to earn financial performance.\n\nThis metrics is not only useful for companies but also for cities when making the decision about the fund to promote climate-related opportunities, \n\nFor that reason, this metrics can detect **practical and actionable points** between city and company. That is asked in the original problem statement.\n\n*What are the practical and actionable points where city and corporate ambition join, i.e. where do cities have problems that corporations affected by those problems could solve, and vice versa?*\n\nThe characteristics of CROM is the following.\n\n* Simple: The formula is very simple.\n* Assemblable: Each item in the formula has a distinctive meaning.\n* Customizable: You can change the weight of the items in the formula according to your interest.\n\nThe construction of this document is the following.\n\n1. How to calculate CROM.\n  * Qualitative Evaluation\n  * Quantitive Evaluation\n  * Calculate CROM\n2. Find the actionable points of city and company by CROM.\n\n\n# How to calculate CROM\n\nCROM is the multiplied value of qualitative evaluation and quantitative evaluation.  \n\n*CROM = qualitative evaluation * quantitative evaluation*\n\nThe CDP data is necessary to calculate these and additional financial data is required for quantitative evaluation.\n\n\n# Qualitative Evaluation\n\nThe qualitative evaluation is calculated by the following.\n\n*Qualitative Evaluation = Time horizon + Likelihood + Impact*\n\nIn short, *The short-term and most likely occur and impactful opportunities* are highly graded.  \nYou can customize the weight of item in the formula.  \nFor example, if you concern the time horizon is important, add weight to the time horizon ( *(1.5 * Time horizon) + Likelihood + Impact* etc).\nI use the following CDP questionnaires for qualitative evaluation.\n\n* C2.4a: *Provide details of opportunities identified with the potential to have a substantive financial or strategic impact on your business.*\n  * Column 4: Primary climate-related opportunity driver\n  * Column 7: Time horizon\n  * Column 8: Likelihood\n  * Column 9: Magnitude of impact\n\nThe response values are the following.\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import os\nimport pandas as pd\nimport numpy as np\nimport altair as alt\nimport re\nimport json","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Confirm the difference of columns\nRESPONSE_ROOT = \"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses\"\nYEARS = (2018, 2019, 2020)\ncl_dfs = {}\n\nfor year in YEARS:\n    kind = \"Climate Change\"\n    file_name = \"{}_Full_{}_Dataset.csv\".format(year, kind.replace(\" \", \"_\"))\n    path = \"{}/{}/{}\".format(RESPONSE_ROOT, kind, file_name)\n    df = pd.read_csv(path)\n    cl_dfs[year] = df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def extract_c24_responses(year_dfs):\n    \"\"\"\n    Extract C2.4a responses\n    Column 4: Primary climate-related opportunity driver\n    Column 7: Time horizon\n    Column 8: Likelihood\n    Column 9: Magnitude of impact\n    \"\"\"\n\n    c24s = []\n    columns = [\n        \"account_number\",\n        \"organization\",\n        \"survey_year\",\n        \"question_number\",\n        \"row_number\"\n    ]\n    \n    for year in year_dfs:\n        year_df = year_dfs[year][(columns + [\"column_number\", \"response_value\"])]\n        c24 = year_df[(year_df[\"question_number\"] == \"C2.4a\")]\n        c24 = c24[(c24[\"column_number\"].isin([4, 7, 8, 9]))]\n        c24 = c24.dropna(subset=[\"response_value\"])\n        c24[\"column_number\"] = c24[\"column_number\"].map(int)\n        stacked = c24.pivot(index=columns, columns=\"column_number\", values=\"response_value\")\n        stacked = stacked.rename_axis(None, axis=1).reset_index()\n        stacked.rename(columns={4: \"opportunity_driver\", 7: \"time_horizon\", 8: \"likelihood\", 9: \"impact\"}, inplace=True)\n        c24s.append(stacked)\n    \n    c24s = pd.concat(c24s)\n    return c24s\n\n\nqualitative_df = extract_c24_responses(cl_dfs)\nqualitative_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now, I convert *Time horizon*, *Likelihood*, *Magnitude of impact* to 1~5 number values."},{"metadata":{"trusted":true},"cell_type":"code","source":"def convert_time_horizon(v):    \n    convert_dict = {\n        \"Unknown\": 0,\n        \"Current\": 4.0,\n        \"Short-term\": 5.0,\n        \"Medium-term\": 2.0,\n        \"Long-term\": 1.0,\n    }\n\n    if not pd.notna(v):\n        return 0\n    else:\n        return convert_dict[v]\n\n\nqualitative_df[\"time_horizon_value\"] = qualitative_df[\"time_horizon\"].apply(convert_time_horizon)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def convert_likelihood(v):    \n    convert_dict = {\n        \"Unknown\": 0,\n        \"Virtually certain\": 5.0,\n        \"Very likely\": 4.5,\n        \"Likely\": 4.0,\n        \"More likely than not\": 3.0,\n        \"About as likely as not\": 2.5,\n        \"Unlikely\": 2.0,\n        \"Very unlikely\": 1.5,\n        \"Exceptionally unlikely\": 1.0\n    }\n\n    if not pd.notna(v):\n        return 0\n    else:\n        return convert_dict[v]\n\n\nqualitative_df[\"likelihood_value\"] = qualitative_df[\"likelihood\"].apply(convert_likelihood)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def convert_impact(v):    \n    convert_dict = {\n        \"Unknown\": 0,\n        \"High\": 5.0,\n        \"Medium-high\": 4.0,\n        \"Medium\": 3.0,\n        \"Medium-low\": 2.0,\n        \"Low\": 1.0\n    }\n\n    if not pd.notna(v):\n        return 0\n    else:\n        return convert_dict[v]\n\n\nqualitative_df[\"impact_value\"] = qualitative_df[\"impact\"].apply(convert_impact)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's calculate qualitative evaluation by `time_horizon_value` * `likelihood_value` * `impact_value`.  \nAnd visualize the value of each `opportunity_driver`. "},{"metadata":{"trusted":true},"cell_type":"code","source":"def qualitative_evaluation(df, time_horizon_weight=1, likelihood_weight=1, impact_weight=1, scale=5):\n    value = (time_horizon_weight * df[\"time_horizon_value\"]) *\\\n             (likelihood_weight * df[\"likelihood_value\"]) *\\\n               (impact_weight * df[\"impact_value\"])\n    if scale > 0:\n        return value / (scale**3) * scale\n    else:\n        return value","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from altair import expr, datum\n\n\ndef visualize_opportunity_drivers_qualitative(df):\n    \"\"\"\n    Visualize qualitative evaluation of each opportunity drivers.\n    \"\"\"\n\n    def merge_driver(x):\n        if isinstance(x, str) and x.startswith(\"Other\"):\n            return \"Other\"\n        else:\n            return x\n    \n    slider_t = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Time Horizon Weight:\")\n    select_t = alt.selection_single(fields=[\"time_horizon_weight\"],\n                                    bind=slider_t, init={\"time_horizon_weight\": 1.0})\n\n    slider_l = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Likelihood Weight:\")\n    select_l = alt.selection_single(fields=[\"likelihood_weight\"],\n                                    bind=slider_l, init={\"time_horizon_weight\": 1.0})\n\n    slider_i = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Impact Weight:\")\n    select_i = alt.selection_single(fields=[\"impact_weight\"],\n                                    bind=slider_i, init={\"impact_weight\": 1.0})\n    \n    select_year = alt.selection_multi(fields=[\"year\"])\n    \n    \n    data = pd.DataFrame({\n            \"year\": df[\"survey_year\"].apply(str),\n            \"opportunity_driver\": df[\"opportunity_driver\"].apply(merge_driver),\n            \"t\": df[\"time_horizon_value\"],\n            \"l\": df[\"likelihood_value\"],\n            \"i\": df[\"impact_value\"],\n           })\n    \n    data = data.groupby([\"year\", \"opportunity_driver\"]).median().reset_index()\n    return alt.Chart(data).mark_bar().encode(\n        x=\"evaluation:Q\",\n        y=alt.Y(\"opportunity_driver\", sort=alt.EncodingSortField(field=\"evaluation\", order=\"descending\")),\n        color=\"year\"\n    ).add_selection(\n        select_t, select_l, select_i, select_year\n    ).transform_calculate(\n        evaluation=(datum.t * select_t.time_horizon_weight + datum.l * select_l.likelihood_weight + datum.i * select_i.impact_weight) / 3\n    ).transform_filter(\n        select_year\n    )\n\nvisualize_opportunity_drivers_qualitative(qualitative_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Please change the weight slider and reflect your interest!\n\n* If we think *Impact* is important, \"Ability to diversity business activities\" is highly rated.\n* *Likelihood* changes the evaluation a little, but *Participation in carbon market* is ranked up if it is important.\n* As *Time Hrizon Weight* decrease, \"Ability to diversity business activities\" is ranked up.\n\nTo change the weight and watch its effect contributes to your consideration."},{"metadata":{},"cell_type":"markdown","source":"# Quantitive Evaluation\n\nThe quantitive evaluation is calculated by the following (This formula is based on [Hybrid Metrics](https://www.sharedvalue.org/resource/hybrid-metrics/)).\n\n*Quantitive Evaluation = EBITDA / CO2 emission*\n\nIn short, this value is fuel efficiency.  \nIf the opportunity is effective, it will not only decrease emissions but also increase earnings because it led to differentiation in the market.\n\nI use the following CDP questionnaires and [financial data](https://www.kaggle.com/takahirokubo0/annual-financial-data-for-hybrid-cdp-kpi) for quantitive evaluation.\n\n* C6.1: What were your organization’s gross global Scope 1 emissions in metric tons CO2e?\n* C6.3: What were your organization's gross global Scope 2 emissions in metric tons CO2e?\n* C6.5: Account for your organization’s Scope 3 emissions, disclosing and explaining any exclusions.\n\nTo extract emissions from the above questionnaires is a little complicated.  \n[Please refer to this notebook for the detail](https://www.kaggle.com/takahirokubo0/cdp-extract-emissions-from-corporate-responses).\n\nYou can customize CO2 emission with the selective summation of Scope1~3, and use another account instead of EBITDA ([Depreciation & Amortization is one of the alternatives](https://www.kaggle.com/takahirokubo0/cdp-hybrid-metrics-for-corporate-sustainability)).\n\n\nAt first, read the financial dataset."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"!pip install simfin","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import simfin as sf\nfrom simfin.names import *\n\n\nFINANCIAL_ROOT = \"../input/annual-financial-data-for-hybrid-cdp-kpi/cdp_financial_data.csv\"\nf_df = pd.read_csv(FINANCIAL_ROOT)\nf_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And read the emisssion data."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def extract_c6_emissions(year_df):\n    \"\"\"\n    Extract Scope1, Scope2 and Scope3 emissions from C6.\n    \"\"\"\n    structure = {\n        \"C6.1\": {\n            \"column_name\": \"Scope1\",\n            \"column_number\": 1,\n            \"row_number\": 1\n        },\n        \"C6.3\": {\n            \"column_name\": [\"Scope2-location\", \"Scope2-market\"],\n            \"column_number\": [1, 2],\n            \"row_number\": 1\n        },\n        \"C6.5\": {\n            \"column_name\": [\"Scope3\"],\n            \"column_number\": 2\n        }\n    }\n    \n    items = [\"account_number\", \"organization\", \"survey_year\",\n             \"question_number\", \"column_number\", \"row_number\",\n             \"table_columns_unique_reference\", \"response_value\"]\n    \n    c6_emissions = []\n    for target_number in structure:\n        location = structure[target_number]\n        df = year_df[year_df[\"question_number\"] == target_number]\n        \n        # Select columns\n        columns = location[\"column_number\"]\n        columns = columns if isinstance(columns, list) else [columns]\n        for i, c in enumerate(columns):\n            name = location[\"column_name\"]\n            name = name if isinstance(name, str) else name[i]\n            selected = df[df[\"column_number\"] == c]\n            selected = selected[items]\n            \n            # Filter by rows\n            if \"row_number\" in location:\n                r = location[\"row_number\"]\n                selected = selected[selected[\"row_number\"] == r]\n            \n            # Preprocess response value\n            selected[\"response_value\"] = pd.to_numeric(selected[\"response_value\"], errors=\"coerce\")\n            selected = selected.dropna(subset=[\"response_value\"])\n            selected[\"scope\"] = pd.Series([name] * len(selected), index=selected.index)\n            c6_emissions.append(selected)\n        \n    c6_emissions = pd.concat(c6_emissions)\n    items.append(\"scope\")\n    items.remove(\"row_number\")\n    c6_emissions = c6_emissions.groupby(items).sum().reset_index()\n    c6_emissions.rename(columns={\"response_value\": \"emissions\"}, inplace=True)\n    \n    return c6_emissions","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def make_quantitive_df(year_dfs, f_df):\n    \"\"\"\n    Make financial & non-financial dataset.\n    \"\"\"\n    \n    emissions = []\n    for year in year_dfs:\n        e_df = extract_c6_emissions(year_dfs[year])\n        e_df[\"survey_year\"] = year\n        pivot = e_df.pivot_table(index=[\"account_number\", \"survey_year\"], columns=\"scope\", values=\"emissions\")\n        pivot = pivot.reset_index()\n        pivot.fillna(0, inplace=True)\n        pivot[\"emissions\"] = pivot[\"Scope1\"] + pivot[\"Scope2-location\"] + pivot[\"Scope2-market\"] + pivot[\"Scope3\"]\n        emissions.append(pivot)\n    \n    emissions = pd.concat(emissions)\n    df = emissions.merge(f_df, how=\"inner\", on=[\"account_number\", \"survey_year\"], suffixes=(\"_emission\", None))    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"quantitive_df = make_quantitive_df(cl_dfs, f_df)\nquantitive_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To allocate the emissions and financial data to opportunities, I use qualitative evaluation value."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def allocate_quantitives(qualitative_df, quantitive_df):\n    \"\"\"\n    Allocate quantitive data by qualitative evaluation.\n    \"\"\"\n    \n    # Calculate allocation rate\n    qualitative_df[\"qualitative_evaluation\"] = qualitative_evaluation(qualitative_df)\n    columns = [\"account_number\", \"survey_year\", \"question_number\", \"row_number\", \"opportunity_driver\"]\n    allocation_rate = qualitative_df[columns + [\"qualitative_evaluation\"]].groupby(columns).agg(\n                        {\"qualitative_evaluation\": \"sum\"})\n    allocation_rate = allocation_rate.groupby(level=list(range(len(columns) - 2))).apply(lambda v: v / float(v.sum()))\n    allocation_rate = allocation_rate.reset_index()\n    allocation_rate.rename(columns={\"qualitative_evaluation\": \"allocation_rate\"}, inplace=True)\n    q_df = qualitative_df.merge(allocation_rate, how=\"left\", on=columns, suffixes=(\"_allocation\", None))\n    \n    # Extract financial values   \n    fv_columns = [\n        REVENUE,\n        COST_REVENUE,\n        OPERATING_INCOME,\n        OPERATING_EXPENSES,\n        DEPR_AMOR,\n        \"EBITDA\",\n        \"Scope1\",\n        \"Scope2-location\",\n        \"Scope2-market\",\n        \"Scope3\",\n        \"emissions\",\n    ]\n    \n    f_columns = [\n        \"account_number\",\n        \"survey_year\",\n        \"Ticker\",\n        CURRENCY,\n        FISCAL_YEAR,\n        FISCAL_PERIOD\n    ]\n     \n    # Merge financial data\n    df = q_df.merge(quantitive_df[f_columns + fv_columns],\n                    how=\"left\", on=[\"account_number\", \"survey_year\"], suffixes=(\"_emission\", None))\n    \n    # Allocate by rate\n    for c in fv_columns:\n        df[c] = df[c] * df[\"allocation_rate\"]\n    \n    return df\n\n\nqq_df = allocate_quantitives(qualitative_df, quantitive_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's visualize quantitive evaluation.  \n(I use sigmoid function to scale quantitive evaluation to 0~1 scale)."},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"def quantitive_evaluation(df, kind=\"EBITDA\", scale=5):\n\n    \n    def clip(s, lower_th=1, upper_th=99):\n        _lower, _upper = np.percentile(s, [lower_th, upper_th])\n        return np.clip(s, _lower, _upper)\n    \n    \n    def f_normalize(s):\n        return (s - np.mean(s)) / np.std(s)\n    \n    \n    def sigmoid(s):\n        return s.apply(lambda v: 0.0 if v < -709 else 1 / (1 + np.exp(-v)))\n    \n    value = f_normalize(clip(df[kind]))\n    emissions = f_normalize(clip(df[\"emissions\"]))\n    value = sigmoid((value / emissions)).fillna(0)\n    \n    if scale > 0:\n        return value * scale\n    else:\n        return value","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def visualize_opportunity_drivers_quantitive(df):\n    \"\"\"\n    Visualize quantitive evaluation of each opportunity drivers.\n    \"\"\"\n\n    def merge_driver(x):\n        if isinstance(x, str) and x.startswith(\"Other\"):\n            return \"Other\"\n        else:\n            return x\n\n    _df = df.dropna(subset=[\"EBITDA\", DEPR_AMOR, \"emissions\"])\n    \n    columns = [\"EBITDA\", DEPR_AMOR]\n    selector_kind = alt.binding_select(options=columns, name=\"Calculation Base\")\n    select_kinds = alt.selection_single(fields=[\"column\"], bind=selector_kind, init={\"column\": \"EBITDA\"})\n\n    data = pd.DataFrame({\n            \"year\": _df[\"survey_year\"].apply(str),\n            \"opportunity_driver\": _df[\"opportunity_driver\"].apply(merge_driver),\n            \"EBITDA\": quantitive_evaluation(_df, \"EBITDA\"),\n             DEPR_AMOR: quantitive_evaluation(_df, DEPR_AMOR)\n           })\n    \n    data = data.groupby([\"year\", \"opportunity_driver\"]).median().reset_index()\n    return alt.Chart(data).transform_fold(\n            columns,\n            as_=[\"column\", \"evaluation\"]\n           ).transform_filter(\n            select_kinds \n           ).mark_bar().encode(\n            x=\"evaluation:Q\",\n            y=alt.Y(\"opportunity_driver\", sort=alt.EncodingSortField(field=\"evaluation\", order=\"descending\")),\n            color=\"year\"\n           ).add_selection(\n            select_kinds\n           )\n\n\nvisualize_opportunity_drivers_quantitive(qq_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The change of calculation base change the value.\n\n* If \"EBITDA\" base, *Participation in carbon market*, *Use of recycling* is highly rated.\n* If \"Depreciation & Amortization\" base, *Acccess to new markets* and *Shift in consumer preferences* is important.\n\nThe \"Depreciation & Amortization\" base emphasis on asset procurement efficiency. Please refer the detail to [this notebook](https://www.kaggle.com/takahirokubo0/cdp-hybrid-metrics-for-corporate-sustainability).\n"},{"metadata":{},"cell_type":"markdown","source":"# Calculate CROM\n\nNow we can calculate CROM.  \n\n*CROM = qualitative evaluation * quantitative evaluation*"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def visualize_opportunity_drivers_crom(df):\n    \"\"\"\n    Visualize qualitative and quantitive evaluation of each opportunity drivers.\n    \"\"\"\n\n    def merge_driver(x):\n        if isinstance(x, str) and x.startswith(\"Other\"):\n            return \"Other\"\n        else:\n            return x\n    \n    slider_t = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Time Horizon Weight:\")\n    select_t = alt.selection_single(fields=[\"time_horizon_weight\"],\n                                    bind=slider_t, init={\"time_horizon_weight\": 1.0})\n\n    slider_l = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Likelihood Weight:\")\n    select_l = alt.selection_single(fields=[\"likelihood_weight\"],\n                                    bind=slider_l, init={\"time_horizon_weight\": 1.0})\n\n    slider_i = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Impact Weight:\")\n    select_i = alt.selection_single(fields=[\"impact_weight\"],\n                                    bind=slider_i, init={\"impact_weight\": 1.0})\n    \n    columns = [\"EBITDA\", DEPR_AMOR]\n    selector_kind = alt.binding_select(options=columns, name=\"Calculation Base\")\n    select_kinds = alt.selection_single(fields=[\"column\"], bind=selector_kind, init={\"column\": \"EBITDA\"})\n\n    select_opp = alt.selection_multi(fields=[\"opportunity_driver\"])\n    \n    _df = df.dropna(subset=[\"EBITDA\", DEPR_AMOR, \"emissions\"])\n\n    data = pd.DataFrame({\n            \"year\": _df[\"survey_year\"].apply(str),\n            \"opportunity_driver\": _df[\"opportunity_driver\"].apply(merge_driver),\n            \"t\": _df[\"time_horizon_value\"],\n            \"l\": _df[\"likelihood_value\"],\n            \"i\": _df[\"impact_value\"],\n            \"EBITDA\": quantitive_evaluation(_df, \"EBITDA\"),\n             DEPR_AMOR: quantitive_evaluation(_df, DEPR_AMOR)\n           })\n    \n    data = data.groupby([\"year\", \"opportunity_driver\"]).median().reset_index()\n    base = alt.Chart(data).transform_calculate(\n                qualitative=(datum.t * select_t.time_horizon_weight + datum.l * select_l.likelihood_weight + datum.i * select_i.impact_weight) / 3\n           ).transform_fold(\n                columns,\n                as_=[\"column\", \"quantitive\"]\n           ).transform_filter(\n                select_kinds\n           ).transform_filter(\n                select_opp\n           ).transform_calculate(\n                crom=datum.qualitative + datum.quantitive\n           ).add_selection(\n                select_t, select_l, select_i, select_kinds, select_opp\n            )\n    \n    location = base.mark_circle().encode(\n                x=alt.X(\"quantitive:Q\", scale=alt.Scale(domain=[0, 5])),\n                y=alt.Y(\"qualitative:Q\", scale=alt.Scale(domain=[0, 7])),\n                size=alt.Size(\"crom:Q\", scale=alt.Scale(align=1.5, domain=[0, 25])),\n                color=\"opportunity_driver\",\n                tooltip=[\"year\", \"opportunity_driver\"]\n            )\n    \n    time_series = base.mark_line().encode(\n                    x=\"year\",\n                    y=alt.Y(\"sum(crom):Q\", scale=alt.Scale(domain=[0, 12])),\n                    color=\"opportunity_driver\"\n                  ).properties(\n                    width=250\n                  )\n    \n    return time_series | location\n\n\nvisualize_opportunity_drivers_crom(qq_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can recognize the following fact by selecting trend in left chart and analyze the reason (qualitative or quantitive) in the right chart.\n\n* Increase\n  * *Development and/or expansion of low emission goods and services* increases its CROM score by both qualitative & quantitive score.\n  * *Use of supportive policy incentives* increases its CROM score by quantitive score.\n* Decrease\n  * *Reduced water usage and consumption* decreases its CROM score by its quantitive score.\n\n\nFinally add company data to analyze."},{"metadata":{"trusted":true},"cell_type":"code","source":"DISCLOSURE_ROOT = \"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing\"\ncl_ddfs = []\n\nfor year in YEARS:\n    kind = \"Climate Change\"\n    file_name = \"{}_Corporates_Disclosing_to_CDP_{}.csv\".format(year, kind.replace(\" \", \"_\"))\n    path = \"{}/{}/{}\".format(DISCLOSURE_ROOT, kind, file_name)\n    df = pd.read_csv(path)\n    cl_ddfs.append(df)\n\ncl_ddfs = pd.concat(cl_ddfs)\nqq_df = qq_df.merge(cl_ddfs, how=\"inner\", on=[\"survey_year\", \"account_number\"], suffixes=(None, \"_master\"))\nqq_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Find the actionable points of city and company by CROM\n\nThe actionable points meet the following 2 conditions.\n\n1. The opportunities of the city and company are overlapping.\n2. The opportunities should be feasible and effective (high CROM).\n\nTo reveal 1, extracting collaborations of cities and matching these to corporate ones is necessary.\n\n* Extract collaborations of cities from *Please provide some key examples of how your city collaborates with business in the table below.*\n  * 2018: 5.1a\n  * 2019: 6.1a\n  * 2020: 6.2a\n* Match the collaboration and opportunities by [Universal Sentence Encoder](https://arxiv.org/abs/1803.11175).\n  * It is suitable to convert text to vector in CDP because the CDP has multiple language responses.\n  \nAt first extract collaborations."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"CITY_RESPONSE_ROOT = \"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses\"\nCITY_DISCLOSING_ROOT = \"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing\"\ncity_dfs = []\n\n\nfor year in YEARS:\n    response_file_name = \"{}_Full_Cities_Dataset.csv\".format(year)\n    disclosing_file_name = \"{}_Cities_Disclosing_to_CDP.csv\".format(year)\n    response_path = \"{}/{}\".format(CITY_RESPONSE_ROOT, response_file_name)\n    disclosing_path = \"{}/{}\".format(CITY_DISCLOSING_ROOT, disclosing_file_name)\n    \n    responses = pd.read_csv(response_path)\n    disclosing = pd.read_csv(disclosing_path)\n    merged = responses.merge(disclosing, how=\"inner\", on=[\"Year Reported to CDP\", \"Account Number\"], suffixes=(None, \"_master\"))\n    city_dfs.append(merged)\n\ncity_dfs = pd.concat(city_dfs)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def extract_city_collaborations(city_dfs):\n    business_collaborations = {\n        2018: \"5.1a\",\n        2019: \"6.1a\",\n        2020: \"6.2a\"\n    }\n    \n    city_collaborations = []\n    for year in YEARS:\n        df = city_dfs[city_dfs[\"Year Reported to CDP\"] == year]\n        question_number = business_collaborations[year]\n        question_df = df[\n                        (df[\"Question Number\"] == question_number) &\\\n                        (df[\"Column Name\"] == \"Description of collaboration\")\n                        ].dropna(subset=[\"Response Answer\"])\n        city_collaborations.append(question_df)\n    \n    city_collaborations = pd.concat(city_collaborations)\n    \n    return city_collaborations\n\n\ncity_collaborations = extract_city_collaborations(city_dfs)\ncity_collaborations.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Each city has its own collaborations."},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_collaboration(df, city_name, year):\n    collaboration = df[(df[\"Organization\"] == city_name) & (df[\"Year Reported to CDP\"] == year)][\"Response Answer\"]\n    return collaboration\n\n\ntokyo_collaboration = get_collaboration(city_collaborations, \"Tokyo Metropolitan Government\", 2020)\ntokyo_collaboration.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Then match the collaborations and opportunities.  "},{"metadata":{"trusted":true},"cell_type":"code","source":"import tensorflow_hub as hub\n\n\nencoder = hub.load(\"https://tfhub.dev/google/universal-sentence-encoder/4\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from sklearn.metrics.pairwise import cosine_similarity\n\n\nclass OpportunityMatcher():\n    \n    def __init__(self, collaborations):\n        self.collaborations = collaborations\n        self._co_keys, self._co_vectors = self.calculate_vectors(collaborations)\n\n    def calculate_vectors(self, series, batch_size=10):\n        keys = series.dropna().unique()\n        vectors = None\n        texts = keys.tolist()\n        for i in range(0, len(texts), batch_size):\n            target = texts[i:(i + batch_size)]\n            v = encoder(target)\n            if vectors is None:\n                vectors = v.numpy()\n            else:\n                vectors = np.vstack([vectors, v.numpy()])\n\n        return keys, vectors\n    \n    def _sigmoid(self, v):\n        return 0.0 if v < -709 else 1 / (1 + np.exp(-v))\n        \n    def add_similarity_weight(self, df, opportunities_column):\n        op_keys, op_vectors = self.calculate_vectors(df[opportunities_column])\n        corr = cosine_similarity(op_vectors, self._co_vectors)\n        \n        folded = []\n        for c in self._co_keys:\n            co_df = []\n            for i, row in df.iterrows():\n                d = {}\n                for _c in df.columns:\n                    d[_c] = row[_c]\n                \n                d[\"collaboration\"] = c\n                \n                if not pd.notnull(row[opportunities_column]):\n                    d[\"similarity\"] = 0\n                else:\n                    y = op_keys.tolist().index(row[opportunities_column])\n                    x = self._co_keys.tolist().index(c)\n                    cr = corr[y, x]\n                    d[\"similarity\"] = self._sigmoid(cr)\n\n                co_df.append(d)\n            \n            folded += co_df\n        \n        folded = pd.DataFrame(folded)\n        return folded","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def visualize_collaboration_crom(df):\n    \"\"\"\n    Visualize collaboration.\n    \"\"\"\n\n    def merge_driver(x):\n        if isinstance(x, str) and x.startswith(\"Other\"):\n            return \"Other\"\n        else:\n            return x\n    \n    slider_t = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Time Horizon Weight:\")\n    select_t = alt.selection_single(fields=[\"time_horizon_weight\"],\n                                    bind=slider_t, init={\"time_horizon_weight\": 1.0})\n\n    slider_l = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Likelihood Weight:\")\n    select_l = alt.selection_single(fields=[\"likelihood_weight\"],\n                                    bind=slider_l, init={\"time_horizon_weight\": 1.0})\n\n    slider_i = alt.binding_range(min=0.5, max=2.0, step=0.1, name=\"Impact Weight:\")\n    select_i = alt.selection_single(fields=[\"impact_weight\"],\n                                    bind=slider_i, init={\"impact_weight\": 1.0})\n    \n    columns = [\"EBITDA\", DEPR_AMOR]\n    selector_kind = alt.binding_select(options=columns, name=\"Calculation Base\")\n    select_kinds = alt.selection_single(fields=[\"column\"], bind=selector_kind, init={\"column\": \"EBITDA\"})\n\n    select_co = alt.selection_multi(fields=[\"collaboration\"])\n    \n    _df = df.dropna(subset=[\"EBITDA\", DEPR_AMOR, \"emissions\"])\n\n    data = pd.DataFrame({\n            \"year\": _df[\"survey_year\"].apply(str),\n            \"collaboration\": _df[\"collaboration\"],\n            \"similarity\": _df[\"similarity\"],\n            \"t\": _df[\"time_horizon_value\"],\n            \"l\": _df[\"likelihood_value\"],\n            \"i\": _df[\"impact_value\"],\n            \"EBITDA\": quantitive_evaluation(_df, \"EBITDA\") * _df[\"similarity\"],\n             DEPR_AMOR: quantitive_evaluation(_df, DEPR_AMOR) * _df[\"similarity\"]\n           })\n    \n    data = data.groupby([\"year\", \"collaboration\"]).median().reset_index()\n    base = alt.Chart(data).transform_calculate(\n                qualitative=(datum.t * select_t.time_horizon_weight + datum.l * select_l.likelihood_weight + datum.i * select_i.impact_weight) * datum.similarity / 3\n           ).transform_fold(\n                columns,\n                as_=[\"column\", \"quantitive\"]\n           ).transform_filter(\n                select_kinds\n           ).transform_filter(\n                select_co\n           ).transform_calculate(\n                crom=datum.qualitative * datum.quantitive * datum.similarity\n           ).add_selection(\n                select_t, select_l, select_i, select_kinds, select_co\n            )\n    \n    location = base.mark_circle().encode(\n                x=alt.X(\"quantitive:Q\"),\n                y=alt.Y(\"qualitative:Q\", scale=alt.Scale(domain=[1, 3])),\n                size=alt.Size(\"crom:Q\", scale=alt.Scale(align=1.5, domain=[0, 5])),\n                color=\"collaboration\",\n                tooltip=[\"year\", \"collaboration\"]\n            )\n    \n    time_series = base.mark_line().encode(\n                    x=\"year\",\n                    y=alt.Y(\"sum(crom):Q\", scale=alt.Scale(domain=[0, 5])),\n                    color=\"collaboration\"\n                  ).properties(\n                    width=250\n                  )\n    \n    return time_series | location","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def show_collaboration(city_name, year, city_collaborations, qq_df):\n    collaboration = get_collaboration(city_collaborations, city_name, year)\n    matcher = OpportunityMatcher(collaboration)\n    qq_df_with_similarity = matcher.add_similarity_weight(qq_df, \"opportunity_driver\")\n    return visualize_collaboration_crom(qq_df_with_similarity)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"show_collaboration(\"Tokyo Metropolitan Government\", 2020, city_collaborations, qq_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* The CROM score increase because of quantitative value increase.\n* \"We are implementing a collaborative research which leverages private companies...\" is most effective collaboration.\n\nYou can evaluate various city collaborations.\n\n\n# Conclusion\n\nI show that CROM is useful to evaluate climate-related opportunities. And we can find actionable points by combining Natural Language Processing. CROM is very simple but enough to use and has many customizable points in it. I think customizable is necessary because climate change status changes from moment to moment and it means we have to change the priority timely.\n\nTo conclude, The CROM is the most suitable metric to triage climate-related opportunities and evaluate collaboration points."}],"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}