import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone
import re
import pyodbc
import shutil
import requests
import json
import math
import glob
import io
import subprocess
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, QRadioButton, QButtonGroup, QFrame, QDateTimeEdit,QGraphicsView,QGraphicsScene)
from PyQt6.QtCore import QDate, Qt, QPoint, QTimer, QDateTime
from PyQt6.QtGui import QAction, QColor, QFont, QPixmap, QImageReader
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 ZoomableView(QGraphicsView):
def __init__(self, scene, parent=None):
super().__init__(scene, parent)
self.setDragMode(QGraphicsView.DragMode.ScrollHandDrag)
self.setTransformationAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
self.setResizeAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
def wheelEvent(self, event):
zoom_in_factor = 1.15
zoom_out_factor = 1.0 / zoom_in_factor
if event.angleDelta().y() > 0:
zoom_factor = zoom_in_factor
else:
zoom_factor = zoom_out_factor
self.scale(zoom_factor, zoom_factor)
class WeatherVerificationApp(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("気象予報精度検証システム (Ver 18.0 - 内部時間軸UTC完全統一版)")
self.resize(1500, 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#TestBtn { background-color: #007BFF; }
QPushButton#TestBtn:hover { background-color: #0069D9; }
QPushButton#AutoBtn { background-color: #D73A49; }
QPushButton#QuitBtn { background-color: #CB2431; }
QLineEdit, QComboBox, QDateEdit, QDateTimeEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
QDateEdit, QDateTimeEdit { min-width: 140px; 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)
self.db_path = "weather_verification(削除禁止).db"
self.last_forecast_folder = "未設定"
# 札幌圏 座標設定 (GRIB2抽出用)
self.lat_min = 42.0 + (40.0 / 60.0)
self.lat_max = 43.0 + (20.0 / 60.0)
self.lon_min = 141.0 + (0.0 / 60.0)
self.lon_max = 141.0 + (40.0 / 60.0)
self.map_aspect_ratio = 1.0 / math.cos(math.radians(43.0))
self.wgrib2_path = "wgrib2.exe"
self.grib_target_points = []
self.grib_selected_point = None
self.t1_auto_timer = QTimer(self); self.t1_auto_timer.timeout.connect(self.extract_and_aggregate_obs)
self.t2_auto_timer = QTimer(self); self.t2_auto_timer.timeout.connect(self.fetch_amedas_api)
self.t3_auto_timer = QTimer(self); self.t3_auto_timer.timeout.connect(self.auto_load_forecast_model_folder)
self.t8_auto_timer = QTimer(self); self.t8_auto_timer.timeout.connect(self.auto_fetch_grib2)
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)} (※内部データ時間軸: UTC世界標準時で統一)")
try:
self.conn.close(); self.init_db()
self.update_tab1_station_combo(); self.update_tab5_station_combo()
QMessageBox.information(self, "完了", f"データベースを切り替えました。\n{path}")
except Exception as e: QMessageBox.critical(self, "エラー", f"データベースの読み込みに失敗しました:\n{e}")
# ★追加：JSTの日付範囲を裏側でUTCの日時検索範囲文字列に変換するヘルパー関数
def jst_date_range_to_utc_str(self, qdate_from, qdate_to):
dt_from = datetime.combine(qdate_from.toPyDate(), datetime.min.time()) - timedelta(hours=9)
dt_to = datetime.combine(qdate_to.toPyDate(), datetime.max.time().replace(second=0, microsecond=0)) - timedelta(hours=9)
return dt_from.strftime("%Y-%m-%d %H:%M"), dt_to.strftime("%Y-%m-%d %H:%M")
def init_db(self):
self.conn = sqlite3.connect(self.db_path)
cursor = self.conn.cursor()
# OBSテーブルの構造チェックと必要に応じた拡張
cursor.execute("PRAGMA table_info(OBS)")
obs_cols = [r[1] for r in cursor.fetchall()]
if obs_cols and "MsCd" not in obs_cols: cursor.execute("DROP TABLE OBS")
if obs_cols and "SnowDepth" not in obs_cols:
try: cursor.execute("ALTER TABLE OBS ADD COLUMN SnowDepth REAL")
except: pass
# 基本実況・予報系テーブルの作成 (すべての日付時刻カラムは内部的にUTCで統一保存)
cursor.execute('''CREATE TABLE IF NOT EXISTS OBS (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, RainfallP1 REAL, SnowfallP1 REAL, SnowDepth 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 JMA_FCST (TargetStart TEXT, TargetEnd TEXT, Region TEXT, Element TEXT, RawText TEXT, MinVal REAL, MaxVal REAL, PRIMARY KEY (TargetStart, TargetEnd, Region, Element))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS GPV_Points (PointID INTEGER PRIMARY KEY AUTOINCREMENT, Lon REAL, Lat REAL, UNIQUE(Lon, Lat))''')
# --- 1. TargetStations (タブ3: 抽出対象予報地点) の整合性チェックと初期データ投入 ---
cursor.execute("PRAGMA table_info(TargetStations)")
tgt_cols = [r[1] for r in cursor.fetchall()]
if not tgt_cols or "TemplateId" not in tgt_cols:
cursor.execute("DROP TABLE IF EXISTS TargetStations")
cursor.execute('''CREATE TABLE TargetStations (TemplateId INTEGER, FcstName TEXT, PRIMARY KEY (TemplateId, FcstName))''')
cursor.execute("SELECT COUNT(*) FROM TargetStations")
if cursor.fetchone()[0] == 0:
print("タブ3の抽出対象エリア(14地点)をテンプレート1〜5に登録中...")
base_targets = ["大通り・円山・山鼻", "屯田・篠路", "苗穂・元町・栄町", "菊水・白石・南郷", "大谷地・新札幌", "豊平・平岸・月寒", "真駒内・澄川・藻岩下", "琴似・発寒", "前田・山口", "北野・清田・里塚", "花川・花畔・新港", "恵庭", "石山・常盤", "野幌・江別"]
tgt_defaults = []
for tid in range(1, 6):
for name in base_targets:
tgt_defaults.append((tid, name))
cursor.executemany("INSERT INTO TargetStations VALUES (?,?)", tgt_defaults)
# --- 2. Mapping (タブ5: 地点マッピング設定) の整合性チェックと初期データ投入 ---
cursor.execute("PRAGMA table_info(Mapping)")
map_cols = [r[1] for r in cursor.fetchall()]
if not map_cols or "TemplateId" not in map_cols:
cursor.execute("DROP TABLE IF EXISTS Mapping")
cursor.execute('''CREATE TABLE Mapping (TemplateId INTEGER, ObsCd TEXT, FcstName TEXT, PRIMARY KEY (TemplateId, ObsCd))''')
cursor.execute("SELECT COUNT(*) FROM Mapping")
if cursor.fetchone()[0] == 0:
print("タブ5の観測・予報マッピング初期値をテンプレート1〜5に登録中...")
base_mappings = [
("MS1", "大通り・円山・山鼻"), ("MS2", "屯田・篠路"), ("MS3", "苗穂・元町・栄町"),
("MS4", "菊水・白石・南郷"), ("MS5", "大谷地・新札幌"), ("MS6", "豊平・平岸・月寒"),
("MS7", "真駒内・澄川・藻岩下"), ("MS8", "琴似・発寒"), ("MS9", "前田・山口"),
("MS25", "北野・清田・里塚"), ("アメダス:札幌", "大通り・円山・山鼻"),
("アメダス:石狩", "花川・花畔・新港"), ("アメダス:恵庭島松", "恵庭"),
("アメダス:小金湯", "石山・常盤"), ("アメダス:江別", "野幌・江別"),
("アメダス:手稲山口", "前田・山口")
]
map_defaults = []
for tid in range(1, 6):
for obs, fcst in base_mappings:
map_defaults.append((tid, obs, fcst))
cursor.executemany("INSERT INTO Mapping VALUES (?,?,?)", map_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)
top_header = QHBoxLayout()
self.lbl_db_path = QLabel(f"📁 現在のDB: {os.path.basename(self.db_path)} (※内部データ時間軸: UTC世界標準時で統一)")
self.lbl_db_path.setStyleSheet("font-weight: bold; color: #444;")
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_tab8()
self.setup_tab3(); self.setup_tab4(); self.setup_tab5(); self.setup_tab6(); self.setup_tab7()
# ==========================================
# タブ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 接続設定 ＆ データ抽出 (※取得時にJSTからUTCへ自動変換してDB保存されます)"); 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"])
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("DB:", self.txt_db); form_layout.addRow("User:", self.txt_user); form_layout.addRow("Pass:", 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("抽出期間 (JST):")); 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_test_conn = QPushButton("🔌 接続テスト"); btn_test_conn.setObjectName("TestBtn"); btn_test_conn.clicked.connect(self.test_sql_connection)
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_test_conn); 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) 検索・閲覧ビューア (※期間指定はJST、画面表示時もJSTに逆変換されます)"); 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("検索"); btn_view_search.clicked.connect(self.search_obs_table)
search_layout.addWidget(QLabel("表示期間 (JST):")); 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, 6)
self.tbl_obs.setHorizontalHeaderLabels(["観測日(JST)▽", "観測時間(JST)▽", "地点コード▽", "降水量(mm)▽", "降雪量(cm)▽", "積雪深(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)")
def test_sql_connection(self):
try:
conn_str = self.get_sql_connection_string(); conn = pyodbc.connect(conn_str, timeout=5); conn.close()
QMessageBox.information(self, "導通点検成功", "SQL Server への接続に成功しました！\n通信状態およびログイン認証は正常です。")
except Exception as e: QMessageBox.critical(self, "導通点検エラー", f"SQL Server への接続に失敗しました。\n設定値またはネットワークを確認してください。\n\n【詳細原因】:\n{e}")
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自動取得を開始")
else: self.t1_auto_timer.start(600000); self.lbl_t1_auto.setText("🔴 MS自動取得中"); self.btn_t1_auto.setText("MS自動取得を停止")
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([str(r[0]) for r in cursor.fetchall()])
self.update_t8_query_points()
def search_obs_table(self):
# UIから入力されるJST期間を、内部のUTC日時に変換してクエリ
utc_start, utc_end = self.jst_date_range_to_utc_str(self.v_date_from, self.v_date_to)
st = self.v_cmb_station.currentText()
cursor = self.conn.cursor()
if st == "すべて":
cursor.execute("SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (utc_start, utc_end))
else:
cursor.execute("SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE MsCd=? AND ObsDay || ' ' || ObsTime BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (st, utc_start, utc_end))
rows = cursor.fetchall(); self.tbl_obs.setRowCount(len(rows))
# 画面に表示する際はUTCからJSTに逆変換して馴染みのある時間にする
for r_i, r_data in enumerate(rows):
dt_utc = datetime.strptime(f"{r_data[0]} {r_data[1]}", "%Y-%m-%d %H:%M")
dt_jst = dt_utc + timedelta(hours=9)
self.tbl_obs.setItem(r_i, 0, QTableWidgetItem(dt_jst.strftime("%Y-%m-%d")))
self.tbl_obs.setItem(r_i, 1, QTableWidgetItem(dt_jst.strftime("%H:%M")))
self.tbl_obs.setItem(r_i, 2, QTableWidgetItem(str(r_data[2])))
self.tbl_obs.setItem(r_i, 3, QTableWidgetItem(str(r_data[3]) if r_data[3] is not None else ""))
self.tbl_obs.setItem(r_i, 4, QTableWidgetItem(str(r_data[4]) if r_data[4] is not None else ""))
self.tbl_obs.setItem(r_i, 5, QTableWidgetItem(str(r_data[5]) if r_data[5] is not None else ""))
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, SnowDepth FROM SNET.T_MS_Statistics WHERE MsCd IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 25) AND ObsDay BETWEEN '{d_from}' AND '{d_to}'"
import warnings
with warnings.catch_warnings():
warnings.simplefilter("ignore")
df = pd.read_sql(query, conn_sql)
conn_sql.close()
if df.empty: return QMessageBox.warning(self, "通知", "指定期間のデータが存在しません。")
df.loc[:, 'MsCd'] = 'MS' + df['MsCd'].astype(str)
df['datetime_str'] = df['ObsDay'].astype(str) + ' ' + df['ObsTime'].astype(str)
df['datetime_jst'] = pd.to_datetime(df['datetime_str'], errors='coerce')
df = df.dropna(subset=['datetime_jst'])
df['HourlyTime_JST'] = df['datetime_jst'].dt.ceil('1h')
df_hourly = df.groupby(['MsCd', 'HourlyTime_JST']).agg({
'RainfallP1': 'sum',
'SnowDepth': 'last'
}).reset_index()
df_hourly = df_hourly.sort_values(['MsCd', 'HourlyTime_JST'])
df_hourly['SnowfallP1'] = df_hourly.groupby('MsCd')['SnowDepth'].diff().clip(lower=0)
# ★ 修正: 保存直前にJSTからUTCへタイムスタンプを完全移行
df_hourly['HourlyTime_UTC'] = df_hourly['HourlyTime_JST'] - pd.Timedelta(hours=9)
df_hourly['ObsDay_UTC'] = df_hourly['HourlyTime_UTC'].dt.strftime('%Y-%m-%d')
df_hourly['ObsTime_UTC'] = df_hourly['HourlyTime_UTC'].dt.strftime('%H:%M')
cursor = self.conn.cursor()
inserted = 0
for _, row in df_hourly.iterrows():
sd = float(row['SnowDepth']) if not pd.isna(row['SnowDepth']) else None
sf = float(row['SnowfallP1']) if not pd.isna(row['SnowfallP1']) else 0.0
cursor.execute("INSERT OR REPLACE INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth) VALUES (?, ?, ?, ?, ?, ?)",
(str(row['ObsDay_UTC']), str(row['ObsTime_UTC']), str(row['MsCd']), float(row['RainfallP1']), sf, sd))
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"抽出が完了しました。(内部でUTCに変換され保存されました)\n集約後データ数: {inserted} 件")
except Exception as e:
if not self.t1_auto_timer.isActive(): QMessageBox.critical(self, "エラー", f"データ処理中にエラーが発生しました:\n{e}")
# ==========================================
# タブ2: アメダス抽出 (API機能統合)
# ==========================================
def setup_tab2(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_api = QGroupBox("気象庁 アメダスAPI (JSON) から自動取得 (※取得時にUTC世界標準時に自動変換して保存します)")
api_layout = QVBoxLayout(); api_layout.setContentsMargins(15, 20, 15, 15)
lbl_api = QLabel("札幌圏のアメダス（札幌、石狩、恵庭島松、小金湯、江別、手稲山口）の最新データを気象庁APIから取得し、\n内部UTC時間に完全変換した上でOBSテーブルに保存します。")
api_layout.addWidget(lbl_api)
h_api = QHBoxLayout()
btn_api_now = QPushButton("最新アメダスデータを1回取得"); btn_api_now.setObjectName("ActionBtn"); btn_api_now.clicked.connect(self.fetch_amedas_api)
self.btn_t2_auto = QPushButton("アメダスAPI自動取得開始 (10分毎)"); self.btn_t2_auto.setObjectName("AutoBtn"); self.btn_t2_auto.clicked.connect(self.toggle_t2_auto)
self.lbl_t2_auto = QLabel(""); self.lbl_t2_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
h_api.addWidget(btn_api_now); h_api.addWidget(self.btn_t2_auto); h_api.addWidget(self.lbl_t2_auto); h_api.addStretch()
api_layout.addLayout(h_api); group_api.setLayout(api_layout)
group_csv = QGroupBox("気象庁アメダス CSV取り込み (過去データ用)")
csv_layout = QHBoxLayout(); csv_layout.setContentsMargins(15, 20, 15, 15)
btn_csv = QPushButton("JMA CSVを選択してOBSへ取り込み"); btn_csv.setObjectName("TestBtn"); 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("ここに読込ログが表示されます..."); self.txt_log2.setMinimumHeight(350)
layout.addWidget(group_api); layout.addWidget(group_csv); layout.addWidget(self.txt_log2); layout.addStretch(); self.tabs.addTab(scroll, "2. アメダス観測値 (API/CSV)")
def toggle_t2_auto(self):
if self.t2_auto_timer.isActive(): self.t2_auto_timer.stop(); self.lbl_t2_auto.setText(""); self.btn_t2_auto.setText("アメダスAPI自動取得開始")
else: self.t2_auto_timer.start(600000); self.lbl_t2_auto.setText("🔴 API自動取得中"); self.btn_t2_auto.setText("アメダス自動取得停止"); self.fetch_amedas_api()
def fetch_amedas_api(self):
target_stations = {"14163": "札幌", "14111": "石狩", "14311": "恵庭島松", "14166": "小金湯", "14164": "江別", "14136": "手稲山口"}
try:
latest_time_url = "https://www.jma.go.jp/bosai/amedas/data/latest_time.txt"
res_time = requests.get(latest_time_url, timeout=5)
latest_time_iso = res_time.text.strip()
dt_jst = datetime.fromisoformat(latest_time_iso)
time_str = dt_jst.strftime("%Y%m%d%H%M00")
data_url = f"https://www.jma.go.jp/bosai/amedas/data/map/{time_str}.json"
res_data = requests.get(data_url, timeout=5)
if res_data.status_code != 200: raise Exception(f"API HTTP Status: {res_data.status_code}")
data_json = res_data.json()
# ★ 修正: 気象庁から取得したJST時間をUTC時間へ完全変換
dt_utc = dt_jst.astimezone(timezone.utc)
obs_day_utc = dt_utc.strftime("%Y-%m-%d")
obs_time_utc = dt_utc.strftime("%H:%M")
cursor = self.conn.cursor()
inserted = 0
self.txt_log2.append(f"--- API取得: {dt_jst.strftime('%m/%d %H:%M')}(JST) ➔ 内部UTC: {obs_day_utc} {obs_time_utc} 保存中 ---")
for st_code, st_name in target_stations.items():
if st_code in data_json:
st_data = data_json[st_code]
rain = st_data.get('precipitation1h', [0.0])[0]
snow_depth = st_data.get('snow', [None])[0]
ms_cd = f"アメダス:{st_name}"
snowfall = 0.0
if snow_depth is not None:
cursor.execute("SELECT SnowDepth FROM OBS WHERE MsCd=? AND SnowDepth IS NOT NULL ORDER BY ObsDay DESC, ObsTime DESC LIMIT 1", (ms_cd,))
last_row = cursor.fetchone()
if last_row and last_row[0] is not None:
snowfall = max(0.0, float(snow_depth) - float(last_row[0]))
cursor.execute("INSERT OR REPLACE INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth) VALUES (?, ?, ?, ?, ?, ?)",
(obs_day_utc, obs_time_utc, ms_cd, float(rain) if rain is not None else 0.0, snowfall, snow_depth))
inserted += 1
self.conn.commit()
self.txt_log2.append(f"✅ 完了: {inserted} 件のデータをUTC統一軸で格納しました。")
self.update_tab1_station_combo()
if not self.t2_auto_timer.isActive(): QMessageBox.information(self, "完了", f"アメダスAPI取得完了。(UTC保存)\n保存件数: {inserted} 件")
except Exception as e:
err_msg = f"API取得エラー: {e}"
self.txt_log2.append(f"❌ {err_msg}")
if not self.t2_auto_timer.isActive(): QMessageBox.critical(self, "エラー", err_msg)
def import_jma_csv(self):
pass
# ==========================================
# タブ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 フォルダ一括抽出 (※抽出時にJSTから内部UTCに完全変換されます)"); 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("発表日 (JST):")); 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 = "<b>【重要：タイムスタンプUTC統一】</b><br>Ver 18.0より、Excelファイル名にある日本時間(JST)の発表時間およびシート内の対象時間は、<b>DB書き込み時にマイナス9時間され、完全なUTC世界標準時として整理保存</b>されます。"
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) (※直近データ50件を表示・表示用にJST変換しています)"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 20, 15, 15)
self.tbl_fcst = FilterableTableWidget(0, 7)
self.tbl_fcst.setHorizontalHeaderLabels(["発表日(JST)▽", "発表時間(JST)▽", "対象日(JST)▽", "対象時間(JST)▽", "予報地点名▽", "予報降水量▽", "予報降雪量▽"])
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 = ? ORDER BY rowid", (tid,))
rows = cursor.fetchall(); self.tbl_targets.clearContents()
for i in range(20):
item = QTableWidgetItem(str(rows[i][0]) if i < len(rows) else "")
self.tbl_targets.setItem(i, 0, item)
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 == "未設定": return QMessageBox.warning(self, "警告", "まずは「手動でフォルダを指定」を実行し、フォルダを記憶させてください。")
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 = [str(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_jst = f"{d_match.group(1)[:4]}-{d_match.group(1)[4:6]}-{d_match.group(1)[6:8]}" if d_match else datetime.now(timezone.utc).strftime("%Y-%m-%d")
if not (d_from <= issue_day_jst <= d_to): continue
t_match = re.search(r'（(\d{2})時用）', filename)
issue_time_jst = f"{t_match.group(1)}:00" if t_match else "00:00"
# ★ 修正: 発表日時(JST)をオブジェクト化
issue_dt_jst = datetime.strptime(f"{issue_day_jst} {issue_time_jst}", "%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, seen_snow_stations = set(), set()
for row_idx in range(len(df)):
cat_str = str(df.iloc[row_idx, 0]).strip()
is_weather_align = "天気整合" in cat_str
if is_weather_align: current_mode = "snow"
elif "3時間降水量" in cat_str and not any(x in cat_str for x in ["最終", "(6)", "（６）"]): current_mode = "rain"
elif any(x in cat_str for x in ["風向", "気温", "湿度", "気圧", "最終", "(5)", "（５）", "(6)", "（６）", "(7)", "（７）"]):
if not is_weather_align: current_mode = None
if not current_mode: continue
rows_to_process = [row_idx]
if is_weather_align and current_mode == "snow" and row_idx > 0:
rows_to_process = [row_idx - 1, row_idx]
for r_idx in rows_to_process:
raw_station = str(df.iloc[r_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[r_idx, col_idx]) if not pd.isna(df.iloc[r_idx, col_idx]) else 0.0
except ValueError: val = 0.0
# ★ 修正: 時間計算時にJSTからUTCへと変換する
target_dt_jst = issue_dt_jst + timedelta(hours=i*3)
issue_dt_utc = issue_dt_jst - timedelta(hours=9)
target_dt_utc = target_dt_jst - timedelta(hours=9)
i_day_u, i_time_u = issue_dt_utc.strftime("%Y-%m-%d"), issue_dt_utc.strftime("%H:%M")
t_day_u, t_time_u = target_dt_utc.strftime("%Y-%m-%d"), target_dt_utc.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))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 3, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 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))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 3, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 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_jst in ["09:00", "12:00"] and j == 0: continue
try: val = float(df.iloc[r_idx, col_idx]) if not pd.isna(df.iloc[r_idx, col_idx]) else 0.0
except ValueError: val = 0.0
if issue_time_jst == "18:00": target_dt_jst = issue_dt_jst + timedelta(hours=12*(j+1))
else: target_dt_jst = datetime.strptime(f"{issue_day_jst} 18:00", "%Y-%m-%d %H:%M") + timedelta(hours=12*j)
issue_dt_utc = issue_dt_jst - timedelta(hours=9)
target_dt_utc = target_dt_jst - timedelta(hours=9)
i_day_u, i_time_u = issue_dt_utc.strftime("%Y-%m-%d"), issue_dt_utc.strftime("%H:%M")
t_day_u, t_time_u = target_dt_utc.strftime("%Y-%m-%d"), target_dt_utc.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))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 12, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 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))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 12, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 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"抽出が完了しました。(内部UTC統一格納)\n処理要素数: {total_inserted} 件")
def load_preview_data(self, table_name, widget):
cursor = self.conn.cursor()
if table_name == "FCST":
cursor.execute("SELECT IssueDay, IssueTime, TargetDay, TargetTime, MsCd, FcstRainfall, FcstSnowfall FROM FCST ORDER BY rowid DESC LIMIT 50")
else:
cursor.execute(f"SELECT * FROM {table_name} LIMIT 50")
rows = cursor.fetchall(); widget.setRowCount(len(rows)); col_count = widget.columnCount()
# プレビュー表示時にUTCをJSTに変換してあげる
for r_i, r_data in enumerate(rows):
if table_name == "FCST":
dt_iss_utc = datetime.strptime(f"{r_data[0]} {r_data[1]}", "%Y-%m-%d %H:%M")
dt_tgt_utc = datetime.strptime(f"{r_data[2]} {r_data[3]}", "%Y-%m-%d %H:%M")
dt_iss_jst = dt_iss_utc + timedelta(hours=9)
dt_tgt_jst = dt_tgt_utc + timedelta(hours=9)
widget.setItem(r_i, 0, QTableWidgetItem(dt_iss_jst.strftime("%Y-%m-%d")))
widget.setItem(r_i, 1, QTableWidgetItem(dt_iss_jst.strftime("%H:%M")))
widget.setItem(r_i, 2, QTableWidgetItem(dt_tgt_jst.strftime("%Y-%m-%d")))
widget.setItem(r_i, 3, QTableWidgetItem(dt_tgt_jst.strftime("%H:%M")))
widget.setItem(r_i, 4, QTableWidgetItem(str(r_data[4])))
widget.setItem(r_i, 5, QTableWidgetItem(str(r_data[5])))
widget.setItem(r_i, 6, QTableWidgetItem(str(r_data[6])))
else:
for c_i, c_data in enumerate(r_data):
if c_i < col_count: widget.setItem(r_i, c_i, QTableWidgetItem(str(c_data) if c_data is not None else ""))
# ==========================================
# タブ4: 気象庁予報 直打ち入力フォーマット
# ==========================================
def setup_tab4(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
lbl_desc = QLabel("<b>【気象庁予報 (降雪量) 手入力フォーマット】(※画面はJST表記、保存時に内部UTCへ自動変換)</b><br>日本時間(JST)で時間枠が表示されます。値を保存する際、システムが自動でマイナス9時間してUTC軸に位置合わせします。")
lbl_desc.setStyleSheet("color: #0366D6; font-size: 14px; padding: 10px; background-color: #F1F8FF; border-radius: 5px;")
layout.addWidget(lbl_desc)
group_input = QGroupBox("対象日の指定 ＆ 降雪量入力 (JST基準で入力)"); input_layout = QVBoxLayout(); input_layout.setContentsMargins(15, 20, 15, 15)
date_layout = QHBoxLayout()
self.jma_base_date = QDateEdit(QDate.currentDate()); self.jma_base_date.setCalendarPopup(True)
self.jma_base_date.dateChanged.connect(self.update_jma_table_dates)
date_layout.addWidget(QLabel("【対象日(夕方発表の直近の夜)】(JST):")); date_layout.addWidget(self.jma_base_date); date_layout.addStretch()
input_layout.addLayout(date_layout)
self.tbl_jma = QTableWidget(6, 5)
self.tbl_jma.setHorizontalHeaderLabels(["地域", "対象期間(JST)", "開始日時(JST)", "終了日時(JST)", "降雪量予報(cm) 手入力"])
self.tbl_jma.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_jma.setAlternatingRowColors(True)
regions = ["石狩地方北部", "石狩地方中部", "石狩地方南部"]
periods = ["18:00〜06:00 (夜間)", "06:00〜18:00 (日中)"]
for i in range(3):
for j in range(2):
row = i * 2 + j
item_reg = QTableWidgetItem(regions[i]); item_reg.setFlags(item_reg.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 0, item_reg)
item_per = QTableWidgetItem(periods[j]); item_per.setFlags(item_per.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 1, item_per)
item_start = QTableWidgetItem(""); item_start.setFlags(item_start.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 2, item_start)
item_end = QTableWidgetItem(""); item_end.setFlags(item_end.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 3, item_end)
self.tbl_jma.setItem(row, 4, QTableWidgetItem(""))
self.update_jma_table_dates()
input_layout.addWidget(self.tbl_jma)
btn_save_jma = QPushButton("JMA_FCST テーブルに保存する"); btn_save_jma.setObjectName("ActionBtn"); btn_save_jma.clicked.connect(self.save_jma_manual_data)
input_layout.addWidget(btn_save_jma); group_input.setLayout(input_layout); layout.addWidget(group_input)
group_preview = QGroupBox("保存済みデータ (JMA_FCST) (※表示用にJSTに自動逆変換して表示しています)"); prev_layout = QVBoxLayout(); prev_layout.setContentsMargins(15, 20, 15, 15)
self.tbl_jma_prev = FilterableTableWidget(0, 6)
self.tbl_jma_prev.setHorizontalHeaderLabels(["開始日時(JST)▽", "終了日時(JST)▽", "地域▽", "生テキスト(範囲)▽", "内部最小値▽", "内部最大値▽"])
self.tbl_jma_prev.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
prev_layout.addWidget(self.tbl_jma_prev); group_preview.setLayout(prev_layout); layout.addWidget(group_preview); layout.addStretch()
self.tabs.addTab(scroll, "4. 気象庁予報(手入力)")
self.refresh_jma_preview()
def update_jma_table_dates(self):
base_dt = self.jma_base_date.date().toPyDate()
for i in range(3):
r0 = i * 2; s0 = datetime(base_dt.year, base_dt.month, base_dt.day, 18, 0); e0 = s0 + timedelta(hours=12)
self.tbl_jma.item(r0, 2).setText(s0.strftime("%Y-%m-%d %H:%M")); self.tbl_jma.item(r0, 3).setText(e0.strftime("%Y-%m-%d %H:%M"))
r1 = i * 2 + 1; s1 = e0; e1 = s1 + timedelta(hours=12)
self.tbl_jma.item(r1, 2).setText(s1.strftime("%Y-%m-%d %H:%M")); self.tbl_jma.item(r1, 3).setText(e1.strftime("%Y-%m-%d %H:%M"))
def save_jma_manual_data(self):
cursor = self.conn.cursor()
saved = 0
for row in range(self.tbl_jma.rowCount()):
region = self.tbl_jma.item(row, 0).text()
start_jst_str = self.tbl_jma.item(row, 2).text()
end_jst_str = self.tbl_jma.item(row, 3).text()
val_str = self.tbl_jma.item(row, 4).text().strip()
if not val_str: continue
nums = re.findall(r'\d+', val_str)
if not nums: continue
min_v = float(nums[0]); max_v = float(nums[-1])
# ★ 修正: 保存時にJSTの文字列時間をUTC時間に完全変換
dt_s_utc = datetime.strptime(start_jst_str, "%Y-%m-%d %H:%M") - timedelta(hours=9)
dt_e_utc = datetime.strptime(end_jst_str, "%Y-%m-%d %H:%M") - timedelta(hours=9)
start_utc_str = dt_s_utc.strftime("%Y-%m-%d %H:%M")
end_utc_str = dt_e_utc.strftime("%Y-%m-%d %H:%M")
cursor.execute("INSERT OR REPLACE INTO JMA_FCST (TargetStart, TargetEnd, Region, Element, RawText, MinVal, MaxVal) VALUES (?, ?, ?, ?, ?, ?, ?)",
(start_utc_str, end_utc_str, region, "降雪量", val_str, min_v, max_v))
saved += 1
self.conn.commit()
self.refresh_jma_preview()
QMessageBox.information(self, "保存完了", f"{saved}件の気象庁手入力データを保存しました。(内部UTC変換)")
def refresh_jma_preview(self):
cursor = self.conn.cursor()
cursor.execute("SELECT TargetStart, TargetEnd, Region, RawText, MinVal, MaxVal FROM JMA_FCST ORDER BY TargetStart DESC LIMIT 50")
rows = cursor.fetchall(); self.tbl_jma_prev.setRowCount(len(rows))
# 表示する時にUTCからJSTへ変換
for r_i, r_data in enumerate(rows):
dt_s_utc = datetime.strptime(r_data[0], "%Y-%m-%d %H:%M")
dt_e_utc = datetime.strptime(r_data[1], "%Y-%m-%d %H:%M")
dt_s_jst = dt_s_utc + timedelta(hours=9)
dt_e_jst = dt_e_utc + timedelta(hours=9)
self.tbl_jma_prev.setItem(r_i, 0, QTableWidgetItem(dt_s_jst.strftime("%Y-%m-%d %H:%M")))
self.tbl_jma_prev.setItem(r_i, 1, QTableWidgetItem(dt_e_jst.strftime("%Y-%m-%d %H:%M")))
self.tbl_jma_prev.setItem(r_i, 2, QTableWidgetItem(str(r_data[2])))
self.tbl_jma_prev.setItem(r_i, 3, QTableWidgetItem(str(r_data[3])))
self.tbl_jma_prev.setItem(r_i, 4, QTableWidgetItem(str(r_data[4])))
self.tbl_jma_prev.setItem(r_i, 5, QTableWidgetItem(str(r_data[5])))
# ==========================================
# タブ5: 精度評価・マッピング
# ==========================================
def setup_tab5(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_mapping = QGroupBox("地点マッピング設定 (観測地点 ⇔ 予報地点/PDF推定地域)")
mapping_layout = QVBoxLayout(); mapping_layout.setContentsMargins(15, 20, 15, 10)
map_header = QHBoxLayout()
self.cmb_map_template = QComboBox()
self.cmb_map_template.addItems([f"テンプレート{i}" for i in range(1, 6)])
self.cmb_map_template.currentIndexChanged.connect(self.load_mapping_data)
map_header.addWidget(QLabel("選択中:")); map_header.addWidget(self.cmb_map_template); map_header.addStretch()
mapping_layout.addLayout(map_header)
tbl_and_btn = QHBoxLayout()
self.tbl_mapping = FilterableTableWidget(0, 3)
self.tbl_mapping.setHorizontalHeaderLabels(["観測地点 (OBS)▽", "連携▽", "予報地点名/地域 (FCST/JMA)▽"])
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(250)
map_btn_layout = QVBoxLayout()
lbl_help = QLabel("※ 気象庁予報の検証時は、ここの「予報地点名」を\nタブ4の地域に書き換えてください。")
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()
tbl_and_btn.addWidget(self.tbl_mapping); tbl_and_btn.addLayout(map_btn_layout)
mapping_layout.addLayout(tbl_and_btn); group_mapping.setLayout(mapping_layout)
group_filter = QGroupBox("検証条件の指定 (※日付入力はJSTベースで行い、自動変換してUTC完全同期抽出されます)")
filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 15, 15, 15)
row_src = QHBoxLayout()
self.cmb_source = QComboBox(); self.cmb_source.addItems(["最適モデルExcel (FCST)", "気象庁手入力フォーマット (JMA_FCST)"])
self.cmb_source.setStyleSheet("background-color: #FFF3E0; font-weight:bold; border: 2px solid #FF8C00; font-size: 14px;")
row_src.addWidget(QLabel("【評価対象データ】:")); row_src.addWidget(self.cmb_source); row_src.addStretch()
filter_layout_main.addLayout(row_src); filter_layout_main.addSpacing(10)
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("【発表日】(JST/Excel用):")); row1.addWidget(self.issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.issue_to)
row1.addSpacing(20)
row1.addWidget(QLabel("【対象日】(JST):")); row1.addWidget(self.target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.target_to); row1.addStretch()
row2 = QHBoxLayout()
self.cmb_station = QComboBox(); self.update_tab5_station_combo()
self.cmb_element = QComboBox(); self.cmb_element.addItems(["降水量", "降雪量"])
self.cmb_element.currentTextChanged.connect(self.update_custom_labels_visibility)
self.cmb_step = QComboBox(); self.cmb_step.addItems(["3時間ステップ", "12時間ステップ", "1時間ステップ"]); 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("時間(Excel用):")); row2.addWidget(self.cmb_step)
row2.addWidget(QLabel("発出(Excel用):")); 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()
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)
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)
group_custom = QGroupBox("プロ仕様 独自評価指標"); custom_layout = QGridLayout(); custom_layout.setContentsMargins(15, 10, 15, 10)
self.custom_labels = []
for i in range(4):
l_title = QLabel(f"【閾値{i+1}】: --"); l_title.setStyleSheet("font-weight: bold; color: #D73A49;")
l_stats = QLabel("全数: -- / 的中(A): -- / 発現: -- / 予報: --")
l_rates = QLabel("的: -- / 捕: -- / 空: -- / TS: --")
self.custom_labels.append({"title": l_title, "stats": l_stats, "rates": l_rates})
custom_layout.addWidget(l_title, i, 0); custom_layout.addWidget(l_stats, i, 1); custom_layout.addWidget(l_rates, i, 2)
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(["観測地点▽", "発表日時(JST)▽", "対象日時(JST)▽", "予報値▽", "実況値(加算済)▽", "差分▽", "判定▽"])
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, "5. 精度評価・マッピング")
self.update_custom_labels_visibility()
self.load_mapping_data()
def update_custom_labels_visibility(self):
is_snow = self.cmb_element.currentText() == "降雪量"
self.custom_labels[3]["title"].setVisible(is_snow)
self.custom_labels[3]["stats"].setVisible(is_snow)
self.custom_labels[3]["rates"].setVisible(is_snow)
def load_mapping_data(self):
tid = self.cmb_map_template.currentIndex() + 1
cursor = self.conn.cursor()
cursor.execute("SELECT ObsCd, FcstName FROM Mapping WHERE TemplateId = ? ORDER BY ObsCd", (tid,))
rows = cursor.fetchall()
self.tbl_mapping.setRowCount(max(len(rows), 20))
for i in range(self.tbl_mapping.rowCount()):
obs_val = str(rows[i][0]) if i < len(rows) else ""
fcst_val = str(rows[i][1]) if i < len(rows) else ""
item_obs = QTableWidgetItem(obs_val)
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(fcst_val))
def save_mapping_data(self):
tid = self.cmb_map_template.currentIndex() + 1
cursor = self.conn.cursor()
cursor.execute("DELETE FROM Mapping WHERE TemplateId = ?", (tid,))
for i in range(self.tbl_mapping.rowCount()):
obs_item = self.tbl_mapping.item(i, 0)
fcst_item = self.tbl_mapping.item(i, 2)
if obs_item and obs_item.text().strip():
cursor.execute("INSERT INTO Mapping (TemplateId, ObsCd, FcstName) VALUES (?, ?, ?)",
(tid, obs_item.text().strip(), fcst_item.text().strip() if fcst_item else ""))
self.conn.commit()
self.update_tab5_station_combo()
QMessageBox.information(self, "完了", f"テンプレート{tid} のマッピングを保存しました。")
def update_tab5_station_combo(self):
self.cmb_station.clear(); self.cmb_station.addItem("全地点総合評価")
cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT ObsCd FROM Mapping"); self.cmb_station.addItems([str(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() == "降水量"
src_jma = "JMA_FCST" in self.cmb_source.currentText()
# ★ 修正: UI上のJST日時指定を、裏側のUTC検索範囲文字列へ変換
utc_tgt_start, utc_tgt_end = self.jst_date_range_to_utc_str(self.target_from, self.target_to)
utc_iss_start, utc_iss_end = self.jst_date_range_to_utc_str(self.issue_from, self.issue_to)
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 WHERE TemplateId = ?", (self.cmb_map_template.currentIndex()+1,))
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ? AND TemplateId = ?", (obs_cd_sel, self.cmb_map_template.currentIndex()+1))
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:
if src_jma:
# ★ 修正: UTCで範囲比較を行うクエリ
query = "SELECT ? AS Station, '-' AS IssueDT, TargetStart || '～' || TargetEnd AS TargetDT, RawText AS FcstText, MinVal, MaxVal, TargetStart, TargetEnd FROM JMA_FCST WHERE Region = ? AND Element = ? AND TargetStart BETWEEN ? AND ?"
df_fcst = pd.read_sql_query(query, self.conn, params=(obs_cd, fcst_name, self.cmb_element.currentText(), utc_tgt_start, utc_tgt_end))
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE MsCd = ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd,))
if df_obs.empty or df_fcst.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
merged_rows = []
for _, row in df_fcst.iterrows():
ts_start = pd.to_datetime(row['TargetStart']); ts_end = pd.to_datetime(row['TargetEnd'])
mask = (df_obs['ObsDT'] > ts_start) & (df_obs['ObsDT'] <= ts_end)
if is_rain:
obs_val = df_obs.loc[mask, 'RainfallP1'].sum()
else:
if df_obs['SnowDepth'].notna().any():
sd_start = df_obs.loc[df_obs['ObsDT'] == ts_start, 'SnowDepth'].last_valid_index()
sd_end = df_obs.loc[df_obs['ObsDT'] == ts_end, 'SnowDepth'].last_valid_index()
start_val = df_obs.loc[sd_start, 'SnowDepth'] if sd_start is not None else 0
end_val = df_obs.loc[sd_end, 'SnowDepth'] if sd_end is not None else 0
obs_val = max(end_val - start_val, 0)
else:
obs_val = df_obs.loc[mask, 'SnowfallP1'].sum()
is_hit = row['MinVal'] <= obs_val <= row['MaxVal']
diff = 0.0
if obs_val < row['MinVal']: diff = obs_val - row['MinVal']
elif obs_val > row['MaxVal']: diff = obs_val - row['MaxVal']
merged_rows.append({
'Station': row['Station'], 'IssueDT': row['IssueDT'], 'TargetDT': row['TargetDT'],
'Fcst': row['MaxVal'], 'FcstText': row['FcstText'], 'Obs': obs_val, 'Diff': diff,
'MinVal': row['MinVal'], 'MaxVal': row['MaxVal'], 'IsRangeHit': is_hit
})
if merged_rows: all_dfs.append(pd.DataFrame(merged_rows))
else:
# ★ 修正: UTCで完全結合検索
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 || ' ' || IssueTime BETWEEN ? AND ? AND TargetDay || ' ' || TargetTime BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, utc_iss_start, utc_iss_end, utc_tgt_start, utc_tgt_end, step_val))
obs_start_dt = datetime.strptime(utc_tgt_start.split()[0], "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, RainfallP1, SnowfallP1, SnowDepth 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_agg = df_obs.resample('1h').agg({'RainfallP1': 'sum', 'SnowfallP1': 'sum', 'SnowDepth': 'last'})
df_obs_agg['SnowDepth'] = df_obs_agg['SnowDepth'].ffill()
if is_rain:
if step_val > 1: df_obs_agg['Obs'] = df_obs_agg['RainfallP1'].rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg['Obs'] = df_obs_agg['RainfallP1']
else:
if df_obs_agg['SnowDepth'].notna().any():
df_obs_agg['Obs'] = df_obs_agg['SnowDepth'].diff(periods=step_val).clip(lower=0)
else:
df_obs_agg['Obs'] = df_obs_agg['SnowfallP1'].rolling(window=step_val, min_periods=1).sum() if step_val > 1 else df_obs_agg['SnowfallP1']
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')
if lead_time_txt != "期間制限なし":
max_hours = int(lead_time_txt.replace("時間以内", "")); df_merged['LeadTime'] = (pd.to_datetime(df_merged['TargetDT']) - pd.to_datetime(df_merged['IssueDT'])).dt.total_seconds() / 3600
df_merged = df_merged[(df_merged['LeadTime'] >= 0) & (df_merged['LeadTime'] <= max_hours)]
df_merged['Diff'] = df_merged['Fcst'] - df_merged['Obs']
df_merged['FcstText'] = df_merged['Fcst'].apply(lambda x: f"{x:.1f}")
all_dfs.append(df_merged)
if not all_dfs:
self.tbl_detail.setRowCount(0)
QMessageBox.warning(self, "データ不足", "条件に合致するデータがありませんでした。")
return
df = pd.concat(all_dfs, ignore_index=True)
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}")
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
if src_jma:
A = len(df[(df['MaxVal'] > 0) & (df['Obs'] > 0) & df['IsRangeHit']]); B = len(df[(df['MaxVal'] > 0) & (~df['IsRangeHit']) & (df['Diff'] < 0)])
C = len(df[(df['MaxVal'] > 0) & (~df['IsRangeHit']) & (df['Diff'] > 0)]); D = len(df[(df['MaxVal'] == 0) & (df['Obs'] == 0) & df['IsRangeHit']])
hit_rate = len(df[df['IsRangeHit']]) / len(df) * 100 if len(df) > 0 else 0
self.lbl_hit.setText(f"完全範囲的中率: {hit_rate:.1f} %"); self.lbl_pod.setText("捕捉率: (範囲判定)"); self.lbl_ts.setText(f"全体TS(概算): {hit_rate:.1f} %")
self.lbl_far.setText("空振り率: --"); self.lbl_miss.setText("見逃し率: --")
else:
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} %")
unit = "mm" if is_rain else "cm"
thresholds = [1.0, 10.0, 20.0] if is_rain else [1.0, 10.0, 20.0, 30.0]
for i, T in enumerate(thresholds):
self.custom_labels[i]["title"].setText(f"【閾値 >= {T} {unit}】")
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df[(df[fcst_col] >= T) & (df['Obs'] >= T)]); B_t = len(df[(df[fcst_col] >= T) & (df['Obs'] < T)])
C_t = len(df[(df[fcst_col] < T) & (df['Obs'] >= T)]); D_t = len(df[(df[fcst_col] < T) & (df['Obs'] < T)])
tot_t, hit_t, pod_t, far_t, miss_t, ts_t = calc_metrics(A_t, B_t, C_t, D_t)
self.custom_labels[i]["stats"].setText(f"全数: {tot_t} / 的中(A): {A_t} / 発現: {A_t+C_t} / 予報: {A_t+B_t}")
self.custom_labels[i]["rates"].setText(f"的: {hit_t:.1f}% / 捕: {pod_t:.1f}% / 空: {far_t:.1f}% / TS: {ts_t:.1f}%")
self.tbl_detail.setRowCount(len(df))
for i, r in df.reset_index().iterrows():
# ★ 修正: 表示の際はUTCからJSTへ変換
iss_jst, tgt_jst = "-", "-"
if str(r['IssueDT']) != "-":
iss_jst = (datetime.strptime(str(r['IssueDT']), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
if "～" in str(r['TargetDT']):
t_parts = str(r['TargetDT']).split("～")
t_s = (datetime.strptime(t_parts[0], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%m/%d %H:%M")
t_e = (datetime.strptime(t_parts[1], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%m/%d %H:%M")
tgt_jst = f"{t_s}～{t_e}"
else:
tgt_jst = (datetime.strptime(str(r['TargetDT']), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
self.tbl_detail.setItem(i, 0, QTableWidgetItem(str(r['Station'])))
self.tbl_detail.setItem(i, 1, QTableWidgetItem(iss_jst))
self.tbl_detail.setItem(i, 2, QTableWidgetItem(tgt_jst))
self.tbl_detail.setItem(i, 3, QTableWidgetItem(str(r['FcstText'])))
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)
judge_text = "範囲的中" if src_jma and r.get('IsRangeHit') else ("的中" if not src_jma and ((r['Fcst']>0 and r['Obs']>0) or (r['Fcst']==0 and r['Obs']==0)) else "外れ")
self.tbl_detail.setItem(i, 6, QTableWidgetItem(judge_text))
except Exception as e: QMessageBox.critical(self, "エラー", f"計算処理中にエラーが発生しました:\n{e}")
finally: self.btn_search.setText("精度評価を実行"); self.btn_search.setEnabled(True)
# ==========================================
# タブ6: 検証Excel出力
# ==========================================
def setup_tab6(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_filter = QGroupBox("プロ仕様・検証レポート出力 (※日付はJST入力、出力されるレポートの表はJST軸に展開されます)"); filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 20, 15, 15)
row_sync = QHBoxLayout()
btn_sync = QPushButton("🔄 タブ5の条件をここにコピー"); btn_sync.clicked.connect(self.sync_from_tab5)
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("【発表日】(JST/Excel用):")); row1.addWidget(self.ex_issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_issue_to)
row1.addSpacing(30)
row1.addWidget(QLabel("【対象日】(JST):")); row1.addWidget(self.ex_target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_target_to); row1.addStretch()
row2 = QHBoxLayout()
self.ex_cmb_source = QComboBox(); self.ex_cmb_source.addItems(["最適モデルExcel (FCST)", "気象庁手入力フォーマット (JMA_FCST)"])
self.ex_cmb_station = QComboBox(); cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT ObsCd FROM Mapping")
self.ex_cmb_station.addItem("全地点及び全地点総合評価"); self.ex_cmb_station.addItems([str(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_source)
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("指定期間の精度を網羅して出力します。(内部UTCで計算し、ExcelシートにはわかりやすいJST時間軸で出力されます)")); 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, "6. 検証Excel出力")
def sync_from_tab5(self):
self.ex_cmb_source.setCurrentText(self.cmb_source.currentText())
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())
if self.cmb_station.currentText() == "全地点総合評価":
self.ex_cmb_station.setCurrentText("全地点及び全地点総合評価")
else:
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(timezone.utc).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() == "降水量"
src_jma = "JMA" in self.ex_cmb_source.currentText()
# ★ 修正: UI期間(JST)を裏側のUTC検索用文字列へ変換
utc_i_from, utc_i_to = self.jst_date_range_to_utc_str(self.ex_issue_from, self.ex_issue_to)
utc_t_from, utc_t_to = self.jst_date_range_to_utc_str(self.ex_target_from, self.ex_target_to)
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 WHERE TemplateId = ?", (self.cmb_map_template.currentIndex()+1,))
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ? AND TemplateId = ?", (obs_cd_sel, self.cmb_map_template.currentIndex()+1))
row = cursor.fetchone()
if not row: return QMessageBox.warning(self, "エラー", "マッピングが見つかりません。")
mappings = [(obs_cd_sel, row[0])]
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
all_eval_dfs = []
wrote_any = False
with pd.ExcelWriter(path, engine='openpyxl') as writer:
for obs_cd, fcst_name in mappings:
if src_jma:
query = "SELECT ? AS Station, '-' AS IssueDT, TargetStart || '～' || TargetEnd AS TargetDT, RawText AS FcstText, MinVal, MaxVal, TargetStart, TargetEnd FROM JMA_FCST WHERE Region = ? AND Element = ? AND TargetStart BETWEEN ? AND ?"
df_fcst = pd.read_sql_query(query, self.conn, params=(obs_cd, fcst_name, self.ex_cmb_element.currentText(), utc_t_from, utc_t_to))
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE MsCd = ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd,))
if df_obs.empty or df_fcst.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
merged_rows = []
for _, row in df_fcst.iterrows():
ts_start = pd.to_datetime(row['TargetStart']); ts_end = pd.to_datetime(row['TargetEnd'])
mask = (df_obs['ObsDT'] > ts_start) & (df_obs['ObsDT'] <= ts_end)
if is_rain:
obs_val = df_obs.loc[mask, 'RainfallP1'].sum()
else:
if df_obs['SnowDepth'].notna().any():
sd_start = df_obs.loc[df_obs['ObsDT'] == ts_start, 'SnowDepth'].last_valid_index()
sd_end = df_obs.loc[df_obs['ObsDT'] == ts_end, 'SnowDepth'].last_valid_index()
start_val = df_obs.loc[sd_start, 'SnowDepth'] if sd_start is not None else 0
end_val = df_obs.loc[sd_end, 'SnowDepth'] if sd_end is not None else 0
obs_val = max(end_val - start_val, 0)
else:
obs_val = df_obs.loc[mask, 'SnowfallP1'].sum()
is_hit = row['MinVal'] <= obs_val <= row['MaxVal']
diff = 0.0
if obs_val < row['MinVal']: diff = obs_val - row['MinVal']
elif obs_val > row['MaxVal']: diff = obs_val - row['MaxVal']
# ★ 修正: Excel出力用データセットを構築する際、日時キーをJST文字列へ逆変換
iss_jst = "-"
if "～" in row['TargetDT']:
p = row['TargetDT'].split("～")
s_j = (datetime.strptime(p[0], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
e_j = (datetime.strptime(p[1], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
tgt_jst = f"{s_j}～{e_j}"
else:
tgt_jst = (datetime.strptime(row['TargetDT'], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
merged_rows.append({'Station': row['Station'], 'IssueDT': iss_jst, 'TargetDT': tgt_jst, 'Fcst': row['MaxVal'], 'FcstText': row['FcstText'], 'Obs': obs_val, 'Diff(Fcst-Obs)': diff, 'IsRangeHit': is_hit, 'MaxVal': row['MaxVal']})
df = pd.DataFrame(merged_rows) if merged_rows else pd.DataFrame()
else:
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 || ' ' || IssueTime BETWEEN ? AND ? AND TargetDay || ' ' || TargetTime BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, utc_i_from, utc_i_to, utc_t_from, utc_t_to, step_val))
obs_start_dt = datetime.strptime(utc_t_from.split()[0], "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, RainfallP1, SnowfallP1, SnowDepth 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_agg = df_obs.resample('1h').agg({'RainfallP1': 'sum', 'SnowfallP1': 'sum', 'SnowDepth': 'last'})
df_obs_agg['SnowDepth'] = df_obs_agg['SnowDepth'].ffill()
if is_rain:
if step_val > 1: df_obs_agg['Obs'] = df_obs_agg['RainfallP1'].rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg['Obs'] = df_obs_agg['RainfallP1']
else:
if df_obs_agg['SnowDepth'].notna().any(): df_obs_agg['Obs'] = df_obs_agg['SnowDepth'].diff(periods=step_val).clip(lower=0)
else: df_obs_agg['Obs'] = df_obs_agg['SnowfallP1'].rolling(window=step_val, min_periods=1).sum() if step_val > 1 else df_obs_agg['SnowfallP1']
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']
df['FcstText'] = df['Fcst'].apply(lambda x: f"{x:.1f}")
# ★ 修正: Excel出力用に、日時情報をJSTに一括置換
df['IssueDT'] = df['IssueDT'].apply(lambda x: (datetime.strptime(x, "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M"))
df['TargetDT'] = df['TargetDT'].apply(lambda x: (datetime.strptime(x, "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M"))
if df.empty: continue
all_eval_dfs.append(df)
rmse = np.sqrt((df['Diff(Fcst-Obs)']**2).mean())
me = df['Diff(Fcst-Obs)'].mean(); mae = df['Diff(Fcst-Obs)'].abs().mean()
if src_jma:
hit_rate = len(df[df['IsRangeHit']]) / len(df) * 100 if len(df) > 0 else 0
tot0, hit0, pod0, far0, miss0, ts0 = len(df), hit_rate, 0, 0, 0, hit_rate
else:
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)
unit = "mm" if is_rain else "cm"
thresholds = [1.0, 10.0, 20.0] if is_rain else [1.0, 10.0, 20.0, 30.0]
t_stats = []
for T in thresholds:
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df[(df[fcst_col] >= T) & (df['Obs'] >= T)]); B_t = len(df[(df[fcst_col] >= T) & (df['Obs'] < T)])
C_t = len(df[(df[fcst_col] < T) & (df['Obs'] >= T)]); D_t = len(df[(df[fcst_col] < T) & (df['Obs'] < T)])
t_stats.append((calc_m(A_t, B_t, C_t, D_t), A_t, A_t+C_t, A_t+B_t))
pivot_fcst = df.pivot_table(index='IssueDT', columns='TargetDT', values='FcstText', aggfunc=lambda x: ' '.join(x))
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)
sheet_name = obs_cd.replace(":", "_")[:31]
start_row_matrix = 26 + len(thresholds) * 2
out_df.to_excel(writer, sheet_name=sheet_name, startrow=start_row_matrix+1, index=False, header=False)
ws = writer.sheets[sheet_name]
ws.cell(row=1, column=1, value="【検証パラメータ】(※出力時間軸: JST基準)")
ws.cell(row=2, column=1, value=f"観測地点: {obs_cd}"); ws.cell(row=2, column=2, value=f"要素: {self.ex_cmb_element.currentText()} ({'JMA手入力' if src_jma else 'FCST'})")
ws.cell(row=3, column=1, value=f"時間ステップ: {step_val}時間"); ws.cell(row=3, column=2, value=f"発出制限: {lead_time_txt}")
ws.cell(row=4, column=1, value=f"発表日期間(JST): {self.ex_issue_from.date().toString('yyyy-MM-dd')} ～ {self.ex_issue_to.date().toString('yyyy-MM-dd')}")
ws.cell(row=5, column=1, value=f"対象日期間(JST): {self.ex_target_from.date().toString('yyyy-MM-dd')} ～ {self.ex_target_to.date().toString('yyyy-MM-dd')}")
ws.cell(row=7, column=1, value="【全体精度】")
ws.cell(row=8, column=1, value=f"RMSE: {rmse:.2f}"); ws.cell(row=8, column=2, value=f"ME: {me:.2f}"); ws.cell(row=8, column=3, value=f"MAE: {mae:.2f}")
ws.cell(row=9, column=1, value=f"全数: {tot0}"); ws.cell(row=9, column=2, value=f"的中回数(A): {int(tot0 * hit0 / 100) if src_jma else A0}")
ws.cell(row=10, column=1, value=f"的中率: {hit0:.1f}%"); ws.cell(row=10, column=2, value=f"捕捉率: {pod0:.1f}%"); ws.cell(row=10, column=3, value=f"空振り率: {far0:.1f}%"); ws.cell(row=10, column=4, value=f"見逃し率: {miss0:.1f}%"); ws.cell(row=10, column=5, value=f"TS: {ts0:.1f}%")
start_row = 12
for i, (T, stats_data) in enumerate(zip(thresholds, t_stats)):
s_mets, A_val, occur_val, fcst_val = stats_data
ws.cell(row=start_row, column=1, value=f"【独自指標: 閾値 >= {T} {unit}】")
ws.cell(row=start_row+1, column=1, value=f"全数: {s_mets[0]}"); ws.cell(row=start_row+1, column=2, value=f"的中(A): {A_val}"); ws.cell(row=start_row+1, column=3, value=f"発現回数: {occur_val}"); ws.cell(row=start_row+1, column=4, value=f"予報回数: {fcst_val}")
ws.cell(row=start_row+2, column=1, value=f"的中率: {s_mets[1]:.1f}%"); ws.cell(row=start_row+2, column=2, value=f"捕捉率: {s_mets[2]:.1f}%"); ws.cell(row=start_row+2, column=3, value=f"空振り率: {s_mets[3]:.1f}%"); ws.cell(row=start_row+2, column=4, value=f"見逃し率: {s_mets[4]:.1f}%"); ws.cell(row=start_row+2, column=5, value=f"TS: {s_mets[5]:.1f}%")
start_row += 4
ws.cell(row=start_row_matrix, column=1, value="▼ 詳細マトリクスデータ (※時間軸: JST)")
ws.cell(row=start_row_matrix+1, column=1, value="発表日時(JST)")
for col_i, t in enumerate(targets):
dt_obj = pd.to_datetime(t)
ws.cell(row=start_row_matrix, column=col_i+2, value=dt_obj.strftime('%Y-%m-%d'))
ws.cell(row=start_row_matrix+1, column=col_i+2, value=dt_obj.strftime('%H:%M'))
wrote_any = True
if obs_cd_sel == "全地点及び全地点総合評価" and all_eval_dfs:
df_all = pd.concat(all_eval_dfs, ignore_index=True)
sheet_name = "総合評価"
df_all.to_excel(writer, sheet_name=sheet_name)
ws_all = writer.sheets[sheet_name]
for row in ws_all['A1:Z100']:
for cell in row: cell.value = None
rmse_all = np.sqrt((df_all['Diff(Fcst-Obs)']**2).mean())
me_all = df_all['Diff(Fcst-Obs)'].mean(); mae_all = df_all['Diff(Fcst-Obs)'].abs().mean()
if src_jma:
A0_all = len(df_all[(df_all['MaxVal'] > 0) & (df_all['Obs'] > 0) & df_all['IsRangeHit']])
hit_rate_all = len(df_all[df_all['IsRangeHit']]) / len(df_all) * 100 if len(df_all) > 0 else 0
tot0_all, hit0_all, pod0_all, far0_all, miss0_all, ts0_all = len(df_all), hit_rate_all, 0, 0, 0, hit_rate_all
else:
A0_all = len(df_all[(df_all['Fcst'] > 0) & (df_all['Obs'] > 0)]); B0_all = len(df_all[(df_all['Fcst'] > 0) & (df_all['Obs'] == 0)])
C0_all = len(df_all[(df_all['Fcst'] == 0) & (df_all['Obs'] > 0)]); D0_all = len(df_all[(df_all['Fcst'] == 0) & (df_all['Obs'] == 0)])
tot0_all, hit0_all, pod0_all, far0_all, miss0_all, ts0_all = calc_m(A0_all, B0_all, C0_all, D0_all)
ws_all.cell(row=1, column=1, value="【全地点総合評価 パラメータ】(※JST出力)")
ws_all.cell(row=2, column=1, value=f"要素: {self.ex_cmb_element.currentText()}"); ws_all.cell(row=2, column=2, value=f"ステップ: {step_val}時間")
ws_all.cell(row=3, column=1, value=f"発表日期間(JST): {self.ex_issue_from.date().toString('yyyy-MM-dd')} ～ {self.ex_issue_to.date().toString('yyyy-MM-dd')}")
ws_all.cell(row=4, column=1, value=f"対象日期間(JST): {self.ex_target_from.date().toString('yyyy-MM-dd')} ～ {self.ex_target_to.date().toString('yyyy-MM-dd')}")
ws_all.cell(row=6, column=1, value="【全体精度】")
ws_all.cell(row=7, column=1, value=f"RMSE: {rmse_all:.2f}"); ws_all.cell(row=7, column=2, value=f"ME: {me_all:.2f}"); ws_all.cell(row=7, column=3, value=f"MAE: {mae_all:.2f}")
ws_all.cell(row=8, column=1, value=f"全数: {tot0_all}"); ws_all.cell(row=8, column=2, value=f"的中回数(A): {A0_all}")
ws_all.cell(row=9, column=1, value=f"的中率: {hit0_all:.1f}%"); ws_all.cell(row=9, column=2, value=f"捕捉率: {pod0_all:.1f}%"); ws_all.cell(row=9, column=3, value=f"空振り率: {far0_all:.1f}%"); ws_all.cell(row=9, column=4, value=f"見逃し率: {miss0_all:.1f}%"); ws_all.cell(row=9, column=5, value=f"TS: {ts0_all:.1f}%")
start_row_all = 11
for T in thresholds:
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df_all[(df_all[fcst_col] >= T) & (df_all['Obs'] >= T)]); B_t = len(df_all[(df_all[fcst_col] >= T) & (df_all['Obs'] < T)])
C_t = len(df_all[(df_all[fcst_col] < T) & (df_all['Obs'] >= T)]); D_t = len(df_all[(df_all[fcst_col] < T) & (df_all['Obs'] < T)])
tot_t, hit_t, pod_t, far_t, miss_t, ts_t = calc_m(A_t, B_t, C_t, D_t)
ws_all.cell(row=start_row_all, column=1, value=f"【独自指標: 閾値 >= {T} {unit}】")
ws_all.cell(row=start_row_all+1, column=1, value=f"全数: {tot_t}"); ws_all.cell(row=start_row_all+1, column=2, value=f"的中(A): {A_t}"); ws_all.cell(row=start_row_all+1, column=3, value=f"発現回数: {A_t+C_t}"); ws_all.cell(row=start_row_all+1, column=4, value=f"予報回数: {A_t+B_t}")
ws_all.cell(row=start_row_all+2, column=1, value=f"的中率: {hit_t:.1f}%"); ws_all.cell(row=start_row_all+2, column=2, value=f"捕捉率: {pod_t:.1f}%"); ws_all.cell(row=start_row_all+2, column=3, value=f"空振り率: {far_t:.1f}%"); ws_all.cell(row=start_row_all+2, column=4, value=f"見逃し率: {miss_t:.1f}%"); ws_all.cell(row=start_row_all+2, column=5, value=f"TS: {ts_t:.1f}%")
start_row_all += 4
if wrote_any: QMessageBox.information(self, "完了", f"レポートを出力しました。(内部UTC計算・JST軸出力)\n保存先: {path}")
else: QMessageBox.warning(self, "データなし", "出力可能なデータがありませんでした。")
except Exception as e: QMessageBox.critical(self, "エラー", f"出力失敗:\n{e}")
# ==========================================
# タブ7: データベース管理
# ==========================================
def setup_tab7(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_backup = QGroupBox("データベースのバックアップ"); b_layout = QVBoxLayout(); b_layout.setContentsMargins(15, 20, 15, 15)
h1 = QHBoxLayout()
self.lbl_backup_folder = QLabel("📁 バックアップ先: 未設定"); self.lbl_backup_folder.setStyleSheet("font-weight: bold; color: #0366D6;")
btn_sel_b = QPushButton("保存先フォルダを選択"); btn_sel_b.clicked.connect(self.select_backup_folder)
h1.addWidget(self.lbl_backup_folder); h1.addWidget(btn_sel_b); h1.addStretch()
b_layout.addLayout(h1)
h2 = QHBoxLayout()
btn_manual_b = QPushButton("今すぐバックアップを作成"); btn_manual_b.setObjectName("ActionBtn"); btn_manual_b.clicked.connect(self.manual_backup)
self.lbl_auto_b = QLabel("自動バックアップ: 停止中"); self.lbl_auto_b.setStyleSheet("color: #D73A49; font-weight: bold;")
self.btn_auto_b = QPushButton("12時間毎の自動バックアップを開始"); self.btn_auto_b.setObjectName("AutoBtn"); self.btn_auto_b.clicked.connect(self.toggle_auto_backup)
h2.addWidget(btn_manual_b); h2.addWidget(self.btn_auto_b); h2.addWidget(self.lbl_auto_b); h2.addStretch()
b_layout.addLayout(h2)
group_backup.setLayout(b_layout)
group_restore = QGroupBox("データの復元 (統合)"); r_layout = QVBoxLayout(); r_layout.setContentsMargins(15, 20, 15, 15)
lbl_r = QLabel("過去のバックアップや別のPCのデータベースファイルを選択し、現在のDBにデータを結合（復元）します。\n※重複するデータは現在のものが優先され、不足しているデータのみが安全に追記されます。")
btn_restore = QPushButton("バックアップDBを選択して復元"); btn_restore.setObjectName("TestBtn"); btn_restore.clicked.connect(self.restore_database)
r_layout.addWidget(lbl_r); r_layout.addWidget(btn_restore); r_layout.addStretch()
group_restore.setLayout(r_layout)
layout.addWidget(group_backup); layout.addWidget(group_restore); layout.addStretch()
self.tabs.addTab(scroll, "7. データベース管理")
self.auto_backup_timer = QTimer(self)
self.auto_backup_timer.timeout.connect(self.manual_backup)
self.backup_folder = ""
def select_backup_folder(self):
folder = QFileDialog.getExistingDirectory(self, "バックアップ先フォルダを選択")
if folder:
self.backup_folder = folder
self.lbl_backup_folder.setText(f"📁 バックアップ先: {folder}")
def manual_backup(self):
if not self.backup_folder:
QMessageBox.warning(self, "警告", "バックアップ先のフォルダを設定してください。")
return
now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
dest = os.path.join(self.backup_folder, f"weather_db_backup_{now_str}.db")
try:
self.conn.commit()
shutil.copy2(self.db_path, dest)
if not self.auto_backup_timer.isActive():
QMessageBox.information(self, "完了", f"データベースのバックアップを作成しました。\n\n保存先:\n{dest}")
except Exception as e:
QMessageBox.critical(self, "エラー", f"バックアップに失敗しました:\n{e}")
def toggle_auto_backup(self):
if self.auto_backup_timer.isActive():
self.auto_backup_timer.stop()
self.btn_auto_b.setText("12時間毎の自動バックアップを開始")
self.lbl_auto_b.setText("自動バックアップ: 停止中")
else:
if not self.backup_folder:
QMessageBox.warning(self, "警告", "バックアップ先のフォルダを設定してください。")
return
self.auto_backup_timer.start(12 * 60 * 60 * 1000)
self.btn_auto_b.setText("自動バックアップを停止")
self.lbl_auto_b.setText("🔴 自動バックアップ: 稼働中 (12時間毎)")
self.manual_backup()
def restore_database(self):
path, _ = QFileDialog.getOpenFileName(self, "復元元のDBファイルを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
if not path: return
try:
cursor = self.conn.cursor()
cursor.execute(f"ATTACH DATABASE '{path}' AS backup_db")
cursor.execute("INSERT OR IGNORE INTO OBS SELECT * FROM backup_db.OBS")
cursor.execute("INSERT OR IGNORE INTO FCST SELECT * FROM backup_db.FCST")
cursor.execute("INSERT OR IGNORE INTO JMA_FCST SELECT * FROM backup_db.JMA_FCST")
self.conn.commit()
cursor.execute("DETACH DATABASE backup_db")
QMessageBox.information(self, "復元完了", "データの結合（復元）が完了しました。")
self.update_tab1_station_combo()
self.search_obs_table()
except Exception as e:
QMessageBox.critical(self, "エラー", f"復元中にエラーが発生しました:\n{e}")
# ==========================================
# タブ8: 解析値 (GRIB2) 抽出＆マップ表示
# ==========================================
def setup_tab8(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QHBoxLayout(inner_widget); layout.setSpacing(15)
left_panel = QWidget(); left_layout = QVBoxLayout(left_panel); left_panel.setFixedWidth(450)
group_mode = QGroupBox("1. GRIB2 データ取得モード (※元データはUTC基準です)")
mode_layout = QVBoxLayout()
auto_layout = QHBoxLayout()
self.btn_t8_auto = QPushButton("▶ GRIB2 自動取得を開始 (1時間毎)")
self.btn_t8_auto.setObjectName("AutoBtn")
self.btn_t8_auto.clicked.connect(self.toggle_t8_auto)
self.lbl_t8_auto = QLabel("停止中")
self.lbl_t8_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
auto_layout.addWidget(self.btn_t8_auto); auto_layout.addWidget(self.lbl_t8_auto); auto_layout.addStretch()
mode_layout.addLayout(auto_layout)
self.grib_radio_latest = QRadioButton("🌟 最新データを取得 (自動取得用)")
self.grib_radio_latest.setChecked(True)
self.grib_radio_range = QRadioButton("📅 過去データを期間指定して一括抽出")
mode_group = QButtonGroup(); mode_group.addButton(self.grib_radio_latest); mode_group.addButton(self.grib_radio_range)
date_layout = QHBoxLayout()
self.grib_date_start = QDateEdit(QDate.currentDate().addDays(-1)); self.grib_date_start.setCalendarPopup(True); self.grib_date_start.setEnabled(False)
self.grib_date_end = QDateEdit(QDate.currentDate()); self.grib_date_end.setCalendarPopup(True); self.grib_date_end.setEnabled(False)
date_layout.addWidget(QLabel("開始(JST):")); date_layout.addWidget(self.grib_date_start); date_layout.addWidget(QLabel("〜 終了:")); date_layout.addWidget(self.grib_date_end)
self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_start.setEnabled(checked))
self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_end.setEnabled(checked))
mode_layout.addWidget(self.grib_radio_latest); mode_layout.addWidget(self.grib_radio_range); mode_layout.addLayout(date_layout)
group_mode.setLayout(mode_layout); left_layout.addWidget(group_mode)
group_folders = QGroupBox("2. 解析値データ フォルダ設定 ＆ DB一括書込")
f_layout = QVBoxLayout()
self.grib_folders = {"rain": "", "snowfall": "", "snowdepth": ""}
self.grib_labels = {}
for key, icon, name in [("rain", "🌧️", "解析雨量"), ("snowfall", "❄️", "解析降雪量"), ("snowdepth", "⛄", "解析積雪深")]:
row = QVBoxLayout()
lbl = QLabel(f"{icon} {name} フォルダ: 未設定")
self.grib_labels[key] = lbl
btn_layout = QHBoxLayout()
btn_set = QPushButton("フォルダ選択"); btn_set.clicked.connect(lambda _, k=key: self.select_grib_folder(k))
btn_ext = QPushButton(f"{name} 全座標をDBへ書込"); btn_ext.setStyleSheet("background-color: #0366D6; color: white;")
btn_ext.clicked.connect(lambda _, k=key, n=name: self.run_grib_db_extraction(k, n))
btn_layout.addWidget(btn_set); btn_layout.addWidget(btn_ext)
row.addWidget(lbl); row.addLayout(btn_layout)
line = QFrame(); line.setFrameShape(QFrame.Shape.HLine); line.setFrameShadow(QFrame.Shadow.Sunken)
row.addWidget(line); f_layout.addLayout(row)
group_folders.setLayout(f_layout); left_layout.addWidget(group_folders)
# 4. 解析値 簡易データ確認
group_query = QGroupBox("4. 解析値 簡易データ確認 (※日本時間JSTで指定可能)")
query_layout = QVBoxLayout()
dt_layout = QHBoxLayout()
self.t8_q_date = QDateEdit(QDate.currentDate()); self.t8_q_date.setCalendarPopup(True)
self.t8_q_time = QComboBox()
self.t8_q_time.addItem("日合計 / 日最大 (24h)")
self.t8_q_time.addItems([f"{str(h).zfill(2)}:00" for h in range(24)])
dt_layout.addWidget(QLabel("日時(JST):")); dt_layout.addWidget(self.t8_q_date); dt_layout.addWidget(self.t8_q_time)
query_layout.addLayout(dt_layout)
pt_layout = QHBoxLayout()
self.t8_q_point = QComboBox()
pt_layout.addWidget(QLabel("地点:")); pt_layout.addWidget(self.t8_q_point)
query_layout.addLayout(pt_layout)
el_layout = QHBoxLayout()
self.t8_q_element = QComboBox()
self.t8_q_element.addItems(["降水量 (RainfallP1)", "降雪量 (SnowfallP1)", "積雪深 (SnowDepth)"])
el_layout.addWidget(QLabel("要素:")); el_layout.addWidget(self.t8_q_element)
query_layout.addLayout(el_layout)
res_layout = QHBoxLayout()
btn_query = QPushButton("値を確認"); btn_query.setObjectName("ActionBtn"); btn_query.clicked.connect(self.run_t8_simple_query)
self.lbl_t8_q_result = QLabel("結果: --")
self.lbl_t8_q_result.setStyleSheet("font-size: 15px; font-weight: bold; color: #D73A49;")
res_layout.addWidget(btn_query); res_layout.addWidget(self.lbl_t8_q_result); res_layout.addStretch()
query_layout.addLayout(res_layout)
group_query.setLayout(query_layout); left_layout.addWidget(group_query)
left_layout.addStretch(1); layout.addWidget(left_panel)
# 【右パネル】 リファレンス画像ビューア (ZoomableViewに変更)
right_panel = QWidget(); right_layout = QVBoxLayout(right_panel)
group_map = QGroupBox("3. GPV座標 リファレンスマップ (地点番号確認用)")
map_layout = QVBoxLayout()
map_layout.addWidget(QLabel("※マウスホイールでズーム、ドラッグで移動ができます。\nここで確認した数字をタブ5の「GPV_〇」と紐付けて検証してください。"))
self.scene = QGraphicsScene()
self.view = ZoomableView(self.scene)
self.view.setStyleSheet("background-color: #E1E4E8; border: 1px solid #D0D7DE;")
map_layout.addWidget(self.view)
btn_reload = QPushButton("🔄 地図画像を再読み込み"); btn_reload.clicked.connect(self.reload_reference_map)
map_layout.addWidget(btn_reload); group_map.setLayout(map_layout); right_layout.addWidget(group_map)
layout.addWidget(right_panel, stretch=1)
self.tabs.insertTab(2, scroll, "解析値 (GRIB2) 抽出＆マップ表示")
QTimer.singleShot(100, self.reload_reference_map)
def toggle_t8_auto(self):
if self.t8_auto_timer.isActive():
self.t8_auto_timer.stop()
self.lbl_t8_auto.setText("停止中")
self.btn_t8_auto.setText("▶ GRIB2 自動取得を開始 (1時間毎)")
else:
self.t8_auto_timer.start(3600000)
self.lbl_t8_auto.setText("🔴 自動取得中")
self.btn_t8_auto.setText("■ GRIB2 自動取得を停止")
self.auto_fetch_grib2()
def auto_fetch_grib2(self):
self.grib_radio_latest.setChecked(True)
for key, name in [("rain", "解析雨量"), ("snowfall", "解析降雪量"), ("snowdepth", "解析積雪深")]:
if self.grib_folders.get(key):
self.run_grib_db_extraction(key, name, silent=True)
def reload_reference_map(self):
from PyQt6.QtGui import QPixmap
self.scene.clear()
pixmap = QPixmap("gpv_reference_map.png")
if not pixmap.isNull():
self.scene.addPixmap(pixmap)
self.view.fitInView(self.scene.sceneRect(), Qt.AspectRatioMode.KeepAspectRatio)
else:
self.scene.addText("画像 'gpv_reference_map.png' が見つかりません。")
def update_t8_query_points(self):
if not hasattr(self, 't8_q_point'):
return # まだタブ8の部品が作られていない時は、ここで処理をスキップする
self.t8_q_point.clear()
try:
cursor = self.conn.cursor()
cursor.execute("SELECT DISTINCT MsCd FROM OBS ORDER BY MsCd")
points = cursor.fetchall()
cursor.execute("SELECT PointID, Lon, Lat FROM GPV_Points")
gpv_map = {f"GPV_{r[0]}": (r[1], r[2]) for r in cursor.fetchall()}
display_items = []
for p in points:
code = str(p[0])
if code in gpv_map:
lon, lat = gpv_map[code]
display_items.append(f"{code} (Lon: {lon:.3f}, Lat: {lat:.3f})")
else:
display_items.append(code)
self.t8_q_point.addItems(display_items)
except Exception as e: pass
def run_t8_simple_query(self):
date_str = self.t8_q_date.date().toString("yyyy-MM-dd")
time_str = self.t8_q_time.currentText()
ms_cd = self.t8_q_point.currentText().split(" (")[0]
el_str = self.t8_q_element.currentText()
if "Rain" in el_str: col, unit = "RainfallP1", "mm"
elif "Snowfall" in el_str: col, unit = "SnowfallP1", "cm"
else: col, unit = "SnowDepth", "cm"
try:
cursor = self.conn.cursor()
if "日合計" in time_str:
# ★ 修正: JSTの1日分(00:00-23:00)を内部のUTC時間範囲に直して集計
jst_start = datetime.combine(self.t8_q_date.date().toPyDate(), datetime.min.time())
utc_start = jst_start - timedelta(hours=9)
utc_end = utc_start + timedelta(hours=23)
u_start_str = utc_start.strftime("%Y-%m-%d %H:%M")
u_end_str = utc_end.strftime("%Y-%m-%d %H:%M")
if col == "SnowDepth":
cursor.execute(f"SELECT MAX({col}) FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
else:
cursor.execute(f"SELECT SUM({col}) FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
else:
# ★ 修正: 特定時間のJST指定をUTCに変換してピンポイント検索
jst_dt = datetime.strptime(f"{date_str} {time_str}", "%Y-%m-%d %H:%M")
utc_dt = jst_dt - timedelta(hours=9)
u_day = utc_dt.strftime("%Y-%m-%d")
u_time = utc_dt.strftime("%H:%M")
cursor.execute(f"SELECT {col} FROM OBS WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (u_day, u_time, ms_cd))
row = cursor.fetchone()
if row and row[0] is not None:
self.lbl_t8_q_result.setText(f"結果: {row[0]:.1f} {unit}")
else:
self.lbl_t8_q_result.setText("結果: データなし")
except Exception as e:
self.lbl_t8_q_result.setText("結果: エラー")
def select_grib_folder(self, key):
folder = QFileDialog.getExistingDirectory(self, "フォルダを選択")
if folder:
self.grib_folders[key] = folder
self.grib_labels[key].setText(f"📁 {folder}")
def run_grib_db_extraction(self, data_type_key, data_name, silent=False):
folder_path = self.grib_folders[data_type_key]
if not folder_path or not os.path.exists(folder_path):
if not silent: QMessageBox.warning(self, "警告", f"{data_name} のフォルダが設定されていません。")
return
if data_type_key == "rain": pattern = "**/*Prr60lv*.bin"
elif data_type_key == "snowfall": pattern = "**/*Psflv*.bin"
elif data_type_key == "snowdepth": pattern = "**/*Psdlv*.bin"
bin_files = glob.glob(os.path.join(folder_path, pattern), recursive=True)
if not bin_files:
if not silent: QMessageBox.warning(self, "ファイルなし", "対象ファイルが見つかりません。")
return
bin_files.sort()
target_files = []
if self.grib_radio_latest.isChecked():
target_files = [bin_files[-1]]
else:
# ★ 修正: JSTの期間指定に対応するUTC日付の範囲でファイルをフィルタ
sd_utc = (self.grib_date_start.date().toPyDate() - timedelta(days=1)).strftime("%Y%m%d")
ed_utc = self.grib_date_end.date().toPyDate().strftime("%Y%m%d")
for f in bin_files:
match = re.search(r'(20\d{6})', os.path.basename(f))
if match and (sd_utc <= match.group(1)[:8] <= ed_utc): target_files.append(f)
if not target_files:
if not silent: QMessageBox.warning(self, "該当なし", "指定期間内にファイルがありません。")
return
temp_csv = os.path.abspath("grib_temp_export.csv")
total_inserted = 0
cursor = self.conn.cursor()
try:
for target_file in target_files:
subprocess.run([self.wgrib2_path, target_file, "-csv", temp_csv], stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True)
with open(temp_csv, 'r', encoding='utf-8', errors='replace') as f:
csv_lines = [line for line in f.readlines() if "," in line]
if not csv_lines: continue
df = pd.read_csv(io.StringIO("".join(csv_lines)), header=None)
raw_lon = pd.to_numeric(df.iloc[:, -3], errors='coerce')
raw_lat = pd.to_numeric(df.iloc[:, -2], errors='coerce')
df["Value"] = pd.to_numeric(df.iloc[:, -1], errors='coerce')
if raw_lat.mean() > raw_lon.mean(): df["Lon"], df["Lat"] = raw_lat, raw_lon
else: df["Lon"], df["Lat"] = raw_lon, raw_lat
df = df.dropna(subset=["Lon", "Lat", "Value"])
f_df = df[(df["Lat"] >= self.lat_min) & (df["Lat"] <= self.lat_max) & (df["Lon"] >= self.lon_min) & (df["Lon"] <= self.lon_max)]
if f_df.empty: continue
unique_coords = f_df[['Lon', 'Lat']].drop_duplicates().sort_values(by=['Lat', 'Lon'], ascending=[False, True]).reset_index(drop=True)
for _, row in unique_coords.iterrows():
cursor.execute("INSERT OR IGNORE INTO GPV_Points (Lon, Lat) VALUES (?, ?)", (row['Lon'], row['Lat']))
db_points = pd.read_sql_query("SELECT PointID, Lon, Lat FROM GPV_Points", self.conn)
f_df = pd.merge(f_df, db_points, on=['Lon', 'Lat'], how='left')
match = re.search(r'(\d{12})', os.path.basename(target_file))
if match:
dt_utc = datetime.strptime(match.group(1), "%Y%m%d%H%M")
obs_day = dt_utc.strftime("%Y-%m-%d")
obs_time = dt_utc.strftime("%H:%M")
else:
continue
count_in_file = 0
for _, r in f_df.iterrows():
ms_cd = f"GPV_{int(r['PointID'])}"
val = r['Value']
cursor.execute("SELECT RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (obs_day, obs_time, ms_cd))
existing = cursor.fetchone()
if existing:
r_p = val if data_type_key == "rain" else existing[0]
s_p = val if data_type_key == "snowfall" else existing[1]
s_d = val if data_type_key == "snowdepth" else existing[2]
cursor.execute("UPDATE OBS SET RainfallP1=?, SnowfallP1=?, SnowDepth=? WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (r_p, s_p, s_d, obs_day, obs_time, ms_cd))
else:
r_p = val if data_type_key == "rain" else 0.0
s_p = val if data_type_key == "snowfall" else 0.0
s_d = val if data_type_key == "snowdepth" else None
cursor.execute("INSERT INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth) VALUES (?,?,?,?,?,?)", (obs_day, obs_time, ms_cd, r_p, s_p, s_d))
count_in_file += 1
total_inserted += count_in_file
self.conn.commit()
self.update_tab1_station_combo()
self.update_tab5_station_combo()
if not silent: QMessageBox.information(self, "書込完了", f"データベースへの書込が完了しました。(UTC統一格納)\n処理ファイル数: {len(target_files)}\n書込座標データ数: {total_inserted} 件")
except Exception as e:
if not silent: QMessageBox.critical(self, "エラー", f"抽出処理エラー:\n{e}")
if __name__ == "__main__":
app = QApplication(sys.argv)
app.setQuitOnLastWindowClosed(False)
QImageReader.setAllocationLimit(0)
window = WeatherVerificationApp()
window.show()
sys.exit(app.exec())
未分類
