This would be out of my league to develop in Python. In my Fortran and Pascal programming days of data acquisition, we routinely used 5,7, or 9 point median filters to smooth and de-spike oceanographic data. I would think this might be a valuable addition to weewx.
It's not a lot of code (bubble sort a small array of input data with the median value being the output). Still, where the weewx developers would put it and how users would configure which channels to filter, the width of the filter, etc., might be a bridge too far. Just a thought. - Paul VE1DX On Tuesday, October 5, 2021 at 10:56:53 AM UTC-3 alankj...@gmail.com wrote: > Here is a description of my adventures cleaning up the database > > http://www.adelieresources.com//2019/01/conversion-from-wview-to-weewx-weather-station-software/ > > On Monday, October 4, 2021 at 6:30:16 AM UTC-7 wfs...@gmail.com wrote: > >> Thanks Tom! I love Weewx and get a kick out of playing with the data. >> My biggest variance here in Springfield Illinois was 4.4 degrees. >> On 4/21/21 at 12:15pm i went from 49.1 to 56.3 then fell back to 54.7. >> No rain that day, just clouds and sun I guess. Breezy. >> >> Walt >> >> On Sunday, October 3, 2021 at 8:39:09 PM UTC-5 tke...@gmail.com wrote: >> >>> Wow! Some serious SQL fu! >>> >>> Very nice. >>> >>> On Sun, Oct 3, 2021 at 12:20 PM wfs...@gmail.com <wfs...@gmail.com> >>> wrote: >>> >>>> Here's an updated query that does better checking and it can delete the >>>> rows or update the temperatures. Test it out on a copy of your database >>>> first. This works for sqlite. Don't know about others. >>>> >>>> Walt >>>> >>>> On Sunday, October 3, 2021 at 6:48:23 AM UTC-5 anc...@gmail.com wrote: >>>> >>>>> Exactly. Is there a way to consider only cases such as +0, -10, +0 >>>>> (i.e. spikes) and not those like +0, -6. -8 ? >>>>> Meanwhile I thank you for your precious help. >>>>> Andrea >>>>> >>>>> Il giorno giovedì 30 settembre 2021 alle 23:03:35 UTC+2 >>>>> wfs...@gmail.com ha scritto: >>>>> >>>>>> Just a reminder, this delete query will delete all situations where >>>>>> the criteria is met. Some of these situations are legit, temps can >>>>>> change >>>>>> rapidly. You want to make sure it's going to delete only the records >>>>>> you >>>>>> want before running it. There are probably some enhancements that can >>>>>> be >>>>>> made to the query to find more obvious spikes. Something like +0, -10, >>>>>> +0 >>>>>> might be a spike, whereas +0, -6. -8 is not, the temp is just falling >>>>>> fast. >>>>>> >>>>>> On Thursday, September 30, 2021 at 1:11:05 PM UTC-5 wfs...@gmail.com >>>>>> wrote: >>>>>> >>>>>>> Well, make sure you back up your database before you try to delete >>>>>>> anything in case this goes wrong. I would >>>>>>> >>>>>>> 1. Stop weewx >>>>>>> 2. Make a copy of the database >>>>>>> 3. Execute the delete SQL >>>>>>> 4. Run the original query again to see if things are OK >>>>>>> 5. Rebuild weewx dailies >>>>>>> 6. Start weewx >>>>>>> >>>>>>> Hopefully I'm not forgetting anything. >>>>>>> >>>>>>> I'm attaching SQL to delete the spiked records. You will have to >>>>>>> change the database name in the .open statement to the db you want to >>>>>>> change. You may need to run sqlite3 with sudo in order to delete >>>>>>> records. >>>>>>> >>>>>>> I thought a better solution would be to update the spiked temp with >>>>>>> an average of the readings surrounding the spike, but I'm having >>>>>>> trouble >>>>>>> coming up with the SQL to do that. >>>>>>> >>>>>>> Walt >>>>>>> On Thursday, September 30, 2021 at 9:41:17 AM UTC-5 anc...@gmail.com >>>>>>> wrote: >>>>>>> >>>>>>>> Thank you Walt, this script works fine! in fact it highlighted that >>>>>>>> spike in my graph: >>>>>>>> >>>>>>>> DT datetime temp temp_prior >>>>>>>> temp_next variance d_back d_forw >>>>>>>> ------------------- ---------- ---------------- >>>>>>>> ---------------- ---------------- ---------- ------------------ >>>>>>>> ---------------- >>>>>>>> 2021-09-30 04:15:00 1632968100 19.2970370370371 >>>>>>>> 19.3363567649282 14.5003779289494 2.38 0.0393197278911508 >>>>>>>> 4.79665910808766 >>>>>>>> >>>>>>>> (I added two columns d_back = |t1-t2| and t_forw = |t1-t3|) >>>>>>>> Now, I need to delete the wrong record: in this case, it is t3 >>>>>>>> (d_forw = 4.8). How can I automatically find it and then remove? >>>>>>>> Unfortunately I don't know the sql language and I can't complete the >>>>>>>> script >>>>>>>> myself. >>>>>>>> >>>>>>>> Thank you very much, >>>>>>>> Andrea >>>>>>>> Il giorno giovedì 30 settembre 2021 alle 15:27:37 UTC+2 >>>>>>>> wfs...@gmail.com ha scritto: >>>>>>>> >>>>>>>>> Here's a query using the "join a table to itself" method if you >>>>>>>>> don't have the LAG function. It prints observations that are outside >>>>>>>>> the >>>>>>>>> average of the surrounding observations by 2+ degrees. I get about >>>>>>>>> 30 >>>>>>>>> observations this year and I think it's legit. Although I don't get >>>>>>>>> spikes >>>>>>>>> in my data, I do use a 15 minute interval and my station is closer to >>>>>>>>> the >>>>>>>>> ground than it should be. A lot can happen in 15 minutes and my temp >>>>>>>>> graph >>>>>>>>> is always kinda wiggly. >>>>>>>>> >>>>>>>>> Caveat Emptor on the attached query. You could modify it to >>>>>>>>> delete or update the offending rows instead of printing them out. >>>>>>>>> >>>>>>>>> Walt >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thursday, September 30, 2021 at 7:52:41 AM UTC-5 >>>>>>>>> tke...@gmail.com wrote: >>>>>>>>> >>>>>>>>>> Yes, it's possible, but it's not a simple SELECT statement. For >>>>>>>>>> example, this query will return all rows where the temperature >>>>>>>>>> difference >>>>>>>>>> between adjacent rows is greater than 1 degree: >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> *SELECT g.* FROM (SELECT dateTime, >>>>>>>>>> datetime(dateTime,'unixepoch','localtime'), LAG(outTemp) OVER >>>>>>>>>> (ORDER BY >>>>>>>>>> dateTime) AS prev_outTemp, outTemp FROM archive) AS g WHERE >>>>>>>>>> ABS(g.outTemp-g.prev_outTemp) > 1;* >>>>>>>>>> >>>>>>>>>> For my own database, this returns >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> *1192323300|2007-10-13 >>>>>>>>>> 17:55:00|64.099998|63.01192382100|2007-10-14 >>>>>>>>>> 10:15:00|51.900002|53.01192756800|2007-10-18 >>>>>>>>>> 18:20:00|44.700001|46.7000011192757100|2007-10-18 >>>>>>>>>> 18:25:00|46.700001|49.51192757400|2007-10-18 18:30:00|49.5|47.400002* >>>>>>>>>> ... >>>>>>>>>> >>>>>>>>>> You would then need to arrange to delete those specific >>>>>>>>>> timestamps. >>>>>>>>>> >>>>>>>>>> The select statement will only work with modern versions of >>>>>>>>>> sqlite, which have the LAG() function. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Thu, Sep 30, 2021 at 3:35 AM anc...@gmail.com < >>>>>>>>>> anc...@gmail.com> wrote: >>>>>>>>>> >>>>>>>>>>> Hi there, >>>>>>>>>>> I need to remove some temperature spikes from my weewx.sdb >>>>>>>>>>> database. I know this has been already asked many times, but my >>>>>>>>>>> case is >>>>>>>>>>> different. I need to delete spikes in which the values are >>>>>>>>>>> climatically >>>>>>>>>>> acceptable: so, it is not sufficient to give (temperature is >>>>>>>>>>> intended in °C) >>>>>>>>>>> >>>>>>>>>>> echo "delete from archive where outTemp < -6;" | sudo sqlite3 >>>>>>>>>>> /home/weewx/archive/weewx.sdb >>>>>>>>>>> >>>>>>>>>>> for solving the problem. In my case, the spike was about 16°C >>>>>>>>>>> when temperature was about 20°C (see the graph below) >>>>>>>>>>> [image: daytempdew.png] >>>>>>>>>>> What I need to do is something like: DELETE if Δt = 300 AND >>>>>>>>>>> |ΔT| > 5, where >>>>>>>>>>> Δt is time difference (in seconds, I chose 300 because my >>>>>>>>>>> archive interval is 5 minutes) and ΔT is the temperature difference >>>>>>>>>>> in that >>>>>>>>>>> time bin. >>>>>>>>>>> In other words I am thinking about using a differential >>>>>>>>>>> approach. It seems to me that this can be the only solution, is >>>>>>>>>>> this >>>>>>>>>>> possible with sqlite3? >>>>>>>>>>> Thank you, >>>>>>>>>>> Andrea >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> You received this message because you are subscribed to the >>>>>>>>>>> Google Groups "weewx-user" group. >>>>>>>>>>> To unsubscribe from this group and stop receiving emails from >>>>>>>>>>> it, send an email to weewx-user+...@googlegroups.com. >>>>>>>>>>> To view this discussion on the web visit >>>>>>>>>>> https://groups.google.com/d/msgid/weewx-user/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%40googlegroups.com >>>>>>>>>>> >>>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>>>>>> . >>>>>>>>>>> >>>>>>>>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "weewx-user" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to weewx-user+...@googlegroups.com. >>>> >>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/weewx-user/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/weewx-user/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/f379634b-50bc-49ec-b2a3-ae243f8c8bcfn%40googlegroups.com.
