2f:T8ff,

1 Explore Energy Usage

Analysis of Energy Consumption Trends by Building

Charles Shipman Payson Building identified as the main contributor to energy usage.
Notable downward trend in energy consumption since 2020 for the Charles Shipman Payson Building.

Types of Energy Contributing to Usage Decrease

Electric grid and fuel oil showed a decline in 2021 and 2022 compared to 2020.
Natural gas usage increased in 2021 but decreased in 2022 compared to 2020.

2 Explore Overall Energy Usage Cost

Comparison of Overall Costs with Energy Consumption Trend

Despite reduced energy usage since 2020, overall costs increased in 2021 and 2022.

Identifying Energy Types Responsible for Cost Increase

Electric type: Decrease in 2021, but a significant increase in 2022.
Natural gas: Continuous cost increase in both 2021 and 2022.
Fuel oil: Similar trend of rising costs over the past two years.

3 Reasons for Decline in Energy Usage vs. Increase in Costs

Key Factor: The increase in unit cost attributed to inflation.

30:T4d4,pma_data = { let data = pma.sheet("Meter Entries").map(d => ({building: d.A, type: d.E, quantity: d.J, units: d.K, start: d.G, end: d.H, delivery: d.I})); // Set "date" to either start or delivery date (latter is used for "Fuel Oil (No. 2)" data.forEach(d => d.date = d.delivery == "Not Available" ? d.start : d.delivery) // Remove extraneous rows at the beginning of the sheet data = data.filter((d, i) => i > 5); // Remove Indoor Portable Type data = data.filter(item => item.type !== "Potable Indoor"); // BTU (British Thermal Units) // kWh (Kilowatt-hours) to BTU: // 1 kWh is equivalent to 3412 BTU. // ccf (hundred cubic feet) to BTU: // 1 ccf of natural gas is approximately equivalent to 103800 BTU. // Gallons of Fuel Oil to BTU: // 1 gallon of fuel oil is approximately equivalent to 138500 BTU. data.map(item => { if (item.type === "Electric - Grid"){ item.unified_quantity_mbtu = item.quantity * 3412.142 / 1000000; }else if(item.type === "Natural Gas"){ item.unified_quantity_mbtu = item.quantity * 103800 / 1000000; }else if (item.type === "Fuel Oil (No. 2)"){ item.unified_quantity_mbtu = item.quantity * 138500 / 1000000; } return item }) return data; }31:T7b0,grouped_pma_data = { // Sample the columns of interest from your sheet let data = pma.sheet("Meter Entries").map(d => ({ building: d.A, type: d.E, quantity: d.J, units: d.K, start: d.G, end: d.H, delivery: d.I })); // Set "date" to either start or delivery date data.forEach(d => d.date = d.delivery == "Not Available" ? d.start : d.delivery); // Remove extraneous rows at the beginning of the sheet data = data.filter((d, i) => i >= 6); // Remove Indoor Portable Type data = data.filter(item => item.type !== "Potable Indoor"); // BTU (British Thermal Units) // kWh (Kilowatt-hours) to BTU: // 1 kWh is equivalent to 3412 BTU. // ccf (hundred cubic feet) to BTU: // 1 ccf of natural gas is approximately equivalent to 103800 BTU. // Gallons of Fuel Oil to BTU: // 1 gallon of fuel oil is approximately equivalent to 138500 BTU. data.map(item => { if (item.type === "Electric - Grid"){ item.unified_quantity_mbtu = item.quantity * 3412.142 / 1000000; }else if(item.type === "Natural Gas"){ item.unified_quantity_mbtu = item.quantity * 103800 / 1000000; }else if (item.type === "Fuel Oil (No. 2)"){ item.unified_quantity_mbtu = item.quantity * 138500 / 1000000; } return item }) let aggregateMap = {}; data.forEach(d => { // here we should group by building, type, date const key = `${d.building}_${d.type}_${d.date.toISOString()}`; if (!aggregateMap[key]) { aggregateMap[key] = { ...d }; } else { aggregateMap[key].quantity += +d.quantity; aggregateMap[key].unified_quantity_mbtu += +d.unified_quantity_mbtu; } }); // Convert the hashmap back to an array of cleaned and aggregated data data = Object.values(aggregateMap); data.forEach(d => { d.formattedDate = d.date.toISOString().substring(0, 7)}) data.forEach(d => { d.year = d.date.toISOString().substring(0, 4)}) // Sort data by date to maintain order data.sort((a, b) => a.date - b.date); return data; } 32:T500,data_env = { // Sample the columns of interest let data = env.sheet("Sheet1").map(d => ({station: d.A, date: d.B, air_temp: d.C, dew_temp: d.D, sea_level_pressure: d.E, wind_direction: d.F, wind_speed_rate: d.G, sky_condition: d.H, precip_1hour: d.I, precip_6hour: d.J})); // Remove extraneous rows at the beginning of the sheet data = data.filter((d, i) => i > 1); data = data.map(d => ({...d,year_month: getYearMonth(d.date)})); // Group data by 'year_month' and calculate averages for 'air_temp', 'dew_temp', etc. data = d3.groups(data, d => d.year_month) .map(([year_month, values]) => { return { year_month, avg_air_temp: average(values.map(d => d.air_temp)), avg_dew_temp: average(values.map(d => d.dew_temp)), avg_sea_level_pressure: average(values.map(d => d.sea_level_pressure)), avg_wind_direction: average(values.map(d => d.wind_direction)), avg_wind_speed_rate: average(values.map(d => d.wind_speed_rate)), avg_precip_1hour: average(values.map(d => d.precip_1hour)), avg_precip_6hour: average(values.map(d => d.precip_6hour)), } }); data = data.map(d => ({...d,year: d.year_month.split("-")[0]})); data.forEach(item => { item.formattedDate = item.year_month; }); return data; }33:[{"id":"fb4032177eaac3eb17424ac40cddac7b9f46be99def0e849318ec0b5b0d177a163995b4aa02a9941b65c86d5c20d55c1d128ed1e6a50bffdd14305fb869d68cb","href":"https://static.observableusercontent.com/files/fb4032177eaac3eb17424ac40cddac7b9f46be99def0e849318ec0b5b0d177a163995b4aa02a9941b65c86d5c20d55c1d128ed1e6a50bffdd14305fb869d68cb","name":"env_info.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1701299250202.504,"size":5506867},{"id":"9399067af75527be6dfd0bfa6ffdbef2a77e1fc0f8a41964e11261f40ec09351a4b394f548b4d5690d69c0a931c52aa86c64cc27f4d28977091e59d2567e2e20","href":"https://static.observableusercontent.com/files/9399067af75527be6dfd0bfa6ffdbef2a77e1fc0f8a41964e11261f40ec09351a4b394f548b4d5690d69c0a931c52aa86c64cc27f4d28977091e59d2567e2e20","name":"pma-2.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1701299250202.504,"size":200776},{"id":"a1bb110d193558e88abbebe37c439f23efa7f502f8fa42d6270a4fb4e44ecfc24b2fd692229b6b718227f4d0760f9660284f45def764b8cc0ea5455b39dc015f","href":"https://static.observableusercontent.com/files/a1bb110d193558e88abbebe37c439f23efa7f502f8fa42d6270a4fb4e44ecfc24b2fd692229b6b718227f4d0760f9660284f45def764b8cc0ea5455b39dc015f","name":"PMA XN Historic Pricing Data References.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1701299250202.504,"size":356322}] 2d:{"title":"PMA2 - Final Project","cells":[{"id":55,"value":"# PMA2 - Final Project","pinned":false,"hidden":false,"mode":"md"},{"id":217,"value":"# 1. Story Outline\n\nhttps://cs7290.github.io/pma/team4/index.html","pinned":false,"hidden":false,"mode":"md"},{"id":799,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":188,"value":"# ETL","pinned":false,"hidden":false,"mode":"md"},{"id":60,"value":"## 1. Load data - PMA, Cost, Env","pinned":false,"hidden":false,"mode":"md"},{"id":4,"value":"pma = FileAttachment(\"pma-2.xlsx\").xlsx()","pinned":false,"hidden":false,"mode":"ojs"},{"id":66,"value":"env = FileAttachment(\"env_info.xlsx\").xlsx()","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"cost = FileAttachment(\"PMA XN Historic Pricing Data References.xlsx\").xlsx()","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"## 2. Extract, Transform & Merge the data","pinned":false,"hidden":false,"mode":"md"},{"id":75,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":104,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":78,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":92,"value":"fuel_oil = {\n let fuel_oil = cost.sheet(\"U.S. No. 2 Heating Oil Resident\").map(d => ({YearMonth: d.A, value: d.B}));\n // Remove extraneous rows at the beginning of the sheet\n fuel_oil = fuel_oil.filter((d, i) => i > 1);\n\n fuel_oil.forEach(item => {\n item.formattedDate = item.YearMonth;\n});\n\n fuel_oil.forEach(item => {\n item.unit_value = item.value;\n});\n \n return fuel_oil;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":95,"value":"electric_data = {\n let electric_data = cost.sheet(\"grid\").map(d => ({MSN: d.A, date: d.B, Value: d.C, Column_Order: d.D, Description: d.E, Unit: d.F}));\n \n // Remove extraneous rows at the beginning of the sheet\n electric_data = electric_data.filter((d, i) => i > 1);\n\n electric_data = electric_data\n .filter(d => {\n return d.date >= 201200 && d.date <= 202400;\n });\n\n electric_data = electric_data\n .filter(d => {\n return d.Description === \"Average Price of Electricity to Ultimate Customers, Commercial\"\n });\n\n electric_data.forEach(item => {\n item.formattedDate = item.date.toString().substr(0, 4) + '-' + item.date.toString().substr(4, 2);\n});\n\n electric_data.forEach(item => {\n item.unit_value = item.Value / 100.0;\n});\n\n return electric_data;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":98,"value":"natural_gas_data = {\n let natural_gas_data = cost.sheet(\"natural gas\").map(d => ({MSN: d.A, date: d.B, Value: d.C, Column_Order: d.D, Description: d.E, Unit: d.F}));\n\n // Remove extraneous rows at the beginning of the sheet\n natural_gas_data = natural_gas_data.filter((d, i) => i > 1);\n\n natural_gas_data = natural_gas_data\n .filter(d => {\n return d.date >= 201200 && d.date <= 202400;\n });\n\n natural_gas_data = natural_gas_data\n .filter(d => {\n return d.Description === \"Natural Gas Price, Delivered to Consumers, Commercial\"\n });\n\n natural_gas_data.forEach(item => {\n item.formattedDate = item.date.toString().substr(0, 4) + '-' + item.date.toString().substr(4, 2);\n});\n\n natural_gas_data.forEach(item => {\n item.unit_value = item.Value / 10;\n});\n return natural_gas_data;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":128,"value":"// Merge datasets\nmerged_data = grouped_pma_data.map(consumption => {\n let costData;\n switch (consumption.type) { \n case \"Fuel Oil (No. 2)\":\n costData = fuel_oil.find(item => item.formattedDate === consumption.formattedDate);\n \n break;\n case \"Electric - Grid\":\n costData = electric_data.find(item => item.formattedDate === consumption.formattedDate);\n break;\n case \"Natural Gas\":\n costData = natural_gas_data.find(item => item.formattedDate === consumption.formattedDate);\n break;\n }\n\n let env_data = data_env.find(d => d.formattedDate === consumption.formattedDate);\n \n return {\n ...consumption,\n costValue: costData ? costData.unit_value : null,\n avg_air_temp: env_data? env_data.avg_air_temp: null,\n avg_dew_temp: env_data? env_data.avg_dew_temp: null,\n };\n});","pinned":false,"hidden":false,"mode":"ojs"},{"id":316,"value":"// Merge datasets\nmerged_data_cost = merged_data.map(consumption => {\n return {\n ...consumption,\n cost: consumption.costValue * consumption.quantity\n };\n});","pinned":false,"hidden":false,"mode":"ojs"},{"id":190,"value":"# 2. Story","pinned":false,"hidden":false,"mode":"md"},{"id":227,"value":"## 2.1 Explore Energy Usage","pinned":false,"hidden":false,"mode":"md"},{"id":661,"value":"1. Analyzing energy consumption trends grouped by building reveals that the Charles Shipman Payson Building is the dominant contributor to energy usage.\n2. Regarding the Charles Shipman Payson Building, it's noteworthy that a downward trend has been evident since 2020","pinned":false,"hidden":false,"mode":"md"},{"id":230,"value":"overall = Plot.plot({\n width: width,\n margin: 50,\n title: \"Total Usage/Quantity Trend\",\n color: { legend: true },\n marks: [\n Plot.frame({ strokeOpacity: 0.1 }),\n Plot.rectY(\n merged_data_cost, \n Plot.binX(\n { y: \"sum\" },\n {\n fx: \"building\",\n x: \"date\",\n y: \"unified_quantity_mbtu\",\n fill: \"building\",\n tip: true\n }\n )\n ),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":694,"value":"Since Charles Shipman Payson Building is the predominant building in usage, my next focus will be on understanding the types of energy contributing to the overall decrease in usage. The graph below illustrates that both the electric grid and fuel oil experienced a decline in 2021 and 2022 relative to 2020. Conversely, natural gas usage saw an increase in 2021 compared to 2020, but it also showed a decrease in 2022 when compared to 2020.","pinned":false,"hidden":false,"mode":"md"},{"id":700,"value":"viewof groupbybuilding4 = Inputs.select(d3.group(merged_data_cost, d => d.building), {label: \"Select a Building\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":404,"value":"viewof groupbytype4 = Inputs.select(d3.group(groupbybuilding4, d => d.type), {label: \"Select a Type\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":670,"value":"overall2 = Plot.plot({\n title: \"Total Usage/Quantity Trend Group By Building & Type\",\n color: { legend: true },\n marks: [\n Plot.frame({ strokeOpacity: 0.1 }),\n Plot.barY(\n groupbytype4,\n Plot.groupX(\n { y: \"sum\" },\n {\n fy: \"building\",\n x: \"year\",\n y: \"unified_quantity_mbtu\",\n fill: \"building\",\n tip: true\n }\n )\n ),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":712,"value":"## 2.2 Explore Overall Energy Usage Cost","pinned":false,"hidden":false,"mode":"md"},{"id":705,"value":"My next inquiry is whether the overall costs have decreased in comparison to 2020, similar to the trend observed in energy consumption. The answer, however, is negative. Despite a reduction in overall energy usage since 2020, the total costs actually increased in both 2021 and 2022 compared to 2020.","pinned":false,"hidden":false,"mode":"md"},{"id":399,"value":"overall3 = Plot.plot({\n width: width,\n margin: 50,\n title: \"Total Usage/Quantity Cost\",\n color: { legend: true },\n marks: [\n Plot.frame({ strokeOpacity: 0.1 }),\n Plot.rectY(\n merged_data_cost, \n Plot.binX(\n { y: \"sum\" },\n {\n fx: \"building\",\n x: \"date\",\n y: \"cost\",\n fill: \"building\",\n tip: true\n }\n )\n ),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":723,"value":"My next question is: which type of energy usage is responsible for this rise in costs? Examining the electric type, we observe a decrease in 2021, followed by a significant increase in 2022 relative to 2020. In the case of natural gas, the costs increased in both 2021 and 2022 compared to 2020. This trend of rising costs over the past two years is also evident in the fuel oil type.","pinned":false,"hidden":false,"mode":"md"},{"id":716,"value":"viewof groupbybuilding5 = Inputs.select(d3.group(merged_data_cost, d => d.building), {label: \"Select a Building\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":416,"value":"viewof groupbytype5 = Inputs.select(d3.group(groupbybuilding5, d => d.type), {label: \"Select a Type\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":421,"value":"overall4 = Plot.plot({\n title: \"Total Usage/Quantity Cost Group By Building & Type\",\n color: { legend: true },\n marks: [\n Plot.frame({ strokeOpacity: 0.1 }),\n Plot.barY(\n groupbytype5,\n Plot.groupX(\n { y: \"sum\" },\n {\n fy: \"building\",\n x: \"year\",\n y: \"cost\",\n fill: \"building\",\n tip: true\n }\n )\n ),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":726,"value":"## 2.3 Why has energy usage declined while the overall energy costs have risen compared to 2020?","pinned":false,"hidden":false,"mode":"md"},{"id":759,"value":"The answer lies in the unit cost - it's due to inflation?!","pinned":false,"hidden":false,"mode":"md"},{"id":735,"value":"viewof groupbytype6 = Inputs.select(d3.group(merged_data_cost, d => d.type), {label: \"Select a Type\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":743,"value":"overall5 = Plot.plot({\n x: { grid: true, label: \"Year\" },\n y: { grid: true },\n color: { scheme: \"BrBG\" },\n title: \"Unit Cost Trend\",\n width,\n subtitle: \"unit: $\",\n marks: [Plot.lineY(groupbytype6, { x: \"date\", y: \"costValue\", tip: true })]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":193,"value":"## 3. Future work","pinned":false,"hidden":false,"mode":"md"},{"id":782,"value":"## 3.1 Explore weather info","pinned":false,"hidden":false,"mode":"md"},{"id":195,"value":"viewof metrcis = Inputs.select([\"avg_air_temp\", \"avg_dew_temp\", \"avg_sea_level_pressure\", \"avg_wind_direction\"], { label: \"Metric\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":198,"value":"viewof year = Inputs.select(Array.from(new Set(merged_data.map(d => d.year))), { label: \"year\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":203,"value":"choosedData = merged_data.filter(d => d.year === year);","pinned":false,"hidden":false,"mode":"ojs"},{"id":210,"value":"overall6 = Plot.plot({\n title: \"Weather Metric Trend\",\n marks: [\n Plot.barY(choosedData, {x: \"formattedDate\", y: metrcis, fill: \"skyblue\"})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":145,"value":"## 3.2 Correlation Analysis ==> Env vs Usage","pinned":false,"hidden":false,"mode":"md"},{"id":339,"value":"### Observation\n\nFor buildings using electricity from the grid, there is a consistent positive correlation between air temperature and electricity consumption. As the air temperature rises, so does the electricity usage. This trend is particularly noticeable in the case of 142 Free Street, where the correlation coefficient is 0.54. In contrast, buildings using natural gas exhibit a negative correlation between air temperature and energy consumption. A notable example is the Portland Museum of Art, which shows a correlation coefficient of -0.4, indicating that higher temperatures lead to lower natural gas consumption.","pinned":false,"hidden":false,"mode":"md"},{"id":157,"value":"viewof groupbytype = Inputs.select(d3.group(merged_data, d => d.type), {label: \"Select a Type\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":160,"value":"viewof groupbybuilding = Inputs.select(d3.group(groupbytype, d => d.building), {label: \"Select a Building\"});","pinned":false,"hidden":false,"mode":"ojs"},{"id":174,"value":"correlation = calculateCorrelation(groupbybuilding, 'avg_air_temp', 'quantity');","pinned":false,"hidden":false,"mode":"ojs"},{"id":148,"value":"scatterPlot = Plot.plot({\n title: \"Temperature vs Energy Consumption\",\n marks: [\n Plot.dot(groupbybuilding, { x: \"avg_air_temp\", y: \"unified_quantity_mbtu\", stroke: \"purple\"})\n ],\n x: {\n label: \"Average Air Temperature (°C)\"\n },\n y: {\n label: \"Energy Consumption mbtu\"\n }\n});\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":79,"value":"## Helper Functions","pinned":false,"hidden":false,"mode":"md"},{"id":82,"value":"function getYearMonth(dateString) {\n const date = new Date(dateString);\n const year = date.getUTCFullYear(); // getUTCFullYear ensures we get the year in UTC\n const month = date.getUTCMonth() + 1; // getUTCMonth returns 0-11, add 1 for 1-12\n return `${year}-${month.toString().padStart(2, '0')}`; // Pad single digit months with a leading zero\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"function average(arr) {\n return arr.reduce((a, b) => a + b, 0) / arr.length;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"// Function to parse and format dates\nfunction formatDate(dateString) {\n return new Date(dateString).toISOString().substring(0, 7);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":110,"value":"function calculateCorrelation(data, column1, column2) {\n const n = data.length;\n const mean1 = d3.mean(data, d => d[column1]);\n const mean2 = d3.mean(data, d => d[column2]);\n const cov = d3.sum(data, d => (d[column1] - mean1) * (d[column2] - mean2)) / n;\n const std1 = Math.sqrt(d3.sum(data, d => Math.pow(d[column1] - mean1, 2)) / n);\n const std2 = Math.sqrt(d3.sum(data, d => Math.pow(d[column2] - mean2, 2)) / n);\n return cov / (std1 * std2);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":252,"value":"plotUsageBy = function(data, by) {\n return {\n title: \"Total Usage/Quantity Trend\",\n subtitle: `Group By ${by}`,\n marginLeft: 50,\n width: 1000,\n // Configure the x scale\n marks: [\n Plot.frame(),\n Plot.line(data, Plot.binX({y: \"sum\"}, {\n x: \"date\",\n y:\"unified_quantity_mbtu\",\n tip: true,\n stroke: \"pink\",\n thresholds: d3.utcMonth,\n })),\n ],\n };\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":322,"value":"plotCostBy = function(data, by) {\n return {\n title: \"Total Cost Trend (unit: dollar)\",\n subtitle: `Group By ${by}`,\n marginLeft: 50,\n width: 1000,\n // Configure the x scale\n marks: [\n Plot.frame(),\n Plot.line(data, Plot.binX({y: \"sum\"}, {\n x: \"date\",\n y:\"cost\",\n tip: true,\n stroke: \"pink\",\n thresholds: d3.utcMonth,\n })),\n ],\n };\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}