Exploratory Hour #41 データを集計したい

2019/12/3(水)に行ったExploratory Hourのスライドです。
このスライドでは、集計 (Summarize)の基本的な使い方とよく使う関数について紹介しています。

Transcript

1. EXPLORATORY 1
2. 2 εϐʔΧʔ നށ ܟొ Customer Succes EXPLORATORY ུྺ େֶࡏֶதʹϑʔυϩεΛݮΒͨ͢ΊʹɺֶੜஂମΛ্ཱͪ͛୅දΛ ຿ΊΔɻͦͷޙɺϏδωεΛ஌ΔͨΊʹԽֶϝʔΧʔͷσϡϙϯͱ ϑʔυςοΫܥελʔτΞοϓͰӦۀͱϚʔέςΟϯάΛܦݧɻΞϓ Ϧͷ੒௕ͷͨΊʹ͸σʔλαΠΤϯε͕ඞཁͩͱײ͡ɺΞϓϦʹಛԽ ͨ͠Ϣʔβʔͷߦಈ෼ੳπʔϧΛ։ൃ͢ΔاۀʹͯɺΞϓϦۀքͷ KPI෼ੳͳͲΛ୲౰͢Δɻݱࡏ͸Exploratory, Inc. ͰΧελϚʔαΫη εΛ୲౰͢Δ๣ΒɺσʔλͷՄࢹԽͱ୳ࡧతσʔλ෼ੳΛઐ໳ͱͯ͠ σʔλαΠΤϯεͷීٴʹऔΓ૊Ήɻ @ShirotoTakato
3. Exploratory Hour
4. ࠓिͷ࣭໰ σʔλΛूܭ͍ͨ͠
5. Customer Name Sales Mike 34 Mike 26 Mike 36 5 ܭࢉΛ࡞੒ͷ৔߹͸1ߦ1ߦʹܭࢉ݁ՌΛฦ͢ ྫɿܭࢉΛ࡞੒ʢMutateʣ Customer Name Counts Sales_avg Mike 3 32 Mike 3 32 Mike 3 32
6. Customer Name counts Sales_avg Mike 3 32 Customer Name Sales Mike 34 Mike 26 Mike 36 6 ूܭͷ৔߹͸ɺάϧʔϓ͝ͱʹ·ͱΊͯ1ߦʹܭࢉ݁ՌΛฦ͢ ྫɿूܭʢSummarizeʣ
7. 7 ूܭʢSummarizeʣ
8. ؔ਺ ఠཁ sum άϧʔϓͷதͷ஋ͷ߹ܭ n άϧʔϓͷதͷߦ਺ n_distinct άϧʔϓͷதͷҰҙͷ஋ͷ਺ mean άϧʔϓͷฏۉ஋ median άϧʔϓͷதԝ஋ min άϧʔϓ಺ͷ࠷খ஋ max άϧʔϓ಺ͷ࠷େ஋ first άϧʔϓͷ࠷ॳͷ஋ last άϧʔϓͷ࠷ޙͷ஋ ूܭ ؔ਺ 8
9. ؔ਺ ఠཁ nth άϧʔϓͷN൪໨ͷ஋ sd άϧʔϓͷඪ४ภࠩ var άϧʔϓͷ෼ࢄ IQR άϧʔϓͷ̐෼Ґൣғ(75%఺͔Β25%఺·Ͱ) mad άϧʔϓͷฏۉઈରภࠩ na_count άϧʔϓ಺ͷܽଛ஋ͷ਺ na_percent άϧʔϓ಺ͷܽଛ஋ͷׂ߹ ूܭ ؔ਺ 9
10. • جຊతͳूܭؔ਺ʢߦͷ਺ɺ߹ܭ஋ɺฏۉ஋ɺҰҙͳ஋ͷ਺ʣ • ࠷ස஋Λmodeؔ਺ͰٻΊΔ • ࠷ॳͷ஋ʢ࠷ޙͷ஋ʣͱ࠷খ஋ʢ࠷େ஋ʣͷҧ͍ • ϩδΧϧܕͷूܭؔ਺ʢTRUEͷ਺ɺTRUEͷׂ߹ʣ ूܭ (Summarize)
11. ച্σʔλ
12. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
13. ूܭ͍ͨ͠஋
14. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
15. 15 εςοϓͷྻϔομϝχϡʔ͔Βूܭ (Summarize)Λબ୒͢Δ
16. 16 ूܭͷμΠΞϩά͕දࣔ͞Εͨ
17. 17 ஋ʹߦͷ਺Λબ୒͢Δ ϓϨϏϡʔը໘ʹબ୒͞Εͨ஋͕දࣔ͞ ΕΔɻαϯϓϧ͞Εͨ5000ߦΛදࣔ͢Δ ͜ͱ͕Ͱ͖Δɻ
18. 18 άϧʔϓԽʹCustomer IDΛબ୒͢Δ Customer ID͝ͱʹάϧʔϓԽͯ͠ɺ ߦͷ਺Λूܭ͢Δ͜ͱ͕Ͱ͖ͨɻ
19. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
20. 20 ͲͷΑ͏ʹߪങͨ͠঎඼ͷछྨΛ਺͑Ε͹͍͍͔ʁ ސ٬໊ ੡඼ Mike ϖϯ Mike ϊʔτ Mike ص Tom ϊʔτ Tom ϖϯ Tom ϊʔτ
21. 21 ͲͷΑ͏ʹߪങͨ͠঎඼ͷछྨΛ਺͑Ε͹͍͍͔ʁ ސ٬໊ ੡඼ Mike ϖϯ Mike ϊʔτ Mike ص Tom ϊʔτ Tom ϖϯ Tom ϊʔτ
22. 22 Ұҙͳ஋ͷ਺Λ࢖༻͢Δͱɺॏෳͨ͠஋ΛΧ΢ϯτ ͤͣʹछྨͷ਺ΛٻΊΔ͜ͱ͕Ͱ͖Δ ސ٬໊ ੡඼ Mike ϖϯ Mike ϊʔτ Mike ص Tom ϊʔτ Tom ϖϯ Tom ϊʔτ 3छྨ 2छྨ
23. 23 ϓϥεϘλϯΛΫϦοΫͯ͠஋Λ௥Ճ͢Δ
24. 24 ஋ʹSub-CategoryΛબͼɺूܭؔ਺ʹҰҙͳ஋ͷ਺ (unique)Λબ୒͢Δ ߪങͨ͠αϒΧςΰϦʔͷछྨΛओ ܭ͢Δ͜ͱ͕Ͱ͖ͨɻ
25. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
26. 26 ஋ʹSalesΛબͼɺूܭؔ਺ʹ߹ܭ஋Λબ୒͢Δ ސ٬͝ͱʹച্ͷ߹ܭ஋Λूܭ͢Δ ͜ͱ͕Ͱ͖ͨ
27. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
28. Customer Name Country Mike China Mike Japan Mike Japan Tom US Tom Japan Tom US Customer Name Country Mike ??? Tom ??? ࠃ͸ΧςΰϦܕͷͨΊूܭͰ͖ͳ͍ʁ
29. Customer Name Country Mike China Mike Japan Mike Japan Tom US Tom Japan Tom US Customer Name Country_mode Mike Japan Tom US ࠷ස஋ʢmodeʣ
30. 30 ஋ʹCountryΛબͼɺूܭؔ਺ʹ࠷ස஋Λબ୒͢Δ ސ٬ͷ࠷΋஫จճ਺͕ଟ͍ࠃ͕Ճ Θͬͨɻ
31. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
32. ސ٬͕࠷ॳʹߪೖͨ͠೔ΛٻΊ͍ͨ
33. 33 ஋ʹOrder DateΛબͼɺूܭؔ਺ʹ࠷ॳͷ஋(first)Λબ୒͢Δ
34. 34 ຊ౰ʹ࠷ॳͷ஋Ͱ͍͍ͷ͔ʁ
35. 35 ͜ͷσʔλ͸஫จ೔ͷঢॱʹฒͼସ͑͞Ε͍ͯͳ͍
36. ࠷ॳͷ஋ vs ࠷খ஋
37. Customer Name Order Date Mike 2015-12-3 Mike 2014-7-14 Mike 2015-12-24 Customer Name ։࢝೔ Mike 2015-12-3 ࠷ॳͷ஋(first)ͷ৔߹
38. Customer Name Order Date Mike 2015-12-3 Mike 2014-7-14 Mike 2015-12-24 Customer Name ։࢝೔ Mike 2014-7-14 ࠷খ஋(min)ͷ৔߹
39. 39 ஋ʹOrder DateΛબͼɺूܭؔ਺ʹ࠷খ஋(min)Λબ୒͢Δ ސ٬͕࠷ॳʹ஫จͨ͠೔͕ूܭ ͞Εͨɻ
40. CustomerID͝ͱʹ • ஫จ݅਺ • ߪങͨ͠঎඼ͷछྨ਺ • ച্߹ܭ • ࠃ • ސ٬ͷ։࢝೔ • ฦ඼਺ ूܭ͍ͨ͠஋
41. 41 ฦ඼ͷྻ͸஋ʹTRUE͔FALSEΛͱΔϩδΧϧܕͷྻ
42. ϩδΧϧܕͷूܭؔ਺
43. • TRUEͷ਺(FALSEͷ਺) • TRUEͷׂ߹(FASLEͷׂ߹) ϩδΧϧܕͷूܭؔ਺
44. Customer Name Returned Mike TRUE Mike FALSE Mike TRUE Customer Name ฦ඼਺ Mike 2 TRUEͷ਺ͷ৔߹
45. Customer Name Returned Mike TRUE Mike FALSE Mike TRUE Customer Name ฦ඼཰ Mike 0.6666 TRUEͷׂ߹ (%)ͷ৔߹
46. 46 ஋ʹReturnedΛબͼɺूܭؔ਺ʹTRUEͷ਺Λબ୒͢Δ
47. ूܭ͢Δ͜ͱ͕Ͱ͖ͨ
48. Q&A
52. Contact Email takato@exploratory.io Twitter Hashtag #ExploratoryHour ͰπΠʔτʂ Twitter @ExploratoryJp Exploratory Hour https://bit.ly/30odd9q