#278　３日間の品類・品目別流動量を算出するコードを改善する

投稿#276では、物流センサスの「都道府県間年間流動量調査」のデータから、年間の品類別の純移出量、純移入量、内部流動量を算出するコードを改善しました。

今回は、物流センサスの「都道府県間流動量３日間調査」のデータから、３日間の品類・品目別の純移出量、純移入量、内部流動量を求めるコードを改善していきます。

マテリアルフロー推計手順の概要

2015年大阪府のマテリアルフローの作成にあたり、島崎（2008）、天野ら（2001）の手法を参照しました。以下に推計手順の概要を示します。

1. 「都道府県間年間流動量調査」のデータから、年間の品類別の純移出量、純移入量、内部流動量を求めます。
2. 「都道府県間流動量３日間調査」のデータから、３日間の品類・品目別の純移出量、純移入量、内部流動量を求めます。
3. 上記1.の結果に、2.で求めた３日間の品類と品目の比率を配分し、年間の品類・品目別の純移出量、純移入量、内部流動量を算出します。
4. 上記3.の結果を、表274−１で示した9品類67品目に集約します。
5. 「港湾統計（年報）2014」および「港湾統計（年報）2015」の「第３表　海上出入貨物表　（２）品種別都道府県別表（輸移出入） 」のデータから、2014年度（2014年4月〜2015年3月）の輸出入量を求めます。
6. 上記5.の結果を、表274−1で示した9品類67品目に集約します。
7. 上記4.と6.の結果を合計して、純流入量・純流出および総流入量・総流出量を算出します。
8. 上記7.の結果に「発産業業種別・品類品目別流動量３日間調査」と「着産業業種・品類品目別流動量３日間調査」の２つのデータから業種別比率を求め、各産業に配分します。
9. 上記8.で配分する際、これらのデータは全国平均を意味するため、大阪府の産業構造や取引状況が考慮されていません。そのため、平成28年経済センサスを用いて、着産業は製造品出荷額、発産業は原材料・燃料・電気使用額を参照し、重みづけによる補正を施します。
10. 業種別、品類・品目別の年間流動量を求めた後、産業廃棄物排出量、一般廃棄物排出量、県内総生産、エネルギー消費量、二酸化炭素排出量など、他の統計データを追加します。

今回の投稿では、手順2.のコードに手を付けていきます。

手順2.で利用するデータは、物流センサスになります。現時点（2025年11月22日）では、物流センサスはe-stat上でデータベース化されておらず、APIを利用することもできない状態です。提供されているのはExcelファイルのみです。

したがって、

推計に必要な最低限のExcelファイルのみをダウンロードし、それらをpandasで読み込み、DataFrameに変換してから推計を行っていきます。Excelファイル上での手計算の工程をなくすことで、生成されるエクセルの数をできるだけ抑えるようにします。

および

jupyterLab上に分散しているセルをできるだけマージして、コードをまとめていきます。

本ブログ投稿#275より引用

ということを意識して、推計のコードを書いていくことにしました。

推計手順2.のコード

def unmerge_all_cells_and_save_as_new(input_file_path, output_file_path):
    """
    指定されたExcelファイルの全てのシートでセル結合を解除し、
    新しいファイルとして保存します。

    Args:
        input_file_path (str): 結合解除したい元のExcelファイルのパス。
        output_file_path (str): 変更を保存する新しいExcelファイルのパス。
    """
    try:
        # 元のExcelファイルを開く
        wb = openpyxl.load_workbook(input_file_path, data_only=True)
    except FileNotFoundError:
        print(f"エラー: ファイルが見つかりません。パスを確認してください: {input_file_path}")
        return

    # 全てのシートをループ処理してセル結合を解除
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"'{sheet_name}'シートのセル結合を解除しています...")

        merged_cells_list = list(ws.merged_cells)
        if not merged_cells_list:
            print(f"'{sheet_name}'シートには結合されたセルがありません。")
            continue

        for merged_cell_range in merged_cells_list:
            ws.unmerge_cells(str(merged_cell_range))

        print(f"'{sheet_name}'シートのセル結合をすべて解除しました。")

    # 変更を新しいファイル名で保存
    wb.save(output_file_path)
    print(f"変更を新しいファイルに保存しました: {output_file_path}")

def remove_spaces_from_excel(file_path):
    """
    指定されたExcelファイルの全てのセルから空白を削除します。

    Args:
        file_path (str): 処理するExcelファイルのパス。
    """
    try:
        # Excelファイルを読み込みます
        workbook = openpyxl.load_workbook(file_path)

        # 全てのシートをループします
        for sheet_name in workbook.sheetnames:
            sheet = workbook[sheet_name]

            # 全ての行をループします
            for row in sheet.iter_rows():
                # 全てのセルをループします
                for cell in row:
                    # セルに値があり、それが文字列型であるか確認します
                    if cell.value and isinstance(cell.value, str):
                        # 文字列から空白を削除し、セルに新しい値を設定します
                        cell.value = cell.value.replace(" ", "").replace("　", "")

        # 変更を元のファイルに上書き保存します
        workbook.save(file_path)
        print(f"ファイル '{file_path}' の全ての文字列から空白が削除されました。")

    except FileNotFoundError:
        print(f"エラー: ファイル '{file_path}' が見つかりませんでした。")
    except Exception as e:
        print(f"エラーが発生しました: {e}")

input_path = '物流センサスデータ/都道府県間流動量（3日間調査品類別）.xlsx'  # 結合を解除したい元のファイル名
output_path = '物流センサスデータ/都道府県間流動量（3日間調査品類別）_unmerge.xlsx'  # 新しく保存するファイル名

unmerge_all_cells_and_save_as_new(input_path, output_path)

excel_file = '物流センサスデータ/都道府県間流動量（3日間調査品類別）_unmerge.xlsx'  # 処理したいExcelファイル名に変更してください
remove_spaces_from_excel(excel_file)

def make_3days_flow_df(file_path, sheet_name, search_string):
    """
    Excelシート内の特定の文字列を含むセルの行番号を取得
    流動量のデータが存在するセル範囲において、流動量を算出し、DataFrameに格納
    そのDataFrameを戻り値として返す
    Args:
        file_path (str): Excelファイルのパス
        sheet_name (str): 検索するシート名
        search_string (str): 検索する文字列

    Returns:
        DataFrame: 流動量を格納したDataFrame
    """
    try:
        # ExcelファイルをDataFrameとして読み込む
        df = pd.read_excel(file_path, sheet_name=sheet_name)

        # DataFrame内のすべてのセルを文字列に変換してから、
        # 検索文字列が含まれる行を特定する
        # .astype(str)は、数値やNaNを文字列に変換して検索可能にする
        # .str.contains(search_string, na=False) は、部分一致で検索し、NaNをFalseとして扱う
        # .any(axis=1) は、行のいずれかのセルに文字列が含まれていればTrueとする
        found_rows = df[df.astype(str).apply(lambda row: row.str.contains(search_string, na=False)).any(axis=1)].index.tolist()

        # DataFrameのインデックス(0から始まる)をExcelの行番号(1から始まる)に変換する
        # ヘッダー行を考慮するため、+2とする
        cell_locations = [row_index + 2 for row_index in found_rows]

        if cell_locations:
            print(f"'{search_string}'が見つかった行番号: {cell_locations}")
        else:
            print(f"'{search_string}'は見つかりませんでした。")

        # Excelファイルを開く
        wb = openpyxl.load_workbook(file_path, data_only=True)

        ws = wb['sheet1']
        row_dict = {}
        for row_num in cell_locations:
            goods = ws.cell(row_num, 3).value
            if goods == '合計':
                continue
            else:
                flow_list = []
                # datumにセルの値を格納する
                datum = []
                for row in range(48):
                    # 各行の値を格納しておくリストrow_datumを用意
                    row_datum = []
                    for column in range(48):
                        # セルの値をrow_datumに格納
                        row_datum.append(ws.cell(row_num + row + 5, column + 3).value)
                    # 1行分の値が格納されたrow_datumをdatumに格納
                    datum.append(row_datum)
                df = pd.DataFrame(datum)

                flow_list.append(round(df.iloc[47, pref_code], 1))   # 総着量
                flow_list.append(round(df.iloc[pref_code, 47], 1))   # 総発量
                flow_list.append(round(df.iloc[pref_code, pref_code], 1))   # 内部流動量  
                flow_list.append(flow_list[0] - flow_list[2])   # 純移入（総着量 - 内部流動量)
                flow_list.append(flow_list[1] - flow_list[2])   # 純移出 (総発量 - 内部流動量)
                row_dict[goods] = flow_list

        df_3days_pref_flow = pd.DataFrame(row_dict).T
        df_3days_pref_flow.columns = ['総着量', '総発量', '内部流動量', '純移入', '純移出']
        return df_3days_pref_flow

    except FileNotFoundError:
        print(f"エラー: ファイル '{file_path}' が見つかりません。")
        return
    except Exception as e:
        print(f"エラーが発生しました: {e}")
        return

file_path = '物流センサスデータ/都道府県間流動量（3日間調査品類別）_unmerge.xlsx' 
sheet_name = 'sheet1'              # あなたのシート名に置き換えてください
search_string = '品類名'      # 検索したい文字列に置き換えてください
df_3days_pref_goods = make_3days_flow_df(file_path, sheet_name, search_string)   # 3日間の品類別流動量のDataFrame

file_path = '物流センサスデータ/都道府県間流動量（3日間調査品目別）_unmerge.xlsx' 
sheet_name = 'sheet1'              # あなたのシート名に置き換えてください
search_string = '品目名'      # 検索したい文字列に置き換えてください
df_3days_pref_item = make_3days_flow_df(file_path, sheet_name, search_string)   # 3日間の品目別流動量のDataFrame

参考文献

• 天野 耕二, 戸辺 勝俊, 長谷川 聖洋, 日本全国の都道府県における物質循環評価手法に関する研究, 環境システム研究論文集, 2001, 29 巻, p. 215-223
• 島崎 洋一, 物流センサスによる山梨県のマテリアルフローの時系列分析, 環境科学会誌, 2008, 21 巻, 1 号, p. 27-36

Follow me!