We made it through 2020 ! Congrats ! This is the time when I look back at my electricity usage for the previous year, do some accounting and make changes for better analysis and reductions in the New Year. Usually I’ll do most analysis in R, but I typically do the high-level data assessment in Excel first, just to get my arms around the data I’m dealing with. And even though Sense has some very nice Trends analysis, I generally find I can sort things out better in Excel Pivot Table of Sense exported hourly data. Here’s how I do it.
Read in Sense hourly data for 2020 and convert to wide format
Sense data is exported in a long format, with hourly data for every device, plus Total Consumption (and optionally Solar Production) as separate time-stamped record. The first step is to elect it all an convert to a separate Pivot Table sheet.
The next step is configuring the Pivot Table to arrange your energy usage data in a wide format, with each device in its own column. Drag ‘DateTime’ into the Rows, ‘kWh’ into the Summation, and Device ‘Name’ into the columns and you will end up with the layout below. Excel automatically adds Quarter and Year grouping to the rows, but we’ll change that in a bit.
Sense actually carries over a little data fro 2021 even though I only exported data for the year 2020, so I use the Row Tables to limit the data to 2020.
The next step is to change the grouping for the DateTime data - I prefer to see Hours, Days and Months.
Once I have the Rows grouped the way I want, I can collapse all the Rows back to highest level, Months.
Time to clean the data
Once I have things grouped the way I want, I can get rid of the Pivot Table Field List, and begin to clean the data. One thing I notice is a column without a name, “(blank)”.
I can double-click on that 0 for “(blank)” and see all the hourly data that rolls up into that 0. It looks like this was a temporary device that Sense never converted into a real device. Can be removed.
To remove, I got to Column Labels and deselect “(blank)”.
Another thing that I’m going to want to do is to sort out all the Vacuum devices that have showed up over and disappeared over time. We have a Central Vacuum, which is used most of the time and do have at least one other plug-in vacuum. This shows me that I’ll either need to mask other out or roll into the Central Vacuum. Also, the Traveling HS110s have been on different devices through out the year. The first one has been on one of my right garage opener since about June. The other HS110 seen most of Oct. inflating a Halloween dragon in our front yard.
BTW - the “Grand Total” on the far right is pretty much a useless summation since it includes all detected devices plus Total Usage plus Solar Production.