Tidying the CDP Data - Part 4 Scope 1 Emissions

This post shows how to extract responses for CDP 2018 question q6_1

What were your organization’s gross global Scope 1 emissions in metric tons CO2e?

The question number CDP years

CDP Yr Question CDP Emissions Yr
2018 6.1 2017
2017 8.2 2016
2016 8.2 2015
2015 8.2 2014
2014 8.2 2013
2013 8.2 2012
2012 8.2 2011
2011 8.2 2010
library(pacman)
p_load(janitor, readxl, tidyverse)
year_in  <- c(2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017) 
path_in  <- paste0("~/Dropbox/data/cdp/investor/cdp_", 2011:2018, ".xlsx") 
sheet_in  <- c('Emissions 8','Emissions 8', '8', 'CC8. Emissions Data', 'CC8.1', 'CC8.1', 'CC8. Emissions Data', "C6.1")

Find columns on each sheet for account, response, report to or row

Companies could report multiple years of data. We only want the year related to the reporting period.

  • CDP 2011-2016 have columns reporting period from and reporting period to


CDP 2017-2018 - row 1 has current years data

Years 2016-2017 look like this:

find_col <- function(path_in, sheet_in) {
  read_excel(path = path_in, sheet = sheet_in) %>% clean_names()
}

clean_names <- map2(path_in, sheet_in, find_col)
map(clean_names, names)
## [[1]]
##  [1] "organisation"                                                                                                                                                                
##  [2] "account_no"                                                                                                                                                                  
##  [3] "discloser_id"                                                                                                                                                                
##  [4] "country"                                                                                                                                                                     
##  [5] "gics_sector"                                                                                                                                                                 
##  [6] "gics_industry_group"                                                                                                                                                         
##  [7] "gics_industry"                                                                                                                                                               
##  [8] "gics_sub_industry"                                                                                                                                                           
##  [9] "access"                                                                                                                                                                      
## [10] "primary_expansion"                                                                                                                                                           
## [11] "secondary_expansions"                                                                                                                                                        
## [12] "complexity"                                                                                                                                                                  
## [13] "reporting_period_from"                                                                                                                                                       
## [14] "reporting_period_to"                                                                                                                                                         
## [15] "x8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                               
## [16] "x8_2a_please_provide_your_gross_global_scope_1_emissions_figure_in_metric_tonnes_co2e"                                                                                       
## [17] "table_question_see_worksheet_8_2b"                                                                                                                                           
## [18] "table_question_see_worksheet_8_2c"                                                                                                                                           
## [19] "table_question_see_worksheet_8_2d"                                                                                                                                           
## [20] "x8_3a_please_provide_your_gross_global_scope_2_emissions_figure_in_metric_tonnes_co2e"                                                                                       
## [21] "table_question_see_worksheet_8_3b"                                                                                                                                           
## [22] "table_question_see_worksheet_8_3c"                                                                                                                                           
## [23] "table_question_see_worksheet_8_3d"                                                                                                                                           
## [24] "x8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_which_are_not_included_in_your_disclosure_24"       
## [25] "table_question_see_worksheet_8_4a"                                                                                                                                           
## [26] "x8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_which_are_not_included_in_your_disclosure_26"       
## [27] "table_question_see_worksheet_8_4a_2"                                                                                                                                         
## [28] "table_question_see_worksheet_8_5"                                                                                                                                            
## [29] "x8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_1_emissions"                                                                               
## [30] "x8_6a_please_indicate_the_proportion_of_your_scope_1_emissions_that_are_verified_assured"                                                                                    
## [31] "table_question_see_worksheet_8_6b"                                                                                                                                           
## [32] "x8_7_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_2_emissions"                                                                               
## [33] "x8_7a_please_indicate_the_proportion_of_your_scope_2_emissions_that_are_verified_assured"                                                                                    
## [34] "table_question_see_worksheet_8_7b"                                                                                                                                           
## [35] "x8_8_are_carbon_dioxide_emissions_from_the_combustion_of_biologically_sequestered_carbon_i_e_carbon_dioxide_emissions_from_burning_biomass_biofuels_relevant_to_your_company"
## [36] "x8_8a_please_provide_the_emissions_in_metric_tonnes_co2e"                                                                                                                    
## [37] "further_information"                                                                                                                                                         
## [38] "attachments"                                                                                                                                                                 
## [39] "x39"                                                                                                                                                                         
## [40] "x40"                                                                                                                                                                         
## [41] "x41"                                                                                                                                                                         
## [42] "x42"                                                                                                                                                                         
## [43] "x43"                                                                                                                                                                         
## [44] "x44"                                                                                                                                                                         
## [45] "x45"                                                                                                                                                                         
## [46] "x46"                                                                                                                                                                         
## [47] "x47"                                                                                                                                                                         
## [48] "x48"                                                                                                                                                                         
## [49] "x49"                                                                                                                                                                         
## [50] "x50"                                                                                                                                                                         
## [51] "x51"                                                                                                                                                                         
## [52] "x52"                                                                                                                                                                         
## [53] "x53"                                                                                                                                                                         
## [54] "x54"                                                                                                                                                                         
## [55] "x55"                                                                                                                                                                         
## [56] "x56"                                                                                                                                                                         
## [57] "x57"                                                                                                                                                                         
## [58] "x58"                                                                                                                                                                         
## [59] "x59"                                                                                                                                                                         
## [60] "x60"                                                                                                                                                                         
## [61] "x61"                                                                                                                                                                         
## [62] "x62"                                                                                                                                                                         
## [63] "x63"                                                                                                                                                                         
## [64] "x64"                                                                                                                                                                         
## [65] "x65"                                                                                                                                                                         
## [66] "x66"                                                                                                                                                                         
## [67] "x67"                                                                                                                                                                         
## [68] "x68"                                                                                                                                                                         
## [69] "x69"                                                                                                                                                                         
## [70] "x70"                                                                                                                                                                         
## [71] "x71"                                                                                                                                                                         
## [72] "x72"                                                                                                                                                                         
## [73] "x73"                                                                                                                                                                         
## [74] "x74"                                                                                                                                                                         
## [75] "x75"                                                                                                                                                                         
## [76] "x76"                                                                                                                                                                         
## [77] "x77"                                                                                                                                                                         
## [78] "x78"                                                                                                                                                                         
## [79] "x79"                                                                                                                                                                         
## [80] "x80"                                                                                                                                                                         
## [81] "x81"                                                                                                                                                                         
## [82] "x82"                                                                                                                                                                         
## [83] "x83"                                                                                                                                                                         
## [84] "x84"                                                                                                                                                                         
## [85] "x85"                                                                                                                                                                         
## [86] "x86"                                                                                                                                                                         
## [87] "x87"                                                                                                                                                                         
## [88] "x88"                                                                                                                                                                         
## [89] "x89"                                                                                                                                                                         
## 
## [[2]]
##  [1] "organisation"                                                                                                                                                                
##  [2] "account_no"                                                                                                                                                                  
##  [3] "discloser_id"                                                                                                                                                                
##  [4] "country"                                                                                                                                                                     
##  [5] "gics_sector"                                                                                                                                                                 
##  [6] "gics_industry_group"                                                                                                                                                         
##  [7] "gics_industry"                                                                                                                                                               
##  [8] "gics_sub_industry"                                                                                                                                                           
##  [9] "access"                                                                                                                                                                      
## [10] "primary_expansion"                                                                                                                                                           
## [11] "secondary_expansions"                                                                                                                                                        
## [12] "complexity"                                                                                                                                                                  
## [13] "reporting_period_from"                                                                                                                                                       
## [14] "reporting_period_to"                                                                                                                                                         
## [15] "x8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                               
## [16] "x8_2a_please_provide_your_gross_global_scope_1_emissions_figure_in_metric_tonnes_co2e"                                                                                       
## [17] "table_question_see_worksheet_8_2b"                                                                                                                                           
## [18] "table_question_see_worksheet_8_2c"                                                                                                                                           
## [19] "table_question_see_worksheet_8_2d"                                                                                                                                           
## [20] "x8_3a_please_provide_your_gross_global_scope_2_emissions_figure_in_metric_tonnes_co2e"                                                                                       
## [21] "table_question_see_worksheet_8_3b"                                                                                                                                           
## [22] "table_question_see_worksheet_8_3c"                                                                                                                                           
## [23] "table_question_see_worksheet_8_3d"                                                                                                                                           
## [24] "x8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_which_are_not_included_in_your_disclosure_24"       
## [25] "table_question_see_worksheet_8_4a"                                                                                                                                           
## [26] "x8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_which_are_not_included_in_your_disclosure_26"       
## [27] "table_question_see_worksheet_8_4a_2"                                                                                                                                         
## [28] "table_question_see_worksheet_8_5"                                                                                                                                            
## [29] "x8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_1_emissions"                                                                               
## [30] "x8_6a_please_indicate_the_proportion_of_your_scope_1_emissions_that_are_verified_assured"                                                                                    
## [31] "table_question_see_worksheet_8_6b"                                                                                                                                           
## [32] "x8_7_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_2_emissions"                                                                               
## [33] "x8_7a_please_indicate_the_proportion_of_your_scope_2_emissions_that_are_verified_assured"                                                                                    
## [34] "table_question_see_worksheet_8_7b"                                                                                                                                           
## [35] "x8_8_are_carbon_dioxide_emissions_from_the_combustion_of_biologically_sequestered_carbon_i_e_carbon_dioxide_emissions_from_burning_biomass_biofuels_relevant_to_your_company"
## [36] "x8_8a_please_provide_the_emissions_in_metric_tonnes_co2e"                                                                                                                    
## [37] "further_information"                                                                                                                                                         
## [38] "attachments"                                                                                                                                                                 
## [39] "x39"                                                                                                                                                                         
## [40] "x40"                                                                                                                                                                         
## [41] "x41"                                                                                                                                                                         
## [42] "x42"                                                                                                                                                                         
## [43] "x43"                                                                                                                                                                         
## [44] "x44"                                                                                                                                                                         
## [45] "x45"                                                                                                                                                                         
## [46] "x46"                                                                                                                                                                         
## [47] "x47"                                                                                                                                                                         
## [48] "x48"                                                                                                                                                                         
## [49] "x49"                                                                                                                                                                         
## [50] "x50"                                                                                                                                                                         
## [51] "x51"                                                                                                                                                                         
## [52] "x52"                                                                                                                                                                         
## [53] "x53"                                                                                                                                                                         
## [54] "x54"                                                                                                                                                                         
## [55] "x55"                                                                                                                                                                         
## [56] "x56"                                                                                                                                                                         
## [57] "x57"                                                                                                                                                                         
## [58] "x58"                                                                                                                                                                         
## [59] "x59"                                                                                                                                                                         
## [60] "x60"                                                                                                                                                                         
## 
## [[3]]
##  [1] "organisation"                                                                                                                                                      
##  [2] "account_no"                                                                                                                                                        
##  [3] "country"                                                                                                                                                           
##  [4] "gics_sector"                                                                                                                                                       
##  [5] "gics_industry_group"                                                                                                                                               
##  [6] "gics_industry"                                                                                                                                                     
##  [7] "gics_sub_industry"                                                                                                                                                 
##  [8] "access"                                                                                                                                                            
##  [9] "primary_expansion"                                                                                                                                                 
## [10] "secondary_expansions"                                                                                                                                              
## [11] "complexity_11"                                                                                                                                                     
## [12] "ticker"                                                                                                                                                            
## [13] "isin"                                                                                                                                                              
## [14] "complexity_14"                                                                                                                                                     
## [15] "reporting_period_from"                                                                                                                                             
## [16] "reporting_period_to"                                                                                                                                               
## [17] "x8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                     
## [18] "x8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e"                                                                             
## [19] "x8_3_please_provide_your_gross_global_scope_2_emissions_figures_in_metric_tonnes_co2e"                                                                             
## [20] "x8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_which_are_not_included_in_your_disclosure"
## [21] "table_question_see_worksheet_8_4a"                                                                                                                                 
## [22] "table_question_see_worksheet_8_5"                                                                                                                                  
## [23] "x8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_1_emissions"                                                                     
## [24] "x8_6a_please_indicate_the_proportion_of_your_scope_1_emissions_that_are_verified_assured"                                                                          
## [25] "table_question_see_worksheet_8_6b"                                                                                                                                 
## [26] "table_question_see_worksheet_8_6c"                                                                                                                                 
## [27] "x8_7_please_indicate_the_verification_assurance_status_that_applies_to_your_scope_2_emissions"                                                                     
## [28] "x8_7a_please_indicate_the_proportion_of_your_scope_2_emissions_that_are_verified_assured"                                                                          
## [29] "table_question_see_worksheet_8_7b"                                                                                                                                 
## [30] "x8_8_are_carbon_dioxide_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization"                                                              
## [31] "x8_8a_please_provide_the_emissions_in_metric_tonnes_co2"                                                                                                           
## [32] "further_information"                                                                                                                                               
## [33] "attachments"                                                                                                                                                       
## [34] "x34"                                                                                                                                                               
## [35] "x35"                                                                                                                                                               
## [36] "x36"                                                                                                                                                               
## [37] "x37"                                                                                                                                                               
## [38] "x38"                                                                                                                                                               
## [39] "x39"                                                                                                                                                               
## [40] "x40"                                                                                                                                                               
## [41] "x41"                                                                                                                                                               
## [42] "x42"                                                                                                                                                               
## [43] "x43"                                                                                                                                                               
## [44] "x44"                                                                                                                                                               
## [45] "x45"                                                                                                                                                               
## [46] "x46"                                                                                                                                                               
## [47] "x47"                                                                                                                                                               
## [48] "x48"                                                                                                                                                               
## [49] "x49"                                                                                                                                                               
## [50] "x50"                                                                                                                                                               
## [51] "x51"                                                                                                                                                               
## [52] "x52"                                                                                                                                                               
## [53] "x53"                                                                                                                                                               
## [54] "x54"                                                                                                                                                               
## [55] "x55"                                                                                                                                                               
## [56] "x56"                                                                                                                                                               
## [57] "x57"                                                                                                                                                               
## [58] "x58"                                                                                                                                                               
## 
## [[4]]
##  [1] "organisation"                                                                                                                                                                                                        
##  [2] "account_no"                                                                                                                                                                                                          
##  [3] "country"                                                                                                                                                                                                             
##  [4] "gics_sector"                                                                                                                                                                                                         
##  [5] "gics_industry_group"                                                                                                                                                                                                 
##  [6] "gics_industry"                                                                                                                                                                                                       
##  [7] "gics_sub_industry"                                                                                                                                                                                                   
##  [8] "access"                                                                                                                                                                                                              
##  [9] "primary_expansion"                                                                                                                                                                                                   
## [10] "secondary_expansions"                                                                                                                                                                                                
## [11] "complexity_11"                                                                                                                                                                                                       
## [12] "ticker"                                                                                                                                                                                                              
## [13] "isin"                                                                                                                                                                                                                
## [14] "complexity_14"                                                                                                                                                                                                       
## [15] "reporting_period_from"                                                                                                                                                                                               
## [16] "reporting_period_to"                                                                                                                                                                                                 
## [17] "cc8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                                                                      
## [18] "cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e"                                                                                                                              
## [19] "cc8_3_please_provide_your_gross_global_scope_2_emissions_figures_in_metric_tonnes_co2e"                                                                                                                              
## [20] "cc8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_that_are_within_your_selected_reporting_boundary_which_are_not_included_in_your_disclosure"
## [21] "table_question_see_worksheet_cc8_4a"                                                                                                                                                                                 
## [22] "table_question_see_worksheet_cc8_5"                                                                                                                                                                                  
## [23] "cc8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_1_emissions"                                                                                                             
## [24] "table_question_see_worksheet_cc8_6a"                                                                                                                                                                                 
## [25] "table_question_see_worksheet_cc8_6b"                                                                                                                                                                                 
## [26] "cc8_7_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_2_emissions"                                                                                                             
## [27] "table_question_see_worksheet_cc8_7a"                                                                                                                                                                                 
## [28] "table_question_see_worksheet_cc8_8"                                                                                                                                                                                  
## [29] "cc8_9_are_carbon_dioxide_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization"                                                                                                               
## [30] "cc8_9a_please_provide_the_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization_in_metric_tonnes_co2"                                                                                         
## [31] "further_information"                                                                                                                                                                                                 
## [32] "attachments"                                                                                                                                                                                                         
## [33] "x33"                                                                                                                                                                                                                 
## [34] "x34"                                                                                                                                                                                                                 
## [35] "x35"                                                                                                                                                                                                                 
## [36] "x36"                                                                                                                                                                                                                 
## [37] "x37"                                                                                                                                                                                                                 
## [38] "x38"                                                                                                                                                                                                                 
## [39] "x39"                                                                                                                                                                                                                 
## [40] "x40"                                                                                                                                                                                                                 
## 
## [[5]]
##  [1] "organisation"                                                                                                                                                                                                        
##  [2] "account_no"                                                                                                                                                                                                          
##  [3] "country"                                                                                                                                                                                                             
##  [4] "gics_sector"                                                                                                                                                                                                         
##  [5] "gics_industry_group"                                                                                                                                                                                                 
##  [6] "gics_industry"                                                                                                                                                                                                       
##  [7] "gics_sub_industry"                                                                                                                                                                                                   
##  [8] "access"                                                                                                                                                                                                              
##  [9] "primary_expansion"                                                                                                                                                                                                   
## [10] "secondary_expansions"                                                                                                                                                                                                
## [11] "complexity_11"                                                                                                                                                                                                       
## [12] "ticker"                                                                                                                                                                                                              
## [13] "isin"                                                                                                                                                                                                                
## [14] "complexity_14"                                                                                                                                                                                                       
## [15] "reporting_period_from"                                                                                                                                                                                               
## [16] "reporting_period_to"                                                                                                                                                                                                 
## [17] "cc8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                                                                      
## [18] "cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e"                                                                                                                              
## [19] "cc8_3_please_provide_your_gross_global_scope_2_emissions_figures_in_metric_tonnes_co2e"                                                                                                                              
## [20] "cc8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_that_are_within_your_selected_reporting_boundary_which_are_not_included_in_your_disclosure"
## [21] "table_question_see_worksheet_cc8_4a"                                                                                                                                                                                 
## [22] "table_question_see_worksheet_cc8_5"                                                                                                                                                                                  
## [23] "cc8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_1_emissions"                                                                                                             
## [24] "table_question_see_worksheet_cc8_6a"                                                                                                                                                                                 
## [25] "table_question_see_worksheet_cc8_6b"                                                                                                                                                                                 
## [26] "cc8_7_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_2_emissions"                                                                                                             
## [27] "table_question_see_worksheet_cc8_7a"                                                                                                                                                                                 
## [28] "table_question_see_worksheet_cc8_8"                                                                                                                                                                                  
## [29] "cc8_9_are_carbon_dioxide_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization"                                                                                                               
## [30] "cc8_9a_please_provide_the_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization_in_metric_tonnes_co2"                                                                                         
## [31] "further_information"                                                                                                                                                                                                 
## [32] "attachments"                                                                                                                                                                                                         
## [33] "x33"                                                                                                                                                                                                                 
## [34] "x34"                                                                                                                                                                                                                 
## [35] "x35"                                                                                                                                                                                                                 
## [36] "x36"                                                                                                                                                                                                                 
## [37] "x37"                                                                                                                                                                                                                 
## [38] "x38"                                                                                                                                                                                                                 
## [39] "x39"                                                                                                                                                                                                                 
## [40] "x40"                                                                                                                                                                                                                 
## 
## [[6]]
##  [1] "organisation"                                                                                                                                                                                                        
##  [2] "account_no"                                                                                                                                                                                                          
##  [3] "country"                                                                                                                                                                                                             
##  [4] "industry_group"                                                                                                                                                                                                      
##  [5] "access"                                                                                                                                                                                                              
##  [6] "primary_expansion"                                                                                                                                                                                                   
##  [7] "secondary_expansions"                                                                                                                                                                                                
##  [8] "complexity_8"                                                                                                                                                                                                        
##  [9] "ticker"                                                                                                                                                                                                              
## [10] "isin"                                                                                                                                                                                                                
## [11] "complexity_11"                                                                                                                                                                                                       
## [12] "reporting_period_from"                                                                                                                                                                                               
## [13] "reporting_period_to"                                                                                                                                                                                                 
## [14] "cc8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                                                                      
## [15] "cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e"                                                                                                                              
## [16] "cc8_3_does_your_company_have_any_operations_in_markets_providing_product_or_supplier_specific_data_in_the_form_of_contractual_instruments"                                                                           
## [17] "table_question_see_worksheet_cc8_3a"                                                                                                                                                                                 
## [18] "cc8_4_are_there_are_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_that_are_within_your_selected_reporting_boundary_which_are_not_included_in_your_disclosure"
## [19] "table_question_see_worksheet_cc8_4a"                                                                                                                                                                                 
## [20] "table_question_see_worksheet_cc8_5"                                                                                                                                                                                  
## [21] "cc8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_1_emissions"                                                                                                             
## [22] "table_question_see_worksheet_cc8_6a"                                                                                                                                                                                 
## [23] "table_question_see_worksheet_cc8_6b"                                                                                                                                                                                 
## [24] "cc8_7_please_indicate_the_verification_assurance_status_that_applies_to_at_least_one_of_your_reported_scope_2_emissions_figures"                                                                                     
## [25] "table_question_see_worksheet_cc8_7a"                                                                                                                                                                                 
## [26] "table_question_see_worksheet_cc8_8"                                                                                                                                                                                  
## [27] "cc8_9_are_carbon_dioxide_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization"                                                                                                               
## [28] "cc8_9a_please_provide_the_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization_in_metric_tonnes_co2"                                                                                         
## [29] "further_information"                                                                                                                                                                                                 
## [30] "attachments"                                                                                                                                                                                                         
## [31] "x31"                                                                                                                                                                                                                 
## [32] "x32"                                                                                                                                                                                                                 
## [33] "x33"                                                                                                                                                                                                                 
## [34] "x34"                                                                                                                                                                                                                 
## [35] "x35"                                                                                                                                                                                                                 
## [36] "x36"                                                                                                                                                                                                                 
## [37] "x37"                                                                                                                                                                                                                 
## [38] "x38"                                                                                                                                                                                                                 
## 
## [[7]]
##  [1] "program_name"                                                                                                                                                                                                    
##  [2] "account_id"                                                                                                                                                                                                      
##  [3] "project_year"                                                                                                                                                                                                    
##  [4] "account_name"                                                                                                                                                                                                    
##  [5] "row"                                                                                                                                                                                                             
##  [6] "accounting_year"                                                                                                                                                                                                 
##  [7] "cc8_1_please_select_the_boundary_you_are_using_for_your_scope_1_and_2_greenhouse_gas_inventory"                                                                                                                  
##  [8] "cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e"                                                                                                                          
##  [9] "cc8_4_are_there_any_sources_e_g_facilities_specific_gh_gs_activities_geographies_etc_of_scope_1_and_scope_2_emissions_that_are_within_your_selected_reporting_boundary_which_are_not_included_in_your_disclosure"
## [10] "cc8_6_please_indicate_the_verification_assurance_status_that_applies_to_your_reported_scope_1_emissions"                                                                                                         
## [11] "cc8_7_please_indicate_the_verification_assurance_status_that_applies_to_at_least_one_of_your_reported_scope_2_emissions_figures"                                                                                 
## [12] "cc8_9_are_carbon_dioxide_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization"                                                                                                           
## [13] "cc8_9a_please_provide_the_emissions_from_biologically_sequestered_carbon_relevant_to_your_organization_in_metric_tonnes_co2"                                                                                     
## 
## [[8]]
##  [1] "account_number"                                                                                                                          
##  [2] "organization"                                                                                                                            
##  [3] "country"                                                                                                                                 
##  [4] "public"                                                                                                                                  
##  [5] "response_received_date"                                                                                                                  
##  [6] "primary_activity"                                                                                                                        
##  [7] "primary_sector"                                                                                                                          
##  [8] "primary_industry"                                                                                                                        
##  [9] "primary_questionnaire_sector"                                                                                                            
## [10] "authority_types"                                                                                                                         
## [11] "row"                                                                                                                                     
## [12] "row_name"                                                                                                                                
## [13] "c6_1_c1_what_were_your_organization_s_gross_global_scope_1_emissions_in_metric_tons_co2e_gross_global_scope_1_emissions_metric_tons_co2e"
## [14] "c6_1_c2_what_were_your_organization_s_gross_global_scope_1_emissions_in_metric_tons_co2e_end_year_of_reporting_period"                   
## [15] "c6_1_c3_what_were_your_organization_s_gross_global_scope_1_emissions_in_metric_tons_co2e_comment"
col_account <- c(rep("account_no", 6), "account_id", "account_number")

col_response  <- c('x8_2a_please_provide_your_gross_global_scope_1_emissions_figure_in_metric_tonnes_co2e',
                   'x8_2a_please_provide_your_gross_global_scope_1_emissions_figure_in_metric_tonnes_co2e',
                   'x8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e',
                   'cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e',
                   'cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e',
                   'cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e',
                   "cc8_2_please_provide_your_gross_global_scope_1_emissions_figures_in_metric_tonnes_co2e",
                   'c6_1_c1_what_were_your_organization_s_gross_global_scope_1_emissions_in_metric_tons_co2e_gross_global_scope_1_emissions_metric_tons_co2e')
                   
col_row  <- c(rep('reporting_period_to', 6), rep('row', 2))                   
files_in <- function(path_in, sheet_in, year_in, col_account, col_response, col_row){
  cleaned <- read_excel(path = path_in, sheet = sheet_in) %>% clean_names()
  cleaned %>%
    mutate(year = year_in) %>%
    select(
      account = col_account, year, response = col_response, row =  col_row
    ) %>%
    mutate(account = as.character(account),
           response = as.numeric(response),
           row = as.character(row)) # needed this b/c these variables were different types in one or more years
}

l  <- list(path_in, sheet_in, year_in, col_account, col_response, col_row)

dtw  <- pmap_dfr(l, files_in)
dtw  %>% head()
## # A tibble: 6 x 4
##   account  year  response row        
##   <chr>   <dbl>     <dbl> <chr>      
## 1 1800     2010  3699579  31 Mar 2011
## 2 5300     2010 14744282  31 Dec 2010
## 3 29900    2010     1844. 30 Jun 2010
## 4 10200    2010       NA  31 Dec 2010
## 5 22300    2010       NA  31 Dec 2010
## 6 13400    2010      236  31 Dec 2010
dtw  %>% tail()
## # A tibble: 6 x 4
##   account  year response row  
##   <chr>   <dbl>    <dbl> <chr>
## 1 31761    2017      NA  3    
## 2 31761    2017      NA  4    
## 3 21064    2017   27167. 1    
## 4 21064    2017   25078. 2    
## 5 21064    2017   26478. 3    
## 6 21064    2017      NA  4

For years 2010-2015 extract year from row find maximum

dtw   %>% select(account, year, response, row)   %>% 
filter(year < 2016)  %>% 
extract(row, "row", "(\\d{4})")   %>%
mutate(row = as.numeric(row))   %>%  
group_by(year, account)  %>% 
    mutate(cur_year = max(row, na.rm = TRUE))  %>%
    filter(cur_year == row)  %>% 
    select(-c(row,cur_year)) -> y2011_2015

y2011_2015
## # A tibble: 10,598 x 3
## # Groups:   year, account [10,598]
##    account  year  response
##    <chr>   <dbl>     <dbl>
##  1 1800     2010  3699579 
##  2 5300     2010 14744282 
##  3 29900    2010     1844.
##  4 10200    2010       NA 
##  5 22300    2010       NA 
##  6 13400    2010      236 
##  7 15400    2010        0 
##  8 32500    2010     1042 
##  9 23100    2010   106450 
## 10 17900    2010 23800000 
## # … with 10,588 more rows

check that only 1 response per account per year

y2011_2015  %>% 
    group_by(year, account)  %>% 
    tally(sort = TRUE)
## # A tibble: 10,598 x 3
## # Groups:   year [6]
##     year account     n
##    <dbl> <chr>   <int>
##  1  2010 10055       1
##  2  2010 10056       1
##  3  2010 10062       1
##  4  2010 10071       1
##  5  2010 10095       1
##  6  2010 10117       1
##  7  2010 10132       1
##  8  2010 10143       1
##  9  2010 10148       1
## 10  2010 10156       1
## # … with 10,588 more rows

For years 2016-2017 keep current year. row = 1

dtw  %>% filter(year > 2015)  %>% 
    group_by(year, account)  %>%
    filter(row == '1')   %>%  
    select(account, year, response)  -> y2016_2017

y2016_2017
## # A tibble: 3,807 x 3
## # Groups:   year, account [3,807]
##    account  year response
##    <chr>   <dbl>    <dbl>
##  1 44       2016      191
##  2 53       2016    67003
##  3 64       2016   480000
##  4 78       2016      653
##  5 87       2016  6983503
##  6 94       2016     7960
##  7 97       2016     3962
##  8 119      2016  1360680
##  9 134      2016  2563160
## 10 135      2016     7250
## # … with 3,797 more rows

bind years together

q6_1  <- bind_rows(y2011_2015, y2016_2017)  %>% rename(q6_1 = response)

q6_1  %>% write_csv("~/Dropbox/data/cdp/investor/derived-data/q6_1.csv")

q6_1
## # A tibble: 14,405 x 3
## # Groups:   year, account [14,405]
##    account  year      q6_1
##    <chr>   <dbl>     <dbl>
##  1 1800     2010  3699579 
##  2 5300     2010 14744282 
##  3 29900    2010     1844.
##  4 10200    2010       NA 
##  5 22300    2010       NA 
##  6 13400    2010      236 
##  7 15400    2010        0 
##  8 32500    2010     1042 
##  9 23100    2010   106450 
## 10 17900    2010 23800000 
## # … with 14,395 more rows

146 observations are not listed on the scope 1 sheets

df_summary  <- read_csv("~/Dropbox/data/cdp/investor/derived-data/df_summary.csv")

df_summary  %>% mutate(account = as.character(account)) %>% anti_join(q6_1)  %>%
    group_by(year)  %>% tally
## # A tibble: 4 x 2
##    year     n
##   <dbl> <int>
## 1  2010     9
## 2  2011    18
## 3  2012    52
## 4  2014    67
df_summary  %>% mutate(account = as.character(account)) %>% anti_join(q6_1)  %>%
    group_by(country)  %>% tally(sort = TRUE)
## # A tibble: 27 x 2
##    country            n
##    <chr>          <int>
##  1 United Kingdom    63
##  2 USA               21
##  3 Sweden             6
##  4 Turkey             5
##  5 Brazil             4
##  6 Hong Kong          4
##  7 Netherlands        4
##  8 Taiwan             4
##  9 Australia          3
## 10 Canada             3
## # … with 17 more rows
df_1  <- read_excel(path = path_in[5], sheet = 1)
df_2  <- read_excel(path = path_in[5], sheet = sheet_in[5] )
df_1  %>%  filter(`Account No` == 14519 )
## # A tibble: 1 x 19
##   Organisation `Account No` Country `GICS Sector` `GICS Industry …
##   <chr>               <dbl> <chr>   <chr>         <chr>           
## 1 Peabody Ene…        14519 USA     Energy        Energy          
## # … with 14 more variables: `GICS Industry` <chr>, `GICS Sub-Industry` <chr>,
## #   `Primary Expansion` <lgl>, `Secondary Expansions` <chr>, Ticker <chr>,
## #   ISIN <chr>, Complexity <chr>, Status <chr>, `Submitted Date` <chr>,
## #   `Ammended Date` <chr>, Public <chr>, `Response Account No` <chr>, `Response
## #   Organisation` <dbl>, Version <chr>
df_2  %>%  filter(`Account No` == 14519 )
## # A tibble: 0 x 40
## # … with 40 variables: Organisation <chr>, `Account No` <dbl>, Country <chr>,
## #   `GICS Sector` <chr>, `GICS Industry Group` <chr>, `GICS Industry` <chr>,
## #   `GICS Sub-Industry` <chr>, Access <chr>, `Primary Expansion` <lgl>,
## #   `Secondary Expansions` <chr>, Complexity...11 <chr>, Ticker <chr>,
## #   ISIN <chr>, Complexity...14 <chr>, `Reporting Period\r\nFrom` <chr>,
## #   `Reporting Period\r\nTo` <chr>, `CC8.1. Please select the boundary you are
## #   using for your Scope 1 and 2 greenhouse gas inventory` <chr>, `CC8.2.
## #   Please provide your gross global Scope 1 emissions figures in metric tonnes
## #   CO2e` <dbl>, `CC8.3. Please provide your gross global Scope 2 emissions
## #   figures in metric tonnes CO2e  ` <dbl>, `CC8.4. Are there are any sources
## #   (e.g. facilities, specific GHGs, activities, geographies, etc.) of Scope 1
## #   and Scope 2 emissions that are within your selected reporting boundary
## #   which are not included in your disclosure?` <chr>, `Table question - see
## #   worksheet "CC8.4a"` <chr>, `Table question - see worksheet "CC8.5"` <chr>,
## #   `CC8.6. Please indicate the verification/assurance status that applies to
## #   your reported Scope 1 emissions` <chr>, `Table question - see worksheet
## #   "CC8.6a"` <chr>, `Table question - see worksheet "CC8.6b"` <chr>, `CC8.7.
## #   Please indicate the verification/assurance status that applies to your
## #   reported Scope 2 emissions` <chr>, `Table question - see worksheet
## #   "CC8.7a"` <chr>, `Table question - see worksheet "CC8.8"` <chr>, `CC8.9.
## #   Are carbon dioxide emissions from biologically sequestered carbon relevant
## #   to your organization?` <chr>, `CC8.9a. Please provide the emissions from
## #   biologically sequestered carbon relevant to your organization in metric
## #   tonnes CO2` <dbl>, `Further Information` <chr>, Attachments <chr>,
## #   ...33 <chr>, ...34 <chr>, ...35 <chr>, ...36 <chr>, ...37 <chr>,
## #   ...38 <chr>, ...39 <chr>, ...40 <lgl>
Avatar
Elizabeth Stanny
Professor of Business Administration

My research interests include climate change and political disclosures.

Related