{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CDP challenge \n# Environmental Awareness as a Key to Success and Sustainability"},{"metadata":{},"cell_type":"markdown","source":"# PREAMBLE\n\nCDP is a global non-profit that drives companies and governments to reduce their greenhouse gas emissions, safeguard water resources, and protect forests. Each year, CDP takes the information supplied in its annual reporting process and scores companies and cities based on their journey through disclosure and towards environmental leadership.\n\nCDP houses the world’s largest, most comprehensive dataset on environmental action. As the data grows to include thousands more companies and cities each year, there is increasing potential for the data to be utilized in impactful ways. Because of this potential, CDP is excited to launch an analytics challenge for the Kaggle community. Data scientists will scour environmental information provided to CDP by disclosing companies and cities, searching for solutions to our most pressing problems related to climate change, water security, deforestation, and social inequity.\n\nHow do you help cities adapt to a rapidly changing climate amidst a global pandemic, but do it in a way that is socially equitable?\nWhat are the projects that can be invested in that will help pull cities out of a recession, mitigate climate issues, but not perpetuate racial/social inequities?\nWhat 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?\nHow can we measure the intersection between environmental risks and social equity, as a contributor to resiliency?\n\n**PROBLEM STATEMENT**\n\nDevelop a methodology for calculating key performance indicators (KPIs) that relate to the environmental and social issues that are discussed in the CDP survey data. Leverage external data sources and thoroughly discuss the intersection between environmental issues and social issues. Mine information to create automated insight generation demonstrating whether city and corporate ambitions take these factors into account."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cities19 <- read.csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\", header = T)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# *Be informed, ask questions, band together with your community, and fight at the local level. And make sure you take your local elections as seriously as the national ones.*\n***Erin Brockovich***"},{"metadata":{},"cell_type":"markdown","source":"![Painting by John Brosio](https://johnbrosio.com/wp-content/uploads/2019/09/family-portrait.jpg)"},{"metadata":{},"cell_type":"markdown","source":"# **Problem Statement**\n\nThis project is rather focused on a particular problem statement: \n\n***How do you help cities adapt to a rapidly changing climate amidst a global pandemic, but do it in a way that is socially equitable?***\n\nIn order to have an effective collaboration in handling environmental challenges, cities and corporations should at least share the same perspective on what these challenges are, on their importance and the ways they could be managed. Coincidence of expectations from both sides is an essential condition for mutually beneficial collaboration of cities and corporations in working on environmental challenges [3]. It is how corporations could help cities to adapt in a socially equitable way, understanding and meeting cities' unmet needs and taking measures that are relevant for the particular social context\n\nIn other words, reducing environmental risks in a socially acceptable and socially beneficial way is more likely if the initiative is understood and shared on the local level - if cities themselves are **aware** about the environmental risks, meaning the cities **do know** them, **are motivated** enough to handle them and actually **behave** in a manner to reduce them. Thus, developing methodology for measuring environmental awareness rate for cities is helpful to understand which cities are prepared and open for handling environmental challenges and  which ones need help, lacking some component of environmental awareness - either knowledge about environmental problem, motivation or action.\n\nFurther analysis is a proposal of methodology for measuring environmental awareness and mismatch of its components\n\nTherefore, as the result of the research below, a few KPIs will be developed and assessed for performance and accuracy:\n\n* KPI \"Environmental Awareness Score\"\n* KPI \"Pro-environmental behavior vs Environmental Knowledge rate\"\n\n\n! One more important disclaimer  should be made before the further analysis: the approach and methodology presented below can be genralized, yet in this reasearch they're based on 2019 Cities data. In Discussion part the ways of their broader application are described\n\n**The notebook is organized in accordance with the process of KPI construction and includes the following milestones [2]:**\n\n* Variables selection and preparation\n* Imputation of missing data\n* Variables Normalization\n* KPI modeling, incl. variables weighting and aggregation\n* KPI Performance and accuracy assessment"},{"metadata":{},"cell_type":"markdown","source":"# Data preparation and EDA\n\nAccodring to the recently developed theory on environmental awareness, there are 3 indispensible components of environmental awareness, including:\n\n* Knowledge\n* Motivation\n* Action\n\n\nAs a part of the following EDA we'll work with variables theoretically selected for potential contruction of KPIs (those variables that describe either knowledge about the environmental problems and risks, motivation (intention to take regulating measures, described in cities' strategy) or action (implementation of particular regulating measures))\n\n\nIn EDA we'll also make the research more focused - exploring countries' representation in the dataset and representation of cities types"},{"metadata":{},"cell_type":"markdown","source":"# Preparation of cities dataset: variables, country and type of cities selection\n\nThe majority of cities reported are US cities, therefore, for KPIs construction we'll focus on US cities\n\nThe majority of US cities disclosed are municipalities, therefore we'll  concentrate on them and include them in further research\n\nAs the result of questionnaire careful consideration, the number of questions was interpreted as describing knowledge, motivation or pro-environmental action (behavior) and, therefore, selected as the preferable ones for the upcoming KPIs construction and analysis\n\nVariables selected:\n\n* v0.1 (Type of city), v0.5 (Population)\n* Knowledge: v2.1 (Climate Hazards), v2.2 (Climate adaptation), v6.0 (City opportunities) - TEXT variables\n* Motivation: v1.0 (Sustainability goals), v3.1 (City council plan), v8.0 (Renewable enregy target), v8.6 (Increase of efficiency target) - variables with Lykert-type sclales of 5\n* Action: v3.0 (CIty actions) - TEXT variables\n\n"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"library(ggplot2)\nlibrary(dplyr)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ggplot(cities19) +\n  geom_bar(aes(y = Country)) + theme(text = element_text(size = 5))\n\ncities19_by_cntry <- cities19 %>%\n  group_by(Country) %>%\n  summarise(counts = n())\ncities19_by_cntry = as.data.frame(cities19_by_cntry)\nhead(cities19_by_cntry[order(-cities19_by_cntry$counts),])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"> # Selecting US cities"},{"metadata":{},"cell_type":"markdown","source":"Preparation of cities dataset includes filtering fo US municipalities, as well as the number of other steps, including change of dataset orientation with cities being presented in rows and variables in columns and additional work with text variables and recording"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#Selecting US cities and questions\nUS_cities19 <- filter(cities19, Country==\"United States of America\")\nUS_cities19 <- filter(US_cities19, Question.Number==\"0.1\"&Column.Number==1| Question.Number==\"0.5\"&Column.Number==1| Question.Number==\"2.1\"&Column.Number==12&Row.Number==0| Question.Number==\"2.1\"&Column.Number==12&Row.Number==1| Question.Number==\"2.2\"&Column.Number==3&Row.Number==0| Question.Number==\"2.2\"&Column.Number==3&Row.Number==1| Question.Number==\"6.0\"&Column.Number==2&Row.Number==0| Question.Number==\"6.0\"&Column.Number==2&Row.Number==1| Question.Number==\"1.0\"| Question.Number==\"3.1\"| Question.Number==\"8.0\"|Question.Number==\"8.6\"| Question.Number==\"3.0\"&Column.Number==6&Row.Number==0| Question.Number==\"3.0\"&Column.Number==6&Row.Number==1)\nt <- as.data.frame(unique(US_cities19$Organization))\nt <- as.data.frame(t[order(t[1]),])\nUS_cities19 <- filter(US_cities19, Question.Number==\"0.1\" | Question.Number==\"0.5\" | Question.Number==\"1.0\"| Question.Number==\"2.1\"| Question.Number==\"2.2\"| Question.Number==\"3.0\"| Question.Number==\"3.1\"| Question.Number==\"6.0\"| Question.Number==\"8.0\" | Question.Number==\"8.6\")\n\n#imputing NA in cells with missing data\nfor (i in 1:nrow(US_cities19)){\n  if (US_cities19[i, \"Response.Answer\"]==\"\"){\n    US_cities19[i, \"Response.Answer\"] <- NA\n  }\n}\n\n#Creating a vector with Cities' names\nUS19_df <- as.data.frame(matrix(ncol = 1, nrow = 171))\nx <- c(\"City\")\ncolnames(US19_df) <- x\nUS19_df$City <- t[,1]\n\n#Preparing data for further analysis\nfor (i in 1:nrow(US19_df)){\n  for (j in 1:nrow(US_cities19)){\n    if (US_cities19[j, \"Organization\"]==US19_df[i, \"City\"]){\n      qn <- as.character(US_cities19[j, \"Question.Number\"])\n      US19_df[i,qn] <- US_cities19[j, \"Response.Answer\"]\n    }\n  }\n}\n\n\nhead(US19_df, n=1)\n\n#In previous steps we've got a document-term matrix\n#Let us try to work with the text data\ntext_data <- US19_df[, c(\"2.1\", \"2.2\", \"3.0\", \"6.0\")]\n\n#Creating an empty data frame for sentiment analysis\ntext_data2 <- as.data.frame(matrix(ncol = 4, nrow = 171))\nx <- c(\"v2.1\", \"v2.2\", \"v3.0\", \"v6.0\")\ncolnames(text_data2) <- x","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"> # Text analysis\n\nApplying sentiment analysis to text variables selected with the use of afinn dictionary, we could derive sentiments - negative or positive evaluation by cities themselves of the current climate hazards, the opportunities that city has to decrease climate risks, the current adaptation of the city to these challenges and the current actions undertaken by city"},{"metadata":{"trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"library(dplyr)\nlibrary(tidytext)\nlibrary(tidyr)\nlibrary(textdata)\ndata(stop_words)\nafinn_df <- tibble(read.csv(\"../input/bing-nrc-afinn-lexicons/Afinn.csv\", header=T))\n#Sentiment Analysis - creating new variables\nfor (j in 1:ncol(text_data)){\n  for (i in 1:nrow(text_data)){\n    t <- tibble(text=as.character(text_data[i,j]))\n    tidy_text <- t %>%\n      unnest_tokens(word, text) %>%\n      anti_join(stop_words)\n    afinn <- tidy_text %>%\n      inner_join(afinn_df) %>% \n      group_by(word) %>% \n      summarise(sentiment = sum(value)) %>% \n      mutate(method = \"AFINN\")\n    text_data2[i,j] <- sum(afinn$sentiment)\n  }\n}\n\n#Final data frame we're going to use for further analysis\nnamevector <- c(\"v2.1\", \"v2.2\", \"v3.0\", \"v6.0\")\nUS19_df[, namevector] <- text_data2\n\nnew_names <- c(\"City\", \"City_adaptation\", \"Climat_hazards\", \"City_council_plan\", \"Sustainability_goals\", \"Type\", \"City_opportunity\", \"City_actions\", \"Population\", \"Renewable_energy_target\", \"Increase_of_efficiency_target\", \"Hazards_sentiment\", \"Adaptation_sentiment\", \"Actions_sentiment\", \"Opportunities_sentiment\")\ncolnames(US19_df) <- new_names","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"head(US19_df, n=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"After conducting sentiment analysis, we've got 4 new interval variables with the range from -5 to 5 for each text answer provided on each of 4 question. The values represent positive or negative evaluation of a particular matter"},{"metadata":{},"cell_type":"markdown","source":"> # Recoding variables with Lykert-type scale\n\nThe following step for data preparation is recording of motivation variables with Lykert-type scale"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#Recoding variables with Lykert-type scale\nfor (i in 1:nrow(US19_df)){\n  for (j in 1:ncol(US19_df)){\n    if (!is.na(US19_df[i, j])){\n      if (US19_df[i, j] == \"Not intending to incorporate\"){\n        US19_df[i, j] <- 1\n      } else if (US19_df[i, j]==\"Do not know\"){\n        US19_df[i, j] <- 2\n      } else if (US19_df[i, j]==\"Intending to incorporate in the next 2 years\"){\n        US19_df[i, j] <- 3\n      } else if (US19_df[i, j]==\"In progress\"){\n        US19_df[i, j] <- 4\n      } else if (US19_df[i, j]==\"Yes\"){\n        US19_df[i, j] <- 5\n      } else if (US19_df[i, j]==\"Not intending to undertake\"){\n        US19_df[i, j] <- 1\n      } else if (US19_df[i, j]==\"Intending to undertake in the next 2 years\"){\n        US19_df[i, j] <- 3\n      }\n    }\n  }\n}\n\n#We'll concentrate on cities / Municipalities\n#Changing the names of the cities \nUS19_df <- filter(US19_df, Type==\"City / Municipality\")\n\ncities <- read.csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Simple Maps US Cities Data/uscities.csv\")\ndictionary <- read.csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\ndictionary <- filter(dictionary, Country==\"United States of America\")\n\nhead(US19_df, n=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"> # County fips\n\nIn order to be able to visualize data properly and analyze maps, we'll match the cities included into research with county_fips and geo coordinates from the Supplementary Data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"for (i in 1:nrow(US19_df)){\n  for (j in 1:nrow(dictionary)){\n    if (US19_df[i, \"City\"]==dictionary[j, \"Organization\"]){\n      US19_df[i, \"city_name\"]<- dictionary[j, \"City\"]\n    }\n  }\n}\n\nfor (i in 1:nrow(US19_df)){\n  for (j in 1:nrow(cities)){\n    if (US19_df[i, \"city_name\"]==cities[j, \"city\"]){\n      US19_df[i, \"county_fips\"]<- as.character(cities[j, \"county_fips\"])\n    }\n  }\n}\n\nUS19_df2 <- filter(US19_df, county_fips!=\"NA\")\n\nfor (i in 1:nrow(US19_df2)){\n  for (j in 1:nrow(cities)){\n    if (US19_df2[i, \"county_fips\"]==cities[j, \"county_fips\"]){\n      US19_df2[i, \"long\"]<- cities[j, \"lng\"]\n      US19_df2[i, \"lat\"]<- cities[j, \"lat\"]\n    }\n  }\n}\n\nUS19_df3 <- as.data.frame(matrix(ncol=19, nrow=nrow(US19_df2)))\n\nUS19_df3[1] <- US19_df2[\"long\"]\nUS19_df3[2] <- US19_df2[\"lat\"]\nUS19_df3[, 3:19] <- US19_df2[, 1:17]\n\nnew_names <- c(\"long\", \"lat\", \"City\", \"City_adaptation\", \"Climat_hazards\", \"City_council_plan\", \"Sustainability_goals\", \"Type\", \"City_opportunity\", \"City_actions\", \"Population\", \"Renewable_energy_target\", \"Increase_of_efficiency_target\", \"Hazards_sentiment\", \"Adaptation_sentiment\", \"Actions_sentiment\", \"Opportunities_sentiment\", \"city_name\", \"county_fips\")\ncolnames(US19_df3) <- new_names","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"tail(US19_df3, n=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"> # Cities visualization \n\nThe coverage of cities, finally included in the study is really wide\n\nBoth West and East coasts, as well as the central part of the country, are covered\n"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# Libraries\nlibrary(ggplot2)\nlibrary(maps)\nlibrary(dplyr)\n \n# Get the world polygon and extract USA\nMainStates <- map_data(\"state\")\n\nggplot() + geom_polygon(data=MainStates, aes(x=long, y=lat, group=group),color=\"black\", fill=\"lightblue\") + \ngeom_point(data=filter(US19_df3, long>=-130), aes(x=long, y=lat)) +\n  coord_map(\"albers\",  at0 = 45.5, lat1 = 29.5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Imputation of missing data\n\nFrom the content point of view, the chosen theoretical framework presumes, that we study 3 social, environmentally-oriented phenomena that in sum let us measure and understand the environmental awareness: knowledge, motivation and pro-environmental behavior. Therefore the assumption that missing data in some of chosen variables is a sign of lacking knowledge, motivation or pro-environmental action seems reasonable. In other words, if no attitude on either of these variables was expressed it implies knowledge, motivation or pro-environmental action being equal to 0\n\nTherefore the selected technique for missing data imputation is its imputation with zero"},{"metadata":{},"cell_type":"markdown","source":"**Summary of all missing data**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"NA_summary <- as.data.frame(matrix(nrow = 22, ncol=1))\nfor (i in 1:ncol(US19_df3)){\n  NA_summary[i,\"Variable\"] <- colnames(US19_df3[i])\n  NA_summary[i, \"NA count\"] <- sum(is.na(US19_df3[i]))\n  NA_summary[i, \"NA percentage\"] <- sum(is.na(US19_df3[i]))/nrow(US19_df3)\n}\nNA_summary <- NA_summary[,2:4]\nhead(NA_summary)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Imputation of missing data with 0**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US19_df3[is.na(US19_df3)] <- 0\nhead(US19_df3, n=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Important and fair comment on the used technique for missing data: as far as some concern may arise about hadling missing data for text variables, it is important to notice that, while counting the sentiments NA have already been considered as neutral perception and coded as 0"},{"metadata":{},"cell_type":"markdown","source":"# Normalization of variables selected for the further analysis"},{"metadata":{},"cell_type":"markdown","source":"In this reserach min-max normalization was chosen for feature scaling"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"normalize <- function(x) {\nreturn ((x - min(x)) / (max(x) - min(x)))\n}\nUS19_df3[,\"Sustainability_goals\"] <- as.numeric(US19_df3[,\"Sustainability_goals\"])\nUS19_df3[,\"City_council_plan\"] <- as.numeric(US19_df3[,\"City_council_plan\"])\nUS19_df3[,\"Renewable_energy_target\"] <- as.numeric(US19_df3[,\"Renewable_energy_target\"])\nUS19_df3[,\"Increase_of_efficiency_target\"] <- as.numeric(US19_df3[,\"Increase_of_efficiency_target\"])\nUS19_df3[,\"Hazards_sentiment\"] <- as.numeric(US19_df3[,\"Hazards_sentiment\"])\nUS19_df3[,\"Adaptation_sentiment\"] <- as.numeric(US19_df3[,\"Adaptation_sentiment\"])\nUS19_df3[,\"Actions_sentiment\"] <- as.numeric(US19_df3[,\"Actions_sentiment\"])\nUS19_df3[,\"Opportunities_sentiment\"] <- as.numeric(US19_df3[,\"Opportunities_sentiment\"])\n\nUS19_df3[c(\"Sustainability_goals_n\", \"City_council_plan_n\", \"Renewable_energy_target_n\", \"Increase_of_efficiency_target_n\", \"Hazards_sentiment_n\", \"Adaptation_sentiment_n\", \"Actions_sentiment_n\", \"Opportunities_sentiment_n\")] <- normalize(US19_df3[c(\"Sustainability_goals\", \"City_council_plan\", \"Renewable_energy_target\", \"Increase_of_efficiency_target\", \"Hazards_sentiment\", \"Adaptation_sentiment\", \"Actions_sentiment\", \"Opportunities_sentiment\")])","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"tail(US19_df3, n=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Multivariate Anlysis"},{"metadata":{},"cell_type":"markdown","source":"Multivariate analysis (PCA in our case) is important as a checkpoint of subjectively selected variables \n\nThe question is whether they're really measuring what they were selected to be measuring"},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":false},"cell_type":"code","source":"matrix_for_pca <- US19_df3[, c(\"Sustainability_goals_n\", \"City_council_plan_n\", \"Renewable_energy_target_n\", \"Increase_of_efficiency_target_n\", \"Hazards_sentiment_n\", \"Adaptation_sentiment_n\", \"Actions_sentiment_n\", \"Opportunities_sentiment_n\")]\n\nfor (i in 1: ncol(matrix_for_pca)){\n  matrix_for_pca[,i] <- as.numeric(matrix_for_pca[,i])\n}\n\npca <- prcomp(matrix_for_pca, center = TRUE,scale. = TRUE)\n\nsummary(pca)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Fortunately, according to PCA, variables selected to measure knowledge fall in the same dimenstion. The same is almost true for motivation variables and action variable"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"library(factoextra)\nfviz_eig(pca)\n\nfviz_pca_var(pca,\n             col.var = \"contrib\", # Color by contributions to the PC\n             gradient.cols = c(\"#00AFBB\", \"#E7B800\", \"#FC4E07\"),\n             repel = TRUE     # Avoid text overlapping\n             )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"res.var <- get_pca_var(pca)\nres.var$contrib","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Weighting"},{"metadata":{},"cell_type":"markdown","source":"In order to decide on how the variables selected for inclusion in KPI should be weighted - with \"equal\" or differentiated weights described, the correlation between them should be also accounted for"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"library(ggcorrplot)\ncorr <- cor(US19_df3[c(\"Sustainability_goals_n\", \"City_council_plan_n\", \"Renewable_energy_target_n\", \"Increase_of_efficiency_target_n\", \"Hazards_sentiment_n\", \"Adaptation_sentiment_n\", \"Actions_sentiment_n\", \"Opportunities_sentiment_n\")])\nggcorrplot(corr)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There's weak positive correlation between almost all the variables\n\nYet, the \"motivation\" variables are slightly higher correlated\n\nWeak correlation is a good sign, proving that the selected variables are close, yet nor overlapping, measuring the same underlying effect"},{"metadata":{},"cell_type":"markdown","source":"**As for the weighting formula, the following theoretically defined weights for Environmental Awareness score could be proposed**\n\nThree parts of awareness - knowledge, motivation, action. Each explains ~30% of KPI\n\nKnowledge includes 3 variables, each of them explains 1/9 share of Environmental Awareness\n\nMotivation includes 4 variables, each of them explains 1/12 share of Environmental Awareness\n\nFinally there's 1 variable fo action selected, therefore, its weight equal 1/3 of Environmental Awareness"},{"metadata":{},"cell_type":"markdown","source":"$$ Environmental\\,awareness\\,score = \\frac {1} {3} * Knowledge + \\frac {1} {3} * Motivation + \\frac {1}{3} * Proenvironmental\\,Behavior $$"},{"metadata":{},"cell_type":"markdown","source":"$$ Knowledge = \\frac {1} {9} * Hazards\\,sentiment + \\frac {1} {9} * City\\,adaptation + \\frac {1}{9} * City\\,opportunities $$"},{"metadata":{},"cell_type":"markdown","source":"$$ Motivation = \\frac {1} {12} * Council\\,plan + \\frac {1} {12} * Sustainability\\,goals + \\frac {1}{12} * Renewable\\,energy\\,target + \\frac {1} {12} * Energy \\,efficiency\\,target $$"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"library(\"ggplot2\")\ndf <- data.frame(a = c(1, 1/3, 1/3, 1/3, 1/3, 1/9, 1/9, 1/9, 1/12, 1/12, 1/12, 1/12),\n                 b = c(\"x\", \"y\", \"y\", \"y\", \"z\", \"z\", \"z\", \"z\", \"z\", \"z\", \"z\", \"z\"),\n                 c = c(\"1 Environmental awareness\", \"2.1 Skills - 30%\", \"2.2 Knowledge - 30%\", \"2.3 Motivation - 30%\", \"3.1 City actions - 30%\", \"3.2 Climate hazards - 11,3%\", \"3.3 City adaptation - 11,3%\", \"3.4 City opportunities - 11,3%\", \"3.5 City have a target to increase energy efficiency - 8,2%\", \"3.6 Sustainability goals - 8,2%\", \"3.7 City council plan - 8,2%\", \"3.8 City has a renewable energy or electricity target - 8,2%\"))\nggplot(df, aes(x = b, y = a, fill = c))+\n  geom_bar(stat = \"identity\")+\n  coord_polar(theta=\"y\") ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Modelling. KPIs contruction"},{"metadata":{},"cell_type":"markdown","source":"Variables chosen for KPI construction may be aggregated in a disctinct ways - either with linear aggregation method or with geometric aggregation method "},{"metadata":{},"cell_type":"markdown","source":"# **Linear aggregation method**\n\n$$KPI = \\sum_{i=1}^{n} w_i * SubIndicator_i$$"},{"metadata":{},"cell_type":"markdown","source":"**Option 1** for building an Environmental Awareness Score.\n\nLinear aggregation method - summing up sub-indicators with theoretically based weights"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US19_df3[\"Environmental_awareness\"] <- (1/12)*US19_df3$Sustainability_goals_n + (1/12)*US19_df3$City_council_plan_n + (1/12)*US19_df3$Renewable_energy_target_n + (1/12)*US19_df3$Increase_of_efficiency_target_n + (1/9)*US19_df3$Hazards_sentiment_n + (1/9)*US19_df3$Adaptation_sentiment_n + (1/3)*US19_df3$Actions_sentiment_n + (1/9)*US19_df3$Opportunities_sentiment_n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"head(US19_df3[, c(\"City\", \"Environmental_awareness\")])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **Geometric aggregation method**\n\n$$ KPI = exp(\\frac {\\sum_{i=1}^{n} w_i * ln(SubIndicator_i)} {\\sum_{i=1}^{n} w_i}) $$"},{"metadata":{},"cell_type":"markdown","source":"**Option 2** for building an Environmental Awareness Score.\n\nGeometric aggregation method - summing up sub-indicators with theoretically based weights"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US19_df3[\"Environmental_awareness2\"] <- exp(((1/12)*log(US19_df3$Sustainability_goals_n) + (1/12)*log(US19_df3$City_council_plan_n) + (1/12)*log(US19_df3$Renewable_energy_target_n) + (1/12)*log(US19_df3$Increase_of_efficiency_target_n) + (1/9)*log(US19_df3$Hazards_sentiment_n) + (1/9)*log(US19_df3$Adaptation_sentiment_n) + (1/3)*log(US19_df3$Actions_sentiment_n) + (1/9)*log(US19_df3$Opportunities_sentiment_n))/(1/12+1/12+1/12+1/12+1/9+1/9+1/9+1/3))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Environmental awareness score distribution**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"m<-mean(US19_df3$Environmental_awareness)\nstd<-sqrt(var(US19_df3$Environmental_awareness))\n\nhist(US19_df3$Environmental_awareness, col = \"lightgreen\")\ncurve(dnorm(x, mean=m, sd=std), \n      col=\"darkgreen\", lwd=2, add=TRUE, yaxt=\"n\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# KPIs for Environmental Knowledge, Environmental Motivation and Pro-Environmental Behavior"},{"metadata":{},"cell_type":"markdown","source":"Separated KPIs for knowledge, motivation, action "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US19_df3[\"knowledge\"] <- (US19_df3$Hazards_sentiment_n + US19_df3$Adaptation_sentiment_n + US19_df3$Opportunities_sentiment_n)\n\nUS19_df3[\"motivation\"] <- (US19_df3$Sustainability_goals_n + US19_df3$City_council_plan_n + US19_df3$Renewable_energy_target_n + US19_df3$Increase_of_efficiency_target_n)\n\nUS19_df3[\"skills\"] <- (US19_df3$Actions_sentiment_n)\n\nnormalize <- function(x) {\nreturn ((x - min(x)) / (max(x) - min(x)))\n}\nUS19_df3[,\"knowledge\"] <- as.numeric(US19_df3[,\"knowledge\"])\nUS19_df3[,\"motivation\"] <- as.numeric(US19_df3[,\"motivation\"])\nUS19_df3[,\"skills\"] <- as.numeric(US19_df3[,\"skills\"])\n\nUS19_df3[c(\"knowledge_n\", \"motivation_n\", \"skills_n\")] <- normalize(US19_df3[c(\"knowledge\", \"motivation\", \"skills\")])\n\nlibrary(ggplot2)\nc <- US19_df3$skills_n\nggplot(US19_df3, aes(x=knowledge_n, y=skills_n)) +\n  geom_point(aes(colour = motivation_n)) + geom_point(shape = 1, colour = \"darkgray\") + scale_colour_gradient2(low=\"#22FF00\", mid=\"gray\", high=\"#FF0000\", midpoint=median(US19_df3$skills_n))+\n  geom_smooth(method=lm)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"**As the scatterplot shows, there's no much difference in motivation for pro-environmental behavior between US cities, yet there's difference in their evaluation of their knowledge about environmental problems and risks, as well as in their evaluation of their pro-enviromental behavior**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"library(ggcorrplot)\ncorr <- cor(US19_df3[,c(\"knowledge_n\", \"motivation_n\", \"skills_n\")])\nggcorrplot(corr)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**A comment on accuracy of the KPIs for environmental knowledge, motivation to pro-environmental action and pro-environmental action: There' re no strong correlations between the KPIs constructed, which is good, meaning they're measuring different things - distinct dimensions of environmental awareness**"},{"metadata":{},"cell_type":"markdown","source":"# US cities could be ranked and pictured according to their Environmental Awareness Score"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ggplot(US19_df3, aes(x=reorder(City, Environmental_awareness), y=Environmental_awareness)) +\n  geom_bar(stat=\"identity\", colour=\"lightgreen\") + coord_flip() + theme(text = element_text(size = 4))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# Libraries\nlibrary(ggplot2)\nlibrary(maps)\nlibrary(dplyr)\n \n# Map\nMainStates <- map_data(\"state\")\n\nggplot() + geom_polygon(data=MainStates, aes(x=long, y=lat, group=group),color= \"gray\", fill=\"white\") + \ngeom_point(data=filter(US19_df3, long>=-130), aes(x=long, y=lat,color = Environmental_awareness), alpha=1) +\nscale_colour_gradient2(low=\"yellow\", mid=\"darkgray\", high=\"purple\", midpoint=median(US19_df3$Environmental_awareness)) +\ncoord_map(\"albers\",  at0 = 45.5, lat1 = 29.5) + labs(title = \"Environmental awareness score across the US\") +\ntheme(legend.position = \"left\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Pro-environmental behavior vs Environmental Knowledge rate"},{"metadata":{},"cell_type":"markdown","source":"One more KPI that could be useful in detecting US cities that are in need for help from corporations. The so-called \"Pro-environmenatal behavior vs Environmental Awareness rate\" shows the relation of Environmental Knowledge to Pro-environmental behavior. Therefore, if the rate is high, it means, cities share knowledge about the environmental challenges, yet are lacking in terms of pro-environmental behavior - in undertaking the real mesures to fight the environmental problem. Which is a red flag, a signal that corporations could be of help, introducing the real practice of pro-environmental behavior into a city"},{"metadata":{},"cell_type":"markdown","source":"$$ Pro-environmental\\,behavior\\,vs\\,Environmental\\,Knowledge\\,rate = \\frac {Environmental\\,Knowledge} {Pro-environmental\\,Behavior} $$"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US19_df3$difference_rate <- US19_df3$knowledge_n / US19_df3$skills_n\n\n# Libraries\nlibrary(ggplot2)\nlibrary(maps)\nlibrary(dplyr)\n \n# Map\nMainStates <- map_data(\"state\")\n\nggplot() + geom_polygon(data=MainStates, aes(x=long, y=lat, group=group),color= \"gray\", fill=\"white\") + \nscale_colour_gradient2(low=\"#22FF00\", mid=\"darkgray\", high=\"#FF0000\", midpoint=median(US19_df3$difference_rate)) +\ngeom_point(data=filter(US19_df3, long>=-130), aes(x=long, y=lat, size = Environmental_awareness, color = difference_rate), alpha=0.9) +\ncoord_map(\"albers\",  at0 = 45.5, lat1 = 29.5) + labs(title = \"Environmental awareness score across the US\") +\ntheme(legend.position = \"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"Though, as we can see from the plot above, a lot of US cities share similar environmental awareness scores, they differ in terms of \"Pro-environmental behavior vs Environmental Knowledge rate\""},{"metadata":{},"cell_type":"markdown","source":"# Basic sensitivity analysis for \"Environmental awareness score\" as an accuracy measure (linear vs geometric aggregation)"},{"metadata":{},"cell_type":"markdown","source":"Despite the fact that a few preliminary steps of evaluating the developed KPIs performance and accuracy has already been undertaken (such as PCA, correlations, etc), more diligent check-up is still needed.\n\nThus, in order to clarify one of the most dubious steps in \"Environmental Awareness score\" construction - the choice of aggregation method - we'll provide the basic sensitivity analysis based on linear regression"},{"metadata":{},"cell_type":"markdown","source":"Sensitivity analysis of the \"Environmental awareness score\" counted with linear aggregation method"},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"US_sorted <- arrange(US19_df3, desc(Environmental_awareness))\nUS_sorted$Rank <- NA\nfor (i in 1:nrow(US_sorted)){\n  US_sorted[i, \"Rank\"] <- as.numeric(i)\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"lm_lin <- lm(Rank ~ Environmental_awareness, US_sorted)\nsummary(lm_lin)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Sensitivity analysis of the \"Environmental awareness score\" counted with geometric aggregation method"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"US_sorted2 <- arrange(US19_df3, desc(Environmental_awareness2))\nUS_sorted$Rank <- NA\nfor (i in 1:nrow(US_sorted2)){\n  US_sorted2[i, \"Rank\"] <- as.numeric(i)\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"lm_geom <- lm(Rank ~ Environmental_awareness2, US_sorted2)\nsummary(lm_geom)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For both models the dependent variable - is Rank of the city, defined based on the specific environmental awareness score\n\nIndependent variable in each of the models is the score itself with either linear or geometric aggregation method\n\nThough the current research was totally based on \"Environmental Awareness score\" with linear aggregation, the analysis shows that the impact on the Rank variable is higher for  geometric aggregation which gives more ideas for the further KPI correction and development "},{"metadata":{},"cell_type":"markdown","source":"# Discussion & Conclusion"},{"metadata":{},"cell_type":"markdown","source":"* \"Environmental awareness score\" and \"Pro-environmental behavior vs Environmental Knowledge rate\" are KPIs, attempting to provide a slightly new perspective on the intersection between environmental and social issues. The rationale behind developing such measures is that the positive environmental change cannot be imposed - neither by companies, nor by government, nor by any other agent. Successful and safe decrease of environmental risks starts with awareness on the local level - with awareness of cities, including their knowledge about the existing environmental problems, their motivation to handle them, represented in targets they announce in their development plans, and their real action - their real pro-environmental behavior. The methodology proposed for cities is inspired by the work on environmental awareness composite indicator development fr 57 countries, conducted by Pekka Harju-Autti and Eevi Kokkinen in 2014 [1]\n\n* The methodology also proposes one more important innovation - automated insight generation. Including the results of text variables sentiment analysis in KPIs is beneficial, as it puts things into perspective. Thus, the measurement contains the cities self-evaluation of the environmental knowledge and pro-environmental behavior, demostrating the current attitude of the city to environmental risks\n\n* Surely, there're things that need a more carefull consideration and a more diligent validation. Among those things are weigting and aggregation methods selected for KPIs construction. Though weighting was rather a theoretically based procedure with equal weights precribing to all sub-indicators in each of 3 dimensions of environmental awareness - to knowledge, motivation, behavior sub-indicators, - it still can be checked better via more sophisticated sensitivity analysis, and corrected if needed. As for the chosen aggregation method, though linear aggreagation with summing up the sub-indices seems to be more appealing and understandable, it leads to the partial compensation of sub-indicators and, therefore, doesn't provide us with the full picture - ranking equally, for example, cities with high knowledge, low pro-environmental behavior and cities with low knowledge and high implementation of pro-environmental actions. To correct this, either deriving of additional KPIs needed (such as \"Pro-environmental behavior vs Environmental Knowledge rate\"), or consideration of alternative aggregation method, as the conductd sensitity analysis supposes\n\n* Finally, the structure of measuring awareness as an approach of some agent to some environmental challenge could be used much broader. For instance, the same KPIs could be built for corporations with the usage of the same variables from their questionnaires. Later, insteresting comparison of KPIs levels achieved by corporations with location in particular cities and the levels of KPIs achieved by these cities themeselves could be outlined."},{"metadata":{},"cell_type":"markdown","source":"# References"},{"metadata":{},"cell_type":"markdown","source":"1. Harju-Autti, Pekka and Eevi Kokkinen. “A Novel Environmental Awareness Index Measured Cross-Nationally For Fifty Seven Countries.” (2014).\n\n2. OECD. \"Handbook on Constructing Composite Indicators. METHODOLOGY AND USER GUIDE\". (2008).\n\n3. Cities and CLimate Change. URL: https://www.unenvironment.org/explore-topics/resource-efficiency/what-we-do/cities/cities-and-climate-change"}],"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}