Exploratory: Data Wrangling - Working with Text Data

Kan will be introducing various data wrangling techniques to clean and transform Text data.

1. EXPLORATORY
2. Kan Nishida co-founder/CEO Exploratory Summary Beginning of 2016, launched Exploratory, Inc. to make Data Science available for everyone. Prior to Exploratory, Kan was a director of development at Oracle leading development teams for building 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 Instructor
3. Mission Make Data Science available for everyone
4. Data Science is not just for Engineers and Statisticians. Exploratory makes it possible for Everyone to do Data Science. The Third Wave
5. First Wave Second Wave Third Wave Proprietary Open Source UI & Programming Programming 2016 2000 1976 Monetization Commoditization Democratization Statisticians Data Scientists Smart Waves - Machine Learning / AI Algorithms Experience Tools Open Source UI & Automation Business Users Theme Users Exploratory
6. Questions Data Science Workflow Communication Data Access Data Wrangling Data Visualization Machine Learning / Statistics Exploration
7. Questions What you can do with Exploratory Communication Data Access Data Wrangling Visualization Machine Learning / Statistics Exploratory Data Analysis
8. Questions Communication Data Access Data Wrangling Visualization Exploratory Data Analysis Machine Learning / Statistics
9. Working with Text Data
10. Regular Expression [\d,]+(\.\d+)? “Exchange Rate for Bitcoin to USD is 14,850.00” 14,850.00 Extract numeric values from this text.
11. @[[a-zA-Z0-9].-]+$ “kan@exploratory.io” exploratory.io Extract a domain from email. Regular Expression
12. Regular Expression is super useful if you know it.
13. But. Many of the common text data handling operations can be done without it.
14. Data Wrangling for Most Common Text Data Problems
15. Unicorn Data
16. Private Companies Valued At $1B+ 224 companies (as of 2018 Jan)
18. • Scrape the Unicorn Data from CB Insight web page (https:// www.cbinsights.com/research-unicorn-companies). • Each row represents an Unicorn Company. • Each company record contains 6 columns: Company Name, Investors, Valuation, Date Joined, Country, Industry Data
19. Want to analyze how the top venture capitals are accumulating unicorns over the years.
20. But the data is not clean and not ready for analysis.
21. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
22. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
23. 1.Click plus (+) button next to data frames 2.Select “Web Scraping”
24. https://www.cbinsights.com/research-unicorn-companies Type the following URL.
27. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
28. When we have the same number of NAs for every single column, that seems to be suspicious…
29. Looks there are some entirely NA rows.
30. We can remove the rows with all NAs.
33. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
34. “Valuation ($B)” column has data that looks numeric, but it’s registered as Character data type. We want to take the number part of the data and register it as Numeric data type.
35. From Column Header Menu 1. Select “Change Data Type” 2. Select “Convert to Numeric”
36. Character to Numeric
37. Compare the unicorns based on the valuation amounts
38. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
39. “Date Joined” column has a date data but registered as Character data type. We want to convert this to Date data type so that we can understand the trend over the time series data better.
40. From Column Header Menu 1. Select “Change Data Type” 2. Select “Convert to Date / Time” 3. Select “Month, Day, Year”
41. Character to Date
42. Assign Date Joined to X-Axis and Country to Color.
43. Use Window Calculation to show the accumulated total number of Unicorns by Country
44. Cumulative total number of Unicorns by Country
45. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
46. There are multiple entries (investors) which are separated by comma.
47. We want to make each entry to have its own row so that we can count and compare the unicorns by investors easier.
48. Need a screenshot of multiple columns to emphasize that the rows are now duplicated for
49. From Column Header Menu 1. Select “Separate” 2. Select “Separate to Rows by…” 3. Select “Comma(,)”
51. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
52. There are three entries related to Google. - Google, Google Capital, Google Ventures
53. We want to merge all Google related entries into one Google by removing ‘ Capital’ and ‘ Ventures’ from the data.
54. From Column Header Menu 1. Select “Work with Text” 2. Select “Remove Text”
55. Remove “ Capital” from ‘Select Investors’ column. Note that there is a space at the beginning.
56. Add “ Ventures” to the previous function. We can use a vertical bar which is an equivalent of ‘OR’ condition.
57. All Google related entries are now merged into one Google.
58. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
59. • Some entries contain special characters. • These are the reserved special characters for ‘new line’, ‘tab’, which can often found in the data that is scraped from webpages. • We want to remove those characters.
60. From Column Header Menu 1. Select “Work with Text” 2. Select “Clean up Text Data”
61. str_clean function removes extra spaces - at the beginning / at the end, and multiple spaces in the middle of text.
63. 1. Scrape Data from Web Page. 2. Remove Empty Rows 3. Convert Character to Numeric 4. Convert Character to Date 5. Separate Text to Multiple Rows 6. Remove Letters from Text Data 7. Clean up by Removing Extra Spaces and Special Characters 8. Create ‘Other’ group
64. Show the cumulative number of unicorns by the investors.
65. At Viz View: 1. Select “Line” to Type 2. Select “Date Joined” to X Axis 3. Select “(Number of Rows)” to Y Axis 4. Select “Select Investors” to Color By
66. Define “Cumulative Sum” window function. 1. Click the hamburger icon next to Y Axis and select “Window Calculation” 2. Select “Cumulative” at Calculation Type 3. Select “Sum” at Summarize Values Using
68. But there are too many investors…
69. Only a handful investors have more than 6 Unicorns. Most of the investors have only one.
70. We can create ‘Other’ group based on the frequency - number of rows for each investor. We want to keep the top 10 investors based on their number of unicorns and group other investors under ‘Other’.
71. Open Color By Property
72. Check ‘Create ‘Other Group’ property
73. Check ‘Hide Other Group’ property
74. Adjust how many values to keep
76. You can create ‘Other’ group as a data wrangling step. (Optional)
77. From Column Header Menu 1. Select “Create ‘Other Group for” 2. Select “Least/Most Frequent Values”
78. Set the value to 10
80. Now ‘Other’ is the largest group with the most of the unicorns. We want to filter this out.
81. Create a chart filter to exclude ‘Other’. 1. Select “Select Investors” at Column 2. Select “is not equal to” at Operator 3. Select “Other” at Value
83. Credits
84. stringr Simple & Consistent Functions for Common String Operations https://stringr.tidyverse.org Hadley Wickham
85. Contact Email kan@exploratory.io Data Science Training https://exploratory.io/training Twitter @KanAugust Online Seminar https://exploratory.io/online-seminar
86. Appendix
87. Cheat Sheet for String Functions