Exploratory Seminar #40 - Introduction to Various Filter Types

There are various Filter types in Exploratory such as Step Filter, Chart Filter, Table View Filter, etc. In this seminar, Kan will introduce all things related to filtering the data in Exploratory and discuss when and how you want to use them.

* Step Filter
* Chart / Analytics Filter
* Table View Filter
* Chart - Limit Values
* Filter with Parameter
* Highlight - Summary View & Chart

Twitter ↓
https://twitter.com/ExploratoryData

UI Tool: Exploratory(https://exploratory.io/)
Exploratory Online Seminar: https://exploratory.io/online-seminar

Transcript

1. EXPLORATORY Online Seminar #40 Introduction to Various Filter Types
2. Kan Nishida CEO/co-founder Exploratory Summary In Spring 2016, launched Exploratory, Inc. to democratize Data Science. Prior to Exploratory, Kan was a director of product development at Oracle leading teams to build various Data Science products in areas including Machine Learning, BI, Data Visualization, Mobile Analytics, Big Data, etc. While at Oracle, Kan also provided training and consulting services to help organizations transform with data. @KanAugust Speaker
3. Vision Everyone will be making data informed decisions.
4. Mission Democratize Data Science
5. 5 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis Data Science Workflow
6. 6 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis ExploratoryɹModern & Simple UI
7. EXPLORATORY Online Seminar #40 Introduction to Various Filter Types
8. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
9. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
10. Filter is to keep only the rows that match with a given condition(s). 10
11. 11 Filter - Keep only the 2020 data. Year Country Sales 2019 Japan 1200 2019 US 900 2019 UK 500 2020 Japan 2000 2020 US 1500 2020 UK 800 Year Country Sales 2020 Japan 2000 2020 US 1500 2020 UK 800
12. 12 Filter - Keep only Japan data. Year Country Sales 2019 Japan 1200 2019 US 900 2019 UK 500 2020 Japan 2000 2020 US 1500 2020 UK 800 Year Country Sales 2019 Japan 1200 2020 Japan 2000
13. 13 Filter - Keep only the data with Sales being greater than $1,000. Year Country Sales 2019 Japan 1,200 2019 US 900 2019 UK 500 2020 Japan 2,000 2020 US 1,500 2020 UK 800 Year Country Sales 2019 Japan 1,200 2020 US 2,000 2020 UK 1,500
14. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
15. • Step Filter • Chart (or Analytics) Filter • Table View Filter 15 There are 3 main types of Filters in Exploratory.
16. • Step Filter • Chart (or Analytics) Filter • Table View Filter 16 There are 3 main types of Filters in Exploratory.
17. 17 We want to filter to keep only 2020 data based on the Order Date column.
18. 18 Select ‘Filter’ -> ‘Equal to’ -> ‘Year’ from the column header menu.
19. 19 Type 2020 as the value. This makes the filter condition to be ‘Order Date is 2020’.
20. 20 Now the data is only for 2020. And the filter definition is registered as a new Step.
21. • Step Filter • Chart (or Analytics) Filter • Table View Filter 21 There are 3 main types of Filters in Exploratory.
22. 22 Create a chart with the Order Date column.
23. 23 A chart is created to show the number of rows for each month.
24. 24 You can assign a column to ‘Color’ to separate the line to multiple groups.
25. 25 This chart is visualizing the data from the Step 5.
26. 26 Now, we can filter the chart data with the Chart Filter.
27. 27 This will filter the data only for this chart, without creating a new Step.
28. 28 We can duplicate this chart and save as a new chart.
29. 29 This time, we can update the existing filter to ‘Office Supplies’.
30. 30 Data Import Create Calculations Filter: Year == 2020
31. 31 Furniture Office Supplies Data Import Create Calculations Filter: Year == 2020 Step Filter Chart Filter
32. 32 Now, the chart is ‘pinned’ to the ‘Step 5 - Filter’. This means it is showing only the 2020 data.
33. 33 If you want to see the whole data without the 2020 filter you can move the ‘pinned’ position to the previous step by drag-and-drop.
34. • Step Filter • Chart (or Analytics) Filter • Table View Filter 34 There are 3 main types of Filters in Exploratory.
35. 35 If you want to find a particular set of data quickly, try Table View Filter. I wanted to see the orders for products that contains ‘Smart Phone’ in their names.
36. 36 Just like the Chart filter, this won’t create a Step (right-hand-side). It lives only inside the Table view.
37. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
38. • Numeric • Character • Logical • Date, POSIXct 38 Most Often Used Data Types
39. 39 Filtering operators vary depending on the data type.
40. 40 The operator types for Numeric data type columns.
41. 41 The operator types that are specific to Character data type columns.
42. 42 The operator types that are specific to Logical data type columns.
43. 43 The operator types that are specific to Date / POSIXct data type columns.
44. Date POSIXct Date and Time information. Only Date information.
45. 45 For the Date / POSIXct columns, you can set the unit of date and time data when you create a Filter condition.
46. 46 Inside the Filter dialog.
47. Absolute Date Filter • equal to / not equal to • is in / is not in • earlier than • later than • between
48. 2018 2017 Year == 2017 2016 Year > 2016 2019 Between 2017-06-01 and 2018-1-30 Absolute Date
49. Relative Date • Previous Year • This Year • Last <N> Years • Year to Date Year
50. Today 2018 2017 Previous Year This Year 2016 Last 2 Years 2019 Year to Date Relative Date
51. 51 For Date / POSIXct columns,
53. 53 Here, I’m setting to keep only the data in the last 12 months. When I open this data next month the period will be different.
54. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
55. 55 Let’s say we want to keep only the orders with the sales greater than the average, which is $756.
56. 56 You can type in ‘$756’ as the value in the Filter dialog.
57. 57 The problem is, when you update the data by re-importing the latest data it is not guaranteed that the mean value will be the same.
58. 58 This means, you will need to manually modify the value every time you re-import the data.
59. Here comes ‘Filter with Summarize Function’! 59
60. 60 Summarize functions return summarized values such as mean, sum, etc. Average Sales 200 Order Date Country Sales 2020-01-02 Japan 50 2020-05-11 Japan 150 2021-02-13 Japan 400
61. 61 Average Sales 200 Order Date Country Sales 2020-01-02 Japan 50 2020-05-11 Japan 150 2021-02-13 Japan 400 You can use the summarize functions (e.g. mean) to make your filter condition reflect the data dynamically.
62. 62 You can use the Summarize Function for Numeric and Date/POSIXct columns. Numeric Date/POSIXct
63. 63 Select ‘Summarize Function’ and select ‘Mean (Average)’.
64. 64 Now we have only the rows with Sales being greater than the average.
65. 65 There is another very common scenario where you want to use the summarize function in the Filter.
66. 66 Let’s say you want to keep only the latest part of a given data. The last month of ‘Order Date’ is December, 2020.
67. 67 You can enter something like ‘2020-12’ as the latest month.
68. 68 But, when you re-import the data in the next month, the ‘2020-12’ will not be the latest month.
69. 69 You can use a Summarize Function ‘Max’ to return the latest month dynamically.
70. 70 Now, it’s guaranteed to filter the data that is always the latest month regardless of when you re-import the data.
71. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
72. 72 Me Team Mates Exploratory Server Publish
73. My team mates can open the data page in their web browsers and download the data.
74. But, here is a problem…
75. 75 Me Team Mates I want data for Europe. I want data for Africa. I want data for Asia. Exploratory Server
76. 76 We can Parameterize the Data!
77. 77 This data uses this Filter step to filter the data by a ‘Market is USCA’ condition.
78. Instead of ‘hard-coding’ the Market value, we can use Parameter to make the Filter dynamic. You can create a new parameter from here.
79. Create a ‘List of Values (Single Select)’ this time.
80. Select the same data frame and the step before the Filter step to get a list of values from the Market column.
81. Once the parameter is created, select the new parameter ‘Market’.
82. Now, click the Parameter button to open the Parameter pane.
83. Select a value from the dropdown and click on the Run button.
84. Republish to update the data at Exploratory Server.
85. Now, you see the Parameter button!
86. In order to use the Parameter, you want to turn on the ‘Use Parameter’ mode first.
87. Just like we did in Exploratory Desktop, select a value and click the Run button!
88. Once the data is returned, then you can click on the ‘Download’ button to download the data if you want.
89. 89 Me Team Mates I want data for Europe. I want data for Africa. I want data for Asia. Exploratory Server By parameterizing the data, everyone can get their own preferred data set. Publish
90. Parameter is not only for Data.
91. You can use for Chart.
92. When you add charts to a Dashboard the related parameters will be automatically added.
93. 93 Take a look at a past seminar ‘How to Use Parameter’ for more details on Parameter.
96. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
97. 97 Visualizing the Total Sales by Country with a bar chart.
98. 98 There are a lot of countries, but most of them are in the very low range.
99. 99 We want to keep only the countries with total sales that are greater than $100,000.
100. 100 The Chart Filter is to filter the original data that is before the chart summarize it.
101. 101 But, we want to filter the data based on the summarized data.
102. 102 Order Date Country Sales 2020-01-01 US 200 2020-01-01 Japan 100 2020-01-02 US 300 ɾɾɾ ɾɾɾ ɾɾɾ 2020-12-31 UK 150 Country Total Sales US 2,000 Japan 1,200 UK 1,000 France 600 Germany 400 Summarize Original Data Summarized Data Chart
103. 103 Order Date Country Sales 2020-01-01 US 200 2020-01-01 Japan 100 2020-01-02 US 300 ɾɾɾ ɾɾɾ ɾɾɾ 2020-12-31 UK 150 Country Total Sales US 2,000 Japan 1,200 UK 1,000 France 600 Germany 400 Summarize Filter on Original Data Filter on Summarized Data This is what we want!
104. 104 Filter Limit Order Date Country Sales 2020-01-01 US 200 2020-01-01 Japan 100 2020-01-02 US 300 ɾɾɾ ɾɾɾ ɾɾɾ 2020-12-31 UK 150 Country Total Sales US 2,000 Japan 1,200 UK 1,000 France 600 Germany 400 Summarize You can use ‘Limit’ feature to filter against the summarized data.
105. 105 Select ‘Limit’ from the X-Axis menu.
106. 106 Create a condition of ‘The current Y-Axis values are greater than 100,000.’
107. 107 Now we can see only the countries with Sales being greater than $100,000.
108. 108 How about keeping the top 20 countries based on Sales?
109. 109 Select ‘Top’ and set ’20’ in the Limit Setting dialog.
110. 110 Now we can see only the top 20 countries based on the total sales amount.
111. • Filter Basics • Filter Types - Step Filter, Chart Filter, Table View Filter • Various Filter Operator Types for Various Data Types • Filter with Summarize Function • Filter with Parameter • Chart: Limit Value • Highlight - Chart & Summary View Agenda
112. 112 We want to know how Japan is, but in a compared way against other countries.
113. 113 Assign the Country column to Color.
114. Select ‘Highlight’ from the menu.
116. 116 One last thing…
117. 117 We have the Table View Filter. We can use the data only in the Table View.
118. Can we filter the data only in the Summary view?
119. Yes, you can use ‘Highlight’ feature!
120. Create a condition of ‘Country is United States.’
121. You can see how the 2,216 rows of US data is in a compared way with all the other values.
122. By clicking on the Ratio button, you can see the ratio of the US data in each column. For example, the orders from US seem to be higher chance of being Returns.
123. That’s it for today! 123
124. EXPLORATORY Online Seminar #41 4/21/2021 (Wed) 11AM PT Cohort Analysis Part 1 - Layer Cake Chart
126. Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar
127. Q & A 127
128. EXPLORATORY 128