日本食品標準成分表(食品成分表)のエクセルファイルを読み込んで、ランキングを作るコード例です。
データに混ざっている『文字列』を、『数値』に変換して保存します。
まず、『ヘッダ行(ラベル行)の書式』を修正します。
次に、『データ行の文字列』を『数値』に変換します。
これで、成分表のデータをうまく並べ替えることができました。
あとは、Excelのオートフィルタでソートすれば、ランキングになります。
Excelファイルの『読み込み』と『数値化』は、Python(パイソン)で行いました。
思い立ったきっかけ
『こまかい意味は置いといて、とりあえず多い順に並べたい。』
食品成分表を見ていて、そう思ったのがきっかけです。
しかし、成分表のエクセルファイルは、数値と文字列が混在していて、思った通りに並べ替えることができませんでした。
(多少不便ではありますが、測定値や推定値がきちんと区別されていて、個人的にはとても良いデータだと思います。)
そこで、データを数値化するコードを書きました。
データを自在にソートできるようになったので、食品成分(栄養成分)のランキング作成が可能になりました。
ランキングにしたことで、成分量の多い食品群を、簡単に探せるようになりました。
やはり、食品を探すときは、測定値と推定値を区別しないほうが便利でした。
もちろん、ひとつひとつの数値は、条件によって大きく異なるのかもしれません。
しかしそれでも、食品ランキングは目安として非常に便利でした。
Excelファイルの出典
使用したエクセルファイルは、以下の『日本食品標準成分表2015年版(七訂)』です。
■ エクセルファイルの出典 (2019年6月1日 時点)
文部科学省ホームページ
> 科学技術・学術
> 科学技術・学術カテゴリー一覧
> 科学技術・学術
> 生命倫理・安全等
> 日本食品標準成分表・資源に関する取組
> 日本食品標準成分表2015年版(七訂)について
> 日本食品標準成分表(資源調査分科会報告)
> 日本食品標準成分表2015年版(七訂)
> 第2章 日本食品標準成分表
> Excel(日本語版)
一括ダウンロード (Excel:日本語) (Excel:917KB) excel!http://www.mext.go.jp/component/a_menu/science/detail/__icsFiles/afieldfile/2017/12/25/1365334_1r12.xlsx
食品成分表を読み込むコード例
メイン関数
一番上の関数です。
Excelファイルを読み込み、数値化して、保存する、という流れになっています。
"""日本食品標準成分表のExcelファイル(xlsx)を読み込むPythonプログラム"""
import os
import pandas as pd
def main():
"""メイン関数"""
# 1. エクセルファイルの場所を指定
# (ファイルパスは、自身の環境に合わせて変更します。)
xlsx_file = r'F:\apps\datas\1365334_1r12.xlsx'
# 2. 日本食品標準成分表(xlsx)を読み込む & 書式を修正
df = get_hyoujun_seibun_hyou(file=xlsx_file)
# 3. 数値を文字列型から数値型に変換
# この関数の中でデータフレームを上書きします。
# 元のdfを保持したかったので、コピーを渡しています。
t_df = str2num(df=df.copy())
# 4. データフレームをファイルに保存する
# (保存フォルダパスは、自身の環境に合わせて変更します。)
save_df(df=t_df, data_dir=r'F:\apps\datas')
# 以上です
return
Excelファイルを読み込んで『書式』を修正する
エクセルファイルは、pandas(パンダス)ライブラリで読み込みました。
エクセルを pandas.DataFrame に変換してから、書式の修正をしています。
『全角スペース(\u3000)』
や『半角スペース(\x20)』
の数字は、『Unicode コードポイント』です。
スペースなどは文字が透明なので、代わりに『Unicode コードポイント』で書いてみました。
もちろん、普通にスペースキーで入力した全角・半角スペースを使っても大丈夫です。
def get_hyoujun_seibun_hyou(file):
"""日本食品標準成分表(xlsx)を読み込む & 書式を修正"""
# ■ エクセルファイルのフォーマット
# ・最初の4行は説明分(読み込まずにスキップする)
# ・次の4行がヘッダ行
# ・残りがデータ行
# ■ エクセルファイルを『文字列』として読み込む
a = pd.read_excel(
file, # エクセルファイルのファイルパス
sheet_name='本表', # 読み込むシートの名前は『本表』
header=None, # ヘッダ行の設定はしない
index_col=False, # インデックス列の設定はしない
skiprows=4, # 最初の4行をスキップする
dtype=str, # 文字列として読み込む
)
# ■ 読み込んだエクセルファイルの状態
# 行番号 列番号 ※ 行と列は『ゼロ始まり』
# [0] [0]食品群 [1]食品番号 [2]索引番号 [3]食品名 [4]廃 棄 率 [5] [6] [7]一般成分 ... [67]備考
# [1] [0] [1] [2] [3] [4] [5]エネルギー(kcal) ...
# [2] [0] [1] [2] [3]Tagnames [4]REFUSE [5]ENERC_ KCAL ...
# [3] [0] [1] [2] [3]単位 [4]% [5]kcal/100 g ...
# [4] [0]01 [1]01001 [2]1 [3]アマランサス 玄穀 ...
# ...
# [2194] [0]18 [1]18022 [2]2198 [3]メンチカツ フライ済み 冷凍 ...
# ■ 書式を修正する
# 1段階目:全セルを一括処理できるところを修正
# 文字列の『nan』を削除
# Excelファイルを文字列として読み込むと、空白が 'nan' になるので削除します。
a.replace(to_replace='nan', value='', inplace=True, regex=False)
# 各セルのテキスト中の『改行』を半角スペース(\x20)に置換
a.replace(to_replace=r'\n', value='\x20', inplace=True, regex=True)
# 『全角スペース(\u3000)』を半角(\x20)にする
a.replace(to_replace=r'\u3000', value='\x20', inplace=True, regex=True)
# 先頭と末尾の『半角スペース(\x20)』を削除
a.replace(to_replace=r'(?:^\x20+|\x20+$)', value='', inplace=True, regex=True)
# 文字列変換テーブルを作成
table = str.maketrans(
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
)
# 文字列変換テーブルで『英数字』を半角に修正
a = a.applymap(lambda x: x.translate(table))
# ■ 書式を修正する
# 2段階目:個別対応が必要なところを修正
# 1行目と2行目の丸カッコを『半角』にする
a.iloc[1] = a.iloc[1].str.replace('(', '(', regex=False)
a.iloc[1] = a.iloc[1].str.replace(')', ')', regex=False)
a.iloc[2] = a.iloc[2].str.replace('(', '(', regex=False)
a.iloc[2] = a.iloc[2].str.replace(')', ')', regex=False)
# 0行目の中で冗長な部分を修正
a.iloc[0] = a.iloc[0].str.replace(r'廃\x20*棄\x20*率', '廃棄率', regex=True)
a.iloc[0] = a.iloc[0].str.replace(r'ビタミン\x20*\(ビタミンA\)', 'ビタミンA', regex=True)
a.iloc[0] = a.iloc[0].str.replace(r'ビタミン\x20*\(ビタミンE\)', 'ビタミンE', regex=True)
# 1行目の中で『均等割り付け』されてる要素を修正
a.iloc[1] = a.iloc[1].str.replace(r'水\x20*分', '水分', regex=True)
a.iloc[1] = a.iloc[1].str.replace(r'脂\x20*質', '脂質', regex=True)
a.iloc[1] = a.iloc[1].str.replace(r'灰\x20*分', '灰分', regex=True)
return a
データを『数値』に変換する
食品成分表のデータから、カッコを外したり、Tr(トレース)を変換したりするところです。
成分表の説明によると、文献などから推定した値には、カッコを付けたとのことです。
トレースと読む『Tr』は、微量という意味とのことです。
データに文字列が入っていると、ソートがうまくできないので、数値型に変換します。
数値変換の方法ですが、食品成分表のデータを『Python』で使うか、『Excel』で使うかによって、最適な方法は異なりました。
ここでは、Excel用の設定にしました。
def str2num(df):
"""データを文字列型から数値型に変換"""
float_nan = float('nan')
# float_inf = float('inf')
# ■ 変換関数を定義
def str2float(x):
"""float型に変換する関数"""
# 推定した値という意味のカッコを削除
x = x.replace('(', '').replace(')', '')
# 空文字列
if x == '':
# NaN (not-a-number) に変換
return float_nan
# 未測定という意味のハイフン('-')
elif x == '-':
# NaN (not-a-number) に変換
return float_nan
# 微量という意味のトレース('Tr')
elif x.lower() == 'tr':
# 負の無限大に変換(Python用)
# 文字列型が混ざるとソートができないので、
# 代わりに負の無限大を割り当てる。
# return -float_inf
# トレース表記を維持(Excel用)
return 'Tr'
# 特別な扱い
elif x == '*':
# 人乳のヨウ素は、海藻摂取の有無で変動が大きかったとのこと。
# 数値を特定できないので、未測定と同じ扱いにする。
return float_nan
# 数値である
else:
# 浮動小数点数に変換
return float(x)
# ■ 『データ部分』に変換関数を適用
df.iloc[4:, 4:-1] = df.iloc[4:, 4:-1].applymap(str2float)
# 説明
# df.iloc[行の指定, 列の指定]
# 行の指定:4行目から最終行までの範囲に適用する。
# 0行目から3行目まではヘッダ行なので無視。
# 列の指定:4列目の廃棄率から、最終列(備考欄)の直前までの範囲に適用する。
return df
CSVファイルに保存する
食品成分表の『ヘッダ修正』と『数値変換』が終わったので、CSVファイルに保存します。
そのままの体裁で保存すると、食品成分表のExcelファイルとほぼ同じ見た目になります。
一方で、Pythonやデータベースで使う場合は、ヘッダを1行にまとめたほうが便利でした。
2通りの保存方法を紹介します。
def save_df(df, data_dir):
"""データフレームをCSVファイルに保存する"""
# ■ そのままの体裁でCSVに保存
# dfのindexとcolumnsには、自動生成の連番が入っています。
# columnsはheaderとして扱われます。
# indexとheaderにNoneを指定して、出力しないようにしました。
df.to_csv(
os.path.join(data_dir, 'data_00.csv'),
encoding='utf-8-sig',
index=None,
header=None,
)
# ■ 列名をタプルにして、ヘッダ行を1行にまとめる。
# データフレーム作成
df2 = pd.DataFrame(
df.iloc[4:, :].values,
columns=df.iloc[0:4, :].T.values,
)
# CSVに保存
df2.to_csv(
os.path.join(data_dir, 'data_01_tuple.csv'),
encoding='utf-8-sig',
index=None,
)
# 列を列名で取得するときは、タプルで指定します。
# 『カルシウム』の列を指定する例:
# df2[('無機質', 'カルシウム', 'CA', 'mg/100 g')]
return
データを並べかえて食品成分表のランキングを作る
Excelのオートフィルタでソートするのが、一番簡単です。
『昇順・降順』で、それぞれ『少ない順・多い順』のランキングができました。
もちろん、Python上でランキングを作ることもできます。
pandas.DataFrame の .sort_values() メソッドで、簡単にソートできました。
数値は変化したけどランキングには影響しなかった
データを『文字列』から『数値』に変換したことで、数値がわずかに変化しました。
これは、小数を扱ううえで、どうしても発生する現象です。
しかしながら、ランキングの大勢(たいせい)には、ほとんど影響しませんでした。
数値化にともなう小数のずれは、ほとんど問題にならなかったです。