Excelおさるくんのastro-notion-blog

• この連載企画では、データサイエンス100本ノックをPython in Excelで実行する方法を比較しながらご紹介しています。
  📖

  Python in Excelとは？

  

  Python in Excelは、Excel上で直接Pythonのコードを実行できる話題の新機能です。

  • 2023年8月に、パブリックプレビュー版が発表されました。
  • 現在は、Microsoftの一定のバージョン以降でMicrosoft 365 insidersプログラムに参加し「Beta Channel」を選択することで利用が可能です。
  • Python in Excelでは、ExcelにPython実行環境である「Anaconda」が組み込まれています。
  • 新しい関数である「PY」関数を使って、セルにPythonプログラムを書き込むと、クラウドでPythonプログラムを実行することができます。
  • Python向けの各種ライブラリとして、Pandas や Matplotlib、seaborn を使えば、データの整形やグラフ化ができたり、scikit-learn を使用すれば機械学習やデータからの予測などの機能を利用できます。

  Python in Excel の概要 - Microsoft サポート

  Excel スプレッドシートでの Python 関数の使用について説明します。

  

  https://support.microsoft.com

  

• それでは早速、演習問題006 複数条件に合致する行を抽出する②にチャレンジしてみましょう！

• データサイエンス100本ノックは、実践的でワクワクするような課題に取り組みながら、プログラミング、データ分析のスキルを楽しく習得することを目指した、データサイエンス初学者のための問題集です。
  📖

  「データサイエンス100本ノック（構造化データ加工編）」とは具体的に？
  データサイエンス初学者を対象に、データの加工・集計、統計学や機械学習を駆使したモデリングの前処理等を学べるように、データと実行環境構築スクリプト、演習問題がワンセットになったコンテンツのことです。

  

  「データサイエンス100本ノック（構造化データ加工編）」 概要

  • アクセス：GitHub（無料公開）
  • 実行環境のサポート言語：SQL、Python、R
  • 演習問題：各言語の設問100問
  • 解答例：各設問に対する解答例のファイルを用意

  GitHub - The-Japan-DataScientist-Society/100knocks-preprocess: データサイエンス100本ノック（構造化データ加工編）

  データサイエンス100本ノック（構造化データ加工編）. Contribute to The-Japan-DataScientist-Society/100knocks-preprocess development by creating an account on GitHub.

  

  https://github.com

  

• レシート明細のCSVデータをExcelで開いています。

  

• データのダウンロードの方法はこちらの動画をご覧ください。

• まず、Excelのワークシート関数を使って実行していきます。

  

演習問題006

• レシート明細データ（df_receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。
  • 顧客ID(customer_id)が"CS018205000001"
  • 売上金額（amount）が1,000以上または売上数量（quantity）が5以上

• 条件式を考える場合はベン図で考えると分かりやすいです。
• 今回のように条件が複雑な場合はいくつかの段階に分けて条件を組み立てていきます。 まず、「売上金額(amount)が1,000以上または売上数量(quantity)が5以上」の条件を組み立てます。
• このような「AまたはB」の論理演算を論理和といいます。

  

• 次に、組み立てた条件と「顧客ID(customer_id)が"CS018205000001"」との論理積を取ります。

  

• 論理積については前回の動画でもご説明いたしましたのでご確認ください。

• まずはFILTER関数を使用して条件に該当する行を抽出します。
  

  FILTER関数とは？

  FILTER関数は指定した条件に合致するデータを抽出し、結果を表形式で表示する関数です。スピルの機能により、抽出結果が複数のセルに展開されます。

  

• FILTER関数で複数条件を指定する際は「*」と「+」を使用します。

  

• 「*」は and 条件を表現します。

  

• 「+」は or 条件を表現します。

  

• 条件に該当する行を抽出できました。
  Copy

  =FILTER(A:I,(F:F="CS018205000001")*((H:H>=5)+(I:I>=1000)))

  

• 続いて、該当の列のみに絞り込みを行うためCHOOSECOLS関数を使用します。
  

  CHOOSECOLS関数とは？

  CHOOSECOLS関数は、配列やデータ範囲から特定の列を抽出するためのExcel関数です。抽出したい列番号を引数に指定して使用します。

  

• CHOOSECOLS関数を使って「1,6,7,8,9」の列を指定して抽出することができました。
  Copy

  =CHOOSECOLS(FILTER(A:I,(F:F="CS018205000001")*((H:H>=5)+(I:I>=1000))),1,6,7,8,9)

  

• 最後にCHOOSECOLS関数を使って見出し行を抽出し、VSTACK関数を使用して見出し行とデータ行を結合させます。
  

  VSTACK関数とは？

  VSTACK関数は複数の配列を垂直方向に繋げる関数で、ヘッダー行とデータ行を結合させることが可能です。

  

• 条件に該当するヘッダー行とデータ行を抽出できました。
  Copy

  =VSTACK(CHOOSECOLS(A1:I1,1,6,7,8,9),CHOOSECOLS(FILTER(A:I,(F:F="CS018205000001")*((H:H>=5)+(I:I>=1000))),1,6,7,8,9))

  

【補足】

• VSTACK関数とCHOOSECOLS関数の順番を入れ替えることで以下のように、より効率的に書き直すことができます。
  Copy

  =CHOOSECOLS(VSTACK(A1:I1,FILTER(A:I,(F:F="CS018205000001")*((H:H>=5)+(I:I>=1000)))),1,6,7,8,9)

  VSTACK関数で先にヘッダーとデータを結合することで、CHOOSECOLS関数による列番号（1,6,7,8,9）の指定を1回で済むようにしています。

指定した条件でデータを絞り込み表示することができました。

次はPython in Excelで確認します。

• レシート明細のCSVデータをExcelで開いています。

  

• 数式タブからPythonの挿入を選択します。

  

• Python in Excelを使ってDataFrameにデータを格納するにはセル範囲を選択します。

  

  列を指定するか、または表のセル範囲を指定します。

• 指定した範囲をdf_receiptという変数に格納します。
  Copy

  df_receipt = xl("A:I", headers=True)

• df_receiptに対してリストで表示したい列名を入力します。
  Copy

  df_receipt = xl("A:I", headers=True)
  df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'quantity', 'amount']]

  

• ここからPandasのqueryメソッドを使用してデータを絞り込みます。
• queryメソッドを使用すると、データフレーム内のデータに対して、文字列ベースのクエリ式を使って条件を指定し、その条件に一致するデータのみを抽出することができます。
• まず、「売上金額 (amount) が1,000以上または売上数量 (quantity) が5以上」の条件を組み立てます。
• queryで論理和をとる場合は|（パイプ）を使用し、文字列で条件を指定します。
  Copy

  df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'quantity', 'amount']].　\
  	query('amount >= 1000 | quantity >= 5')

  

  Ctrl + Enterで実行します。

• 表示がDataFrameに変わりましたら、数式バー隣のプルダウンメニューをクリック、PythonオブジェクトからExcelの値に変更します。

  

• 「売上金額 (amount) が1,000以上または売上数量 (quantity) が5以上」の行が抽出できました。

  

• 次に、この条件と「顧客ID (customer_id) が"CS018205000001"」との論理積を取ります。
• この際、&（アンパサンド）は|（パイプ）よりも優先されてしまうため、|（パイプ）の条件式に丸括弧をつけます。
• 条件式全体をシングルクォーテーションで囲み、検索する文字列をダブルクォーテーションで囲みます。
  Copy

  df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'quantity', 'amount']].　\
  	query('customer_id == "CS018205000001"　& (amount >= 1000 | quantity >= 5)')

  

• Ctrl + Enterで実行します。

  

• 条件で絞り込みしたデータが出力されました。

  

• なお、逆に条件式全体をダブルクォーテーションで囲み、検索文字列をシングルクォーテーションで囲むこともできます。
• またqueryメソッドの場合は&（アンパサンド）をand、|（パイプ）をorと記述することも可能です。
  Copy

  df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'quantity', 'amount']].　\
  	query("customer_id == 'CS018205000001'　and (amount >= 1000 or quantity >= 5)")

  

• Ctrl + Enterで実行します。

  

  条件で絞り込みしたデータが出力されました。

  以上で、データサイエンス100本ノック演習問題006 複数条件に合致する行を抽出する②をクリアしました。

• 最後までお読みいただきありがとうございます！
• この記事へのご質問やアドバイスがありましたら、ぜひコメントもお待ちしております。
• またXでもVBA、Pythonに関するアウトプットをしていますので、🔽フォローいただけますと幸いです😆