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
DEFAULT_DB_PATH = os.path.join(os.getcwd(), "sapporo_ml_ready_archive.db")
# ==========================================
# バックグラウンド処理: ワイドフォーマットDB生成/上書きスレッド
# ==========================================
class DBGeneratorThread(QThread):
progress = pyqtSignal(int, str)
finished = pyqtSignal(str)
error = pyqtSignal(str)
def __init__(self, npz_dir, db_path, target_coords):
super().__init__()
self.npz_dir = npz_dir
self.db_path = db_path
self.target_coords = target_coords # [(lat, lon), (lat, lon), ...]
def run(self):
if not self.target_coords:
self.error.emit("抽出対象の座標がありません。先にCSVを読み込んでください。")
return
try:
self.progress.emit(5, "ML Readyデータベースの初期化中...")
conn = sqlite3.connect(self.db_path)
cursor = conn.cursor()
# 【上書き追加対応】モデルごとに縦に積み、横に要素を広げる超ワイドフォーマット
cursor.execute('''
CREATE TABLE IF NOT EXISTS weather_master (
id INTEGER PRIMARY KEY AUTOINCREMENT,
model_type TEXT, init_time TEXT, valid_time TEXT, ft INTEGER,
lat REAL, lon REAL,
surf_temp REAL, surf_u REAL, surf_v REAL, surf_rain REAL, surf_snow REAL,
cloud_tot REAL, cloud_low REAL, cloud_mid REAL, cloud_high REAL,
gh_975 REAL, gh_950 REAL, gh_925 REAL, gh_850 REAL, gh_700 REAL, gh_500 REAL,
u_975 REAL, u_950 REAL, u_925 REAL, u_850 REAL, u_700 REAL, u_500 REAL,
v_975 REAL, v_950 REAL, v_925 REAL, v_850 REAL, v_700 REAL, v_500 REAL,
tddep_950 REAL, tddep_925 REAL, tddep_850 REAL, tddep_700 REAL,
ept_950 REAL, ept_925 REAL, ept_850 REAL,
w_700 REAL, vort_500 REAL,
ssi_500_850 REAL, ssi_700_850 REAL, ssi_700_925 REAL,
UNIQUE(model_type, lat, lon, valid_time, ft)
)
''')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_time_loc ON weather_master (valid_time, lat, lon)')
npz_files = glob.glob(os.path.join(self.npz_dir, "*.npz"))
if not npz_files:
self.error.emit(f"エラー: {self.npz_dir} に .npz ファイルが存在しません。")
return
total_files = len(npz_files)
insert_list = []
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"
model_type = f"{m_model}_{m_mode}"
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)
dt_valid = dt_init + timedelta(hours=ft_val)
str_init = dt_init.strftime("%Y-%m-%d %H:%M:%S")
str_valid = dt_valid.strftime("%Y-%m-%d %H:%M:%S")
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_v(key_opts, sy, sx):
k = next((x for x in key_opts if x in files), None)
if not k: return None
arr = data[k]
try:
v = arr[sy, sx] if arr.ndim == 2 else arr[sx, sy]
return None if np.isnan(v) or v > 200000 or v < -10000 else round(float(v), 3)
except: return None
for t_lat, t_lon in self.target_coords:
# 最寄りグリッドの探索
dist = (lon_2d - t_lon)**2 + (lat_2d - t_lat)**2
sy, sx = np.unravel_index(np.argmin(dist), dist.shape)
# データ抽出
row = {
"model": model_type, "init": str_init, "valid": str_valid, "ft": ft_val,
"lat": round(t_lat, 4), "lon": round(t_lon, 4),
"s_t": get_v(['t2m', 'tmp', 'temp'], sy, sx),
"s_u": get_v(['u10', 'u', 'ugrd'], sy, sx),
"s_v": get_v(['v10', 'v', 'vgrd'], sy, sx),
"s_r": get_v(['precip', 'tp', 'apcp'], sy, sx),
"s_s": get_v(['snow', 'weasd', 'snod', 'asnow'], sy, sx),
"c_t": get_v(['tcc'], sy, sx), "c_l": get_v(['lcc'], sy, sx),
"c_m": get_v(['mcc'], sy, sx), "c_h": get_v(['hcc'], sy, sx),
}
# 上空要素
for lvl in [975, 950, 925, 850, 700, 500]:
row[f"gh_{lvl}"] = get_v([f'gh{lvl}', f'z{lvl}'], sy, sx)
row[f"u_{lvl}"] = get_v([f'u{lvl}'], sy, sx)
row[f"v_{lvl}"] = get_v([f'v{lvl}'], sy, sx)
if lvl in [950, 925, 850, 700]: row[f"td_{lvl}"] = get_v([f'tddep{lvl}'], sy, sx)
if lvl in [950, 925, 850]: row[f"ep_{lvl}"] = get_v([f'ep{lvl}'], sy, sx)
row["w_700"] = get_v(['w700'], sy, sx)
row["vort_500"] = get_v(['vort500'], sy, sx)
# SSI近似計算 (T_upper - T_lower) ※正確な断熱上昇ではない簡易指標として
t500 = get_v(['t500'], sy, sx); t700 = get_v(['t700'], sy, sx)
t850 = get_v(['t850'], sy, sx); t925 = get_v(['t925'], sy, sx)
row["ssi_500_850"] = round(t500 - t850, 3) if t500 is not None and t850 is not None else None
row["ssi_700_850"] = round(t700 - t850, 3) if t700 is not None and t850 is not None else None
row["ssi_700_925"] = round(t700 - t925, 3) if t700 is not None and t925 is not None else None
insert_list.append((
row["model"], row["init"], row["valid"], row["ft"], row["lat"], row["lon"],
row["s_t"], row["s_u"], row["s_v"], row["s_r"], row["s_s"],
row["c_t"], row["c_l"], row["c_m"], row["c_h"],
row["gh_975"], row["gh_950"], row["gh_925"], row["gh_850"], row["gh_700"], row["gh_500"],
row["u_975"], row["u_950"], row["u_925"], row["u_850"], row["u_700"], row["u_500"],
row["v_975"], row["v_950"], row["v_925"], row["v_850"], row["v_700"], row["v_500"],
row["td_950"], row["td_925"], row["td_850"], row["td_700"],
row["ep_950"], row["ep_925"], row["ep_850"],
row["w_700"], row["vort_500"],
row["ssi_500_850"], row["ssi_700_850"], row["ssi_700_925"]
))
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"抽出中... {i}/{total_files} ファイル")
self.progress.emit(85, "DBへ上書き保存 (UPSERT) 中...")
# INSERT OR REPLACE で自動上書き
cursor.executemany('''
INSERT OR REPLACE INTO weather_master (
model_type, init_time, valid_time, ft, lat, lon,
surf_temp, surf_u, surf_v, surf_rain, surf_snow,
cloud_tot, cloud_low, cloud_mid, cloud_high,
gh_975, gh_950, gh_925, gh_850, gh_700, gh_500,
u_975, u_950, u_925, u_850, u_700, u_500,
v_975, v_950, v_925, v_850, v_700, v_500,
tddep_950, tddep_925, tddep_850, tddep_700,
ept_950, ept_925, ept_850,
w_700, vort_500,
ssi_500_850, ssi_700_850, ssi_700_925
) VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?, ?,?,?, ?,?, ?,?,?)
''', insert_list)
conn.commit()
conn.close()
self.progress.emit(100, "生成完了")
self.finished.emit(f"✓ DB更新完了: {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 8.0 - 超ワイドDB & 上書き追加対応)")
self.resize(1400, 900)
self.setStyleSheet("background-color: #2b2b2b; color: #e0e0e0; font-family: 'MS Gothic';")
self.current_csv_data = None
self.target_coords = []
self.current_db_path = DEFAULT_DB_PATH
self.current_npz_dir = os.path.join(os.getcwd(), "gpv_cache_npz")
self.current_page = 1
self.records_per_page = 500
self.total_records = 0
self.init_ui()
def init_ui(self):
central_widget = QWidget()
self.setCentralWidget(central_widget)
main_layout = QVBoxLayout(central_widget)
# --- 共通パス設定エリア ---
path_group = QGroupBox("📁 プロジェクト設定 (CSV・NPZ・DB)")
path_group.setStyleSheet("QGroupBox { border: 1px solid #555; font-weight: bold; }")
path_layout = QGridLayout(path_group)
self.btn_load_csv = QPushButton("1. 観測地点CSVを読込 (必須)")
self.btn_load_csv.setStyleSheet("background-color: #2980b9; padding: 6px;")
self.btn_load_csv.clicked.connect(self.load_csv_data)
self.lbl_csv_status = QLabel("CSV: 未読込 (抽出対象座標が未定です)")
self.btn_sel_npz = QPushButton("2. NPZフォルダを選択")
self.btn_sel_npz.setStyleSheet("background-color: #34495e; padding: 6px;")
self.btn_sel_npz.clicked.connect(self.select_npz_dir)
self.lbl_npz_dir = QLabel(f"NPZ: {self.current_npz_dir}")
self.btn_sel_db = QPushButton("3. 出力/接続先 DBを選択")
self.btn_sel_db.setStyleSheet("background-color: #34495e; padding: 6px;")
self.btn_sel_db.clicked.connect(self.select_db_file)
self.lbl_db_path = QLabel(f"DB: {os.path.basename(self.current_db_path)}")
self.lbl_db_path.setStyleSheet("color: #00fa9a; font-weight: bold;")
path_layout.addWidget(self.btn_load_csv, 0, 0)
path_layout.addWidget(self.lbl_csv_status, 0, 1)
path_layout.addWidget(self.btn_sel_npz, 1, 0)
path_layout.addWidget(self.lbl_npz_dir, 1, 1)
path_layout.addWidget(self.btn_sel_db, 2, 0)
path_layout.addWidget(self.lbl_db_path, 2, 1)
main_layout.addWidget(path_group)
# --- タブエリア ---
self.tabs = QTabWidget()
self.tabs.setStyleSheet("""
QTabBar::tab { background-color: #3c3f41; padding: 10px; font-weight: bold; width: 250px; 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.tabs.addTab(self.tab1_db, "🗄 [Tab 1] DB生成＆要素別ビューワー")
self.tabs.addTab(self.tab2_export, "📋 [Tab 2] Excel帳票出力")
main_layout.addWidget(self.tabs)
self.setup_tab_1_db()
self.setup_tab_2_export()
# ==========================================
# [Tab 1] DB生成 ＆ 要素別ビューワー
# ==========================================
def setup_tab_1_db(self):
layout = QVBoxLayout(self.tab1_db)
# --- DB生成コントロール ---
gen_layout = QHBoxLayout()
self.btn_generate_db = QPushButton("⚡ 本番: NPZからDBへ上書き抽出 (UPSERT)")
self.btn_generate_db.setStyleSheet("background-color: #c0392b; font-size: 12pt; font-weight: bold; padding: 10px;")
self.btn_generate_db.clicked.connect(self.start_db_generation)
gen_layout.addWidget(self.btn_generate_db)
self.progress_bar = QProgressBar()
self.progress_bar.setVisible(False)
gen_layout.addWidget(self.progress_bar)
self.lbl_status = QLabel("ステータス: 待機中")
gen_layout.addWidget(self.lbl_status)
layout.addLayout(gen_layout)
# --- テーブル閲覧コントロール ---
view_group = QGroupBox("🔍 エクセルライク抽出ビューワー")
view_layout = QVBoxLayout(view_group)
filter_layout = QHBoxLayout()
filter_layout.addWidget(QLabel("表示カテゴリ:"))
self.combo_category = QComboBox()
self.combo_category.addItems([
"基本情報 (時間・座標)", "地上要素 (気温・風・降水・雲)",
"高度面 (975〜500hPa)", "上空風向風速 (975〜500hPa)",
"湿数・相当温位", "安定度(SSI)・上昇流・渦度", "全て表示"
])
self.combo_category.currentTextChanged.connect(self.reload_current_page)
filter_layout.addWidget(self.combo_category)
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("◀ 前の500件")
self.btn_prev_page.clicked.connect(self.prev_page)
self.lbl_page_info = QLabel("ページ: 1 / 1")
self.btn_next_page = QPushButton("次の500件 ▶")
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)
view_layout.addLayout(filter_layout)
self.table_db = QTableWidget()
self.table_db.setStyleSheet("background-color: #1e1e1e; gridline-color: #444; color: #e0e0e0;")
view_layout.addWidget(self.table_db)
layout.addWidget(view_group)
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.target_coords = []
for _, row in self.current_csv_data.iterrows():
# 緯度・経度が3,4列目にあると想定 (iloc[2], iloc[3])
self.target_coords.append((float(row.iloc[2]), float(row.iloc[3])))
self.lbl_csv_status.setText(f"CSV: {os.path.basename(path)} ({len(self.target_coords)}地点を抽出対象に設定)")
self.lbl_csv_status.setStyleSheet("color: #3498db; font-weight: bold;")
except Exception as e: QMessageBox.critical(self, "エラー", str(e))
def select_npz_dir(self):
d = QFileDialog.getExistingDirectory(self, "NPZフォルダを選択", self.current_npz_dir)
if d:
self.current_npz_dir = d
self.lbl_npz_dir.setText(f"NPZ: {self.current_npz_dir}")
def select_db_file(self):
path, _ = QFileDialog.getSaveFileName(self, "DBファイルを選択/作成", self.current_db_path, "DB (*.db *.sqlite)")
if path:
self.current_db_path = path
self.lbl_db_path.setText(f"DB: {os.path.basename(self.current_db_path)}")
self.reload_current_page()
def start_db_generation(self):
if not self.target_coords:
QMessageBox.warning(self, "警告", "抽出する対象のCSVを読み込んでください。")
return
self.btn_generate_db.setEnabled(False)
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
self.db_thread = DBGeneratorThread(self.current_npz_dir, self.current_db_path, self.target_coords)
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)
QMessageBox.information(self, "生成完了", msg)
self.reload_current_page()
def on_db_error(self, msg):
self.btn_generate_db.setEnabled(True)
self.progress_bar.setVisible(False)
QMessageBox.critical(self, "エラー", msg)
# --- DB ビューワーロジック ---
def prev_page(self):
if self.current_page > 1: self.current_page -= 1; self.reload_current_page()
def next_page(self):
max_p = max(1, (self.total_records + self.records_per_page - 1) // self.records_per_page)
if self.current_page < max_p: self.current_page += 1; self.reload_current_page()
def reload_current_page(self):
if not os.path.exists(self.current_db_path): return
cat = self.combo_category.currentText()
# カテゴリに応じた抽出カラムの決定
base_cols = "id, model_type, valid_time, ft, lat, lon"
if "基本情報" in cat: cols = base_cols
elif "地上" in cat: cols = f"{base_cols}, surf_temp, surf_u, surf_v, surf_rain, surf_snow, cloud_tot, cloud_low, cloud_mid, cloud_high"
elif "高度面" in cat: cols = f"{base_cols}, gh_975, gh_950, gh_925, gh_850, gh_700, gh_500"
elif "上空風向" in cat: cols = f"{base_cols}, u_975, v_975, u_950, v_950, u_925, v_925, u_850, v_850, u_700, v_700, u_500, v_500"
elif "湿数" in cat: cols = f"{base_cols}, tddep_950, tddep_925, tddep_850, tddep_700, ept_950, ept_925, ept_850"
elif "安定度" in cat: cols = f"{base_cols}, ssi_500_850, ssi_700_850, ssi_700_925, w_700, vort_500"
else: cols = "*" # 全て表示
try:
conn = sqlite3.connect(self.current_db_path)
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM weather_master")
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 {cols} FROM weather_master ORDER BY valid_time, model_type 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("-" if val is None else str(val)))
self.table_db.resizeColumnsToContents()
self.lbl_page_info.setText(f"ページ: {self.current_page} / {max_pages}")
self.lbl_status.setText(f"ロード完了 (全 {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 as e:
self.lbl_status.setText(f"DB読込エラー: {e}")
# ==========================================
# [Tab 2] Excel帳票出力
# ==========================================
def setup_tab_2_export(self):
layout = QVBoxLayout(self.tab2_export)
self.btn_export_excel = QPushButton("📊 現在のDBから エクセル本番帳票を出力 (※機能はTab1に統合されました)")
self.btn_export_excel.setStyleSheet("background-color: #7f8c8d; font-size: 12pt; padding: 10px;")
self.btn_export_excel.setEnabled(False)
layout.addWidget(self.btn_export_excel)
layout.addWidget(QLabel("※機械学習用データの加工・確認は、Tab 1 の「エクセルライク抽出ビューワー」をご利用ください。"))
layout.addStretch()
if __name__ == '__main__':
app = QApplication(sys.argv)
window = IntegratedWeatherSystem()
window.show()
sys.exit(app.exec())
未分類
コメント