I have another unrelated question about the data collected. Is there a way I can export the device data and then overlap them, e.g. in Excell to figure out if I have duplicates so I can merge them or rename them accordingly?
A post was split to a new topic: Can These Approaches Improve Device Detection?
I have seen three ways to do waveform overlay to look for relationships. Each has its own challenges:
- Home Assistant with Sense Integration - requires free Home Assistant and a platform to run it on along, plus a little IT system knowhow. Here’s an example of an overlay using data from Sense - I have charted Total Usage overlaid with the native detections for both our EVs, plus a separate DCM-measured view of the Model 3 charging.
- Sense Data Export with Excel or another tool - A little limited because the minimum increment for export is 1 hour, plus you’ll need to deal with missing hours. But entirely possible with a little knowledge of Excel and Pivot Tables. Here’s the same power usage overlay of the two Model 3 devices I show above, done with Sense Data Export and Excel.
BTW - I have to show as an Excel bar chart because when I switch to a line chart, the two lines exactly overlay each other (but they are the same data, one from Sense native detection and one measured)
I’ll add how to do this in Excel in the comments below.
- Graphical Overlay - Requires a graphic editing program. explained here.
Here’s how to do it in Excel.
- Export the time period you want to look at, from the Sense Web App. In my case I’m doing it for the month of January 2023, so I go to the Month Dashboard under the top level Dashboard. The Port button is in the upper left.
- Download with the Interval set to Hour, not Day
- Open the downloaded CSV file in Excel, then select the all the data below the Sense column names.
- Hit “Summarize in PivotTable” in the Data menu. Click through the dialog box to create a new sheet, then fill in the PivotTable Fields like below. If you use a time period greater than a month, Excel might try to summing the energy usage into months and quarters, and will require an additional step or two to get back to hours.
- Use the Column Labels filtering to choose just the Devices your want to compare. In this case I’ll be comparing ‘Tesla Model 3’ native detection against ‘Model 3’ DCM detection.
- Next, insert a PiviotTable Timeline using the Sense DateTime. The Timeline will give you the ability to control the time window you are looking at in the PivotTable. This will be useful when we zoom-in of the dats where we want to do the overlay / comparison.
- Almost there - The next step is to create a PivotChart that will show the hourly energy usage of the two devices side-by-side. It will default to a bar chart.
-
Here’s the near finished product - with the PivotTable, the Timeline (showing the whole month of Jan 23) and the PivotChart.
-
The final step is to zoom into the dates of interest for a little clearer picture.
-
Looking closer, we can see nearly perfect matches between the kWh bars the ‘Tesla Model 3’ native detection vs ‘Model 3’ DCM detection.
- One more option that works better if the two (or more) devices you want to overlay are very different in energy usage, or if they might only partially overlap (one sometimes on when the other is off) is to do a scatter chart of the two kWh values against each other at each time point. Correlation/overlap will show up as a straight line of points. Points off the straight line will indicate hours where the devices diverge in behavior. Here’s my example my Sense native detections perfectly correlating with the measured DCM data.
- I’m going to add one more wrinkle to this discussion - this technique can be used to look for relationships, even when they aren’t exact or there all the time. I have added Total Usage to the same chart. We know that when the the Model 3 is charging, the Total Usage will be significantly correlated with the Model 3 energy usage because it dominates total usage. The graph shows exactly that. And if we were to avoid charting the places where the charger was essentially zero, we would have an even more exact fit ! This kind of chart might be the best way to “see” the relationship using Sense Exported data in Excel.
- And if you really want the best way to search for correlation between pairs of devices in Excel, you can use Excel’s free Correlation Add-in pack to construct a Correlogram (correlation matrix) between the different devices. Here’s that same chart above expressed as a Correlogram:
- Not a lot to look at, but tells a lot. A ‘1’ means that a device is 100% correlated with another - that’s why the diagonal, which shows a device’s correlation with itself is 100%. You’ll notice that the Model 3 (DCM) is highly correlated with Tesla Model 3 (native detection), no surprise. Model 3 (DCM) is only somewhat correlated with Total Usage. From the graph we can see why - all the ‘near zeros’ when the Model 3 isn’t charging and Total Usage is all over the place detract from the correlation. But surprise, surprise, the Tesla Model 3 (native detection) is far more correlated with Total Usage. Why ? - It’s the ‘near zeros’. Sense native detections don’t provide hourly data in Export wh the device is off. Those NA (no data) points don’t count against the Tesla Model 3 (native detection) from a correlation perspective, while the ‘near zeros’ measured by the Model 3 (DCM) do degrade the corruption. So one has to be very careful when thinking about what they are trying to understand when setting up correlation tests. In your case, you probably want empty hours filled in with zeros for all devices because it is important to look for off/off and on/on correlation to see id two waveforms are the same device.
2 Likes
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.