Combining Results from Multiple Panels / Multiple Senses into a Single Report Using Excel

I do have multiple panels but I’ve been able to solve with a single Sense, but I figured I could take a shot at how to combine exports from multiple panels using Excel, since that might be a good solution for some. Though I prefer R for my data-crunching Excel has a nice way to create what many people seem to want, a wide report of Device Usage vs. TimeStamp

Assumptions / pre-conditions

  • Both boxes are monitored separately (power for the second panel doesn’t flow through the first panels mains)
  • All devices are named by the user to be unique across both panels (Sense can’t guarantee unique IDs across two or more Sense monitors, especially when accounts can’t be linked).
  • You want each Other and Always On per panel to be accounted for separately.
  • Sense units are in the same time zone

Here are the steps:

  1. Export/Download the same time period and interval for each of your two Sense units. Rename the first .csv before downloading the second because they appear to otherwise overwrite one another.

  2. Open both in Excel. Rename the Always On, Other, Total Usage, and Solar Production (if needed) entries in the Name column in one of the files. I suggest appending an “_2”. You can do this using the Find/Replace All command under edit. Or you can choose to append “_2” to all the names in the Name column by using another column and the concat() function, then repast the values back into the original names column and get rid of the auxiliary column.

  3. Copy all the data rows from the second spreadsheet to the first spreadsheet. It doesn’t really matter where you put them - you can paste them at the end of the first set of data, or insert the data rows just after the label.

  4. Select the whole data range including labels and pick Pivot Table under the Data menu. You’ll need to OK the source data range and the creation of the Pivot Table on a new page.

  5. Fill out the Pivot Table Fields form like below. Drag the DateTime into rows, Name into columns and kWh into summation values. Once done, you will have a nice wide pivot table that has DateTime vertically in chronological order and all Sense devices on the horizontal axis, ready fo further analysis.