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, QMenu, QGridLayout,
QSystemTrayIcon, QStyle)
from PyQt6.QtCore import QDate, Qt, QPoint, QTimer
from PyQt6.QtGui import QFont, QColor, QAction
エクセル風のフィルタ機能とコピー機能を備えた特製テーブルウィジェット
class FilterableTableWidget(QTableWidget):
def init(self, rows, columns):
super().init(rows, columns)
self.horizontalHeader().sectionClicked.connect(self.show_filter_menu)
self.filters = {}
self.setStyleSheet(“QHeaderView::section { background-color: #F6F8FA; padding: 5px; border: 1px solid #D0D7DE; }”)
def keyPressEvent(self, event):
if event.key() == Qt.Key.Key_C and (event.modifiers() & Qt.KeyboardModifier.ControlModifier):
self.copy_selection()
else:
super().keyPressEvent(event)
def copy_selection(self):
selection = self.selectedIndexes()
if not selection: return
rows = sorted(list(set(idx.row() for idx in selection)))
cols = sorted(list(set(idx.column() for idx in selection)))
copy_text = ""
for r in rows:
row_data = []
for c in cols:
item = self.item(r, c)
if item and self.isItemSelected(item):
row_data.append(item.text().replace("\n", " "))
else:
row_data.append("")
copy_text += "\t".join(row_data) + "\n"
QApplication.clipboard().setText(copy_text)
def show_filter_menu(self, logical_index):
menu = QMenu(self)
values = set()
for row in range(self.rowCount()):
item = self.item(row, logical_index)
if item: values.add(item.text())
values = sorted(list(values))
action_all = menu.addAction("すべて表示 (フィルタ解除)")
menu.addSeparator()
for val in values: menu.addAction(val)
header_pos = self.horizontalHeader().sectionViewportPosition(logical_index)
global_pos = self.mapToGlobal(self.horizontalHeader().pos() + QPoint(header_pos, self.horizontalHeader().height()))
selected_action = menu.exec(global_pos)
if not selected_action: return
if selected_action == action_all:
if logical_index in self.filters: del self.filters[logical_index]
else:
self.filters[logical_index] = selected_action.text()
self.apply_filters()
def apply_filters(self):
for row in range(self.rowCount()):
hidden = False
for col, filter_val in self.filters.items():
item = self.item(row, col)
if item and item.text() != filter_val:
hidden = True; break
self.setRowHidden(row, hidden)class WeatherVerificationApp(QMainWindow):
def init(self):
super().init()
self.setWindowTitle(“気象予報精度検証システム (Ver 13.1 – スレッドスコア搭載)”)
self.resize(1400, 950)
self.really_quit = False # 完全終了判定フラグ
# タブをオレンジ色ベースに設定
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: #FFD180; color: #555; padding: 10px 20px; margin-right: 2px; border-top-left-radius: 8px; border-top-right-radius: 8px; font-weight: bold; }
QTabBar::tab:selected { background: #FF8C00; color: white; border-bottom: 2px solid #E65100; }
QGroupBox { font-weight: bold; color: #E65100; border: 1px solid #D0D7DE; border-radius: 6px; margin-top: 15px; background-color: #FFFFFF; }
QGroupBox::title { subcontrol-origin: margin; left: 10px; padding: 0 5px; }
QPushButton { background-color: #FF8C00; color: white; border: none; border-radius: 5px; padding: 8px 16px; font-weight: bold; }
QPushButton:hover { background-color: #F57C00; }
QPushButton:pressed { background-color: #E65100; }
QPushButton:disabled { background-color: #A0A0A0; color: #E0E0E0; }
QPushButton#ActionBtn { background-color: #28A745; }
QPushButton#ActionBtn:hover { background-color: #218838; }
QPushButton#AutoBtn { background-color: #D73A49; }
QPushButton#QuitBtn { background-color: #CB2431; }
QLineEdit, QComboBox, QDateEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
QDateEdit { min-width: 130px; font-size: 13px; }
QComboBox { min-width: 120px; }
QTableWidget { gridline-color: #E1E4E8; border: 1px solid #D0D7DE; font-size: 13px; }
QScrollArea { border: none; background-color: transparent; }
""")
# タスクトレイアイコンの設定
self.tray_icon = QSystemTrayIcon(self)
self.tray_icon.setIcon(self.style().standardIcon(QStyle.StandardPixmap.SP_ComputerIcon))
self.tray_icon.setToolTip("気象検証システム - 継続抽出・計算中")
tray_menu = QMenu()
show_action = QAction("画面を表示する", self)
show_action.triggered.connect(self.showNormal)
quit_action = QAction("完全に終了する", self)
quit_action.triggered.connect(self.force_quit)
tray_menu.addAction(show_action)
tray_menu.addSeparator()
tray_menu.addAction(quit_action)
self.tray_icon.setContextMenu(tray_menu)
self.tray_icon.show()
self.tray_icon.activated.connect(self.on_tray_icon_activated)
# ★ DBの名称変更と旧データ引き継ぎ処理
old_db_path = "weather_verification.db"
self.db_path = "weather_verification(削除禁止).db"
if os.path.exists(old_db_path) and not os.path.exists(self.db_path):
try:
os.rename(old_db_path, self.db_path)
except Exception as e:
print(f"DBリネームエラー: {e}")
self.db_path = old_db_path
self.last_forecast_folder = "未設定"
# 自動取得タイマー
self.t1_auto_timer = QTimer(self); self.t1_auto_timer.timeout.connect(self.extract_and_aggregate_obs)
self.t3_auto_timer = QTimer(self); self.t3_auto_timer.timeout.connect(self.auto_load_forecast_model_folder)
self.init_db()
self.init_ui()
def on_tray_icon_activated(self, reason):
if reason == QSystemTrayIcon.ActivationReason.DoubleClick:
self.showNormal()
self.activateWindow()
def closeEvent(self, event):
if not self.really_quit:
event.ignore()
self.hide()
self.tray_icon.showMessage(
"バックグラウンドで実行中",
"自動抽出・計算処理は継続しています。\n完全に終了する場合は右クリックまたは完全終了ボタンから終了してください。",
QSystemTrayIcon.MessageIcon.Information,
3000
)
else:
event.accept()
def force_quit(self):
self.really_quit = True
QApplication.quit()
def select_db(self):
path, _ = QFileDialog.getOpenFileName(self, "SQLiteデータベースを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
if path:
self.db_path = path
self.lbl_db_path.setText(f"📁 現在のDB: {os.path.basename(path)}")
try:
self.conn.close()
self.init_db()
self.update_tab1_station_combo()
self.update_tab4_station_combo()
QMessageBox.information(self, "完了", f"データベースを切り替えました。\n{path}")
except Exception as e:
QMessageBox.critical(self, "エラー", f"データベースの読み込みに失敗しました:\n{e}")
def init_db(self):
self.conn = sqlite3.connect(self.db_path)
cursor = self.conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS OBS (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, RainfallP1 REAL, SnowfallP1 REAL, PRIMARY KEY (ObsDay, ObsTime, MsCd))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS FCST (IssueDay TEXT, IssueTime TEXT, TargetDay TEXT, TargetTime TEXT, MsCd TEXT, ForecastStep INTEGER, FcstRainfall REAL, FcstSnowfall REAL, PRIMARY KEY (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS Mapping (ObsCd TEXT PRIMARY KEY, FcstName TEXT)''')
cursor.execute("PRAGMA table_info(TargetStations)")
if "TemplateId" not in [r[1] for r in cursor.fetchall()]:
cursor.execute("DROP TABLE IF EXISTS TargetStations")
cursor.execute('''CREATE TABLE TargetStations (TemplateId INTEGER, FcstName TEXT, PRIMARY KEY (TemplateId, FcstName))''')
targets = [(1, "大通り・円山・山鼻"), (1, "屯田・篠路"), (1, "苗穂・元町・栄町"), (1, "菊水・白石・南郷"), (1, "大谷地・新札幌"), (1, "豊平・平岸・月寒"), (1, "真駒内・澄川・藻岩下"), (1, "琴似・発寒"), (1, "前田・山口"), (1, "北野・清田・里塚"), (1, "花川・花畔・新港"), (1, "恵庭"), (1, "石山・常盤"), (1, "野幌・江別")]
cursor.executemany("INSERT INTO TargetStations VALUES (?,?)", targets)
cursor.execute("SELECT COUNT(*) FROM Mapping")
if cursor.fetchone()[0] == 0:
defaults = [("MS1", "大通り・円山・山鼻"), ("MS2", "屯田・篠路"), ("MS3", "苗穂・元町・栄町"), ("MS4", "菊水・白石・南郷"), ("MS5", "大谷地・新札幌"), ("MS6", "豊平・平岸・月寒"), ("MS7", "真駒内・澄川・藻岩下"), ("MS8", "琴似・発寒"), ("MS9", "前田・山口"), ("MS25", "北野・清田・里塚"), ("アメダス:札幌", "大通り・円山・山鼻"), ("アメダス:石狩", "花川・花畔・新港"), ("アメダス:恵庭島松", "恵庭"), ("アメダス:小金湯", "石山・常盤"), ("アメダス:江別", "野幌・江別"), ("アメダス:手稲山口", "前田・山口")]
cursor.executemany("INSERT INTO Mapping VALUES (?,?)", defaults)
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):
main_widget = QWidget()
main_layout = QVBoxLayout(main_widget)
# トップヘッダー領域（DB選択 ＆ 完全終了）
top_header = QHBoxLayout()
self.lbl_db_path = QLabel(f"📁 現在のDB: {os.path.basename(self.db_path)}")
self.lbl_db_path.setStyleSheet("font-weight: bold; color: #555;")
btn_db = QPushButton("⚙️ DBを選択 / 変更")
btn_db.clicked.connect(self.select_db)
btn_quit = QPushButton("❌ 完全に終了する")
btn_quit.setObjectName("QuitBtn")
btn_quit.clicked.connect(self.force_quit)
top_header.addWidget(self.lbl_db_path)
top_header.addStretch()
top_header.addWidget(btn_db)
top_header.addWidget(btn_quit)
main_layout.addLayout(top_header)
self.tabs = QTabWidget()
main_layout.addWidget(self.tabs)
self.setCentralWidget(main_widget)
self.setup_tab1(); self.setup_tab2(); self.setup_tab3(); self.setup_tab4(); self.setup_tab5()
# ==========================================
# タブ1: MS観測値
# ==========================================
def setup_tab1(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_db = QGroupBox("SQL Server 接続設定 ＆ データ抽出"); db_layout = QVBoxLayout(); db_layout.setContentsMargins(15, 25, 15, 15)
header_layout = QHBoxLayout()
self.lbl_t1_auto = QLabel(""); self.lbl_t1_auto.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px;")
header_layout.addStretch(); header_layout.addWidget(self.lbl_t1_auto); db_layout.addLayout(header_layout)
form_layout = QFormLayout()
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("172."); self.txt_db = QLineEdit("S"); 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)
db_layout.addLayout(form_layout)
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()
db_layout.addLayout(filter_layout)
action_layout = QHBoxLayout()
btn_extract = QPushButton("手動で抽出して保存"); btn_extract.setObjectName("ActionBtn"); btn_extract.clicked.connect(self.extract_and_aggregate_obs)
self.btn_t1_auto = QPushButton("MS自動取得を開始 (10分毎)"); self.btn_t1_auto.setObjectName("AutoBtn"); self.btn_t1_auto.clicked.connect(self.toggle_t1_auto)
action_layout.addWidget(btn_extract); action_layout.addWidget(self.btn_t1_auto); action_layout.addStretch()
db_layout.addLayout(action_layout); group_db.setLayout(db_layout)
group_view = QGroupBox("ローカルDB (OBS) 検索・閲覧ビューア (※ヘッダーをクリックで絞り込み可)"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 25, 15, 15)
search_layout = QHBoxLayout(); self.v_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.v_date_from.setCalendarPopup(True)
self.v_date_to = QDateEdit(QDate.currentDate()); self.v_date_to.setCalendarPopup(True)
self.v_cmb_station = QComboBox(); self.update_tab1_station_combo()
btn_view_search = QPushButton("OBSテーブルを検索"); btn_view_search.clicked.connect(self.search_obs_table)
search_layout.addWidget(QLabel("表示期間:")); search_layout.addWidget(self.v_date_from); search_layout.addWidget(QLabel("～")); search_layout.addWidget(self.v_date_to)
search_layout.addWidget(QLabel("地点:")); search_layout.addWidget(self.v_cmb_station); search_layout.addWidget(btn_view_search); search_layout.addStretch()
view_layout.addLayout(search_layout)
self.tbl_obs = FilterableTableWidget(0, 5)
self.tbl_obs.setHorizontalHeaderLabels(["観測日▽", "観測時間▽", "地点コード▽", "降水量(mm)▽", "降雪量(cm)▽"]); self.tbl_obs.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_obs.setMinimumHeight(350); view_layout.addWidget(self.tbl_obs); group_view.setLayout(view_layout)
layout.addWidget(group_db); layout.addWidget(group_view); self.tabs.addTab(scroll, "1. 観測値設定① (MS：DB)"); self.search_obs_table()
def toggle_t1_auto(self):
if self.t1_auto_timer.isActive():
self.t1_auto_timer.stop()
self.lbl_t1_auto.setText(""); self.btn_t1_auto.setText("MS自動取得を開始 (10分毎)")
else:
self.t1_auto_timer.start(600000) # 10分
self.lbl_t1_auto.setText("🔴 MS自動取得中"); self.btn_t1_auto.setText("MS自動取得を停止")
self.extract_and_aggregate_obs()
def update_tab1_station_combo(self):
self.v_cmb_station.clear(); self.v_cmb_station.addItem("すべて")
cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT MsCd FROM OBS ORDER BY MsCd")
self.v_cmb_station.addItems([r[0] for r in cursor.fetchall()])
def search_obs_table(self):
d_from = self.v_date_from.date().toString("yyyy-MM-dd"); d_to = self.v_date_to.date().toString("yyyy-MM-dd"); st = self.v_cmb_station.currentText()
cursor = self.conn.cursor()
if st == "すべて": cursor.execute("SELECT * FROM OBS WHERE ObsDay BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (d_from, d_to))
else: cursor.execute("SELECT * FROM OBS WHERE MsCd=? AND ObsDay BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (st, d_from, d_to))
rows = cursor.fetchall(); self.tbl_obs.setRowCount(len(rows))
for r_i, r_data in enumerate(rows):
for c_i, c_data in enumerate(r_data): self.tbl_obs.setItem(r_i, c_i, QTableWidgetItem(str(c_data)))
def get_sql_connection_string(self):
return f"DRIVER={{{self.cmb_driver.currentText()}}};SERVER={self.txt_server.text()};DATABASE={self.txt_db.text()};UID={self.txt_user.text()};PWD={self.txt_pwd.text()}"
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_Statistics WHERE MsCd IN ('MS1','MS2','MS3','MS4','MS5','MS6','MS7','MS8','MS9','MS25') AND 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 OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1) VALUES (?, ?, ?, ?, ?)", (row['ObsDay'], row['ObsTime'], row['MsCd'], row['RainfallP1'], row['SnowfallP1']))
inserted += 1
self.conn.commit(); self.update_tab1_station_combo(); self.search_obs_table()
if not self.t1_auto_timer.isActive():
QMessageBox.information(self, "完了", f"期間 [{d_from} ~ {d_to}] の抽出が完了しました。\n集約後データ数: {inserted} 件")
except Exception as e:
if not self.t1_auto_timer.isActive(): QMessageBox.critical(self, "エラー", f"データ処理中にエラーが発生しました:\n{e}")
# ==========================================
# タブ2: アメダス抽出
# ==========================================
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(15, 25, 15, 15)
btn_csv = QPushButton("JMA CSVを選択してOBSへ取り込み"); btn_csv.setObjectName("ActionBtn"); btn_csv.clicked.connect(self.import_jma_csv)
csv_layout.addWidget(btn_csv); csv_layout.addStretch(); group_csv.setLayout(csv_layout)
self.txt_log2 = QTextEdit(); self.txt_log2.setReadOnly(True); self.txt_log2.setText("ここにCSVの読込ログが表示されます..."); self.txt_log2.setMinimumHeight(400)
layout.addWidget(group_csv); layout.addWidget(self.txt_log2); layout.addStretch(); self.tabs.addTab(scroll, "2. 観測値設定② (アメダスCSV等)")
def import_jma_csv(self):
path, _ = QFileDialog.getOpenFileName(self, "気象庁アメダスCSVを選択", "", "CSV Files (*.csv)")
if not path: return
try:
with open(path, 'r', encoding='shift_jis', errors='replace') as f: lines = f.readlines()
header_idx = -1
for i, line in enumerate(lines[:15]):
if "年月日時" in line: header_idx = i; break
if header_idx == -1 or header_idx == 0: return QMessageBox.critical(self, "エラー", "CSVから「年月日時」の行が見つかりません。")
stations_raw = lines[header_idx - 1].strip('\n').split(','); elements_raw = lines[header_idx].strip('\n').split(',')
current_st = ""; stations = []
for s in stations_raw:
if s.strip(): current_st = s.strip()
stations.append(current_st)
inserted = 0; cursor = self.conn.cursor(); self.txt_log2.append(f"--- 読込開始: {os.path.basename(path)} ---")
for line in lines[header_idx + 1:]:
cols = line.strip('\n').split(',')
if not cols[0].startswith("20"): continue
try: dt = pd.to_datetime(cols[0].strip()); obs_day = dt.strftime("%Y-%m-%d"); obs_time = dt.strftime("%H:%M")
except: continue
station_data = {}
for col_idx in range(1, min(len(cols), len(stations), len(elements_raw))):
st = stations[col_idx]; el = elements_raw[col_idx]; val_str = cols[col_idx].strip()
if not st or not el: continue
if st not in station_data: station_data[st] = {'rain': None, 'snow': None}
try: val = float(val_str) if val_str else 0.0
except: val = 0.0
if "降水量" in el and station_data[st]['rain'] is None: station_data[st]['rain'] = val
elif "降雪" in el and station_data[st]['snow'] is None: station_data[st]['snow'] = val
for st, vals in station_data.items():
r = vals['rain'] if vals['rain'] is not None else 0.0; s = vals['snow'] if vals['snow'] is not None else 0.0
cursor.execute("INSERT OR REPLACE INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1) VALUES (?, ?, ?, ?, ?)", (obs_day, obs_time, f"アメダス:{st}", r, s))
inserted += 1
self.conn.commit(); self.update_tab1_station_combo(); self.txt_log2.append(f"✅ 完了: {inserted} 件の観測データをOBSに保存しました。\n")
QMessageBox.information(self, "完了", f"CSVの取り込みが完了しました。\n保存データ数: {inserted} 件")
except Exception as e: QMessageBox.critical(self, "エラー", f"CSV読込失敗:\n{e}")
# ==========================================
# タブ3: 予報Excel抽出
# ==========================================
def setup_tab3(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
# 上部エリアの左右分割レイアウト
top_layout = QHBoxLayout()
# 左側：テンプレートと抽出ボタン
left_panel = QVBoxLayout()
group_targets = QGroupBox("抽出対象の予報地点設定 (テンプレート機能)"); target_layout = QVBoxLayout(); target_layout.setContentsMargins(15, 20, 15, 15)
tgt_header_layout = QHBoxLayout(); self.cmb_template = QComboBox(); self.cmb_template.addItems([f"テンプレート{i}" for i in range(1, 6)])
self.cmb_template.currentIndexChanged.connect(self.load_target_stations)
tgt_header_layout.addWidget(QLabel("選択中:")); tgt_header_layout.addWidget(self.cmb_template); tgt_header_layout.addStretch()
self.lbl_t3_auto = QLabel(""); self.lbl_t3_auto.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px;")
tgt_header_layout.addWidget(self.lbl_t3_auto); target_layout.addLayout(tgt_header_layout)
tbl_layout = QHBoxLayout(); self.tbl_targets = FilterableTableWidget(20, 1)
self.tbl_targets.setHorizontalHeaderLabels(["抽出対象に含める予報地点名▽"]); self.tbl_targets.horizontalHeader().setSectionResizeMode(0, QHeaderView.ResizeMode.Stretch)
self.tbl_targets.setMinimumHeight(200)
tgt_btn_layout = QVBoxLayout(); tgt_btn_layout.addWidget(QLabel("現在のテンプレートに保存")); btn_save_tgt = QPushButton("保存")
btn_save_tgt.setObjectName("ActionBtn"); btn_save_tgt.clicked.connect(self.save_target_stations)
tgt_btn_layout.addWidget(btn_save_tgt); tgt_btn_layout.addStretch()
tbl_layout.addWidget(self.tbl_targets); tbl_layout.addLayout(tgt_btn_layout); target_layout.addLayout(tbl_layout); group_targets.setLayout(target_layout)
group_op = QGroupBox("最適モデルExcel フォルダ一括抽出"); op_layout = QVBoxLayout(); op_layout.setContentsMargins(15, 20, 15, 15)
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("手動でフォルダを指定")
btn_folder.setObjectName("ActionBtn"); btn_folder.clicked.connect(self.manual_load_forecast_model_folder)
self.btn_t3_auto = QPushButton("予報自動取得を開始"); self.btn_t3_auto.setObjectName("AutoBtn"); self.btn_t3_auto.clicked.connect(self.toggle_t3_auto)
action_layout.addWidget(btn_folder); action_layout.addWidget(self.btn_t3_auto); action_layout.addStretch(); op_layout.addLayout(filter_layout); op_layout.addLayout(action_layout); group_op.setLayout(op_layout)
left_panel.addWidget(group_targets); left_panel.addWidget(group_op)
# 右側：現在の抽出状況・要領
right_panel = QVBoxLayout()
group_info = QGroupBox("現在の抽出状況と要領"); info_layout = QVBoxLayout(); info_layout.setContentsMargins(15, 20, 15, 15)
self.lbl_folder_path = QLabel("📁 抽出先フォルダ: 未設定")
self.lbl_folder_path.setStyleSheet("font-weight: bold; color: #0366D6; padding-bottom: 10px;")
self.lbl_folder_path.setWordWrap(True)
info_text = """【抽出元エクセルの対象範囲】
以下の表名（行の先頭）を自動検知してデータを吸い上げます。
🌧️ 降水量の抽出：
「(1)3時間降水量」という表から抽出を開始します。
※「最終」や「(6)」「(7)」などの天気・別指標の表が見えたら自動でロックを解除し、関係のない数値を読み込まないよう防護しています。
❄️ 降雪量の抽出：
「降雪量」という表から抽出を開始します。
※同様に「最終的な降雪量」等の別の表に突入する前に抽出をストップします。
💡 ワンポイント：
抽出は常に「一番上に登場した表」を優先し、下部にある同名の重複表（ノイズ）は無視する安全装置が働いています。
“””
lbl_info = QLabel(info_text)
lbl_info.setWordWrap(True)
lbl_info.setStyleSheet(“background-color: #F6F8FA; border: 1px solid #D0D7DE; padding: 10px; border-radius: 5px;”)
info_layout.addWidget(self.lbl_folder_path); info_layout.addWidget(lbl_info); info_layout.addStretch()
group_info.setLayout(info_layout)
right_panel.addWidget(group_info)
# 左右を結合
top_layout.addLayout(left_panel, 6)
top_layout.addLayout(right_panel, 4)
group_view = QGroupBox("データプレビュー (FCST)"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 20, 15, 15)
self.tbl_fcst = FilterableTableWidget(0, 7)
self.tbl_fcst.setHorizontalHeaderLabels(["発表日▽", "発表時間▽", "対象日▽", "対象時間▽", "予報地点名▽", "予報降水量▽", "予報降雪量▽"])
self.tbl_fcst.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_fcst.setMinimumHeight(250); view_layout.addWidget(self.tbl_fcst); group_view.setLayout(view_layout)
layout.addLayout(top_layout); layout.addWidget(group_view)
self.tabs.addTab(scroll, "3. 予報値抽出設定"); self.load_target_stations(); self.load_preview_data("FCST", self.tbl_fcst)
def load_target_stations(self):
tid = self.cmb_template.currentIndex() + 1
cursor = self.conn.cursor(); cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ?", (tid,))
rows = cursor.fetchall(); self.tbl_targets.clearContents()
for i in range(20): self.tbl_targets.setItem(i, 0, QTableWidgetItem(rows[i][0] if i < len(rows) else ""))
def save_target_stations(self):
tid = self.cmb_template.currentIndex() + 1
cursor = self.conn.cursor(); cursor.execute("DELETE FROM TargetStations WHERE TemplateId = ?", (tid,))
for i in range(self.tbl_targets.rowCount()):
item = self.tbl_targets.item(i, 0)
if item and item.text().strip(): cursor.execute("INSERT INTO TargetStations VALUES (?, ?)", (tid, item.text().strip()))
self.conn.commit(); QMessageBox.information(self, "完了", f"テンプレート{tid} を保存しました。")
def toggle_t3_auto(self):
if self.t3_auto_timer.isActive():
self.t3_auto_timer.stop()
self.lbl_t3_auto.setText(""); self.btn_t3_auto.setText("予報自動取得を開始")
else:
if self.last_forecast_folder == "未設定":
QMessageBox.warning(self, "警告", "まずは「手動でフォルダを指定」を実行し、フォルダを記憶させてください。")
return
self.t3_auto_timer.start(600000)
self.lbl_t3_auto.setText("🔴 予報自動取得中"); self.btn_t3_auto.setText("予報自動取得を停止")
self.auto_load_forecast_model_folder()
def manual_load_forecast_model_folder(self):
folder = QFileDialog.getExistingDirectory(self, "予報Excelフォルダを選択")
if folder:
self.last_forecast_folder = folder
self.lbl_folder_path.setText(f"📁 抽出先フォルダ:\n{folder}")
self.process_forecast_folder(folder, silent=False)
def auto_load_forecast_model_folder(self):
if self.last_forecast_folder != "未設定": self.process_forecast_folder(self.last_forecast_folder, silent=True)
def process_forecast_folder(self, folder_path, silent=False):
d_from = self.t3_date_from.date().toString("yyyy-MM-dd"); d_to = self.t3_date_to.date().toString("yyyy-MM-dd")
period_3h_hours = 36; cols_3h = period_3h_hours // 3
tid = self.cmb_template.currentIndex() + 1; cursor = self.conn.cursor()
cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ?", (tid,))
valid_targets = [r[0] for r in cursor.fetchall()]
if not valid_targets:
if not silent: QMessageBox.warning(self, "警告", "現在のテンプレートの抽出対象リストが空です。")
return
total_inserted = 0
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:
d_match = re.search(r'(20\d{6})', filename)
issue_day = f"{d_match.group(1)[:4]}-{d_match.group(1)[4:6]}-{d_match.group(1)[6:8]}" if d_match else datetime.now().strftime("%Y-%m-%d")
if not (d_from <= issue_day <= d_to): continue
t_match = re.search(r'（(\d{2})時用）', filename)
issue_time = f"{t_match.group(1)}:00" if t_match else "00:00"
issue_dt = datetime.strptime(f"{issue_day} {issue_time}", "%Y-%m-%d %H:%M")
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)
current_mode = None
seen_rain_stations = set()
seen_snow_stations = set()
for row_idx in range(len(df)):
cat_str = str(df.iloc[row_idx, 0]).strip()
if "3時間降水量" in cat_str and not any(x in cat_str for x in ["最終", "(6)", "（６）"]):
current_mode = "rain"
elif "降雪量" in cat_str and not any(x in cat_str for x in ["最終", "(7)", "（７）"]):
current_mode = "snow"
elif any(x in cat_str for x in ["天気", "風向", "気温", "湿度", "気圧", "最終", "(5)", "（５）", "(6)", "（６）"]):
current_mode = None
if not current_mode: continue
raw_station = str(df.iloc[row_idx, 1]).strip()
if not raw_station or raw_station == "nan": continue
clean_name = re.sub(r'^.*?[)）]','', raw_station).strip()
if clean_name not in valid_targets: continue
if current_mode == "rain":
if clean_name in seen_rain_stations: continue
seen_rain_stations.add(clean_name)
elif current_mode == "snow":
if clean_name in seen_snow_stations: continue
seen_snow_stations.add(clean_name)
for i in range(cols_3h):
col_idx = 2 + i
if col_idx >= len(df.columns): break
try: val = float(df.iloc[row_idx, col_idx]) if not pd.isna(df.iloc[row_idx, col_idx]) else 0.0
except ValueError: val = 0.0
target_dt = issue_dt + timedelta(hours=i*3)
t_day, t_time = target_dt.strftime("%Y-%m-%d"), target_dt.strftime("%H:%M")
if current_mode == "rain": cursor.execute("INSERT OR REPLACE INTO FCST (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstRainfall, FcstSnowfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstSnowfall FROM FCST WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (issue_day, issue_time, t_day, t_time, clean_name, 3, val, t_day, t_time, clean_name, issue_day, issue_time, 3))
else: cursor.execute("INSERT OR REPLACE INTO FCST (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstSnowfall, FcstRainfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstRainfall FROM FCST WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (issue_day, issue_time, t_day, t_time, clean_name, 3, val, t_day, t_time, clean_name, issue_day, issue_time, 3))
total_inserted += 1
base_12h_col = 2 + cols_3h
for j in range(3):
col_idx = base_12h_col + j
if col_idx >= len(df.columns): break
if issue_time in ["09:00", "12:00"] and j == 0: continue
try: val = float(df.iloc[row_idx, col_idx]) if not pd.isna(df.iloc[row_idx, col_idx]) else 0.0
except ValueError: val = 0.0
if issue_time == "18:00": target_dt = issue_dt + timedelta(hours=12*(j+1))
else: target_dt = datetime.strptime(f"{issue_day} 18:00", "%Y-%m-%d %H:%M") + timedelta(hours=12*j)
t_day, t_time = target_dt.strftime("%Y-%m-%d"), target_dt.strftime("%H:%M")
if current_mode == "rain": cursor.execute("INSERT OR REPLACE INTO FCST (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstRainfall, FcstSnowfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstSnowfall FROM FCST WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (issue_day, issue_time, t_day, t_time, clean_name, 12, val, t_day, t_time, clean_name, issue_day, issue_time, 12))
else: cursor.execute("INSERT OR REPLACE INTO FCST (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstSnowfall, FcstRainfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstRainfall FROM FCST WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (issue_day, issue_time, t_day, t_time, clean_name, 12, val, t_day, t_time, clean_name, issue_day, issue_time, 12))
total_inserted += 1
except Exception as e: print(f"ファイル {filename} の処理エラー: {e}")
self.conn.commit(); self.load_preview_data("FCST", self.tbl_fcst)
if not silent: QMessageBox.information(self, "完了", f"抽出が完了しました。\n処理要素数: {total_inserted} 件")
# ==========================================
# タブ4: 精度評価・マッピング
# ==========================================
def setup_tab4(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_mapping = QGroupBox("地点マッピング設定 (観測地点 ⇔ 予報地点)"); mapping_layout = QHBoxLayout(); mapping_layout.setContentsMargins(15, 20, 15, 10)
self.tbl_mapping = FilterableTableWidget(16, 3); self.tbl_mapping.setHorizontalHeaderLabels(["観測地点 (OBS)▽", "連携▽", "予報地点名 (FCST)▽"])
self.tbl_mapping.horizontalHeader().setSectionResizeMode(0, QHeaderView.ResizeMode.ResizeToContents); self.tbl_mapping.horizontalHeader().setSectionResizeMode(1, QHeaderView.ResizeMode.ResizeToContents)
self.tbl_mapping.horizontalHeader().setSectionResizeMode(2, QHeaderView.ResizeMode.Stretch); self.tbl_mapping.setMinimumHeight(200); self.load_mapping_data()
map_btn_layout = QVBoxLayout(); lbl_help = QLabel("※ 予報地点名を書き換えて「保存」を押します。")
btn_save_map = QPushButton("保存して更新"); btn_save_map.setObjectName("ActionBtn"); btn_save_map.clicked.connect(self.save_mapping_data)
map_btn_layout.addWidget(lbl_help); map_btn_layout.addWidget(btn_save_map); map_btn_layout.addStretch()
mapping_layout.addWidget(self.tbl_mapping); mapping_layout.addLayout(map_btn_layout); group_mapping.setLayout(mapping_layout)
group_filter = QGroupBox("検証条件の指定 (発表日 ＆ 対象日 ＆ 発出期間)")
filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 15, 15, 15)
row1 = QHBoxLayout()
self.issue_from = QDateEdit(QDate.currentDate().addDays(-7)); self.issue_from.setCalendarPopup(True)
self.issue_to = QDateEdit(QDate.currentDate()); self.issue_to.setCalendarPopup(True)
self.target_from = QDateEdit(QDate.currentDate().addDays(-7)); self.target_from.setCalendarPopup(True)
self.target_to = QDateEdit(QDate.currentDate().addDays(2)); self.target_to.setCalendarPopup(True)
row1.addWidget(QLabel("【発表日】:")); row1.addWidget(self.issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.issue_to)
row1.addSpacing(20)
row1.addWidget(QLabel("【対象日】:")); row1.addWidget(self.target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.target_to)
row1.addStretch()
row2 = QHBoxLayout()
self.cmb_station = QComboBox(); self.update_tab4_station_combo()
self.cmb_element = QComboBox(); self.cmb_element.addItems(["降水量", "降雪量"])
self.cmb_step = QComboBox(); self.cmb_step.addItems(["1時間ステップ", "3時間ステップ", "12時間ステップ"]); self.cmb_step.setCurrentText("3時間ステップ")
self.cmb_leadtime = QComboBox(); self.cmb_leadtime.addItems(["期間制限なし", "12時間以内", "24時間以内", "36時間以内"])
self.btn_search = QPushButton("精度評価を実行"); self.btn_search.setObjectName("ActionBtn"); self.btn_search.clicked.connect(self.calculate_accuracy)
row2.addWidget(QLabel("観測地点:")); row2.addWidget(self.cmb_station); row2.addWidget(QLabel("要素:")); row2.addWidget(self.cmb_element)
row2.addWidget(QLabel("時間ステップ:")); row2.addWidget(self.cmb_step)
row2.addWidget(QLabel("発出期間:")); row2.addWidget(self.cmb_leadtime)
row2.addStretch(); row2.addWidget(self.btn_search)
filter_layout_main.addLayout(row1); filter_layout_main.addSpacing(10); filter_layout_main.addLayout(row2)
group_filter.setLayout(filter_layout_main)
eval_layout = QHBoxLayout()
# 1. 量的評価
group_quant = QGroupBox("量的評価指標"); quant_layout = QVBoxLayout(); quant_layout.setContentsMargins(15, 20, 15, 15)
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)
# 2. カテゴリ評価 (★スレッドスコア追加)
group_cat = QGroupBox("カテゴリ評価 (>0.0)"); cat_layout = QGridLayout(); cat_layout.setContentsMargins(15, 20, 15, 15)
self.lbl_hit = QLabel("的中率: --"); self.lbl_pod = QLabel("捕捉率: --"); self.lbl_ts = QLabel("スレッドスコア: --")
self.lbl_far = QLabel("空振り率: --"); self.lbl_miss = QLabel("見逃し率: --")
cat_layout.addWidget(self.lbl_hit, 0, 0); cat_layout.addWidget(self.lbl_pod, 0, 1); cat_layout.addWidget(self.lbl_ts, 0, 2)
cat_layout.addWidget(self.lbl_far, 1, 0); cat_layout.addWidget(self.lbl_miss, 1, 1)
group_cat.setLayout(cat_layout)
# 3. 独自指標 (★スレッドスコア追加)
group_custom = QGroupBox("独自指標 (閾値評価)"); custom_layout = QVBoxLayout(); custom_layout.setContentsMargins(15, 10, 15, 10)
self.lbl_custom1_title = QLabel("【閾値1】: --"); self.lbl_custom1_title.setStyleSheet("font-weight: bold; color: #D73A49;")
self.lbl_custom1_stats = QLabel("全数: -- / 的中(A): --")
self.lbl_custom1_rates = QLabel("的中率: -- / 捕捉率: -- / 空振り率: -- / 見逃し率: -- / TS: --")
custom_layout.addWidget(self.lbl_custom1_title); custom_layout.addWidget(self.lbl_custom1_stats); custom_layout.addWidget(self.lbl_custom1_rates)
custom_layout.addSpacing(5)
self.lbl_custom2_title = QLabel("【閾値2】: --"); self.lbl_custom2_title.setStyleSheet("font-weight: bold; color: #D73A49;")
self.lbl_custom2_stats = QLabel("全数: -- / 的中(A): --")
self.lbl_custom2_rates = QLabel("的中率: -- / 捕捉率: -- / 空振り率: -- / 見逃し率: -- / TS: --")
custom_layout.addWidget(self.lbl_custom2_title); custom_layout.addWidget(self.lbl_custom2_stats); custom_layout.addWidget(self.lbl_custom2_rates)
group_custom.setLayout(custom_layout)
eval_layout.addWidget(group_quant, 1)
eval_layout.addWidget(group_cat, 1)
eval_layout.addWidget(group_custom, 2)
self.tbl_detail = FilterableTableWidget(0, 7)
self.tbl_detail.setHorizontalHeaderLabels(["観測地点▽", "発表日時▽", "対象日時▽", "予報値▽", "実況値(加算済)▽", "差分▽", "判定▽"])
self.tbl_detail.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch); self.tbl_detail.setMinimumHeight(250)
layout.addWidget(group_mapping); layout.addWidget(group_filter); layout.addLayout(eval_layout); layout.addWidget(self.tbl_detail); layout.addStretch()
self.tabs.addTab(scroll, "4. 精度評価・マッピング")
def load_mapping_data(self):
cursor = self.conn.cursor(); cursor.execute("SELECT ObsCd, FcstName FROM Mapping"); rows = cursor.fetchall()
for i, row in enumerate(rows):
item_obs = QTableWidgetItem(row[0]); item_obs.setFlags(item_obs.flags() & ~Qt.ItemFlag.ItemIsEditable); item_obs.setBackground(QColor("#F6F8FA")); self.tbl_mapping.setItem(i, 0, item_obs)
item_arrow = QTableWidgetItem(" ⟷ "); item_arrow.setFlags(item_arrow.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_mapping.setItem(i, 1, item_arrow)
self.tbl_mapping.setItem(i, 2, QTableWidgetItem(row[1]))
def save_mapping_data(self):
cursor = self.conn.cursor()
for i in range(self.tbl_mapping.rowCount()):
if self.tbl_mapping.item(i, 0): cursor.execute("INSERT OR REPLACE INTO Mapping (ObsCd, FcstName) VALUES (?, ?)", (self.tbl_mapping.item(i, 0).text(), self.tbl_mapping.item(i, 2).text().strip()))
self.conn.commit(); self.update_tab4_station_combo(); QMessageBox.information(self, "完了", "マッピング設定を保存しました。")
def update_tab4_station_combo(self):
self.cmb_station.clear(); self.cmb_station.addItem("すべて")
cursor = self.conn.cursor(); cursor.execute("SELECT ObsCd FROM Mapping"); self.cmb_station.addItems([r[0] for r in cursor.fetchall()])
def calculate_accuracy(self):
self.btn_search.setText("⏳ 計算中...お待ちください")
self.btn_search.setEnabled(False)
QApplication.processEvents()
try:
obs_cd_sel = self.cmb_station.currentText()
is_rain = self.cmb_element.currentText() == "降水量"
d_issue_from = self.issue_from.date().toString("yyyy-MM-dd"); d_issue_to = self.issue_to.date().toString("yyyy-MM-dd")
d_tgt_from = self.target_from.date().toString("yyyy-MM-dd"); d_tgt_to = self.target_to.date().toString("yyyy-MM-dd")
step_val = 3 if "3時間" in self.cmb_step.currentText() else (12 if "12時間" in self.cmb_step.currentText() else 1)
lead_time_txt = self.cmb_leadtime.currentText()
cursor = self.conn.cursor()
if obs_cd_sel == "すべて":
cursor.execute("SELECT ObsCd, FcstName FROM Mapping")
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ?", (obs_cd_sel,))
row = cursor.fetchone()
if not row:
self.btn_search.setText("精度評価を実行"); self.btn_search.setEnabled(True)
return QMessageBox.warning(self, "通知", "マッピングが見つかりません。")
mappings = [(obs_cd_sel, row[0])]
all_dfs = []
for obs_cd, fcst_name in mappings:
fcst_query = f"SELECT '{obs_cd}' AS Station, IssueDay || ' ' || IssueTime AS IssueDT, TargetDay || ' ' || TargetTime AS TargetDT, {'FcstRainfall' if is_rain else 'FcstSnowfall'} AS Fcst FROM FCST WHERE MsCd = ? AND IssueDay BETWEEN ? AND ? AND TargetDay BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, d_issue_from, d_issue_to, d_tgt_from, d_tgt_to, step_val))
obs_start_dt = datetime.strptime(d_tgt_from, "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, {'RainfallP1' if is_rain else 'SnowfallP1'} AS Obs FROM OBS WHERE MsCd = ? AND ObsDay >= ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd, obs_start_dt.strftime("%Y-%m-%d")))
if df_fcst.empty or df_obs.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
df_obs.set_index('ObsDT', inplace=True)
df_obs = df_obs.resample('1h').sum()
if step_val > 1: df_obs_agg = df_obs.rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg = df_obs
df_obs_agg = df_obs_agg.reset_index()
df_obs_agg['TargetDT'] = df_obs_agg['ObsDT'].dt.strftime('%Y-%m-%d %H:%M')
df_merged = pd.merge(df_fcst, df_obs_agg[['TargetDT', 'Obs']], on='TargetDT', how='inner')
all_dfs.append(df_merged)
if not all_dfs:
self.tbl_detail.setRowCount(0)
error_msg = "指定された期間・条件に合致する「予報データ」と「観測データ」のペアが見つかりませんでした。\n\n【原因例】\n・対象期間の予報がまだ抽出されていない\n・観測データが未取得\n・マッピング名が間違っている"
QMessageBox.warning(self, "データ不足", error_msg)
return
df = pd.concat(all_dfs, ignore_index=True)
if lead_time_txt != "期間制限なし":
max_hours = int(lead_time_txt.replace("時間以内", ""))
df['LeadTime'] = (pd.to_datetime(df['TargetDT']) - pd.to_datetime(df['IssueDT'])).dt.total_seconds() / 3600
df = df[(df['LeadTime'] >= 0) & (df['LeadTime'] <= max_hours)]
if df.empty:
self.tbl_detail.setRowCount(0)
return QMessageBox.warning(self, "通知", "発出期間の制限によりデータが0件になりました。")
df['Diff'] = df['Fcst'] - df['Obs']
# --- 1. 量的評価 ---
self.lbl_rmse.setText(f"RMSE: {np.sqrt((df['Diff']**2).mean()):.2f}"); self.lbl_me.setText(f"ME (差): {df['Diff'].mean():.2f}"); self.lbl_mae.setText(f"MAE: {df['Diff'].abs().mean():.2f}")
# --- 2. カテゴリ評価 (>0) (★スレッドスコア追加) ---
def calc_metrics(A, B, C, D):
total = A + B + C + D
hit = (A + D) / total * 100 if total > 0 else 0
pod = A / (A + C) * 100 if (A + C) > 0 else 0
far = B / (A + B) * 100 if (A + B) > 0 else 0
miss = C / (A + C) * 100 if (A + C) > 0 else 0
ts = A / (A + B + C) * 100 if (A + B + C) > 0 else 0 # スレッドスコア
return total, hit, pod, far, miss, ts
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, hit, pod, far, miss, ts = calc_metrics(A, B, C, D)
self.lbl_hit.setText(f"的中率: {hit:.1f} %"); self.lbl_pod.setText(f"捕捉率: {pod:.1f} %")
self.lbl_far.setText(f"空振り率: {far:.1f} %"); self.lbl_miss.setText(f"見逃し率: {miss:.1f} %")
self.lbl_ts.setText(f"スレッドスコア: {ts:.1f} %")
# --- 3. 独自指標評価 (1.0 と 10.0) ---
unit = "mm" if is_rain else "cm"
thresholds = [1.0, 10.0]
# 閾値1
T1 = thresholds[0]
self.lbl_custom1_title.setText(f"【閾値 >= {T1} {unit}】")
A1 = len(df[(df['Fcst'] >= T1) & (df['Obs'] >= T1)]); B1 = len(df[(df['Fcst'] >= T1) & (df['Obs'] < T1)])
C1 = len(df[(df['Fcst'] < T1) & (df['Obs'] >= T1)]); D1 = len(df[(df['Fcst'] < T1) & (df['Obs'] < T1)])
tot1, hit1, pod1, far1, miss1, ts1 = calc_metrics(A1, B1, C1, D1)
self.lbl_custom1_stats.setText(f"全数: {tot1} / 的中(A): {A1}")
self.lbl_custom1_rates.setText(f"的中率: {hit1:.1f}% / 捕捉率: {pod1:.1f}% / 空振り率: {far1:.1f}% / 見逃し率: {miss1:.1f}% / TS: {ts1:.1f}%")
# 閾値2
T2 = thresholds[1]
self.lbl_custom2_title.setText(f"【閾値 >= {T2} {unit}】")
A2 = len(df[(df['Fcst'] >= T2) & (df['Obs'] >= T2)]); B2 = len(df[(df['Fcst'] >= T2) & (df['Obs'] < T2)])
C2 = len(df[(df['Fcst'] < T2) & (df['Obs'] >= T2)]); D2 = len(df[(df['Fcst'] < T2) & (df['Obs'] < T2)])
tot2, hit2, pod2, far2, miss2, ts2 = calc_metrics(A2, B2, C2, D2)
self.lbl_custom2_stats.setText(f"全数: {tot2} / 的中(A): {A2}")
self.lbl_custom2_rates.setText(f"的中率: {hit2:.1f}% / 捕捉率: {pod2:.1f}% / 空振り率: {far2:.1f}% / 見逃し率: {miss2:.1f}% / TS: {ts2:.1f}%")
# テーブル描画
self.tbl_detail.setRowCount(len(df))
for i, r in df.reset_index().iterrows():
self.tbl_detail.setItem(i, 0, QTableWidgetItem(r['Station'])); self.tbl_detail.setItem(i, 1, QTableWidgetItem(r['IssueDT']))
self.tbl_detail.setItem(i, 2, QTableWidgetItem(r['TargetDT'])); self.tbl_detail.setItem(i, 3, QTableWidgetItem(f"{r['Fcst']:.1f}"))
self.tbl_detail.setItem(i, 4, 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, 5, diff_item)
self.tbl_detail.setItem(i, 6, QTableWidgetItem("的中" if (r['Fcst']>0 and r['Obs']>0) or (r['Fcst']==0 and r['Obs']==0) else "外れ"))
except Exception as e:
QMessageBox.critical(self, "エラー", f"計算処理中にエラーが発生しました:\n{e}")
finally:
self.btn_search.setText("精度評価を実行")
self.btn_search.setEnabled(True)
# ==========================================
# タブ5: 検証Excel出力
# ==========================================
def setup_tab5(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_filter = QGroupBox("プロ仕様・検証レポート出力"); filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 20, 15, 15)
row_sync = QHBoxLayout()
btn_sync = QPushButton("🔄 タブ4の条件をここにコピー"); btn_sync.clicked.connect(self.sync_from_tab4)
row_sync.addWidget(btn_sync); row_sync.addStretch(); filter_layout_main.addLayout(row_sync)
row1 = QHBoxLayout()
self.ex_issue_from = QDateEdit(QDate.currentDate().addDays(-7)); self.ex_issue_from.setCalendarPopup(True)
self.ex_issue_to = QDateEdit(QDate.currentDate()); self.ex_issue_to.setCalendarPopup(True)
self.ex_target_from = QDateEdit(QDate.currentDate().addDays(-7)); self.ex_target_from.setCalendarPopup(True)
self.ex_target_to = QDateEdit(QDate.currentDate().addDays(2)); self.ex_target_to.setCalendarPopup(True)
row1.addWidget(QLabel("【発表日】:")); row1.addWidget(self.ex_issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_issue_to)
row1.addSpacing(30)
row1.addWidget(QLabel("【対象日】:")); row1.addWidget(self.ex_target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_target_to)
row1.addStretch()
row2 = QHBoxLayout()
self.ex_cmb_station = QComboBox(); cursor = self.conn.cursor(); cursor.execute("SELECT ObsCd FROM Mapping"); self.ex_cmb_station.addItem("すべて"); self.ex_cmb_station.addItems([r[0] for r in cursor.fetchall()])
self.ex_cmb_element = QComboBox(); self.ex_cmb_element.addItems(["降水量", "降雪量"])
self.ex_cmb_step = QComboBox(); self.ex_cmb_step.addItems(["1時間ステップ", "3時間ステップ", "12時間ステップ"]); self.ex_cmb_step.setCurrentText("3時間ステップ")
self.ex_cmb_leadtime = QComboBox(); self.ex_cmb_leadtime.addItems(["期間制限なし", "12時間以内", "24時間以内", "36時間以内"])
row2.addWidget(QLabel("観測地点:")); row2.addWidget(self.ex_cmb_station); row2.addWidget(QLabel("要素:")); row2.addWidget(self.ex_cmb_element)
row2.addWidget(QLabel("時間ステップ:")); row2.addWidget(self.ex_cmb_step)
row2.addWidget(QLabel("発出期間:")); row2.addWidget(self.ex_cmb_leadtime); row2.addStretch()
filter_layout_main.addLayout(row1); filter_layout_main.addSpacing(10); filter_layout_main.addLayout(row2)
group_filter.setLayout(filter_layout_main)
group_export = QGroupBox("レポート出力実行"); e_layout = QVBoxLayout(); e_layout.setContentsMargins(15, 25, 15, 15)
btn_export = QPushButton("検証レポートExcelを作成"); btn_export.setObjectName("ActionBtn"); btn_export.clicked.connect(self.export_matrix_excel)
e_layout.addWidget(QLabel("全地点一括出力対応。上部に指定期間等の情報、全体精度、および独自指標（1mm/10mm等）を記載します。")); 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 sync_from_tab4(self):
self.ex_issue_from.setDate(self.issue_from.date()); self.ex_issue_to.setDate(self.issue_to.date())
self.ex_target_from.setDate(self.target_from.date()); self.ex_target_to.setDate(self.target_to.date())
self.ex_cmb_station.setCurrentText(self.cmb_station.currentText()); self.ex_cmb_element.setCurrentText(self.cmb_element.currentText())
self.ex_cmb_step.setCurrentText(self.cmb_step.currentText()); self.ex_cmb_leadtime.setCurrentText(self.cmb_leadtime.currentText())
def export_matrix_excel(self):
now_str = datetime.now().strftime('%Y%m%d_%H%M%S') # 時間まで記載
default_name = f"Verification_Report_{now_str}.xlsx"
path, _ = QFileDialog.getSaveFileName(self, "検証レポートを保存", default_name, "Excel Files (*.xlsx)")
if not path: return
try:
obs_cd_sel = self.ex_cmb_station.currentText()
is_rain = self.ex_cmb_element.currentText() == "降水量"
d_i_from = self.ex_issue_from.date().toString("yyyy-MM-dd"); d_i_to = self.ex_issue_to.date().toString("yyyy-MM-dd")
d_t_from = self.ex_target_from.date().toString("yyyy-MM-dd"); d_t_to = self.ex_target_to.date().toString("yyyy-MM-dd")
step_val = 3 if "3時間" in self.ex_cmb_step.currentText() else (12 if "12時間" in self.ex_cmb_step.currentText() else 1)
lead_time_txt = self.ex_cmb_leadtime.currentText()
cursor = self.conn.cursor()
if obs_cd_sel == "すべて":
cursor.execute("SELECT ObsCd, FcstName FROM Mapping")
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ?", (obs_cd_sel,))
row = cursor.fetchone()
if not row: return QMessageBox.warning(self, "エラー", "マッピングが見つかりません。")
mappings = [(obs_cd_sel, row[0])]
with pd.ExcelWriter(path, engine='openpyxl') as writer:
wrote_any = False
for obs_cd, fcst_name in mappings:
fcst_query = f"SELECT IssueDay || ' ' || IssueTime AS IssueDT, TargetDay || ' ' || TargetTime AS TargetDT, {'FcstRainfall' if is_rain else 'FcstSnowfall'} AS Fcst FROM FCST WHERE MsCd = ? AND IssueDay BETWEEN ? AND ? AND TargetDay BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, d_i_from, d_i_to, d_t_from, d_t_to, step_val))
obs_start_dt = datetime.strptime(d_t_from, "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, {'RainfallP1' if is_rain else 'SnowfallP1'} AS Obs FROM OBS WHERE MsCd = ? AND ObsDay >= ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd, obs_start_dt.strftime("%Y-%m-%d")))
if df_fcst.empty or df_obs.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
df_obs.set_index('ObsDT', inplace=True)
df_obs = df_obs.resample('1h').sum()
if step_val > 1: df_obs_agg = df_obs.rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg = df_obs
df_obs_agg = df_obs_agg.reset_index()
df_obs_agg['TargetDT'] = df_obs_agg['ObsDT'].dt.strftime('%Y-%m-%d %H:%M')
df = pd.merge(df_fcst, df_obs_agg[['TargetDT', 'Obs']], on='TargetDT', how='inner')
if df.empty: continue
if lead_time_txt != "期間制限なし":
max_hours = int(lead_time_txt.replace("時間以内", ""))
df['LeadTime'] = (pd.to_datetime(df['TargetDT']) - pd.to_datetime(df['IssueDT'])).dt.total_seconds() / 3600
df = df[(df['LeadTime'] >= 0) & (df['LeadTime'] <= max_hours)]
if df.empty: continue
df['Diff(Fcst-Obs)'] = df['Fcst'] - df['Obs']
# --- 指標計算用ヘルパー (★スレッドスコア追加) ---
def calc_m(A, B, C, D):
tot = A + B + C + D
h = (A + D) / tot * 100 if tot > 0 else 0
p = A / (A + C) * 100 if (A + C) > 0 else 0
f = B / (A + B) * 100 if (A + B) > 0 else 0
m = C / (A + C) * 100 if (A + C) > 0 else 0
ts = A / (A + B + C) * 100 if (A + B + C) > 0 else 0 # スレッドスコア
return tot, h, p, f, m, ts
# 1. 量的
rmse = np.sqrt((df['Diff(Fcst-Obs)']**2).mean())
me = df['Diff(Fcst-Obs)'].mean()
mae = df['Diff(Fcst-Obs)'].abs().mean()
# 2. カテゴリ (>0)
A0 = len(df[(df['Fcst'] > 0) & (df['Obs'] > 0)]); B0 = len(df[(df['Fcst'] > 0) & (df['Obs'] == 0)])
C0 = len(df[(df['Fcst'] == 0) & (df['Obs'] > 0)]); D0 = len(df[(df['Fcst'] == 0) & (df['Obs'] == 0)])
tot0, hit0, pod0, far0, miss0, ts0 = calc_m(A0, B0, C0, D0)
# 3. 独自指標
unit = "mm" if is_rain else "cm"
T1, T2 = 1.0, 10.0
A1 = len(df[(df['Fcst'] >= T1) & (df['Obs'] >= T1)]); B1 = len(df[(df['Fcst'] >= T1) & (df['Obs'] < T1)])
C1 = len(df[(df['Fcst'] < T1) & (df['Obs'] >= T1)]); D1 = len(df[(df['Fcst'] < T1) & (df['Obs'] < T1)])
tot1, hit1, pod1, far1, miss1, ts1 = calc_m(A1, B1, C1, D1)
A2 = len(df[(df['Fcst'] >= T2) & (df['Obs'] >= T2)]); B2 = len(df[(df['Fcst'] >= T2) & (df['Obs'] < T2)])
C2 = len(df[(df['Fcst'] < T2) & (df['Obs'] >= T2)]); D2 = len(df[(df['Fcst'] < T2) & (df['Obs'] < T2)])
tot2, hit2, pod2, far2, miss2, ts2 = calc_m(A2, B2, C2, D2)
pivot_fcst = df.pivot_table(index='IssueDT', columns='TargetDT', values='Fcst', aggfunc='mean')
pivot_obs = df.pivot_table(index='IssueDT', columns='TargetDT', values='Obs', aggfunc='mean')
pivot_diff = df.pivot_table(index='IssueDT', columns='TargetDT', values='Diff(Fcst-Obs)', aggfunc='mean')
combined_rows = []
targets = sorted(pivot_fcst.columns)
for issue in sorted(pivot_fcst.index):
obs_row = [f"{issue} (観測)"]; fcst_row = [f"{issue} (予報)"]; diff_row = ["差分 (予報-観測)"]
for t in targets:
obs_row.append(pivot_obs.loc[issue, t] if t in pivot_obs.columns and not pd.isna(pivot_obs.loc[issue, t]) else np.nan)
fcst_row.append(pivot_fcst.loc[issue, t] if t in pivot_fcst.columns and not pd.isna(pivot_fcst.loc[issue, t]) else np.nan)
diff_row.append(pivot_diff.loc[issue, t] if t in pivot_diff.columns and not pd.isna(pivot_diff.loc[issue, t]) else np.nan)
combined_rows.extend([obs_row, fcst_row, diff_row, [np.nan] * (len(targets) + 1)])
out_df = pd.DataFrame(combined_rows, columns=['発表日時'] + targets)
sheet_name = obs_cd.replace(":", "_")[:31]
out_df.to_excel(writer, sheet_name=sheet_name, startrow=24, index=False)
worksheet = writer.sheets[sheet_name]
# ===== ヘッダー情報の書き込み (★列5にTSを追加) =====
# [基本条件]
worksheet.cell(row=1, column=1, value="【検証パラメータ】")
worksheet.cell(row=2, column=1, value=f"観測地点: {obs_cd}"); worksheet.cell(row=2, column=2, value=f"要素: {self.ex_cmb_element.currentText()}")
worksheet.cell(row=3, column=1, value=f"時間ステップ: {step_val}時間"); worksheet.cell(row=3, column=2, value=f"発出期間制限: {lead_time_txt}")
worksheet.cell(row=4, column=1, value=f"発表日期間: {d_i_from} ～ {d_i_to}")
worksheet.cell(row=5, column=1, value=f"対象日期間: {d_t_from} ～ {d_t_to}")
# [量的評価・カテゴリ評価]
worksheet.cell(row=7, column=1, value="【全体精度 (>0.0)】")
worksheet.cell(row=8, column=1, value=f"RMSE: {rmse:.2f}"); worksheet.cell(row=8, column=2, value=f"ME: {me:.2f}"); worksheet.cell(row=8, column=3, value=f"MAE: {mae:.2f}")
worksheet.cell(row=9, column=1, value=f"全数: {tot0}"); worksheet.cell(row=9, column=2, value=f"的中回数(A): {A0}")
worksheet.cell(row=10, column=1, value=f"的中率: {hit0:.1f}%"); worksheet.cell(row=10, column=2, value=f"捕捉率: {pod0:.1f}%"); worksheet.cell(row=10, column=3, value=f"空振り率: {far0:.1f}%"); worksheet.cell(row=10, column=4, value=f"見逃し率: {miss0:.1f}%"); worksheet.cell(row=10, column=5, value=f"TS: {ts0:.1f}%")
# [独自指標 1]
worksheet.cell(row=12, column=1, value=f"【独自指標 1: 閾値 >= {T1} {unit}】")
worksheet.cell(row=13, column=1, value=f"全数: {tot1}"); worksheet.cell(row=13, column=2, value=f"的中回数(A): {A1}")
worksheet.cell(row=14, column=1, value=f"的中率: {hit1:.1f}%"); worksheet.cell(row=14, column=2, value=f"捕捉率: {pod1:.1f}%"); worksheet.cell(row=14, column=3, value=f"空振り率: {far1:.1f}%"); worksheet.cell(row=14, column=4, value=f"見逃し率: {miss1:.1f}%"); worksheet.cell(row=14, column=5, value=f"TS: {ts1:.1f}%")
# [独自指標 2]
worksheet.cell(row=16, column=1, value=f"【独自指標 2: 閾値 >= {T2} {unit}】")
worksheet.cell(row=17, column=1, value=f"全数: {tot2}"); worksheet.cell(row=17, column=2, value=f"的中回数(A): {A2}")
worksheet.cell(row=18, column=1, value=f"的中率: {hit2:.1f}%"); worksheet.cell(row=18, column=2, value=f"捕捉率: {pod2:.1f}%"); worksheet.cell(row=18, column=3, value=f"空振り率: {far2:.1f}%"); worksheet.cell(row=18, column=4, value=f"見逃し率: {miss2:.1f}%"); worksheet.cell(row=18, column=5, value=f"TS: {ts2:.1f}%")
worksheet.cell(row=23, column=1, value="▼ 詳細マトリクスデータ")
wrote_any = True
if wrote_any: QMessageBox.information(self, "完了", f"レポートを出力しました。\n保存先: {path}")
else: QMessageBox.warning(self, "データなし", "出力可能なデータがありませんでした。")
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)
app.setQuitOnLastWindowClosed(False)
window = WeatherVerificationApp()
window.show()
sys.exit(app.exec())
