{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CDP: Extract Emissions from Corporate Responses\n\nIn this notebook, extract CO2 emission data from corporate responses.  \nThere are various questionnaires in CDP that relate to CO2 emission. I show the relation of these and extract the most suitable data.\n\n\n![top_ima](https://i.imgur.com/lanKdmV.png)\n\n**I hope this notebook helps your analysis and if it realizes, please upvote!**\n\n\n## Questionnaires that relate to emissions\n\n* C4: Targets and performance\n* C5: Emissions methodology\n* C6: Emissions data\n\nThe above questionnaires all require to answer Scope1/2 emissions. Which is most suitable?  \n\n**In short, C6 is the easiest to use.**\n\nI explain why is it and the difference between C4 & C5.\n\n\n## C6: Emissions data\n\n\"C6: Emissions data\" mainly asks Scope1, Scope2, and Scope3 emissions.  \n\nThe following picture is from `2019_Climate_Change_Questionnarie.pdf`. The ~2019 questionnaire document is most understandable because the response flow chart picture is attached.\n\n![questionnaire_c6](https://i.imgur.com/WzqzUkR.png)\n\nIf you are not very familiar with Scope, the following are short commentaries.\n\n* Scope1: The emissions from the company's own fuels.\n* Scope2: The emissions from the supplied energy (ex. electric power).\n  * location based: Based on the grid-average emission factor data.\n  * market based; Based on the procurement source grid factor data. If you purchase renewable energy, you can reflect it.\n* Scope3: The emissions from the overall supply chain.\n  * There are 15 categories of relations in the company's supply chain and report CO2e each of these.\n\nThe method to calculate GHG emission is defined at [GHG protocal](https://ghgprotocol.org/).\n\n\n### Extract each Scope emissions\n\nLet's extract the emissions data from C6 responses.\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":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},"cell_type":"code","source":"RESPONSE_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":{},"cell_type":"markdown","source":"The emission data is asked at C6.1, C6.3 and C6.5.  \nTo extract the data, we have to understand the structure of each questionnaire.\n\n**`question_number` C6.1**\n\n`column_number` = 1 and `row_number` = 1 is our target.\n\n![c6.1](https://i.imgur.com/Bk5O0cX.png)\n\n\n**`question_number` C6.3**\n\n`column_number` = 1 (location-based), 2 (market-based)  and `row_number` = 1 is our target.\n\n\n![c6.3](https://i.imgur.com/ASFPDuK.png)\n\n\n**`question_number` C6.5**\n\n`column_number` = 2 is our target. There are various categories, for that reason we have to sum up these to get Scope3 data.\n\n![c6.5](https://i.imgur.com/AQ6E8rV.png)\n\n\nAdditionally, you can confirm the response rate of each question from [this notebook](https://www.kaggle.com/takahirokubo0/overview-of-corporations-data-of-cdp)."},{"metadata":{"trusted":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    \n    return c6_emissions\n\nc6_emissions_2020 = extract_c6_emissions(cl_dfs[2020])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"c6_emissions_2020.head(5)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"def test_emissions(year_df, emissions):\n    \"\"\"\n    Test emissions values\n    \"\"\"\n    \n    import random\n    master = year_df[year_df[\"question_number\"].isin([\"C6.1\", \"C6.3\", \"C6.5\"])]\n\n    def get_value(series):\n        return float(series.tolist()[0])\n    \n    # C6.1\n    have_scope1 = emissions[emissions[\"scope\"] == \"Scope1\"]\n    account_number = random.choice(have_scope1[\"account_number\"].unique().tolist())\n    df = have_scope1[have_scope1[\"account_number\"] == account_number]\n    c6_1 = df[(df[\"question_number\"] == \"C6.1\") & (df[\"row_number\"] == 1) & (df[\"column_number\"] == 1)]\n    em = emissions[(emissions[\"account_number\"] == account_number) & (emissions[\"scope\"] == \"Scope1\")]    \n    assert len(c6_1) > 0\n    assert len(em) > 0\n    assert get_value(c6_1[\"response_value\"]) == get_value(em[\"response_value\"])\n\n    # C6.3\n    for i, s in enumerate([\"Scope2-location\", \"Scope2-market\"]):\n        have_scope2 = emissions[emissions[\"scope\"] == s]\n        account_number = random.choice(have_scope2[\"account_number\"].unique().tolist())\n        df = master[master[\"account_number\"] == account_number]\n\n        c6_3 = df[(df[\"question_number\"] == \"C6.3\") & (df[\"row_number\"] == 1) & (df[\"column_number\"] == i + 1)]\n        em = emissions[(emissions[\"account_number\"] == account_number) & (emissions[\"scope\"] == s)]\n        assert len(c6_3) > 0\n        assert len(em) > 0\n        assert get_value(c6_3[\"response_value\"]) == get_value(em[\"response_value\"])\n    \n    # C6.5\n    have_scope3 = emissions[emissions[\"scope\"] == \"Scope3\"]\n    account_number = random.choice(have_scope3[\"account_number\"].unique().tolist())\n    df = master[master[\"account_number\"] == account_number]\n\n    c6_5 = df[(df[\"question_number\"] == \"C6.5\") & (df[\"column_number\"] == 2)]\n    assert sum(c6_5[\"table_columns_unique_reference\"].apply(lambda x: x.endswith(\"Metric tonnes CO2e\"))) == len(c6_5)\n    c6_5 = sum(c6_5[\"response_value\"].dropna().astype(float))\n    em = emissions[(emissions[\"account_number\"] == account_number) & (emissions[\"scope\"] == \"Scope3\")]\n    em = sum(em[\"response_value\"].astype(float))\n    assert c6_5 == em\n    \n    return True\n\n\nfor i in range(10):\n    test_emissions(cl_dfs[2020], c6_emissions_2020)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here is the simple visualization."},{"metadata":{"trusted":true},"cell_type":"code","source":"alt.Chart(\n    c6_emissions_2020\\\n    .groupby([\"organization\", \"scope\"])\\\n    .sum()[\"response_value\"]\\\n    .reset_index()\\\n    .sort_values(by=\"response_value\", ascending=False).head(30)\n).mark_bar().encode(\n    x=\"response_value:Q\",\n    y=alt.Y(\"organization\", sort=alt.EncodingSortField(field=\"response_value\", order=\"descending\")),\n    color=\"scope\"\n)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## C5: Emissions methodology\n\n\"C5: Emissions methodology\" is questionnaire for \"methodology\" and it asks **\"base year\"** Scope1 and Scope2 .\n\n![c5.jpg](https://i.imgur.com/9gNntvE.png)\n\n\nSadly, response of C5 is missiong in this competition data.  \nFor that reason, you can't use C5.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"for year in cl_dfs:\n    df = cl_dfs[year]\n    c5_1 = df[df[\"question_number\"] == \"C5.1\"]\n    print(\"Number of responses for C5.1 at {} is {}.\".format(year, len(c5_1)))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"(It was too late to notice this fact before I started implementation).  \nThe following code will be helpful someday we can access C5."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def extract_c5_emissions(year_df, emissions=\"emissions\"):\n    \"\"\"\n    Extract base year's Scope1, Scope2 emissions from C5.\n    \"\"\"\n    structure = {\n        \"C5.1\": {\n            \"columns\": [\"base_year_begin\", \"base_year_end\", \"emissions\", None],\n            \"rows\": [\"Scope1\", \"Scope2-location\", \"Scope2-market\"]\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    c5_emissions = []\n    for target_number in structure:\n        location = structure[target_number]\n        df = year_df[year_df[\"question_number\"] == target_number]\n\n        print(len(df))\n\n        # Select columns\n        bases = []\n        for i, c in enumerate(location[\"columns\"]):\n            if c is None:\n                continue\n            selected = df[df[\"column_number\"] == i]\n            selected = selected[items]\n            selected.rename(columns={\"response_value\": c}, inplace=True)\n            if len(bases) == 0:\n                bases.append(selected)\n            else:\n                bases.append(selected[c])\n        \n        bases = pd.concat(bases, axis=1)\n        # Select rows\n        rows = []\n        for j, r in enumerate(location[\"rows\"]):\n            if r is None:\n                continue\n            rows.append(j)\n        bases = bases[bases[\"row_number\"].isin(rows)]\n        scope = bases[\"row_number\"].apply(lambda i: location[\"rows\"][i])\n        bases.insert(len(bases.columns), \"scope\", scope)\n\n\n        # Preprocess response value\n        bases[emissions] = pd.to_numeric(bases[emissions], errors=\"coerce\")\n        bases = bases.dropna(subset=[c for c in location[\"columns\"] if c is not None])\n        c5_emissions.append(bases)\n\n    c5_emissions = pd.concat(c5_emissions)\n    \n    return c5_emissions\n\nextract_c5_emissions(cl_dfs[2020])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## C4: Targets and performance\n\n\n\"C4: Targets and performance\" asks the reduction plan and its progress than emissions itself.\nIt relates TCFD \"Metrics and Targets\".\n\n![c4](https://i.imgur.com/TOYUGP6.png)\n\nThe C4 questionnarie branch off \"C4.1a\" and \"C4.2b\" depends on unit or target.\n\n* C4.1a: Reduce the actual emissions in a future year (=\"Absolute target\").\n* C4.2b: Calculate reduction based on the normalized value by business metric (=\"Intensity target\").\n\nHow to set the target (C4) and how much amount of CO2 is emitted (C6) is another story.\nFor that reason, to use C6 is suitable if you want to know actual emissions.\n\nThe structure of C4 is the following.\n\n![c4_structure](https://i.imgur.com/DI2CebL.png)\n\nLet's watch the distribution of kinds of targets."},{"metadata":{"trusted":true},"cell_type":"code","source":"def compare_c4_target(year_dfs):\n    \"\"\"\n    Compare target kind\n    \"\"\"\n    SCOPE_COLUMN = {\n        2018: 2,\n        2019: 2,\n        2020: 4\n    }\n    items = [\"account_number\", \"organization\", \"survey_year\",\n             \"question_number\", \"column_number\", \"row_number\",\n             \"table_columns_unique_reference\", \"response_value\"]\n\n    c4_responses = []\n    for year in year_dfs:\n        df = year_dfs[year]\n        column = SCOPE_COLUMN[year]\n        for q in (\"C4.1a\", \"C4.1b\"):\n            responses = df[(df[\"question_number\"] == q) & (df[\"column_number\"] == column)][items]\n            responses = responses.dropna(subset=[\"response_value\"])\n            responses.rename(columns={\"response_value\": \"scope\"}, inplace=True)\n            responses[\"survey_year\"] = str(year)\n            c4_responses.append(responses)\n        \n    c4_responses = pd.concat(c4_responses).reset_index(drop=True)\n    return c4_responses\n\n\nc4_responses = compare_c4_target(cl_dfs)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def visualize_c4(c4_responses, threshold=10):\n    \"\"\"\n    Visualize Target kinds.\n    \"\"\"\n    r = c4_responses\\\n            .groupby([\"scope\", \"survey_year\"])\\\n            .size()\\\n            .reset_index(name=\"count\")\n    \n    r = r[r[\"count\"] > threshold]\n    return alt.Chart(r).mark_bar().encode(\n        x=\"count:Q\",\n        y=alt.Y(\"scope\", sort=alt.EncodingSortField(field=\"count\", order=\"descending\")),\n        color=\"survey_year\"\n    )\nvisualize_c4(c4_responses)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The most of targets are mixed Scope 1 and Scope 2.  \nIt seems to be difficult to get the pure value of each Scope.\n\n\n## Back to C6 and Visualize Emissions\n\nAt last, we back to C6 and visualize its data.\n\nLet's extract corporate attributes (ticker etc) from disclosure data."},{"metadata":{"trusted":true},"cell_type":"code","source":"DISCLOSURE_ROOT = \"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing\"\nYEARS = (2018, 2019, 2020)\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[year] = df","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"def extract_master(year_df):\n    \"\"\"\n    Extract corporate attribute data.\n    \"\"\"\n    items = [\n        \"account_number\",\n        \"primary_ticker\",\n        \"organization\",\n        \"survey_year\",\n        \"country\",\n        \"region\",\n        \"authority_types\",\n        \"activities\",\n        \"sectors\",\n        \"industries\",\n        \"primary_activity\",\n        \"primary_sector\",\n        \"primary_industry\",\n        \"primary_questionnaire_sector\",\n    ]\n    df = year_df[items]\n    df.dropna(subset=[\"account_number\", \"primary_ticker\", \"survey_year\"], inplace=True)\n    return df\n\n\nextract_master(cld_dfs[2020]).head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now join the disclosure and emissions."},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"def make_emissions_data(year_dfs, year_ddfs):\n    \"\"\"\n    Make emissions data by using C6 and disclosure data\n    \"\"\"\n    \n    emissions = []\n    for year in year_dfs:\n        c6 = extract_c6_emissions(year_dfs[year])\n        c6.rename(columns={\"response_value\": \"emissions\"}, inplace=True)\n        master = extract_master(year_ddfs[year])\n        df = c6.merge(master, how=\"inner\", on=[\"account_number\", \"survey_year\"], suffixes=(\"_emission\", None))\n        df[\"survey_year\"] = str(year)\n        emissions.append(df)\n    \n    emissions = pd.concat(emissions)\n    return emissions\n\nemissions = make_emissions_data(cl_dfs, cl_ddfs)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"emissions.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's visualize emissions.  \nYou can filter the data by click."},{"metadata":{"trusted":true},"cell_type":"code","source":"def visualize_emissions(emissions, height=300, left_width=100, right_width=500):\n    \"\"\"\n    Visualize emissions intaractively\n    Left: yearly emissions\n    Right: emissions in each sector\n    \"\"\"\n    \n    items = [\"survey_year\", \"primary_industry\", \"scope\", \"emissions\"]\n    source = emissions[items]\\\n                .groupby(items[:-1])\\\n                .sum()\\\n                .reset_index()\n        \n    selector = alt.selection_single(empty=\"all\", fields=[\"scope\"])\n    base = alt.Chart(source).properties(\n                height=height\n            ).add_selection(selector)\n    \n    \n    left = base.mark_bar().encode(\n                x=\"survey_year\",\n                y=\"emissions:Q\",\n                color=alt.condition(selector, \"scope\", alt.value(\"lightgray\"))\n            ).properties(width=left_width)\n    \n    \n    right = base.mark_bar().encode(\n                x=\"survey_year\",\n                y=alt.Y(\"emissions:Q\", stack=\"normalize\"),\n                color=\"primary_industry\",\n            ).transform_filter(\n                selector\n            ).properties(width=right_width)\n    \n    return left | right\n\n\nvisualize_emissions(emissions)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can confirm \"Food, beverage\" decrease its emissions but \"Manufacturing\" increases.  \nIs this means \"Food, beverage\" is eager to reduce emissions, and \"Manufacturing\" is lazy to climate change?\n\nWe need more surveys to unravel observation!"}],"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}