#292 算出対象ごとに物質循環の指標を算出する/Calculating Material Circulation Indicators across Different Levels of Aggregation
宮崎 稔也An English translation of this article is provided at the bottom of the page.
前回の投稿では、物質循環の指標を設定し、着産業別の物質循環の指標を算出しました。今回の投稿では、
- 都道府県全体
- 産業三大分類別（第1次産業、第2次産業、第三次産業）
- 第2次産業中の産業大分類別（鉱業、建設業、製造業）
- 製造業における各産業別（食料品など）
というような算出対象ごとに物質循環の指標を算出していきます。
都道府県全体
都道府県全体の物質循環の指標を算出するコードは以下のようになります。
# 1. df_indicator の全行（各業種）を合計して、都道府県全体の値を算出
# G, W, F の単純合計をとります
total_g = df_indicator['G'].sum()
total_w = df_indicator['W'].sum().round(1)
total_f = df_indicator['F'].sum().round(1)
# 2. 合計値に基づいた地域全体の指標を計算
total_wf = total_w / total_f if total_f != 0 else 0
total_wg = total_w / total_g if total_g != 0 else 0
total_fg = total_f / total_g if total_g != 0 else 0
# 3. 新しいDataFrameとして作成
df_total_pref = pd.DataFrame({
'G': [total_g],
'W': [total_w],
'F': [total_f],
'W/F': [total_wf],
'W/G': [total_wg],
'F/G': [total_fg]
}, index=['都道府県'])
# 表示設定（大きな数値も見やすく）
pd.options.display.float_format = '{:.3f}'.format
# 結果の確認
df_total_pref[['W/F', 'W/G', 'F/G']]産業三大分類別（第1次産業、第2次産業、第三次産業）
産業三大分類別（第1次産業、第2次産業、第三次産業）の物質循環の指標を算出するコードは以下のようになります。
# 新しいDataFrameを「df_three_sectors」という名前で作成
df_three_sectors = df_indicator.groupby('三大分類')[['G', 'W', 'F']].sum().round(1)
# 合計値に基づいて指標を再計算
df_three_sectors['W/F'] = df_three_sectors['W'] / df_three_sectors['F']
df_three_sectors['W/G'] = df_three_sectors['W'] / df_three_sectors['G']
df_three_sectors['F/G'] = df_three_sectors['F'] / df_three_sectors['G']
# 0除算対策
df_three_sectors = df_three_sectors.replace([np.inf, -np.inf], np.nan).fillna(0.0)
# 指数表記を避けて表示（お好みで）
pd.options.display.float_format = '{:.3f}'.format
# インデックスの順序を「第1次 → 第2次 → 第3次」に整える
sector_order = ['第1次産業', '第2次産業', '第3次産業']
df_three_sectors = df_three_sectors.reindex(sector_order)
# 結果の確認
df_three_sectors[['W/F', 'W/G', 'F/G']]第2次産業中の産業大分類別（鉱業、建設業、製造業）
第2次産業中の産業大分類別（鉱業、建設業、製造業）の物質循環の指標を算出するコードは以下のようになります。
# 1. 産業大分類ごとに G, W, F の合計を算出
# 新しいDataFrameの名前を df_major_sectors に設定
df_major_sectors = df_indicator.groupby('産業大分類')[['G', 'W', 'F']].sum().round(1)
# 2. 指標を再計算
df_major_sectors['W/F'] = df_major_sectors['W'] / df_major_sectors['F']
df_major_sectors['W/G'] = df_major_sectors['W'] / df_major_sectors['G']
df_major_sectors['F/G'] = df_major_sectors['F'] / df_major_sectors['G']
# 3. 0除算対策（infやNaNを0に置換）
df_major_sectors = df_major_sectors.replace([np.inf, -np.inf], np.nan).fillna(0.0)
# 4. 指数表記を避けて表示
pd.options.display.float_format = '{:.3f}'.format
# 5. インデックスの順序を「第1次 → 第2次 → 第3次」に整える
major_sectors_order = ['農林水産業', '鉱業', '建設業', '製造業', '第3次産業']
df_major_sectors = df_major_sectors.reindex(major_sectors_order)
# 結果の確認
df_major_sectors[['W/F', 'W/G', 'F/G']]製造業における各産業別（食料品など）
製造業における各産業別（食料品など）の物質循環の指標を算出するコードは以下のようになります。
# 1. インデックスを略称(abbreviation)に切り替えた表示用データフレームを作成
# ※元の df_indicator を壊さないよう .copy() を推奨
df_display = df_indicator.copy()
# 2. 'abbreviation' 列をインデックスに設定
df_display = df_display.set_index('abbreviation')
# 3. インデックス名をきれいにする（'industry' から '業種' などへ）
df_display.index.name = '製造業'
# 4. 算出した指標を中心に表示
# 小数点以下の表示桁数を調整
pd.options.display.float_format = '{:.3f}'.format
df_second_industry = df_display[df_display['産業大分類'] == '製造業']
df_second_industry[['W/F', 'W/G', 'F/G']]English Translation Part is Here.
In my previous post, I defined the material circulation indicators and calculated them for each destination industry. In this post, I will calculate these indicators for each of the following target categories:
- Entire Prefecture
- Three Major Industrial Sectors (Primary, Secondary, and Tertiary Industries)
- Major Industrial Classifications within the Secondary Industry (Mining, Construction, and Manufacturing)
- Specific Industries within the Manufacturing Sector (Food products, etc.)
Entire Prefecture
The code to calculate the material circulation indicators for the entire prefecture is as follows:
# 1. Sum up all rows (all industries) in df_indicator to calculate total values for the prefecture.
# We take the simple sum of G (GRP), W (Waste), and F (Inflow).
total_g = df_indicator['G'].sum()
total_w = df_indicator['W'].sum().round(1)
total_f = df_indicator['F'].sum().round(1)
# 2. Calculate regional indicators based on the total values.
total_wf = total_w / total_f if total_f != 0 else 0
total_wg = total_w / total_g if total_g != 0 else 0
total_fg = total_f / total_g if total_g != 0 else 0
# 3. Create a new DataFrame for the totals.
df_total_pref = pd.DataFrame({
'G': [total_g],
'W': [total_w],
'F': [total_f],
'W/F': [total_wf],
'W/G': [total_wg],
'F/G': [total_fg]
}, index=['Entire Prefecture'])
# Display settings (for better readability of large numbers)
pd.options.display.float_format = '{:.3f}'.format
# Verify the results
df_total_pref[['W/F', 'W/G', 'F/G']]
Three Major Industrial Sectors
The code to calculate indicators for the Primary, Secondary, and Tertiary industries is as follows:
# Create a new DataFrame named 'df_three_sectors'
df_three_sectors = df_indicator.groupby('Major_Sector')[['G', 'W', 'F']].sum().round(1)
# Recalculate indicators based on the total values
df_three_sectors['W/F'] = df_three_sectors['W'] / df_three_sectors['F']
df_three_sectors['W/G'] = df_three_sectors['W'] / df_three_sectors['G']
df_three_sectors['F/G'] = df_three_sectors['F'] / df_three_sectors['G']
# Handle division by zero
df_three_sectors = df_three_sectors.replace([np.inf, -np.inf], np.nan).fillna(0.0)
# Formatting
pd.options.display.float_format = '{:.3f}'.format
# Reorder index to "Primary → Secondary → Tertiary"
sector_order = ['Primary Industry', 'Secondary Industry', 'Tertiary Industry']
df_three_sectors = df_three_sectors.reindex(sector_order)
# Verify results
df_three_sectors[['W/F', 'W/G', 'F/G']]
Major Industrial Classifications within the Secondary Industry
The code to calculate indicators for Mining, Construction, and Manufacturing is as follows:
# 1. Sum G, W, and F for each major industrial classification
df_major_sectors = df_indicator.groupby('Major_Classification')[['G', 'W', 'F']].sum().round(1)
# 2. Recalculate indicators
df_major_sectors['W/F'] = df_major_sectors['W'] / df_major_sectors['F']
df_major_sectors['W/G'] = df_major_sectors['W'] / df_major_sectors['G']
df_major_sectors['F/G'] = df_major_sectors['F'] / df_major_sectors['G']
# 3. Handle division by zero
df_major_sectors = df_major_sectors.replace([np.inf, -np.inf], np.nan).fillna(0.0)
# 4. Reorder index
major_sectors_order = ['Agriculture, Forestry, and Fisheries', 'Mining', 'Construction', 'Manufacturing', 'Tertiary Industry']
df_major_sectors = df_major_sectors.reindex(major_sectors_order)
# Verify results
df_major_sectors[['W/F', 'W/G', 'F/G']]
Specific Industries within the Manufacturing Sector
The code to calculate indicators for each industry (e.g., Food products) within the manufacturing sector is as follows:
# 1. Create a display DataFrame with index switched to abbreviations
df_display = df_indicator.copy()
# 2. Set 'abbreviation' as the index
df_display = df_display.set_index('abbreviation')
# 3. Clean up the index name
df_display.index.name = 'Manufacturing'
# 4. Filter for Manufacturing and display indicators
pd.options.display.float_format = '{:.3f}'.format
df_second_industry = df_display[df_display['Major_Classification'] == 'Manufacturing']
df_second_industry[['W/F', 'W/G', 'F/G']]
Follow me!
