このページは解答編です。
↓ネ
↓タ
↓バ
↓レ
↓防
↓止
答えと解説
設問はこちらでした。
P-084: 顧客データ（customer.csv）の全顧客に対して全期間の売上金額に占める2019年売上金額の割合を計算し、新たなデータを作成せよ。ただし、売上実績がない場合は0として扱うこと。そして計算した割合が0超のものを抽出し、結果を10件表示せよ。また、作成したデータに欠損が存在しないことを確認せよ。
※結果は、customer.csvのレコード順に出力すること
※全期間の売上金額を「amount_all」、2019年の売上金額を「amount_2019」、売上割合を「amount_rate」とすること
解答ワークフローは以下のようになります。
今までと比較すると非常に巨大なワークフローです。今までの集大成的な問題となっています。
問題文には書かれていませんが、receipt.csvが必要になります。
基本的な考え方として、顧客データ（customer.csv）に対して売上を集計し結合する、ということになると思います。売上についてはreceipt.csv全体の合計と、2019年のみの合計を行う必要があります。また、最終的に作成したフィールドに対しての欠損確認が必要です。欠損確認は、７９問目で行いました。
ということで、やることとしては以下の通りとなります。
- receipt.csvにて年がわかるフィールドを作成する
- １のデータに対して、全体の売上の集計を行う
- １のデータに対して、２０１９のみ抽出して売上の集計を行う
- ２，３のデータをcustomer_idで結合し、２０１９年と全体の割合を計算する
- 顧客データ（customer.csv）と４を結合する
- 売上実績がないデータは0として扱うため、Nullを0に変換する
- ４で作成した項目に対して0を超えるものを抽出する
- 最後に欠損確認を行う
ツールレベルで細かく書き出してはいませんが、ある程度のまとまりで書くとこのようになると考えて良いと思います。これを順番に実現していきましょう。
実際のワークフローを作成します
最初にreceipt.csvについて不要な項目の削除と後々の計算処理のためにデータ型を変更していきたいと思います。セレクトツールでamountを数値型（Int16）に変換します。
１．receipt.csvにて年がわかるフィールドを作成する
次に、年がわかるようなフィールドを作成しましょう。sales_ymdフィールドから年を取得していきます。
このような場合は、日時ツールを使います変換するフォーマットを選択は「文字列を日付/時刻形式に変換する」を選択し、変換する文字列フィールドは「sales_ymd」、新しい列名は「sales_ymd_date」とします。入力された文字列フィールドに一致する形式を選択はプリセットの「yyyMMdd」を選択します。
次に、年を取得していきましょう。またもや日時ツールですが、以下のようなオプションで取得できます。変換するフォーマットを選択は「日付/時刻形式を文字列に変換する」を選択。変換する日時/時刻フィールドを選択は先ほど作成した「sales_ymd_date」、新しい列名は「Year」にしましょう。新しい列の形式を選択は「カスタム」でカスタム形式は「yyyy」とします。
２．１のデータに対して、全体の売上の集計を行う
ここで、全期間の売上集計を行いましょう。集計ツールを使います。customer_idでグループ化し、amountの合計を取ります。フィールド名は「amount_all」にしましょう。
３．１のデータに対して、２０１９のみ抽出して売上の集計を行う
さらに、１のデータに対して２０１９のみのデータで集計を行いましょう。
まず、フィルターツールでデータを絞ります。基本フィルターでフィールドを「Year」、演算子は「等しい」、値は「2019」と設定します。
これで２０１９年のデータに絞れているので、これに対して集計を行います。customer_idでグループ化し、amountの合計を取ります。フィールド名は「amount_2019」とでもしておきましょう。
４．２，３のデータをcustomer_idで結合し、２０１９年と全体の割合を計算する
それでは２，３で作成したデータを結合ツールで結合しましょう。結合ツールを以下のように接続します。
結合ツールの設定は以下のとおりです。キーフィールドは「customer_id」で、重複する不要な項目も削除しましょう。
なお、2019年に売上がない顧客が存在するため、すべてのデータが結合できるわけではありません。すなわち、そのような結合できなかったデータはL出力に出てきています。これらも必要になるため、J出力とユニオンする必要があります。つまり、以下のように結合ツールのLとJ出力からユニオンツールに接続する必要があります。
この後、フォーミュラツールで割合を計算します。フィールド名は「amount_rate」、データ型は数値型（Double）とします。
[amount_2019]/[amount_all]これで一旦receipt.csvの集計作業は完了となります。
５．顧客データ（customer.csv）と４を結合する
それでは、顧客データの方を見てみましょう。まず、不要な項目をセレクトツールで削除しましょう。実は必要なのは「customer_id」のみです。
次に、「結果は、customer.csvのレコード順に出力すること」とあるので、レコードIDツールでレコードの順番を保存しておきましょう。
ここから４のデータと結合していきます。
結合ツールの設定は以下の通り、キーフィールドは「customer_id」です。
なお、売上が全くない顧客もいるため、ここでもキーフィールドがマッチしないレコードが出てきます。L側に出てきているため、ユニオンツールでL出力とJ出力を結合しましょう。
６．売上実績がないデータは0として扱うため、Nullを0に変換する
ユニオンの出力を見ると、Nullが大量に出ています。
これを0に変換していきたいので、データクレンジングツールを使います。クレンジングしたいフィールドは新規作成の３フィールドです。その他、行いたい処理としてNullの置換を行います（Nullを0に置換だけ選べば問題ありません）。
７．４で作成した項目に対して0を超えるものを抽出する
ここで、amount_rateが0を超えるものを抽出しましょう。
フィルターツールで基本フィルターを使います。フィールド名は「amount_rate」、演算子は「＞」、値は「0」とします。
ここまででほぼ処理としては終わりましたが、元々のcustomer.csvの順番を復元したいと思います。
ソートツールでRecordIDの昇順に並べ替えを行います。
最後の仕上げに不要な項目（RecordID）をセレクトツールで削除します。
データ自体はサンプリングツール等で先頭から１０件を抽出すれば完了です。
８．最後に欠損確認を行う
最後に今回作成した３つのフィールド「amount_all」「amount_2019」「amount_rate」の欠損確認を行いましょう。基本的には途中でデータクレンジングツールでNullを0にしているので、欠損は出ないはずです。
基本データプロファイルツールで基本統計量をまず取得します。その後、フィルターツールでNameフィールドからNullsのみを抽出します。
これで完了です。
まとめ
今回は総まとめ問題でした。しかしながら、各内容は基本的なものばかりなので、地道にワークフローを組んでいけば問題ないはずです。
コメント