sn-da-large-file-analysis

大規模 Excel 分析スキル

必須ルール

合計行数が 10,000 行以上の場合、このスキルの方法を必ず使用してください。

データ規模	読み込み方法	理由
10k 行未満	pd.read_excel() で直接読み込み	メモリ負荷がありません
10k～100k 行	pd.read_excel() → Parquet に変換 → 分析には pd.read_parquet() を使用	読み込みの繰り返しによる遅延を回避できます
100k～1M 行	openpyxl read_only + iter_rows によるストリーミング → Parquet	pd.read_excel() ではメモリ不足またはタイムアウトが発生します
1M 行超	ストリーミング読み込み + 複数シートへの分割（Excel の 1 シートあたりの最大行数は 1,048,576 行）	チャンクに分割する必要があります

禁止事項：

• 100k 行以上のファイルをすべて読み込むために pd.read_excel() を使用しないでください
• fc-list、find ... fonts でフォントを検索したり、pip install でパッケージをインストールしたりしないでください
• 大規模な DataFrame で df.iterrows() を使用しないでください（itertuples() またはベクトル化された操作を使用してください）
• ベクトル化できる操作に df.apply(lambda...) を使用しないでください

---

環境のセットアップ

import pandas as pd
import numpy as np
import os
import gc

pd.options.mode.copy_on_write = True

# CJK font setup (fixed paths — do NOT search for fonts)
# ⚠️ Copy this block as-is. Do NOT use fc-list, find, subprocess, or glob to locate fonts.
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm

_FONT_PATHS = [
    '/mnt/afs_agents/SimHei.ttf',
    '/mnt/afs_agents/mnt/data/SimHei.ttf',
    os.path.expanduser('~/.fonts/SimHei.ttf'),
    '/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc',
    '/usr/share/fonts/SimHei.ttf',
]
for _p in _FONT_PATHS:
    if os.path.exists(_p):
        fm.fontManager.addfont(_p)
        matplotlib.rcParams['font.family'] = fm.FontProperties(fname=_p).get_name()
        break
matplotlib.rcParams['axes.unicode_minus'] = False

---

コア手法 1：ファイル構造を調べる（データを読み込まずに）

大規模なファイルを操作する前に、データをメモリに読み込まずにシートと行数を確認してください。

import openpyxl

def inspect_excel(file_path):
    """Stream-inspect Excel structure. Returns {sheet_name: {rows, columns}}."""
    wb = openpyxl.load_workbook(file_path, read_only=True, data_only=True)
    info = {}
    for name in wb.sheetnames:
        ws = wb[name]
        row_count = 0
        header = None
        for i, row in enumerate(ws.iter_rows(values_only=True)):
            if i == 0:
                header = [str(c) if c is not None else f"Col_{j}" for j, c in enumerate(row)]
            else:
                row_count += 1
        info[name] = {"rows": row_count, "columns": header}
    wb.close()
    return info

# Usage
file_info = inspect_excel(file_path)
for sheet, meta in file_info.items():
    print(f"Sheet '{sheet}': {meta['rows']} rows, {len(meta['columns'])} cols")
    print(f"  Columns: {meta['columns'][:10]}...")
total_rows = sum(m['rows'] for m in file_info.values())
print(f"Total rows: {total_rows}")

---

コア手法 2：ストリーミング読み込み → Parquet（100k 行以上）

100k 行以上のファイルでは、pd.read_excel() を決して使用しないでください。openpyxl のストリーミング読み込みで Parquet に変換してください。

import openpyxl
import pyarrow as pa
import pyarrow.parquet as pq

def stream_excel_to_parquet(excel_path, parquet_path, sheet_name=None, chunk_size=50000):
    """Stream Excel rows to Parquet with constant memory usage.

    All columns are cast to string to avoid cross-chunk schema mismatches
    (Excel mixed-type columns may be all-None in some chunks, causing PyArrow
    to infer null type instead of string). Convert numeric columns after loading
    Parquet with pd.to_numeric() as needed.
    """
    wb = openpyxl.load_workbook(excel_path, read_only=True, data_only=True)
    ws = wb[sheet_name] if sheet_name else wb.active

    header = None
    writer = None
    chunk_rows = []
    total_written = 0

    def _flush(rows):
        nonlocal writer
        table = pa.table({
            col: pa.array(
                [str(r[idx]) if r[idx] is not None else None for r in rows],
                type=pa.string(),
            )
            for idx, col in enumerate(header)
        })
        if writer is None:
            writer = pq.ParquetWriter(parquet_path, table.schema)
        writer.write_table(table)

    for i, row in enumerate(ws.iter_rows(values_only=True)):
        if i == 0:
            header = [str(c) if c is not None else f"Col_{j}" for j, c in enumerate(row)]
            continue

        chunk_rows.append(list(row))

        if len(chunk_rows) >= chunk_size:
            _flush(chunk_rows)
            total_written += len(chunk_rows)
            print(f"  Written {total_written:,} rows...")
            chunk_rows = []
            gc.collect()

    if chunk_rows:
        _flush(chunk_rows)
        total_written += len(chunk_rows)

    if writer:
        writer.close()
    wb.close()
    print(f"Done: {total_written:,} rows -> {parquet_path}")
    return total_written

---

コア手法 3：中規模ファイルの Parquet 変換（10k～100k 行）

10k～100k 行の場合、pd.read_excel() でメモリ不足にはなりませんが、繰り返し分析する場合は Parquet のほうがはるかに高速です。

def convert_excel_to_parquet(excel_path, parquet_path, sheet_name=0):
    """Medium file: pd.read_excel -> Parquet cache."""
    if os.path.exists(parquet_path):
        print(f"Cache exists: {parquet_path}")
        return
    df = pd.read_excel(excel_path, sheet_name=sheet_name)
    df.columns = df.columns.astype(str)
    df.to_parquet(parquet_path, engine='pyarrow', compression='snappy')
    row_count = len(df)
    del df
    gc.collect()
    print(f"Converted {row_count:,} rows -> {parquet_path}")

---

コア手法 4：メモリの最適化（型のダウンキャスト）

Parquet の読み込み後、メモリ使用量をさらに削減します。

def optimize_dtypes(df):
    """Auto-downcast numeric types + convert low-cardinality strings to Category.
    Typically saves 50-80% memory."""
    start_mb = df.memory_usage(deep=True).sum() / 1024**2

    for col in df.select_dtypes(include=['int64', 'int32']).columns:
        c_min, c_max = df[col].min(), df[col].max()
        if c_min >= np.iinfo(np.int8).min and c_max <= np.iinfo(np.int8).max:
            df[col] = df[col].astype(np.int8)
        elif c_min >= np.iinfo(np.int16).min and c_max <= np.iinfo(np.int16).max:
            df[col] = df[col].astype(np.int16)
        elif c_min >= np.iinfo(np.int32).min and c_max <= np.iinfo(np.int32).max:
            df[col] = df[col].astype(np.int32)

    for col in df.select_dtypes(include=['float64']).columns:
        df[col] = df[col].astype(np.float32)

    for col in df.select_dtypes(include=['object', 'string']).columns:
        if df[col].nunique() / max(len(df), 1) < 0.5:
            df[col] = df[col].astype('category')

    end_mb = df.memory_usage(deep=True).sum() / 1024**2
    print(f"Memory: {start_mb:.1f} MB -> {end_mb:.1f} MB (saved {(1 - end_mb/start_mb)*100:.0f}%)")
    return df

---

コア手法 5：大規模ファイルの書き込み

def write_large_excel(df, output_path, sheet_name="Sheet1"):
    """Auto-select write strategy based on data size."""
    total_cells = len(df) * len(df.columns)

    if len(df) > 1_000_000:
        csv_path = output_path.rsplit('.', 1)[0] + '.csv'
        df.to_csv(csv_path, index=False)
        print(f"Over 1M rows — exported as CSV: {csv_path}")
        return csv_path

    if total_cells > 50_000:
        from openpyxl import Workbook
        from openpyxl.cell import WriteOnlyCell

        wb = Workbook(write_only=True)
        ws = wb.create_sheet(title=sheet_name)
        ws.append(list(df.columns))
        for idx, row in enumerate(df.itertuples(index=False)):
            ws.append([None if pd.isna(v) else v for v in row])
            if (idx + 1) % 100_000 == 0:
                print(f"  Written {idx + 1:,} rows...")
        wb.save(output_path)
        wb.close()
        print(f"write_only mode: {len(df):,} rows -> {output_path}")
    else:
        df.to_excel(output_path, index=False, sheet_name=sheet_name)
        print(f"Standard write: {len(df):,} rows -> {output_path}")
    return output_path

---

例 1：10 万行のテーブル — 列の分布 + グラフ

シナリオ：ユーザーが 10 万行の売上 Excel ファイルを持っており、棒グラフで地域別の売上分布を確認したいとします。

import pandas as pd
import os, gc

excel_path = "sales_100k.xlsx"
parquet_path = "sales_100k.parquet"

# === Step 1: Inspect structure ===
file_info = inspect_excel(excel_path)
total_rows = sum(m['rows'] for m in file_info.values())
print(f"Total rows: {total_rows}")

# === Step 2: Choose read strategy by row count ===
if total_rows >= 100_000:
    stream_excel_to_parquet(excel_path, parquet_path)
else:
    convert_excel_to_parquet(excel_path, parquet_path)

# === Step 3: Load Parquet + optimize memory ===
df = pd.read_parquet(parquet_path)
df = optimize_dtypes(df)
print(f"Shape: {df.shape}")
print(df.head(3))

# === Step 4: Analysis ===
region_sales = df.groupby('Region')['Sales'].sum().sort_values(ascending=False)
print(region_sales)

# === Step 5: Visualization ===
fig, ax = plt.subplots(figsize=(10, 6))
region_sales.plot(kind='bar', ax=ax, color='#4C72B0')
ax.set_title('Sales by Region')
ax.set_ylabel('Sales')
plt.tight_layout()
plt.savefig('region_sales.png', dpi=150, bbox_inches='tight')
plt.show()

# === Step 6: Cleanup ===
del df
gc.collect()

---

例 2：100 万行のテーブル — ストリーミング読み込み + フィルタリング + エクスポート

シナリオ：ユーザーが 100 万行の取引ログを持っており、金額が 10,000 を超えるレコードをエクスポートしたいとします。

import pandas as pd
import os, gc

excel_path = "transactions_1m.xlsx"
parquet_path = "transactions_1m.parquet"

# === Step 1: Stream to Parquet (1M rows — MUST use streaming, never pd.read_excel) ===
stream_excel_to_parquet(excel_path, parquet_path, chunk_size=50000)

# === Step 2: Load only needed columns (saves memory) ===
df = pd.read_parquet(parquet_path, columns=['TransactionID', 'Amount', 'Date', 'Type'])
df = optimize_dtypes(df)
print(f"Shape: {df.shape}, Memory: {df.memory_usage(deep=True).sum()/1024**2:.1f} MB")

# === Step 3: Vectorized filtering (never use apply/iterrows) ===
mask = df['Amount'] > 10000
high_value = df[mask].copy()
print(f"Filtered: {len(high_value):,} / {len(df):,} rows")

# === Step 4: Export ===
output_path = write_large_excel(high_value, 'high_value_transactions.xlsx')

# === Step 5: Cleanup ===
del df, high_value
gc.collect()

---

ベクトル化操作の早見表

大規模なファイルでは、低速な操作は決して使用せず、ベクトル化された代替手段を使用してください。

低速（禁止）	高速（こちらを使用）
df.apply(lambda x: x*2)	df['col'] * 2
df.iterrows()	df.itertuples(index=False)
for i in range(len(df)): df.iloc[i]	ベクトル化されたブールインデックス df[mask]
df['a'].map(lambda x: 'Y' if x>0 else 'N')	np.where(df['a']>0, 'Y', 'N')
df.groupby('a').apply(custom_func)	df.groupby('a').agg({'b':'sum','c':'mean'})

---

メモリ使用量の見積もり

メモリ不足を避けるため、読み込み前にメモリ使用量を見積もってください。

Estimated MB ≈ rows × cols × 8 / 1024² (numeric columns)
Estimated MB ≈ rows × cols × 50 / 1024² (with text columns)

行数	20 列（数値）	20 列（テキストを含む）
100k	約 15 MB	約 95 MB
500k	約 76 MB	約 477 MB
1M	約 153 MB	約 953 MB

推定メモリ使用量が利用可能な RAM の 80% を超える場合は、列を選択して読み込む（pd.read_parquet(columns=[...])）か、チャンク単位で処理してください。

---

ベストプラクティス

1. Parquet が最適です：10k 行以上のファイルは、分析前に Parquet に変換してください。Parquet は列指向の読み込みと圧縮保存に対応しており、xlsx より 10～50 倍高速に読み込めます。
2. ストリーミングを安全策として使います：100k 行以上の場合は、必ず openpyxl の read_only + iter_rows を使用してください。ファイル全体の読み込みに pd.read_excel() は決して使用しないでください。
3. メモリを速やかに解放します：中間 DataFrame を処理するたびに del df; gc.collect() を実行してください。
4. Excel の行数制限：1 シートあたりの最大行数は 1,048,576 行です。上限を超える場合は複数のシートに自動分割するか、CSV としてエクスポートしてください。
5. 出力には write_only を使用します：50k セルを超えるファイルでは、openpyxl Workbook(write_only=True) を使用してください。