[学習記録]初代業務ツール

import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import pandas as pd
import numpy as np
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, Alignment
import os
from datetime import datetime
from scipy import signal 


def load_csv(csv_path):
    # 判定用に必要なセルだけ読む
    check_df = pd.read_csv(
        csv_path,
        header=None,
        usecols=[2],      # 3列目
        nrows=4           # 4行目まで読む
    )
    v4 = pd.to_numeric(check_df.iloc[3, 0], errors="coerce")

    # 条件分岐
    if  v4 == 0:
        usecols = [3, 4, 5]
    else:
        usecols = [2, 3, 4]

    # 本体読み込み
    df = pd.read_csv(csv_path, header=None, usecols=usecols)
    df.columns = ["X(mG)", "Y(mG)", "Z(mG)"]
    df = df.apply(pd.to_numeric, errors="coerce")

    return df

def apply_lpf(data, fs, fc, order=4):
    nyq = fs / 2
    normal_fc = fc / nyq
    b, a = signal.butter(order, normal_fc, btype="low")
    return signal.filtfilt(b, a, data)

def write_header(ws, columns, start_row, col_offset, mode):
    for j, col_name in enumerate(columns):
        if col_name.startswith("time"):
            if mode in ["FFT", "正規化"]:
                ws.cell(row=start_row, column=col_offset + j, value="frequency(Hz)")
            else:
                ws.cell(row=start_row, column=col_offset + j, value=f"time_{mode}")
        else:
            ws.cell(row=start_row, column=col_offset + j, value=col_name)


def write_extract(ws, df_tail, start_row, col_offset):
    for r in range(len(df_tail)):
        excel_row = start_row + r + 1
        ws.cell(row=excel_row, column=col_offset,
                value=df_tail["time(msec)_単位変換"].iat[r])
        for j, axis in enumerate(["X(G)", "Y(G)", "Z(G)"]):
            ws.cell(row=excel_row,
                    column=col_offset + j + 1,
                    value=df_tail[axis].iat[r])


def write_dc_remove(ws, start_row, n, extract_col, col_offset):
    # time列（そのまま参照）
    for r in range(n):
        excel_row = start_row + r + 1
        ws.cell(row=excel_row, column=col_offset,
                value=f"={ws.cell(row=excel_row, column=extract_col).coordinate}")

    # X,Y,Z
    for j in range(3):
        src_col = extract_col + j + 1
        dc_cell = ws.cell(row=start_row + n + 2,
                          column=col_offset + j + 1)
        dc_cell.value = (
            f"=AVERAGE({ws.cell(row=start_row+1, column=src_col).coordinate}:"
            f"{ws.cell(row=start_row+n, column=src_col).coordinate})"
        )

        for r in range(n):
            excel_row = start_row + r + 1
            ws.cell(row=excel_row,
                    column=col_offset + j + 1,
                    value=f"={ws.cell(row=excel_row, column=src_col).coordinate}-{dc_cell.coordinate}")


def write_window(ws, start_row, n, col_offset, dc_col):
    for r in range(n):
        excel_row = start_row + r + 1
        ws.cell(row=excel_row, column=col_offset,
                value=f"=0.5*(1-COS(2*PI()*{r}/({n}-1)))")

        for j in range(3):
            ws.cell(row=excel_row,
                    column=col_offset + j + 1,
                    value=f"={ws.cell(row=excel_row, column=dc_col + j + 1).coordinate}"
                          f"*{ws.cell(row=excel_row, column=col_offset).coordinate}")


def write_fft_empty(ws, start_row, n, col_offset, width):
    for j in range(width):
        for r in range(n):
            ws.cell(row=start_row + r + 1,
                    column=col_offset + j,
                    value=None)


def write_normalize(ws, start_row, n, fft_col, col_offset, width):
    for j in range(width):
        for r in range(n):
            excel_row = start_row + r + 1
            src = ws.cell(row=excel_row, column=fft_col + j).coordinate
            if j == 0:
                ws.cell(row=excel_row, column=col_offset + j, value=f"={src}")
            else:
                ws.cell(row=excel_row, column=col_offset + j, value=f"={src}*(4/{n})")


# ================== GUI ==================
root = tk.Tk()
root.title("CSV → Excel 横並び出力（単位変換＋加工）")
root.geometry("800x450")

col_shift_var = tk.BooleanVar(value=False)

# --- 抽出行数 ---
ttk.Label(root, text="抽出行数(n):").pack(anchor="w", padx=10, pady=5)
n_var = tk.StringVar(value="5000")
ttk.Entry(root, textvariable=n_var, width=20).pack(padx=10)

# --- LPF設定 ---
lpf_var = tk.BooleanVar(value=False)
fc_var = tk.StringVar(value="400")

lpf_frame = ttk.Frame(root)
lpf_frame.pack(fill="x", pady=10)

ttk.Checkbutton(
    lpf_frame,
    text="ローパスフィルタを適用（Butterworth）",
    variable=lpf_var
).pack(anchor="center")

fc_frame = ttk.Frame(root)
fc_frame.pack()

ttk.Label(fc_frame, text="LPFカットオフ周波数 [Hz]").pack(side="left", padx=5)

fc_entry = ttk.Entry(fc_frame, textvariable=fc_var, width=10)
fc_entry.pack(side="left")

def update_fc_state(*args):
    fc_entry.config(state="normal" if lpf_var.get() else "disabled")

lpf_var.trace_add("write", update_fc_state)
update_fc_state()




# --- サンプリング周波数設定 ---
fs_var = tk.StringVar(value="2000")

fs_frame = ttk.Frame(root)
fs_frame.pack()

ttk.Label(fs_frame, text="サンプリング周波数 fs [Hz]").pack(side="left", padx=5)
fs_entry = ttk.Entry(fs_frame, textvariable=fs_var, width=10)
fs_entry.pack(side="left")

def update_fs_state(*args):
    fs_entry.config(state="normal" if lpf_var.get() else "disabled")

lpf_var.trace_add("write", update_fs_state)
update_fs_state()

# --- CSV入力 ---
ttk.Label(root, text="CSV入力").pack(anchor="w", padx=10, pady=5)
csv_path_var1 = tk.StringVar()
ttk.Entry(root, textvariable=csv_path_var1, width=70).pack(padx=10)

def select_csv1():
    path = filedialog.askopenfilename(filetypes=[("CSV", "*.csv")])
    if path:
        csv_path_var1.set(path)

ttk.Button(root, text="参照", command=select_csv1).pack(pady=5)

# --- 出力Excel ---
ttk.Label(root, text="出力Excelファイル").pack(anchor="w", padx=10, pady=5)
output_var = tk.StringVar(value="output/加工データ.xlsx")
ttk.Entry(root, textvariable=output_var, width=70).pack(padx=10)

def select_output():
    path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel", "*.xlsx")])
    if path:
        output_var.set(path)

ttk.Button(root, text="参照", command=select_output).pack(pady=5)

# ================== CSV→Excel変換 ==================
def convert_csv_to_excel(csv_paths,  output_path, n=5500): # n=処理を施す行数
    if not csv_paths or not any(csv_paths):
        messagebox.showwarning("警告", "CSVを選択してください")
        return

    #  常に新規（上書き）
    wb = Workbook()
    if wb.active.title == "Sheet":
        wb.remove(wb.active)

    ws = wb.create_sheet()

    # 基本位置（1-index）
    start_row = 4
    start_col = 1

    # 加工セット定義
    tail_sets = ["抽出", "DC除去", "窓関数", "FFT", "正規化"]

    for csv_path in csv_paths:
        if not csv_path:
            continue

        # CSVタイトル取得
        csv_title = os.path.splitext(os.path.basename(csv_path))[0]

        # タイトル（CSV名） ※ start_col を使うと複数CSV対応
        ws.cell(row=1, column=1, value=f"CSV: {csv_title}")
        ws.cell(row=1, column=1).font = Font(bold=True)
        ws.cell(row=2, column=1, value=datetime.now().strftime("作成日: %Y-%m-%d"))
        # ===== CSV 読み込み（X,Y,Zのみ）=====
        df = load_csv(csv_path) 

        # ===== time 列を後付け（0〜N-1）=====
        df.insert(0, "time(msec)", np.arange(len(df)))

        # ===== 元データコピー =====
        df_input = df.copy()

        # ===== 単位変換 ===== 0.1[mG]→1[G]
        df_conv = df.copy()
        df_conv["X(mG)"] /= 10000
        df_conv["Y(mG)"] /= 10000
        df_conv["Z(mG)"] /= 10000
        df_conv.rename(columns={
            "time(msec)": "time(msec)_単位変換",
            "X(mG)": "X(G)",
            "Y(mG)": "Y(G)",
            "Z(mG)": "Z(G)"
        }, inplace=True)

        # ===== ローパスフィルタ=====
        if lpf_var.get():
            fs = float(fs_var.get())  # GUI から取得
            fc = float(fc_var.get())
            for axis in ["X(G)", "Y(G)", "Z(G)"]:
                df_conv[axis] = apply_lpf(df_conv[axis].values, fs, fc)

        # ===== time列 0〜N に置換 =====
        df_conv["time(msec)_単位変換"] = np.arange(len(df_conv))

        # ===== tail 抽出（単位変換後） =====
        df_tail = df_conv.tail(n).reset_index(drop=True)

        # ===== Excel へ元データ＋単位変換データ =====
        df_all = pd.concat([df_input, pd.DataFrame(np.nan, index=df_input.index, columns=[""]), df_conv], axis=1)
        # df_all を start_col に書き込む（ヘッダ含む）
        for r_idx, row in enumerate(dataframe_to_rows(df_all, index=False, header=True), start_row):
            for c_idx, value in enumerate(row):
                ws.cell(row=r_idx, column=start_col + c_idx, value=value)

        # block_width : 各加工ブロックの列数（df_tail の列数）
        block_width = len(df_tail.columns)  # 通常 4 (time, X, Y, Z)

        # ===== 各モード処理（整理版）=====
        MODE_FUNCS = ["抽出", "DC除去", "窓関数", "FFT", "正規化"]

        extract_col = start_col + df_all.shape[1] + 1

        for i, mode in enumerate(MODE_FUNCS):
            col_offset = start_col + df_all.shape[1] + 1 + i * (block_width + 1)

            if mode == "抽出":
                write_extract(ws, df_tail, start_row, col_offset)

            elif mode == "DC除去":
                write_dc_remove(ws, start_row, n, extract_col, col_offset)

            #elif mode == "窓関数":
                #write_window(ws, start_row, n, col_offset, extract_col)

            elif mode == "窓関数":
                dc_i = MODE_FUNCS.index("DC除去")
                dc_col = start_col + df_all.shape[1] + 1 + dc_i * (block_width + 1)
                write_window(ws, start_row, n, col_offset, dc_col)

            elif mode == "FFT":
                write_fft_empty(ws, start_row, n, col_offset, block_width)

            elif mode == "正規化":
                fft_i = MODE_FUNCS.index("FFT")
                fft_col = start_col + df_all.shape[1] + 1 + fft_i * (block_width + 1)
                write_normalize(ws, start_row, n, fft_col, col_offset, block_width)
                    # ★ ヘッダーは1列右にずらす
                write_header(ws, df_tail.columns, start_row, col_offset + 5, mode)

            write_header(ws, df_tail.columns, start_row, col_offset, mode)
            
        # 次CSVの開始位置を適切に移動
        # df_all の幅 + 各加工ブロック分の幅（各 block_width と空白1列ずつ）
        advance = df_all.shape[1] + len(tail_sets) * (block_width + 1) + 1
        start_col += advance

    # 列幅調整（必要に応じて範囲を広げてください）
    for col_idx in range(1, 200):  # 十分余裕を見た列数
        try:
            letter = ws.cell(row=start_row, column=col_idx).column_letter
            ws.column_dimensions[letter].width = 15
        except Exception:
            pass
    


    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    wb.save(output_path)
    messagebox.showinfo("完了", f"Excel出力完了:\n{output_path}")

# --- 実行ボタン ---
def run_conversion():
    try:
        n_value = int(n_var.get())
        if n_value <= 0:
            raise ValueError
    except ValueError:
        messagebox.showerror("エラー", "抽出行数(n)は正の整数で入力してください")
        return

    convert_csv_to_excel(
        [csv_path_var1.get()],
        output_var.get(),
        int(n_var.get())
    )

ttk.Button(root, text="変換実行", command=run_conversion).pack(pady=20)

root.mainloop()