{"cells":[{"metadata":{},"cell_type":"markdown","source":"## Load packages"},{"metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"install.packages(\"RTextTools\")\nlibrary(dplyr)\nlibrary(ggplot2)\nlibrary(RTextTools)\nlibrary(h2o)\nlibrary(caret)\n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Part 1: Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Data from : https://ourworldindata.org/grapher/annual-co2-emissions-per-country?tab=chart\nannual.co2.emissions.per.country <- read.csv(\"../input/co2-emissions/annual-co2-emissions-per-country.csv\")\n\ncities_responses_2020 <- read.csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n\ncities_disclosing_2020 <-read.csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporations_responses_2020 <-read.csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv\")\n\n\ncorporations_disclosing_2020 <-read.csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Part 2 : Research Question\n\nWe want to view the opportunities of cities and corporate corporations identified against climate change through the functions:\n\n* printCityOrganization (country, org)\n* printOpportunitiesCorporation (org)\n\nand we want to build a model to predict the type of response that a city organization gives to the question:\n\nDoes your city collaborate in partnership with businesses in your city on sustainability projects?\n\nwith the following possible answers:\n\n* Yes\n* In progress\n* Intending to undertake in the next 2 years\n* Not intending to undertake\n* Do not know"},{"metadata":{},"cell_type":"markdown","source":"## Part 3 : Exploratory Data Analysis"},{"metadata":{"trusted":true,"_kg_hide-output":false},"cell_type":"code","source":"annual.co2.emissions.per.country %>%\n  filter(Year==2018 & !Entity %in% c(\"World\",\"Asia and Pacific (other)\",\"EU-28\",\"Middle East\",\"Americas (other)\",\"Europe (other)\",\"International transport\",\"Statistical differences\",\"Africa\",\"Asia (excl. China & India)\",\"EU-28\" ,\"Europe\",\"Europe (excl. EU-27)\" ,\"Europe (excl. EU-28)\" ,\"International transport\" ,\"North America (excl. USA)\"  ,\"North America\", \"Non KP Annex B\"  ,\"Non KP Annex B\",\"Non-OECD\",\"OECD\" ,\"KP Annex B\",\"Asia\",\"EU-27\" ,\"South America\" )) %>%\n  mutate(Entity=reorder(Entity,Annual.CO2.emissions))  %>%\n  top_n(40) %>%\n   ggplot(aes(Entity,Annual.CO2.emissions, fill=Entity))+\n  geom_bar(stat=\"identity\")+\n  coord_flip()+\n  guides(fill=FALSE)+\n  geom_text(aes(label=round(Annual.CO2.emissions,2)), hjust=0, size=2)+\n  ylab(\"CO2 milions tonnes\") +\n  xlab(\"Country\") +\n  ggtitle(\"CO2 emissions per Country in 2018\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":false},"cell_type":"code","source":"cities_disclosing_2020 %>%\n  group_by(Country) %>%\n  summarise(total=n()) %>%\n  mutate(Country=reorder(Country,total))  %>%\n  top_n(40) %>%\n   ggplot(aes(Country,total, fill=Country))+\n  geom_bar(stat=\"identity\")+\n  coord_flip()+\n  guides(fill=FALSE)+\n  geom_text(aes(label=total), hjust=0, size=2)+\n  ylab(\"Total\") +\n  xlab(\"Country\") +\n  ggtitle(\"Cities organizations disclosing in 2020 by Country\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":false},"cell_type":"code","source":"corporations_disclosing_2020 %>%\n  group_by(country) %>%\n  summarise(total=n()) %>%\n  mutate(country=reorder(country,total))  %>%\n  ggplot(aes(country,total, fill=country))+\n  geom_bar(stat=\"identity\")+\n  coord_flip()+\n  guides(fill=FALSE)+\n  geom_text(aes(label=total), hjust=0, size=3)+\n  ylab(\"Total\") +\n  xlab(\"Country\") +\n  ggtitle(\"Corporations disclosing in 2020 per Country\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printOpportunitiesCorporation <- function(org){\n  df3<-corporations_disclosing_2020 %>%\n        filter(organization==org) %>%\n        select(country, primary_industry,primary_sector,primary_activity)\n          \n          cat(\"COUNTRY:\",as.character(df3$country),\"CORPORATION: \",org,\"\\n\\n\")\n  \n          cat(paste(\"PRIMARY INDUSTRY:\",df3$primary_industry,\", PRIMARY SECTOR:\",df3$primary_sector,\", PRIMARY ACTIVITY:\",df3$primary_activity,\"\\n\\n\")) \n  \n  \n  df2<-corporations_responses_2020 %>%\n        filter(organization==org, question_number==\"C2.4a\") %>%\n        select(question_unique_reference,response_value)\n      \n      cat(org,\"OPPORTUNITIES :\\n\\n\")\n      cat(\"QUESTION: \",as.character(unique(df2$question_unique_reference)),\"\\n\\n\")\n      print(paste(\"RESPONSE: \",df2$response_value))\n      \n\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printPartnership <- function(df){\n\n      if(nrow(df)!=0) {\n      \n          for (org in df$organization) {\n            \n              printOpportunitiesCorporation(org)\n              \n              cat(\"\\n\\n******************************************************\\n\\n\")\n          }\n      \n      }\n\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printCorporations <- function(c,opportunity){\n  cat(\"\\n\\nCORPORATIONS BY OPPORTUNITIES:\\n\\n\")\n  if (opportunity %in% c(\"Development of clean energy technologies/businesses\",\"Development of energy efficiency measures and technologies\",\"Increased energy security\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry==\"Power generation\" , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  }\n  else if (opportunity %in% c(\"Development of waste management sector\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_activity ==\"Waste management\" , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  }  \n  else if (opportunity %in% c(\"Development of tourism industry and eco-tourism sector\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry ==\"Hospitality\" , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  } \n  else if (opportunity %in% c(\"Development of sustainable transport sector\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry ==\"Transportation services\"  , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  } \n  else if (opportunity %in% c(\"Development of sustainable construction/real estate sector\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_sector ==\"Construction\"   , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n  } \n  else if (opportunity %in% c(\"Development of local sustainable food businesses\",\"Increased food security\",\"Extended agricultural seasons\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry ==\"Food, beverage & agriculture\"   , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  } \n  else if (opportunity %in% c(\"Increased opportunities for investment in infrastructure projects\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry ==\"Infrastructure\" , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  } \n  else if (opportunity %in% c(\"Increased opportunities for trade (nationally or internationally)\")){\n    cat(toupper(opportunity),\"\\n\\n\")\n    df2<-corporations_disclosing_2020 %>%\n        filter(primary_industry ==\"Retail\" , country==c) %>%\n        select(organization,primary_industry,primary_sector,primary_activity)\n    \n  } \n  printPartnership(df2)\n}     ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printCountryOpportunities <- function(country) {\n  \n\n  df_org<-cities_responses_2020 %>%\n    filter(Question.Number==\"6.0\", Column.Name==\"Opportunity\", Country==country, ) %>%\n    select(Organization)\n  \n  \n  for (org in unique(df_org$Organization)) {\n    df<-cities_responses_2020 %>%\n       filter(Question.Number ==\"6.0\", Column.Name==\"Opportunity\", Country==country, Organization==org) %>%\n       select(Question.Name, Response.Answer)\n    \n    df_city<- cities_disclosing_2020 %>%\n      filter(Country==country,Organization==org) %>%\n      select(City)\n    city <-df_city$City\n    \n    if (city==\"\") next\n    \n    cat(\"_____________________________________________________________\\n\\n\")\n    cat(paste(\"COUNTRY:\",country,\", CITY:\",city,\", ORGANIZATION:\",org,\"\\n\\n\"))\n    \n    cat(\"QUESTION: \",unique(as.character(df$Question.Name)),\"\\n\\n\")\n    cat(\"OPPORTUNITIES:\\n\\n\")\n    opportunities <-unique(as.character(df$Response.Answer))\n    print(opportunities)\n    \n    cat(\"\\n\\n\")\n  }\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printCityOrganization <- function(country, org) {\n      df<-cities_responses_2020 %>%\n         filter(Question.Number ==\"6.0\", Column.Name==\"Opportunity\", Country==country, Organization==org) %>%\n         select(Question.Name, Response.Answer)\n      \n      df_city<- cities_disclosing_2020 %>%\n        filter(Country==country,Organization==org) %>%\n        select(City)\n      city <-df_city$City\n      \n      if (city!=\"\") {\n      \n          cat(\"_____________________________________________________________\\n\\n\")\n          cat(paste(\"COUNTRY:\",country,\", CITY:\",city,\", ORGANIZATION:\",org,\"\\n\\n\"))\n          \n          cat(\"QUESTION: \",unique(as.character(df$Question.Name)),\"\\n\\n\")\n          cat(\"OPPORTUNITIES:\\n\\n\")\n          opportunities <-unique(as.character(df$Response.Answer))\n          print(opportunities)\n          \n          cat(\"\\n\\n\")\n      }\n    }","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#printOpportunitiesCorporation(\"TransAlta Corporation\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#printCountryOpportunities(\"China\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printCountryOpportunities(\"China, Hong Kong Special Administrative Region\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#printCorporations(\"United States of America\",\"Development of clean energy technologies/businesses\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"printCityOrganization(\"China\",\"Chengdu Municipal Government\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Part 4: Modeling\n\nA dataset is built with the cities' answers to the questions:\n\n6.0 Please indicate the opportunities your city has identified as a result of addressing climate change and describe how the city is positioning itself to take advantage of these opportunities.\n\nSelect from:\n* Development of clean energy technologies / businesses Text field\n* Development of energy efficiency measures and technologies\n* Development of waste management sector\n* Development of water management sector\n* Development of circular economy models and businesses\n* Development of tourism industry and eco-tourism sector\n* Development of sustainable transport sector\n* Development of sustainable construction / real estate sector\n* Development of local sustainable food businesses\n* Increased opportunities for investment in infrastructure projects\n* Increased opportunities for trade (nationally or internationally)\n* Additional funding opportunities\n* Increase opportunities for partnerships\n* Improved flood risk mitigation\n* Increased water security\n* Increased energy security\n* Development of climate change resiliency projects\n* Increased food security\n* Extended agricultural seasons\n* Reduced risk to natural capital\n* Reduced risk to human health\n* Creation / development of carbon markets\n* Carbon tax revenue\n* Development of resource conservation and management\n* Improved efficiency of municipal operations\n* Other, please specify\n* No opportunities identified\n\n6.2 Does your city collaborate in partnership with businesses in your city on sustainability projects?\n\n* Yes\n* In progress\n* Intending to undertake in the next 2 years\n* Not intending to undertake\n* Do not know\n\n\nThe following numerical variables are added to the dataset: current population of the city and size of the territory in square km.\n\nThe answer to question 6.2 is the target to predict.\n\nIn the sample, the total per response is given by the following plot:\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"df<-cities_responses_2020 %>%\n       filter(Question.Number ==\"6.2\") %>%\n       select(Country,Organization,Response.Answer)\n\ndf$Response.Answer <- as.factor(as.character(df$Response.Answer))\n\ndf %>%\n  group_by(Response.Answer) %>%\n  summarise(total=n()) %>%\n  mutate(Response.Answer=reorder(Response.Answer,total))  %>%\n  ggplot(aes(Response.Answer,total, fill=Response.Answer))+\n  geom_bar(stat=\"identity\")+\n  coord_flip()+\n  guides(fill=FALSE)+\n  geom_text(aes(label=total), hjust=0, size=2)+\n  ylab(\"Total\") +\n  xlab(\"Answer\") +\n  ggtitle(\"Does your city collaborate in partnership with businesses\",subtitle =  \" in your city on sustainability projects?\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### H2o Model"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities <- cities_disclosing_2020\ndf<-cities_responses_2020 %>%\n       filter(Question.Number ==\"0.5\", Column.Name==\"Current population\") %>%\n       select(Country,Organization,Response.Answer)\ndf_cities <- merge(df_cities,df, by = c(\"Country\",\"Organization\"))\ndf_cities$Response.Answer <- as.numeric(as.character(df_cities$Response.Answer)) \ncolnames(df_cities)[14] <- \"Current population\"\n\n\ndf<-cities_responses_2020 %>%\n       filter(Question.Number ==\"0.6\", Column.Name==\"Land area of the city boundary as defined in question 0.1 (in square km)\") %>%\n       select(Country, Organization,Response.Answer)\ndf_cities <- merge(df_cities,df, by = c(\"Country\",\"Organization\"))\ndf_cities$Response.Answer <- as.numeric(as.character(df_cities$Response.Answer)) \ncolnames(df_cities)[15] <- \"Land area square Km\"\n\n\ndf<-cities_responses_2020 %>%\n       filter(Question.Number ==\"6.2\") %>%\n       select(Country,Organization,Response.Answer)\ndf_cities <- merge(df_cities,df, by = c(\"Country\",\"Organization\"))\ncolnames(df_cities)[16] <- \"target\"\ndf_cities$target <- as.factor(as.character(df_cities$target))\n\n\ndf<-cities_responses_2020 %>%\n       filter(Question.Number ==\"6.0\", Column.Name==\"Opportunity\") %>%\n       select(Country,Organization,Response.Answer)\ndf_cities <- merge(df_cities,df, by = c(\"Country\",\"Organization\"))\ncolnames(df_cities)[17] <- \"opportunity\"\ndf_cities$opportunity <- as.character(df_cities$opportunity)\ndf_cities$Country <- as.character(df_cities$Country)\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The non-existent responses from the target are deleted:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities <-df_cities[-which(df_cities$target==\"\"),]\ndf_cities$target <- as.factor(as.character(df_cities$target)) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The h2o model is created:"},{"metadata":{"trusted":true},"cell_type":"code","source":"h2o.init()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We build a training set made from 70% of the observations on which the model is trained and a testing set made from the remaining 30% of observations on which the model is tested:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train <- createDataPartition(df_cities$target,p=0.7,list = FALSE)\ntraining_set <- df_cities[train,]\ntesting_set <- df_cities[-train,]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_hf <- as.h2o(training_set)\ny <- \"target\"\nx <- names(training_set)[c(1,14,15,17)]\n\naml <- h2o.automl(x = x, y = y,\n                  training_frame = df_hf,\n                  max_runtime_secs = 30)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"lb <- aml@leaderboard\nlb","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The leader model found is tested on the testing set:"},{"metadata":{"trusted":true},"cell_type":"code","source":"test <- as.h2o(testing_set)\nmodel <- aml@leader\np1 = h2o.predict(model, newdata=test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The accuracy is calculated on the testing set:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df2 <- as.data.frame(p1$predict)\n\npaste(\"Accuracy=\", mean(df2$predict==testing_set$target))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The confusion matrix is constructed :"},{"metadata":{"trusted":true},"cell_type":"code","source":"confusionMatrix(df2$predict,testing_set$target)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Sentiment Analysys Model:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df1<-cities_responses_2020 %>%\n         filter(Question.Number ==\"6.0\", Column.Name==\"Opportunity\", ) %>%\n         select(Country,Organization, Response.Answer)\n\ndf2<-cities_responses_2020 %>%\n         filter(Question.Number ==\"6.2\") %>%\n         select(Country,Organization, Response.Answer)\n\n\ndf1 <- merge(df1,df2 , by = c(\"Country\",\"Organization\"))\ncolnames(df1)[3:4] <- c(\"opportunity\",\"target\")\ndf1$opportunity <- as.factor(as.character(df1$opportunity))\ndf1$target <- as.factor(as.character(df1$target))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"head(df1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A sentyment analysis model is built through a Boosting algorithm to predict the answer to the question:\n\n6.2 Does your city collaborate in partnership with businesses in your city on sustainability projects?\n\nIn particular, the training set is 1500 observations, while the testing set is 1077 observations.\n\nThe data on which the sentyment analysis model is built are: Country and opportunity of the dataset df1."},{"metadata":{"trusted":true},"cell_type":"code","source":"data <- df1[sample(1:2577,size=2577,replace=FALSE),]\nmatrix <- create_matrix(cbind(data[\"Country\"],data[\"opportunity\"]), language=\"english\", \nremoveNumbers=TRUE, stemWords=FALSE)\ncontainer <- create_container(matrix,as.numeric(data$target),trainSize=1:1500, testSize=1501:2577, \nvirgin=FALSE)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"models = train_models(container, algorithms=c(\"BOOSTING\"))\n\nresults = classify_models(container, models)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"analytics = create_analytics(container, results)\nsummary(analytics)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As you can see, the Accuracy of the forecast is 82.91%"},{"metadata":{"trusted":true},"cell_type":"code","source":"paste(\"Accuracy=\",mean(analytics@document_summary$MANUAL_CODE==\nanalytics@document_summary$LOGITBOOST_LABEL))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Part 6: Prediction"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_test <- data_frame(Country=\"Canada\",`Current population`=74003,`Land area square Km`=329, opportunity=\"Development of sustainable construction/real estate sector\")\n\ndf_test <- as.h2o(df_test)\np1 = h2o.predict(model, newdata=df_test)\n\np2<-as.character(p1$predict)\n\nhead(df_test)\npaste(\"Does your city collaborate in partnership with businesses in your city on sustainability projects?\", p2$predict[1,1])\n\n\ndf_test <- data_frame(Country=\"Turkey\",`Current population`=482713,`Land area square Km`=25.2, opportunity=\"Reduced risk to human health\")\n\ndf_test <- as.h2o(df_test)\np1 = h2o.predict(model, newdata=df_test)\n\np2<-as.character(p1$predict)\n\nhead(df_test)\npaste(\"Does your city collaborate in partnership with businesses in your city on sustainability projects?\", p2$predict[1,1])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":""}],"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}