Community
OpenEnergyMonitor

Community

Manually edit feed data (to delete bad data) What tools to use?

Hello - I’m running an emonpi install and using it to log my OpenEVSE and some current clamps I have on the house power. Working well (I had tried setting up a FAMP server, but weird problems were making me crazy, plus emonpi is just so nice and packaged)

Here’s my problem… Last month some wonky data came out of my current meter for reasons that have been corrected. However, I now have points in my data that are many orders of magnitude higher than what they ought to be, and it’s completely trashing my views (because of the zoom) and causing havoc with my historical data. Is there any way I can go in and scrub the insanely high values out of the database?

I guess I could directly edit the SQL database? Any suggestions on that tip? I imagine that I’d need to set permission for remote access and use MySql Workbench on another machine…

Or maybe write a post processor script that would set any values over… say… 1000 kwh a day and set them to zero? Maybe this already exists?

I’d appreciate any suggestions as to how I could attack this problem.

Thanks!

Andy

The data isn’t held in mySQL tables (unless you have specifically used mySQL feed engines) the default is phpfina or phptimeseries files.

Have you looked at the post-process module?

(I’m surprised there doesn’t appear to be a “removespikes” post process already)

or there is the removespikes script in the usefulscripts repo that might help
https://github.com/emoncms/usefulscripts#remove-spikes

[edit - scrub that, I just noticed the “ONLY WORKS WITH PHPFIWA DATA!” warning]

Andy, a while ago I developed data validation feature for feeds. It can go through a feed dataset and limit its max and min values. It won’t overwrite them to 0 though.

Please have a look at the post linked below. It would be great if you can try it. I never did a Pull Request as I wanted more people to try it and don’t really know if it is a desired feature.

Is this still the avenue for data changed? I have a single day that has completely skewed my history which i woiuld love to remove or trim.

Under Visualisation select Edit Realtime. Sometimes a bit hit & miss but generally works fine. Just zoom in so you get all data points for the view.

And data in the visulaisation area does not match the history graph - i.e. it looks fine and has positive values across use, import, and solar. Seems the issue is only in History view within the My Solar app.

Did you zoom in? There are some oddities in how the graph averages the display, unless you zoom in such that it returns every data point in the window.

OK yep, found the cliff - cumulative use kwh and solar kwh fell to zero and then started accumulating again. Just to be a pain - any pointers in realigning thedata after the drop off continues to accumulate in line with the past data? i.e. from that drop off time to now and ongoing, all data points + 2124.23.
Or can I delete the data in this window for all 6 x feeds and the future data will realign?


An in data viewer I can drill down to where everything dropped off and the CSV output shows some nulls before cumulative starts from zero.


Once an accumulator has lost the accumulation point, it is a real pain to get it back. I think you need to do a post process but @TrystanLea may be able to help.

For him to do so, can you post the input processes used (i.e. are you using one of the accumulator processes).

Hello @Tockley, as @borpin mentioned you should be able to use the post process module to fix this. See the guide here: https://guide.openenergymonitor.org/emoncms/postprocess/

You need to use the powertokwh process using a power feed as the source feed. Let me know if you get stuck

Thanks both - does this mean i need to create a new/unique use, import, and solar kwh feed? I.e. no longer use the existing feeds in the solar app? The powertokwh post process requires a unique name.

Slightly strange jump where there was a period of no monitoring but seems to have mostly corrected the accumulated data:

Yes, the unique name requirement is partly to give an oppertunity to check the result before switching the feeds and deleting the original.

I usually set the new feed name to something like solar_kwh2 and then when Im happy delete the original and rename the new solar_kwh2 feed to solar_kwh.

To which bit? :rofl: I think all that is needed is a new accumulating feed not the Use etc as well.

I’ve tried this before and I feel I’m missing something. At what point do you change the input processing to use the new feed?

Is it;

  • Post process to create new feed
  • Add logging to that feed to the input processing (quickly else you will have a data gap)
  • Delete old feed.
  • Rename new feed to old feed name.

This is not an uncommon request.