{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# CDP: Overview of Corporations Data\n\nCDP offers 3 types of data, Cities, Corporations, and Supplementary. We focus on \"Corporations\" data in this notebook.\nTo clarify \"What is the CDP data?\", We tackle 3 analyses in the following sections.\n\n1. How much the amount of data?\n2. What kinds of features in CDP?\n  1. Quantitative\n  2. Qualitative(Text)\n  3. Categorical\n  4. Temporal\n3. Basic statistics of representative feature\n\n\n## 0. Data Preparation\n\n### 0.1 load the data","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\n\n\nprint(os.getcwd())","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:13.321352Z","iopub.execute_input":"2021-07-26T07:56:13.321733Z","iopub.status.idle":"2021-07-26T07:56:13.581473Z","shell.execute_reply.started":"2021-07-26T07:56:13.321692Z","shell.execute_reply":"2021-07-26T07:56:13.580468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are 3 types of data in CDP.\n\n* Disclosing\n  * The information about disclosed companies (answer status, sectors and ticker etc).\n* Questionaries\n  * The PDF document of questionaries.\n* Responses\n  * The response to questionaries. The response seems to be the matrix, so there are column x row information in it.\n\nAt first, we focus on the status of responses.","metadata":{}},{"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\n","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:13.583499Z","iopub.execute_input":"2021-07-26T07:56:13.584111Z","iopub.status.idle":"2021-07-26T07:56:33.307715Z","shell.execute_reply.started":"2021-07-26T07:56:13.584067Z","shell.execute_reply":"2021-07-26T07:56:33.306542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 1. How much the amount of data?\n\n\nLet's count the responses and companies.","metadata":{}},{"cell_type":"code","source":"def show_counts(year_dfs):\n    year_counts = []\n    for year in year_dfs:\n        number_of_companies = year_dfs[year][\"account_number\"].nunique()\n        row = {\n            \"year\": year,\n            \"number_of_questions\": year_dfs[year][\"question_number\"].nunique(),\n            \"number_of_companies\": number_of_companies,\n            \"total_count_of_responses\": len(year_dfs[year]),\n            \"average_response_count\": len(year_dfs[year]) / number_of_companies\n        }\n        year_counts.append(row)\n    \n    return pd.DataFrame(year_counts)\n\n\nshow_counts(cl_dfs).head()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:33.309156Z","iopub.execute_input":"2021-07-26T07:56:33.309533Z","iopub.status.idle":"2021-07-26T07:56:33.465359Z","shell.execute_reply.started":"2021-07-26T07:56:33.309491Z","shell.execute_reply":"2021-07-26T07:56:33.464692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Then, count the response for each question numbers.","metadata":{}},{"cell_type":"code","source":"def show_response_counts(year_dfs):\n    response_counts = []\n    for year in year_dfs:\n        number_of_companies = year_dfs[year][\"account_number\"].nunique()\n        counts = year_dfs[year][\"question_number\"].value_counts().reset_index(name=\"counts\")\n        counts = counts.rename(columns={\"index\": \"question_number\"})\n        years = pd.Series([str(year)] * len(counts), name=\"year\")\n        df = pd.concat([years, counts], axis=1)\n        response_counts.append(df)\n    response_counts = pd.concat(response_counts)\n    \n    return response_counts\n\n\nresponse_counts = show_response_counts(cl_dfs)\nalt.Chart(response_counts).mark_rect().encode(\n    x=\"question_number:O\",\n    y=\"year:O\",\n    color='counts:Q'\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:33.466260Z","iopub.execute_input":"2021-07-26T07:56:33.466638Z","iopub.status.idle":"2021-07-26T07:56:33.742832Z","shell.execute_reply.started":"2021-07-26T07:56:33.466610Z","shell.execute_reply":"2021-07-26T07:56:33.742125Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There seem to exist very frequently responded questions (year 2018, C8.2d).\nLet's see about this question.  \n\n2019 C8.2d is \"(C8.2d) List the average emission factors of the fuels reported in C8.2c.\"","metadata":{}},{"cell_type":"code","source":"c8_2d = cl_dfs[2019][cl_dfs[2019][\"question_number\"] == \"C8.2d\"].groupby([\"account_number\"]).nunique()[\"row_number\"]\nc8_2d.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:33.744727Z","iopub.execute_input":"2021-07-26T07:56:33.745091Z","iopub.status.idle":"2021-07-26T07:56:35.125629Z","shell.execute_reply.started":"2021-07-26T07:56:33.745063Z","shell.execute_reply":"2021-07-26T07:56:35.124737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Many companies has same amount of responses.  Now see the detail of the response.","metadata":{}},{"cell_type":"code","source":"cl_dfs[2019][(cl_dfs[2019][\"question_number\"] == \"C8.2d\") & (cl_dfs[2019][\"account_number\"] == 58)]","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:35.127019Z","iopub.execute_input":"2021-07-26T07:56:35.127277Z","iopub.status.idle":"2021-07-26T07:56:35.241906Z","shell.execute_reply.started":"2021-07-26T07:56:35.127252Z","shell.execute_reply":"2021-07-26T07:56:35.240888Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`response_value` seems to be NaN.","metadata":{}},{"cell_type":"code","source":"c8_2d_exclude_nan = cl_dfs[2019][cl_dfs[2019][\"question_number\"] == \"C8.2d\"].dropna(subset=[\"response_value\"]).groupby([\"account_number\"]).nunique()[\"row_number\"]\nc8_2d_exclude_nan.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:35.243703Z","iopub.execute_input":"2021-07-26T07:56:35.244001Z","iopub.status.idle":"2021-07-26T07:56:35.496512Z","shell.execute_reply.started":"2021-07-26T07:56:35.243974Z","shell.execute_reply":"2021-07-26T07:56:35.495394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(c8_2d_exclude_nan / c8_2d).mean()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:35.497650Z","iopub.execute_input":"2021-07-26T07:56:35.497916Z","iopub.status.idle":"2021-07-26T07:56:35.508686Z","shell.execute_reply.started":"2021-07-26T07:56:35.497889Z","shell.execute_reply":"2021-07-26T07:56:35.507836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"3% of response is not NaN value.  From this perspective, we have to deleote NaN value in dataframe. ","metadata":{}},{"cell_type":"code","source":"for year in YEARS:\n    cl_dfs[year].dropna(subset=[\"response_value\"], inplace=True)\n\nresponse_counts = show_response_counts(cl_dfs)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:35.510000Z","iopub.execute_input":"2021-07-26T07:56:35.510392Z","iopub.status.idle":"2021-07-26T07:56:35.980655Z","shell.execute_reply.started":"2021-07-26T07:56:35.510350Z","shell.execute_reply":"2021-07-26T07:56:35.979724Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"response_counts.groupby(\"question_number\").agg({\"counts\": np.mean}).sort_values(by=\"counts\", ascending=False).head(10)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:35.981838Z","iopub.execute_input":"2021-07-26T07:56:35.982120Z","iopub.status.idle":"2021-07-26T07:56:35.999532Z","shell.execute_reply.started":"2021-07-26T07:56:35.982091Z","shell.execute_reply":"2021-07-26T07:56:35.998614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"alt.Chart(response_counts).mark_rect().encode(\n    x=\"question_number:O\",\n    y=\"year:O\",\n    color='counts:Q'\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:36.000807Z","iopub.execute_input":"2021-07-26T07:56:36.001068Z","iopub.status.idle":"2021-07-26T07:56:36.027963Z","shell.execute_reply.started":"2021-07-26T07:56:36.001042Z","shell.execute_reply":"2021-07-26T07:56:36.027281Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There seems to frequent question and rare question.\n\nFrequent\n\n* C2.x: Risks and opportunities\n  * C2.3a: Have you identified any inherent climate-related risks with the potential to have a substantive financial or strategic impact on your business?\n  * C2.4a: Have you identified any climate-related opportunities with the potential to have a substantive financial or strategic impact on your business?\n* C4.x: Targets and performance\n  * C4.3b: Provide details on the initiatives implemented in the reporting year in the table below.\n* C6.x: Emissions data\n  * C6.5: Account for your organization’s gross global Scope 3 emissions, disclosing and explaining any exclusions.\n* C7.x: Emissions breakdown\n  * C7.3b: Break down your total gross global Scope 1 emissions by business facility.\n  * C7.6: Indicate which gross global Scope 2 emissions breakdowns you are able to provide.\n  * C7.6b: Break down your total gross global Scope 2 emissions by business facility.\n\nNot Frequent\n\n* C0.x: Introduction\n* C1.x: Governance\n\n[TCFD](https://www.fsb-tcfd.org/recommendations/) requires \"governance\" first, but only a few companies respond. It means that the disclosure of climate change is still developing.  \nOn the contrary, `Risk and opporunities` and the report of results (`Targets and performance`, `Emissions data`, `Emissions breakdown`) is frequent.","metadata":{}},{"cell_type":"markdown","source":"## 2. What kinds of features in CDP?\n\nLet's identify the kind of features in CDP data. Specifically, I categorize the features as follows.\n\n1. Quantitative\n2. Qualitative(Text)\n3. Categorical\n4. Temporal\n\nIt's especially difficult to discriminate 2 & 3 because there are very long options in CDP questionaries.  \n(For example C8.2e \"None (no purchases of low-carbon electricity, heat, steam or cooling\"))\n\nFor that reason, I use `question_unique_reference` and `table_columns_unique_reference` to know the type of question.  \nEx. \"Select\" indicates Categorial.","metadata":{}},{"cell_type":"code","source":"def show_kinds_of_questions(year_df):\n    column_index = year_df[\"table_columns_unique_reference\"].apply(lambda x: x.split(\"-\")[0]).reset_index(name=\"column_index\")\n    column_text = year_df[\"table_columns_unique_reference\"].apply(lambda x: x.split(\"-\")[-1]).reset_index(name=\"column_text\")\n\n    df = pd.concat([year_df[[\"question_number\", \"question_unique_reference\"]], column_index, column_text], axis=1)\n    grouped = df.groupby(\n        [\"question_number\", \"question_unique_reference\",\n         \"column_index\", \"column_text\"]).size().reset_index(name=\"counts\")\n    return grouped\n\n\nquestion_groups = show_kinds_of_questions(cl_dfs[2020])\nquestion_groups","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:36.029228Z","iopub.execute_input":"2021-07-26T07:56:36.029488Z","iopub.status.idle":"2021-07-26T07:56:36.743738Z","shell.execute_reply.started":"2021-07-26T07:56:36.029462Z","shell.execute_reply":"2021-07-26T07:56:36.742838Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"question_groups[\"question_unique_reference\"].apply(lambda x: \" \".join(x.split()[:2])).value_counts()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:36.744843Z","iopub.execute_input":"2021-07-26T07:56:36.745092Z","iopub.status.idle":"2021-07-26T07:56:36.766423Z","shell.execute_reply.started":"2021-07-26T07:56:36.745067Z","shell.execute_reply":"2021-07-26T07:56:36.765661Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"question_groups[\"column_text\"].apply(lambda x: x.split(\" \")[0]).value_counts()[:20]","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:36.767618Z","iopub.execute_input":"2021-07-26T07:56:36.767867Z","iopub.status.idle":"2021-07-26T07:56:36.783968Z","shell.execute_reply.started":"2021-07-26T07:56:36.767842Z","shell.execute_reply":"2021-07-26T07:56:36.783299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Use `question_unique_reference` and `column_text`to detect type.","metadata":{}},{"cell_type":"code","source":"from datetime import datetime\n\n\ndef get_typed_responses(year_df):\n    df = year_df[pd.notnull(year_df[\"response_value\"])]\n    \n    def extract_type(row):\n        question_text = row[\"question_unique_reference\"]\n        column_text = row[\"table_columns_unique_reference\"].replace(row[\"question_number\"] + \"_\", \"\", 1)\n        value = row[\"response_value\"]\n        if \"-\" in column_text:\n            column_text = column_text.split(\"-\", 1)[1]\n        else:\n            column_text = \"\"\n        \n        # Is number value\n        if isinstance(value, int):\n            if len([w for w in column_tokens if w in [\"date\", \"time\", \"year\"]]) > 0:\n                return \"temporal\"\n            else:\n                return \"quantitative\"\n        elif isinstance(value, float):\n            return \"quantitative\"\n        elif value.isdigit():\n            return \"quantitative\"\n        else:\n            try:\n                float(value)\n                return \"quantitative\"\n            except ValueError:\n                pass\n            \n            try:\n                datetime.strptime(value, \"%Y-%m-%d\")\n                return \"temporal\"\n            except ValueError:\n                pass\n                        \n            # Text value\n            first_question_token = question_text.lower().split(\" \")[0]\n            first_column_token = column_text.lower().split(\" \")[0].replace(\"please \", \"\")\n            if first_question_token in [\"describe\", \"give\", \"explain\"]:\n                return \"qualitative\"\n            elif first_column_token in [\"comment\", \"explain\", \"details\"]:\n                return \"qualitative\"\n            else:\n                return \"categorical\"\n    \n    df[\"response_type\"] = df.apply(extract_type, axis=1)\n    return df\n\n\ntyped_cl_df_2020 = get_typed_responses(cl_dfs[2020])\ntyped_cl_df_2020[[\"response_type\", \"response_value\"]]","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:36.785015Z","iopub.execute_input":"2021-07-26T07:56:36.785433Z","iopub.status.idle":"2021-07-26T07:56:46.936762Z","shell.execute_reply.started":"2021-07-26T07:56:36.785405Z","shell.execute_reply":"2021-07-26T07:56:46.935784Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"typed_cl_df_2020.groupby(\"response_type\").size().plot.bar()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:46.937887Z","iopub.execute_input":"2021-07-26T07:56:46.938136Z","iopub.status.idle":"2021-07-26T07:56:47.144768Z","shell.execute_reply.started":"2021-07-26T07:56:46.938111Z","shell.execute_reply":"2021-07-26T07:56:47.144008Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. Basic statistics of representative feature\n\nWe focus `qualitative` feature in this section.  \nLet's visualize its length.","metadata":{}},{"cell_type":"code","source":"def show_response_length(year_dfs):\n    response_lengths = []\n    for year in year_dfs:\n        df = get_typed_responses(year_dfs[year])\n        df[\"year\"] = df[\"survey_year\"].apply(str)\n        df[\"length\"] = df[\"response_value\"].apply(lambda x: len(x.split()))\n        qualitatives = df[df[\"response_type\"] == \"qualitative\"]\n        length = qualitatives[[\"year\", \"question_number\", \"length\"]].groupby([\"year\", \"question_number\"]).max()[\"length\"].reset_index()\n        response_lengths.append(length)\n    response_lengths = pd.concat(response_lengths)\n    \n    return response_lengths\n\nresponse_lengths = show_response_length(cl_dfs)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:56:47.145946Z","iopub.execute_input":"2021-07-26T07:56:47.146474Z","iopub.status.idle":"2021-07-26T07:57:17.044912Z","shell.execute_reply.started":"2021-07-26T07:56:47.146430Z","shell.execute_reply":"2021-07-26T07:57:17.043908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"alt.Chart(response_lengths).mark_rect().encode(\n    x=\"question_number:O\",\n    y=\"year:O\",\n    color='length:Q'\n)\n","metadata":{"execution":{"iopub.status.busy":"2021-07-26T07:57:17.046307Z","iopub.execute_input":"2021-07-26T07:57:17.046851Z","iopub.status.idle":"2021-07-26T07:57:17.074011Z","shell.execute_reply.started":"2021-07-26T07:57:17.046801Z","shell.execute_reply":"2021-07-26T07:57:17.073044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The C0.x and C1.x are responded by only a few companies but its length is long.  \nMost of the response is a short description.\n\n\nC2.3a and C2.4a is crucial to disclose TCFD epecially `transition risk` and `physical risk`.","metadata":{}},{"cell_type":"code","source":"def show_keyword_include_rate(year_dfs, question_number, keyword):\n    response_rate = []\n    for year in year_dfs:\n        number_of_companies = year_dfs[year][\"account_number\"].nunique()\n        question_frame = year_dfs[year][(year_dfs[year][\"question_number\"] == question_number)]\n        description = question_frame[\"response_value\"].str.lower()\n        counts = question_frame[description.str.contains(keyword)][\"account_number\"].nunique()\n        result = {\n            \"year\": str(year),\n            \"question_number\": question_number,\n            \"keyword\": keyword,\n            \"keyword_match\": counts,\n            \"number_of_company\": number_of_companies,\n            \"rate\": counts / number_of_companies\n        }\n        response_rate.append(result)\n    response_rate = pd.DataFrame(response_rate)\n    \n    return response_rate","metadata":{"execution":{"iopub.status.busy":"2021-07-26T08:22:31.670318Z","iopub.execute_input":"2021-07-26T08:22:31.670701Z","iopub.status.idle":"2021-07-26T08:22:31.678174Z","shell.execute_reply.started":"2021-07-26T08:22:31.670669Z","shell.execute_reply":"2021-07-26T08:22:31.677326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"disclosure_df = []\n\n\nfor q in (\"C2.3a\", \"C2.4a\"):\n    for keyword in (\"transition risk\", \"physical risk\"):\n        df = show_keyword_include_rate(cl_dfs, q, keyword)\n        disclosure_df.append(df)\n\n\npd.concat(disclosure_df)","metadata":{"execution":{"iopub.status.busy":"2021-07-26T08:22:33.467551Z","iopub.execute_input":"2021-07-26T08:22:33.467892Z","iopub.status.idle":"2021-07-26T08:22:34.319030Z","shell.execute_reply.started":"2021-07-26T08:22:33.467864Z","shell.execute_reply":"2021-07-26T08:22:34.318099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cl_dfs[2020][(cl_dfs[2020][\"question_number\"] == \"C2.3a\") & (cl_dfs[2020][\"response_value\"].str.contains(\"transition risk\"))][\"response_value\"][:2].tolist()","metadata":{"execution":{"iopub.status.busy":"2021-07-26T08:28:19.795140Z","iopub.execute_input":"2021-07-26T08:28:19.795541Z","iopub.status.idle":"2021-07-26T08:28:19.989545Z","shell.execute_reply.started":"2021-07-26T08:28:19.795504Z","shell.execute_reply":"2021-07-26T08:28:19.988795Z"},"trusted":true},"execution_count":null,"outputs":[]}]}