import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import re
import pyodbc
from PyQt6.QtWidgets import (QApplication, QMainWindow, QTabWidget, QWidget,
QVBoxLayout, QHBoxLayout, QPushButton, QLabel,
QFileDialog, QTableWidget, QTableWidgetItem, QComboBox,
QDateEdit, QMessageBox, QGroupBox, QHeaderView, QTextEdit,
QLineEdit, QCheckBox, QFormLayout, QScrollArea)
from PyQt6.QtCore import QDate, Qt, QTimer
from PyQt6.QtGui import QFont, QColor
class WeatherVerificationApp(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle(“気象予報精度検証システム (Ver 4.1 – UIめり込み完全解消版)”)
self.resize(1300, 850)
# ★ UIの「めり込み・潰れ」を直すためのスタイルシート修正
self.setStyleSheet(“””
QMainWindow { background-color: #F4F6F9; font-family: ‘Segoe UI’, ‘Meiryo’, sans-serif; }
QTabWidget::pane { border: 1px solid #D0D7DE; background: white; border-radius: 8px; }
QTabBar::tab { background: #E1E4E8; color: #586069; padding: 10px 20px; margin-right: 2px; border-top-left-radius: 8px; border-top-right-radius: 8px; font-weight: bold; }
QTabBar::tab:selected { background: #FFFFFF; color: #0366D6; border-bottom: 2px solid #0366D6; }
/* グループボックスの余白を大きく取り、文字のめり込みを防ぐ */
QGroupBox {
font-weight: bold; color: #24292E;
border: 1px solid #D0D7DE; border-radius: 8px;
margin-top: 25px; /* 上部マージンをさらに拡大 */
padding: 20px 10px 10px 10px; /* 内側のパディングを拡大(上/右/下/左) */
background-color: #FFFFFF;
}
QGroupBox::title {
subcontrol-origin: margin;
subcontrol-position: top left;
left: 15px; top: -10px; /* タイトルの位置を調整 */
padding: 0 5px;
background-color: transparent;
color: #0366D6;
}
QPushButton { background-color: #0366D6; color: white; border: none; border-radius: 5px; padding: 8px 16px; font-weight: bold; }
QPushButton:hover { background-color: #005CC5; }
QPushButton:pressed { background-color: #00448B; }
QPushButton#ActionBtn { background-color: #28A745; }
QPushButton#ActionBtn:hover { background-color: #218838; }
QLineEdit, QComboBox, QDateEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
QTableWidget { gridline-color: #E1E4E8; border: 1px solid #D0D7DE; font-size: 13px; }
QHeaderView::section { background-color: #F6F8FA; color: #24292E; font-weight: bold; padding: 5px; border: 1px solid #D0D7DE; }
QScrollArea { border: none; background-color: transparent; }
“””)
self.db_path = “weather_verification.db”
self.init_db()
self.init_ui()
def init_db(self):
self.conn = sqlite3.connect(self.db_path)
cursor = self.conn.cursor()
cursor.execute(”’
CREATE TABLE IF NOT EXISTS T_MS_Stastics (
ObsDay TEXT, ObsTime TEXT, MsCd TEXT,
RainfallP1 REAL, SnowfallP1 REAL,
PRIMARY KEY (ObsDay, ObsTime, MsCd)
)
”’)
cursor.execute(”’
CREATE TABLE IF NOT EXISTS T_Forecast (
IssueDay TEXT, IssueTime TEXT, TargetDay TEXT, TargetTime TEXT, MsCd TEXT,
FcstRainfall REAL, FcstSnowfall REAL,
PRIMARY KEY (IssueDay, IssueTime, TargetDay, TargetTime, MsCd)
)
”’)
self.conn.commit()
def create_scroll_tab(self):
scroll = QScrollArea()
scroll.setWidgetResizable(True)
inner_widget = QWidget()
scroll.setWidget(inner_widget)
return scroll, inner_widget
def init_ui(self):
self.tabs = QTabWidget()
self.setCentralWidget(self.tabs)
self.setup_tab1()
self.setup_tab2()
self.setup_tab3()
self.setup_tab4()
self.setup_tab5()
# ==========================================
# タブ1: MS観測値 (ODBC設定 ＆ 期間抽出)
# ==========================================
def setup_tab1(self):
scroll, inner_widget = self.create_scroll_tab()
layout = QVBoxLayout(inner_widget)
layout.setSpacing(15) # レイアウト間の隙間を確保
group_db = QGroupBox(“SQL Server 接続設定”)
form_layout = QFormLayout()
form_layout.setContentsMargins(10, 20, 10, 10) # フォーム内の余白確保
self.cmb_driver = QComboBox()
self.cmb_driver.setEditable(True)
self.cmb_driver.addItems([“SQL Server”, “ODBC Driver 17 for SQL Server”, “ODBC Driver 18 for SQL Server”, “SQL Server Native Client 11.0”])
self.txt_server = QLineEdit(“localhost\\SQLEXPRESS”)
self.txt_db = QLineEdit(“SNET”)
self.txt_user = QLineEdit(“sa”)
self.txt_pwd = QLineEdit(“”)
self.txt_pwd.setEchoMode(QLineEdit.EchoMode.Password)
form_layout.addRow(“ODBCドライバー:”, self.cmb_driver)
form_layout.addRow(“サーバー名 (Host):”, self.txt_server)
form_layout.addRow(“データベース名:”, self.txt_db)
form_layout.addRow(“ユーザーID:”, self.txt_user)
form_layout.addRow(“パスワード:”, self.txt_pwd)
btn_test_db = QPushButton(“SQL Server 導通テスト”)
btn_test_db.clicked.connect(self.test_sqlserver_connection)
form_layout.addRow(“”, btn_test_db)
group_db.setLayout(form_layout)
group_op = QGroupBox(“観測データ抽出設定 (10分値 → 1時間値へ集約)”)
op_layout = QVBoxLayout()
op_layout.setContentsMargins(10, 20, 10, 10)
op_layout.setSpacing(10)
filter_layout = QHBoxLayout()
self.t1_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.t1_date_from.setCalendarPopup(True)
self.t1_date_to = QDateEdit(QDate.currentDate()); self.t1_date_to.setCalendarPopup(True)
filter_layout.addWidget(QLabel(“抽出期間:”))
filter_layout.addWidget(self.t1_date_from)
filter_layout.addWidget(QLabel(“～”))
filter_layout.addWidget(self.t1_date_to)
filter_layout.addStretch()
action_layout = QHBoxLayout()
btn_extract = QPushButton(“指定期間を抽出してDBへ集約保存”)
btn_extract.setObjectName(“ActionBtn”)
btn_extract.clicked.connect(self.extract_and_aggregate_obs)
self.chk_auto_t1 = QCheckBox(“自動抽出を有効化 (常駐モード)”)
action_layout.addWidget(btn_extract)
action_layout.addWidget(self.chk_auto_t1)
action_layout.addStretch()
op_layout.addLayout(filter_layout)
op_layout.addLayout(action_layout)
group_op.setLayout(op_layout)
group_view = QGroupBox(“ローカルDB プレビュー (T_MS_Stastics)”)
view_layout = QVBoxLayout()
view_layout.setContentsMargins(10, 20, 10, 10)
self.tbl_obs = QTableWidget(0, 5)
self.tbl_obs.setHorizontalHeaderLabels([“観測日”, “観測時間”, “地点コード”, “降水量(mm)”, “降雪量(cm)”])
self.tbl_obs.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_obs.setMinimumHeight(200)
view_layout.addWidget(self.tbl_obs)
group_view.setLayout(view_layout)
layout.addWidget(group_db)
layout.addWidget(group_op)
layout.addWidget(group_view)
layout.addStretch()
self.tabs.addTab(scroll, “1. DB設定・MS観測値”)
self.load_preview_data(“T_MS_Stastics”, self.tbl_obs)
def get_sql_connection_string(self):
driver = self.cmb_driver.currentText()
server = self.txt_server.text()
db = self.txt_db.text()
user = self.txt_user.text()
pwd = self.txt_pwd.text()
return f”DRIVER={{{driver}}};SERVER={server};DATABASE={db};UID={user};PWD={pwd}”
def test_sqlserver_connection(self):
try:
conn = pyodbc.connect(self.get_sql_connection_string(), timeout=3)
conn.close()
QMessageBox.information(self, “成功”, “SQL Serverへの接続に成功しました！”)
except Exception as e:
QMessageBox.critical(self, “エラー”, f”接続失敗。ドライバー名やパスワードを確認してください:\n{e}”)
def extract_and_aggregate_obs(self):
d_from = self.t1_date_from.date().toString(“yyyy-MM-dd”)
d_to = self.t1_date_to.date().toString(“yyyy-MM-dd”)
try:
conn_sql = pyodbc.connect(self.get_sql_connection_string())
query = f”SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1 FROM T_MS_Stastics WHERE ObsDay BETWEEN ‘{d_from}’ AND ‘{d_to}'”
df = pd.read_sql(query, conn_sql)
conn_sql.close()
if df.empty:
return QMessageBox.warning(self, “通知”, “指定期間のデータが存在しません。”)
df[‘datetime_str’] = df[‘ObsDay’].astype(str) + ‘ ‘ + df[‘ObsTime’].astype(str)
df[‘datetime’] = pd.to_datetime(df[‘datetime_str’], errors=’coerce’)
df = df.dropna(subset=[‘datetime’])
df[‘HourlyTime’] = df[‘datetime’].dt.ceil(‘1h’)
df_hourly = df.groupby([‘MsCd’, ‘HourlyTime’])[[‘RainfallP1’, ‘SnowfallP1’]].sum().reset_index()
df_hourly[‘ObsDay’] = df_hourly[‘HourlyTime’].dt.strftime(‘%Y-%m-%d’)
df_hourly[‘ObsTime’] = df_hourly[‘HourlyTime’].dt.strftime(‘%H:%M’)
cursor = self.conn.cursor()
inserted = 0
for _, row in df_hourly.iterrows():
cursor.execute(“””
INSERT OR REPLACE INTO T_MS_Stastics (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1)
VALUES (?, ?, ?, ?, ?)
“””, (row[‘ObsDay’], row[‘ObsTime’], row[‘MsCd’], row[‘RainfallP1’], row[‘SnowfallP1’]))
inserted += 1
self.conn.commit()
self.load_preview_data(“T_MS_Stastics”, self.tbl_obs)
QMessageBox.information(self, “完了”, f”期間 [{d_from} ~ {d_to}] の抽出・集約が完了しました。\n集約後データ数: {inserted} 件”)
except Exception as e:
QMessageBox.critical(self, “エラー”, f”データ処理中にエラーが発生しました:\n{e}”)
# ==========================================
# タブ2: アメダス抽出 (BUFR解読ボタン復活版)
# ==========================================
def setup_tab2(self):
scroll, inner_widget = self.create_scroll_tab()
layout = QVBoxLayout(inner_widget)
layout.setSpacing(15)
group_csv = QGroupBox(“気象庁ダウンロードCSV取り込み”)
csv_layout = QHBoxLayout()
csv_layout.setContentsMargins(10, 20, 10, 10)
btn_csv = QPushButton(“JMA CSVを選択して取り込み”)
btn_csv.setObjectName(“ActionBtn”)
btn_csv.clicked.connect(self.dummy_action)
csv_layout.addWidget(btn_csv)
csv_layout.addStretch()
group_csv.setLayout(csv_layout)
# ★ 誤って削除していたBUFR解読グループを復活
group_bufr = QGroupBox(“アメダスBUFRファイル解読 (メイン機能)”)
bufr_layout = QHBoxLayout()
bufr_layout.setContentsMargins(10, 20, 10, 10)
btn_bufr = QPushButton(“BUFRファイルを解読”)
btn_bufr.clicked.connect(self.dummy_action) # 後日解読スクリプトと結線します
bufr_layout.addWidget(btn_bufr)
bufr_layout.addStretch()
group_bufr.setLayout(bufr_layout)
self.txt_log2 = QTextEdit()
self.txt_log2.setReadOnly(True)
self.txt_log2.setText(“処理ログがここに表示されます…”)
self.txt_log2.setMinimumHeight(200)
layout.addWidget(group_csv)
layout.addWidget(group_bufr) # 復活
layout.addWidget(self.txt_log2)
layout.addStretch()
self.tabs.addTab(scroll, “2. アメダス抽出(CSV/BUFR)”)
def dummy_action(self):
QMessageBox.information(self, “通知”, “この機能は後日詳細なフォーマットに合わせて実装します。”)
# ==========================================
# タブ3: 予報Excel抽出
# ==========================================
def setup_tab3(self):
scroll, inner_widget = self.create_scroll_tab()
layout = QVBoxLayout(inner_widget)
layout.setSpacing(15)
group_op = QGroupBox(“最適モデルExcel フォルダ一括取り込み設定”)
op_layout = QVBoxLayout()
op_layout.setContentsMargins(10, 20, 10, 10)
op_layout.setSpacing(10)
filter_layout = QHBoxLayout()
self.t3_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.t3_date_from.setCalendarPopup(True)
self.t3_date_to = QDateEdit(QDate.currentDate()); self.t3_date_to.setCalendarPopup(True)
filter_layout.addWidget(QLabel(“抽出対象期間 (発表日):”))
filter_layout.addWidget(self.t3_date_from)
filter_layout.addWidget(QLabel(“～”))
filter_layout.addWidget(self.t3_date_to)
filter_layout.addStretch()
action_layout = QHBoxLayout()
btn_folder = QPushButton(“フォルダを指定してExcelを一括取り込み”)
btn_folder.setObjectName(“ActionBtn”)
btn_folder.clicked.connect(self.load_forecast_model_folder)
self.chk_auto_t3 = QCheckBox(“Excelデータの自動取得を有効化 (常駐モード)”)
action_layout.addWidget(btn_folder)
action_layout.addWidget(self.chk_auto_t3)
action_layout.addStretch()
op_layout.addLayout(filter_layout)
op_layout.addLayout(action_layout)
group_op.setLayout(op_layout)
group_view = QGroupBox(“データプレビュー (T_Forecast)”)
view_layout = QVBoxLayout()
view_layout.setContentsMargins(10, 20, 10, 10)
self.tbl_fcst = QTableWidget(0, 7)
self.tbl_fcst.setHorizontalHeaderLabels([“発表日”, “発表時間”, “対象日”, “対象時間”, “地点(MS)”, “予報降水量”, “予報降雪量”])
self.tbl_fcst.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_fcst.setMinimumHeight(300)
view_layout.addWidget(self.tbl_fcst)
group_view.setLayout(view_layout)
layout.addWidget(group_op)
layout.addWidget(group_view)
layout.addStretch()
self.tabs.addTab(scroll, “3. 予報Excel抽出”)
self.load_preview_data(“T_Forecast”, self.tbl_fcst)
def load_forecast_model_folder(self):
folder_path = QFileDialog.getExistingDirectory(self, “予報Excelフォルダを選択”)
if not folder_path: return
d_from = self.t3_date_from.date().toString(“yyyy-MM-dd”)
d_to = self.t3_date_to.date().toString(“yyyy-MM-dd”)
total_inserted = 0
cursor = self.conn.cursor()
target_rain_categories = [“(1)3時間降水量”, “(6)最終的な降水量”, “最終的な降水量”, “降水量”]
target_snow_categories = [“(7)最終的な降雪量”, “最終的な降雪量”, “降雪量”]
station_map = {
“大通り・円山・山鼻”: “MS1”, “大通・円山・山鼻”: “MS1”, “屯田・篠路”: “MS2”,
“苗穂・元町・栄町”: “MS3”, “菊水・白石・南郷”: “MS4”, “大谷地・新札幌”: “MS5”,
“豊平・平岸・月寒”: “MS6”, “真駒内・澄川・藻岩下”: “MS7”, “琴似・発寒”: “MS8”,
“前田・山口”: “MS9”, “北野・清田・里塚”: “MS25”
}
def clean_station_name(raw_name):
clean_name = re.sub(r’^.*?\)’,”, str(raw_name)).strip()
return station_map.get(clean_name, clean_name)
for filename in os.listdir(folder_path):
if not filename.endswith(“.xlsx”) or filename.startswith(“~”): continue
file_path = os.path.join(folder_path, filename)
try:
date_match = re.search(r'(20\d{6})’, filename)
issue_day = f”{date_match.group(1)[:4]}-{date_match.group(1)[4:6]}-{date_match.group(1)[6:8]}” if date_match else datetime.now().strftime(“%Y-%m-%d”)
if not (d_from <= issue_day <= d_to): continue
time_match = re.search(r’（(\d{2})時用）’, filename)
issue_time = f”{time_match.group(1)}:00″ if time_match else “00:00”
excel_file = pd.ExcelFile(file_path)
target_sheet = “①最適モデル” if “①最適モデル” in excel_file.sheet_names else excel_file.sheet_names[0]
df = pd.read_excel(file_path, header=None, sheet_name=target_sheet)
target_times_raw = df.iloc[1, 2:].fillna(“”).tolist()
current_category = “”
for row_idx in range(2, len(df)):
cat_val = df.iloc[row_idx, 0]
if pd.notna(cat_val) and str(cat_val).strip() != “”:
current_category = str(cat_val).strip()
raw_station = df.iloc[row_idx, 1]
if pd.isna(raw_station) or str(raw_station).strip() == “”: continue
station_code = clean_station_name(raw_station)
is_rain = any(k in current_category for k in target_rain_categories)
is_snow = any(k in current_category for k in target_snow_categories)
if not (is_rain or is_snow): continue
base_date_obj = datetime.strptime(issue_day, “%Y-%m-%d”)
for col_idx in range(2, 2 + len(target_times_raw)):
if col_idx >= len(df.columns): break
raw_target_t = str(target_times_raw[col_idx-2]).strip()
if not raw_target_t: continue
val = df.iloc[row_idx, col_idx]
val = 0.0 if pd.isna(val) else float(val)
add_days = 0
if “明日” in raw_target_t: add_days = 1
elif “明後日” in raw_target_t: add_days = 2
target_day = (base_date_obj + timedelta(days=add_days)).strftime(“%Y-%m-%d”)
t_match = re.search(r'(\d+)時’, raw_target_t)
target_t = f”{int(t_match.group(1)):02d}:00″ if t_match else “00:00”
if is_rain:
cursor.execute(“INSERT OR REPLACE INTO T_Forecast (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, FcstRainfall, FcstSnowfall) VALUES (?,?,?,?,?,?, COALESCE((SELECT FcstSnowfall FROM T_Forecast WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=?), 0.0))”,
(issue_day, issue_time, target_day, target_t, station_code, val, target_day, target_t, station_code, issue_day, issue_time))
elif is_snow:
cursor.execute(“INSERT OR REPLACE INTO T_Forecast (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, FcstSnowfall, FcstRainfall) VALUES (?,?,?,?,?,?, COALESCE((SELECT FcstRainfall FROM T_Forecast WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=?), 0.0))”,
(issue_day, issue_time, target_day, target_t, station_code, val, target_day, target_t, station_code, issue_day, issue_time))
total_inserted += 1
except Exception as e:
print(f”ファイル {filename} の処理エラー: {e}”)
self.conn.commit()
self.load_preview_data(“T_Forecast”, self.tbl_fcst)
QMessageBox.information(self, “完了”, f”期間 [{d_from} ~ {d_to}] のExcelを一括処理しました。\n処理要素数: {total_inserted} 件”)
# ==========================================
# タブ4: 精度評価・分析
# ==========================================
def setup_tab4(self):
scroll, inner_widget = self.create_scroll_tab()
layout = QVBoxLayout(inner_widget)
layout.setSpacing(15)
group_filter = QGroupBox(“検証条件の指定”)
filter_layout = QHBoxLayout()
filter_layout.setContentsMargins(10, 20, 10, 10)
self.date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.date_from.setCalendarPopup(True)
self.date_to = QDateEdit(QDate.currentDate()); self.date_to.setCalendarPopup(True)
self.cmb_station = QComboBox(); self.cmb_station.addItems([“MS1”, “MS2”, “MS3”, “MS4”, “MS5”])
self.cmb_element = QComboBox(); self.cmb_element.addItems([“降水量”, “降雪量”])
self.cmb_step = QComboBox(); self.cmb_step.addItems([“1時間ステップ”, “3時間ステップ”, “12時間ステップ”])
btn_search = QPushButton(“精度評価を実行”)
btn_search.setObjectName(“ActionBtn”)
btn_search.clicked.connect(self.calculate_accuracy)
filter_layout.addWidget(QLabel(“期間:”)); filter_layout.addWidget(self.date_from)
filter_layout.addWidget(QLabel(“～”)); filter_layout.addWidget(self.date_to)
filter_layout.addWidget(QLabel(“地点:”)); filter_layout.addWidget(self.cmb_station)
filter_layout.addWidget(QLabel(“要素:”)); filter_layout.addWidget(self.cmb_element)
filter_layout.addWidget(QLabel(“時間ステップ:”)); filter_layout.addWidget(self.cmb_step)
filter_layout.addStretch()
filter_layout.addWidget(btn_search)
group_filter.setLayout(filter_layout)
eval_layout = QHBoxLayout()
group_quant = QGroupBox(“量的評価指標”)
quant_layout = QVBoxLayout()
quant_layout.setContentsMargins(10, 20, 10, 10)
self.lbl_rmse = QLabel(“RMSE: –“); self.lbl_me = QLabel(“ME (差): –“); self.lbl_mae = QLabel(“MAE: –“)
quant_layout.addWidget(self.lbl_rmse); quant_layout.addWidget(self.lbl_me); quant_layout.addWidget(self.lbl_mae)
group_quant.setLayout(quant_layout)
group_cat = QGroupBox(“カテゴリ評価 (閾値: >0.0)”)
cat_layout = QVBoxLayout()
cat_layout.setContentsMargins(10, 20, 10, 10)
self.lbl_hit = QLabel(“的中率: –“); self.lbl_pod = QLabel(“捕捉率: –“); self.lbl_far = QLabel(“空振り率: –“); self.lbl_miss = QLabel(“見逃し率: –“)
cat_layout.addWidget(self.lbl_hit); cat_layout.addWidget(self.lbl_pod); cat_layout.addWidget(self.lbl_far); cat_layout.addWidget(self.lbl_miss)
group_cat.setLayout(cat_layout)
eval_layout.addWidget(group_quant)
eval_layout.addWidget(group_cat)
self.tbl_detail = QTableWidget(0, 6)
self.tbl_detail.setHorizontalHeaderLabels([“発表日時”, “対象日時”, “予報値”, “実況値”, “差分”, “判定”])
self.tbl_detail.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_detail.setMinimumHeight(300)
layout.addWidget(group_filter)
layout.addLayout(eval_layout)
layout.addWidget(self.tbl_detail)
layout.addStretch()
self.tabs.addTab(scroll, “4. 精度評価・分析”)
def calculate_accuracy(self):
station = self.cmb_station.currentText()
element = “FcstRainfall, RainfallP1” if self.cmb_element.currentText() == “降水量” else “FcstSnowfall, SnowfallP1”
d_from = self.date_from.date().toString(“yyyy-MM-dd”)
d_to = self.date_to.date().toString(“yyyy-MM-dd”)
step_text = self.cmb_step.currentText()
query = f”’
SELECT F.IssueDay || ‘ ‘ || F.IssueTime, F.TargetDay || ‘ ‘ || F.TargetTime, {element}, F.TargetTime
FROM T_Forecast F INNER JOIN T_MS_Stastics O
ON F.TargetDay = O.ObsDay AND (F.TargetTime = O.ObsTime OR F.TargetTime LIKE ‘%’||O.ObsTime||’%’) AND F.MsCd = O.MsCd
WHERE F.MsCd = ? AND F.IssueDay BETWEEN ? AND ?
”’
df = pd.read_sql_query(query, self.conn, params=(station, d_from, d_to))
if df.empty: return QMessageBox.warning(self, “通知”, “条件に合致するデータがありません。”)
df.columns = [‘IssueDT’, ‘TargetDT’, ‘Fcst’, ‘Obs’, ‘TargetTimeOnly’]
df[‘Hour’] = df[‘TargetTimeOnly’].str[:2].astype(int)
if step_text == “3時間ステップ”:
df = df[df[‘Hour’] % 3 == 0]
elif step_text == “12時間ステップ”:
df = df[df[‘Hour’] % 12 == 0]
if df.empty: return QMessageBox.warning(self, “通知”, f”{step_text} の条件に合致するデータがありません。”)
df[‘Diff’] = df[‘Fcst’] – df[‘Obs’]
rmse = np.sqrt((df[‘Diff’]**2).mean())
self.lbl_rmse.setText(f”RMSE: {rmse:.2f}”); self.lbl_me.setText(f”ME (差): {df[‘Diff’].mean():.2f}”)
self.lbl_mae.setText(f”MAE: {df[‘Diff’].abs().mean():.2f}”)
A = len(df[(df[‘Fcst’] > 0) & (df[‘Obs’] > 0)])
B = len(df[(df[‘Fcst’] > 0) & (df[‘Obs’] == 0)])
C = len(df[(df[‘Fcst’] == 0) & (df[‘Obs’] > 0)])
D = len(df[(df[‘Fcst’] == 0) & (df[‘Obs’] == 0)])
total = A + B + C + D
self.lbl_hit.setText(f”的中率: {((A + D) / total * 100):.1f} %” if total > 0 else “的中率: –“)
self.lbl_pod.setText(f”捕捉率: {(A / (A + C) * 100):.1f} %” if (A + C) > 0 else “捕捉率: –“)
self.lbl_far.setText(f”空振り率: {(B / (A + B) * 100):.1f} %” if (A + B) > 0 else “空振り率: –“)
self.lbl_miss.setText(f”見逃し率: {(C / (A + C) * 100):.1f} %” if (A + C) > 0 else “見逃し率: –“)
self.tbl_detail.setRowCount(len(df))
for i, r in df.reset_index().iterrows():
self.tbl_detail.setItem(i, 0, QTableWidgetItem(r[‘IssueDT’]))
self.tbl_detail.setItem(i, 1, QTableWidgetItem(r[‘TargetDT’]))
self.tbl_detail.setItem(i, 2, QTableWidgetItem(f”{r[‘Fcst’]:.1f}”))
self.tbl_detail.setItem(i, 3, QTableWidgetItem(f”{r[‘Obs’]:.1f}”))
diff_item = QTableWidgetItem(f”{r[‘Diff’]:+.1f}”)
if r[‘Diff’] > 0: diff_item.setForeground(QColor(“#D73A49”))
elif r[‘Diff’] < 0: diff_item.setForeground(QColor(“#0366D6”))
self.tbl_detail.setItem(i, 4, diff_item)
judge = “的中” if (r[‘Fcst’]>0 and r[‘Obs’]>0) or (r[‘Fcst’]==0 and r[‘Obs’]==0) else “外れ”
self.tbl_detail.setItem(i, 5, QTableWidgetItem(judge))
# ==========================================
# タブ5: 検証Excel出力
# ==========================================
def setup_tab5(self):
scroll, inner_widget = self.create_scroll_tab()
layout = QVBoxLayout(inner_widget)
layout.setSpacing(15)
group_filter = QGroupBox(“マトリクス出力条件”)
f_layout = QHBoxLayout()
f_layout.setContentsMargins(10, 20, 10, 10)
self.ex_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.ex_date_from.setCalendarPopup(True)
self.ex_date_to = QDateEdit(QDate.currentDate()); self.ex_date_to.setCalendarPopup(True)
self.ex_cmb_station = QComboBox(); self.ex_cmb_station.addItems([“MS1”, “MS2”, “MS3”, “MS4”, “MS5”])
self.ex_cmb_element = QComboBox(); self.ex_cmb_element.addItems([“降水量”, “降雪量”])
f_layout.addWidget(QLabel(“期間:”)); f_layout.addWidget(self.ex_date_from)
f_layout.addWidget(QLabel(“～”)); f_layout.addWidget(self.ex_date_to)
f_layout.addWidget(QLabel(“地点:”)); f_layout.addWidget(self.ex_cmb_station)
f_layout.addWidget(QLabel(“要素:”)); f_layout.addWidget(self.ex_cmb_element)
f_layout.addStretch()
group_filter.setLayout(f_layout)
group_export = QGroupBox(“斜め遷移マトリクスExcel出力”)
e_layout = QVBoxLayout()
e_layout.setContentsMargins(10, 20, 10, 10)
desc = QLabel(“縦軸に「発表時間」、横軸に「対象時間」を配置し、\n誤差、予報値、実況値の3シートを含むExcelファイルを出力します。”)
btn_export = QPushButton(“マトリクス形式でExcel出力”)
btn_export.setObjectName(“ActionBtn”)
btn_export.clicked.connect(self.export_matrix_excel)
e_layout.addWidget(desc)
e_layout.addWidget(btn_export)
e_layout.addStretch()
group_export.setLayout(e_layout)
layout.addWidget(group_filter)
layout.addWidget(group_export)
layout.addStretch()
self.tabs.addTab(scroll, “5. 検証Excel出力”)
def export_matrix_excel(self):
path, _ = QFileDialog.getSaveFileName(self, “検証マトリクスを保存”, “Verification_Matrix.xlsx”, “Excel Files (*.xlsx)”)
if not path: return
try:
station = self.ex_cmb_station.currentText()
element_fcst = “FcstRainfall” if self.ex_cmb_element.currentText() == “降水量” else “FcstSnowfall”
element_obs = “RainfallP1” if self.ex_cmb_element.currentText() == “降水量” else “SnowfallP1”
d_from = self.ex_date_from.date().toString(“yyyy-MM-dd”)
d_to = self.ex_date_to.date().toString(“yyyy-MM-dd”)
query = f”’
SELECT
F.IssueDay || ‘ ‘ || F.IssueTime AS IssueDT,
F.TargetDay || ‘ ‘ || F.TargetTime AS TargetDT,
F.{element_fcst} AS Fcst, O.{element_obs} AS Obs
FROM T_Forecast F
INNER JOIN T_MS_Stastics O
ON F.TargetDay = O.ObsDay AND (F.TargetTime = O.ObsTime OR F.TargetTime LIKE ‘%’||O.ObsTime||’%’) AND F.MsCd = O.MsCd
WHERE F.MsCd = ? AND F.IssueDay BETWEEN ? AND ?
”’
df = pd.read_sql_query(query, self.conn, params=(station, d_from, d_to))
if df.empty: return QMessageBox.warning(self, “データなし”, “出力可能なデータがありません。”)
df[‘Diff(Fcst-Obs)’] = df[‘Fcst’] – df[‘Obs’]
pivot_diff = df.pivot(index=’IssueDT’, columns=’TargetDT’, values=’Diff(Fcst-Obs)’)
pivot_fcst = df.pivot(index=’IssueDT’, columns=’TargetDT’, values=’Fcst’)
pivot_obs = df.pivot(index=’IssueDT’, columns=’TargetDT’, values=’Obs’)
with pd.ExcelWriter(path) as writer:
pivot_diff.to_excel(writer, sheet_name=’誤差(予報-実況)’)
pivot_fcst.to_excel(writer, sheet_name=’予報値’)
pivot_obs.to_excel(writer, sheet_name=’実況値’)
QMessageBox.information(self, “完了”, f”マトリクスExcelを出力しました。\n保存先: {path}”)
except Exception as e:
QMessageBox.critical(self, “エラー”, f”出力失敗:\n{e}”)
def load_preview_data(self, table_name, widget):
cursor = self.conn.cursor()
cursor.execute(f”SELECT * FROM {table_name} LIMIT 50″)
rows = cursor.fetchall()
widget.setRowCount(len(rows))
for r_i, r_data in enumerate(rows):
for c_i, c_data in enumerate(r_data):
widget.setItem(r_i, c_i, QTableWidgetItem(str(c_data)))
if __name__ == “__main__”:
app = QApplication(sys.argv)
window = WeatherVerificationApp()
window.show()
sys.exit(app.exec())
コメント