データラングリングとは、生データから分析用の加工・整形データを作ることです。Excelでデータ解析をしている人にとって関数やVBAを使ったデータ加工は日常茶飯事な作業と言えます。この記事では、 Python in Excel でのDataFrameを使ったデータクリーニングやデータラングリングの方法について、および info, describe, value_counts, iterrows, map, mergeメソッド等のサンプルコードを紹介します。
2023年10月現在、Python in Excel は Microsoft 365 Insider Program のベータチャネルを選択することによって使用できるベータ版のExcelでのみ利用可能です。
ベータ版のExcelのインストール・初期設定方法は以下で詳しく紹介しています。
データラングリング、データクリーニングとは
データラングリングとは、データマンジングとも呼ばれ、分析などのさまざまな下流の目的により適切かつ価値のあるものにすることを目的として、データを１つの『生』データ形式から別の形式に変換およびマッピングするプロセスです。
Python for Excel Analysts: Data Cleaning and Wrangling | Anaconda
データラングリングの最も一般的な形式の１つは、１つ以上の既存のテーブル列のデータから新しい列を追加することです。以下は例です。
- 計算の実行 (例: A列からB列を減算する)
- 文字列の列から部分文字列を抽出する
- バイナリインジケーターの作成 (例: 欠損値か否か)
機械学習では、このプロセスを「特徴量エンジニアリング」と呼ぶそうです。特徴量エンジニアリングとは予測モデルの作成に最も役立つデータ表現を作成することを目的としています。
データクリーニングは、クリーニングが必要な列に対してクリーニングされたデータで上書きすることをいい、データラングリングの作業のうちの１つです。
サンプルデータ
Anaconda の公式サイトにあるサンプルデータを使用して説明します。以下のページ内の「download here」のリンクからダウンロードできます。
テーブル名が「InternetSales」、データが60398件の売上データが入っています。
サンプルコード
前準備
サンプルデータのブックにシートを追加し、A1セルに以下のコードを入力します。
# A1セル
# dataframeを定義
df1 = xl("InternetSales[#すべて]", headers = True)
Pythonの入力方法や出力種類、DataFrameの定義方法については、以下の記事で詳しく紹介しています。
１．データクリーニングが必要かどうかを確認する
クリーニングが必要な列かどうかを特定するには、info, describe, value_counts メソッドが役立ちます。
DataFrame の概要を取得（info）
# A5セル
df1.info() # DataFrameの概要が診断ウィンドウに表示される結果 DataFrameの概要が診断ウィンドウに表示される（セルには「None」が返る）
診断ウィンドウの情報から次のことが分かります。
- DataFrame には 60,398 の行があります。
- DataFrame には ９つの列があります。
- 各列には 60,398 個の非null値があります。
- 次の列は数値です: SalesOrderLineNumber、OrderQuantity、TotalProductCost、SalesAmount、およびGrossProfit。
- OrderDate列は日付と時刻です。
- 次の列は文字列です: SalesOrderNumber、ProductSubcategoryName、およびProductName
最初に特定したいことの１つは欠損値があるかどうかです。ほとんどの分析では、デフォルト値で置き換えるなどして欠損データを消去する必要があります。
info メソッドでは、欠損値はnullと表現され、非null値（Non-Null）の数が表示されます。上記の例では、全ての列の非null値の数が行の数と一致するため、データの欠落はありません。
欠損値をデフォルト値に書き換えるサンプルコードはこちらを参考にしてください。
数値データのプロファイリング（describe）
数値列の場合は、describe メソッドでデータの特性を簡単につかめます。
# A7セル
df1.describe() # 要約統計量を取得結果
要約統計量から、意味をなさない最小値や最大値、値の広がり（分散）等がないかを確認し、データクリーニングが必要な可能性のある列を特定します。
例えば、「OrderQuantity」列には値「１」のみが含まれています。ビジネスプロセスによっては、これは予想されることである場合もあれば、データ品質の問題を示している場合もあります。
文字列データのプロファイリング（value_counts）
文字列データの列は、特に人間によって入力されたデータの場合（番地など）にクリーニングが必要になることがよくあります。（Excelあるある）
value_counts メソッドは、列内に含まれる一意の文字列値とカウントのリストを返します。（カウントの降順）
# A9セル
df1["ProductName"].value_counts() # 列のカウントリストを返す結果
「ProductName」列の一部のデータに色とサイズが含まれているので、次のセクションでクリーニングを行います。
例）Mountain-100 Silver, 48（製品名 色, サイズ）
２．データをクリーニング、加工する
前セクションで「ProductName」列のラインナップとカウントを確認しました。
一部の「ProductName」列のデータに色とサイズが含まれているので、DataFrameに以下のクリーニング、加工を行いたいと思います。
- 色とサイズの列を追加する
- 「ProductName」列は製品名だけにする
以下は、ProductNameの一部抜粋です。「,」の後ろはサイズかと思いきや一部のデータは色です。サンプルコードはややベタな感じになりましたが、３種類のロジック（サンプル１,２,３）を以下に記します。
# A11セル（関数を定義）
# ProductNameから色を取得する関数
def getColor(para):
colors = ["Black", "Blue", "Red", "Yellow", "Silver"]
for color in colors:
if color in para:
return color
else:
return None
# ProductNameからサイズを取得する関数
def getSize(para):
sizes = ["38", "40", "42", "44", "46", "48", "50", "52", "54", "56", "58", "60", "62", "S", "M", "L", "XL"]
for size in sizes:
if (", " + size) in para:
return size
else:
return None
# ProductNameから色、サイズを除く関数
def replace(para):
color = getColor(para)
size = getSize(para)
if color is not None:
para = para.replace(color, "") # 色を消す
if size is not None:
para = para.replace(", " + size, "") # サイズを消す
para = para.replace(",", "") # カンマを消す
para = str.strip(para) # スペースを消す
return paraサンプル１ iterrows, map（ループ）
5, 6行目で取得済みの色とサイズを使って、ループしながら「ProductName」列の値を加工した「NewName」列を追加しています。17行目をコメントアウトしていますが、元の列を上書きしようとするとエラーになるので新しい列（NewName）を追加しています。
21行目のように fillna メソッドを使うと欠損値を書き換えることができます。
# A12セル
df2 = df1.copy()
# 色とサイズを別の列に追加
df2["Color"] = df2["ProductName"].map(getColor)
df2["Size"] = df2["ProductName"].map(getSize)
# ProductNameをクリーニング
for index, data in df2.iterrows():
tmp = data["ProductName"]
if data["Color"] is not None:
tmp = tmp.replace(data["Color"], "") # 色を消す
if data["Size"] is not None:
tmp = tmp.replace(", " + data["Size"], "") # サイズを消す
tmp = tmp.replace(",", "") # カンマを消す
tmp = str.strip(tmp) # スペースを消す
#df2.loc[index, "ProductName"] = tmp # 上書きしようとするとエラー
df2.loc[index, "NewName"] = tmp
# NaNを置換
df2["Color"] = df2["Color"].fillna("-")
df2["Size"] = df2["Size"].fillna("-")
df2結果 件数が多い（60398件）からか結果が返るまでに15秒位かかる
結果 17行目のコードはエラーが出る
サンプル２ mapのみ
加工する列３つともmapで処理します。
# B12セル
df3 = df1.copy()
# 色とサイズを別の列に追加
df3["Color"] = df3["ProductName"].map(getColor)
df3["Size"] = df3["ProductName"].map(getSize)
# NaNを置換
df3["Color"] = df3["Color"].fillna("-")
df3["Size"] = df3["Size"].fillna("-")
# ProductNameをクリーニング
df3["ProductName"] = df3["ProductName"].map(replace)
df3結果
サンプル３ merge（別のDataFrameで加工してから結合）
一旦、重複のない ProductName の DataFrame を作成して加工してから、元の DataFrame に結合しています。
# C12セル
# ProductNameのマスタ（のようなもの）を作成
sr1 = df1["ProductName"].value_counts()
df4 = pd.DataFrame({sr1.name: sr1}) # seriesをdataframeに変換
df4.reset_index(inplace= True) # インデックスを列に変換
df4.columns = ["ProductName", "Count"] # 列名を変更
# 色、サイズ、新しい名前の列を追加
df4["Color"] = df4["ProductName"].map(getColor).fillna("-")
df4["Size"] = df4["ProductName"].map(getSize).fillna("-")
df4["NewName"]=df4["ProductName"].map(replace)
# 元のdataframeと結合
df5 = df1.merge(df4, on="ProductName", how="inner")
df5.drop(columns=["Count"], inplace=True) # 不要な列を除く
df5結果
３．複数列のデータを計算した列を追加する
売上金額からコストを引いた利益（GrossProfit）の列を追加します。
# A15セル
df1["GrossProfit"] = df1["SalesAmount"] - df1["TotalProductCost"]結果
まとめ
データラングリング、クリーニング自体はエクセラーの人には馴染みのある処理で難しくはないですが、DataFrameで同様の処理を行う場合、沢山ある DataFrame や Series のメソッドに慣れる必要がありそうです。
全てのメソッドを紹介することはできそうにないので、以下に公式サイトのリンクを置いておきます。
参考
コメント
[…] Pandasはデータラングリングに非常に強力な機能を提供しています。データラングリングとは、生データを分析可能な形式に変換する一連のプロセスを指します。以下に、Pandasの主なデータラングリング機能をいくつか紹介します123。 […]