[Music]
Hello everyone. Welcome back to the
education track. So next up we have
Renee and this is guess what? Renee.
Renee is one of those duo people. And
what exactly does she do? Uh kind of
everything. So uh she is a cloud
developer advocate on the Python
advocacy at Microsoft. She is CEO and
co-founder of tech inclusion best known
for the girls programming network um
which runs workshop for girls all around
Australia. On top of this uh Renee
started her own business connected code
um bringing tech education uh
opportunities to school. Uh, and
recently Renee was awarded Champion of
Change 2025
uh by the women leading uh tech. And fun
fact, she also has two cats, Artemis and
BB. Um, so let's welcome Renee. It's not
not all work. There's cats in there as
well. Thank you, Ally. Uh, hello
everybody. It's super excited to be here
today to present my talk, who do it.
XLSX. Um, to how we can teach Python
through data investigation.
Uh, as we just heard, I do a lot of
things. Basically, they all come down to
tech, education, community squished into
some form together. And I really love
context driven learning experiences,
which we've already heard a lot about
today. So, hopefully this is a nice
extension of that in a slightly new
landscape.
Uh, so uh the today I'm going to talk
about the world of Python, data, and AI
in 2025 and the education landscape. Uh,
what is Python in Excel? uh framing your
own crime for educational purposes only.
Uh then we're going to solve the crime
and then I'm going to tell you how you
can apply this to your own curriculum
hopefully.
Okay. So here we are in 2025. What are
we doing in the technology landscape?
We have a couple of questions really
when we're kind of trying to teach kids
technology whether they're new, whether
they're super engaged, wherever they are
on the spectrum of knowing stuff and
liking stuff. How can we teach Python in
a fun and an engaging way? Is there a
way to make it feel more comfortable,
familiar, a place that we could do that?
And is there an alternative approach
that means that maybe they can't just go
to chatt and get it to solve the
problems for them because we do want
them to learn a thing. Uh why do we want
them to learn a thing when AI can do it
all for them theoretically? Well, from
my own experience, AI can be a really
useful tool. I know as a you know person
who works at Microsoft I'm using copilot
a lot and it is a really helpful tool
but that is because I actually have been
working as a developer for 10 plus years
and I do know what I want it to
ultimately do so it works best when I
can give it specifications about the
stack that I want it to use uh the
requirements that I needed to have and
the context that I know that I also know
that it doesn't yet know so that's
really useful u I also know the
limitations and the options of things
that are available so I know to ask for
things that aren't realistic or, you
know, are going to go a little bit ary.
And I also know that sometimes,
especially when the context is large and
I've asked it a lot of questions, it
kind of get can get a little bit off
track um because it doesn't really know
how all these pieces of context actually
fit together uh necessarily. So when
that goes wrong, either I can work out
what's gone wrong and can prompt it in a
specific way or I can prompt it or I can
just go around the AI and do it myself
and fix out the problems that maybe are
too complex for it to actually deal with
within the scope of my project. So to be
able to do that, I did need that 10
years of experience as a developer. So
we do need kids to actually keep
learning to code even as the AI tools
get better and better. We need them to
have that understanding and you get that
understanding by doing.
So I have been working with a lot of
data recently and I've experienced some
of these problems firsthand. I've got to
say the AI was great when I was like yes
I need this simple calculation done
really fast. But the more data I added
in and the more complicated and the more
connections the data had, the more it
kind of got a little bit iffy in terms
of like how is it all going to fit
together and the code kind of didn't
work anymore. like if I had a better
mental model of all this that I created
that would be great because I'd be able
to quickly fix it but right now I have a
lot of AI generated code um and not and
it doesn't have the full picture of
things uh that it needs to have um thank
you to my lovely husband Jack who is
getting me out of this meeting that I'm
not going to on my phone.
Okay. So, uh, so that thing is that AI
isn't great when there's a humongous
picture which happens when you have a
lot of data that you want to work with.
So, how can we get kids to work with a
lot of data so they too can experience
the joy of working with their own data
and finding out the hidden details in
it?
So, this is where Python and Excel comes
in. So, but first I'm going to tell you
a little bit about Python and Excel
before I tell you about this mystery
that I have craved for us. Okay. So what
is Python Excel? Well, you know already
know how you can write formulas in
Excel. Now you can write Python in Excel
in cells. So you can just do equals PI
and hit tab. And now you have these fun
little green pi boxes and you can write
Python in there. And you don't just have
to write a single line of Python. You
can actually write many lines of Python
which is really cool. The cells actually
work together as well. So we can see a
equals 5. I can now use a in the next
cell down. And you can see that a plus 2
is seven. So, you can do this for
multiple many cells, which is very
exciting. If you're like, "Oh, but I
want to write a lot of code. It's going
to be hard to see it in those tiny
cells." Don't worry. There's this Python
editor where you can see all of the
code, uh, which is really handy. And you
can also see the stuff that you want to
print out because, as you can see here,
you might go print hello. And the thing
is, print hello prints a thing, but it
doesn't return anything. It returns
none. So, you're going to have that
printed out, which is not what you want
to see. But what you do want to see as a
thing that you printed out here in your
interpreter here. So that's really
helpful.
Uh other things you might want to know
about Python Excel is you can insert it
with this uh once you go to the data
ribbon, you can insert a Python cell. If
you don't want to write the three
characters of equals pi, you can click a
button instead. Don't worry if your
interpreter ever gets stuck. You can
reset your environment and it will just
run all your code again and connect up
properly again. Um, you've got the
editor to pop that out and you've got
this initialization button which will
show you all of the stuff it imports
before you actually start coding. So
these are all the things that you don't
need the children to see but will
already be built in when you load Python
in Excel. Most importantly for this
demonstration is pandas or pandas as pd
as it will be you know shortened to in
the examples.
So how do I get some data into my python
in Excel? It's super easy. I've made a
little pi but uh pi cell. I just go and
highlight some data somewhere. Really
easy. And then it does all of this uh
information here for me, which is great.
Uh so that's I didn't have to type any
of that. It figures that out. It figures
out the headings. Hooray. And now I can
just I'm going to assign it to a
variable uh student attendance, which is
handy. And now I have this data frame.
Once again, it doesn't print out the
whole data frame, but you can see it if
you need to here by doing show data type
card. And you can see all of the
wonderful data that you've loaded in,
which might be handy to be like, what
was that data again? Oh, there it is.
So, we have some data. We can also print
out all of that data in the uh
interpreter as well.
Okay. So, why would I want to use Python
in Excel? Well, uh it's a familiar
place. Lots of people have used
spreadsheets before and if someone's
like new to coding like oh I don't know
how to do coding at all potentially
other teachers who are like oh no I
couldn't do coding I'm not a techie like
that was like well do you know how to
use a spreadsheet they're like yeah of
course do you write the fun functions
and that oh yeah I've done a function
from time to time it's a friendly
landscape who wouldn't want to do
something in Excel it's a wonderful tool
what I also think might be useful is it
has a clear link to variables variables
are a topic that is commonly hard for
students to grasp at first. But if
you've been using spreadsheets for a
while, even if you've only done some
basic formulas like cell A1 plus cell B2
equals something in another cell, you've
used variables. You just they don't have
very fun names. They're called cell A1.
That's a variable name. And people know
that if I put different data into that
cell, the formula answer is going to
change. So I'm hoping that this will be
a way for students to more easily grasp
some of those concepts. Um, finally,
Python Excel means, you know, you saw
how easy it was to grab that data. I
highlighted a thing. It worked all the
stuff out for me. It means I get to skip
the stuff like reading a CSV and doing
like with open blah blah blah, looping
through a thing, filtering that data
out. It's actually super fast and easy.
And so, you can skip that part and get
to the fun part of solving a mystery.
So, um,
the interesting thing is though, when I
first came to Python in Excel, I'm like,
cool, I've done a lot of spreadsheeting.
That's really cool. I've also written a
lot of Python code. Bringing these two
together is going to be super fun and
I'm going to definitely intuitively
understand how I can use this new tool
in this new landscape to do something
fun. But at first it was just like,
okay, I'm going to do this. I'm like,
no, I can just do that in normal Excel.
And then I started doing another thing.
It's like, well, I could do this with
Python, but like doesn't really need
Excel. It doesn't really make any sense.
So it was this new kind of paradigm that
I had to kind of get my mind around of
like how to join these up and use the
skills that I have in both in a
different way uh to solve different
kinds of problems.
Uh so I found the kind of two things
that I end up doing most in Python XL
isn't like your normal Python like I
wrote a script has a lot of loops and if
statements and things in it and it isn't
your kind of normal like there's like
filtering and you've got sorting and
things that you can already do in Python
sorry in Excel and you're like well why
would I need to do something else? So I
found that firstly being able to index
an whole series of data is super useful
and indexing it by name and then
secondly being able to map a function or
some sort of process to that data like a
column or whatnot of your data to make
new data to perform some tasks is where
the wonder of Python XL lies at least
for me so far on my Python and Excel
journey. Um, and all this functionality
is easily built in with pandas as we
saw, which is already loaded into Python
and Excel.
So, I'm got this example for you, but
I'm just going to say this is a small
example because it fits better on the
slides. Where the magic really is going
to happen is when you have a giant
example. And I'm about to give an
example of like some school students.
And my school only has 20 students,
which is not that fun because a kid
could be like, I could just do this by
hand. So, I would recommend that your
school has 800 students or something
like that, which would be much more fun
and much more annoying for them to try
and just figure it out with a pencil and
paper.
Okay, so we're going to solve this
mystery. Welcome to the case of the
missing snake. It happened at 12:00. The
location, the science lab. The vict the
victim, Banana, the banana python. The
notes. After leaving her classroom at 12
p.m., Ms. Monty returned 5 minutes later
to feed Banana only to discover that she
was missing.
So we have the crime, we have the time.
But who could have done this crime? We
have some information on hand. The
information is we've got the student
attendance for the day. We have the
timets of each student. We have the time
it takes to get between different
classes. We have canteen purchasers from
the day. And we also have the side fact
that all of year 11 and 12 are off
campus today for an excursion. So,
different pieces of information for this
crime that's occurred after period 3 at
12:00 uh on this day. So, here's our
attendance records. We've got our room
walking times in this uh symmetrical
data set here. We've got our student
timets.
We've got canteen purchases. And then
also we have this handy dandy detective
data toolkit sheet that you can give
your kids uh to hopefully help them with
some of the ideas that they can use to
put this together. So let's hopefully be
able to find out who has released banana
and hopefully we will be able to
retrieve banana.
Okay. So to give this to students I
would be providing something along the
lines of this. Um, I've got all of these
uh different uh pieces of data already
loaded up in here in the different
sheets of the table. And then I've given
a data analysis template, but you could
just go be like, "No, figure it out
yourself, children." Or you can give,
you know, less more details. You could
just give these big headings, whatever
you'd like. That's up to you.
But here are the steps that I am going
to take to solve this mystery here with
you today. Firstly, I want to find all
the locations in the school that are
within a five minute walk of the science
lab. So, we know this happened in the
first five minutes of lunch after period
3. Let's find out where you could get
come, where you could have come from.
Then, we're going to find out which
students are timetabled to be in that
location on this, you know, for this
period. Then, which students are
actually at school today? Some might be
sick, some are on that school excursion.
uh we can join those together to work
out who was here and was supposed to be
nearby. And finally, we can work out if
anybody has another alibi.
So, here we go. We've got our school uh
walk time table here. And what we really
want to do is just grab this science row
here because it's got all the distances
or the all the walking time distances to
the science room. So, to be able to work
with this data, we need to be able to
load that walk time data. we're going to
set. So because we are working with uh
something like we could have worked with
either column here but for this example
I'm going to work with the row instead
of the column even though they are
symmetrical. Um but it's just a way you
can teach both columns and rows because
we'll be working with rows later. Uh
then we can work out which of these ones
in this row are within a five minute
walking distance.
Okay. So firstly we saw how to load data
earlier. Super easy. Uh now we are going
to set the index to that location
column. So it's saying, hey, this is the
label of the column that I want you to
use. So when I want to look something
up, get the, you know, it's going to be
mapped from this key to the data
associated with the rest of that row.
Then we are able to search for the
science row because we've got that, you
know, science label that we can look up.
We can see all of the different places,
but we don't want all of those. Some of
these are too far too far away to make
it to the science room in time. So here
we go. Uh we can now search for I guess
I've said actually we're going for 3
minutes or less here. I guess you need
to get in and get out with your snake
actually uh to load uh to yeah get that
places that are close to science uh in
this time. So we've now got this list
here which I've gone index set to list
and we're going to keep this in our
pocket for later uh because we'll be
needing to check some students out to
rule them out as suspicious or not
suspicious.
Okay, so we know where the people could
have come from. Well, who was in those
potential locations? So, we've got our
list of English, science, history, and
geography over here. Let's apply it to
everyone in period 3 here. So, because
that's the period that happens before
lunch in my fictional school where you
have lunch at 12:00, it's all fine.
Uh, so we need to load that data again.
Then we're going to be checking which uh
which classes were had in period 3 and
only keeping the ones that are in the
list of useful data. So here we are
working with uh columns now instead of
rows and we can either we could print
out all of the period 3 classes just to
be like oh yeah that's good. Or we could
print out all the kids who had period 3
um and who had a student ID. You'll also
know they're not student names. We need
to do some data matching up later. Oh
no.
So here we go. Uh we have got our close
to science uh children who are uh here
we've got the index as we said we had
that list already sorry and then we want
to create I'm going to call it a filter
almost where we are creating a series of
booleans which is saying hey it's the
period 3 class in the list of those ones
we mentioned before and it's just going
okay science yep true false false true
true false and we're just going to have
all of the classes we're going to have
that true false true true false whatever
serial list series list and we can apply
that to the data to filter out
everything that is false which we can do
here. Uh so I've done it this way just
to make it read a little bit simpler. Uh
so you can have your timetables and we
can have that close to science check or
bull or whatever you want to call that
series you've got there. And it's just
going to get rid of everything that was
false. Uh which is handy because now
they're out because they are cleared of
this snake related crime. And we have
just the student IDs of the students who
could have done it.
Okay, so those are the places people
could have come from, but you have to be
at school today to actually come from
that spot. So let's check the attendance
ro to see who was here. We're going to
be looking at the status if it was set
to present. And we saw just a second
ago, we could easily uh you know search
through a column. This time we're going
to set up a conditional. Um rather than
doing the is in, we can just use much
like your if statements, you can say
attention status is equal to present. Um
and then we're just going to get one of
those lists again of all the booleans um
that we can use to filter out all the
kids that were not present today. So
we're going to get rid of all those kids
who are absent and we've now limited it
down to I guess we've got all of the
kids here now and they've got the is
present. So most of the kids are present
today, but we also need know something.
No one in year 11 and 12 is on campus
today. They're offsite. So, we can also
add this secondary filter here which is
called only juniors because we want to
get everyone who is in year 11 or 12.
They're out of here. So, we're going to
filter once again creating this onampus
uh boolean checklist and filter out to
get only the junior students who have
checked in today got that tick as
attending.
Great. Okay. So now we know who was on
campus today, who's been marked as
present, and is in in year 11 and 12.
And we also have that list of the
classrooms and the students that were in
them that could have made it to the
science lab in time to commit this
crime. So it's time to join that data
because, as you can see, we've got names
over here in our attendance role, but we
don't have any names here. It's going to
be hard to start making accusations if
I'm like you S0108,
I think you did it. So
we can use something that is called
pandis merge here. It's super easy to
merge two pieces of data if they have a
matching um matching field like the
student ID. So super easily we just map
those together. Um and now we can see
we've got our joint data which is this
yellow one here that was in both. We've
got the blue data that came from the on
campus from the RO and then we've got
the green one that is coming from the
timets for the kids.
Uh so that's all there together and we
do only have six remaining suspects
which is very convenient.
Okay so we are nearly at the end of our
journey but we do you know have a little
more data we can use to work out we have
who was buying lunch at the canteen at
the time of the crime. Uh so we can see
that we have this time data here for the
time the purchases were made. Um, so
maybe we can investigate that to sort
out this a little bit more.
We do we are working with times though,
but I don't know about you if I I've
never tried to teach datetime objects to
a child and I want to keep it that way.
Um, I wanted to keep it that way for you
as well and give them an opportunity to
create their own small functions that
enable them to interact with the
strings. Uh, so that's what we're going
to be doing. So I created two functions.
one that is get hour and one that is get
minute. Um much easier than dealing with
a datetime object that we really don't
need. So I've created these two
functions and this is actually a really
great place for you to int introduce
other concepts from your curriculum that
you want to be including and I'll talk
about that a little bit more later.
So I've run my two functions here um
applying it to that time uh field and
I've actually been able to create two
new columns of data by setting just like
you could set something in a dictionary
essentially but I can set it to a whole
new row of data. So these ones all
happened at 12 and here we have the
minute column. So that's how you can
extend on the data and get students to
build out the information that they
have.
Um cool.
Now, uh, once we have that, we are able
to then also want to see what names
these are of the students because we're
once again back to student IDs. We've
tapped in. We're buying things at the
canteen with our student ID cards, which
I think is a thing that actually happens
in some places in this day and age. Um,
but we're back to not having names. And
also, some of these kids might have not
have come from a place that was, you
know, potentially suspicious. So, let's
have a look. Uh we've got our alibi list
and we want to say if you're not that's
what this little title here is for. If
you're not in the alibi list, we're
going to keep you as a suspect. Uh so we
can run that and see if we have maybe
solved this crime. We have. There is one
suspect remaining. It's Mia Kim. And I'm
going to leave it as an exercise to the
reader as why the delightful Mia Kim has
stolen this snake uh today. But
hopefully you've been able to spot all
the snakes throughout here. And I think
we'll be able to recover Banana because
I think she's around.
So, uh, I hope that's been an
interesting set of, you know, concepts
you're able to use in your classroom.
But I think there's like a couple of
exercises I want to just go through to
be like, how can I extend this to what I
need to do for my curriculum? So,
firstly, let's talk about extending this
basic example with more data because
there's, you know, just infinite amounts
of data that you too can make up. Uh for
for instance, you might want to add more
data files. There might be other ways to
have an alibi, something like a
lunchtime chess club or competition that
people have to check in for. Um adding
more fields to the data such as for the
attendance list, you might have people
who have come in late to school or
people have gone home early. So those
are more opportunities to introduce more
uh conditions on like whether someone
was at the school at the time of the
crime specifically.
Uh you could also introduce adding or
calculating like fuzzy data. For
instance, we had walking times and it
kind of assumes that every student walks
at the same time. You might have
information from their PE subjects to be
like, well, Charlie's a really fast
walker. He could have got to the science
lab and back in that time. Or maybe year
seven's always work slower than year
12's. Uh, you could do all of that. Um,
or you could have a crime note and
average number of misspellings of words
per, you know, different grade levels.
Um, you could obviously also include
decoy data throw the kids right off the
center of the trail.
Uh there's of course the curriculums
that we all have uh that we might need
to introduce specific topics like if
statements, loops, lists and
dictionaries and more. Um which when
working with Python, Excel because it is
kind of that more mapping based
filtering data sets functionality. It's
just a little bit not the kind of same
that you might have for projects that
you're already used to using. But with a
little creativity, we can introduce them
with functions. For instance, maybe
every student has a biography that's
part of their student profile and it
says things in like open form text that
things like that Zoe, she loves being in
the string ensemble and the chess club.
We know chess club is on Tuesdays when
this crime occurred. Uh so let us write
a function to see if free form text
includes chess club in it to see if we
can create a new set of data called is
in chess club and can check someone off
the list and give them a new alibi
potentially with that. Or we could have
something more complicated if we want to
use a loop. Maybe there's a note left at
the scene of the crime. It seems to have
a whole lot of long words for uh you
know high school age kid. Let's maybe
look through each student's bio to see
if the proportion of words that are over
a certain length matches the proportion
of words in this letter that are just
exceptionally long for a child. So, we
could really come up with anything that
we wanted there. Finally, maybe Python
in Excel isn't for you. You might not be
an M365 school, and that's okay. I
accept you anyway because you too can do
this in Jupyter Notebooks using VS Code,
which is free. Uh and yeah is free code
is also great because you do have much
like the Python interpreter in Excel.
You have these cells where you can just
write your code. You can print stuff out
after them as well as the ability to
write uh markdown and instructions. So
it might actually be a great way for you
or your kids to write down the process
of doing this. Uh so yeah uh the only
thing you'll need to do with that is a
little bit more data crunching to
actually put the data into a form to you
know get data out of CSVs rather than it
just being in Excel and highlighting
that table. So yeah that is all of my
talk today. I hope you have had fun
looking for banana with me. Um and maybe
this will be something that you can use
in your classroom. Uh feel free to check
out the link. The Jupyter notebook's not
up there yet but the Excel file with the
data examples are in there. Uh, and the
slides are also in there as well.