As mentioned last post, I think it is time to look at adding temperature data to the database and dashboard. We will, obviously, start with the former. The only temperature data I have is in my daily notes files. In this case going back to the 2015 file. Though I may just process the last 10+ years as I likely won’t be displaying anything in the dashboard older than that. On the other hand, that might be seen as a waste of potentially valuable data.
In 2015, I had a electronic thermometer which kept track of the high and low temperature every day. When that one gave up the ghost, the newer ones didn’t save any temperature data at all. So, for the most part, the only temperature recorded after that was typically the temperature when I got up in the morning. Sometimes later in the day. Last summer I wrote a Python script to take the current air temperature and humidity and estimate the dew point and use that to calculate the humidex. So some days since then there have been considerably more daily temperatures recorded.
All in all, this is going to be another parsing challenge.
Oldest Year(s) First
Okay, let’s see what I can do with that 2015 notes file. I decided having a minimum and maximum for any day would likely be a good thing. Assuming that information is available.
Here’s the first 4 days of the January, 2015.
2015.01.01
- Happy New Year! At 07:30 it's -3.6°C, yesterday's high was 1.3°C, overnight low -3.6°C. Seems to always get a little cooler as the sun comes up this time of year. Both hummingbird feeders are already out. Also put some millet on the ground. Took forever for them to clean it up yesterday.
2015.01.02
- As of around 08:15 light snowfall or icy rain began. Currently 0.6°C, 24 hour low -3.6°C, high 2.4°C.
- Light snow continued for a few hours. Started turning to slush in mid afternoon.
2015.01.03
- A little dark but under street lights doesn't look like we got more snow overnight. Good thing, cause have to take new Camry in for snow tires this morning. As of 07:00 1.3°C; 24 hour high 1.3°C, low: 0.4°C.
2015.01.04
- Started raining last night. Continued into the morning, don't expect it to stop anytime soon. But, no snow so far.
- 08:40 & current temp 1.1°C (Barbara said it was warmer when she got up). 24 hr low 1.1°C, high 2.7°C
- Barbara says wet snow started coming down around 09:20. As of 09:38, is 0.9°C.
- Near as we could tell from the back door was about 20mm in the rain gauge when it started snowing.
- Snow/ice didn't last long, started raining heavily shortly after. Continued all day.
Rather inconsistent. The 24 hour high is likely for the previous day. The low likely for the current day. Though it is conceiveable that might not always be the case.
Got a feeling this is going to require more than one regex. I think I will start by writing another generator, returning only the rows that contain the degree symbol.
New Database Table
Before getting too carried away parsing files and such, I best make sure I have somewhere to store the data. So, a bit of work to be done in the Weather_db class. A new method to create the temperature table if it is not available. Which also means updating the class initialization and mk_tbl methods.
Method to Create Temperature Table
Originally, I saw the table only having a datetime and a temperature column. However on further thought (something I do much more slowly than I once did), I thought perhaps there should also be a minimum and maximum column as well. In the end I decided to simply have the one column. I would let Python or SQL sort out the values I want for the dashboard once the table is populated. So there will likely be dates with multiple entries and dates with only one.
The datetime column will be like that in the rainfall table. The temperature column a float. I recorded all temperatures in centigrade. Don’t know why, I was raised on fahrenheit; and am still a little more comfortable with that. Though I do think centigrade is more sensible: water freezes at 0° and boils at 100° (depending on air pressure of course). 32° and 212° never really made much sense to me. (Sorry, a bit of a wandering mind moment.)
The method will really be very familiar. And perhaps I could refactor the one that builds the rainfall table, but I think that would add a bit of smell to that code. I added a new table name to __init__.
... ...
# put all table names in dictionary
self.tnms = {
"rf_tnm": "rainfall",
"mh_tnm": "rf_mon_history",
"tp_tnm": "temperature",
}
... ...
def mk_temp_tbl(self):
"""Create the temperature table. The name is hard-coded as a class variable.
It has, at least initially, 2 columns: datetime, temperature.
params: None
returns:
True if table created or already existed, False otherwise
"""
c_tnm = self.tnms["tp_tnm"]
_, tbl_ok = self.is_tbl_in_db(c_tnm)
if tbl_ok:
return True
tp_tbl = f"""CREATE TABLE IF NOT EXISTS {c_tnm} (
row_id INTEGER PRIMARY KEY,
datetime TEXT NOT NULL,
temperature REAL NOT NULL
);"""
rslt = self.qry_exec(tp_tbl)
_, tbl_ok = self.is_tbl_in_db(c_tnm)
# print(f"in mk_rain_tbl: {nms}")
return tbl_ok
Okay, let’s update mk_tbl (that was easy) and add a wee test.
... ...
def mk_tbl(self, k_tnm):
... ...
call_who = {
"mh_tnm": self.mk_mhist_tbl,
"rf_tnm": self.mk_rain_tbl,
"tp_tnm": self.mk_temp_tbl,
}
... ...
elif c_tnm == "temperature":
nx_q = f"SELECT datetime, temperature FROM {c_tnm}"
And in the terminal I get the following. Everyhting pretty much as expected.
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
rows in rainfall table: 1774
c_month minMonth maxMonth
0 2014.03 3.0 54.0
1 2014.04 1.5 53.0
2 2014.05 5.0 54.0
3 2014.06 2.0 14.5
4 2014.07 3.0 23.0
.. ... ... ...
133 2025.04 0.0 28.0
134 2025.05 0.5 19.0
135 2025.06 0.0 32.0
136 2025.07 7.0 11.5
137 2025.08 1.5 51.5
[138 rows x 3 columns]
rows in rf_mon_history table: 12
month avg
0 01 244.351818
1 02 139.068182
2 03 167.779167
3 04 109.266667
4 05 79.979231
5 06 53.941667
6 07 24.241667
7 08 41.146154
8 09 103.209091
9 10 194.161818
10 11 275.767273
11 12 254.759091
rows in temperature table: 0
Empty DataFrame
Columns: [datetime, temperature]
Index: []
Generator for Data File Parsing
Okay, let’s code and test the file generator. It should only return rows containg the HTML entity for the degree symbol. I have put it in the rg_data module. It now looks like it was poorly named (rg = rain gauge, which of course is not involved here).
I also created another function to get the path to all the files I plan to parse. As I did for the rainfall data.
Here they are.
... ...
## for temperature data
def get_temp_dsrcs():
# get list of all data sources
# set of temperature data srcs
d_srcs = []
# daily gdn pages with temp/precipitation
dir_1 = Path("F:/BaRKqgs/gdn")
yrs_1 = list(range(2015, 2026))
for yr in yrs_1:
d_srcs.append(dir_1/f"bark_gdn_{yr}.php")
return d_srcs
def get_temp_data(rg_fl):
'''Generator for temperature php files, only want certain lines so decided use generator
Parameters:
rg_fl: full path to file
'''
# d_yr = rg_fl.name[10:14]
# print(d_yr)
with open(rg_fl, "r", encoding='utf8') as ds:
c_dt = ""
for ln in ds:
ln = ln.strip()
if ln[:4] == "<h3>":
c_dt = ln[4:14]
if "°" in ln:
yield f"{c_dt}: {ln}"
And in data2db a wee test of the data source function. Of course, a new if block.
... ...
import utils.rg_data as wd # wd = weather data
... ...
do_dn = False
do_tmpr = True
... ...
if do_tmpr:
chk_i_tmp = True
tst_dsrc = True
tst_gnr = True
proc_fl_1 = False
# instantiate database class using empty database file
cwd = Path(__file__).cwd()
fl_pth = cwd/"data"
fl_nm = "weather.db"
db_pth = fl_pth/fl_nm
rfall = Weather_db(db_pth)
tmpr_srcs = wd.get_temp_dsrcs()
if tst_dsrc:
print(f"temperature file sources:\n{tmpr_srcs}")
In the terminal:
(dbd-3.13) PS R:\learn\dashboard> python data2db.py
temperature file sources:
[WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2015.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2016.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2017.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2018.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2019.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2020.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2021.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2022.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2023.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2024.php'), WindowsPath('F:/BaRKqgs/gdn/bark_gdn_2025.php')]
And now let’s test the generator. I am going to go through each source file and get the first 5 notes/rows returned by the generator.
if tst_gnr:
for t_src in tmpr_srcs:
cnt = 0
print(f"\n{t_src}")
t_gnr = wd.get_temp_data(t_src)
for rw in t_gnr:
print(f"\t{rw}")
cnt += 1
if cnt == 5:
break
And in the terminal I got the following. I have removed most of the output for inclusion in the post.
(dbd-3.13) PS R:\learn\dashboard> python data2db.py
F:\BaRKqgs\gdn\bark_gdn_2015.php
2015.01.01: <li>Happy New Year! At 07:30 it's -3.6°C, yesterday's high was 1.3°C, overnight low -3.6°C. Seems to always get a little cooler as the sun comes up this time of year. Both hummingbird feeders are already out. Also put some millet on the ground. Took forever for them to clean it up yesterday.</li>
2015.01.02: <li>As of around 08:15 light snowfall or icy rain began. Currently 0.6°C, 24 hour low -3.6°C, high 2.4°C.</li>
2015.01.03: <li>A little dark but under street lights doesn't look like we got more snow overnight. Good thing, cause have to take new Camry in for snow tires this morning. As of 07:00 1.3°C; 24 hour high 1.3°C, low: 0.4°C.</li>
2015.01.04: <li>08:40 & current temp 1.1°C (Barbara said it was warmer when she got up). 24 hr low 1.1°C, high 2.7°C</li>
2015.01.04: <li>Barbara says wet snow started coming down around 09:20. As of 09:38, is 0.9°C.</li>
F:\BaRKqgs\gdn\bark_gdn_2016.php
2016.01.01: <li>08:21 & -2.7°C. 24hr low -3.3°C, high 1.6°C.</li>
2016.01.02: <li>09:36 & -2.5°C. 24hr low -3.3°C, high 1.0°C. Still foggy, lots of frost (from fog) on all exposed surfaces (including plants). Thought we saw patches of frost on the backs of crows yesterday.</li>
2016.01.04: <li>10:42 & 0.4°C. 48hr low -2.5°C, high 0.4°C, nothing measureable in rain gauge (asftbd), though light dusting of snow on the ground and the odd new snowflake this morning.</li>
2016.01.06: <li>12:53 & 6.4°C. 48hr low -0.4°C, high 6.4°C, nothing measureable in rain gauge (asftbd).</li>
2016.01.09: <li>09:50 & 1,1°C. 72hr low -0.8°C, high 7.0°C (yesterday I would think), nothing measureable in rain gauge (asftbd).</li>
... ...
F:\BaRKqgs\gdn\bark_gdn_2024.php
2024.01.01: <li>Foggy, 4°C (90%). YVR: fog 3.7°C (100%), ESE 10 km/h.</li>
2024.01.02: <li>Mostly cloudy, 5°C (89%). YVR: mostly cloudy, 5.2°C (99%), SW 8 km/h.</li>
2024.01.03: <li>Mostly cloudy, 6°C (96%). YVR: mostly cloudy, 6.3°C (100%), S 6 km/h.</li>
2024.01.04: <li>Rain, 7°C (95%). YVR: light rain, 7.2°C (100%), SE 27 km/h.</li>
2024.01.05: <li>Mostly cloudy, 5°C (93%). YVR: mostly cloudy, 4.1°C (92%), ESE 8 km/h.</li>
F:\BaRKqgs\gdn\bark_gdn_2025.php
2025.01.01: <li>Cloudy, 3°C (84%). YVR: mostly cloudy, 3.7°C (77%, 0.1°), E 14 km/h.</li>
2025.01.02: <li>Mostly cloudy?, 2°C (83%). YVR: mostly cloudy, 3.6°C (75%, -0.5°C), E 11 km/h. Only got to a high of 3°C.</li>
2025.01.03: <li>Mostly? cloudy, 2°C (92%). YVR: mist, 2.3°C (100%), E 11 km/h.</li>
2025.01.04: <li>Cloudy, 5°C (99%). YVR: cloudy, 5.8°C (100%), E 13 km/h.</li>
2025.01.05: <li>Cloudy, 6°C (99%). YVR: 5.8°C (100%), N 2 km/h.</li>
And, can already see a number of problems. 2106.01.09 has a temperature value of 1,1 (note comma rather than decimal point). And there was no note returned for 2016.01.03 or 2016.01.05? After checking the file, I in fact failed to record the temperature(s) for those two days. Wonder how many others I missed. I corrected the entry for the 9th.
Parsing Notes for Temperature(s)
Okay, I am currently thinking that I will record the “current” temperature at the date and time specified. Then for the 24 hour minimum and maximum, I plan to record the minimum under the current date at 06:00. The maximum I will record under the previous date at 16:00. I may adjust those times on an individual basis if there is a conflict between the current time and 06:00 for the minimum.
Let’s Start Slowly
I am going to try and sort out a set of regexs that work for the first year of notes and the first month. At present I see two top level cases:
- note contains strings low and high
- note does not contain low and high
In the first case I will execute a regex to get high temperature, the low temperature and the time along with the current temperature. In the second case only the time and current temperature.
I’ve coded that like this. I am going to take small steps. To start first 5 days of January 2015. There was a bit of refactoring before I got to the code that follows.
... ...
tst_gnr = False
tst_rgx = True
... ...
# let's start with the first file, 2015, and only look at the rows
# for January
src_ndx = 0
tst_mon = "01"
s_dt = "2015.01.01"
e_dt = "2015.01.05"
# let's try to sort out the regexs I will need to extract all the
# pertinent temperature data, expect there will be more than one
# and order of execution may matter
if tst_rgx:
# I will first check if 'low' and 'high' are in the returned note/row
# if so, I will attempt to extract the low and high temp values
# then look at getting the value for the specified time in that note
t_gnr = wd.get_temp_data(tmpr_srcs[src_ndx])
for rw in t_gnr:
d_dt, d_nt = rw.split(": ", 1)
t_mn = d_dt[5:7]
# for test only do a 5 days
if d_dt > e_dt:
break
if d_dt >= s_dt:
print("\n", d_dt, d_nt)
if "low" in d_nt and "high" in d_nt:
h_rgx = r"^.*?high.*?([-\.0-9]+)\&"
rx = re.compile(h_rgx, re.IGNORECASE )
c_hgh = rx.search(d_nt)
l_rgx = r"^.*?low.*?([-\.0-9]+)\&"
rx = re.compile(l_rgx, re.IGNORECASE )
c_low = rx.search(d_nt)
t_rgx = r"^.*?(\d{2}:\d{2}).*?([-\.0-9]+)\&"
rx = re.compile(t_rgx, re.IGNORECASE )
t_cur = rx.search(d_nt)
print(f"\t{c_hgh.group(1)}, {c_low.group(1)}, {t_cur.group(1)} + {t_cur.group(2)}")
else:
t_rgx = r"^.*?(\d{2}:\d{2})[^.].*?([-\.0-9]+)\&"
rx = re.compile(t_rgx, re.IGNORECASE )
t_cur = rx.search(d_nt)
print(f"\t{t_cur.group(1)} + {t_cur.group(2)}")
And the terminal output was as follows.
(dbd-3.13) PS R:\learn\dashboard> python data2db.py
2015.01.01 <li>Happy New Year! At 07:30 it's -3.6°C, yesterday's high was 1.3°C, overnight low -3.6°C. Seems to always get a little cooler as the sun comes up this time of year. Both hummingbird feeders are already out. Also put some millet on the ground. Took forever for them to clean it up yesterday.</li>
1.3, -3.6, 07:30 + -3.6
2015.01.02 <li>As of around 08:15 light snowfall or icy rain began. Currently 0.6°C, 24 hour low -3.6°C, high 2.4°C.</li>
2.4, -3.6, 08:15 + 0.6
2015.01.03 <li>A little dark but under street lights doesn't look like we got more snow overnight. Good thing, cause have to take new Camry in for snow tires this morning. As of 07:00 1.3°C; 24 hour high 1.3°C, low: 0.4°C.</li>
1.3, 0.4, 07:00 + 1.3
2015.01.04 <li>08:40 & current temp 1.1°C (Barbara said it was warmer when she got up). 24 hr low 1.1°C, high 2.7°C</li>
2.7, 1.1, 08:40 + 1.1
2015.01.04 <li>Barbara says wet snow started coming down around 09:20. As of 09:38, is 0.9°C.</li>
09:38 + 0.9
2015.01.05 <li>Still raining heavily. Rain gauge looks to be at 100mm. Taking a chance it won't overflow before tomorrow morning. 19:45 & currently 5.8°C, 36 hour low 0.7°C, high 5.8°C. Forecast is for 9°C by tomorrow morning. Rain is also supposed to stop overnight.</li>
5.8, 5.8, 19:45 + 5.8
I am now going to slowly work my way through the month in 5 or 10 day increments. Fixing my code as necessary. Looking at 2015.01.06-10 the code looks to be working as expected. But I did fail to record the temperature(s) on the 10th. And once again on the 15th and 18th. I also had a case where the time was entered as “)8:13”. Which generated an AttributeError for the date in question.
And, there were also a number of cases where the low or high matched the current temperature. Not too sure how I go about resolving that.
I also noticed that for some dates I indicated if it was cloudy, clear, foggy, raining or snowing. I am thinking it might be fun to record that information as well if available. Though not sure how I would use that in the dashboard. A while after writing the preceding sentence(s), I realized that at some point (2023?) I also started recording the humidity along with the temperature. So, I think I will add code to capture that as well if present.
Need to sort whether I am going to modify that temperature table or add one or more other tables? A decision for another day
Interim Parsing
I think I am going to parse the whole of 2015, writing whatever I extract to a CSV file. If, there is a possible issue or error, I will write that as a comment.
I started out by testing for one month to the terminal, then to the CSV file. I finally ran the file completely. The code below represents what I had for that final run on the full file.
... ...
def get_wthr_cond(nt):
"""Check for any weather condition(s) in the passed in daily note"""
d_cnd = ""
if "mainly clear" in nt or "mainly sunny" in nt:
d_cnd = "mainly clear"
if "mostly clear" in nt or "mostly sunny" in nt:
d_cnd = "mostly clear"
elif "clear " in nt or "sun " in nt or "sunny" in nt:
d_cnd = "clear"
elif "mostly cloudy" in nt:
d_cnd = "mostly cloudy"
elif "partly cloudy" in nt:
d_cnd = "mostly cloudy"
elif "cloudy" in nt or "clouds" in nt:
d_cnd = "cloudy"
elif "rain " in nt or "raining" in nt:
d_cnd = "rain"
elif "snow " in nt or "snowing" in nt:
d_cnd = "snow"
return d_cnd
# let's start with the first file, 2015
src_ndx = 0
tst_mon = "01"
tst_yr = "2015"
mx_dt = "2015.02.01"
s_dt = "2015.01.01"
e_dt = "2015.12.31"
# CSV file
csv_nm = f"tmpr_{tst_yr}.csv"
csv_pth = fl_pth/csv_nm
fh_csv = open(csv_pth, "a", encoding='utf8')
# let's try to sort out the regexs I will need to extract all the
# pertinent temperature data, expect there will be more than one
# and order of execution may matter
if tst_rgx:
# I will first check if 'low' and 'high' are in the returned note/row
# if so, I will attempt to extract the low and high temp values
# then look at getting the value for the specified time in that note
t_gnr = wd.get_temp_data(tmpr_srcs[src_ndx])
for rw in t_gnr:
# print(f"\n{rw}")
d_dt, d_nt = rw.split(": ", 1)
t_mn = d_dt[5:7]
# for test only do a 5 days
if d_dt > e_dt:
break
# if d_dt > mx_dt:
# break
if d_dt >= s_dt:
# print("\n", d_dt, d_nt)
w_cnd = get_wthr_cond(d_nt)
c_hgh, c_low = "", ""
try:
if "low" in d_nt and "high" in d_nt:
h_rgx = r"^.*?high.*?([-\.0-9]+)\&"
rx = re.compile(h_rgx, re.IGNORECASE )
c_hgh = rx.search(d_nt).group(1)
l_rgx = r"^.*?low.*?([-\.0-9]+)\&"
rx = re.compile(l_rgx, re.IGNORECASE )
c_low = rx.search(d_nt).group(1)
t_rgx = r"^.*?(\d{2}:\d{2}).*?([-\.0-9]+)\&"
rx = re.compile(t_rgx, re.IGNORECASE )
t_cur = rx.search(d_nt)
# print(f"{d_dt},{c_hgh.group(1)},{c_low.group(1)},{t_cur.group(1)} + {t_cur.group(2)},{w_cnd}")
u_rgx = r"\(([\d\.]+\%)"
rx = re.compile(u_rgx, re.IGNORECASE )
u_cur = rx.search(d_nt)
c_hum = u_cur.group(1) if u_cur else ""
fh_csv.write(f"{d_dt},{c_hgh},{c_low},{t_cur.group(1)} + {t_cur.group(2)},{w_cnd},{c_hum}\n")
ct_hgh = (c_hgh == t_cur.group(2))
ct_low = (c_low == t_cur.group(2))
if ct_hgh or ct_low:
# print(f"# {'curr == high' if ct_hgh else 'curr == low'}")
fh_csv.write(f"# {'curr == high' if ct_hgh else 'curr == low'}\n")
except Exception as e:
# print(f"# Error: {e}\n# {d_dt}\n# {d_nt}")
fh_csv.write(f"# Error: {e}\n# {d_dt}\n# {d_nt}\n")
fh_csv.close()
Here’s some samples from the file that was generated.
... ...
2015.01.30,9.5,1.3,08:05 + 1.3,,
# curr == low
2015.01.31,10.1,1.0,09:49 + 3.2,,
# Error: 'NoneType' object has no attribute 'group'
# 2015.02.01
# <li>)8:13 & 5.7°C; looks to be about 3.5-4mm precip in the rain gauge. 24hr low 3.2°C, high 6.7°C.</li>
2015.02.02,6.9,5.7,08:21 + 6.5,,
2015.02.03,7.6,6.4,05:30 + 6.8,rain,
2015.02.04,5.2,5.2,08:37 + 5.2,,
# curr == high
2015.02.05,7.4,5.2,09:00 + 7.4,rain,
# curr == high
... ...
2015.07.18,28.3,16.4,13:13 + 28.3,,
# curr == high
# Error: 'NoneType' object has no attribute 'group'
# 2015.07.19
# <li>Not a cloud to be seen, and Barbara said already 19°C at 06:45.</li>
2015.07.19,31.4,18.7,08:41 + 22.9,,
# Error: 'NoneType' object has no attribute 'group'
# 2015.07.19
# <li>Watered the back by hand. Will check front later. 11am & 27°C already.</li>
2015.07.20,31.8,18.1,13:02 + 21.7,,
2015.07.22,25.4,13.6,08:47 + 15.5,,
2015.07.25,24.8,14.0,10:33 + 17.0,mostly cloudy,
I have decided I will process all the remaining files in this same fashion. I will then one year at a time, go through the CSV files looking for issues. Then do my best to fix them. I will likely add a “??:” to the front of the problem note, then add a new one that “eliminates” the issue.
I don’t really see any easy way to do so in code. Well, not without some shortcuts that might not capture the available data.
Done
And with that I am going to call this post finished. It is going to take me some time to go through the tidying process described above. Don’t know how long, but expect at least a week of full working days. So, in my world, perhaps two weeks or more.
Until next time, I hope your projects don’t lead you to this kind of expensive use of your time.