I think I am now going to look at obtaining temperature data from the database. And how to display it. E.G. table and/or chart.
New Chart Function
But, first, I am going to convert the pie chart code from the other day into a suitable function in the db_charts module. Followed by a quick test.
def wc_hist_pie(wc_df, c_wc, sv_pth=""):
"""Generate plotly pie chart showing historical weather conditions for a specified day of year.
params:
wc_df: dataframe of data to be plotted;
two columns: condition (str), cnt (int)
c_wc: weather condition for same day this year
sv_pth: path to which to save chart html, do not save if falsy
returns: plotly figure
"""
wcs = wc_cnt['condition'].to_list()
a_pull = [0] * len(wcs)
t_y, t_m = 0.99, 30
if c_wc in wcs:
wc_idx = wcs.index(c_wc)
a_pull[wc_idx] = 0.2
t_y, t_m = 0.96, 10
cnts = wc_cnt['cnt'].to_list()
fig = go.Figure(data=[go.Pie(labels=wcs, values=cnts, pull=a_pull,
textinfo='label+value', textfont_size=18)])
fig.update_traces(showlegend=False, selector=dict(type='pie'))
fig.update_layout(autosize=False, width=600, height=600,
margin=dict(l=0, r=0, b=0, t=t_m, pad=0),
title_text=f"Historical Morning Weather for {md_dy}", title_x=0.5, title_y=t_y,
title_font_size=22)
if sv_pth:
fig.write_html(sv_pth)
return fig
... ...
if do_wc_chart:
md_dy = "09.24"
wc_2dy = "heavy rain"
wc_cnt = rfall.get_wc_count(md_dy)
fig = wc_hist_pie(wc_cnt, wc_2dy)
fig.show()
And I can assure you the chart generated by the function matches the final one in the last post.
Temperature Data
I am not sure how the above chart will fit into the dashboard. But, I do know I want to display temperature data in the dashboard in a similar fashion to the rainfall data we worked on earlier. So let’s look at getting the data I think is needed.
I will start by obtaining a table of the current month’s morning temperatures. Which will eventually be displayed in the dashboard.
Table of Current Month’s Daily Temperatures
Right now I have a bit of a problem. There are days with multiple temperature entries. I just want the first one if more than one. (Well, I really want the lowest early morning temperature but…) I think I have something working. Took a fair bit of messing around and rethinking how SQL works. But here’s the finished test code and the terminal output.
The nested block starting SELECT tm is used to get the earliest time for each day with temperature data in the month. The outer query uses that information to get the data to be used for the table. I.E. the temperature for the earliest available time for each day, with a temperature recorded, in the table.
... ...
if do_dly_tmp:
yr_mn = "2025.10"
c_yr = time.strftime("%Y", time.localtime())
# get first temperature for each available day in month
t_qry = f"""SELECT datetime, temperature
FROM {t_nm}
WHERE SUBSTR(datetime, 1, 7)='{yr_mn}' AND SUBSTR(datetime, 12, 5) IN
(SELECT tm
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 1, 7)='{yr_mn}'
GROUP BY dt
)
);
"""
mn_tmps = rfall.qry_pd(t_qry, i_col="datetime")
print(f"\n{mn_tmps}")
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
temperature
datetime
2025.10.01 05:20 13.0
2025.10.02 06:28 13.0
2025.10.03 06:26 10.0
2025.10.04 07:03 13.0
2025.10.05 07:21 9.0
2025.10.06 08:06 8.0
2025.10.07 06:59 9.0
2025.10.08 06:32 11.0
2025.10.09 06:52 11.0
2025.10.10 07:06 10.0
2025.10.11 06:52 11.0
2025.10.12 06:56 9.0
2025.10.13 06:26 6.0
2025.10.14 07:52 4.0
2025.10.15 07:26 5.0
2025.10.16 07:02 5.0
2025.10.17 06:56 10.0
2025.10.18 07:52 8.0
2025.10.19 06:49 9.0
2025.10.20 06:28 8.0
2025.10.21 06:56 10.0
2025.10.22 07:03 6.0
2025.10.23 06:27 10.0
2025.10.24 07:09 12.0
2025.10.25 07:52 8.0
And it looks like I am getting the earliest temperature of the day for each available day. Checked a few other years and months and things seemed to work as expected/intended.
Historical Average, Minimum and Maximum Temperature for the Month
Okay, I am going to use the average of the temperatures in the above table to represent the average temperature for the currently specified month. And try to see if I can come up with some kind of gauge to use in the dashboard. I plan to get the historical minimum, maximum and average temperatures for the given month.
Going to get messy. As I will need to once again select the earliest temperature for each available day in the database. But, I think a refactoring of the query above should get me what I’m after. As usual a new if block.
I start by selecting available rows for the current month, excluding those for the specified year. Then choosing only those with the earliest time. Finally I get the average, minimum and maximum. That seemingly unnecessary nested query, SELECT datetime, is needed because the IN operator requires a subquery with only one column.
... ...
if do_mn_anx:
# get historical average, minimum and maximum temperatures for specified month
t_qry = f"""
SELECT AVG(temperature) AS av, MIN(temperature) AS mn, MAX(temperature) AS mx
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{yr_mn[-2:]}' AND datetime IN
(SELECT datetime
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{yr_mn[-2:]}' AND temperature IS NOT Null
AND SUBSTR(datetime, 1, 4)!='{c_yr}'
GROUP BY dt
)
);
"""
t_hist = rfall.qry_pd(t_qry)
print(f"\n{t_hist}")
And, in the terminal I got the following for October and December.
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
av mn mxn
0 9.39699 0.0 15.9
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
av mn mxn
0 3.140071 -14.0 14.0
Looks about right.
Historical Monthly Average, Minimum and Maximum by Year
This revised query actually turned out to be a fairly simple refactoring of the above code.
if do_yr_anx:
# get monthly average, minimum and maximum by year, excluding current year
t_qry = f"""
SELECT SUBSTR(datetime, 1, 4) as yr, AVG(temperature) AS av, MIN(temperature) AS mn, MAX(temperature) AS mx
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{yr_mn[-2:]}' AND datetime IN
(SELECT datetime
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{yr_mn[-2:]}' AND temperature IS NOT Null
AND SUBSTR(datetime, 1, 4)!='{c_yr}'
GROUP BY dt
)
)
GROUP BY yr;
"""
t_hist = rfall.qry_pd(t_qry, i_col="yr")
print(f"\n{t_hist}")
And in the terminal I got the following for October and December.
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
av mn mx
yr
2015 11.890476 7.5 15.9
2016 10.132258 7.2 14.8
2017 9.526667 5.2 14.4
2018 9.206452 4.9 14.9
2019 7.538710 1.0 11.6
2020 9.490323 1.0 15.7
2021 8.531250 1.0 14.0
2022 10.333333 7.0 13.0
2023 8.838710 0.0 14.0
2024 9.348387 4.0 13.9
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
av mn mx
yr
2015 4.812500 -1.2 11.2
2016 0.287097 -7.3 9.0
2017 2.241935 -2.1 14.0
2018 3.754839 -2.8 8.4
2019 4.790323 -0.8 7.5
2020 4.593548 -1.7 8.5
2021 -6.036364 -14.0 4.0
2022 0.033333 -10.0 9.0
2023 6.193548 2.0 10.0
2024 5.087097 1.0 9.0
You know that went a lot faster than I expected.
Add New Methods
I am going to convert the above code into methods of the Weather_db class. Test the methods. And call this post done.
... ...
def get_mon_temps(self, yr_mn):
"""Get the daily temperatures for the specified month and year.
Specifically the earliest temperature for each available day.
Param:
yr_mn: the year and month to get temperatures for, yyyy.mm (str)
Returns:
pandas dataframe of temperature data
"""
t_nm = self.tnms["tp_tnm"]
t_qry = f"""
SELECT datetime, temperature
FROM {t_nm}
WHERE SUBSTR(datetime, 1, 7)='{yr_mn}' AND SUBSTR(datetime, 12, 5) IN
(SELECT tm
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 1, 7)='{yr_mn}'
GROUP BY dt
)
);
"""
mn_tmps = self.qry_pd(t_qry, i_col="datetime")
return mn_tmps
... ...
def get_mn_anx(self, yr_mn):
"""Get historical monthly average, minimum and maximum temperature for specified month.
Use the earliest temperature for each day available in the database. Do not include
data for the 'current' year.
"""
t_nm = self.tnms["tp_tnm"]
c_mn = yr_mn[-2:]
c_yr = yr_mn[:4]
q_manx = f"""
SELECT AVG(temperature) AS av, MIN(temperature) AS mn, MAX(temperature) AS mx
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{c_mn}' AND datetime IN
(SELECT datetime
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{c_mn}' AND temperature IS NOT Null
AND SUBSTR(datetime, 1, 4)!='{c_yr}'
GROUP BY dt
)
);
"""
m_anx = self.qry_pd(q_manx)
return m_anx
... ...
def get_yr_t_manx(self, yr_mn):
t_nm = self.tnms["tp_tnm"]
c_mn = yr_mn[-2:]
c_yr = yr_mn[:4]
q_yanx = f"""
SELECT SUBSTR(datetime, 1, 4) as yr, AVG(temperature) AS av, MIN(temperature) AS mn, MAX(temperature) AS mx
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{c_mn}' AND datetime IN
(SELECT datetime
FROM
(SELECT datetime, SUBSTR(datetime, 1, 10) AS dt, min(SUBSTR(datetime, 12 , 5)) AS tm
FROM {t_nm}
WHERE SUBSTR(datetime, 6, 2)='{c_mn}' AND temperature IS NOT Null
AND SUBSTR(datetime, 1, 4)!='{c_yr}'
GROUP BY dt
)
)
GROUP BY yr;
"""
y_maxn = self.qry_pd(q_yanx, i_col="yr")
return y_maxn
... ...
if do_t_mthd:
# test the three new methods
yr_mn = "2025.10"
mn_tmps = rfall.get_mon_temps(yr_mn)
print(f"\n{mn_tmps}")
mn_anx = rfall.get_mn_anx(yr_mn)
print(f"\n{mn_anx}")
y_manx = rfall.get_yr_t_manx(yr_mn)
print(f"\n{y_manx}")
And in the terminal I got the following.
(dbd-3.13) PS R:\learn\dashboard\utils> python weather_db.py
temperature
datetime
2025.10.01 05:20 13.0
2025.10.02 06:28 13.0
2025.10.03 06:26 10.0
2025.10.04 07:03 13.0
2025.10.05 07:21 9.0
2025.10.06 08:06 8.0
2025.10.07 06:59 9.0
2025.10.08 06:32 11.0
2025.10.09 06:52 11.0
2025.10.10 07:06 10.0
2025.10.11 06:52 11.0
2025.10.12 06:56 9.0
2025.10.13 06:26 6.0
2025.10.14 07:52 4.0
2025.10.15 07:26 5.0
2025.10.16 07:02 5.0
2025.10.17 06:56 10.0
2025.10.18 07:52 8.0
2025.10.19 06:49 9.0
2025.10.20 06:28 8.0
2025.10.21 06:56 10.0
2025.10.22 07:03 6.0
2025.10.23 06:27 10.0
2025.10.24 07:09 12.0
2025.10.25 07:52 8.0
av mn mx
0 9.39699 0.0 15.9
av mn mx
yr
2015 11.890476 7.5 15.9
2016 10.132258 7.2 14.8
2017 9.526667 5.2 14.4
2018 9.206452 4.9 14.9
2019 7.538710 1.0 11.6
2020 9.490323 1.0 15.7
2021 8.531250 1.0 14.0
2022 10.333333 7.0 13.0
2023 8.838710 0.0 14.0
2024 9.348387 4.0 13.9
I’ll let you check whether or not these results match those above. I believe they do.
Done
I had thought I might get to coding the charts and adding them to the dashboard. But, this was a good days work. So I am going to call it a day and this post finished.
May your days go as well as this one did for me.