import sys
import os
import glob
import re
import sqlite3
import numpy as np
import pandas as pd
from datetime import datetime, timedelta
from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
QPushButton, QLabel, QTableWidget, QTableWidgetItem, QTabWidget,
QHeaderView, QFileDialog, QMessageBox, QGroupBox, QGridLayout,
QCheckBox, QComboBox, QSplitter, QProgressBar, QTextEdit)
from PyQt6.QtCore import Qt, QThread, pyqtSignal
CACHE_DIR = os.path.join(os.getcwd(), "gpv_cache_npz")
DEFAULT_DB_PATH = os.path.join(os.getcwd(), "sapporo_ml_ready_archive.db")
# ==========================================
# バックグラウンド処理: 【LightGBM対応】ワイドフォーマットDB生成スレッド
# ==========================================
class DBGeneratorThread(QThread):
progress = pyqtSignal(int, str)
finished = pyqtSignal(str)
error = pyqtSignal(str)
def __init__(self, db_path):
super().__init__()
self.db_path = db_path
def run(self):
try:
self.progress.emit(5, "ML Readyデータベースの初期化中...")
conn = sqlite3.connect(self.db_path)
cursor = conn.cursor()
# 【革新】LightGBM等での機械学習に直結する超ワイドフォーマット設計
cursor.execute('''
CREATE TABLE IF NOT EXISTS ml_ready_weather_data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
lat REAL, lon REAL,
valid_time TEXT, ft INTEGER, init_time TEXT,
msm_gpv_rain REAL, msm_gpv_temp REAL, msm_gpv_u REAL, msm_gpv_v REAL, msm_gpv_cloud REAL, msm_gpv_rh REAL,
msm_guid_rain REAL, msm_guid_temp REAL, msm_guid_u REAL, msm_guid_v REAL, msm_guid_cloud REAL, msm_guid_rh REAL, msm_guid_snow REAL,
gsm_gpv_rain REAL, gsm_gpv_temp REAL, gsm_gpv_u REAL, gsm_gpv_v REAL, gsm_gpv_cloud REAL, gsm_gpv_rh REAL,
gsm_guid_rain REAL, gsm_guid_temp REAL, gsm_guid_u REAL, gsm_guid_v REAL, gsm_guid_cloud REAL, gsm_guid_rh REAL, gsm_guid_snow REAL,
obs_rain REAL, obs_temp REAL, obs_wind_spd REAL, obs_wind_dir REAL, obs_snow REAL,
UNIQUE(lat, lon, valid_time, ft)
)
''')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_ml_base ON ml_ready_weather_data (valid_time, lat, lon)')
npz_files = glob.glob(os.path.join(CACHE_DIR, "*.npz"))
if not npz_files:
self.error.emit("エラー: キャッシュフォルダに .npz ファイルが存在しません。")
return
lats = np.arange(42.80, 43.30, 0.009)
lons = np.arange(141.10, 141.60, 0.012)
mesh_points = [(lat, lon) for lat in lats for lon in lons]
total_files = len(npz_files)
# メモリ上でデータを結合するための辞書
# キー: (lat, lon, valid_time, ft, init_time)
# 値: { 'msm_gpv_temp': 15.0, ... }
merged_data = {}
self.progress.emit(10, "NPZファイルからの特徴量抽出とマージを開始...")
for i, fpath in enumerate(npz_files):
fname = os.path.basename(fpath)
m_model = "msm" if "MSM" in fname else "gsm"
m_mode = "guid" if "GUID" in fname else "gpv"
prefix = f"{m_model}_{m_mode}_" # 例: msm_gpv_
time_match = re.search(r'_(\d{14})_', fname)
ft_match = re.search(r'_FT(\d+)', fname)
if not time_match or not ft_match: continue
init_time_str = time_match.group(1)
ft_val = int(ft_match.group(1))
dt_init = datetime.strptime(init_time_str, "%Y%m%d%H%M%S") + timedelta(hours=9) # JST化
dt_valid = dt_init + timedelta(hours=ft_val)
try:
data = np.load(fpath)
files = data.files
lon_key = next((k for k in files if 'lon' in k.lower()), None)
lat_key = next((k for k in files if 'lat' in k.lower()), None)
if not lon_key or not lat_key: continue
lon_arr, lat_arr = data[lon_key], data[lat_key]
if lon_arr.ndim == 1: lon_2d, lat_2d = np.meshgrid(lon_arr, lat_arr)
else: lon_2d, lat_2d = lon_arr, lat_arr
def get_arr(possible_keys):
key = next((k for k in possible_keys if k in files), None)
return data[key] if key else None
arr_rain = get_arr(['precip', 'tp', 'apcp'])
arr_temp = get_arr(['t2m', 'tmp', 'temp'])
arr_u = get_arr(['u10', 'u', 'ugrd'])
arr_v = get_arr(['v10', 'v', 'vgrd'])
arr_cloud = get_arr(['tcc'])
arr_snow = get_arr(['snow', 'weasd', 'snod', 'asnow'])
arr_rh = get_arr(['rh2m', 'rh'])
for m_lat, m_lon in mesh_points:
lat_r, lon_r = round(m_lat, 4), round(m_lon, 4)
dict_key = (lat_r, lon_r, dt_valid.strftime("%Y-%m-%d %H:%M:%S"), ft_val, dt_init.strftime("%Y-%m-%d %H:%M:%S"))
if dict_key not in merged_data:
merged_data[dict_key] = {}
dist = (lon_2d - lon_r)**2 + (lat_2d - lat_r)**2
sy, sx = np.unravel_index(np.argmin(dist), dist.shape)
def extract_val(arr):
if arr is None: return None
try:
v = arr[sy, sx] if arr.ndim == 2 else arr[sx, sy]
if np.isnan(v) or v > 200000 or v < -10000: return None
return round(float(v), 2)
except: return None
# AIが計算できるよう、風向はU, Vベクトルのまま保存する
merged_data[dict_key][prefix + 'rain'] = extract_val(arr_rain)
merged_data[dict_key][prefix + 'temp'] = extract_val(arr_temp)
merged_data[dict_key][prefix + 'u'] = extract_val(arr_u)
merged_data[dict_key][prefix + 'v'] = extract_val(arr_v)
merged_data[dict_key][prefix + 'cloud'] = extract_val(arr_cloud)
merged_data[dict_key][prefix + 'rh'] = extract_val(arr_rh)
if m_mode == 'guid':
merged_data[dict_key][prefix + 'snow'] = extract_val(arr_snow)
data.close()
except Exception as e:
print(f"Error {fname}: {e}")
if i % max(1, total_files // 10) == 0:
self.progress.emit(10 + int((i / total_files) * 70), f"ML特徴量抽出・マージ中... {i}/{total_files} ファイル")
self.progress.emit(85, "統合ワイドデータをDBへ高速書き込み中...")
# データベースへのINSERT用リスト作成
insert_list = []
for k, v in merged_data.items():
insert_list.append((
k[0], k[1], k[2], k[3], k[4], # lat, lon, valid, ft, init
v.get('msm_gpv_rain'), v.get('msm_gpv_temp'), v.get('msm_gpv_u'), v.get('msm_gpv_v'), v.get('msm_gpv_cloud'), v.get('msm_gpv_rh'),
v.get('msm_guid_rain'), v.get('msm_guid_temp'), v.get('msm_guid_u'), v.get('msm_guid_v'), v.get('msm_guid_cloud'), v.get('msm_guid_rh'), v.get('msm_guid_snow'),
v.get('gsm_gpv_rain'), v.get('gsm_gpv_temp'), v.get('gsm_gpv_u'), v.get('gsm_gpv_v'), v.get('gsm_gpv_cloud'), v.get('gsm_gpv_rh'),
v.get('gsm_guid_rain'), v.get('gsm_guid_temp'), v.get('gsm_guid_u'), v.get('gsm_guid_v'), v.get('gsm_guid_cloud'), v.get('gsm_guid_rh'), v.get('gsm_guid_snow')
))
cursor.executemany('''
INSERT OR REPLACE INTO ml_ready_weather_data (
lat, lon, valid_time, ft, init_time,
msm_gpv_rain, msm_gpv_temp, msm_gpv_u, msm_gpv_v, msm_gpv_cloud, msm_gpv_rh,
msm_guid_rain, msm_guid_temp, msm_guid_u, msm_guid_v, msm_guid_cloud, msm_guid_rh, msm_guid_snow,
gsm_gpv_rain, gsm_gpv_temp, gsm_gpv_u, gsm_gpv_v, gsm_gpv_cloud, gsm_gpv_rh,
gsm_guid_rain, gsm_guid_temp, gsm_guid_u, gsm_guid_v, gsm_guid_cloud, gsm_guid_rh, gsm_guid_snow
) VALUES (?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?,?)
''', insert_list)
conn.commit()
conn.close()
self.progress.emit(100, "生成完了")
self.finished.emit(f"✓ ML Ready データベース完成: {len(insert_list):,} 件の特徴量レコードを構築しました。")
except Exception as e:
self.error.emit(f"DB生成エラー: {str(e)}")
# ==========================================
# メインアプリケーション
# ==========================================
class IntegratedWeatherSystem(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("統合気象データプラットフォーム (Ver 7.0 - ML Ready ワイドDBエンジン)")
self.resize(1350, 900)
self.setStyleSheet("background-color: #2b2b2b; color: #e0e0e0; font-family: 'MS Gothic';")
self.current_csv_data = None
self.current_db_path = DEFAULT_DB_PATH
self.current_page = 1
self.records_per_page = 1000
self.total_records = 0
# Excel出力・マップ表示用 (DBのプレフィックスに対応)
self.weather_elements = [
{"id": "rain", "name": "降水量 (mm)"},
{"id": "temp", "name": "気温 (℃)"},
{"id": "wind", "name": "風向・風速"}, # ※DB内はu,vだが、Excel出力時に風向文字に変換する
{"id": "cloud", "name": "全雲量 (%)"},
{"id": "snow", "name": "降雪量 (cm)"},
{"id": "rh", "name": "湿度 (%)"}
]
self.init_ui()
if os.path.exists(self.current_db_path):
self.update_db_info(self.current_db_path)
def init_ui(self):
central_widget = QWidget()
self.setCentralWidget(central_widget)
main_layout = QVBoxLayout(central_widget)
self.tabs = QTabWidget()
self.tabs.setStyleSheet("""
QTabBar::tab { background-color: #3c3f41; padding: 12px; font-weight: bold; width: 280px; font-size: 10pt; }
QTabBar::tab:selected { background-color: #007acc; color: white; }
QTabWidget::pane { border: 1px solid #555; }
""")
self.tab1_db = QWidget()
self.tab2_export = QWidget()
self.tab3_vis = QWidget()
self.tabs.addTab(self.tab1_db, "🗄 [Tab 1] ML-DB管理 (高機能ブラウザ)")
self.tabs.addTab(self.tab2_export, "📋 [Tab 2] ワイドDB抽出＆Excel出力")
self.tabs.addTab(self.tab3_vis, "🗺 [Tab 3] 1kmメッシュマップ分析")
main_layout.addWidget(self.tabs)
self.setup_tab_1_db()
self.setup_tab_2_export()
self.setup_tab_3_vis()
# ==========================================
# [Tab 1] 高機能 DB Browser
# ==========================================
def setup_tab_1_db(self):
layout = QVBoxLayout(self.tab1_db)
toolbar = QHBoxLayout()
self.btn_open_db = QPushButton("📂 DBを開く(O)")
self.btn_open_db.setStyleSheet("background-color: #34495e; padding: 6px;")
self.btn_open_db.clicked.connect(self.open_database_file)
self.btn_save_db = QPushButton("💾 別名で保存")
self.btn_save_db.setStyleSheet("background-color: #34495e; padding: 6px;")
self.btn_save_db.clicked.connect(self.save_database_as)
self.btn_generate_db = QPushButton("⚡ 本番: NPZから ML Ready DB を構築")
self.btn_generate_db.setStyleSheet("background-color: #c0392b; font-weight: bold; padding: 6px;")
self.btn_generate_db.clicked.connect(self.start_db_generation)
self.lbl_db_file = QLabel(f"対象DB: {os.path.basename(self.current_db_path)}")
self.lbl_db_file.setStyleSheet("color: #00fa9a; font-weight: bold; margin-left: 20px;")
toolbar.addWidget(self.btn_open_db)
toolbar.addWidget(self.btn_save_db)
toolbar.addWidget(self.btn_generate_db)
toolbar.addWidget(self.lbl_db_file)
toolbar.addStretch()
layout.addLayout(toolbar)
self.progress_bar = QProgressBar()
self.progress_bar.setVisible(False)
layout.addWidget(self.progress_bar)
self.lbl_status = QLabel("ステータス: 待機中")
layout.addWidget(self.lbl_status)
self.db_tabs = QTabWidget()
self.tab_schema = QWidget()
schema_layout = QVBoxLayout(self.tab_schema)
self.table_schema = QTableWidget()
self.table_schema.setStyleSheet("background-color: #1e1e1e; color: #e0e0e0;")
self.table_schema.setColumnCount(3)
self.table_schema.setHorizontalHeaderLabels(["テーブル名 / カラム名", "データ型", "スキーマ詳細"])
self.table_schema.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Interactive)
schema_layout.addWidget(self.table_schema)
self.tab_data = QWidget()
data_layout = QVBoxLayout(self.tab_data)
filter_layout = QHBoxLayout()
filter_layout.addWidget(QLabel("テーブル:"))
self.combo_tables = QComboBox()
self.combo_tables.currentTextChanged.connect(self.on_table_changed)
filter_layout.addWidget(self.combo_tables)
self.btn_refresh_data = QPushButton("🔄 更新")
self.btn_refresh_data.clicked.connect(self.reload_current_page)
filter_layout.addWidget(self.btn_refresh_data)
filter_layout.addStretch()
self.btn_prev_page = QPushButton("◀ 前の1000件")
self.btn_prev_page.clicked.connect(self.prev_page)
self.lbl_page_info = QLabel("ページ: 1 / 1")
self.btn_next_page = QPushButton("次の1000件 ▶")
self.btn_next_page.clicked.connect(self.next_page)
filter_layout.addWidget(self.btn_prev_page)
filter_layout.addWidget(self.lbl_page_info)
filter_layout.addWidget(self.btn_next_page)
data_layout.addLayout(filter_layout)
self.table_db = QTableWidget()
self.table_db.setStyleSheet("background-color: #1e1e1e; gridline-color: #444; color: #e0e0e0;")
data_layout.addWidget(self.table_db)
self.tab_sql = QWidget()
sql_layout = QVBoxLayout(self.tab_sql)
self.txt_sql = QTextEdit()
self.txt_sql.setPlaceholderText("SELECT lat, lon, valid_time, msm_guid_temp, obs_temp FROM ml_ready_weather_data LIMIT 100;")
self.txt_sql.setStyleSheet("background-color: #1e1e1e; color: #f1c40f; font-family: Consolas; font-size: 11pt;")
sql_layout.addWidget(self.txt_sql, 1)
self.btn_exec_sql = QPushButton("▶ SQLを実行 (F5)")
self.btn_exec_sql.setStyleSheet("background-color: #27ae60; font-weight: bold; padding: 5px;")
self.btn_exec_sql.clicked.connect(self.execute_custom_sql)
sql_layout.addWidget(self.btn_exec_sql)
self.table_sql_result = QTableWidget()
self.table_sql_result.setStyleSheet("background-color: #1e1e1e; color: #e0e0e0;")
sql_layout.addWidget(self.table_sql_result, 3)
self.db_tabs.addTab(self.tab_schema, "📋 MLスキーマ構造")
self.db_tabs.addTab(self.tab_data, "🔍 ワイドデータ閲覧(ページネーション付)")
self.db_tabs.addTab(self.tab_sql, "💻 カスタムSQL実行")
layout.addWidget(self.db_tabs)
def open_database_file(self):
path, _ = QFileDialog.getOpenFileName(self, "SQLiteを開く", "", "DB (*.db *.sqlite)")
if path:
self.current_db_path = path
self.update_db_info(path)
def save_database_as(self):
if not os.path.exists(self.current_db_path): return
default_name = f"sapporo_ml_archive_{datetime.now().strftime('%Y%m%d')}.db"
path, _ = QFileDialog.getSaveFileName(self, "別名保存", default_name, "DB (*.db)")
if path:
import shutil
shutil.copy2(self.current_db_path, path)
self.current_db_path = path
self.update_db_info(path)
QMessageBox.information(self, "完了", f"保存しました。\n{path}")
def update_db_info(self, db_path):
self.lbl_db_file.setText(f"対象DB: {os.path.basename(db_path)}")
self.combo_tables.blockSignals(True)
self.combo_tables.clear()
try:
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [r[0] for r in cursor.fetchall() if r[0] != "sqlite_sequence"]
self.combo_tables.addItems(tables)
self.table_schema.setRowCount(0)
row_idx = 0
for table in tables:
cursor.execute(f"PRAGMA table_info({table})")
for col in cursor.fetchall():
self.table_schema.insertRow(row_idx)
self.table_schema.setItem(row_idx, 0, QTableWidgetItem(f"{table} . {col[1]}"))
self.table_schema.setItem(row_idx, 1, QTableWidgetItem(col[2]))
self.table_schema.setItem(row_idx, 2, QTableWidgetItem("PK" if col[5] else ""))
row_idx += 1
conn.close()
self.combo_tables.blockSignals(False)
if tables:
self.current_page = 1
self.load_table_data(tables[0])
except Exception as e:
self.lbl_status.setText(f"エラー: {e}")
def on_table_changed(self, table_name):
self.current_page = 1; self.load_table_data(table_name)
def reload_current_page(self): self.load_table_data(self.combo_tables.currentText())
def prev_page(self):
if self.current_page > 1: self.current_page -= 1; self.load_table_data(self.combo_tables.currentText())
def next_page(self):
max_pages = max(1, (self.total_records + self.records_per_page - 1) // self.records_per_page)
if self.current_page < max_pages: self.current_page += 1; self.load_table_data(self.combo_tables.currentText())
def load_table_data(self, table_name):
if not table_name or not os.path.exists(self.current_db_path): return
try:
conn = sqlite3.connect(self.current_db_path)
cursor = conn.cursor()
cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
self.total_records = cursor.fetchone()[0]
max_pages = max(1, (self.total_records + self.records_per_page - 1) // self.records_per_page)
if self.current_page > max_pages: self.current_page = max_pages
offset = (self.current_page - 1) * self.records_per_page
cursor.execute(f"SELECT * FROM {table_name} LIMIT {self.records_per_page} OFFSET {offset}")
rows = cursor.fetchall()
col_names = [description[0] for description in cursor.description]
self.table_db.setColumnCount(len(col_names))
self.table_db.setHorizontalHeaderLabels(col_names)
self.table_db.setRowCount(len(rows))
for r_idx, row in enumerate(rows):
for c_idx, val in enumerate(row):
self.table_db.setItem(r_idx, c_idx, QTableWidgetItem("NULL" if val is None else str(val)))
self.lbl_page_info.setText(f"ページ: {self.current_page} / {max_pages}")
self.lbl_status.setText(f"ロード完了: {table_name} (全 {self.total_records:,} 件)")
self.btn_prev_page.setEnabled(self.current_page > 1)
self.btn_next_page.setEnabled(self.current_page < max_pages)
conn.close()
except Exception: pass
def execute_custom_sql(self):
sql = self.txt_sql.toPlainText().strip()
if not sql or not os.path.exists(self.current_db_path): return
try:
conn = sqlite3.connect(self.current_db_path)
cursor = conn.cursor()
cursor.execute(sql)
if sql.upper().startswith("SELECT"):
rows = cursor.fetchall()
col_names = [description[0] for description in cursor.description]
self.table_sql_result.setColumnCount(len(col_names))
self.table_sql_result.setHorizontalHeaderLabels(col_names)
self.table_sql_result.setRowCount(len(rows))
for r_idx, row in enumerate(rows):
for c_idx, val in enumerate(row):
self.table_sql_result.setItem(r_idx, c_idx, QTableWidgetItem("NULL" if val is None else str(val)))
self.lbl_status.setText(f"SQL実行成功: {len(rows)} 件")
else:
conn.commit()
self.lbl_status.setText("SQL実行成功: コミット済")
conn.close()
except Exception as e:
QMessageBox.critical(self, "SQLエラー", str(e))
def start_db_generation(self):
self.btn_generate_db.setEnabled(False)
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
self.db_thread = DBGeneratorThread(self.current_db_path)
self.db_thread.progress.connect(self.update_progress)
self.db_thread.finished.connect(self.on_db_generated)
self.db_thread.error.connect(self.on_db_error)
self.db_thread.start()
def update_progress(self, val, text):
self.progress_bar.setValue(val)
self.lbl_status.setText(text)
def on_db_generated(self, msg):
self.btn_generate_db.setEnabled(True)
self.progress_bar.setVisible(False)
self.lbl_status.setText(msg)
self.update_db_info(self.current_db_path)
QMessageBox.information(self, "生成完了", "ML Ready ワイドデータベースの構築が完了しました！")
def on_db_error(self, msg):
self.btn_generate_db.setEnabled(True)
self.progress_bar.setVisible(False)
QMessageBox.critical(self, "エラー", msg)
# ==========================================
# [Tab 2] メッシュ抽出＆Excel出力 (新スキーマ対応)
# ==========================================
def setup_tab_2_export(self):
splitter = QSplitter(Qt.Orientation.Vertical)
layout = QVBoxLayout(self.tab2_export)
layout.addWidget(splitter)
top_widget = QWidget()
top_layout = QVBoxLayout(top_widget)
ctrl_layout = QHBoxLayout()
self.btn_load_csv = QPushButton("📁 観測地点CSVを読込")
self.btn_load_csv.setStyleSheet("background-color: #2980b9; font-weight: bold; padding: 6px;")
self.btn_load_csv.clicked.connect(self.load_csv_data)
ctrl_layout.addWidget(self.btn_load_csv)
ctrl_layout.addStretch()
top_layout.addLayout(ctrl_layout)
self.table_csv = QTableWidget()
self.table_csv.setStyleSheet("background-color: #1e1e1e;")
top_layout.addWidget(self.table_csv)
splitter.addWidget(top_widget)
bottom_widget = QWidget()
bottom_layout = QVBoxLayout(bottom_widget)
matrix_group = QGroupBox("🎛 モデル別 × 気象要素 出力マトリクス")
matrix_group.setStyleSheet("QGroupBox { font-weight: bold; border: 1px solid #555; }")
grid_matrix = QGridLayout(matrix_group)
self.chk_all_select = QCheckBox("全一括選択")
self.chk_all_select.stateChanged.connect(self.toggle_all_checkboxes)
grid_matrix.addWidget(self.chk_all_select, 0, 0, 1, 5)
self.model_frames = [
{"id": "gsm_gpv", "name": "GSM GPV"}, {"id": "msm_gpv", "name": "MSM GPV"},
{"id": "gsm_guid", "name": "GSM ガイダンス"}, {"id": "msm_guid", "name": "MSM ガイダンス"}
]
self.matrix_checkboxes = {}
for m_idx, m_cfg in enumerate(self.model_frames):
lbl = QLabel(m_cfg["name"]); lbl.setStyleSheet("font-weight: bold; color: #3498db;")
grid_matrix.addWidget(lbl, 1, m_idx + 1, Qt.AlignmentFlag.AlignCenter)
for e_idx, elem in enumerate(self.weather_elements):
grid_matrix.addWidget(QLabel(elem["name"]), e_idx + 2, 0)
for m_idx, m_cfg in enumerate(self.model_frames):
chk = QCheckBox()
if "gpv" in m_cfg["id"] and elem["id"] == "snow": chk.setEnabled(False)
self.matrix_checkboxes[f"{m_cfg['id']}_{elem['id']}"] = chk
grid_matrix.addWidget(chk, e_idx + 2, m_idx + 1, Qt.AlignmentFlag.AlignCenter)
bottom_layout.addWidget(matrix_group)
self.btn_export_excel = QPushButton("📊 ワイドDBから内挿抽出し、本番Excel帳票を出力")
self.btn_export_excel.setStyleSheet("background-color: #e67e22; font-size: 12pt; font-weight: bold; padding: 10px;")
self.btn_export_excel.clicked.connect(self.execute_dynamic_excel_export)
bottom_layout.addWidget(self.btn_export_excel)
splitter.addWidget(bottom_widget)
def load_csv_data(self):
path, _ = QFileDialog.getOpenFileName(self, "CSVを開く", "", "CSV (*.csv)")
if not path: return
try:
self.current_csv_data = pd.read_csv(path)
self.table_csv.setColumnCount(len(self.current_csv_data.columns))
self.table_csv.setHorizontalHeaderLabels(self.current_csv_data.columns)
self.table_csv.setRowCount(len(self.current_csv_data))
for r in range(len(self.current_csv_data)):
for c in range(len(self.current_csv_data.columns)):
self.table_csv.setItem(r, c, QTableWidgetItem(str(self.current_csv_data.iat[r, c])))
except Exception as e: QMessageBox.critical(self, "エラー", str(e))
def toggle_all_checkboxes(self, state):
for chk in self.matrix_checkboxes.values():
if chk.isEnabled(): chk.setChecked(state == 2)
def execute_dynamic_excel_export(self):
if self.current_csv_data is None or not os.path.exists(self.current_db_path):
QMessageBox.warning(self, "警告", "CSV読込とDB生成が必要です。")
return
save_path, _ = QFileDialog.getSaveFileName(self, "Excel保存", f"DBout_{datetime.now().strftime('%Y%m%d')}.xlsx", "Excel (*.xlsx)")
if not save_path: return
try:
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
wb = openpyxl.Workbook()
default_sheet = wb.active
font_title = Font(name="MS Gothic", size=14, bold=True, color="0F172A")
fill_msm = PatternFill(start_color="1E3A8A", end_color="1E3A8A", fill_type="solid")
fill_gsm = PatternFill(start_color="065F46", end_color="065F46", fill_type="solid")
box_border = Border(left=Side(style='thin', color='CBD5E1'), right=Side(style='thin', color='CBD5E1'), top=Side(style='thin', color='CBD5E1'), bottom=Side(style='thin', color='CBD5E1'))
conn = sqlite3.connect(self.current_db_path)
def calculate_wind_dir_from_uv(u, v):
if u is None or v is None or np.isnan(u) or np.isnan(v): return "-"
spd = np.sqrt(u**2 + v**2)
dir_deg = (np.degrees(np.arctan2(u, v)) + 180) % 360
dirs = ["北", "北北東", "北東", "東北東", "東", "東南東", "南東", "南南東", "南", "南南西", "南西", "西南西", "西", "西北西", "北西", "北北西", "北"]
idx = int((dir_deg + 11.25) / 22.5) % 16
return f"{dirs[idx]} {round(float(spd), 1)}"
for s_name, m_keys in [("MSM予報", ["msm_gpv", "msm_guid"]), ("GSM予報", ["gsm_gpv", "gsm_guid"])]:
ws = wb.create_sheet(title=s_name)
is_gsm = "GSM" in s_name
max_ft = 72 if is_gsm else 39
step = 3 if is_gsm else 1
fts = list(range(0, max_ft + 1, step))
current_row = 4
ws.cell(row=1, column=1, value=f"■ {s_name} (ML DB 抽出)").font = font_title
for m_id in m_keys:
for elem in self.weather_elements:
chk_key = f"{m_id}_{elem['id']}"
if chk_key not in self.matrix_checkboxes or not self.matrix_checkboxes[chk_key].isChecked(): continue
db_col = f"{m_id}_{elem['id']}" # 例: msm_gpv_temp
ws.cell(row=current_row, column=1, value=f"【{m_id.upper()}】 {elem['name']}").fill = fill_gsm if is_gsm else fill_msm
current_row += 2
for p_idx, row_data in self.current_csv_data.iterrows():
p_lat, p_lon = float(row_data.iloc[2]), float(row_data.iloc[3])
ws.cell(row=current_row, column=2, value=str(row_data.iloc[1]))
cursor = conn.cursor()
cursor.execute(f"SELECT lat, lon FROM ml_ready_weather_data ORDER BY ((lat-{p_lat})*(lat-{p_lat}) + (lon-{p_lon})*(lon-{p_lon})) ASC LIMIT 1")
nearest = cursor.fetchone()
ref_lat, ref_lon = nearest if nearest else (0.0, 0.0)
for ft_idx, ft in enumerate(fts):
cell = ws.cell(row=current_row, column=7+ft_idx)
cell.border = box_border
if elem["id"] == "wind":
sql = f"SELECT {m_id}_u, {m_id}_v FROM ml_ready_weather_data WHERE lat={ref_lat} AND lon={ref_lon} AND ft={ft}"
cursor.execute(sql)
res = cursor.fetchone()
cell.value = calculate_wind_dir_from_uv(res[0], res[1]) if res else "-"
else:
sql = f"SELECT {db_col} FROM ml_ready_weather_data WHERE lat={ref_lat} AND lon={ref_lon} AND ft={ft}"
cursor.execute(sql)
res = cursor.fetchone()
cell.value = res[0] if res and res[0] is not None else "-"
current_row += 1
current_row += 2
conn.close()
wb.remove(default_sheet)
wb.save(save_path)
QMessageBox.information(self, "成功", f"ML DBからデータを抽出し、Excelを出力しました。\n{save_path}")
except Exception as e:
QMessageBox.critical(self, "エラー", str(e))
# ==========================================
# [Tab 3] 1kmメッシュマップ分析
# ==========================================
def setup_tab_3_vis(self):
layout = QHBoxLayout(self.tab3_vis)
left_panel = QWidget()
left_layout = QVBoxLayout(left_panel)
left_layout.addWidget(QLabel("【メッシュ描画設定】 (※次期フェーズ実装)"))
layout.addWidget(left_panel, 2)
right_panel = QWidget()
right_layout = QVBoxLayout(right_panel)
layout.addWidget(right_panel, 8)
if __name__ == '__main__':
app = QApplication(sys.argv)
window = IntegratedWeatherSystem()
window.show()
sys.exit(app.exec())
未分類
コメント