Introducing How I am Testing My Sense CSV Data with LLMs

, ,

Using LLMs to Analyze Sense CSV Data: An Introduction

This is the first post in a multi-part series on Sense CSV data and LLMs, what we can learn from it, and what some limitations are.

Hi all,
Lately, I’ve been testing out some of the most common LLMs to see what kind of data can be extracted from the CSV files from my personal Sense monitor. Some of this is for fun, but it has also been my intent to see what I can do to help Sense users get more out of their own data—especially if, like me, you—the user—aren’t spreadsheet wizards, nor do you feel that you should spend a very long time doing math or other tedious work organizing a spreadsheet to understand key data points.

Though I can say, once I got started, it’s almost meditative. I’m far from even good at manipulating spreadsheet data, but I can see why people would want to, for sure! The new Cory Doctorow YA novel (don’t judge), Picks and Shovels, also sings the praises of the transformative power in the early days of electronic spreadsheets. It was a key part of what shaped his character Martin Hench, an investigative techie accountant over three books spanning Hench’s whole career.

Anyway, personal feelings aside—as was the case in the ’70s and ’80s when spreadsheet programs were transformative—LLMs can do that again. This is assuming LLMs can handle the data correctly. If so, LLMs can unlock insights in minutes rather than hours or days, with a much lower learning curve.

On the other hand, if LLMs don’t handle the data correctly, make errors, or “get bored” and only sample part of the data set, it can take hours of work to sort out sloppy data and misinformation that an LLM spat out in under a minute. There is a lot that can go wrong, and it’s a weird thing to have to “trust” or even not trust a program—but here we are.

So at least now, I wanted to test if I can—within reason—recommend LLMs to derive some tricky but not complicated data from the CSV and some basic internet searches—such as understanding the local weather, conversion of therms to kWh, or local electrical and gas rates.

The idea is that these questions are something I could do with pre-Gemini off-the-shelf Google products but wouldn’t want to because of the amount of time and work, and tedious data entry involved to do this. For example, searching the air temperature in my area by the hour for a whole month, then lining that up with my hourly heating usage, calculating an average, then calculating in percentages the change and cost of each hour and day that deviated from average.

To start, I wanted to use the free versions of Claude, Gemini, and ChatGPT to answer the same three questions with the same set of CSV data. I will link the data and provide the spreadsheets and examples.

Some snags I ran into immediately: I wanted to do bigger data sets—like several months (Jan–May 21) over a span of all detected devices and all the hourly data in my house. Claude instantly rejected this as way too much data, so I had to cut, and cut again, to settle on a month’s worth of data and only focus on a few devices or total usage.

This is already a significant snag and limitation in what free LLMs can do. I didn’t try the same with Gemini or ChatGPT, but I’m setting the bar with what Claude would accept, as the data set is still big enough to provide some interesting insight—but any smaller and the task starts to be something you would not need an LLM to help with.

I may test out paid LLMs and look into what else to test later, but I chose free and mainstream now. This is because I want there to be a low bar to entry. I do not expect Sense users to pay a pricey monthly/yearly subscription just for some extra insights. Paid versions would appeal to a very small number of folks who may be interested in trying this for themselves.

Please be aware that with free, and perhaps even non-free versions, these LLMs will likely use your data for model training purposes and may not be private.

I would be skeptical to think any large amount of data is truly anonymous, so I just want to advise that if you are uploading your own data, be careful to try to strip as much irrelevant info from it as possible (I know—extra work), and in some cases, use the data points and numbers you want to analyze with generic names for devices. Just be careful of personal information if you are concerned about that.

I am choosing to share as much as possible because I want to be straightforward and honest about my assessments and am not too worried about the limited data I’m sharing. I do admit, though, I would not want to do this with someone else’s data without their explicit, enthusiastic consent.

Finally, this is not scientific, but I am intending to be a little rigorous. It’s meant to spur discussion, with the hope we can all learn something. At this time, I have found using LLMs to organize Sense data—especially the free ones—to be very mixed. I plan to discover more along the way.

I think that if you don’t want to watch a TV show or a movie because you know the end or the twist, it probably wasn’t worth watching to begin with. This is a generalization, but what I’m saying is that the journey here will be the discussion. The conclusion is that I wouldn’t confidently use LLMs—at least free ones—to get real insights from your Sense CSV, not yet. In my opinion, you would have to spend more time verifying the data is correct. But it may be worth playing with for fun if you are curious about seeing what happens.

For those who are interested, I think it’s worth someone’s time to investigate how one can best and reliably use LLMs to get good, reliable insights on their data sets if possible. This is a conversation, and if you love or hate LLMs, that’s fine—speak up! Just be respectful. We may not be working toward the exact same goal, but as Sense users, we all are likely here to better understand Sense or want to share what we have found.

OK, with that said, I’m going to start posting my results tomorrow!

Disclaimer: uploading your data into an LLM, especially a free one, may not necessarily be private and will likely be used as training data for said firm’s current and future models.

3 Likes

I’m sure @kevin1 will have some good input on this; I’ve seen him post similar things with his data that he’s done some crunching on and the results are pretty cool. I would imagine that using a LLM there could be a lot more that could be done, and with relatively short work/time involved.

I may try some similar testing with my own data using Grok, since I pay for the basic usage of that service. And I just recently got an SDR up and running on a raspberry pi, and I had Grok help me setup the configuration of RTL-AMR so that I can pickup my water meter broadcasts. I had it help me setup an Influx database, and then it helped me setup Grafana and a few charts that I can pull up on any local device with the raspi IP address and I can now watch my (near) realtime water usage.

All that to say, I still had to do a lot of work myself getting things to work, but with the help of Grok, it did the hard part for me and I ironed out the kinks to get a working solution, so maybe the same thing (or similar) could be done with Sense data.

2 Likes

I’m looking forward to the discussion. Based on the first couple posts, however, this thread may not be for non-technical users because there is a lot lingo flying around. Some of these I already knew, others I just looked up. In case it helps anyone else:

  • LLM = large language model. LLMs are a subgroup of artificial intelligence programs distinguished by allowing interaction with the user using conversation rather than programming.
  • CSV = comma separated value. If you choose to download Sense data, this is the data format that you get. This format was selected because it works on a lot of different operating systems and software packages.
  • Gemini = brand name of Google’s latest LLM.
  • Grok = brand name of LLM from one of Elon Musk’s companies
  • YA = young adult, a target age for literature.
  • SDR = software defined radio, a technology for deciphering coded transmissions
3 Likes

That’s great to hear, and the amount of detail it appears you put into setting up your house and monitor is impressive! It will be interesting to see what Grok does! Keep us posted if you like.

Yeah, I’ll post more tomorrow and release some testing methods I have soon. I just want to play around with them a little more first to make sure they appear to work.

Using paid Claude, I was able to set up a Pi-hole with no prior knowledge. While dealing with some errors along the way, it was an interesting way to learn and ask questions when hung up. With that, I had to disconnect it as it really didn’t work as well as I wanted it to, and while I got it to block some ads sometimes, it seemed to break more than I expected. There was probably a lot more work to be done. Maybe I’ll get back to it or perhaps find another project for my Raspberry Pi Zero 2W.

Thanks again!

Thank you. I was worried that this, at times, may be technical and assume prior knowledge. I also didn’t want to throw around a lot of hard-to-parse language, so I encourage everyone to ask questions and look stuff up. I can make corrections along the way too.

I do think my initial post was pretty long, so I didn’t want to spend too much more space trying to explain each point.

With that, at least for “YA,” I see it as a sticker at the public library enough where, to me, it seems more common than “young adult,” hah.

Thanks @Edison517,

The kinds of analysis I have played with the most with Sense export (CSV) are:

  1. Look for Missing Data, especially in the mains (Usage) and solar (Solar Production). Those are a good indicator of general internet and Sense reliability.

  2. If it looks like the mains and solar data is all there, compute and compare the net usage from Sense against the net usage from my PG&E utility net meter. The link below shows what these first two look like:

  1. Then it’s useful to look at usage and changes in usage by device category, or the top 10 individual devices.
  1. Finally I like to look at correlation between my Sense air conditioner (AC) detections and the AC runtimes coming from my upstairs and downstairs Ecobees.

I’m going to try to repeat this kind of analysis using Perplexity Pro. The main caveat is that I typically do my analysis on a yearly basis, but the 25MB file size limit of Perplexity Pro will likely keep me limited to monthly datasets.

ps: pretty impressive that you have the SDR up and running for water flow. I broke down when the local water companies started offering rebates and bought a couple of Flumes, which do similar. Had some fun trying to correlate them against my Rachio sprinkler runtimes here.