>From a strict data science point of view, you’re better off deriving those from primary observations. Writing an xtype extension would be more effective.
On Tue, Aug 17, 2021 at 10:27 AM [email protected] <[email protected]> wrote: > Thanks Tom, that did the trick. > > Some general information on the two text fields: > > Lightning Frequency is based on number of Lightning Strikes over 5 minute > period > '----' --- 0 > Occasional --- 1 to 5 > Frequent --- 6 to 30 > Continuous --- over 31 > > Comfort level is based off of the dewpoint. > Pleasant dp <= 55.9 > Comfortable dp between 56.0 --- 60.9 > Getting Sticky dp between 61.0 --- 65.9 > Uncomfortable dp between 66.0 --- 70.9 > Oppressive dp between 71.0 --- 75.9 > Miserable dp > 76.0 > > Rich > On Tuesday, August 17, 2021 at 11:54:34 AM UTC-4 [email protected] wrote: > >> Oops. I forgot yet another step! TEXT types cannot use the daily >> summaries, so they should be left out of the schema for the summaries: >> >> day_summaries = [(e[0], 'scalar') for e in table >> if e[0] not in ('dateTime', 'usUnits', 'interval', >> 'lightning_frequency', 'comfort_level')] + [('wind', >> 'VECTOR')] >> >> If you don't want to start all over, you can just drop the two tables: >> >> *sqlite3 weewx.sdb* >> sqlite> *DROP TABLE archive_day_lightning_frequency;* >> sqlite> *DROP TABLE archive_day_comfort_level;* >> sqlite> *.quit* >> >> This is getting complicated enough that we probably should have some >> directions in either the *Customizing Guide* or in the wiki for how to >> do this. I've created issue #688 >> <https://github.com/weewx/weewx/issues/688> to track. >> >> On Tue, Aug 17, 2021 at 8:19 AM [email protected] <[email protected]> >> wrote: >> >>> The accumulator stanza was configured correctly in weewx.conf. >>> Everything works as expected but still have the following error: >>> >>> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace >>> failed for database weewx.sdb: table archive_day_comfort_level has 9 >>> columns but 5 values were supplied >>> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace >>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9 >>> columns but 5 values were supplied >>> Aug 17 11:15:24 raspberrypi weewx[8482] INFO weewx.manager: Added record >>> 2021-08-17 11:15:00 EDT (1629213300) to database 'weewx.sdb' >>> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace >>> failed for database weewx.sdb: table archive_day_comfort_level has 9 >>> columns but 5 values were supplied >>> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace >>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9 >>> columns but 5 values were supplied >>> >>> I'm summing the reason for this error is the those fields are text >>> values not the expected integer or real values. >>> >>> Rich >>> >>> >>> On Tuesday, August 17, 2021 at 8:30:50 AM UTC-4 [email protected] wrote: >>> >>>> The problem is that I forgot a step. In weewx.conf, you need to set an >>>> appropriate extractor for the string. For example, >>>> >>>> [Accumulator] >>>> [[comfort_level]] >>>> accumulator = firstlast >>>> extractor = last >>>> [[lightning_frequency]] >>>> accumulator = firstlast >>>> extractor = last >>>> >>>> >>>> This means that what will be stored in the database will be the last >>>> value seen during an archive period. An alternative is "first". >>>> >>>> One other detail: if you want to use it in tags, you also need to >>>> specify the ".raw" suffix. For example: >>>> >>>> <p>Current = $current.comfort_level.raw</p> >>>> <p>First string of the day = $day.comfort_level.first.raw</p> >>>> <p>Last string = $day.comfort_level.last.raw</p> >>>> >>>> >>>> -tk >>>> >>>> >>>> >>>> On Tue, Aug 17, 2021 at 4:52 AM Tom Keffer <[email protected]> wrote: >>>> >>>>> I was able to reproduce the problem. >>>>> >>>>> Let me work on a solution. >>>>> >>>>> -tk >>>>> >>>>> On Mon, Aug 16, 2021 at 9:50 PM [email protected] <[email protected]> >>>>> wrote: >>>>> >>>>>> Tom >>>>>> >>>>>> Added two columns via the schema: comfort_level and >>>>>> lightning_frequency; in which the data types are TEXT. Again the database >>>>>> gets populated with the correct data. >>>>>> >>>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace >>>>>> failed for database weewx.sdb: table archive_day_comfort_level has 9 >>>>>> columns but 5 values were supplied >>>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace >>>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has >>>>>> 9 >>>>>> columns but 5 values were supplied >>>>>> Aug 17 00:35:26 raspberrypi weewx[1359] INFO weewx.manager: Added >>>>>> record 2021-08-17 00:35:00 EDT (1629174900) to database 'weewx.sdb' >>>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace >>>>>> failed for database weewx.sdb: table archive_day_comfort_level has 9 >>>>>> columns but 5 values were supplied >>>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace >>>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has >>>>>> 9 >>>>>> columns but 5 values were supplied >>>>>> >>>>>> This is the schema: >>>>>> >>>>>> table = [('dateTime', 'INTEGER NOT NULL UNIQUE PRIMARY >>>>>> KEY'), >>>>>> ('usUnits', 'INTEGER NOT NULL'), >>>>>> ('interval', 'INTEGER NOT NULL'), >>>>>> ('altimeter', 'REAL'), >>>>>> ('appTemp', 'REAL'), >>>>>> ('appTemp1', 'REAL'), >>>>>> ('barometer', 'REAL'), >>>>>> ('batteryStatus1', 'REAL'), >>>>>> ('batteryStatus2', 'REAL'), >>>>>> ('batteryStatus3', 'REAL'), >>>>>> ('batteryStatus4', 'REAL'), >>>>>> ('batteryStatus5', 'REAL'), >>>>>> ('batteryStatus6', 'REAL'), >>>>>> ('batteryStatus7', 'REAL'), >>>>>> ('batteryStatus8', 'REAL'), >>>>>> ('cloudbase', 'REAL'), >>>>>> ('co', 'REAL'), >>>>>> ('co2', 'REAL'), >>>>>> ('consBatteryVoltage', 'REAL'), >>>>>> ('dewpoint', 'REAL'), >>>>>> ('dewpoint1', 'REAL'), >>>>>> ('ET', 'REAL'), >>>>>> ('extraHumid1', 'REAL'), >>>>>> ('extraHumid2', 'REAL'), >>>>>> ('extraHumid3', 'REAL'), >>>>>> ('extraHumid4', 'REAL'), >>>>>> ('extraHumid5', 'REAL'), >>>>>> ('extraHumid6', 'REAL'), >>>>>> ('extraHumid7', 'REAL'), >>>>>> ('extraHumid8', 'REAL'), >>>>>> ('extraTemp1', 'REAL'), >>>>>> ('extraTemp2', 'REAL'), >>>>>> ('extraTemp3', 'REAL'), >>>>>> ('extraTemp4', 'REAL'), >>>>>> ('extraTemp5', 'REAL'), >>>>>> ('extraTemp6', 'REAL'), >>>>>> ('extraTemp7', 'REAL'), >>>>>> ('extraTemp8', 'REAL'), >>>>>> ('forecast', 'REAL'), >>>>>> ('hail', 'REAL'), >>>>>> ('hailBatteryStatus', 'REAL'), >>>>>> ('hailRate', 'REAL'), >>>>>> ('heatindex', 'REAL'), >>>>>> ('heatindex1', 'REAL'), >>>>>> ('heatingTemp', 'REAL'), >>>>>> ('heatingVoltage', 'REAL'), >>>>>> ('humidex', 'REAL'), >>>>>> ('humidex1', 'REAL'), >>>>>> ('inDewpoint', 'REAL'), >>>>>> ('inHumidity', 'REAL'), >>>>>> ('inTemp', 'REAL'), >>>>>> ('inTempBatteryStatus', 'REAL'), >>>>>> ('leafTemp1', 'REAL'), >>>>>> ('leafTemp2', 'REAL'), >>>>>> ('leafWet1', 'REAL'), >>>>>> ('leafWet2', 'REAL'), >>>>>> ('lightning_distance', 'REAL'), >>>>>> ('lightning_disturber_count', 'REAL'), >>>>>> ('lightning_energy', 'REAL'), >>>>>> ('lightning_noise_count', 'REAL'), >>>>>> ('lightning_strike_count', 'REAL'), >>>>>> ('luminosity', 'REAL'), >>>>>> ('maxSolarRad', 'REAL'), >>>>>> ('nh3', 'REAL'), >>>>>> ('no2', 'REAL'), >>>>>> ('noise', 'REAL'), >>>>>> ('o3', 'REAL'), >>>>>> ('outHumidity', 'REAL'), >>>>>> ('outTemp', 'REAL'), >>>>>> ('outTempBatteryStatus', 'REAL'), >>>>>> ('pb', 'REAL'), >>>>>> ('pm10_0', 'REAL'), >>>>>> ('pm1_0', 'REAL'), >>>>>> ('pm2_5', 'REAL'), >>>>>> ('pressure', 'REAL'), >>>>>> ('radiation', 'REAL'), >>>>>> ('rain', 'REAL'), >>>>>> ('rainBatteryStatus', 'REAL'), >>>>>> ('rainRate', 'REAL'), >>>>>> ('referenceVoltage', 'REAL'), >>>>>> ('rxCheckPercent', 'REAL'), >>>>>> ('signal1', 'REAL'), >>>>>> ('signal2', 'REAL'), >>>>>> ('signal3', 'REAL'), >>>>>> ('signal4', 'REAL'), >>>>>> ('signal5', 'REAL'), >>>>>> ('signal6', 'REAL'), >>>>>> ('signal7', 'REAL'), >>>>>> ('signal8', 'REAL'), >>>>>> ('snow', 'REAL'), >>>>>> ('snowBatteryStatus', 'REAL'), >>>>>> ('snowDepth', 'REAL'), >>>>>> ('snowMoisture', 'REAL'), >>>>>> ('snowRate', 'REAL'), >>>>>> ('so2', 'REAL'), >>>>>> ('soilMoist1', 'REAL'), >>>>>> ('soilMoist2', 'REAL'), >>>>>> ('soilMoist3', 'REAL'), >>>>>> ('soilMoist4', 'REAL'), >>>>>> ('soilTemp1', 'REAL'), >>>>>> ('soilTemp2', 'REAL'), >>>>>> ('soilTemp3', 'REAL'), >>>>>> ('soilTemp4', 'REAL'), >>>>>> ('supplyVoltage', 'REAL'), >>>>>> ('txBatteryStatus', 'REAL'), >>>>>> ('UV', 'REAL'), >>>>>> ('uvBatteryStatus', 'REAL'), >>>>>> ('windBatteryStatus', 'REAL'), >>>>>> ('windchill', 'REAL'), >>>>>> ('windDir', 'REAL'), >>>>>> ('windGust', 'REAL'), >>>>>> ('windGustDir', 'REAL'), >>>>>> ('windrun', 'REAL'), >>>>>> ('windSpeed', 'REAL'), >>>>>> ('lightning_frequency', 'TEXT'), >>>>>> ('comfort_level', 'TEXT'), >>>>>> ] >>>>>> >>>>>> day_summaries = [(e[0], 'scalar') for e in table >>>>>> if e[0] not in ('dateTime', 'usUnits', 'interval')] >>>>>> + [('wind', 'VECTOR')] >>>>>> >>>>>> schema = { >>>>>> 'table': table, >>>>>> 'day_summaries' : day_summaries >>>>>> } >>>>>> >>>>>> Rich >>>>>> On Monday, August 16, 2021 at 10:32:02 PM UTC-4 [email protected] >>>>>> wrote: >>>>>> >>>>>>> Tom >>>>>>> >>>>>>> I tried adding the column using the following command: >>>>>>> >>>>>>> wee_database --add-column=lightning_frequency --type=TEXT >>>>>>> >>>>>>> It does add the column and populates the data but generates an error >>>>>>> during the archival cycle. I guess it looking for integers and real type >>>>>>> values. >>>>>>> >>>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] ERROR weewx.manager: >>>>>>> Replace failed for database weewx.sdb: table >>>>>>> archive_day_lightning_frequency has 9 columns but 5 values were supplied >>>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] INFO weewx.manager: Added >>>>>>> record 2021-08-16 22:00:00 EDT (1629165600) to database 'weewx.sdb' >>>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] ERROR weewx.manager: >>>>>>> Replace failed for database weewx.sdb: table >>>>>>> archive_day_lightning_frequency has 9 columns but 5 values were supplied >>>>>>> >>>>>>> Next I will try adding using the schema. >>>>>>> >>>>>>> Rich >>>>>>> On Monday, August 16, 2021 at 6:55:55 PM UTC-4 [email protected] >>>>>>> wrote: >>>>>>> >>>>>>>> Can't say that I've tried that, but I don't see why it wouldn't >>>>>>>> work. It's certainly simpler. >>>>>>>> >>>>>>>> Let me know how it goes. >>>>>>>> >>>>>>>> On Mon, Aug 16, 2021 at 9:11 AM [email protected] < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Thanks Tom. >>>>>>>>> >>>>>>>>> I have another question before proceeding. Do I need to >>>>>>>>> physically update the schema to include column and type or could I >>>>>>>>> just use >>>>>>>>> the following command: >>>>>>>>> >>>>>>>>> wee_database --add-column=comments --type=TEXT >>>>>>>>> >>>>>>>>> Rich >>>>>>>>> >>>>>>>>> On Monday, August 16, 2021 at 8:29:16 AM UTC-4 [email protected] >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Good question. Yes, it is possible to store text. >>>>>>>>>> >>>>>>>>>> 1. Assuming you're using sqlite, make sure you read their >>>>>>>>>> documentation on data types: https://sqlite.org/datatype3.html >>>>>>>>>> >>>>>>>>>> 2. Create a new schema accordingly. For example, >>>>>>>>>> >>>>>>>>>> table = [('dateTime', 'INTEGER NOT NULL UNIQUE PRIMARY KEY'), >>>>>>>>>> ('usUnits', 'INTEGER NOT NULL'), >>>>>>>>>> ('interval', 'INTEGER NOT NULL'), >>>>>>>>>> ('outTemp', 'REAL'), >>>>>>>>>> ('comments', 'TEXT') >>>>>>>>>> ] >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> day_summaries = [('outTemp', 'scalar'), ('comments', 'scalar')] >>>>>>>>>> >>>>>>>>>> schema = { >>>>>>>>>> 'table': table, >>>>>>>>>> 'day_summaries' : day_summaries >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> This is just an example. Your schema is likely to have many more >>>>>>>>>> types than this. >>>>>>>>>> >>>>>>>>>> 3. See the directions in the Customizing Guide for how to specify >>>>>>>>>> that your new schema be used. *Picking a starting schema >>>>>>>>>> <http://www.weewx.com/docs/customizing.htm#Picking_a_starting_schema>* >>>>>>>>>> . >>>>>>>>>> >>>>>>>>>> 4. Read the developer's notes on Accumulators >>>>>>>>>> <https://github.com/weewx/weewx/wiki/Accumulators>. In your >>>>>>>>>> weewx.conf file, specify that your text type should be accumulated >>>>>>>>>> using >>>>>>>>>> the "firstlast" accumulator. This will prevent it from trying to >>>>>>>>>> find the average, etc, of a string. >>>>>>>>>> >>>>>>>>>> [Accumulator] >>>>>>>>>> [[comments]] >>>>>>>>>> accumulator = firstlast >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> That's it. If your new type appears in the archive record, then >>>>>>>>>> it will be included in the database. >>>>>>>>>> >>>>>>>>>> Tags like >>>>>>>>>> >>>>>>>>>> <p>Details: $current.comments, last recorded at >>>>>>>>>> $day.comments.last</p> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> should also work. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Sun, Aug 15, 2021 at 8:43 PM [email protected] < >>>>>>>>>> [email protected]> wrote: >>>>>>>>>> >>>>>>>>>>> In the Weewx DB, the column data types are primary Real or >>>>>>>>>>> Integer but is it possible to store data as Text? >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> 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 [email protected]. >>>>>>>>>>> To view this discussion on the web visit >>>>>>>>>>> https://groups.google.com/d/msgid/weewx-user/faf66dd7-7e4a-45fa-9737-6f0094024c88n%40googlegroups.com >>>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/faf66dd7-7e4a-45fa-9737-6f0094024c88n%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 [email protected]. >>>>>>>>> >>>>>>>> To view this discussion on the web visit >>>>>>>>> https://groups.google.com/d/msgid/weewx-user/c4c792b5-c214-446e-9e4b-6d76dbc14aabn%40googlegroups.com >>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/c4c792b5-c214-446e-9e4b-6d76dbc14aabn%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 [email protected]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/weewx-user/be7ea2ed-51c5-4f1b-bf21-d37f8d2c0898n%40googlegroups.com >>>>>> <https://groups.google.com/d/msgid/weewx-user/be7ea2ed-51c5-4f1b-bf21-d37f8d2c0898n%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 [email protected]. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/weewx-user/9e0b1921-f0bc-4c1e-8dd4-09a32bd63610n%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/9e0b1921-f0bc-4c1e-8dd4-09a32bd63610n%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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-user/3d113ef1-7ffb-4314-ba5a-be576b322538n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/3d113ef1-7ffb-4314-ba5a-be576b322538n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- -tk -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBN77v4vBMSAOCfAnCmkTM7aErNcypcXVeJ3Y45WZ1ZMw%40mail.gmail.com.
