データサイエンス100本ノックをAlteryxで。19日目(解答)

100本ノック

このページは解答編です。

↓ネ

↓タ

↓バ

↓レ

↓防

↓止

答えと解説

設問はこちらでした。

P-019: レシート明細データ(receipt.csv)に対し、1件あたりの売上金額(amount)が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID(customer_id)、売上金額(amount)、付与したランクを表示させること。なお、売上金額(amount)が等しい場合は同一順位を付与するものとする。
※同順位の場合はcustomer_idの昇順に並べること(Alteryxオリジナル条件)
※同一順位について:2位が2つあれば次は3位ではなく4位となるものとする

解答ワークフローは以下のようになります。

Alteryxで実行するには急にレベル感があがりました(元がPythonなどの問題なので、そちらだと比較的簡単にできるようです)が、ちょっと頭の良い体操ということでやってみたいと思いますが、今回は新しい要素がたくさん出てきているため、大長編となっています。

順位をつけるには?

Alteryxで順位をつけるには、上から1,2,3と数字を振っていけばいいだけですので、このような場合はレコードIDツールを使うことができます。

まず、単純な順位付けを行ってみたいと思います。基本的には、売上金額(amount)で降順に並べ替えましょう(ついでに顧客ID(customer_id)でも並べ替えておきます)。

ソート設定

基本的にレコードIDツールはデフォルト設定で使うことが多いですが、今回は名前だけ「Rank」とつけておきましょう。

レコードID設定

結果はこのようになります。

これは単純に売上金額(amount)の大きな順に並び替えただけになります。順位付けの基本形としてはこのような形になります。ここまでのワークフローは以下のような形になります。

解答その1

それではここから同一順位を考慮した形にしていきましょう。つけたい順位としては以下のようになります。

データのパターンに注目すると、一つ上の行とamountが同じであれば一つ上の行のRankをコピーして上書きする、というアルゴリズムで同一順位が考慮できそうです。

このように行をまたいで処理を行うには「複数行フォーミュラ」ツールが使えます。

設定は以下のようになります。今回は既存のフィールドを更新します。更新するフィールドは「Rank」です。

今回必要となる計算式は以下の通りです。

IF [Row-1:amount]=[amount] THEN [Row-1:Rank] ELSE [Rank] ENDIF

複数行フォーミュラツールで、一つ上の行を取得するには、フィールド名の頭に「Row-1」と記載します。逆に一つ下の行を取得するには、「Row+1」と記載します。この行数というのはオプションで増やすことができ、「Row+2」「Row-3」といった表記もできます。

ということで、一つ前の行の「amount」([Row-1:amount])と現在の行の「amount」が同じであれば、というところが赤色の部分です。同じであれば、一つ前の行の「Rank」([Row-1:Rank])をもってきます(緑色部分)。そうでなければそのままにしたいので、[Rank]と記載しています(水色部分)。

最終的なワークフローとしては以下の通りです。

一番スタンダードな方法かと思いますが、データのパターンを利用しているため、よくデータを見てひらめかないとたどり着きにくいかもしれません。

2. 複数行フォーミュラツールを使わない方法

次に、複数行フォーミュラツールを使わずにやってみましょう。ちょっとトリッキーかもしれません。

つけたい順位に対してサブランクというのを考えてみたいと思います。

Rankからこのサブランクを引いて1を足すとつけたい順位が得られます。

このようなサブランクが得られるツールが実はAlteryxにはあり、タイルツールがこのようなケースで使えます。

早速設定してみましょう。今回は、タイル法は「固有値」、固有列は「amount」、「未ソートのままにする」にチェックを入れます。これで、「amount」の値が異なるものについて番号を振る形になります。

これにより、以下のようなデータが得られます。「Tile_Num」がamountの値ごとに付与されます。「Tile_SequenceNum」は各タイル内の通し番号となります。

つまり、同一順位を認めるのであればタイルツールの「Tile_Num」がランクになるのですが、今回は「同一順位は認めるが、次の順位はレコード数分スキップされた番号を付けないといけない」のでこのままではNGです。

ただ、ここまで得られれば以下のような計算式で欲しい順位が得られます。

[Rank]-[Tile_SequenceNum]+1

この計算を行うには「フォーミュラ」ツールが必要です。

以下のように設定します。出力先は既存フィールドの「Rank」を選択。計算式は上で記載した計算式となります。

最終的なワークフローは以下の通りです。

3. 売上金額ごとにカウントを取る方法

順位を振る際に、完全に計算式でやってみたいと思います。まずデータを眺めてみましょう。

順位を得るには、amountごとに各amountごとの件数を足せればよさそうです。ただし、一つ前のamountの件数を足す必要があります。また、初期値は1である必要があります。

まず、カウントを取りましょう。集計ツールを使います。

集計ツールの使い方としては、「フィールド」の部分から処理を行いたいフィールドを選択し、真ん中にあるボタンをクリックすると出てくるプルダウンメニューにて処理内容を選択すると、下側の「アクション」に処理内容が登録されるようになっています。

この設定により、amountごとのカウントが得られます。

このデータを元のデータに結合する必要があります。この場合は「結合」ツールを使います。

結合ツールは入力を二つ持ち、出力を三つもちます。二つのデータストリームをキーフィールドの値で結合し、出力します(もしくは、レコードの場所で結合も可能)。このとき、キーフィールドの値が一致したものは「J」出力、そうでなければそれぞれ「L」「R」出力に出力されます。

それを踏まえて、今回の設定は以下の通りとなります。

これにより以下のようなデータが得られます。

無事に各レコードに各amountごとの件数が付加されています。ここまでのワークフローは以下の通りです。

これでようやく最初に検討していたものの準備が整いましたので、ここから最初に検討していたところに戻ります。上のワークフローからソートを行っておく必要がありますが、説明は割愛します。

さて、最初の絵を持ってくると以下のような感じでした。データ自体は持ってくることができたので、実際にこれを実現していきましょう。

順位を得るには、amountごとに各amountごとの件数を足していくことになります。ただし、一つ前のamountの件数を足す必要があります。また、初期値は1である必要があります。

これは行をまたいだ計算になるため複数行フォーミュラツールを使います。これを計算式にすると、

IF IsNull([Row-1:amount]) THEN 1 
ELSEIF [Row-1:amount]=[amount] THEN [Row-1:Rank] ELSE [Row-1:Rank]+[Row-1:Count] ENDIF

となります。

初期値1というのは、赤色の部分でamountがNULLであれば1にする、としています。それ以外の条件としては、amountが同じであれば一つ前のランクをそのまま持ってきて、amountが異なるのであれば一つ前のamountの件数を足す(水色の部分)としています。

実際の設定は以下の通りです。今回は、新しいフィールドを作成しています。「Rank」という名称で、タイプ(型)は「Int32」、「存在しない行の値」については「NULL」に変える必要があります。これで最初の行の処理を行うためです。

最終的なワークフローは以下の通りです。

終わりに

今回は想像以上にボリューミーでした・・・。レコードIDツール、集計ツール、結合ツール、タイルツール、複数行フォーミュラツール、と新しいツールが出てきたのでぜひ使いこなしてください。いずれも非常に重要なツールです。

解答ワークフローダウンロード

コメント

タイトルとURLをコピーしました