{"cells":[{"metadata":{},"cell_type":"markdown","source":"**INTRODUCTION**\n---\nUpon examination of the city-level datasets provideed, we noted the inconsistency of the questionnaires from one year to the other. In particular, questionnaires differ among themselves in:\n\n* Number and identity of respondents;\n* Number and coding of questions;\n* Question text and answer structure.\n\nIn addition, many questions are constructed as to create high-dimensional categorical variables. Therefore, the first part of our work hinged on:\n\n1. Making uniform the format of questions and answers across years, in order to create a cross-section of all cities that answered to any questionnaires from 2018 to 2020, retaining the most recent response. In this way, we maximize the number of observations.\n2. Reducing dimensionality of the variables considered, which are the ones suggested by CDP in the file CDP_recommendations_for_questions_to_focus_on.xlsx\n\n**1. Uniformity**\n---\nFormerly, we needed to understand which questions were substantially repeated across years, even if the question name and number changed. Unfortunately, it is a task very difficult to accomplish for a machine. Therefore, we hand-selected all the questions based on their equivalence to other years' questions - in the columns 2018, 2019, 2020 of the following table.\n"},{"metadata":{},"cell_type":"markdown","source":"**2. Transformations**\n---\nThe KNIME workflow can be easily followed from https://svgshare.com/i/Rxg.svg, and corresponds to ../input/cities-project-workflow/cities_project.knwf\n\nThe new numbering index is created to conform the most possible to existing question numbers. Indeed, in short, it reallocates questions following the most recent sections, i.e. those in 2020. In case a question was present in previous years and not in 2020, the Excel file tried to replicate existing patterns of section change. In case this was not possible, the remaining questions were assigned to a section manually. This is how the “SS” part is obtained. The “QQ” part, instead, is obtained by ordering question numbers, giving preference to year 2020, then 2019, then 2018. The aggregate of the two is the updated question indexation.\n\n\nThe notable transformations introduced, referring to the uniform index just created, are:\n\n0100: multiplicative index for 0101, which is itself a dummy\n(Question Number MATCHES \"0100\") AND (Response Answer  MATCHES \"Yes\") => 1\n(Question Number MATCHES \"0100\") AND (Response Answer  MATCHES \"No\") => 0\n(Question Number MATCHES \"0100\") AND (Response Answer  MATCHES \"Do not know\") => 0\n(Question Number MATCHES \"0100\") AND (Response Answer  MATCHES \"Intending to undertake in future\") => 0.5\n(Question Number MATCHES \"0100\") AND (Response Answer  MATCHES \"In progress\") => 0.75\n\n0200: multiplicative index for 0201, which is itself a dummy\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"Yes\") => 1\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"No\") => 0\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"Do not know\") => 0\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"Not intending to undertake\") => 0\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"Intending to undertake in future\") => 0.5\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"Intending to undertake in the next 2 years\") => 0.5\n(Question Number MATCHES \"0200\") AND (Response Answer  MATCHES \"In progress\") => 0.75\n\n0202: Columns 5, 7, 8 are retained as dummies\n0205: Values are aggregated according to Appendix G of 2019, while qualitative response are recodified as:\n(Column Number = 2) AND (Response Answer = \"Currently affecting the city\") => 1\n(Column Number = 2) AND (Response Answer = \"Expected to affect the city in the future\") => 0.75\n(Column Number = 2) AND (Response Answer = \"Yes\") => 1\n(Column Number = 2) AND (Response Answer = \"No\") => 0.5\n(Column Number = 2) AND (Response Answer = \"Do not know\") => 0.75\n\n(Column Name = \"Magnitude of impact\") AND (Response Answer = \"Extremely serious\") => 1\n(Column Name = \"Magnitude of impact\") AND (Response Answer = \"Serious\") => 0.75\n(Column Name = \"Magnitude of impact\") AND (Response Answer = \"Less serious\") => 0.5\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Does not currently impact the city\") => 0.1\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Probability of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Does not currently impact the city\") => 0.1\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Current probability of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Consequence of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Does not currently impact the city\") => 0.1\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Current consequence of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Do not know\") => 0.5\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Does not currently impact the city\") => 0.1\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"High\") => 1\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Low\") => 0.25\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium Low\") => 0.5\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium\") => 0.75\n(Column Name = \"Current magnitude of hazard\") AND (Response Answer = \"Medium High\") => 0.9\n\n(Column Name = \"Future change in frequency\") AND (Response Answer = \"Do not know\") => 1\n(Column Name = \"Future change in frequency\") AND (Response Answer = \"Increasing\") => 1.5\n(Column Name = \"Future change in frequency\") AND (Response Answer = \"Decreasing\") => 0.5\n(Column Name = \"Future change in frequency\") AND (Response Answer = \"None\") => 1\n(Column Name = \"Future change in frequency\") AND (Response Answer = \"Not expected to happen in the future\") => 0.25\n\n(Column Name = \"Future change in intensity\") AND (Response Answer = \"Do not know\") => 1\n(Column Name = \"Future change in intensity\") AND (Response Answer = \"Increasing\") => 1.5\n(Column Name = \"Future change in intensity\") AND (Response Answer = \"Decreasing\") => 0.5\n(Column Name = \"Future change in intensity\") AND (Response Answer = \"None\") => 1\n(Column Name = \"Future change in intensity\") AND (Response Answer = \"Not expected to happen in the future\") => 0.25\n\n0209: Values are reassigned according to the following and multiply the dummy coming from each category\n(Column Number = 2) AND (Response Answer = \"Enhance\") => 1\n(Column Number = 2) AND (Response Answer = \"Support\") => 1\n(Column Number = 2) AND (Response Answer = \"Supports\") => 1\n(Column Number = 2) AND (Response Answer = \"Do not know\") => 0\n(Column Number = 2) AND (Response Answer = \"Challenge\") => -1\n(Column Number = 2) AND (Response Answer = \"Challenges\") => -1\n\n0300: Value are aggregated similarly to 0205, while values in other columns are codified as:\n(Column Number = 3) AND (Response Answer = \"Complete\") => 1\n(Column Number = 3) AND (Response Answer = \"Operation\") => 1\n(Column Number = 3) AND (Response Answer = \"Monitoring and reporting\") => 1\n(Column Number = 3) AND (Response Answer = \"Implementation\") => 0.8\n(Column Number = 3) AND (Response Answer = \"Pre-implementation\") => 0.6\n(Column Number = 3) AND (Response Answer = \"Pre-feasibility study\") => 0.4\n(Column Number = 3) AND (Response Answer = \"Scoping\") => 0.2\n(Column Number = 4) AND (Response Answer = \"Implementation\") => 0.8\n(Column Number = 4) AND (Response Answer = \"Implementation complete but not in operation\") => 0.8\n(Column Number = 4) AND (Response Answer = \"Operation\") => 1\n(Column Number = 4) AND (Response Answer = \"Monitoring and reporting\") => 1\n(Column Number = 4) AND (Response Answer = \"Pre-implementation\") => 0.6\n(Column Number = 4) AND (Response Answer = \"Pre-feasibility study\") => 0.4\n(Column Number = 4) AND (Response Answer = \"Scoping\") => 0.2\n\n0402: Values are uniformed across years\n\n0406: Values are modified according to the following, in order to multiply dummies per category:\n(Column Number = 7) AND (Response Answer = \"High\") => 0.9\n(Column Number = 7) AND (Response Answer = \"Medium\") => 0.6\n(Column Number = 7) AND (Response Answer = \"Question not applicable\") => 0.6\n(Column Number = 7) AND (Response Answer = \"Low\") => 0.3\n(Column Number = 3) AND (Response Answer = \"High\") => 0.9\n(Column Number = 3) AND (Response Answer = \"Medium\") => 0.6\n(Column Number = 3) AND (Response Answer = \"Low\") => 0.3\n\n\n**0500**: Aggregation of indexes of 0501, 0502, 0503, 0504\nFor 0501, 0503, 0504\nAmbition\n\n$$ln(1+ ((PercentageReductionTarget)/(AveragePercentageReductionTarget))*((TargetYear -Year)/(AverageTargetYear - Year))$$\n\nEfficiency\n\n$$ln(1 + ((Year - 2017)/(TargetYear - TargetStart year))*((PercentageOfTargetAchievedSoFar/100)*(PercentageReductionTarget)))$$\n\nFor 0502\nAmbition\n\n$$ln(1 + ((Absolute Emissions Per Capita)/(Maximum Absolute Emissions Per Capita))*((Target Year - Year)/(Average Target Year) - Year)$$\n\nEfficiency\n\n$$ln(1 + ((Year - 2017)/(Target Year - Target Start Year))*((Percentage Of Target Achieved/100)))$$\n\n\n0602: Multiplicative index for 0603, similarly as before\n\nAll the other variables undergo rather standard and less arbitrary transformations.\n"},{"metadata":{},"cell_type":"markdown","source":"Data Mining\n---\n../input/cdp-citiesr/CDP cities.R\nSubsequently, the variables obtained by the described transformations are joined to form a dataset with each row corresponds to an observation, i.e. a city.\nAt this stage, we looked closely at the distribution of the data obtained and noticed that a critical variable such as GDP incurred both in extremely frequent missing values and, when present, unfeasible or incomparable values. Therefore, we decided to mine such data ourselves.\nFollowing the strong literature on the argument (e.g. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4112959/) we decided to use geospatial night data to use nocturne light as a proxy for GDP/economic development. In order to obtain the maximum amount of coordinates possible, we resorted to an R algorithm that, iteratively:\n\n* First, searched on Wikipedia for the city\n\n* Then, used the first geocoded result to obtain relevant coordinates for the vast majority of the cities of the sample\n\nWe matched the coordinates with the luminosity on a 13500x6750 nocturne satellite image by NASA (https://earthobservatory.nasa.gov/features/NightLights/page3.php): values were extracted as the average luminosity for a neighborhood of each coordinate, by excluding sea-related pixels. In this way, we obtain a reliable estimate of the city size and within-country variability. However, between country the values were rather unprecise. Consequently, we imported from the World Bank a country-level database of GDP per capita (https://data.worldbank.org/indicator/NY.GDP.PCAP.CD), and interacted it with luminosity: the result is an index that is capable of accounting for different development both across countries and across cities of the same country.\n\nWe then proceeded to download and match with the dataset the Gini Index by country (https://data.worldbank.org/indicator/SI.POV.GINI) and the Unemployment rate (https://data.worldbank.org/indicator/SL.UEM.TOTL.ZS), common social variables.\n\n"},{"metadata":{},"cell_type":"markdown","source":"Lastly, we constructed some KPIs with the variables obtained.\n\nComparable Effort Index features the 0500 Index calculated before, that measured quantitatively the GHG reduction targets for each city, scaled by the Gini Index and Luminosity x GDP per capita Index. It is meant to measure the effort in terms of climate for a city by creating a level playing field within cities, i.e. controlling for inequality and wealth.\n\nComparable effort\n\n$$ln(1 + GHG Emission Reduction Target  (Gini Index)/(Luminosity)  (GDP per capita))$$\n\nPPP (Public Private Partnership) measures the collaboration between the cities and any private sector of the economy.\n\nPPP\nln(1 + Sum Of Collaboration Score Across All Sectors)\n\nEnergy mix appropriateness measures whether the energy mix is sensible relative to the hazards faced by the city.\n\nEnergy mix appropriateness\n$$ln(1 +  (Total hazards*(Energy mix over renewable and nuclear sources))/(Energy mix over fossil sources))\nTotal hazards = ln(1 + Sum of hazard scores)$$\n\nRisks to Action ratio is similar to the previous index but measures the actions taken against climate change\n\nRisks-to-Actions ratio\n$$(Sum of (Number of Actions taken)/(Hazard score) over all hazards)^2$$"},{"metadata":{},"cell_type":"markdown","source":"https://imgur.com/9QaVe3f\nActions-to-Risks ratio\nLastly, we can look at the distribution of this KPI, which scores highest on cities that, given the severity of exposure to climatic risks, take the highest number of actions. The map shows that the indicator is again highest in Europe and the United States.\n\nhttps://imgur.com/qveF99K\nhttps://imgur.com/ILJNR7B\nhttps://imgur.com/PCNgWRK\nComparable efforts\nWe can see that this KPI correctly accounts for the different socio-economic conditions of cities. Cities in countries with low-income and low-Gini index are still assigned a high score, if their score in terms of ambition and efficiency is high, once their socio-economic disadvantage is taken into account.\nMost cities with a high KPI are still located in Europe and the United States.\n\n\nhttps://imgur.com/hClD8LO\nEnergy mix appropriateness\nCities with a high score in this KPI are shown in green, while cities with a low score are shown in red. Notice that cities that perform best according to this KPI are those facing the highest hazards but still managing to shift a great part of their energy mix towards renewable or nuclear resources.\n\nhttps://imgur.com/lxqzMkx\nhttps://imgur.com/kc9uu6Q\nPPP\nOnce again, the KPI for public and private collaboration scores highest in Europe and the United States. This KPI also seems to be partially associated with a lower Gini index.\n"},{"metadata":{},"cell_type":"markdown","source":"We apologize for the messiness of this report, it is our first time on Kaggle and wwe faced many technical inconveniences."},{"metadata":{"trusted":true},"cell_type":"code","source":"library(readr)\nlibrary(stringr)\nlibrary(plyr)\nlibrary(stringi)\nlibrary(jsonlite)\nlibrary(countrycode)\nlibrary(raster)\nlibrary(rgdal)\nlibrary(data.table)\n\n#Function to search for a string on Wikipedia\nMySearch <- function(srsearch){\n  FullSearchString <- paste(\"http://en.wikipedia.org/w/api.php?action=query&list=search&srsearch=\",srsearch,\"&format=json\",sep=\"\")\n  Response <- fromJSON(FullSearchString)\n  return(Response)\n}\n\n#Function to retreive the coordinates from the pageid of a Wikipedia page\nCoordSearch <- function(pageid){\n  FullSearchString<-paste(\"https://en.wikipedia.org/w/api.php?action=query&prop=coordinates&pageids=\",pageid,\"&format=json\",sep=\"\")\n  Response2 <- fromJSON(FullSearchString)\n  return(Response2)\n}\n\n#Import the dataset obtained from KNIME\nList_of_cities_country <- List_of_cities_country <- read_csv(\"../input/various-files/List of cities_country.csv\", \n                                                             col_names = FALSE, locale = locale(encoding = \"ISO-8859-1\"))\ndf = List_of_cities_country\n\n#Countries are taken back to their standard names\ndf$X2 = countrycode(as.character(df$X2), origin = \"wb\", destination = \"country.name.en\")\n#Country names are added to the main string\nfor (i in 1:nrow(df)){\n  df[i,1] = str_c(as.character(df[i,1]), as.character(df[i,2]), sep = \", \")\n}\nwrite.csv(df, \"output.csv\")\n","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"name":"ir","display_name":"R","language":"R"},"language_info":{"name":"R","codemirror_mode":"r","pygments_lexer":"r","mimetype":"text/x-r-source","file_extension":".r","version":"3.6.3"}},"nbformat":4,"nbformat_minor":4}