Challenge Preppin'Data 2023:Week 39

概要

アンディマレーのグランドスラム 200 勝を祝うダッシュボードを作成するため、他の 3 人のメンバー (ナダル、フェデラー、ジョコビッチ) と比較できるデータセットを作成したいと考えました。
各年 (2000 年から 2023 年) のファイルに、その年に行われた各試合の行が含まれています。
各プレイヤーが勝った最初の 200 試合のみを比較し、200 勝目までの敗戦を合わせて確認します。また、勝利ごとに、前回の勝利から経過した日数を算出します。

Requirement

すべてのファイルを 1 つのデータセットにまとめる
グランドスラムの試合のみにフィルターをかける
マレー、ナダル、フェデラー、ジョコビッチがプレーした試合のみを対象としてする
勝者のフィールドにも敗者のフィールドにも名前が入っている可能性があるため注意
各プレイヤーが勝った最初の 200 試合のみを抽出に、200 勝目までの損失データと比較できるデータセットを作成する
前回の勝利から経過した日数を算出する

問題のページ（英語）※ここからデータをダウンロード

出題者の回答のページ（英語）※動画あり

自分の解決法

複数のファイルをワールドカードでユニオンする。この際にグランドスリムにフィルターをかける

Winnerのデータを先に作る

Winnerのフィールドを複製して、フィールド名をPlayer of Interestに変更する
Player of Interestを4選手にフィルターする

次に同様にしてLoserのデータを作る

Loserのフィールドを複製して、フィールド名をPlayer of Interestに変更する
Player of Interestを4選手にフィルターする

2と3をユニオンする

この際に、TableNameを使って、Win/Lossというフィールド名に変更し、それぞれ値をWinとLossに書き換える

各選手ごとに200勝目の試合を特定し、200週目以前のデータに絞り込む

最初は、各選手ごと、WIN/LOSSごとに古い日付ごとに行番号をふる
次に、各選手ごと、WIN/LOSSごとに前回との試合の経過日数を計算する
WINでかつ200勝までは、上記の2ステップで作成した行番号と経過日数を残し、それ以外をクリアする
200勝目の日付を抽出し、それ以前のデータのみにフィルターをかける

出力
最後に、出力先やファイル名を指定したら完成です。

今回の感想

今回は計算式がもりもりでした。

FIXED関数で200勝目の日付を簡単に参照できました

〇〇ごとに行番号を指定したい場合、PARTITIONで〇〇をしたいして、データを並べ替えるとできます。

前回の試合日は、Lookup関数で1つ前のデータを参照します。