Data Wrangling for Visualizing COVID-19 Data

Once you get your data, more often than not, you'd find a need to clean and transform the data before even visualizing it to make any sense. We call this task Data Wrangling.

In this seminar, I'm going to introduce a few commonly used techniques to clean and transform data with COVID-19 data.

* Transform Wide data to Long data
* Convert Text to Date data type
* Calculate the difference from the previous row with Lag function
* Join with another data frame

Knowing these simple techniques is essential for creating effective visualizations.

This is a part of the Data Visualization Workshop. The past recording and tutorial slides can be found at the workshop page (https://exploratory.io/note/kanaugust/Data-Visualization-Workshop-YAZ6azM0MU).

Transcript

1. EXPLORATORY Online Seminar #29 Data Wrangling for Data Visualization
2. Kan Nishida CEO/co-founder Exploratory Summary Beginning of 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. @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 #29 Data Wrangling for Data Visualization
7. COVID-19 Data (Johns Hopkins)
8. You can download this data and save it on your local PC. Then, you can import it as a local CSV file. But, It’s actually better to directly import it as a remote file. This will make it easier to re-import the data when the original data (at Github repository) is updated, which is ‘daily’.
9. Import a Remote CSV Data
10. 10 Select ‘File Data’.
11. 11 Select ‘Remote’ tab.
12. 12 Select ‘Text File (CSV, Delimited)’.
13. 13 Copy & Paste the following URL. https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/ csse_covid_19_data/csse_covid_19_time_series/ time_series_covid19_deaths_global.csv
14. 14 Click ‘OK’ button to download.
15. 15 Once you review the data, click ‘Save’ button to import.
16. 16 Type the data frame name.
17. 17 The data is imported and shown with the Summary view.
18. 18 There are a lot of columns.
19. 19 Each day is presented as a column.
20. 20 This data needs some Data Wrangling before Visualizing it!
21. • Transform ‘Wide’ Data to ‘Long’ Data • Calculate # of New Deaths & Visualize • Join with Another Data Frame Data Wrangling Tasks
22. • Transform ‘Wide’ Data to ‘Long’ Data • Calculate # of New Deaths & Visualize • Join with Another Data Frame Data Wrangling Tasks
23. 23 We want to assign ‘Date’ to X-Axis but we can select only one column.
24. 24 This type of data is called ‘Wide Data’.
25. 25 We want to have the data in this format called ‘Long Data’.
26. 26 If the data is in the long format then it’s easier to assign them to X and Y Axis.
27. 27 We want to transform the Wide data format to the Long data format.
28. 28 Select the first date column.
29. 29 Select all the date columns with Shift Key.
30. 30 Select ‘Gather (Wide to Long)’ and ‘Selected Range’ from the column header menu of any columns.
31. 31 Confirm the Start and the End columns are selected properly.
32. 32 Enter the newly created column names. For example, ‘Date’ and ‘Deaths’.
33. 33 Now you have the data in the long format.
34. 34 By the way….
35. 35 The hosted data (at Johns Hopkins) gets updated everyday. We will want to click on this ‘Re-import’ button to import the latest date in any future days.
36. 36 This means that each day will have an extra column.
37. 37 And that means that we need to manually update the ‘End’ column setting every time we re-import the data.
38. 38 Instead of selecting the columns we want to transform, why not selecting the columns we don’t want to transform? Want to Transform Don’t Want to Transform
39. 39 Click the token inside the Gather step.
40. 40 Select ‘Exclude’ instead of ‘Include’.
41. 41 We want to ‘gather’ the columns except for these columns. Don’t Want to Transform
42. 42 Select Province/State column for the Start and Long column for the End.
43. 43 Click the Run button.
44. 44 You’ll get the same result as before. Just a different way of selecting the columns!
45. 45 Now, in any future dates, all you’ll need to do to get the latest data is to simply click the Re-import button!
46. Visualize the # of Deaths Trend 46
47. 47 Go to the Chart view and move the Chart Pin to the 2nd step so that you’ll be able to use the transformed data.
48. 48 • X-Axis: Date • Y-Axis: Deaths • Calculation for Y-Axis: SUM Select ‘Line’ chart and setup as follows.
49. 49 The X-Axis looks strange.
50. 50 The dates are sorted ‘alphabetically’, not in a natural ‘Date’ order.
51. 51 The data type for ‘Date’ column is Character.
52. 52 We can change the data type from Character to Date.
53. 53 Select ‘Change Data Type’ -> ‘Convert to Date / Time’ and ???
54. 54 Don’t Panic!
55. 2017-01-01 y m d 2017-01-01 08:10:10 y m d h m s 55 All you need to know is the order in which the date components are presented.
56. 56 For example, this data is presented in Month, Day, and Year order so we can select ‘Month, Day, Year’ from the menu.
57. 57 In the opened dialog, you’ll see ‘mdy’ function being populated.
58. mdy("01-10-2015") mdy("01/10/2015") mdy("Created on 1 10 2015") 58 mdy
59. mdy_hms("10-01-2015T06:10:15") mdy_hms("10/01/2015T06:10:15") mdy_hms("Created on 10 1 2015") 59 mdy_hms
60. ymd("2015-10-01") ymd("2015/10/01") ymd("Created on 2015 October 1st") 60 ymd
61. 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") 61 ymd_hms
62. 62 Go back to the original chart and move the Pin to the new step.
63. 63 Now we can see the # of deaths along the time line.
64. 64 The data keeps increasing. Looks this is a cumulative deaths, not a daily new death.
65. 65 Go to the Table view and click the Filter button.
66. 66 • Column: Country/Region • Operator: Equal To • Value: US
67. 67 We can see only the US data.
68. 68 There are 278 rows of US data.
69. 69 Change the # of Rows setting to 500.
70. 70 We can see that the Deaths column has the cumulative number of deaths.
71. 71 The last row has the latest death number.
72. 72 Remove the filter from the Table view.
73. • Transform ‘Wide’ Data to ‘Long’ Data • Calculate # of New Deaths & Visualize • Join with Another Data Frame Data Wrangling Tasks
74. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 74
75. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 75
76. 76 Some countries have each row for each date.
77. 77 This will become a problem when we want to perform ‘Window Calculation’ later on, so we want to Summarize the data at Country level first.
78. 78 Select ‘Summarize’ from the column header menu of ‘Country/Region’ column.
79. 79 Select ‘Country/Region’ and ‘Date’ for the Group By, and select ‘Day’ as the aggregation function for the Date column.
80. 80 Select ‘Deaths’ column for the Value.
81. 81 The data is summarized. Each row represents each day for each country.
82. 82 Go back to the previous chart and move the Pin to the new step (Step 4).
83. 83 Select the Country/Region column for the Color.
84. 84 It creates ‘Other’ group when there are many unique values.
85. 85 There are 189 countries, and that will make too many lines to show in the chart. So, we want to limit the number of the countries to be show in the chart.
86. 86 Select ‘Limit’ from the Color’s menu.
87. 87 Configure it so that it will pick the top 20 countries based on the number of deaths.
88. 88 We still have the ‘Others’ Group, which we don’t want at this point.
89. 89 Click ‘Frequency 20’ button.
90. 90 Select ‘None’ from the menu and Apply.
91. 91 Now we are looking at the trend of top 20 countries based on the deaths numbers.
92. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 92
93. 93 Country Date Deaths US 2020/4/1 1000 US 2020/4/2 1200 US 2020/4/3 1300
94. 94 Country Date Deaths New Deaths US 2020/4/1 1,000 <NA> US 2020/4/2 1,200 200 US 2020/4/3 1,300 100 1,200 - 1,000 = 200 New Deaths = Current Value - Previous Value
95. 95 Country Date Deaths New Deaths US 2020/4/1 1,000 <NA> US 2020/4/2 1,200 200 US 2020/4/3 1,300 100 New Deaths = Current Value - Previous Value 1,300 - 1,200 = 100
96. 96 Country Date Deaths lag(Deaths) US 2020/4/1 1,000 <NA> US 2020/4/2 1,200 1,000 US 2020/4/3 1,300 1,200 lag(Deaths)
97. 97 Country Date Deaths lag(Deaths) New Deaths US 2020/4/1 1,000 <NA> <NA> US 2020/4/2 1,200 1,000 200 US 2020/4/3 1,300 1,200 100 New Deaths = Deaths - lag(Deaths)
98. Something to consider when you use ‘lag’ function… • The data needs to be sorted as expected. • You don’t want to ‘cross lag’. 98
99. Something to consider when you use ‘lag’ function… • The data needs to be sorted as expected. • You don’t want to ‘cross lag’. 99
100. 100 If the Date is not sorted, you end up getting values from unexpected dates. Country Date Deaths New Deaths US 2020/4/1 1000 <NA> US 2020/4/3 1300 300 US 2020/4/2 1200 -100
101. 101 The Date column needs to be sorted. Country Date Deaths New Deaths US 2020/4/1 1000 <NA> US 2020/4/2 1200 200 US 2020/4/3 1300 100
102. Something to consider when you use ‘lag’ function… • The data needs to be sorted as expected. • You don’t want to ‘cross lag’. 102
103. 103 We have multiple countries. Country Date Deaths US 2020/4/1 1000 US 2020/4/2 1200 US 2020/4/3 1300 Japan 2020/4/1 100 Japan 2020/4/2 120
104. 104 We don’t want to ‘cross lag’ across the countries. Country Date Deaths lag(Deaths) New Deaths US 2020/4/1 1000 <NA> <NA> US 2020/4/2 1200 1000 200 US 2020/4/3 1300 1200 100 Japan 2020/4/1 100 1300 -1200 Japan 2020/4/2 120 100 20
105. 105 Country Date Deaths lag(Deaths) New Deaths US 2020/4/1 1000 <NA> <NA> US 2020/4/2 1200 1000 200 US 2020/4/3 1300 1200 100 Japan 2020/4/1 100 <NA> <NA> Japan 2020/4/2 120 100 20 This is how we want.
106. 106 We want to do the ‘lag’ calculations only within an each group. Country Date Deaths US 2020/4/1 1000 US 2020/4/2 1200 US 2020/4/3 1300 Japan 2020/4/1 100 Japan 2020/4/2 120
107. 107 We can group the data frame with Group By step. Country Date Deaths US 2020/4/1 1000 US 2020/4/2 1200 US 2020/4/3 1300 Japan 2020/4/1 100 Japan 2020/4/2 120
108. Country Date Deaths lag(Deaths) US 2020/4/1 1000 <NA> US 2020/4/2 1200 1,000 US 2020/4/3 1300 1,200 Japan 2020/4/1 100 <NA> Japan 2020/4/2 120 100 Then, the ‘lag’ calculation is done only within the each group, not cross groups.
109. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 109
112. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 112
113. 113 Select ‘Group By’ from the column header of the Country/Region column.
114. 114 Each group (Country) is colored by a different color.
115. Calculate # of New Deaths & Visualize • Summarize Data by Country • Arrange (Sort) Data by Date • Group Data by Country • Calculate ‘New Death’ by using ‘lag’ function (Window Calculation) 115
116. 116 Select ‘Create Calculation’ -> Difference from -> Previous
117. 117 The calculation with the lag function is automatically populated.
118. 118 The ‘New Deaths’ column is created.
119. 119 The ‘lag’ calculation is not done when it crosses the boundary.
120. 120 Go back to the Chart view and Copy (Duplicate) the existing one.
121. 121 Make sure to move the Pin button to the last step.
122. 122 Now it is showing the daily deaths by day by each country (Top 20).
123. 123 There are some strange data. The negative values of ‘number of deaths’?
124. 124 The negative values of ‘number of deaths’? Probably, the reporting error…
125. 125 Replace Values based on Conditions
126. 126 Select ‘Create Calculation’ from the column header menu of ‘Deaths_sum’ column.
127. 127 Use ‘ifelse’ function to set 0 when the original value is less than 0.
128. 128 Now, at least there is no strange data.
129. 129 It is hard to see the trend. Let’s smooth out the lines by calculating ‘Moving Average’. This can be done as the Data Wrangling step. But, You can do it directly inside the chart, which is a bit quicker!
130. 130 Select ‘Window Calculation’ from the Y-Axis menu.
131. 131 Select ‘Moving Calculation’.
132. 132 Select ‘Mean (Average)’ to make it ‘Moving Average’ and set the window size to 7 to make it ‘7 days moving average’.
133. 133 Once you click ‘Apply’ button, you’ll see the trend of daily deaths better.
134. • Transform ‘Wide’ Data to ‘Long’ Data • Calculate # of New Deaths & Visualize • Join with Another Data Frame Data Wrangling Tasks
135. Calculate # of Deaths per Population 135
136. Calculate # of Deaths per Population • Import Country Population Data • Convert Country Name to ISO Code • Join with the Population Data 136
137. Calculate # of Deaths per Population • Import Country Population Data • Convert Country Name to ISO Code • Join with the Population Data 137
138. 138 We want to add ‘Population’ column here.
139. 139 We can download the population data from Data Catalog.
140. 140 Search with ‘population’.
141. 141 Click ‘Import’ button.
142. 142 Type a name for the Population data and click ‘Create’ button.
143. 143 There are multiple years of data.
144. 144 Country Year Population US 1960 2,000 US 1961 2,100 US ɾɾɾ ɾɾɾ US 2018 3,200 We want to keep only the last year data with ‘Filter’ step.
145. 145 Select ‘Filter’ -> ‘Equal to’.
146. 146 We can use the ‘Summarize Function’ to get the max value of the Year for each country and keep only the matching rows.
147. 147 Now we have only 2018 data.
148. 148 Ungroup Data
149. 149 We’d recommend you ‘un-group’ the data when not necessary to avoid unexpected result. You can ‘un-group’ from the Grouped button.
151. Calculate # of Deaths per Population • Import Country Population Data • Convert Country Name to ISO Code • Join with the Population Data 151
152. 152 Country Names/Codes are not matching… Original Data Population Data
153. 153 We can convert the country names / codes to various options. United States / US Country Name/Code Continent: Americas Country Name: United States ISO2C: US ISO3C: USA and others…
154. 154 Select ‘Text Data Wrangling (UI)’ -> ‘Convert’ from the column header menu.
155. 155 Select ‘Country Name’ for the From and ‘ISO2’ for the To.
156. 156 Check ‘Create New Column’ and type the name.
157. 157 There are NAs. Looks some countries are not converted properly.
158. 158 Use the Highlight feature to find those NA rows.
159. 159 These 3 countries are the NA, not converted.
160. 160 This time, we’ll simply remove them. Select ‘Filter’ -> ‘Is not in’.
161. 161 Select those 3 countries.
162. Calculate # of Deaths per Population • Import Country Population Data • Convert Country Name to ISO Code • Join with the Population Data 162
163. 163 We want to add the population column from another data frame.
164. 164 We need ‘Key’ columns to join two data frames. Country Deaths United States 200 Japan 20 China 40 Country Population United States 3,200 Japan 1,200 China 13,000 Key Column
165. 165 We’ll use the country codes. Key Columns
166. 166 Original Columns Joined Columns
167. 167 Select ‘Join’ from the column header menu.
168. 168 Select the ‘Population’ data frame and select ‘Country_iso2c’ as the key column.
169. 169 Additional columns are added from the Population data frame.
170. Calculate # of Deaths per Population • Import Country Population Data • Convert Country Name to ISO Code • Join with Population Data • Calculate ‘# of Deaths per Population’ 170
171. 171 Select ‘Create Calculation’ from the menu.
172. 172 Type the calculation.
173. 173 You get a newly calculated column.
174. 174 Create a new chart.
175. 175 Assign the ‘Death per Population’ column to Y-Axis.
176. 176 Apply the Moving Average to smooth the line.
178. 178 Select a few countries of your interest with the Chart filter.
179. 179 Now, we can see the number of deaths per population trend for the selected countries.
180. Q & A
181. Information Email kan@exploratory.io Website https://exploratory.io Twitter @KanAugust Seminar https://exploratory.io/online-seminar
182. EXPLORATORY 182