1 00:00:00,420 --> 00:00:05,910 [Music] 2 00:00:10,240 --> 00:00:14,799 Hello everyone. Welcome back to the 3 00:00:12,240 --> 00:00:20,199 education track. So next up we have 4 00:00:14,799 --> 00:00:20,199 Renee and this is guess what? Renee. 5 00:00:21,439 --> 00:00:26,320 Renee is one of those duo people. And 6 00:00:23,840 --> 00:00:29,679 what exactly does she do? Uh kind of 7 00:00:26,320 --> 00:00:31,279 everything. So uh she is a cloud 8 00:00:29,679 --> 00:00:34,640 developer advocate on the Python 9 00:00:31,279 --> 00:00:36,719 advocacy at Microsoft. She is CEO and 10 00:00:34,640 --> 00:00:38,719 co-founder of tech inclusion best known 11 00:00:36,719 --> 00:00:40,719 for the girls programming network um 12 00:00:38,719 --> 00:00:43,840 which runs workshop for girls all around 13 00:00:40,719 --> 00:00:46,480 Australia. On top of this uh Renee 14 00:00:43,840 --> 00:00:48,399 started her own business connected code 15 00:00:46,480 --> 00:00:50,559 um bringing tech education uh 16 00:00:48,399 --> 00:00:52,879 opportunities to school. Uh, and 17 00:00:50,559 --> 00:00:54,960 recently Renee was awarded Champion of 18 00:00:52,879 --> 00:00:58,160 Change 2025 19 00:00:54,960 --> 00:01:00,399 uh by the women leading uh tech. And fun 20 00:00:58,160 --> 00:01:04,239 fact, she also has two cats, Artemis and 21 00:01:00,399 --> 00:01:06,159 BB. Um, so let's welcome Renee. It's not 22 00:01:04,239 --> 00:01:08,479 not all work. There's cats in there as 23 00:01:06,159 --> 00:01:09,840 well. Thank you, Ally. Uh, hello 24 00:01:08,479 --> 00:01:12,560 everybody. It's super excited to be here 25 00:01:09,840 --> 00:01:16,000 today to present my talk, who do it. 26 00:01:12,560 --> 00:01:18,240 XLSX. Um, to how we can teach Python 27 00:01:16,000 --> 00:01:20,479 through data investigation. 28 00:01:18,240 --> 00:01:22,080 Uh, as we just heard, I do a lot of 29 00:01:20,479 --> 00:01:24,159 things. Basically, they all come down to 30 00:01:22,080 --> 00:01:26,400 tech, education, community squished into 31 00:01:24,159 --> 00:01:27,920 some form together. And I really love 32 00:01:26,400 --> 00:01:29,360 context driven learning experiences, 33 00:01:27,920 --> 00:01:30,880 which we've already heard a lot about 34 00:01:29,360 --> 00:01:33,600 today. So, hopefully this is a nice 35 00:01:30,880 --> 00:01:35,439 extension of that in a slightly new 36 00:01:33,600 --> 00:01:38,320 landscape. 37 00:01:35,439 --> 00:01:41,600 Uh, so uh the today I'm going to talk 38 00:01:38,320 --> 00:01:44,560 about the world of Python, data, and AI 39 00:01:41,600 --> 00:01:47,680 in 2025 and the education landscape. Uh, 40 00:01:44,560 --> 00:01:50,799 what is Python in Excel? uh framing your 41 00:01:47,680 --> 00:01:52,399 own crime for educational purposes only. 42 00:01:50,799 --> 00:01:53,520 Uh then we're going to solve the crime 43 00:01:52,399 --> 00:01:55,119 and then I'm going to tell you how you 44 00:01:53,520 --> 00:01:56,720 can apply this to your own curriculum 45 00:01:55,119 --> 00:01:59,840 hopefully. 46 00:01:56,720 --> 00:02:03,840 Okay. So here we are in 2025. What are 47 00:01:59,840 --> 00:02:05,680 we doing in the technology landscape? 48 00:02:03,840 --> 00:02:07,520 We have a couple of questions really 49 00:02:05,680 --> 00:02:09,200 when we're kind of trying to teach kids 50 00:02:07,520 --> 00:02:11,200 technology whether they're new, whether 51 00:02:09,200 --> 00:02:13,360 they're super engaged, wherever they are 52 00:02:11,200 --> 00:02:15,920 on the spectrum of knowing stuff and 53 00:02:13,360 --> 00:02:18,959 liking stuff. How can we teach Python in 54 00:02:15,920 --> 00:02:20,879 a fun and an engaging way? Is there a 55 00:02:18,959 --> 00:02:23,440 way to make it feel more comfortable, 56 00:02:20,879 --> 00:02:25,120 familiar, a place that we could do that? 57 00:02:23,440 --> 00:02:27,200 And is there an alternative approach 58 00:02:25,120 --> 00:02:29,680 that means that maybe they can't just go 59 00:02:27,200 --> 00:02:32,160 to chatt and get it to solve the 60 00:02:29,680 --> 00:02:34,400 problems for them because we do want 61 00:02:32,160 --> 00:02:36,319 them to learn a thing. Uh why do we want 62 00:02:34,400 --> 00:02:39,040 them to learn a thing when AI can do it 63 00:02:36,319 --> 00:02:41,040 all for them theoretically? Well, from 64 00:02:39,040 --> 00:02:43,040 my own experience, AI can be a really 65 00:02:41,040 --> 00:02:44,800 useful tool. I know as a you know person 66 00:02:43,040 --> 00:02:46,720 who works at Microsoft I'm using copilot 67 00:02:44,800 --> 00:02:49,440 a lot and it is a really helpful tool 68 00:02:46,720 --> 00:02:51,680 but that is because I actually have been 69 00:02:49,440 --> 00:02:53,519 working as a developer for 10 plus years 70 00:02:51,680 --> 00:02:55,760 and I do know what I want it to 71 00:02:53,519 --> 00:02:57,440 ultimately do so it works best when I 72 00:02:55,760 --> 00:02:59,360 can give it specifications about the 73 00:02:57,440 --> 00:03:00,959 stack that I want it to use uh the 74 00:02:59,360 --> 00:03:03,680 requirements that I needed to have and 75 00:03:00,959 --> 00:03:05,680 the context that I know that I also know 76 00:03:03,680 --> 00:03:07,440 that it doesn't yet know so that's 77 00:03:05,680 --> 00:03:08,959 really useful u I also know the 78 00:03:07,440 --> 00:03:11,519 limitations and the options of things 79 00:03:08,959 --> 00:03:13,360 that are available so I know to ask for 80 00:03:11,519 --> 00:03:15,360 things that aren't realistic or, you 81 00:03:13,360 --> 00:03:17,440 know, are going to go a little bit ary. 82 00:03:15,360 --> 00:03:19,200 And I also know that sometimes, 83 00:03:17,440 --> 00:03:20,879 especially when the context is large and 84 00:03:19,200 --> 00:03:22,319 I've asked it a lot of questions, it 85 00:03:20,879 --> 00:03:24,159 kind of get can get a little bit off 86 00:03:22,319 --> 00:03:26,239 track um because it doesn't really know 87 00:03:24,159 --> 00:03:28,959 how all these pieces of context actually 88 00:03:26,239 --> 00:03:31,040 fit together uh necessarily. So when 89 00:03:28,959 --> 00:03:32,799 that goes wrong, either I can work out 90 00:03:31,040 --> 00:03:35,200 what's gone wrong and can prompt it in a 91 00:03:32,799 --> 00:03:36,720 specific way or I can prompt it or I can 92 00:03:35,200 --> 00:03:39,120 just go around the AI and do it myself 93 00:03:36,720 --> 00:03:41,280 and fix out the problems that maybe are 94 00:03:39,120 --> 00:03:43,920 too complex for it to actually deal with 95 00:03:41,280 --> 00:03:46,319 within the scope of my project. So to be 96 00:03:43,920 --> 00:03:48,720 able to do that, I did need that 10 97 00:03:46,319 --> 00:03:50,400 years of experience as a developer. So 98 00:03:48,720 --> 00:03:52,640 we do need kids to actually keep 99 00:03:50,400 --> 00:03:54,640 learning to code even as the AI tools 100 00:03:52,640 --> 00:03:56,319 get better and better. We need them to 101 00:03:54,640 --> 00:03:59,280 have that understanding and you get that 102 00:03:56,319 --> 00:04:01,439 understanding by doing. 103 00:03:59,280 --> 00:04:03,840 So I have been working with a lot of 104 00:04:01,439 --> 00:04:05,599 data recently and I've experienced some 105 00:04:03,840 --> 00:04:07,439 of these problems firsthand. I've got to 106 00:04:05,599 --> 00:04:09,120 say the AI was great when I was like yes 107 00:04:07,439 --> 00:04:10,879 I need this simple calculation done 108 00:04:09,120 --> 00:04:12,720 really fast. But the more data I added 109 00:04:10,879 --> 00:04:14,640 in and the more complicated and the more 110 00:04:12,720 --> 00:04:16,720 connections the data had, the more it 111 00:04:14,640 --> 00:04:18,320 kind of got a little bit iffy in terms 112 00:04:16,720 --> 00:04:20,079 of like how is it all going to fit 113 00:04:18,320 --> 00:04:22,160 together and the code kind of didn't 114 00:04:20,079 --> 00:04:24,560 work anymore. like if I had a better 115 00:04:22,160 --> 00:04:25,919 mental model of all this that I created 116 00:04:24,560 --> 00:04:28,160 that would be great because I'd be able 117 00:04:25,919 --> 00:04:30,639 to quickly fix it but right now I have a 118 00:04:28,160 --> 00:04:32,400 lot of AI generated code um and not and 119 00:04:30,639 --> 00:04:35,840 it doesn't have the full picture of 120 00:04:32,400 --> 00:04:38,240 things uh that it needs to have um thank 121 00:04:35,840 --> 00:04:39,600 you to my lovely husband Jack who is 122 00:04:38,240 --> 00:04:42,720 getting me out of this meeting that I'm 123 00:04:39,600 --> 00:04:46,880 not going to on my phone. 124 00:04:42,720 --> 00:04:48,960 Okay. So, uh, so that thing is that AI 125 00:04:46,880 --> 00:04:50,800 isn't great when there's a humongous 126 00:04:48,960 --> 00:04:52,639 picture which happens when you have a 127 00:04:50,800 --> 00:04:54,400 lot of data that you want to work with. 128 00:04:52,639 --> 00:04:56,240 So, how can we get kids to work with a 129 00:04:54,400 --> 00:04:58,320 lot of data so they too can experience 130 00:04:56,240 --> 00:05:00,720 the joy of working with their own data 131 00:04:58,320 --> 00:05:02,320 and finding out the hidden details in 132 00:05:00,720 --> 00:05:04,639 it? 133 00:05:02,320 --> 00:05:06,000 So, this is where Python and Excel comes 134 00:05:04,639 --> 00:05:07,520 in. So, but first I'm going to tell you 135 00:05:06,000 --> 00:05:09,280 a little bit about Python and Excel 136 00:05:07,520 --> 00:05:12,639 before I tell you about this mystery 137 00:05:09,280 --> 00:05:14,639 that I have craved for us. Okay. So what 138 00:05:12,639 --> 00:05:16,000 is Python Excel? Well, you know already 139 00:05:14,639 --> 00:05:18,639 know how you can write formulas in 140 00:05:16,000 --> 00:05:21,280 Excel. Now you can write Python in Excel 141 00:05:18,639 --> 00:05:23,120 in cells. So you can just do equals PI 142 00:05:21,280 --> 00:05:25,199 and hit tab. And now you have these fun 143 00:05:23,120 --> 00:05:26,960 little green pi boxes and you can write 144 00:05:25,199 --> 00:05:29,199 Python in there. And you don't just have 145 00:05:26,960 --> 00:05:31,840 to write a single line of Python. You 146 00:05:29,199 --> 00:05:34,160 can actually write many lines of Python 147 00:05:31,840 --> 00:05:35,759 which is really cool. The cells actually 148 00:05:34,160 --> 00:05:37,840 work together as well. So we can see a 149 00:05:35,759 --> 00:05:40,320 equals 5. I can now use a in the next 150 00:05:37,840 --> 00:05:42,320 cell down. And you can see that a plus 2 151 00:05:40,320 --> 00:05:44,080 is seven. So, you can do this for 152 00:05:42,320 --> 00:05:46,720 multiple many cells, which is very 153 00:05:44,080 --> 00:05:47,840 exciting. If you're like, "Oh, but I 154 00:05:46,720 --> 00:05:49,199 want to write a lot of code. It's going 155 00:05:47,840 --> 00:05:50,880 to be hard to see it in those tiny 156 00:05:49,199 --> 00:05:52,240 cells." Don't worry. There's this Python 157 00:05:50,880 --> 00:05:55,039 editor where you can see all of the 158 00:05:52,240 --> 00:05:57,440 code, uh, which is really handy. And you 159 00:05:55,039 --> 00:05:58,720 can also see the stuff that you want to 160 00:05:57,440 --> 00:06:00,639 print out because, as you can see here, 161 00:05:58,720 --> 00:06:03,120 you might go print hello. And the thing 162 00:06:00,639 --> 00:06:04,319 is, print hello prints a thing, but it 163 00:06:03,120 --> 00:06:05,600 doesn't return anything. It returns 164 00:06:04,319 --> 00:06:06,960 none. So, you're going to have that 165 00:06:05,600 --> 00:06:09,039 printed out, which is not what you want 166 00:06:06,960 --> 00:06:11,199 to see. But what you do want to see as a 167 00:06:09,039 --> 00:06:12,800 thing that you printed out here in your 168 00:06:11,199 --> 00:06:14,479 interpreter here. So that's really 169 00:06:12,800 --> 00:06:15,759 helpful. 170 00:06:14,479 --> 00:06:18,639 Uh other things you might want to know 171 00:06:15,759 --> 00:06:20,479 about Python Excel is you can insert it 172 00:06:18,639 --> 00:06:22,319 with this uh once you go to the data 173 00:06:20,479 --> 00:06:23,440 ribbon, you can insert a Python cell. If 174 00:06:22,319 --> 00:06:25,440 you don't want to write the three 175 00:06:23,440 --> 00:06:27,759 characters of equals pi, you can click a 176 00:06:25,440 --> 00:06:29,360 button instead. Don't worry if your 177 00:06:27,759 --> 00:06:31,440 interpreter ever gets stuck. You can 178 00:06:29,360 --> 00:06:33,120 reset your environment and it will just 179 00:06:31,440 --> 00:06:34,880 run all your code again and connect up 180 00:06:33,120 --> 00:06:36,240 properly again. Um, you've got the 181 00:06:34,880 --> 00:06:38,000 editor to pop that out and you've got 182 00:06:36,240 --> 00:06:39,919 this initialization button which will 183 00:06:38,000 --> 00:06:41,360 show you all of the stuff it imports 184 00:06:39,919 --> 00:06:42,639 before you actually start coding. So 185 00:06:41,360 --> 00:06:44,720 these are all the things that you don't 186 00:06:42,639 --> 00:06:46,800 need the children to see but will 187 00:06:44,720 --> 00:06:48,880 already be built in when you load Python 188 00:06:46,800 --> 00:06:52,479 in Excel. Most importantly for this 189 00:06:48,880 --> 00:06:54,720 demonstration is pandas or pandas as pd 190 00:06:52,479 --> 00:06:57,039 as it will be you know shortened to in 191 00:06:54,720 --> 00:06:59,840 the examples. 192 00:06:57,039 --> 00:07:02,160 So how do I get some data into my python 193 00:06:59,840 --> 00:07:05,280 in Excel? It's super easy. I've made a 194 00:07:02,160 --> 00:07:07,039 little pi but uh pi cell. I just go and 195 00:07:05,280 --> 00:07:10,400 highlight some data somewhere. Really 196 00:07:07,039 --> 00:07:12,400 easy. And then it does all of this uh 197 00:07:10,400 --> 00:07:14,240 information here for me, which is great. 198 00:07:12,400 --> 00:07:15,440 Uh so that's I didn't have to type any 199 00:07:14,240 --> 00:07:18,160 of that. It figures that out. It figures 200 00:07:15,440 --> 00:07:19,599 out the headings. Hooray. And now I can 201 00:07:18,160 --> 00:07:21,840 just I'm going to assign it to a 202 00:07:19,599 --> 00:07:23,599 variable uh student attendance, which is 203 00:07:21,840 --> 00:07:25,599 handy. And now I have this data frame. 204 00:07:23,599 --> 00:07:27,919 Once again, it doesn't print out the 205 00:07:25,599 --> 00:07:31,440 whole data frame, but you can see it if 206 00:07:27,919 --> 00:07:32,960 you need to here by doing show data type 207 00:07:31,440 --> 00:07:34,319 card. And you can see all of the 208 00:07:32,960 --> 00:07:35,599 wonderful data that you've loaded in, 209 00:07:34,319 --> 00:07:37,599 which might be handy to be like, what 210 00:07:35,599 --> 00:07:40,319 was that data again? Oh, there it is. 211 00:07:37,599 --> 00:07:42,960 So, we have some data. We can also print 212 00:07:40,319 --> 00:07:45,759 out all of that data in the uh 213 00:07:42,960 --> 00:07:48,319 interpreter as well. 214 00:07:45,759 --> 00:07:51,440 Okay. So, why would I want to use Python 215 00:07:48,319 --> 00:07:53,360 in Excel? Well, uh it's a familiar 216 00:07:51,440 --> 00:07:55,039 place. Lots of people have used 217 00:07:53,360 --> 00:07:56,639 spreadsheets before and if someone's 218 00:07:55,039 --> 00:07:58,080 like new to coding like oh I don't know 219 00:07:56,639 --> 00:07:59,520 how to do coding at all potentially 220 00:07:58,080 --> 00:08:00,960 other teachers who are like oh no I 221 00:07:59,520 --> 00:08:02,879 couldn't do coding I'm not a techie like 222 00:08:00,960 --> 00:08:04,879 that was like well do you know how to 223 00:08:02,879 --> 00:08:06,240 use a spreadsheet they're like yeah of 224 00:08:04,879 --> 00:08:07,759 course do you write the fun functions 225 00:08:06,240 --> 00:08:09,520 and that oh yeah I've done a function 226 00:08:07,759 --> 00:08:11,039 from time to time it's a friendly 227 00:08:09,520 --> 00:08:14,080 landscape who wouldn't want to do 228 00:08:11,039 --> 00:08:16,319 something in Excel it's a wonderful tool 229 00:08:14,080 --> 00:08:19,199 what I also think might be useful is it 230 00:08:16,319 --> 00:08:21,280 has a clear link to variables variables 231 00:08:19,199 --> 00:08:24,160 are a topic that is commonly hard for 232 00:08:21,280 --> 00:08:25,440 students to grasp at first. But if 233 00:08:24,160 --> 00:08:26,720 you've been using spreadsheets for a 234 00:08:25,440 --> 00:08:31,520 while, even if you've only done some 235 00:08:26,720 --> 00:08:33,919 basic formulas like cell A1 plus cell B2 236 00:08:31,520 --> 00:08:35,680 equals something in another cell, you've 237 00:08:33,919 --> 00:08:38,399 used variables. You just they don't have 238 00:08:35,680 --> 00:08:40,399 very fun names. They're called cell A1. 239 00:08:38,399 --> 00:08:42,240 That's a variable name. And people know 240 00:08:40,399 --> 00:08:44,080 that if I put different data into that 241 00:08:42,240 --> 00:08:45,760 cell, the formula answer is going to 242 00:08:44,080 --> 00:08:48,240 change. So I'm hoping that this will be 243 00:08:45,760 --> 00:08:50,720 a way for students to more easily grasp 244 00:08:48,240 --> 00:08:52,640 some of those concepts. Um, finally, 245 00:08:50,720 --> 00:08:54,320 Python Excel means, you know, you saw 246 00:08:52,640 --> 00:08:55,680 how easy it was to grab that data. I 247 00:08:54,320 --> 00:08:57,519 highlighted a thing. It worked all the 248 00:08:55,680 --> 00:09:00,000 stuff out for me. It means I get to skip 249 00:08:57,519 --> 00:09:02,320 the stuff like reading a CSV and doing 250 00:09:00,000 --> 00:09:03,920 like with open blah blah blah, looping 251 00:09:02,320 --> 00:09:06,000 through a thing, filtering that data 252 00:09:03,920 --> 00:09:07,519 out. It's actually super fast and easy. 253 00:09:06,000 --> 00:09:11,200 And so, you can skip that part and get 254 00:09:07,519 --> 00:09:13,519 to the fun part of solving a mystery. 255 00:09:11,200 --> 00:09:15,200 So, um, 256 00:09:13,519 --> 00:09:17,120 the interesting thing is though, when I 257 00:09:15,200 --> 00:09:18,959 first came to Python in Excel, I'm like, 258 00:09:17,120 --> 00:09:20,959 cool, I've done a lot of spreadsheeting. 259 00:09:18,959 --> 00:09:22,560 That's really cool. I've also written a 260 00:09:20,959 --> 00:09:24,240 lot of Python code. Bringing these two 261 00:09:22,560 --> 00:09:25,760 together is going to be super fun and 262 00:09:24,240 --> 00:09:28,160 I'm going to definitely intuitively 263 00:09:25,760 --> 00:09:29,680 understand how I can use this new tool 264 00:09:28,160 --> 00:09:31,279 in this new landscape to do something 265 00:09:29,680 --> 00:09:32,399 fun. But at first it was just like, 266 00:09:31,279 --> 00:09:34,640 okay, I'm going to do this. I'm like, 267 00:09:32,399 --> 00:09:35,680 no, I can just do that in normal Excel. 268 00:09:34,640 --> 00:09:36,720 And then I started doing another thing. 269 00:09:35,680 --> 00:09:38,480 It's like, well, I could do this with 270 00:09:36,720 --> 00:09:40,240 Python, but like doesn't really need 271 00:09:38,480 --> 00:09:42,000 Excel. It doesn't really make any sense. 272 00:09:40,240 --> 00:09:43,920 So it was this new kind of paradigm that 273 00:09:42,000 --> 00:09:46,240 I had to kind of get my mind around of 274 00:09:43,920 --> 00:09:47,760 like how to join these up and use the 275 00:09:46,240 --> 00:09:49,760 skills that I have in both in a 276 00:09:47,760 --> 00:09:52,160 different way uh to solve different 277 00:09:49,760 --> 00:09:54,560 kinds of problems. 278 00:09:52,160 --> 00:09:56,880 Uh so I found the kind of two things 279 00:09:54,560 --> 00:09:58,720 that I end up doing most in Python XL 280 00:09:56,880 --> 00:10:00,720 isn't like your normal Python like I 281 00:09:58,720 --> 00:10:02,640 wrote a script has a lot of loops and if 282 00:10:00,720 --> 00:10:04,959 statements and things in it and it isn't 283 00:10:02,640 --> 00:10:06,800 your kind of normal like there's like 284 00:10:04,959 --> 00:10:08,480 filtering and you've got sorting and 285 00:10:06,800 --> 00:10:10,160 things that you can already do in Python 286 00:10:08,480 --> 00:10:12,320 sorry in Excel and you're like well why 287 00:10:10,160 --> 00:10:14,560 would I need to do something else? So I 288 00:10:12,320 --> 00:10:17,440 found that firstly being able to index 289 00:10:14,560 --> 00:10:19,440 an whole series of data is super useful 290 00:10:17,440 --> 00:10:21,839 and indexing it by name and then 291 00:10:19,440 --> 00:10:24,720 secondly being able to map a function or 292 00:10:21,839 --> 00:10:27,839 some sort of process to that data like a 293 00:10:24,720 --> 00:10:31,120 column or whatnot of your data to make 294 00:10:27,839 --> 00:10:33,600 new data to perform some tasks is where 295 00:10:31,120 --> 00:10:35,760 the wonder of Python XL lies at least 296 00:10:33,600 --> 00:10:37,760 for me so far on my Python and Excel 297 00:10:35,760 --> 00:10:39,680 journey. Um, and all this functionality 298 00:10:37,760 --> 00:10:41,920 is easily built in with pandas as we 299 00:10:39,680 --> 00:10:44,320 saw, which is already loaded into Python 300 00:10:41,920 --> 00:10:47,200 and Excel. 301 00:10:44,320 --> 00:10:48,640 So, I'm got this example for you, but 302 00:10:47,200 --> 00:10:50,240 I'm just going to say this is a small 303 00:10:48,640 --> 00:10:52,240 example because it fits better on the 304 00:10:50,240 --> 00:10:53,920 slides. Where the magic really is going 305 00:10:52,240 --> 00:10:55,120 to happen is when you have a giant 306 00:10:53,920 --> 00:10:56,800 example. And I'm about to give an 307 00:10:55,120 --> 00:10:58,880 example of like some school students. 308 00:10:56,800 --> 00:11:00,560 And my school only has 20 students, 309 00:10:58,880 --> 00:11:02,160 which is not that fun because a kid 310 00:11:00,560 --> 00:11:03,760 could be like, I could just do this by 311 00:11:02,160 --> 00:11:05,600 hand. So, I would recommend that your 312 00:11:03,760 --> 00:11:07,519 school has 800 students or something 313 00:11:05,600 --> 00:11:09,200 like that, which would be much more fun 314 00:11:07,519 --> 00:11:10,959 and much more annoying for them to try 315 00:11:09,200 --> 00:11:12,880 and just figure it out with a pencil and 316 00:11:10,959 --> 00:11:15,920 paper. 317 00:11:12,880 --> 00:11:18,720 Okay, so we're going to solve this 318 00:11:15,920 --> 00:11:21,120 mystery. Welcome to the case of the 319 00:11:18,720 --> 00:11:23,920 missing snake. It happened at 12:00. The 320 00:11:21,120 --> 00:11:27,680 location, the science lab. The vict the 321 00:11:23,920 --> 00:11:30,000 victim, Banana, the banana python. The 322 00:11:27,680 --> 00:11:33,279 notes. After leaving her classroom at 12 323 00:11:30,000 --> 00:11:35,519 p.m., Ms. Monty returned 5 minutes later 324 00:11:33,279 --> 00:11:37,200 to feed Banana only to discover that she 325 00:11:35,519 --> 00:11:39,760 was missing. 326 00:11:37,200 --> 00:11:41,760 So we have the crime, we have the time. 327 00:11:39,760 --> 00:11:44,240 But who could have done this crime? We 328 00:11:41,760 --> 00:11:45,600 have some information on hand. The 329 00:11:44,240 --> 00:11:47,200 information is we've got the student 330 00:11:45,600 --> 00:11:49,200 attendance for the day. We have the 331 00:11:47,200 --> 00:11:50,800 timets of each student. We have the time 332 00:11:49,200 --> 00:11:54,000 it takes to get between different 333 00:11:50,800 --> 00:11:55,760 classes. We have canteen purchasers from 334 00:11:54,000 --> 00:11:57,680 the day. And we also have the side fact 335 00:11:55,760 --> 00:11:59,760 that all of year 11 and 12 are off 336 00:11:57,680 --> 00:12:01,680 campus today for an excursion. So, 337 00:11:59,760 --> 00:12:04,079 different pieces of information for this 338 00:12:01,680 --> 00:12:07,440 crime that's occurred after period 3 at 339 00:12:04,079 --> 00:12:09,760 12:00 uh on this day. So, here's our 340 00:12:07,440 --> 00:12:12,560 attendance records. We've got our room 341 00:12:09,760 --> 00:12:14,959 walking times in this uh symmetrical 342 00:12:12,560 --> 00:12:16,639 data set here. We've got our student 343 00:12:14,959 --> 00:12:19,440 timets. 344 00:12:16,639 --> 00:12:22,320 We've got canteen purchases. And then 345 00:12:19,440 --> 00:12:25,200 also we have this handy dandy detective 346 00:12:22,320 --> 00:12:27,519 data toolkit sheet that you can give 347 00:12:25,200 --> 00:12:29,279 your kids uh to hopefully help them with 348 00:12:27,519 --> 00:12:32,639 some of the ideas that they can use to 349 00:12:29,279 --> 00:12:34,639 put this together. So let's hopefully be 350 00:12:32,639 --> 00:12:36,160 able to find out who has released banana 351 00:12:34,639 --> 00:12:38,320 and hopefully we will be able to 352 00:12:36,160 --> 00:12:40,639 retrieve banana. 353 00:12:38,320 --> 00:12:42,480 Okay. So to give this to students I 354 00:12:40,639 --> 00:12:44,720 would be providing something along the 355 00:12:42,480 --> 00:12:47,120 lines of this. Um, I've got all of these 356 00:12:44,720 --> 00:12:48,800 uh different uh pieces of data already 357 00:12:47,120 --> 00:12:50,959 loaded up in here in the different 358 00:12:48,800 --> 00:12:53,920 sheets of the table. And then I've given 359 00:12:50,959 --> 00:12:55,600 a data analysis template, but you could 360 00:12:53,920 --> 00:12:57,200 just go be like, "No, figure it out 361 00:12:55,600 --> 00:12:58,720 yourself, children." Or you can give, 362 00:12:57,200 --> 00:13:00,000 you know, less more details. You could 363 00:12:58,720 --> 00:13:02,959 just give these big headings, whatever 364 00:13:00,000 --> 00:13:04,399 you'd like. That's up to you. 365 00:13:02,959 --> 00:13:05,920 But here are the steps that I am going 366 00:13:04,399 --> 00:13:08,800 to take to solve this mystery here with 367 00:13:05,920 --> 00:13:10,480 you today. Firstly, I want to find all 368 00:13:08,800 --> 00:13:13,120 the locations in the school that are 369 00:13:10,480 --> 00:13:14,720 within a five minute walk of the science 370 00:13:13,120 --> 00:13:16,959 lab. So, we know this happened in the 371 00:13:14,720 --> 00:13:19,200 first five minutes of lunch after period 372 00:13:16,959 --> 00:13:22,160 3. Let's find out where you could get 373 00:13:19,200 --> 00:13:23,600 come, where you could have come from. 374 00:13:22,160 --> 00:13:25,920 Then, we're going to find out which 375 00:13:23,600 --> 00:13:28,000 students are timetabled to be in that 376 00:13:25,920 --> 00:13:30,160 location on this, you know, for this 377 00:13:28,000 --> 00:13:31,440 period. Then, which students are 378 00:13:30,160 --> 00:13:34,240 actually at school today? Some might be 379 00:13:31,440 --> 00:13:35,920 sick, some are on that school excursion. 380 00:13:34,240 --> 00:13:38,480 uh we can join those together to work 381 00:13:35,920 --> 00:13:40,480 out who was here and was supposed to be 382 00:13:38,480 --> 00:13:44,480 nearby. And finally, we can work out if 383 00:13:40,480 --> 00:13:47,839 anybody has another alibi. 384 00:13:44,480 --> 00:13:51,040 So, here we go. We've got our school uh 385 00:13:47,839 --> 00:13:53,360 walk time table here. And what we really 386 00:13:51,040 --> 00:13:55,120 want to do is just grab this science row 387 00:13:53,360 --> 00:13:57,199 here because it's got all the distances 388 00:13:55,120 --> 00:13:58,880 or the all the walking time distances to 389 00:13:57,199 --> 00:14:00,320 the science room. So, to be able to work 390 00:13:58,880 --> 00:14:02,480 with this data, we need to be able to 391 00:14:00,320 --> 00:14:05,120 load that walk time data. we're going to 392 00:14:02,480 --> 00:14:06,480 set. So because we are working with uh 393 00:14:05,120 --> 00:14:08,399 something like we could have worked with 394 00:14:06,480 --> 00:14:09,839 either column here but for this example 395 00:14:08,399 --> 00:14:11,199 I'm going to work with the row instead 396 00:14:09,839 --> 00:14:13,440 of the column even though they are 397 00:14:11,199 --> 00:14:15,120 symmetrical. Um but it's just a way you 398 00:14:13,440 --> 00:14:16,880 can teach both columns and rows because 399 00:14:15,120 --> 00:14:19,199 we'll be working with rows later. Uh 400 00:14:16,880 --> 00:14:21,600 then we can work out which of these ones 401 00:14:19,199 --> 00:14:23,920 in this row are within a five minute 402 00:14:21,600 --> 00:14:26,480 walking distance. 403 00:14:23,920 --> 00:14:28,959 Okay. So firstly we saw how to load data 404 00:14:26,480 --> 00:14:31,040 earlier. Super easy. Uh now we are going 405 00:14:28,959 --> 00:14:32,959 to set the index to that location 406 00:14:31,040 --> 00:14:35,199 column. So it's saying, hey, this is the 407 00:14:32,959 --> 00:14:36,639 label of the column that I want you to 408 00:14:35,199 --> 00:14:38,639 use. So when I want to look something 409 00:14:36,639 --> 00:14:41,279 up, get the, you know, it's going to be 410 00:14:38,639 --> 00:14:44,639 mapped from this key to the data 411 00:14:41,279 --> 00:14:46,880 associated with the rest of that row. 412 00:14:44,639 --> 00:14:48,959 Then we are able to search for the 413 00:14:46,880 --> 00:14:50,720 science row because we've got that, you 414 00:14:48,959 --> 00:14:52,079 know, science label that we can look up. 415 00:14:50,720 --> 00:14:53,519 We can see all of the different places, 416 00:14:52,079 --> 00:14:55,760 but we don't want all of those. Some of 417 00:14:53,519 --> 00:14:58,079 these are too far too far away to make 418 00:14:55,760 --> 00:15:00,800 it to the science room in time. So here 419 00:14:58,079 --> 00:15:02,560 we go. Uh we can now search for I guess 420 00:15:00,800 --> 00:15:04,240 I've said actually we're going for 3 421 00:15:02,560 --> 00:15:05,760 minutes or less here. I guess you need 422 00:15:04,240 --> 00:15:09,600 to get in and get out with your snake 423 00:15:05,760 --> 00:15:12,320 actually uh to load uh to yeah get that 424 00:15:09,600 --> 00:15:14,720 places that are close to science uh in 425 00:15:12,320 --> 00:15:17,600 this time. So we've now got this list 426 00:15:14,720 --> 00:15:18,880 here which I've gone index set to list 427 00:15:17,600 --> 00:15:21,199 and we're going to keep this in our 428 00:15:18,880 --> 00:15:23,600 pocket for later uh because we'll be 429 00:15:21,199 --> 00:15:25,279 needing to check some students out to 430 00:15:23,600 --> 00:15:27,120 rule them out as suspicious or not 431 00:15:25,279 --> 00:15:29,440 suspicious. 432 00:15:27,120 --> 00:15:31,519 Okay, so we know where the people could 433 00:15:29,440 --> 00:15:34,880 have come from. Well, who was in those 434 00:15:31,519 --> 00:15:36,480 potential locations? So, we've got our 435 00:15:34,880 --> 00:15:38,800 list of English, science, history, and 436 00:15:36,480 --> 00:15:42,000 geography over here. Let's apply it to 437 00:15:38,800 --> 00:15:43,600 everyone in period 3 here. So, because 438 00:15:42,000 --> 00:15:44,959 that's the period that happens before 439 00:15:43,600 --> 00:15:48,639 lunch in my fictional school where you 440 00:15:44,959 --> 00:15:50,800 have lunch at 12:00, it's all fine. 441 00:15:48,639 --> 00:15:52,959 Uh, so we need to load that data again. 442 00:15:50,800 --> 00:15:55,360 Then we're going to be checking which uh 443 00:15:52,959 --> 00:15:57,120 which classes were had in period 3 and 444 00:15:55,360 --> 00:15:59,199 only keeping the ones that are in the 445 00:15:57,120 --> 00:16:01,279 list of useful data. So here we are 446 00:15:59,199 --> 00:16:03,120 working with uh columns now instead of 447 00:16:01,279 --> 00:16:04,639 rows and we can either we could print 448 00:16:03,120 --> 00:16:06,480 out all of the period 3 classes just to 449 00:16:04,639 --> 00:16:09,920 be like oh yeah that's good. Or we could 450 00:16:06,480 --> 00:16:12,560 print out all the kids who had period 3 451 00:16:09,920 --> 00:16:14,000 um and who had a student ID. You'll also 452 00:16:12,560 --> 00:16:16,480 know they're not student names. We need 453 00:16:14,000 --> 00:16:18,160 to do some data matching up later. Oh 454 00:16:16,480 --> 00:16:20,800 no. 455 00:16:18,160 --> 00:16:25,120 So here we go. Uh we have got our close 456 00:16:20,800 --> 00:16:27,120 to science uh children who are uh here 457 00:16:25,120 --> 00:16:29,279 we've got the index as we said we had 458 00:16:27,120 --> 00:16:31,040 that list already sorry and then we want 459 00:16:29,279 --> 00:16:33,519 to create I'm going to call it a filter 460 00:16:31,040 --> 00:16:35,839 almost where we are creating a series of 461 00:16:33,519 --> 00:16:39,120 booleans which is saying hey it's the 462 00:16:35,839 --> 00:16:40,560 period 3 class in the list of those ones 463 00:16:39,120 --> 00:16:43,120 we mentioned before and it's just going 464 00:16:40,560 --> 00:16:44,399 okay science yep true false false true 465 00:16:43,120 --> 00:16:45,920 true false and we're just going to have 466 00:16:44,399 --> 00:16:48,240 all of the classes we're going to have 467 00:16:45,920 --> 00:16:50,959 that true false true true false whatever 468 00:16:48,240 --> 00:16:53,279 serial list series list and we can apply 469 00:16:50,959 --> 00:16:55,680 that to the data to filter out 470 00:16:53,279 --> 00:16:58,639 everything that is false which we can do 471 00:16:55,680 --> 00:17:01,040 here. Uh so I've done it this way just 472 00:16:58,639 --> 00:17:02,959 to make it read a little bit simpler. Uh 473 00:17:01,040 --> 00:17:05,600 so you can have your timetables and we 474 00:17:02,959 --> 00:17:07,520 can have that close to science check or 475 00:17:05,600 --> 00:17:09,360 bull or whatever you want to call that 476 00:17:07,520 --> 00:17:11,039 series you've got there. And it's just 477 00:17:09,360 --> 00:17:12,880 going to get rid of everything that was 478 00:17:11,039 --> 00:17:14,640 false. Uh which is handy because now 479 00:17:12,880 --> 00:17:17,600 they're out because they are cleared of 480 00:17:14,640 --> 00:17:20,240 this snake related crime. And we have 481 00:17:17,600 --> 00:17:22,640 just the student IDs of the students who 482 00:17:20,240 --> 00:17:24,880 could have done it. 483 00:17:22,640 --> 00:17:26,640 Okay, so those are the places people 484 00:17:24,880 --> 00:17:28,400 could have come from, but you have to be 485 00:17:26,640 --> 00:17:30,320 at school today to actually come from 486 00:17:28,400 --> 00:17:32,880 that spot. So let's check the attendance 487 00:17:30,320 --> 00:17:35,120 ro to see who was here. We're going to 488 00:17:32,880 --> 00:17:36,960 be looking at the status if it was set 489 00:17:35,120 --> 00:17:39,200 to present. And we saw just a second 490 00:17:36,960 --> 00:17:40,400 ago, we could easily uh you know search 491 00:17:39,200 --> 00:17:42,320 through a column. This time we're going 492 00:17:40,400 --> 00:17:44,559 to set up a conditional. Um rather than 493 00:17:42,320 --> 00:17:46,720 doing the is in, we can just use much 494 00:17:44,559 --> 00:17:49,600 like your if statements, you can say 495 00:17:46,720 --> 00:17:50,880 attention status is equal to present. Um 496 00:17:49,600 --> 00:17:53,760 and then we're just going to get one of 497 00:17:50,880 --> 00:17:55,520 those lists again of all the booleans um 498 00:17:53,760 --> 00:17:57,600 that we can use to filter out all the 499 00:17:55,520 --> 00:17:58,640 kids that were not present today. So 500 00:17:57,600 --> 00:18:01,360 we're going to get rid of all those kids 501 00:17:58,640 --> 00:18:03,679 who are absent and we've now limited it 502 00:18:01,360 --> 00:18:06,160 down to I guess we've got all of the 503 00:18:03,679 --> 00:18:07,679 kids here now and they've got the is 504 00:18:06,160 --> 00:18:09,840 present. So most of the kids are present 505 00:18:07,679 --> 00:18:11,919 today, but we also need know something. 506 00:18:09,840 --> 00:18:14,480 No one in year 11 and 12 is on campus 507 00:18:11,919 --> 00:18:16,799 today. They're offsite. So, we can also 508 00:18:14,480 --> 00:18:18,960 add this secondary filter here which is 509 00:18:16,799 --> 00:18:21,600 called only juniors because we want to 510 00:18:18,960 --> 00:18:23,120 get everyone who is in year 11 or 12. 511 00:18:21,600 --> 00:18:26,400 They're out of here. So, we're going to 512 00:18:23,120 --> 00:18:29,679 filter once again creating this onampus 513 00:18:26,400 --> 00:18:31,280 uh boolean checklist and filter out to 514 00:18:29,679 --> 00:18:32,960 get only the junior students who have 515 00:18:31,280 --> 00:18:35,200 checked in today got that tick as 516 00:18:32,960 --> 00:18:38,480 attending. 517 00:18:35,200 --> 00:18:40,160 Great. Okay. So now we know who was on 518 00:18:38,480 --> 00:18:42,080 campus today, who's been marked as 519 00:18:40,160 --> 00:18:44,320 present, and is in in year 11 and 12. 520 00:18:42,080 --> 00:18:46,000 And we also have that list of the 521 00:18:44,320 --> 00:18:47,280 classrooms and the students that were in 522 00:18:46,000 --> 00:18:49,200 them that could have made it to the 523 00:18:47,280 --> 00:18:51,760 science lab in time to commit this 524 00:18:49,200 --> 00:18:53,200 crime. So it's time to join that data 525 00:18:51,760 --> 00:18:54,640 because, as you can see, we've got names 526 00:18:53,200 --> 00:18:55,919 over here in our attendance role, but we 527 00:18:54,640 --> 00:18:58,000 don't have any names here. It's going to 528 00:18:55,919 --> 00:19:00,640 be hard to start making accusations if 529 00:18:58,000 --> 00:19:04,160 I'm like you S0108, 530 00:19:00,640 --> 00:19:07,200 I think you did it. So 531 00:19:04,160 --> 00:19:09,840 we can use something that is called 532 00:19:07,200 --> 00:19:12,240 pandis merge here. It's super easy to 533 00:19:09,840 --> 00:19:14,559 merge two pieces of data if they have a 534 00:19:12,240 --> 00:19:17,200 matching um matching field like the 535 00:19:14,559 --> 00:19:19,520 student ID. So super easily we just map 536 00:19:17,200 --> 00:19:21,600 those together. Um and now we can see 537 00:19:19,520 --> 00:19:23,120 we've got our joint data which is this 538 00:19:21,600 --> 00:19:25,440 yellow one here that was in both. We've 539 00:19:23,120 --> 00:19:27,760 got the blue data that came from the on 540 00:19:25,440 --> 00:19:30,320 campus from the RO and then we've got 541 00:19:27,760 --> 00:19:32,640 the green one that is coming from the 542 00:19:30,320 --> 00:19:34,960 timets for the kids. 543 00:19:32,640 --> 00:19:37,360 Uh so that's all there together and we 544 00:19:34,960 --> 00:19:41,320 do only have six remaining suspects 545 00:19:37,360 --> 00:19:41,320 which is very convenient. 546 00:19:41,440 --> 00:19:47,039 Okay so we are nearly at the end of our 547 00:19:44,799 --> 00:19:49,280 journey but we do you know have a little 548 00:19:47,039 --> 00:19:51,520 more data we can use to work out we have 549 00:19:49,280 --> 00:19:54,000 who was buying lunch at the canteen at 550 00:19:51,520 --> 00:19:56,799 the time of the crime. Uh so we can see 551 00:19:54,000 --> 00:19:59,440 that we have this time data here for the 552 00:19:56,799 --> 00:20:01,360 time the purchases were made. Um, so 553 00:19:59,440 --> 00:20:04,720 maybe we can investigate that to sort 554 00:20:01,360 --> 00:20:07,120 out this a little bit more. 555 00:20:04,720 --> 00:20:10,240 We do we are working with times though, 556 00:20:07,120 --> 00:20:12,160 but I don't know about you if I I've 557 00:20:10,240 --> 00:20:14,960 never tried to teach datetime objects to 558 00:20:12,160 --> 00:20:16,799 a child and I want to keep it that way. 559 00:20:14,960 --> 00:20:18,880 Um, I wanted to keep it that way for you 560 00:20:16,799 --> 00:20:20,799 as well and give them an opportunity to 561 00:20:18,880 --> 00:20:22,559 create their own small functions that 562 00:20:20,799 --> 00:20:24,400 enable them to interact with the 563 00:20:22,559 --> 00:20:26,960 strings. Uh, so that's what we're going 564 00:20:24,400 --> 00:20:29,360 to be doing. So I created two functions. 565 00:20:26,960 --> 00:20:31,679 one that is get hour and one that is get 566 00:20:29,360 --> 00:20:33,520 minute. Um much easier than dealing with 567 00:20:31,679 --> 00:20:35,440 a datetime object that we really don't 568 00:20:33,520 --> 00:20:36,880 need. So I've created these two 569 00:20:35,440 --> 00:20:39,200 functions and this is actually a really 570 00:20:36,880 --> 00:20:40,960 great place for you to int introduce 571 00:20:39,200 --> 00:20:42,400 other concepts from your curriculum that 572 00:20:40,960 --> 00:20:45,440 you want to be including and I'll talk 573 00:20:42,400 --> 00:20:48,320 about that a little bit more later. 574 00:20:45,440 --> 00:20:51,440 So I've run my two functions here um 575 00:20:48,320 --> 00:20:53,120 applying it to that time uh field and 576 00:20:51,440 --> 00:20:55,280 I've actually been able to create two 577 00:20:53,120 --> 00:20:57,280 new columns of data by setting just like 578 00:20:55,280 --> 00:20:59,360 you could set something in a dictionary 579 00:20:57,280 --> 00:21:00,880 essentially but I can set it to a whole 580 00:20:59,360 --> 00:21:02,080 new row of data. So these ones all 581 00:21:00,880 --> 00:21:03,520 happened at 12 and here we have the 582 00:21:02,080 --> 00:21:05,440 minute column. So that's how you can 583 00:21:03,520 --> 00:21:06,880 extend on the data and get students to 584 00:21:05,440 --> 00:21:09,120 build out the information that they 585 00:21:06,880 --> 00:21:12,480 have. 586 00:21:09,120 --> 00:21:15,600 Um cool. 587 00:21:12,480 --> 00:21:18,240 Now, uh, once we have that, we are able 588 00:21:15,600 --> 00:21:20,000 to then also want to see what names 589 00:21:18,240 --> 00:21:21,600 these are of the students because we're 590 00:21:20,000 --> 00:21:23,840 once again back to student IDs. We've 591 00:21:21,600 --> 00:21:25,679 tapped in. We're buying things at the 592 00:21:23,840 --> 00:21:26,960 canteen with our student ID cards, which 593 00:21:25,679 --> 00:21:29,200 I think is a thing that actually happens 594 00:21:26,960 --> 00:21:31,039 in some places in this day and age. Um, 595 00:21:29,200 --> 00:21:32,559 but we're back to not having names. And 596 00:21:31,039 --> 00:21:34,880 also, some of these kids might have not 597 00:21:32,559 --> 00:21:37,600 have come from a place that was, you 598 00:21:34,880 --> 00:21:41,360 know, potentially suspicious. So, let's 599 00:21:37,600 --> 00:21:43,440 have a look. Uh we've got our alibi list 600 00:21:41,360 --> 00:21:45,120 and we want to say if you're not that's 601 00:21:43,440 --> 00:21:46,720 what this little title here is for. If 602 00:21:45,120 --> 00:21:49,600 you're not in the alibi list, we're 603 00:21:46,720 --> 00:21:51,440 going to keep you as a suspect. Uh so we 604 00:21:49,600 --> 00:21:54,159 can run that and see if we have maybe 605 00:21:51,440 --> 00:21:56,960 solved this crime. We have. There is one 606 00:21:54,159 --> 00:21:58,400 suspect remaining. It's Mia Kim. And I'm 607 00:21:56,960 --> 00:22:00,799 going to leave it as an exercise to the 608 00:21:58,400 --> 00:22:02,960 reader as why the delightful Mia Kim has 609 00:22:00,799 --> 00:22:04,320 stolen this snake uh today. But 610 00:22:02,960 --> 00:22:05,600 hopefully you've been able to spot all 611 00:22:04,320 --> 00:22:07,280 the snakes throughout here. And I think 612 00:22:05,600 --> 00:22:10,480 we'll be able to recover Banana because 613 00:22:07,280 --> 00:22:13,679 I think she's around. 614 00:22:10,480 --> 00:22:15,600 So, uh, I hope that's been an 615 00:22:13,679 --> 00:22:17,520 interesting set of, you know, concepts 616 00:22:15,600 --> 00:22:19,200 you're able to use in your classroom. 617 00:22:17,520 --> 00:22:20,559 But I think there's like a couple of 618 00:22:19,200 --> 00:22:22,320 exercises I want to just go through to 619 00:22:20,559 --> 00:22:24,559 be like, how can I extend this to what I 620 00:22:22,320 --> 00:22:26,559 need to do for my curriculum? So, 621 00:22:24,559 --> 00:22:28,799 firstly, let's talk about extending this 622 00:22:26,559 --> 00:22:30,320 basic example with more data because 623 00:22:28,799 --> 00:22:32,880 there's, you know, just infinite amounts 624 00:22:30,320 --> 00:22:34,159 of data that you too can make up. Uh for 625 00:22:32,880 --> 00:22:36,159 for instance, you might want to add more 626 00:22:34,159 --> 00:22:37,840 data files. There might be other ways to 627 00:22:36,159 --> 00:22:39,760 have an alibi, something like a 628 00:22:37,840 --> 00:22:42,080 lunchtime chess club or competition that 629 00:22:39,760 --> 00:22:44,799 people have to check in for. Um adding 630 00:22:42,080 --> 00:22:46,000 more fields to the data such as for the 631 00:22:44,799 --> 00:22:47,919 attendance list, you might have people 632 00:22:46,000 --> 00:22:49,679 who have come in late to school or 633 00:22:47,919 --> 00:22:51,840 people have gone home early. So those 634 00:22:49,679 --> 00:22:53,360 are more opportunities to introduce more 635 00:22:51,840 --> 00:22:55,039 uh conditions on like whether someone 636 00:22:53,360 --> 00:22:56,960 was at the school at the time of the 637 00:22:55,039 --> 00:22:58,960 crime specifically. 638 00:22:56,960 --> 00:23:00,480 Uh you could also introduce adding or 639 00:22:58,960 --> 00:23:02,480 calculating like fuzzy data. For 640 00:23:00,480 --> 00:23:04,000 instance, we had walking times and it 641 00:23:02,480 --> 00:23:06,080 kind of assumes that every student walks 642 00:23:04,000 --> 00:23:08,640 at the same time. You might have 643 00:23:06,080 --> 00:23:10,240 information from their PE subjects to be 644 00:23:08,640 --> 00:23:11,760 like, well, Charlie's a really fast 645 00:23:10,240 --> 00:23:14,159 walker. He could have got to the science 646 00:23:11,760 --> 00:23:15,760 lab and back in that time. Or maybe year 647 00:23:14,159 --> 00:23:18,240 seven's always work slower than year 648 00:23:15,760 --> 00:23:20,240 12's. Uh, you could do all of that. Um, 649 00:23:18,240 --> 00:23:22,240 or you could have a crime note and 650 00:23:20,240 --> 00:23:24,640 average number of misspellings of words 651 00:23:22,240 --> 00:23:26,320 per, you know, different grade levels. 652 00:23:24,640 --> 00:23:28,400 Um, you could obviously also include 653 00:23:26,320 --> 00:23:30,799 decoy data throw the kids right off the 654 00:23:28,400 --> 00:23:32,720 center of the trail. 655 00:23:30,799 --> 00:23:35,039 Uh there's of course the curriculums 656 00:23:32,720 --> 00:23:36,880 that we all have uh that we might need 657 00:23:35,039 --> 00:23:38,240 to introduce specific topics like if 658 00:23:36,880 --> 00:23:40,559 statements, loops, lists and 659 00:23:38,240 --> 00:23:42,400 dictionaries and more. Um which when 660 00:23:40,559 --> 00:23:44,960 working with Python, Excel because it is 661 00:23:42,400 --> 00:23:47,840 kind of that more mapping based 662 00:23:44,960 --> 00:23:50,000 filtering data sets functionality. It's 663 00:23:47,840 --> 00:23:51,520 just a little bit not the kind of same 664 00:23:50,000 --> 00:23:53,520 that you might have for projects that 665 00:23:51,520 --> 00:23:55,200 you're already used to using. But with a 666 00:23:53,520 --> 00:23:57,520 little creativity, we can introduce them 667 00:23:55,200 --> 00:23:59,679 with functions. For instance, maybe 668 00:23:57,520 --> 00:24:01,679 every student has a biography that's 669 00:23:59,679 --> 00:24:04,720 part of their student profile and it 670 00:24:01,679 --> 00:24:07,039 says things in like open form text that 671 00:24:04,720 --> 00:24:08,799 things like that Zoe, she loves being in 672 00:24:07,039 --> 00:24:10,799 the string ensemble and the chess club. 673 00:24:08,799 --> 00:24:14,159 We know chess club is on Tuesdays when 674 00:24:10,799 --> 00:24:16,240 this crime occurred. Uh so let us write 675 00:24:14,159 --> 00:24:18,799 a function to see if free form text 676 00:24:16,240 --> 00:24:21,760 includes chess club in it to see if we 677 00:24:18,799 --> 00:24:23,840 can create a new set of data called is 678 00:24:21,760 --> 00:24:25,279 in chess club and can check someone off 679 00:24:23,840 --> 00:24:27,760 the list and give them a new alibi 680 00:24:25,279 --> 00:24:29,279 potentially with that. Or we could have 681 00:24:27,760 --> 00:24:31,600 something more complicated if we want to 682 00:24:29,279 --> 00:24:33,520 use a loop. Maybe there's a note left at 683 00:24:31,600 --> 00:24:35,760 the scene of the crime. It seems to have 684 00:24:33,520 --> 00:24:37,760 a whole lot of long words for uh you 685 00:24:35,760 --> 00:24:39,760 know high school age kid. Let's maybe 686 00:24:37,760 --> 00:24:42,240 look through each student's bio to see 687 00:24:39,760 --> 00:24:44,640 if the proportion of words that are over 688 00:24:42,240 --> 00:24:46,080 a certain length matches the proportion 689 00:24:44,640 --> 00:24:48,720 of words in this letter that are just 690 00:24:46,080 --> 00:24:50,000 exceptionally long for a child. So, we 691 00:24:48,720 --> 00:24:53,200 could really come up with anything that 692 00:24:50,000 --> 00:24:54,960 we wanted there. Finally, maybe Python 693 00:24:53,200 --> 00:24:57,600 in Excel isn't for you. You might not be 694 00:24:54,960 --> 00:24:59,760 an M365 school, and that's okay. I 695 00:24:57,600 --> 00:25:02,159 accept you anyway because you too can do 696 00:24:59,760 --> 00:25:05,120 this in Jupyter Notebooks using VS Code, 697 00:25:02,159 --> 00:25:07,279 which is free. Uh and yeah is free code 698 00:25:05,120 --> 00:25:09,679 is also great because you do have much 699 00:25:07,279 --> 00:25:11,120 like the Python interpreter in Excel. 700 00:25:09,679 --> 00:25:12,799 You have these cells where you can just 701 00:25:11,120 --> 00:25:15,279 write your code. You can print stuff out 702 00:25:12,799 --> 00:25:17,760 after them as well as the ability to 703 00:25:15,279 --> 00:25:19,360 write uh markdown and instructions. So 704 00:25:17,760 --> 00:25:21,279 it might actually be a great way for you 705 00:25:19,360 --> 00:25:24,240 or your kids to write down the process 706 00:25:21,279 --> 00:25:25,679 of doing this. Uh so yeah uh the only 707 00:25:24,240 --> 00:25:28,000 thing you'll need to do with that is a 708 00:25:25,679 --> 00:25:30,720 little bit more data crunching to 709 00:25:28,000 --> 00:25:33,200 actually put the data into a form to you 710 00:25:30,720 --> 00:25:34,799 know get data out of CSVs rather than it 711 00:25:33,200 --> 00:25:37,840 just being in Excel and highlighting 712 00:25:34,799 --> 00:25:39,679 that table. So yeah that is all of my 713 00:25:37,840 --> 00:25:42,480 talk today. I hope you have had fun 714 00:25:39,679 --> 00:25:43,600 looking for banana with me. Um and maybe 715 00:25:42,480 --> 00:25:45,840 this will be something that you can use 716 00:25:43,600 --> 00:25:47,679 in your classroom. Uh feel free to check 717 00:25:45,840 --> 00:25:50,000 out the link. The Jupyter notebook's not 718 00:25:47,679 --> 00:25:52,080 up there yet but the Excel file with the 719 00:25:50,000 --> 00:25:56,120 data examples are in there. Uh, and the 720 00:25:52,080 --> 00:25:56,120 slides are also in there as well.