Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Exploratory Seminar #40 - Introduction to Various Filter Types

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

19fc8f6113c5c3d86e6176362ff29479?s=128

Kan Nishida
PRO

April 01, 2021
Tweet

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,

  52. 52

  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.
  94. 94

  95. 95

  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.

  115. None
  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
  125. None
  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