Green Office Quantification Demo- Sage Gibbons

Code Set-Up

#Example: 'person' is simulated data, conv stores some basic known values we want to use for quantification conversions, rest is done with mutate.

person <- read_xlsx(here("GO_Conversions.xlsx"), sheet=3)
conv <- read_xlsx(here("GO_Conversions.xlsx"), sheet=2)

quant <- coalesce_join(person, conv, by = 'type')

#Conversion Mutations

quant %>% mutate(standby_hours_dy = 8 - active_hours_dy,
                 kwh_active_dy = ((active_watts*active_hours_dy)/1000),
                 kwh_standby_dy = ((standby_watts*standby_hours_dy)/1000),
                 kwh_dy = kwh_active_dy+kwh_standby_dy,
                 kwh_wk = ifelse(type == c("incandescent", "halogen", "cfl", "led", "computer", "monitor", "personal_printer","other_elec"), kwh_dy*5, 
                          ifelse(type == "glass",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton,
                          ifelse(type == "aluminum",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, #this can be cleaned with an '%in%'
                          ifelse(type == "plastic",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton,
                          ifelse(type == "cardboard",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton,
                          ifelse(type == "paper",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, NA))))))) -> quant

quant %>% mutate(gallons_oil_wk = kwh_wk/43.9,
                 kg_CO2_wk = (((gallons_oil_wk/42)*0.43)*1000)) -> quant

#Psuedo-real example. Using goquestidy, which is aggregated data, to get net impact in a hypothetical year if all participants took survey at same time. Need to use goques with a loop over month/year of entry to get over-time calculations.

#Relevant Questions:
#Count of lightbulbs and kind of lightbulbs
#"All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs."
#-> If Yes, Will just assume they are all using 2 LEDs. If Action Needed, will just assume using 2 incandescents.

#Wattage of computer and monitor and personal printer and “other electronics”
#"My computer monitor is set to sleep after 10 minutes or less"
#-> If Yes, do (8 - assumed 6 active_hours_dy) + .166 (ten minutes) = active_hours_dy and standby_hours_dy = 0

#"I shut off my personal printer when not in use"
#-> If Yes, standby_hours_dy = 0

#Amount of recycling for each material
#"I recycle all bottles and cans that I come across in the office."
#-> If Yes, will just assume 1 plastic bottle /wk

#"I recycle all cardboard that I come across in the office."
#-> If Yes, will just assume 1 cardboard box / wk

#Amount of paper used
#"I recycle all office paper."
#-> If Yes, value_wk for type == "paper" stays same, ifelse, value_wk for type == "paper" is "0".

#Note that I still need to develop columns that captures usage independent of sustainable action. Number of pieces of paper used, bottles drunk, etc. Because of the large amount of assumptions I'm using here, I'm sticking with quantities that match the sustainability questions. For example, "I recycle all paper" the value_wk is pieces of paper recycled. Not making a claim on paper used by those who don't do this.

goques %>% filter(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." |
                      question == "My computer monitor is set to sleep after 10 minutes or less." |
                      question == "I shut off my personal printer when not in use." |
                      question == "I recycle all bottles and cans that I come across in the office." |
                      question ==  "I recycle all cardboard that I come across in the office." |
                      question == "I recycle all office paper.") %>%
  select(`NU ID`, question, section, answer_1) -> goquant

#Adding columns from 'person' simulated data
goquant[c("type", "value", "value_wk", "active_watts", "standby_watts", "active_hours_dy", "standby_hours_dy")] <- NA

#lightbulbs
goquant %>% mutate(type = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." & answer_1 == "Yes", "led", 
            ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." & answer_1 == "Action Needed", "incandescent", NA))) -> goquant

goquant %>% mutate(value = ifelse(type == "led", 2, 
                           ifelse(type == "incandescent", 2, type))) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "led", 9, 
                                  ifelse(type == "incandescent", 60, active_watts))) -> goquant
goquant %>% mutate(standby_watts = ifelse(type == "led", 0, 
                                  ifelse(type == "incandescent", 0, standby_watts))) -> goquant

goquant %>% mutate(active_hours_dy = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.", 8, active_hours_dy)) -> goquant
goquant %>% mutate(standby_hours_dy = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.", 8- active_hours_dy, standby_hours_dy)) -> goquant

#computer monitor
goquant %>% mutate(type = ifelse(question == "My computer monitor is set to sleep after 10 minutes or less.","monitor", type)) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "monitor", 45, active_watts)) -> goquant
goquant %>% mutate(standby_watts = ifelse(type == "monitor", 4, standby_watts)) -> goquant
goquant %>% mutate(active_hours_dy = ifelse(type == "monitor", 6, active_hours_dy)) -> goquant

goquant %>% mutate(standby_hours_dy = ifelse(type == "monitor" & answer_1 == "Yes", 0, 
                            ifelse(type == "monitor" & answer_1 == "Action Needed", 8-active_hours_dy, standby_hours_dy))) -> goquant

#personal printer (here need to account for whether they have one or not)
goquant %>% mutate(type = ifelse(question == "I shut off my personal printer when not in use.", "printer", type)) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "printer" & answer_1 != "N/A", 40, 
                                  ifelse(type == "printer" & answer_1 == "N/A", 0, active_watts))) -> goquant
                   
goquant %>% mutate(standby_watts = ifelse(type == "printer" & answer_1 != "N/A", 4, 
                                  ifelse(type == "printer" & answer_1 == "N/A", 0, standby_watts))) -> goquant                   
goquant %>% mutate(active_hours_dy = ifelse(type == "printer" & answer_1 != "N/A", 1,
                                     ifelse(type == "printer" & answer_1 == "N/A", 0, active_hours_dy))) -> goquant

goquant %>% mutate(standby_hours_dy = ifelse(type == "printer" & answer_1 == "Yes",
                                             0, 
                   ifelse(type == "printer" & answer_1 == "Action Needed", 
                          8-active_hours_dy, 
                   ifelse(type == "printer" & answer_1 == "N/A", 0, standby_hours_dy)))) -> goquant

#Bottles and Cans Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all bottles and cans that I come across in the office.", "plastic", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "plastic", 1, value_wk)) -> goquant

#Cardboard Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all cardboard that I come across in the office.", "cardboard", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "cardboard", 1, value_wk)) -> goquant

#Paper Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all office paper.", "paper", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "paper" & answer_1 == "Yes" , 25, value_wk)) -> goquant

quant <- coalesce_join(goquant, conv, by = 'type')

quant %>% mutate(kwh_active_dy = ((active_watts*active_hours_dy)/1000),
                 kwh_standby_dy = ((standby_watts*standby_hours_dy)/1000),
                 kwh_dy = kwh_active_dy+kwh_standby_dy,
                 kwh_wk = ifelse(type == c("incandescent", "led", "monitor","printer"), kwh_dy*5, NA)) -> quant

quant %>% mutate(kwh_wk = ifelse(type == "paper",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton,
                          ifelse(type == "plastic",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, #this can be cleaned with an '%in%'
                          ifelse(type == "cardboard", 
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, kwh_dy*5)))) -> quant

quant %>% mutate(gallons_oil_wk = kwh_wk/43.9,
                 kg_CO2_wk = (((gallons_oil_wk/42)*0.43)*1000)) -> quant

Quantifiying Environmental Impact of the Green Office Challenge.

The following quantifies the (theoretical) environmental impact of six questions from the Green Office Challenge. It is a combination of reasonable assumptions (e.g. watts for an average computer monitor) with Green Office survey data (e.g. number of people who say “Yes” to having their monitor set to sleep after 10 minutes). A number of conversions were then calculated to estimate the impact on Kilowatt Hours of Electricity, Gallons of Oil, and Kilograms of CO2 for a 48-week year

The survey data contains 483 participants and is subset to just their first survey. The resultant calculations are, in effect, the theoretical environmental impact over one year if all 483 participants took the survey at the same time.

Dataframes

Below is the dataframe (Table 1) for which all following visualizations are referenced. To dive deeper into how it was created, select “Show All Code” underneath the “Code” button in the top-right of this document.

Each row corresponds to a unique question and answer_1 combination. The other columns illustrate the various assumptions used in this project. For example, in the question, “All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.”, when the column “answer_1” is “Yes” the column “type” has a value of “led” and when the column “answer_1” is “Action Needed”, the column “type” has a value of “incandescent”. Furthermore, the “value” column for those lightbulb types are both “2”, indicating that all other calculations for that question assumes a participant has either 2 incandescent or led lightbulbs in their workstation.

Several of the other questions also use similar simplyifing assumptions, given that the actual data is unknown. For example, in the question “I recycle all bottles and cans that I come across in the office.”, I assume the “type” of recycled object to be a plastic bottle and that participants who answered “Yes” recycle just 2 of these a week.

Gathering the actual information from all previous Green Office participants will allow much more precise and in-depth calculations for the questions in this project and several others from the larger Green Office Survey.

All other data assumptions (e.g. weight (lbs) per type of recyclable material) or conversions (e.g. kwh_wk to gallons_oil_wk) come from authoritative sources such as the EPA.

Table 1:

quant %>% select(-`NU ID`) -> quant_lite
quant_lite[!duplicated(quant_lite),]

NA
NA

Supplementing Table 1, this dataframe (Table 1) shows the counts of different answers for each of the six questions examined in this project. These counts were used to calculate the environmental impact of those who do take the reference action (i.e. “Yes”) and those who do not (i.e. “Action Needed”).

Table 2:

goquestidy %>% select(question, `Action Needed`, `N/A`, Yes) %>% ungroup() %>%
semi_join(., quant %>% select(question)) %>% filter(surveyorder==1) %>% select(-surveyorder)

NA

Technological Adjustment Questions and Visualizations

Three Technology-related questions from the Green Office Survey lend themselves to quantification:

1. “All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.”
2. “I shut off my personal printer when not in use.”
3. “My computer monitor is set to sleep after 10 minutes or less.”

The key assumptions for these questions are the Watts of electricity used when a device is active and in standby as well as the number of hours for each. This allows for the calculation of Kilowatt hours per day and by extension Kilowatt hours per week and per year. The exact assumptions used can be seen in Table 1, depending on the type of device and the answer given to the question.

Below is the visualized impact of each of these “Technological Adjustment” questions from the assumptions in Table 1 relative to the count of answer type from Table 2:

Recycling Questions and Visualizations

Three Recycling-related questions from the Green Office Survey lend themselves to quantification:

1. “I recycle all bottles and cans that I come across in the office.”
2. “I recycle all cardboard that I come across in the office.”
3. “I recycle all office paper.”

The key assumptions for these questions are the type of recycled material and the quantity recycled in a week. The exact assumptions used can be seen in Table 1. Where type is “plastic”, “cardboard”, or "paper, weight (lbs) is based on EPA data for a 20 fl oz bottle, a 2’ by 2’ by 2’ cardboard box, 3mm, and a standard sheet of printer paper, respectively.

Below is the visualized impact of each of these “Recycling” questions from the assumptions in Table 1 relative to the count of answer type from Table 2:

Time Series

Below is a time series visualization of the environmental impact Green Office Participants made each week since the program began.

goques2 %>% filter(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." |
                      question == "My computer monitor is set to sleep after 10 minutes or less." |
                      question == "I shut off my personal printer when not in use." |
                      question == "I recycle all bottles and cans that I come across in the office." |
                      question ==  "I recycle all cardboard that I come across in the office." |
                      question == "I recycle all office paper.") %>%
  select(`NU ID`, question, section, answer_1, date, month, year) -> goquant

#Adding columns from 'person' simulated data
goquant[c("type", "value", "value_wk", "active_watts", "standby_watts", "active_hours_dy", "standby_hours_dy")] <- NA

#lightbulbs
goquant %>% mutate(type = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." & answer_1 == "Yes", "led", 
            ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs." & answer_1 == "Action Needed", "incandescent", NA))) -> goquant

goquant %>% mutate(value = ifelse(type == "led", 2, 
                           ifelse(type == "incandescent", 2, type))) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "led", 9, 
                                  ifelse(type == "incandescent", 60, active_watts))) -> goquant
goquant %>% mutate(standby_watts = ifelse(type == "led", 0, 
                                  ifelse(type == "incandescent", 0, standby_watts))) -> goquant

goquant %>% mutate(active_hours_dy = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.", 8, active_hours_dy)) -> goquant
goquant %>% mutate(standby_hours_dy = ifelse(question == "All of my task lighting (including floor and desk lamps) uses either LED or CFL, but no incandescent light bulbs.", 8- active_hours_dy, standby_hours_dy)) -> goquant

#computer monitor
goquant %>% mutate(type = ifelse(question == "My computer monitor is set to sleep after 10 minutes or less.","monitor", type)) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "monitor", 45, active_watts)) -> goquant
goquant %>% mutate(standby_watts = ifelse(type == "monitor", 4, standby_watts)) -> goquant
goquant %>% mutate(active_hours_dy = ifelse(type == "monitor", 6, active_hours_dy)) -> goquant

goquant %>% mutate(standby_hours_dy = ifelse(type == "monitor" & answer_1 == "Yes", 0, 
                            ifelse(type == "monitor" & answer_1 == "Action Needed", 8-active_hours_dy, standby_hours_dy))) -> goquant

#personal printer (here need to account for whether they have one or not)
goquant %>% mutate(type = ifelse(question == "I shut off my personal printer when not in use.", "printer", type)) -> goquant

goquant %>% mutate(active_watts = ifelse(type == "printer" & answer_1 != "N/A", 40, 
                                  ifelse(type == "printer" & answer_1 == "N/A", 0, active_watts))) -> goquant
                   
goquant %>% mutate(standby_watts = ifelse(type == "printer" & answer_1 != "N/A", 4, 
                                  ifelse(type == "printer" & answer_1 == "N/A", 0, standby_watts))) -> goquant                   
goquant %>% mutate(active_hours_dy = ifelse(type == "printer" & answer_1 != "N/A", 1,
                                     ifelse(type == "printer" & answer_1 == "N/A", 0, active_hours_dy))) -> goquant

goquant %>% mutate(standby_hours_dy = ifelse(type == "printer" & answer_1 == "Yes",
                                             0, 
                   ifelse(type == "printer" & answer_1 == "Action Needed", 
                          8-active_hours_dy, 
                   ifelse(type == "printer" & answer_1 == "N/A", 0, standby_hours_dy)))) -> goquant

#Bottles and Cans Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all bottles and cans that I come across in the office.", "plastic", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "plastic", 1, value_wk)) -> goquant

#Cardboard Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all cardboard that I come across in the office.", "cardboard", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "cardboard", 1, value_wk)) -> goquant

#Paper Recycling
goquant %>% mutate(type = ifelse(question == "I recycle all office paper.", "paper", type)) -> goquant

goquant %>% mutate(value_wk = ifelse(type == "paper" & answer_1 == "Yes" , 25, value_wk)) -> goquant

quant <- coalesce_join(goquant, conv, by = 'type')

quant %>% mutate(kwh_active_dy = ((active_watts*active_hours_dy)/1000),
                 kwh_standby_dy = ((standby_watts*standby_hours_dy)/1000),
                 kwh_dy = kwh_active_dy+kwh_standby_dy,
                 kwh_wk = ifelse(type == c("incandescent", "led", "monitor","printer"), kwh_dy*5, NA)) -> quant

quant %>% mutate(kwh_wk = ifelse(type == "paper",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton,
                          ifelse(type == "plastic",
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, #this can be cleaned with an '%in%'
                          ifelse(type == "cardboard", 
                      ((`weight (lbs)`*value_wk)/2000)*kwh_per_ton, kwh_dy*5)))) -> quant

quant %>% mutate(gallons_oil_wk = kwh_wk/43.9,
                 kg_CO2_wk = (((gallons_oil_wk/42)*0.43)*1000)) -> quant

Conclusion

This project emphasizes the potential for the Green Office Challenge to be a major facet of Northeastern University’s Sustainability and Climate goals. The environmental impact of these six questions is significant even under conservative assumptions. By gathering more accurate data in place of these assumptions, we can demonstrate the impact of this program at the individual, department, and campus level across a much broader variety of environmental impacts. This information can play a central role in helping participants of the Green Office Challenge understand the impact of their efforts and commit to taking further action.