Exploratory Seminar: Data Wrangling with Date / Time Data & Visualizing It

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

Transcript

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 Date & Time
10. • User Activity Data • Each row represents an user access for a fictional online service. • There are 6 columns, timestamp, user id, event type, IP address, OS, and OS version. • Download EDF Data
12. Questions 1. What is the duration (date range) of this data? 2. What is DAU (Daily Active Users) and how it’s been changed over time? 3. Which days of week (e.g. Monday) and hours are more active?
15. Character vs. Date/Time Data Type
16. Character vs. Date/Time
17. Character vs. Date/Time
18. Character vs. Date/Time Date data is recognized as character. Dates duration is igonored Sorted as character. e.g. 10 (Oct.) comes after 1 (Jan) Data: Date-unicorn.csv
19. Character vs. Date/Time Various transformation on date data is available Data is sorted as dates. Duration honors date interval.
20. Date vs. POSIXct Data Type for Data & Time
21. Date POSIXct Only Date Both Date and Time
22. By making it to Date & Time data type, you can do a lot of cool things.
23. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
24. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
27. Only codes you need to know • Year • Month • Day • Hour • Minute • Second
28. Date Format 2017-01-01 y m d 2017-01-01 08:10:10 y m d h m s
29. ymd("2015-10-01") ymd("2015/10/01") ymd("Created on 2015 October 1st") ymd
30. ymd_hms("2015-01-10T06:10:15") ymd_hms("2015/01/10 06:10:15 UTC") ymd_hms("Created on 15-01-10 at 06:10:15 AM") ymd_hms
31. mdy("01-10-2015") mdy("01/10/2015") mdy("Created on 1 10 2015") mdy
32. mdy_hms("10-01-2015T06:10:15") mdy_hms("10/01/2015T06:10:15") mdy_hms("Created on 10 1 2015") mdy_hms
33. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
34. Date/Time • 2017-06-24
35. • 2017-06-24 • June • 24th • 2017 • 175th day in 2017 • Saturday Date Attributes • 2017-06-24
36. • 2017-01-01 08:10:10 Time Attributes
37. • AM • 8 hours • 10 minutes • 10 seconds Time Attributes • 2017-01-01 08:10:10
38. month(start_time, label = TRUE) → Jan, Feb, Mar … Extract Month
39. Extract Month From Column Header Menu 1. Select “Extract” 2. Select “Month - Short Name (Jan)”
40. Extract Month
42. wday(start_time, label = TRUE) → Sun, Mon, Tue … Extract Day of Week
43. Extract Day of Week From Column Header Menu 1. Select “Extract” 2. Select “Day of Week - Short Name (Mon)”
44. Extract Day of Week
46. Ordinal - Ordered Factor • Month, Day of Week should be sorted in the natural order. • R’s factor data type supports ‘Order’ information. • Functions like ‘wday’, ‘month’, take care of it.
47. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
48. Relative vs. Absolute
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. Absolute Date • equal to / not equal to • is in / is not in • earlier than • later than • between
52. Today 2018 2017 Year == 2017 Date > 1 year ago 2016 Year > 2016 2019 Between 2017-06-01 and 2018-1-30 Absolute Date
53. Filter: Year is 2017
54. Filter: Later than 1 year ago
55. Filter: Later than 2016-12-01
56. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
57. 3 weeks 4 weeks 2 weeks First Date Last Date First Date Last Date First Date Last Date Duration
58. Calculate Duration
59. last_activity_date - first_activity_date
60. 1. Calculate lifetime for each user
61. 1. Calculate lifetime for each user
62. last_activity_date - first_activity_date
63. Duration is calculated and stored in ‘seconds’ (unit) as ‘difftime’ data type.
64. From Column Header Menu 1. Select “Change Data Type” 2. Select “Convert to Number” 3. Select “Days” 2. Convert the lifetime to numeric data type (in days)
65. 2. Convert the lifetime to numeric data type (in days)
66. as.numeric(duration, units = “days”) You can set ‘units’ inside ‘as.numeric’ function.
67. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
68. Round Date/Time • Round • Ceiling • Floor
69. Round to Day
70. Round to Week Now timestamp column shows each week’s start date
71. Round to Month Now timestamp column shows each month’s start date (i.e. 1st)
72. # round round_date(start_time, unit="week") # ceil ceiling_date(start_time, unit="week") # floor floor_date(start_time, unit="week") Round vs. Ceiling vs. Floor
73. round_date(ymd(“2017-06-24”), unit="week") → “2017-06-25" Round
74. The border is on Wednesday noon Round
75. ceiling_date(ymd(“2017-06-24”), unit="week") → “2017-06-25" Ceiling
76. The border is on Sunday midnight (0:00am) Ceiling
77. floor_date(ymd(“2017-06-24”), unit="week") → “2017-06-18" Floor
78. The border is on Sunday midnight (0:00am) Floor
79. To round date to week.. From Column Header Menu 1. Select “Round” 2. Select “Round Date ” 3. Select “Week”
80. round_date(created_at, “week”)
81. 1. Convert Character to Date / Time 2. Extract Date / Time Attributes 3. Filter with Date / Time 4. Duration 5. Round Date / Time 6. Timezone Common Tasks
82. • We have Temperature Data of London and Tokyo • Each row represents a temperature for a certain date/time in year 2016. There are 17,498 temperature data of London and 19,489 temperature data of Tokyo • Each temperature record has date/time, longitude, latitude, temperature, etc • Filename: Date-London-temp.csv and Date-Tokyo-temp.csv Timezone - Data
83. Extension Data - Weather
88. For London, 2:00pm is the peak of Average temperature → It sounds reasonable. For Tokyo, 5:00am is the peak of Average temperature → ??? When you compare hourly temperature data between London and Tokyo Data: Date-London-temp.csv, Date-Tokyo-temp.csv
89. • From the hourly temperature data of Tokyo, I want to know what time is the most hot in the day, but the time indicated by the date / time data is different from the actual time in Tokyo • We would like to compare average hourly temperatures of two cities with different time zones Problem
90. 2PM JST (Japan Standard Time) 2PM GMT (Greenwich Mean Time) Timezone
91. London Tokyo (Time difference from London: 9hours)
92. UTC (Coordinated Universal Time) • It is the base point for all other time zones in the world • POSIXct is basically based on the UTC • UTC and GMT (Greenwich Mean Time) are almost identical. (→ That is why the hourly temperature data for London is displayed correctly on the previous chart.)
93. Timezone • 2017-01-01 08:10:10 UTC • 2017-01-01 08:10:10 -900
94. Various Time zones • America/New York • America/Los_Angeles • Asia/Tokyo
95. Asia/Tokyo America/Los_Angeles America/New_York
96. with_tz # Append Timezone information with_tz(ymd_hms("2015-10-01 02:20:34”)) → "2015-09-30 19:20:34 PDT" Default value of with_tz is local machine’s timezone. In this example, PDT (Pacific Daylight Time)
97. with_tz(ymd_hms("2015-10-01 02:20:34”)) → "2015-09-30 19:20:34 PDT" with_tz(ymd_hms("2015-10-01 02:20:34"), tz = "Asia/Tokyo") → "2015-10-01 11:20:34 JST" with_tz By specifying timezone information, You can convert date/time to any timezone
98. with_tz From Column Header Menu 1. Select “Create Calculation”
99. with_tz
100. Credits
101. lubridate Do more with dates and times in R https://lubridate.tidyverse.org Garrett Grolemund Hadley Wickham Vitalie Spinu
102. Future Seminars
103. January 15th (Tuesday), 2019 • Data Wrangling: Working with Text Data Planned • Analytics 101 - When to use which algorithms? • Data Wrangling: Introduction to Regular Expression https://exploratory.io/online-seminar
104. Contact Email kan@exploratory.io Data Science Training https://exploratory.io/training Twitter @KanAugust Online Seminar https://exploratory.io/online-seminar