マルチインデックスの変換に対して値の整合性を保つツール

はじめに

こんにちは、品質管理部の沖です。

OS のバージョンの違いにおける製品への影響を調査・検証する業務において、複数の観点による比較のために行と列が多重になった表（マルチインデックスをもつ表）を用いることがあります。

記事 [1] では、マルチインデックスをもつエクセルファイルの表を効率的に作成するツールを紹介しました。記事 [2] では、マルチインデックスをもつエクセルファイルの差分をセル単位で抽出するツールを紹介しました。

本記事ではマルチインデックスの変換に対して値の整合性を保つ 2 種のツールを紹介します。エムオーテックス社内の AI サービスと相談しながら作成しました。それぞれ、実装の所要時間は生成と微調整で30分ほどです。

ツール 1: 軸と軸内のインデックス間の交換に対する整合性を保持

マルチインデックスにおける複数の行と列それぞれを 軸 とよびます。本記事では、上記の記事 [1] で作成した表に値を書き込んだエクセルファイルに対して、マルチインデックスにおけるそれぞれの軸同士 と、軸内のインデックス同士 を交換した場合にも、入力した値の整合性を保つツールを紹介します。これは、機能としては Excel 固有の行と列を交換して貼り付けるオプションの一般化になっています。

例えば、複数列のうち一番下の列と一番上の列で置き換えることで、表示として強調する順番を交換、軸内でインデックスを交換、そして複数行と複数列を交換することが本ツールで可能になります。ここでは、軸として規格に関する列と OS に関する列 を交換し、OS の軸内でインデックス同士 を交換します (図 1 と 2 を参照)。

図 1. 列インデックスが上から 規格1, 規格2; 規格 A, 規格B; 製品 A, 製品B; OS1, OS2 の順番。

図 2. 列インデックスが上から OS2, OS1; 規格1, 規格2; 規格 A, 規格B; 製品 A, 製品B の順番。

ツール 2: インデックスのマルチとシングルの相互変換の整合性を保持

出力形式で出力されたマルチインデックスをセミコロン “;” 区切りでシングルインデックスにし、さらにマルチインデックスに逆変換する相互変換するツールを紹介します。

例）規格1;規格A;製品A;OS1

背景

Excel 固有の機能

・貼り付けオプションで、値を保った状態で行と列の軸を交換することが可能です。

図 3. 行と列の交換。

• セルを選択して Shift キーを押しながらドラッグすることで、値を保った状態で行と列それぞれの軸内のインデックスを入れ替えることが可能です。

マルチインデックスをもつエクセルファイル

• マルチインデックスをもつエクセルファイルは、複数行と複数列をもちます。
• 通常の行と列をもつ表と同様に、軸を交換したり、インデックスを目的に応じた優先順位で並び替える要請も自然と考えられます。

マルチインデックスとシングルインデックスとの比較

• インデックスはシングルの方がマルチより関係データベースの枠組みで処理しやすいです。
• インデックスはマルチの方が行と列の階層構造を把握しやすいです。

課題

1. エクセルファイルがマルチインデックスをもつ場合、目的に応じて行と列それぞれ複数の軸、軸内のインデックスの交換、及びセルの値に関する整合性のチェックには手動だと膨大な時間がかかります。
2. インデックスのマルチとシングルの相互変換も、変換と整合性のチェックには手動だと膨大な時間がかかります。
3. Excel において上記の 2 種の変換を機能として提供はしておりません。

次に記載のような、マルチインデックスの具体例を考えます。

行インデックス: 「制御 1～5」の 5 種類、「制御 A～E」の 5 種類としたとき、5 * 5 = 25 通り。 列インデックス: 「機種種別α」が 5 種類、「機種種別β」が 5 種類、「製品の種別」が 3 種類、「OS 種別」が 2 種類としたとき、5 * 5 * 3 * 2 = 150 通り。

合計 25 * 150 = 3750 個のセルをもつエクセルファイルは、軸と軸内のインデックスの交換により整合性を保つためのインデックスと値の書き換え、その整合性のチェックには手動で実施すると 2 週間以上かかると見込まれます。

インデックスのマルチとシングルの相互変換も同様の 2 週間以上かかると見込まれます。

ツールによる解決

ツール 1: 課題 1 と 3 の解決

マルチインデックスのエクセルファイルにおいて軸とインデックスの交換で整合性を保つツールを作成しました。マルチインデックスをもつエクセルファイルを読み込み、テキストエリア内で軸と軸内のインデックスを交換し、インデックスの内容を検証後、エクセルファイルを作成します。

まず、ファイルを読み込みむと、テキストエリアに読み込んだ軸と軸内のインデックスが表示されます (図 4, 5, 6を参照)。

図 4. ファイル読み込み箇所。（本画像はツールの画面キャプチャのため、ご利用はできかねます。）

図 5. 軸と軸内のインデックスの交換前のインデックス。ファイルを読み込むと自動的に表示されます。（本画像はツールの画面キャプチャのため、ご利用はできかねます。）

図 6. 軸と軸内のインデックスの交換前の表。

次に、テキストエリアに軸と軸内のインデックスを交換します。 (図 7, 8を参照)。ボタン「インデックス内容検証」を押下することで、軸と軸内のインデックスに過不足がないことを検証し、最後にボタン「新しいExcelファイルを生成」を押下することでエクセルファイルをダウンロードします。

図 7. 軸と軸内のインデックスの交換後のインデックス。軸内について、「制御A, 制御B, 制御C」を「制御A, 制御C, 制御B」に入れ替えています。テキストエリアに修正後のインデックスを入力した後に、「インデックス内容検証」ボタンを押下し、「新しいExcelファイルを作成」ボタンを押下します。（本画像はツールの画面キャプチャのため、ご利用はできかねます。）

図 8. 軸と軸内のインデックスの交換後の表。

補足

行インデックス、列インデックスのハイパーキューブの軸をハイパーキューブの次元を配分して自由に入れ替えることでイメージを掴むことができます (図 9 を参照)。

図 9. ハイパーキューブによる軸と軸内のインデックスの交換。例えば、交換前後の行インデックスの次元と列インデックスの総和は 2 + 4 = 3 + 3 で等しいです。自由にハイパーキューブの軸と軸内のインデックスを交換するイメージです。

ソースコード

import streamlit as st
import pandas as pd
import numpy as np
import io
import itertools
from collections import Counter

############################################
# 自動的に MultiIndex の行・列レベル数を検出する関数
############################################
def auto_detect_levels(filename, max_levels=5):
    """
    Excel ファイル（pandas の to_excel 出力形式・シート名 "MultiIndex"）から、
    行インデックスのレベル数（ROW_INDEX_LEVELS）および
    列インデックスのレベル数（COL_INDEX_LEVELS）を自動検出します。

    pd.read_excel を用い、インデックス名が "A…"（行側）および "B…"（列側）で始まるものを採用します。
    見つかった組み合わせ (row_levels, col_levels) と DataFrame を返します。
    """
    for row_levels in range(1, max_levels+1):
        for col_levels in range(1, max_levels+1):
            try:
                df_try = pd.read_excel(
                    filename, 
                    header=list(range(col_levels)),
                    index_col=list(range(row_levels)),
                    engine="openpyxl"
                )
                idx_names = df_try.index.names
                col_names = df_try.columns.names
                if (all(isinstance(name, str) and name.startswith("A") for name in idx_names)
                    and all(isinstance(name, str) and name.startswith("B") for name in col_names)):
                    return row_levels, col_levels, df_try
            except Exception:
                continue
    raise ValueError("Excel ファイルから MultiIndex のレベル数を自動検出できませんでした。")

############################################
# メイン処理
############################################
st.title("マルチインデックスのエクセルファイルにおいて軸とインデックスの交換で整合性を保つツール")
uploaded_file = st.file_uploader("Excel ファイルをアップロードしてください", type=["xlsx"], key="uploader")
if uploaded_file is not None:
    try:
        # 自動検出（row_levels_auto: 行側レベル数 / col_levels_auto: 列側レベル数）
        row_levels_auto, col_levels_auto, df_loaded = auto_detect_levels(uploaded_file, max_levels=5)
    except Exception as e:
        st.error(f"自動検出に失敗しました: {e}")
    else:
        st.markdown(f"自動検出された行インデックスのレベル数：{row_levels_auto}、列インデックスのレベル数：{col_levels_auto}")
        
        # 元の MultiIndex の各軸のトークン一覧を抽出
        original_row_levels = [list(df_loaded.index.levels[i]) for i in range(df_loaded.index.nlevels)]
        original_col_levels = [list(df_loaded.columns.levels[i]) for i in range(df_loaded.columns.nlevels)]
        
        # セッションに元の軸情報を保存（軸同士の交換対応のため、全体で比較します）
        st.session_state['orig_row_levels'] = original_row_levels
        st.session_state['orig_col_levels'] = original_col_levels
        
        # テキストエリアの初期値：各レベルごとにトークンをカンマ区切りで1行表示
        default_text_rows = "\n".join([", ".join(level) for level in original_row_levels])
        default_text_cols = "\n".join([", ".join(level) for level in original_col_levels])
        
        st.markdown("### インデックスの編集")
        st.markdown("""
【例】  
■ 交換前  
【行】  
制御1, 制御2, 制御3  
制御A, 制御B, 制御C  

【列】  
規格1, 規格2  
規格A, 規格B  
製品A, 製品B  
OS1, OS2  

■ 交換後（軸同士の交換と軸内の並び替え）  
【行】  
制御1, 制御2, 制御3  

【列】  
規格1, 規格2  
規格A, 規格B  
製品B, 製品A  
OS1, OS2  
制御A, 制御B, 制御C
""")
        new_row_text = st.text_area("編集可能な 行インデックス（各レベルを改行）", value=default_text_rows, key="row_text")
        new_col_text = st.text_area("編集可能な 列インデックス（各レベルを改行）", value=default_text_cols, key="col_text")
        
        # セッション変数に検証結果を保持（初回は False）
        if "validated" not in st.session_state:
            st.session_state.validated = False
        
        # インデックス内容検証（軸間・軸内の交換の両方に対応）
        if st.button("インデックス内容検証", key="validate_button"):
            # テキストエリアから各レベルのトークン一覧を取得
            new_rows_levels = [
                [token.strip() for token in line.split(",") if token.strip()]
                for line in new_row_text.splitlines() if line.strip()
            ]
            new_cols_levels = [
                [token.strip() for token in line.split(",") if token.strip()]
                for line in new_col_text.splitlines() if line.strip()
            ]
            
            valid = True
            # 合計のレベル数の検証（軸の再編成の場合、行・列それぞれの個数は変わり得ますが、全体での数は変わらないはず）
            orig_total_levels = st.session_state['orig_row_levels'] + st.session_state['orig_col_levels']
            new_total_levels = new_rows_levels + new_cols_levels
            
            if len(new_total_levels) != len(orig_total_levels):
                st.warning(f"全体の軸/インデックスのレベル数が変更されています（元は {len(orig_total_levels)} レベル）。")
                valid = False
            else:
                # 各レベルごとに、元のラベル集合（順序無視）との一致を検証（Counter で両者の出現頻度も比較）
                orig_levels_counter = Counter(frozenset(level) for level in orig_total_levels)
                new_levels_counter = Counter(frozenset(level) for level in new_total_levels)
                if orig_levels_counter != new_levels_counter:
                    st.warning("新しい軸・インデックスのラベルが、元のものと一致していません。")
                    valid = False
            
            if valid:
                st.success("検証完了しました。")
                st.session_state.validated = True
                st.session_state.new_rows_levels = new_rows_levels
                st.session_state.new_cols_levels = new_cols_levels
            else:
                st.error("インデックス内容検証に失敗しました。元のラベルと同一のラベルのみ（軸間の入れ替えおよび軸内の入れ替えは可）となるようにしてください。")
                st.session_state.validated = False
        
        # 新しいExcelファイル生成
        if st.button("新しいExcelファイルを生成", key="create_excel"):
            if not st.session_state.get("validated", False):
                st.error("まずインデックス内容の検証を行ってください。")
            else:
                try:
                    # 生成する MultiIndex は、from_product を用いて構築します。
                    new_row_index = pd.MultiIndex.from_product(
                        st.session_state.new_rows_levels, 
                        names=[f"A{i+1}" for i in range(len(st.session_state.new_rows_levels))]
                    )
                    new_col_index = pd.MultiIndex.from_product(
                        st.session_state.new_cols_levels, 
                        names=[f"B{i+1}" for i in range(len(st.session_state.new_cols_levels))]
                    )
                except Exception as e:
                    st.error(f"MultiIndex の再構築に失敗しました: {e}")
                    st.stop()

                # 元の DataFrame の総セル数（例：9×16=144）
                total_cells = df_loaded.shape[0] * df_loaded.shape[1]
                new_total = len(new_row_index) * len(new_col_index)
                if total_cells != new_total:
                    st.error(f"再構築された軸の総要素数 ({new_total}) が元データセル数 ({total_cells}) と一致しません。")
                    st.stop()
                
                # 「セルの再配置」を、元のセルの論理的組み合わせに基づいて行います。
                # ① 元の DF の各セルについて、全因子の組み合わせ（row_tuple + col_tuple）を取得し、
                #     その sorted 版を canonical key として辞書に登録します。
                old_mapping = {}
                for r_key, row_vals in zip(df_loaded.index, df_loaded.values):
                    for c_key, cell in zip(df_loaded.columns, row_vals):
                        full_key = tuple(r_key) + tuple(c_key)
                        canon = tuple(sorted(full_key))
                        old_mapping[canon] = cell
                
                # ② 新しい MultiIndex の各セルについて同様に全因子の組み合わせを作成し、
                #     canonical key を用いて元の値を取得します。
                new_data = []
                for r_key in new_row_index:
                    row_vals = []
                    for c_key in new_col_index:
                        new_full = tuple(r_key) + tuple(c_key)
                        canon_new = tuple(sorted(new_full))
                        # 対応する元の値が存在しなければ None を設定
                        value = old_mapping.get(canon_new, None)
                        row_vals.append(value)
                    new_data.append(row_vals)
                
                df_new = pd.DataFrame(new_data, index=new_row_index, columns=new_col_index)
                
                st.subheader("更新された DataFrame")
                st.dataframe(df_new)
                
                output2 = io.BytesIO()
                with pd.ExcelWriter(output2, engine="xlsxwriter") as writer:
                    # 出力シート名は "MultiIndex"（元コードと同じ）
                    df_new.to_excel(writer, sheet_name="MultiIndex")
                output2.seek(0)
                st.download_button(
                    label="更新されたExcelファイルをダウンロード",
                    data=output2,
                    file_name="reconfigured_multi_index.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                )

ツール 2: 課題 2 と 3 の解決

インデックスのマルチとシングルを相互変換するツールを作成しました。

上記のツール 1 の例で用いた 図 5 はマルチインデックスをもつエクセルファイルです。 図 10 はインデックスをマルチからシングルに変換したものです。 図 11 はインデックスをシングルからマルチに変換したものです。図1 と同じものが得られ、相互変換が可能なことを示します。

図 10 インデックスをマルチからシングルに変換したエクセルファイル

図 11 インデックスをシングルから再度マルチに変換したエクセルファイル

ソースコード

import streamlit as st
import pandas as pd
import io

st.title("エクセルファイルのインデックスをマルチとシングルで相互変換するツール")
st.markdown("""
このアプリでは以下 2 つの変換が可能です。  
1. MultiIndex → SingleIndex  
　　・アップロードされた Excel ファイルの行インデックスは "A1", "A2", …、  
　　　列インデックスは "B1", "B2", … となっている場合、  
　　　各階層の値をセミコロン (;) で連結し、シングルインデックスに変換します。  
2. SingleIndex → MultiIndex  
　　・アップロードされた Excel ファイルの行・列のインデックスが、  
　　　各階層の値をセミコロン (;) で区切った文字列になっている場合、  
　　　それらを分割して MultiIndex に復元します。  
""")

conversion_mode = st.radio("変換モードを選択してください", 
                           ["MultiIndex → SingleIndex", "SingleIndex → MultiIndex"])

# ─────────────────────────────
# モード1：MultiIndex → SingleIndex 変換
# ─────────────────────────────
if conversion_mode == "MultiIndex → SingleIndex":
    st.header("MultiIndex → SingleIndex 変換")
    st.markdown("""
    ※ アップロードする Excel ファイルは、streamlit アプリで出力されたもので、  
　　　行インデックスの名前が "A1", "A2", …、列インデックスの名前が "B1", "B2", …  
　　　となっている前提です。
    """)
    uploaded_file = st.file_uploader("MultiIndex の Excel ファイルをアップロードしてください", type=["xlsx"])
    if uploaded_file is not None:
        
        def auto_detect_levels(file, max_levels=5):
            """
            アップロードされた Excel ファイルから、  
            行インデックスのレベル数（ROW_INDEX_LEVELS）および  
            列インデックスのレベル数（COL_INDEX_LEVELS）を自動検出します。  
            各試行で pd.read_excel を header と index_col にリスト（0～レベル数-1）を指定し、  
            得られた index.names / columns.names がそれぞれ "A…" および "B…" で始まる場合に採用します。
            """
            for row_levels in range(1, max_levels + 1):
                for col_levels in range(1, max_levels + 1):
                    try:
                        file.seek(0)  # 毎回ファイルの先頭に戻す
                        df_try = pd.read_excel(
                            file,
                            header=list(range(col_levels)),
                            index_col=list(range(row_levels)),
                            engine="openpyxl"
                        )
                        idx_names = df_try.index.names
                        col_names = df_try.columns.names
                        if (all(isinstance(name, str) and name.startswith("A") for name in idx_names)
                            and all(isinstance(name, str) and name.startswith("B") for name in col_names)):
                            return row_levels, col_levels, df_try
                    except Exception:
                        continue
            raise ValueError("Excel ファイルから MultiIndex のレベル数を自動検出できませんでした。")
        
        try:
            row_levels, col_levels, df = auto_detect_levels(uploaded_file)
            st.success(f"MultiIndex のレベル自動検出に成功！ 行側: {row_levels} 層、列側: {col_levels} 層")
        except Exception as e:
            st.error(f"MultiIndex のレベル自動検出に失敗しました：{e}")
            st.stop()
        
        st.subheader("読み込んだ DataFrame (MultiIndex)")
        st.dataframe(df)

        # MultiIndex の各階層をセミコロンで連結してシングルインデックスに変換
        if isinstance(df.index, pd.MultiIndex):
            df.index = df.index.map(lambda x: ";".join(str(item) for item in x))
        else:
            df.index = df.index.astype(str)
        
        if isinstance(df.columns, pd.MultiIndex):
            df.columns = df.columns.map(lambda x: ";".join(str(item) for item in x))
        else:
            df.columns = df.columns.astype(str)
        
        st.subheader("変換後の DataFrame (SingleIndex)")
        st.dataframe(df)
        
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
            df.to_excel(writer, index=True, sheet_name="Converted")
        output.seek(0)
        
        st.download_button(
            label="変換後の Excel ファイルをダウンロード",
            data=output,
            file_name="converted_single_index.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )

# ─────────────────────────────
# モード2：SingleIndex → MultiIndex 変換
# ─────────────────────────────
elif conversion_mode == "SingleIndex → MultiIndex":
    st.header("SingleIndex → MultiIndex 変換")
    st.markdown("""
    アップロードされる Excel ファイルは、既に行・列ともにシングルインデックスになっており、  
    各インデックスの文字列はセミコロン (;) で各階層の値が区切られている必要があります。  
    この変換では、各インデックスをセミコロンで分割し、元の MultiIndex に戻します。  
    ※ 行側の MultiIndex の各階層名は自動的に "A1", "A2", …、  
　　　列側は "B1", "B2", … として設定します。
    """)
    uploaded_file = st.file_uploader("シングルインデックスの Excel ファイルをアップロードしてください", type=["xlsx"], key="single_to_multi")
    if uploaded_file is not None:
        try:
            # index_col=0 によりエクセルの最初の列をデータフレームのインデックスとして読み込む
            df = pd.read_excel(uploaded_file, index_col=0, engine="openpyxl")
        except Exception as e:
            st.error(f"Excel ファイルの読み込みに失敗しました：{e}")
            st.stop()
        
        st.subheader("読み込んだ DataFrame (SingleIndex)")
        st.dataframe(df)

        def single_to_multi(df):
            """
            DataFrame の行インデックス・列名がシングル index で、  
            各要素がセミコロン (;) で区切られた文字列になっている場合、  
            それらを分割して MultiIndex に変換します。
            """
            # 行側の MultiIndex 化
            if len(df.index) > 0:
                first_row = str(df.index[0])
                row_levels = first_row.count(";") + 1
            else:
                row_levels = 1
            new_index = []
            for item in df.index:
                parts = [part.strip() for part in str(item).split(";")]
                if len(parts) < row_levels:
                    parts += [""] * (row_levels - len(parts))
                new_index.append(tuple(parts))
            row_names = [f"A{i+1}" for i in range(row_levels)]
            new_row_index = pd.MultiIndex.from_tuples(new_index, names=row_names)
            
            # 列側の MultiIndex 化
            if len(df.columns) > 0:
                first_col = str(df.columns[0])
                col_levels = first_col.count(";") + 1
            else:
                col_levels = 1
            new_columns = []
            for col in df.columns:
                parts = [part.strip() for part in str(col).split(";")]
                if len(parts) < col_levels:
                    parts += [""] * (col_levels - len(parts))
                new_columns.append(tuple(parts))
            col_names = [f"B{i+1}" for i in range(col_levels)]
            new_col_index = pd.MultiIndex.from_tuples(new_columns, names=col_names)
            
            df_multi = df.copy()
            df_multi.index = new_row_index
            df_multi.columns = new_col_index
            return df_multi

        df_multi = single_to_multi(df)
        st.subheader("変換後の DataFrame (MultiIndex)")
        st.dataframe(df_multi)

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
            df_multi.to_excel(writer, index=True, sheet_name="Converted")
        output.seek(0)
        
        st.download_button(
            label="変換後の Excel ファイルをダウンロード",
            data=output,
            file_name="converted_multi_index.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )

終わりに

通常、マルチインデックスをもつエクセルファイルにおける、軸と軸内のインデックスの交換で整合性を保つ交換は、手動で実施すると膨大で階層的なインデックスをたどり時間がかかります。また、Excel ではこのような機能を提供しておりません。

本ツールを用いることでこの作業を省力化できます。今後の展開としては、テスト自動化の結果の整理や変換に使用する予定です。

様々な業務において、マルチインデックスをもつ表で軸とインデックスの交換するような場合に、参考になれば幸いです。

お読みいただきありがとうございました。

参考

[1] マルチインデックスをもつエクセルファイルの作成ツール

[2] マルチインデックスをもつエクセルファイルの差分をセル単位で抽出するツール