How Does Data Export Work? Why is it Limited?

[sense9150]

It wasn’t easy to find, but I just did find it at home dot sense dot com slash usage (links not allowed, even to the sense website?)

I too would like to request a higher resolution of data. I’m guessing that all the data is there somewhere and the export function is summing the detailed data into the hourly or daily data that is exported.

Can I ask where this data is coming from? Is it stored in my Sense device and if so, how much can be held in there? Or is it stored on Sense servers and if so, same question.

I noticed a problem with the export. If I’m looking at today’s data and I try to export it, I get yesterday’s data. Similar goes for week, month and year. For daily data I can just wait for tomorrow to get today’s data. But it’s not practical to wait until the end of the year to get this year’s data. I don’t mind getting a partial year’s worth of data when I ask for this year’s data and the year isn’t over yet.

I’ve been using the Sense device since I installed it on July 29 until today, August 20. So when I ask for this month’s data, I only get the last few days of July. It would be nice if I could enter a date range and then get any set of data I want, of course at any resolution I want.

Can I request the data be formatted differently for ease of analysis? We all know what it’s like now so I won’t describe it. But I would propose putting device ID related data along the X axis and the time intervals along the Y axis. This would make it so much easier the plot a graph of the data in Excel when all the power data for each device is in its own column. The Device ID, Name, Device Type, Device Make, Device Model, Device Location can take up the first six rows of the csv data; that would be fine. Some people I see are using programs to rearrange the data. So I guess my request would have to be an option so as not to make people that are happy with the existing format unhappy.

Thanks for your consideration and please let me know what you think.

[kevin1]

Good questions - I’ll attempt to answer as a user who has exploited exported data for a lot of different projects in the Data Analysis Category of the community.

• Export is found in the upper right of the Usage (Sense) Dashboard of the Web App (not the iOS or Android app). Right now there is a known bug with the Web App and Safari, so you’ll want to use Chrome or FireFox to view the Usage summary charts.

• Export Data is the Same as in the Summary Graph - Export outputs the same data that is shown in the summary graph for whichever time period (Day/Week/Month/Year/Billing) you are looking at, though you can choose to export with either Hourly or Daily resolution. So the graph shows you the extent of what data you should expect to see in Export.

• New Data is Aggregated Hourly - Usage bar charts in the Dashboard are aggregated and updated with new data every hour, usually a few minutes after the hour. So the 10AM to 11AM data will be aggregated and added to the Day/Week/Month/Year/Billing charts at about 11:10PM. Aggregation is the process of summing up the new data incoming data stored at the Sense servers for the new period. Aggregation takes time and compute power, especially for many thousands of Sense accounts that are all updating each hour, so Sense probably staggers the start times of all the aggregations over maybe 15 minutes, so not all of them hit the servers at the same time.

• Sense Stores Much More Detailed Data in the Cloud (Sense mothership) - Your Sense monitor gathers data at a very accurate level, with the ability to capture millions of samples per second, but then processes the data so that is can send a better organized and far more compact set of data to the Sense servers hosted at Amazon AWS. You can see some of the more detailed granularity data in the Sense Power Meter, which contains half-second resolution data. If you want more info, read this.

Other - Usage Percentage Totals

@james_reilley, This is just speculation based on my experiences plus a view of what’s exposed via Sense. I think there are really two levels of ML inference going on with Sense, one that relies integrally on the Sense monitor and another that relies solely on the mothership. One foundational thought - due to limited home upstream data rates, the Sense monitor can’t send all the 4M samples/sec the monitor is capable of back the the Sense mothership. Therefore the Sense monitor has to do some…

• Underlying Sense Data stored at the Mothership Can be Messy - We’re used to seeing nice, simple Sense data the apps, but the underlying data can be messy, between data dropouts (numerous causes), CT/sensor issues (which garble the data), etc, so Sense often does a fair amount of extra work at the Mothership to clean up the data before we see it in the Power Meter or subsequent displays. In some cases missing data gets filled or garbled data gets reconstituted. Occasionally Sense will offer views of their internal data viewer that includes greater resolution of some regions (not all) of the data, plus separated out by leg/phase.

• Export uses Pre-Computed Aggregated Data - Even though I wish I could get more detailed data directly from Sense, I think there are two good reasons why they only export data that is already used in the Day/Week/Month/Year/Billing summary. 1) It is already there, pre-cleaned, computed and ready - doing other ranges, would require significant additional overhead that Sense has to pay for. 2) The higher resolution could be messy, with holes, and potentially missing values.

• You can access higher resolution data form Sense, but it comes at a cost - I use an open source home automation hub called Home Assistant. It has a very nice Sense integration that samples data from the Sense mothership every 2 minutes (configurable). But this approach requires extra hardware and expertise, plus exposes you to the underlying messiness of rawer data than your you see in the Sense apps or Export…

• 50 Users - 50 Different Preferred Formats - The problem with formats is each person seems to require something slightly different, and there will never be a perfect format for each person’s usage model. The current Sense data format is a very complete and simple “long format” that is very amenable to easy reformatting on the user end. That’s really what export is for - so you can reformat and aggregate to the format you want. I think you’ll be much happier with Sense and other home devices that provide detailed data if you learn how to use the relatively complex, but powerful Pivot Table feature in Excel, or the equivalent in most other spreadsheets. You’re using Excel, and the reshaping can be done in just one command.

1. Import your Sense .csv into Excel, and select the whole data long data table. Here, I’m looking at my Hourly Export for yesterday.

2. Invoke Pivot Table, under the Data menu and click through until you get the Pivot Table Field configuration tab that looks like below. Drag the 3 items with checks into the corresponding Rows, Columns and Summation boxes.

3. You’ll get a wide version of the same data that looks like this. Each column shows the hourly usage for each device. There will be one or two special columns, Total Usage and Solar Production, that represent house level data. You won’t see an Other, because that’s just the energy that isn’t accounted for when you sum up Always On and all the devices. You’ll also notice blanks - those are hours where usage is zero.

[kevin1]

@sense9150 - Once you have mastered simple Pivot Tables with Sense Hourly Export data, you can move on to a couple more capabilities you requested. If you Export data for an entire year at Hour granularity, you’ll get a Pivot Table that looks like this. Setting DateTime for the rows automatically separates into Months and DateTime levels of hierarchy (Excel tried to do intelligent things with DateTime)

You can click on the +'es to expand any month into daily data.

Next you can ge more selective about which devices you want to see. Clicking on the “Column Labels” down arrow allows you to select only those devices you want to look at.

That gives me just my two AC units, Always on and Total Usage

You can also get selective about the time period by hitting the down arrow next to the Row Labels - Here I’m getting rid of the incomplete month of August.

And finally, I can do a quick PivotChart by hitting the PivotChart icon in the toolbar of the PivotTable Analyze menu.

[kevin1]

@sense9150, one more fun feature that another user pointed out to me, and exactly what you seem to be looking for. Here’s the same full year (2022) data that I Pivot Table summarized, and Pivot Chart charted in the previous post, after I removed Months from the row Field - now data is summarized by day / DateTime. I have also done one more thing - I did an “Insert Timeline” from the Pivot Table Analyze toolbar.

The timeline lets you do realtime selection and charting of custom date ranges.

[kevin1]

@sense9150,

One more fun visualization for you based on Export. If you export your current year or previous year in Day resolution, you can use a web utility I built to create a chart like this, that shows your weekly top 10 device usage by percentage and absolute measure.

You’ll notice some ugliness in the midd of my 2021, mid-summer. That’s a period when Sense was “detecting” too much device usage - I think it was missing my AC turning off sometimes, and Always On grew larger than it really was.

To use the utility, Export a yearly file in Day resolution, then go to this link:

https://pgstats.shinyapps.io/SenseUsagePlot/