経理のExcel作業をpandasで置き換える｜CSV一括取込・帳票整形・Excel出力

from pathlib import Path
import pandas as pd

data_dir = Path("data/2026-08")

# 同じ形式のCSVをまとめて1つのDataFrameにする
dfs = [pd.read_csv(f) for f in sorted(data_dir.glob("*.csv"))]
df = pd.concat(dfs, ignore_index=True)

dfs = []
for f in sorted(data_dir.glob("*.csv")):
    tmp = pd.read_csv(f)
    tmp["source"] = f.stem  # ファイル名を列として残す
    dfs.append(tmp)

df = pd.concat(dfs, ignore_index=True)

# read_csv の thousands で区切り記号を指定するだけ
df = pd.read_csv("shikin.csv", thousands=",")

df["金額"] = df["金額"].str.replace(",", "").astype("int64")

names = ["勘定科目コード", "勘定科目名", "金額"]
dtype = {"勘定科目コード": str, "勘定科目名": str, "金額": float}

df = pd.read_csv("moto.csv", names=names, dtype=dtype)

import mojimoji

# 全角を半角に（英数字・記号が対象、日本語はそのまま）
df["摘要"] = df["摘要"].apply(mojimoji.zen_to_han)

# 英数字は半角に、カナは全角のままにする
df["摘要"] = df["摘要"].apply(lambda x: mojimoji.zen_to_han(x, kana=False))

import pandas as pd

df = pd.DataFrame({
    "科目": ["現金", "売掛金", "商品"],
    "当期": [1000, 2000, 3000],
    "前期": [900, 1800, "―"],  # 文字列が混ざっている
})

df.sum(numeric_only=True)
# 当期    6000
# dtype: int64

df.dtypes  # 型を確認
df["前期"] = pd.to_numeric(df["前期"], errors="coerce")  # 変換できない値はNaNに

import pandas as pd

sheets = {
    "貸借対照表": bs_df,
    "損益計算書": pl_df,
    "部門別内訳": bumon_df,
}

with pd.ExcelWriter("gessou_2026-08.xlsx") as writer:
    for name, sheet_df in sheets.items():
        sheet_df.to_excel(writer, sheet_name=name, index=False)

def export_xlsx(input_path, output_path):
    dfs = pd.read_html(input_path)
    with pd.ExcelWriter(output_path) as writer:
        for i, d in enumerate(dfs, start=1):
            d.to_excel(writer, sheet_name=f"table{i}", index=False)

export_xlsx("kessan.html", "kessan.xlsx")

df.to_csv("shiwake_log.csv", mode="a", index=False, header=False)

from pathlib import Path
import mojimoji
import pandas as pd

data_dir = Path("data/2026-08")
dtype = {"勘定科目コード": str, "勘定科目名": str}

# 1. 部門別CSVを一括取込（桁区切りも同時に解決）
dfs = []
for f in sorted(data_dir.glob("*.csv")):
    tmp = pd.read_csv(f, thousands=",", dtype=dtype)
    tmp["部門"] = f.stem
    dfs.append(tmp)

df = pd.concat(dfs, ignore_index=True)

# 2. 表記ゆれをならす
df["摘要"] = df["摘要"].apply(lambda x: mojimoji.zen_to_han(x, kana=False))

# 3. 部門別に集計
bumon_df = df.groupby("部門").sum(numeric_only=True).reset_index()

# 4. Excelに出力
with pd.ExcelWriter("gessou_2026-08.xlsx") as writer:
    df.to_excel(writer, sheet_name="明細", index=False)
    bumon_df.to_excel(writer, sheet_name="部門別集計", index=False)