As mentioned at the end of the last post, I am now going to look at adding the charts and gauges to the dashboard. For now only for the two rainfall cases. No temperature data yet available.
Display Rain Gauge for Both Cases
Okay, let’s see if we can get that semi-circular gauge displayed in the first container for both the monthly and annual rainfall cases. Will be a lot of copy and paste from the charts module’s test code m’thinks.
As I need the current month or year to-date rainfall, I am going to move the call to get the table ahead of the code to generate the plotly figure. And, in the case of the year to-date do a bit of arithmetic. Also had to do some resizing of the dashboard and figure layouts. Since the gauge/chart has a title, I am no longer setting a header for that column.
... ...
import utils.db_charts as dbc
... ...
# set ratio for the two columns
row1 = st.columns([3, 2])
row2 = st.container(height=400)
# needed more height for the gauge to display properly
grid1 = [col.container(height=500) for col in row1]
... ...
if w_item == "Month-to-date rainfall":
r_av, r_mn, r_mx = rfall.get_history(c_ym[5:])
m_rf = rfall.get_mon_rf(c_ym)
g_val = m_rf.at[m_rf.index[-1], "Month-to-date"]
fig = dbc.rf_gauge(s_mon, g_val, r_av, r_mn, r_mx)
fig.update_layout(autosize=True, width=700, height=500)
# displaying the chart on the dashboard
grid1[0].plotly_chart(fig, use_container_width=True)
grid1[1].header(f"{s_mon} {c_ym[:4]} Rainfall To-date")
# display rainfall data table
with grid1[1]:
st.dataframe(m_rf, width=400)
row2.header(f"{s_mon} Rainfall for Last 10 Years")
elif w_item == "Year-to-date rainfall":
y_rf = rfall.get_year_rf(c_ym[:4])
r_av, r_mn, r_mx = rfall.get_history(c_ym[:4], do_mon=False)
tot_25 = 0
for rf in y_rf["Rainfall to-date"]:
tot_25 += rf
fig = dbc.rf_gauge(c_ym[:4], tot_25, r_av, r_mn, r_mx, do_mon=False)
fig.update_layout(autosize=True, width=700, height=500)
# displaying the chart on the dashboard
grid1[0].plotly_chart(fig, use_container_width=True)
grid1[1].header(f"{c_ym[:4]} Monthly Rainfall")
# display rainfall data table
with grid1[1]:
st.dataframe(y_rf, width=250)
row2.header("Annual Rainfall for Last 10 Years")
And here’s a shot of the yearly gauge on the dashboard.
That was relatively easy, though it took a bit of time to sort out the re-sizing of the plot and layout.
Display 10 Year History Charts
I expect this will also be relatively easy (copy and paste). And, that, once again, some resizing will need to be played with.
Refactor Database Class Method
Well I decided to modify one of the database class methods. Specifically, get_rf_monthly(). I did not want to include the partial data for 2014 and the current year. I also figured I should allow for limiting the years displayed. So perhaps not as easy as I figured above.
Probably didn’t need to include the complete code for the refactored method, but… And, I expect there is some additional refactoring called for.
def get_rf_monthly(self, s_yr="", e_yr="", do_df=True):
"""Get total monthly rainfall for all months in rainfall table
excluding the current month, as it may not be complete.
params:
s_yr: first year to include
e_yr: last year to include
do_df: if true convert query output to dataframe and retun df
else return raw query output (list of tuples)
returns: see params
"""
# get current year and month as yyyy.mm
c_ym = time.strftime("%Y.%m", time.localtime())
# current table name
c_tnm = self.tnms['rf_tnm']
# get data from rainfall table
# generate where clause based on method arguments
if s_yr and e_yr:
w_cls = f"SUBSTR(datetime, 1, 4) >= '{s_yr}' AND SUBSTR(datetime, 1, 4) <= '{e_yr}'"
elif s_yr and (not e_yr):
w_cls = f"SUBSTR(datetime, 1, 4) >= '{s_yr}' AND SUBSTR(datetime, 1, 4) < '{c_ym[:4]}'"
elif e_yr and (not s_yr):
w_cls = f"SUBSTR(datetime, 1, 4) > '2014' AND SUBSTR(datetime, 1, 4) <= '{e_yr}'"
else:
w_cls = f"SUBSTR(datetime, 1, 4) > '2014' AND SUBSTR(datetime, 1, 4) < '{c_ym[:4]}'"
q_yrly = f"""SELECT SUBSTR(dttm, 1, 4) as yr, SUBSTR(dttm, 6, 2) as mon, ROUND(m_tot, 2)
FROM
(SELECT MAX(datetime) as dttm, monthly as m_tot
FROM {c_tnm}
WHERE {w_cls}
GROUP BY SUBSTR(datetime, 1, 7));"""
rslt = self.qry_exec(q_yrly)
if do_df:
rf_yr_mn = {}
for rw in rslt:
if rw[0] not in rf_yr_mn:
rf_yr_mn[rw[0]] = {}
rf_yr_mn[rw[0]][rw[1]] = rw[2]
rf_yr = pd.DataFrame(rf_yr_mn)
rf_yr.sort_index(inplace=True)
return rf_yr
else:
return rslt
Note: I did test using those new arguments. They seemed to work as intended.
Annual History Bar Chart
Okay, some more fooling around with sizing, but all in all reasonably straight forward.
elif w_item == "Year-to-date rainfall":
y_rf = rfall.get_year_rf(c_ym[:4])
r_av, r_mn, r_mx = rfall.get_history(c_ym[:4], do_mon=False)
rf_yr = rfall.get_rf_monthly()
tot_25 = 0
for rf in y_rf["Rainfall to-date"]:
tot_25 += rf
fig1 = dbc.rf_gauge(c_ym[:4], tot_25, r_av, r_mn, r_mx, do_mon=False)
fig1.update_layout(autosize=True, width=700, height=500)
# displaying the chart on the dashboard
grid1[0].plotly_chart(fig1, use_container_width=True)
grid1[1].header(f"{c_ym[:4]} Monthly Rainfall")
# display table
with grid1[1]:
st.dataframe(y_rf, width=250)
# display historical barchart
fig2 = dbc.rf_mon_sbar(rf_yr)
fig2.update_layout(autosize=True, height=400)
row2.plotly_chart(fig2)
And, in the dashboard I got the following. And it is interactive—in the dashboard, not in the image below.
Refactor Another Database Class Method
Figured I should also allow limiting the years of data displayed in the historical monthly rainfall charts. Sorry, once again the whole method instead of just the altered portion(s).
def get_mon_hist(self, g_mn, s_yr="", e_yr="", do_df=True):
"""Get the total month's rainfall and minimum and maximum daily rainfall
for the specified month for all years in the rainfall table except the
current year (may be incomplete month of data).
params:
g_mn: month, string 'mm', for which to get data
s_yr: first year to include
e_yr: last year to include
do_df: return dataframe if True, list of tuples otherwise
returns:
see params above
"""
# get current year and month as yyyy.mm
c_ym = time.strftime("%Y.%m", time.localtime())
# current table name
c_tnm = self.tnms['rf_tnm']
# generate where clause based on method arguments
if s_yr and e_yr:
w_cls = f"SUBSTR(datetime, 1, 4) >= '{s_yr}' AND SUBSTR(datetime, 1, 4) <= '{e_yr}'"
elif s_yr and (not e_yr):
w_cls = f"SUBSTR(datetime, 1, 4) >= '{s_yr}' AND SUBSTR(datetime, 1, 7) < '{c_ym}'"
elif e_yr and (not s_yr):
w_cls = f"SUBSTR(datetime, 1, 4) <= '{e_yr}'"
else:
w_cls = f"SUBSTR(datetime, 1, 7) < '{c_ym}'"
nx_q = f"""SELECT substr(datetime, 1, 4) as yr, substr(datetime, 6, 2) as mon, MIN(daily) as min, MAX(daily) as max, MAX(monthly) as m_tot
FROM {c_tnm}
WHERE {w_cls} and mon = '{g_mn}'
GROUP BY yr;"""
if do_df:
mn_rf = self.qry_pd(nx_q)
else:
mn_rf = self.exec(nx_q)
return mn_rf
I have not yet tested the optional arguments.
Monthly Historical Stacked Scatter Plot
Okay, let’s get that historical data chart displayed. I am going to use the horizontal stacked scatter plot for this one. Just like the look.
... ...
if w_item == "Month-to-date rainfall":
r_av, r_mn, r_mx = rfall.get_history(c_ym[5:])
m_rf = rfall.get_mon_rf(c_ym)
mn_rf = rfall.get_mon_hist(c_ym[5:])
... ...
# display historical data for this month in the 2nd row
fig2 = dbc.hist_rf_mon(mn_rf, c_typ="hgsp", sv_pth="")
row2.plotly_chart(fig2, use_container_width=True)
And, in the dashboard I got the following.
Edge Case(s)
Well, I really didn’t think things through with respect to some of the code above. Tried to start dashboard and it crashed. Just going to show you the bit that let me know I didn’t consider an edge case. Specifically, no rainfall yet in the current month. My previous tests were run in August 2025, which at the time of my coding had some rainfall. Today, was 2025.09.01 and no rain has yet fallen this month.
File "R:\learn\dashboard\dashboard.py", line 63, in <module>
g_val = m_rf.at[m_rf.index[-1], "Month-to-date"]
~~~~~~~~~~^^^^
File "E:\appDev\Miniconda3\envs\dbd-3.13\Lib\site-packages\pandas\core\indexes\base.py", line 5401, in __getitem__
return getitem(key)
IndexError: index -1 is out of bounds for axis 0 with size 0
And, with this refactoring the dashboard started up nicely.
if len(m_rf) > 0:
g_val = m_rf.at[m_rf.index[-1], "Month-to-date"]
else:
g_val = 0
At the moment I am not going to look for a similar edge case for the annual rainfall display. I think the code there works differently enough to be safe. We shall see in 4 months or so.
Finished
I now have a functional dashboard. Well at least for rainfall. Still need to work on phase 2. That will include adding daily temperature values to the database. And incorporating that information in the dashboard. I will likely start next post, as I believe this one is now pretty much done. Can’t think of anything to add to the dashboard or post in the immediate present.
Until next time, enjoy!
Update
I decided my get_daily_rf.py utility should allow for me to specify that I wanted the monthly rainfall history table to be updated. Or not. So, I am going to add a new comand line argument for that purpose. It will use the store_true action type. So, if present, the pertinent argument will be assigned a value of true. It will be false otherwise. I was going to use a shortcode of -h, but that conflicts with argparse’s built-in help argument.
Here are the changes in the utility’s code.
... ...
def get_rf_parser():
... ...
parser.add_argument("-u", "--history", help="Update history table as well",
action="store_true")
... ...
do_hist = rf_args.history
... ...
if do_hist:
print(f"Updating monthly average rainfall history table")
wdb.updt_hist_tbl()
... ...
hist_q = f"SELECT month, avg FROM {wdb.tnms["mh_tnm"]}"
d_rslt = wdb.qry_pd(hist_q)
print(d_rslt)
And a couple quick tests. Since there has been no rainfall in 2025.09, the first query returns an empty table.
(dbd-3.13) PS R:\learn\dashboard\utils> python get_daily_rf.py 0 -d 2025.09.01
args: Namespace(date='2025.09.01', time='08:00', history=False, rainfall=0.0)
0.0 2025.09.01 08:00 False
2025.09 month to date: 0
('2025.09.01 08:00', rf_rf, mtd + rf_rf) -> zero_ok: False
Empty DataFrame
Columns: [row_id, datetime, daily, monthly]
Index: []
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 36.241667
8 09 103.209091
9 10 194.161818
10 11 275.767273
11 12 254.759091
(dbd-3.13) PS R:\learn\dashboard\utils> python get_daily_rf.py 0 -d 2025.09.01 -u
args: Namespace(date='2025.09.01', time='08:00', history=True, rainfall=0.0)
0.0 2025.09.01 08:00 True
2025.09 month to date: 0
('2025.09.01 08:00', rf_rf, mtd + rf_rf) -> zero_ok: False
Updating monthly average rainfall history table
Empty DataFrame
Columns: [row_id, datetime, daily, monthly]
Index: []
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
And that seemed to work. No update during the first test. In the second, we got a line indicating the history table was being updated. And, the August average has gone up. Given that the rainfall for the month was 100 mm and the prior average was 36.24 mm, that was what we expected to see.
Okay, now I am pretty sure this post is done.