Seminar #37 - Why Excel Users Love Exploratory? - Part 1

We have found 8 top reasons on why our users have chosen Exploratory over Excel for performing their day-to-day data analysis based on the interviews.

In this seminar, Kan will discuss the first 4 of them along with a live demo.

* Data Size Limitation & Performance
* Learning Cost
* Debuggability
* Data Reproducibility & Automation
* Specialization with Excel God
* Lack of Intuitive Understanding
* Lack of Analytical Capability
* Reproducibility for Reporting

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

Transcript

1. EXPLORATORY Online Seminar #37 Why Excel Users Love Exploratory - Part 1 ❤
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. Mission Democratize Data Science
4. 4 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis Data Science Workflow
5. 5 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis ExploratoryɹModern & Simple UI
6. EXPLORATORY Online Seminar #37 Why Excel Users Love Exploratory - Part 1 ❤
7. https://www.theguardian.com/world/2020/oct/05/england-covid-cases-error-unknown-how-many-contacts-not-traced-says-minister
8. • More than 50,000 potentially infectious people may have been missed by contact tracers and not told to self-isolate because of the data blunder. • Nearly 16,000 coronavirus cases went unreported in England. • Public Health England (PHE) admitted it was to blame for the IT error that meant 15,841 positive test results were left off the official daily figures between 25 September and 2 October.
9. “What happened here was that some of the data got truncated and it was lost,”
10. “It was down to a combination of human error and IT, that is a human using IT. It’s fundamentally an IT error but there is a bit of human error involved in this too.”
11. https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england
12. https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england
13. • PHE was responsible for collating the test results from public and private labs, and publishing the daily updates on case count and tests performed. • Much of the work is still done manually, with individual labs sending PHE spreadsheets containing their results. • One lab had sent its daily test report to PHE in the form of a CSV file – the simplest possible database format, just a list of values separated by commas. That report was then loaded into Microsoft Excel, and the new tests at the bottom were added to the main database.
14. • While CSV files can be any size, Microsoft Excel files can only be 1,048,576 rows long – or, in older versions which PHE may have still been using, a mere 65,536. • When a CSV file longer than that is opened, the bottom rows get cut off and are no longer displayed. That means that, once the lab had performed more than a million tests, it was only a matter of time before its reports failed to be read by PHE.
15. ”The error was the latest in a “pandemic of incompetence from the government. There are mistakes and there are really serious mistakes. This is a highly significant mistake that tells me the system is not fit for purpose.” Joe AndersonɺLiverpool’s mayor
16. • In 2013, an Excel error at JPMorgan masked the loss of almost $6bn (£4.6bn), after a cell mistakenly divided by the sum of two interest rates, rather than the average. • “There is no way to trace where your data comes from, there’s no audit trail (so you can overtype numbers and not know it), and there’s no easy way to test spreadsheets, for starters. • The biggest problem is that anyone can create Excel spreadsheets – badly. Because it’s so easy to use, the creation of even important spreadsheets is not restricted to people who understand programming and do it in a methodical, well- documented way,”
17. 17 Recent versionsɿ1 Million (1,048,576) rows Older versions (Excel 2003 or older)ɿ65,000 rows Data Size Limitation
18. 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting 8 Reasons Why Excel Users Love Exploratory
19. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting
20. 20 Recent versionsɿ1 Million (1,048,576) rows Older versions (Excel 2003 or older)ɿ65,000 rows Data Size Limitation
21. 21 • Even if you can import the data (without being left out), as the data size grows the slower it takes to import. • Once it goes beyond a few hundred rows it can freeze or shutdown.
22. 22 Initial Import 26 secs. 5 secs. Excel Exploratory 500,000 Rows (11 Columns) Data
23. 23 Initial Import From the 2nd times (Restarting the app, reopening the projects, etc.) 26 secs. 5 secs. Excel Exploratory Excel Exploratory 0.5 secs 500,000 Rows (11 Columns) Data 26 secs.
24. 24 57 secs. 7 secs. Excel Exploratory Excel Exploratory 1ඵ 0.6 secs. 57 secs. 1,000,000 Rows (11 Columns) Data Initial Import From the 2nd times (Restarting the app, reopening the projects, etc.)
25. 25 Excel Exploratory 0.5 M 1 M 26ඵ 57ඵ 5ඵ 7ඵ 26 secs. 57 secs. 5 secs. 7 secs. Initial Import Rows 0.5 M 1 M
26. 26 Excel Exploratory 26ඵ 57ඵ 26 secs. 57 secs. 0.5 secs. 0.6 secs. 0.5 M 1 M Rows 0.5 M 1 M From the 2nd times (Restarting the app, reopening the projects, etc.)
27. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting
28. Easy to enter data, but… As you start adding more calculations or data wrangling tasks it becomes dramatically more complicated and more error prone.
29. • Conditional Calculation with IF function • Joining Multiple Data Sets with VLOOKUP function • Clean and manipulate Text data • Visualizing Data with Charts For example…
30. • Conditional Calculation with IF function • Joining Multiple Data Sets with VLOOKUP function • Clean and manipulate Text data • Visualizing Data with Charts For example…
31. Want to create 2 groupsʢLowɺHighʣof customers based on the Sales values. 31
32. In Excel, you’d use the ‘IF’ function. This is not bad. 32
33. 33 Now, want to create 3 groupsʢLowɺMedium, Highʣof customers based on the Sales values.
34. 34 =IF(J2 > 500, "High", IF(J2 > 100, "Medium", “Low")) IF inside another IF IF function starts getting ‘nested’, and that’s nasty…
35. 35 In Exploratory, use ‘Create Category (Bin) UI’.
36. 36 Open the UI dialog from the column header menu.
38. 38 A new step is added to the right hand side.
39. 39 How about more complicated conditions?
40. 40 Create conditions with multiple columns. e.g. When ‘Sales’ is NA or ‘Returned’ is TRUE, ‘Sales’ should be 0.
41. 41 The nested functions are nasty… =IF(K2 = TRUE, 0, IF(J2 = "", 0, J2 ))
42. With Exploratory
43. Use ‘Replace with Conditions’ UI to construct multiple conditions. 43
44. 44 Open the UI dialog from the column header menu.
45. Add multiple conditions. 45
46. 46 Construct the conditions with Filter UI.
47. 47 A new step of ‘Replace with Condition’ is created.
48. • Conditional Calculation with IF function • Joining Multiple Data Sets with VLOOKUP function • Clean and manipulate Text data • Visualizing Data with Charts For example…
49. 49 Order Data Price Data Want to bring in the pricing information from Price data based on the product ID.
50. 50 =VLOOKUP(C3,Price!B:C,2,FALSE) In Excel, you can use VLOOKUP function but boy, do we still call Excel an UI tool?
51. 51 Order Data Price Data If you want to use multiple columns as the keys to join 2 data sets, sorry but I don’t know what to tell you…
52. With Exploratory
53. Use the ‘Join’ UI to define how you want to combine two data frames. 53
54. 54 Open the ‘Join’ UI dialog from the column header menu.
55. You can select from various Join operation types and set multiple columns as the Join keys. 55
56. 56 The added column(s) will be highlighted.
57. • Conditional Calculation with IF function • Joining Multiple Data Sets with VLOOKUP function • Clean and manipulate Text data • Visualizing Data with Charts For example…
58. 58 Who said there is a such thing called a company name standard!
59. People love entering names in various ways. We celebrate Diversity! 59 • UPPERCAE, lowercase, Title Case • Half-width / Full-width (Japanese) • Extra Spaces, Tabs, New Lines • Unnecessary Text Letters
60. With Exploratory
61. 61 Use ‘Text Data Wrangling’ UI to clean up the text intuitively.
62. Open the Text Data Wrangling UI dialog from the column header menu. 62
63. 63 You can see the ‘before’ and the ‘after’ in the preview.
64. 64 Remove unnecessary text letters.
66. • Conditional Calculation with IF function • Joining Multiple Data Sets with VLOOKUP function • Clean and manipulate Text data • Visualizing Data with Charts For example…
67. 1. Need to summarize (aggregate) the data first 2. Switching the column assignments is complicated 3. Different charts ask you different ways to configure Top 3 Annoyances when creating charts
68. 68 Order Data Summarize Create a Chart 1. Need to summarize (aggregate) the data first
69. 69 2. Switching the column assignments is complicated Need to assign the area that contains the target data…
70. 3. Different charts ask you different ways to configure 70 Bar Chart Scatter Chart
71. With Exploratory
72. 72 Create a chart directly from the raw data (without summarizing it separately.)
73. 73 The chart takes care of the summarization (aggregation). You can configure how you want to summarize easily.
74. 74 You can change the aggregation level such as Day, Month, Year, etc.
75. 75 You can switch the chart types without reconfiguring the column assignments.
76. 76 ‘Visualization Grammar’ based design provides a consistent way to configure the charts, and the configuration is maintained even when switching the chart types.
77. 77 ‘Visualization Grammar’ based design provides a consistent way to configure the charts, and the configuration is maintained even when switching the chart types.
78. 78 Not only rounding the Date, but also extracting a part of date information.
79. 79 Just by assigning a column, you can group the data and aggregate the numbers.
80. 80 By assigning a column to Repeat By you can create a chart for each category quickly.
81. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting
82. Data Analyst Business People Can you create a list for north American customers who are top 100 based on Sales per day
83. After a few hours of work, he came up with the list!
84. But, why some data are negative even though they are supposed to be sales per day?
85. 1. Removed NA rows 2. Removed outlier values 3. Cleaned up the customer names 4. Summarized the Sales by Customer 5. Extracted the first and the last order date for each customer 6. Extracted the customers country information 7. Filtered only for Northern America customers 8. Calculated the customer life time period for each customer 9. Calculated the sales per day for each customer 10. Keep only the top 100 customers 85 Where did it go wrong? If you have recorded what you have done to get the result…
86. Many of the data wrangling operations are done in different sheets, which makes it hard to understand what and how they are done.
87. Need to click each cell to see what kind of calculation is done there.
88. If the data is filtered those cells that need to be looked at might be hidden…
89. Again, you will need to do these in all the sheets.
90. 1. Removed NA rows 2. Removed outlier values 3. Cleaned up the customer names 4. Summarized the Sales by Customer 5. Extracted the first and the last order date for each customer 6. Extracted the customers country information 7. Filtered only for Northern America customers 8. Calculated the customer life time period for each customer 9. Calculated the sales per day for each customer 10. Keep only the top 100 customers 90 After all the works, finally you have found where the problem was made!
91. 1. Removed NA rows 2. Removed outlier values 3. Cleaned up the customer names 4. Summarized the Sales by Customer 5. Extracted the first and the last order date for each customer 6. Extracted the customers country information 7. Filtered only for Northern America customers 8. Calculated the customer life time period for each customer 9. Calculated the sales per day for each customer 10. Keep only the top 100 customers 91 Sheet 1 Once you fixed the problem, you will need to review and fix the following steps manually, one by one. Sheet 2 Sheet 3
92. Once you fixed the problem, you will need to review and fix the following steps manually, one by one.
93. With Exploratory
94. 94 All the data wrangling steps you do in Exploratory are recorded as Steps. 1. Removed NA rows 2. Removed outlier values 3. Cleaned up the customer names 4. Summarized the Sales by Customer 5. Extracted the first and the last order date 6. Extracted the customers country 7. Filtered for Northern America customers 8. Calculated the customer life time period 9. Calculated the sales per day 10. Keep only the top 100 customers
95. 95 The data wrangling steps are managed and ordered in a way the data should be reproduced.
96. 96 Each step has its own title that explains what kind of task is, and you can customize it along with comments.
97. 97 You can click each step to open what the operation is with an intuitive UI.
98. The summary view is generated automatically for each step so that you can see how the data is changed before and after the step, in an intuitive way. 98
99. 99 Clicking on a column will highlight the steps that have something to do with it.
100. Found that this calculation is subtracting the last date from the first date where it is supposed to be the opposite. 100
101. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting
102. Hey, sorry, the file I gave you had some errors. Here is the correct one! Data Analyst Business People
103. If the same file has been updated, then all you need to do is to click the Import button. 103
104. Data Analyst Business People Can you create the same list you created before, but with this month’s data?
105. Data Analyst Business People It’s the same operation and format, you can do it quickly, right?
106. Data for Last Month Data for This Month
107. Well… How did I do that…?
108. I guess I will need to do the whole thing from scratch…
109. You will need to replicate what you have done before, and this for sure will cause unexpected errors. 109
110. 110 You can automate the process but maybe you’d better off learning R or Python…
111. With Exploratory
112. All the data wrangling steps are recorded as Step automatically! 112
113. 113 If you need to switch the data file (e.g. Excel) then you can open the data source step.
114. 114 Then, just change the file.
115. Select the new file.
116. Once the new data is imported, you can click the last step, which will reproduce all the data wrangling operations for the new data.
117. 117 How about when I have another data frame that I want to apply the same data wrangling steps?
118. 118 I want to apply the same data wrangling steps that I have created for another data frame.
119. 119 I want to apply the same data wrangling steps that I have created for another data frame. A data frame I have wrangled before. A data frame I need to wrangle now.
120. 120 Select multiple steps with Shift Key and click ‘Copy Step’ button.
121. 121 Select ‘Paste Step’ from the ‘More’ menu of the data source step.
122. 122 The same data wrangling steps are automatically reproduced on top of the new data.
123. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting
124. When it comes to Excel, there is always a go-to-person! (if you’re lucky!) Excel God
125. ʮ๺ถސ٬Ұ೔͋ͨΓͷച্τοϓ100ʯσʔλ 1. Removed NA rows 2. Removed outlier values 3. Cleaned up the customer names 4. Summarized the Sales by Customer 5. Extracted the first and the last order date 6. Extracted the customers country 7. Filtered for Northern America customers 8. Calculated the customer life time period 9. Calculated the sales per day 10. Keep only the top 100 customers
126. You Boss The guy who has been taking care of the Excel left the company. Can you take care of it from now on? One day…
127. 127 • Nobody knows how the data was cleaned and transformed because there is no history or steps. • Each calculations with special Excel functions is not each to understand for others. Blackbox
128. 128 • Always the Excel works go to the same one person. It becomes a bottleneck, and it doesn’t scale. • Transferring such works is hard, and usually never happens. Bottleneck / Burnout
129. With Exploratory
130. &%' EDF - Exploratory Data Format You can export the data along with the steps to reproduce the data as an EDF, and you can share it with others so that they can import the data along with the steps to prepare the data.
131. Select a step you want to export the data for and select ‘EDF’ under the Export menu.
132. Once you receive the EDF you can import in Exploratory.
133. Simply by importing the EDF will reproduce the data the way it was created.
134. 134 You can click each step to open the related UI dialog and intuitively understand how the data was created, and update it if needed.
135. 135 By exporting your data in a reproducible way as EDF and sharing it with other members, you can collaborate with others more effectively.
136. That’s it for today! 136
137. EXPLORATORY Online Seminar #38 Why Excel Users Love Exploratory - Part 2 ❤
138. 8 Reasons Why Excel Users Love Exploratory 1. Better Performance & Bigger Data Size 2. Lower Learning Cost 3. Better Debuggability 4. Data Reproducibility & Automation 5. No Dependency on Excel God 6. Intuitive Understanding with Visualization 7. Better and Quicker Analytical Capability 8. Reproducibility for Reporting Part 2
140. Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar
141. Q & A 141
142. EXPLORATORY 142