#446: Python in Excel

WEBVTT
00:00:00.001 --> 00:00:04.960
Why is Python so popular? There's plenty of room for debate on this, but one solid reason is it's
00:00:04.960 --> 00:00:10.440
easy to adopt, easy to use, and caters to people who are not quite developers, data scientists, but
00:00:10.440 --> 00:00:16.260
need to get some computing done. Do you know where the largest untap set of those people hang out?
00:00:16.260 --> 00:00:22.700
Excel. That's why it's super exciting that Python is now going to be built directly into Excel.
00:00:22.700 --> 00:00:29.180
Just go into a cell and type equals pi, and you're off writing full Python 3 code that is backed by a
00:00:29.180 --> 00:00:35.840
lightweight anaconda distribution of Python. We have Dr. Sarah Kaiser here to give us the rundown on
00:00:35.840 --> 00:00:42.180
Python and Excel. This is Talk Python To Me, episode 445, recorded live and on location at
00:00:42.180 --> 00:00:45.760
Microsoft Ignite in Seattle, November 16th, 2023.
00:00:58.360 --> 00:01:05.480
Welcome to Talk Python To Me, a weekly podcast on Python. This is your host, Michael Kennedy.
00:01:05.480 --> 00:01:10.580
Follow me on Mastodon, where I'm @mkennedy, and follow the podcast using @talkpython,
00:01:10.580 --> 00:01:16.960
both on fosstodon.org. Keep up with the show and listen to over seven years of past episodes at
00:01:16.960 --> 00:01:23.080
Talk Python.fm. We've started streaming most of our episodes live on YouTube. Subscribe to our YouTube
00:01:23.080 --> 00:01:29.000
channel over at talkpython.fm/youtube to get notified about upcoming shows and be part of that
00:01:29.000 --> 00:01:35.340
episode. This episode is sponsored by Posit Connect from the makers of Shiny. Publish, share, and deploy
00:01:35.340 --> 00:01:41.560
all of your data projects that you're creating using Python. Streamlit, Dash, Shiny, Bokeh, FastAPI,
00:01:41.560 --> 00:01:48.820
Flask, Quarto, Reports, Dashboards, and APIs. Posit Connect supports all of them. Try Posit Connect for free
00:01:48.820 --> 00:01:56.160
by going to talkpython.fm/Posit, P-O-S-I-T. And it's brought to you by the PyBytes Developer
00:01:56.160 --> 00:02:01.340
Mindset Program. PyBytes' core mission is to help you break the vicious cycle of tutorial paralysis
00:02:01.340 --> 00:02:06.460
through developing real-world applications. The PyBites Developer Mindset Program will help you build
00:02:06.460 --> 00:02:11.800
the confidence you need to become a highly effective developer. The link is in your podcast player show
00:02:11.800 --> 00:02:19.340
notes. Sarah, welcome to Talk Python To Me. Hey, how's it going? Hey, it's going super well, super well here
00:02:19.340 --> 00:02:25.620
at Ignite for one more time to talk about cool stuff with Python and things going on. This time it's going
00:02:25.620 --> 00:02:32.600
to be Excel and Python. It's like Reese's Pieces. You got your chocolate and my peanut butter. Like,
00:02:32.600 --> 00:02:37.380
what's going on here? I mean, the cats and dogs, are they living together? What's happening?
00:02:37.680 --> 00:02:42.220
The old school and the new school. That's right. The open source and the not?
00:02:42.220 --> 00:02:47.860
Yeah, yeah. My background is I've been a data scientist for a long time. I did my PhD in quantum
00:02:47.860 --> 00:02:52.940
computing, in like experimental physics stuff. So trying, I actually, my first programming language
00:02:52.940 --> 00:02:57.680
was Mathematica. And that was actually what got me hooked on kind of the notebook sort of interface.
00:02:57.680 --> 00:03:02.320
Yeah, okay. Well, normally people say their first programming language when that's the background is
00:03:02.320 --> 00:03:10.640
math lab. Yeah. I would say Mathematica is like the proper mathematicians programming space because it
00:03:10.640 --> 00:03:16.920
does symbolic math, right? Which is so remarkable when you see it go. Yeah. Like that was basically
00:03:16.920 --> 00:03:23.000
like I could visualize. The thing was being able to do easy visualizations. So actually after undergrad,
00:03:23.000 --> 00:03:26.220
I worked at Mathematica for a while on their visualizations team.
00:03:26.220 --> 00:03:28.300
Yeah. What technologies was that in?
00:03:28.300 --> 00:03:32.280
I was actually just writing Mathematica in Mathematica. Thankfully I didn't have to touch
00:03:32.280 --> 00:03:38.680
any of the optimization stuff underneath, but designing new like gauges and plotting built-ins
00:03:38.680 --> 00:03:44.540
and stuff like that. I really enjoy visualizing and understanding data that way. And so when I got
00:03:44.540 --> 00:03:51.280
to grad school, realized that none of our instruments talked together. Like everything was an entirely
00:03:51.280 --> 00:03:58.400
separate archaic non-documented protocol and we were having to somehow like either sit by hand and match
00:03:58.400 --> 00:04:03.280
up all of these CSV dumps from the different tools and stuff like that. So basically I started learning
00:04:03.280 --> 00:04:10.800
Python to make our lab work. So initially it was like using a serial and C types libraries to actually
00:04:10.800 --> 00:04:16.080
interface with these APIs. But I realized someone once showed me a Jupyter notebooks and I was like,
00:04:16.080 --> 00:04:18.800
Okay. This is how we're, this is the way.
00:04:18.800 --> 00:04:20.880
This is Mathematica for Python.
00:04:20.880 --> 00:04:28.560
Exactly. So I have been using Jupyter notebooks almost since they came out and really enjoyed that
00:04:28.560 --> 00:04:34.240
both as a learning tool, a teaching tool, and a kind of reproducible science tool.
00:04:34.240 --> 00:04:38.560
Yeah. It's just taken the world by storm. Really. They were here at Microsoft Ignite,
00:04:38.560 --> 00:04:44.320
the keynote, they went, they had like basically one code oriented demo in the two and a half hour
00:04:44.320 --> 00:04:49.760
our keynote. And it was now we have a Jupyter notebook with some Python code that's going to
00:04:49.760 --> 00:04:53.440
do some machine learning and you know, back to cats and dogs.
00:04:53.440 --> 00:04:54.800
Yeah. Yeah. Yeah. Yeah.
00:04:54.800 --> 00:04:57.920
That's incredible. So yeah, very cool. These days, what are you doing?
00:04:57.920 --> 00:05:05.200
I am a developer advocate at Microsoft on the Python team focusing on data science and machine learning
00:05:05.200 --> 00:05:11.200
sorts of Python tools. So I get to, I get to do all the fun stuff with pandas, numpy,
00:05:11.200 --> 00:05:18.080
scipy, all of those things. And that's kind of why when I heard about the Python and Excel preview,
00:05:18.080 --> 00:05:26.320
I was like, Oh, I have done many pandas import from Excel sort of things and was kind of curious
00:05:26.320 --> 00:05:32.560
to see. Cause initially I was like, Hmm, I'm pretty cool with my Python. I'm not sure why I want to go
00:05:32.560 --> 00:05:36.160
that way, but suffice it to say, I think I I'm convinced now.
00:05:36.160 --> 00:05:37.440
Are you? Okay. Yeah.
00:05:37.440 --> 00:05:42.720
Yeah. Because it's kind of a different way than a Jupyter notebook. It's for pluses and minus. We'll
00:05:42.720 --> 00:05:49.520
talk about some fun ones of those as we go. Who are you evangelizing Python on the behalf of Microsoft
00:05:49.520 --> 00:05:54.400
too? So are you talking to like companies that do .NET saying, Hey, you should do Python. Are you talking to
00:05:54.960 --> 00:06:01.120
companies that love Python and saying like, Hey, you should consider Azure or our, our offering? What
00:06:01.120 --> 00:06:02.800
does your world look like? What do you do today?
00:06:02.800 --> 00:06:08.480
Yeah. A lot of it is doing testing. I work a lot with the VS Code team and the Jupyter specifically,
00:06:08.480 --> 00:06:13.760
like the Jupyter features in VS Code to make sure all the notebooks work to make sure, you know,
00:06:13.760 --> 00:06:19.840
packages with kind of weird output visualizations, you know, are they rendering properly? Generally,
00:06:19.840 --> 00:06:26.480
I think I work with a lot of the actual Python open source maintainer communities in the kind of scientific
00:06:26.480 --> 00:06:32.000
Python space. So like with the num focus supported projects and really just try to kind of understand
00:06:32.000 --> 00:06:39.040
what are they running into? And as we're definitely seeing both with all of the LLM and machine learning
00:06:39.040 --> 00:06:46.560
stuff, like data is getting so big that I definitely can't open it in one Excel sheet. And so the data is,
00:06:46.560 --> 00:06:52.240
you know, basically, we're to the it's all native on the cloud. And so there's, I think, a lot of
00:06:52.240 --> 00:06:57.680
kind of the academic space that I'm, I come from of like, that isn't really part of the curriculum or
00:06:57.680 --> 00:07:04.160
what's taught or, you know, people kind of have to learn it ad hoc. And so basically, I kind of like
00:07:04.160 --> 00:07:10.000
to think of myself as someone who gets to go do all that learning about kind of how does science and
00:07:10.000 --> 00:07:15.760
data interact with the cloud, and in this case, specifically Azure, and just try and help build
00:07:15.760 --> 00:07:21.360
out scenarios, like build sample apps, things like that, that can make it so they don't have to waste
00:07:21.360 --> 00:07:26.320
half their PhD time trying to understand cloud DevOps stuff.
00:07:26.320 --> 00:07:31.280
Yeah, it sounds a little bit like, you know, a lot of people who are just brought into a lab and said,
00:07:31.280 --> 00:07:36.880
here, you're going to learn Python, and then you can go do your, your actual research, they often sort of
00:07:36.880 --> 00:07:43.840
skimp out on like, some of the very basic software practices, like what is Git? What is DevOps? How do I run
00:07:43.840 --> 00:07:49.280
this? Like, yeah, those kinds of things, right? So maybe you've run a lot of workshops, like, have you?
00:07:49.280 --> 00:07:56.720
Basically, in my postdocs, I got really tired of my teams and groups that I was working with not
00:07:56.720 --> 00:08:01.760
knowing. So I basically made a standard two day workshop that was like, all of the computer science
00:08:01.760 --> 00:08:08.320
skills you missed in your physics degree. We did have at one point data that just existed only on
00:08:08.320 --> 00:08:13.040
a jump drive, and that jump drive walked away. And that does not feel super good from a research
00:08:13.040 --> 00:08:14.320
standpoint. I can imagine.
00:08:14.320 --> 00:08:20.240
Yeah, let's just say we, we had a review of that and instituted new policies.
00:08:20.240 --> 00:08:22.880
We decided backups are good.
00:08:22.880 --> 00:08:23.600
Yep.
00:08:23.600 --> 00:08:30.640
Yeah, it's a bit of a divergent, but I worked on some math research before I graduated and got my
00:08:30.640 --> 00:08:37.120
bachelor's. And it wasn't, it was just in C++, but we had these large silicon graphics computers,
00:08:37.120 --> 00:08:41.840
like big mainframe thing. We had, you know, Onyx workstations that went into it. We came in one morning
00:08:41.840 --> 00:08:47.680
and nobody could log in. The computers wouldn't respond or turn on us. It's like a quarter million
00:08:47.680 --> 00:08:53.200
dollar computer that got used and it won't turn on. We're like, oh boy. What had happened is one of
00:08:53.200 --> 00:09:00.320
the grad students, non developer types, just doing their research, had a problem with their program.
00:09:00.320 --> 00:09:05.200
So they started logging to see what was going on and they left it running overnight. It filled the disk
00:09:05.200 --> 00:09:11.680
to the very last bite. And apparently once the last bite was gone, the whole mainframe couldn't,
00:09:11.680 --> 00:09:16.800
operate and just nobody could even figure out what was wrong with it forever. And eventually,
00:09:16.800 --> 00:09:18.640
yeah. So, you know, practices.
00:09:18.640 --> 00:09:27.120
Yeah. We also had a $5 million microscope get bricked because a younger grad student didn't
00:09:27.120 --> 00:09:31.120
understand that Windows XP service pack zero should never be plugged into the internet.
00:09:31.120 --> 00:09:35.120
Oh, so no firewall, none of that.
00:09:35.120 --> 00:09:39.920
Oh, no, no, no, no. They were just like, well, it has an ethernet port and the company said they can,
00:09:39.920 --> 00:09:42.080
you know, do support over TeamViewer or something like that.
00:09:42.080 --> 00:09:43.200
Yeah. So they were like, yeah.
00:09:43.200 --> 00:09:44.880
A TeamViewer. That's never going to go wrong.
00:09:44.880 --> 00:09:50.320
Yeah. So we walked in and we had to yank it out of the wall and we had the police there the next day
00:09:50.320 --> 00:09:55.200
because it had already been doing illegal financial transactions in about 30 seconds.
00:09:55.200 --> 00:09:58.720
Wow. You mean a microscope. You're here to arrest a microscope.
00:09:58.720 --> 00:10:00.160
Yeah. I was like, it's over there.
00:10:00.160 --> 00:10:05.200
They're like, well, that's not a computer. It already is. All right. Another interesting thing
00:10:05.200 --> 00:10:11.520
before we dive into our main topic here is I'm not sure if this is real or not. Did it happen or did
00:10:11.520 --> 00:10:18.400
it not? This quantum work that you're doing? Yeah. So my PhD was on quantum cryptography
00:10:18.400 --> 00:10:25.440
hardware. So both there exist commercial devices. So part of it was finding side channels and the
00:10:25.440 --> 00:10:30.480
physical devices that you could buy off the shelf. And then the other part was designing satellite
00:10:30.480 --> 00:10:37.520
optics for doing ground to satellite based key exchange. So, yeah, that was a lot of what I was
00:10:37.520 --> 00:10:41.600
doing in the like prototyping things in the lab that would look like a satellite and we'd,
00:10:41.600 --> 00:10:46.320
you know, satellite on one table, sender on the other. And we slowly worked out to,
00:10:46.320 --> 00:10:50.160
we did a bunch of tests in an airplane because turns out like putting things on a satellite is
00:10:50.160 --> 00:10:58.320
kind of expensive. And we just flew really high in a 747. Yeah. And weather balloons turn out to be
00:10:58.320 --> 00:11:04.000
not super predictable in the path once you launch them. And so if you're trying to like aim telescopes,
00:11:04.000 --> 00:11:07.600
yeah, it kind of ended up being messy, but it was pretty cool. We got to go,
00:11:07.600 --> 00:11:13.680
I got to help with the design process for these single photon kind of collection systems that went
00:11:13.680 --> 00:11:19.520
from literally, I'm just putting a couple lenses on the table and running Python stepper motor things in
00:11:19.520 --> 00:11:24.560
my Jupyter notebook to something that was actually automatically tracking and working from the ground
00:11:24.560 --> 00:11:28.960
to an airplane, hopefully eventually a satellite, but that's kind of out of my hands at this point.
00:11:28.960 --> 00:11:34.160
Yeah. Space takes a long time to get to a lot of planning and a lot of, we worked on it for
00:11:34.160 --> 00:11:39.200
eight years and then we had our launch window missed by a week, you know, or something like that. Right?
00:11:39.200 --> 00:11:41.920
Yeah, that's rough. And as a grad student, you got to get out of there.
00:11:41.920 --> 00:11:44.960
Yeah. I got to get a degree. I need to eat.
00:11:44.960 --> 00:11:45.920
Yeah.
00:11:45.920 --> 00:11:50.320
So let's get, let's get going. Cool. Well, that sounds like a really fun project. You must have
00:11:50.320 --> 00:11:55.120
learned a lot. And whenever you're talking to hardware, I think that's a special kind of programming.
00:11:55.360 --> 00:11:59.520
It's not just, I run the program and then it just goes, but like there's stuff it touches.
00:11:59.520 --> 00:12:05.600
Yeah. Like you don't get to necessarily do your own architectural design. You have to kind of
00:12:05.600 --> 00:12:11.680
first observe and usually reverse engineer what the original device engineers came up with and then
00:12:11.680 --> 00:12:18.800
figure out how is this, you know, this device is streaming base. This device, how am I going to like,
00:12:18.800 --> 00:12:21.280
these are entirely different, like application models.
00:12:21.280 --> 00:12:27.040
You really chose the serial port. Okay. All right, then let's go with that. Cool. All right. Now,
00:12:27.040 --> 00:12:34.880
before we get into Excel in Python, let's maybe talk about working with Excel from Python, like the
00:12:34.880 --> 00:12:41.120
reverse, right? So you talked a lot about notebooks and many people go from kind of notebook into CSV,
00:12:41.120 --> 00:12:47.760
into Excel. And there's some common tools for doing that. Like even built into Pandas, for example,
00:12:47.760 --> 00:12:51.680
could be Wilson's what they can do there before they have to necessarily decide, like,
00:12:51.680 --> 00:12:57.040
I need to run it in inside of Excel. I think there's still a special value to having structured code.
00:12:57.040 --> 00:12:57.840
Oh yeah, for sure.
00:12:57.840 --> 00:13:02.960
That you could have. I mean, notebooks is like kind of a fuzzy structured code. It's not like,
00:13:02.960 --> 00:13:08.640
it's not like a proper PY file because you can run the stuff still out of order, but at least within
00:13:08.640 --> 00:13:13.600
the cell, it runs within order. You know, it's like one dimensional out of order rather than like Excel,
00:13:13.600 --> 00:13:17.680
Excel, which is two dimensional potentially, or three if you're taking to other workbooks. But
00:13:17.680 --> 00:13:21.280
anyway, what can people do if they're not just to talk to Excel now?
00:13:21.280 --> 00:13:25.520
Right. I think you kind of alluded to it, but the main way there are a couple of packages.
00:13:25.520 --> 00:13:32.800
One is actually built into Pandas, but I think it's a dependency on OpenPy Excel. But yeah, that basically,
00:13:32.800 --> 00:13:38.720
I've been playing around with that recently to try and see if I can get support for the
00:13:38.720 --> 00:13:45.920
new Python based cells in that parser. It turns out parsing XML is not fun and not something I like
00:13:45.920 --> 00:13:48.000
doing. But basically-
00:13:48.000 --> 00:13:53.040
I've spent the last two days parsing Excel or XML. Oh my gosh, it's not the funnest.
00:13:53.040 --> 00:13:57.360
And in particular, you know, standardized document specs, because that's basically,
00:13:57.360 --> 00:14:02.080
so there are wonderful packages out there already that do this for you. And so what you would do is
00:14:02.080 --> 00:14:07.600
you'd basically just hand your Excel file, CSV, whatever to these packages, and they would
00:14:07.600 --> 00:14:12.880
return them as basically whatever you like. Usually I go to Pandas tables because there are data frames,
00:14:12.880 --> 00:14:19.040
because why not? But yeah, so there's basically any format that you could pretty much want. There's
00:14:19.040 --> 00:14:24.480
probably already really nice tooling to import that. And you can just go right on your merry way using
00:14:24.480 --> 00:14:32.000
your Python. Yeah, you can use the XLSX writer if you want to create Excel. Yeah, yeah, yeah.
00:14:32.000 --> 00:14:32.800
Right? Like-
00:14:32.800 --> 00:14:39.440
Yeah, it's really cool. You can even like make plots. You can, from Python, make a plot in the Excel
00:14:39.440 --> 00:14:43.920
worksheet so that when somebody opens it, they see the plot made in Excel. Kind of crazy.
00:14:45.600 --> 00:14:51.440
This portion of Talk Python To Me is brought to you by Posit, the makers of Shiny, formerly RStudio,
00:14:51.440 --> 00:14:57.280
and especially Shiny for Python. Let me ask you a question. Are you building awesome things? Of
00:14:57.280 --> 00:15:02.080
course you are. You're a developer or data scientist. That's what we do. And you should check out Posit
00:15:02.080 --> 00:15:08.080
Connect. Posit Connect is a way for you to publish, share, and deploy all the data products that you're
00:15:08.080 --> 00:15:14.160
building using Python. People ask me the same question all the time. Michael, I have some cool data
00:15:14.160 --> 00:15:19.360
science project or notebook that I built. How do I share it with my users, stakeholders, teammates?
00:15:19.360 --> 00:15:26.560
Do I need to learn FastAPI or Flask or maybe Vue or ReactJS? Hold on now. Those are cool technologies,
00:15:26.560 --> 00:15:31.360
and I'm sure you'd benefit from them, but maybe stay focused on the data project. Let Posit Connect
00:15:31.360 --> 00:15:36.480
handle that side of things. With Posit Connect, you can rapidly and securely deploy the things you build
00:15:36.480 --> 00:15:42.800
in Python. Streamlit, Dash, Shiny, Bokeh, FastAPI, Flask, Quarto, ports, dashboards,
00:15:42.800 --> 00:15:48.560
and APIs. Posit Connect supports all of them. And Posit Connect comes with all the bells and
00:15:48.560 --> 00:15:53.840
whistles to satisfy IT and other enterprise requirements. Make deployment the easiest
00:15:53.840 --> 00:15:58.720
step in your workflow with Posit Connect. For a limited time, you can try Posit Connect for free
00:15:58.720 --> 00:16:06.640
for three months by going to talkpython.fm/posit. That's talkpython.fm/posit. The link is in your
00:16:06.640 --> 00:16:10.960
podcast player show notes. Thank you to the team at Posit for supporting Talk Python.
00:16:10.960 --> 00:16:16.640
That's been around for a long time. That's not like an announcement or anything.
00:16:16.640 --> 00:16:17.680
Yeah, yeah, yeah.
00:16:17.680 --> 00:16:23.200
There's been different ways, and they've come in and out of support for different Python versions and stuff.
00:16:23.200 --> 00:16:28.880
So, but there are ways to kind of do that. And I see that a lot of people are like, I'm working in
00:16:28.880 --> 00:16:34.960
notebooks, but the final consumer of this information is not a data scientist or a Python person. They
00:16:34.960 --> 00:16:41.280
live in Excel and they live in Outlook. The only thing I can give them is Excel spreadsheets or a PDF.
00:16:41.280 --> 00:16:46.480
Yep. That is pretty much always the output of my notebooks is I either export it, like the whole
00:16:46.480 --> 00:16:52.640
notebook as a PDF or yeah, basically organizing things such that I can use Pandas or something to
00:16:52.640 --> 00:16:59.200
export it to an Excel workbook. But it's kind of a pain then because like you have your, there's that
00:16:59.200 --> 00:17:03.600
translation step that you kind of have to keep going back and forth. If you're dialoguing, you know,
00:17:03.600 --> 00:17:08.000
somebody says, oh, but what happens if you do this? And it's like, okay, I got to go rerun and remake a
00:17:08.000 --> 00:17:11.040
new PDF for you. Could you just like do it?
00:17:11.040 --> 00:17:15.760
I just need a quick change. Like, could you just incorporate that? Like, yeah, it's not the same change.
00:17:15.760 --> 00:17:16.720
We got to read.
00:17:16.720 --> 00:17:21.840
Ideally, we all have nice DevOps pipelines where, you know, you just commit a change to source and it
00:17:21.840 --> 00:17:24.480
re-renders all the notebooks. That's in a perfect world.
00:17:24.480 --> 00:17:24.960
Of course, that's how it works.
00:17:24.960 --> 00:17:26.560
Yes, obviously that's how I always work.
00:17:26.560 --> 00:17:33.440
It is. So I guess, you know, before we get in the details of how this works, like, why?
00:17:33.440 --> 00:17:39.680
Right. Because we just laid out, it is possible to like go to notebooks, have Excel kind of as an
00:17:39.680 --> 00:17:41.440
output and a save as in a sense.
00:17:41.440 --> 00:17:46.880
The biggest thing for me is like, well, it's not intended, of course, as any sort of replacement.
00:17:46.880 --> 00:17:51.040
It really is, as you were kind of mentioning before, it's like a different front end sort
00:17:51.040 --> 00:17:55.600
of thing. Like we view there's Python scripts, there's Jupyter notebooks, which is kind of like
00:17:55.600 --> 00:18:01.680
an alternative front end for writing that Python code. I kind of think of using Excel and Python
00:18:01.680 --> 00:18:07.760
like this as kind of using a different kind of data first. Like whenever we're thinking about data,
00:18:07.760 --> 00:18:13.520
it's usually in some form of grid or table or something like that. So like being able to work
00:18:13.520 --> 00:18:20.800
with the data kind of visually in that sort of UI is really, really interesting and kind of to the
00:18:20.800 --> 00:18:25.520
point that you just made about like the end consumer might only know how to use Excel and
00:18:25.520 --> 00:18:29.360
doesn't know anything about Python or doesn't know anything about running DevOps pipeline.
00:18:29.360 --> 00:18:35.360
I didn't mean that comment either to be disparaging because there are people that do incredible stuff
00:18:35.360 --> 00:18:38.480
with Excel. Maybe they shouldn't, but they can and they do.
00:18:38.480 --> 00:18:43.680
I have in kind of delving trying to understand what are like the weirdest things Excel can do as I've been
00:18:43.680 --> 00:18:49.120
trying to make demos like people do some weird like competitions with Excel. Like there's the painting
00:18:49.120 --> 00:18:50.000
in Excel.
00:18:50.000 --> 00:18:51.840
Is there a flight simulator?
00:18:51.840 --> 00:18:57.520
There's flight simulator. Like you can, you know, with macros, it's very scary. So I, yeah,
00:18:57.520 --> 00:19:05.520
absolutely. Like I am, I am not an Excel pro. I am a Python developer who uses Excel when prompted.
00:19:05.520 --> 00:19:11.920
Yeah. Well, there's no question. Absolutely. No debate about it, that the upper bound of what
00:19:11.920 --> 00:19:16.160
you can do with Python is greater than the upper bound of what you can do with Excel. But so many
00:19:16.160 --> 00:19:20.720
people just have, they have tabular data and they need reports and understanding and change this,
00:19:20.720 --> 00:19:22.160
what happens there. And yeah.
00:19:22.160 --> 00:19:27.360
Yeah. And I, and I think the biggest value, like kind of having played with this now for
00:19:27.360 --> 00:19:32.720
a little over half a year is collaboration. You don't have that kind of, you don't have to
00:19:32.720 --> 00:19:36.240
have to have that iteration loop between, okay, here we talked about it, or, you know,
00:19:36.240 --> 00:19:40.320
you came and sat at my computer while I showed you, you know, my Jupyter notebook. So we can make
00:19:40.320 --> 00:19:45.760
changes like that. All of the things like having multiple people in the Excel doc at once, you know,
00:19:45.760 --> 00:19:50.080
you can just send the share link and they can join and they can run things in the notebook. They don't
00:19:50.080 --> 00:19:55.360
have to install or set up Python and you can just, you can leave comments like you would in Word docs.
00:19:55.360 --> 00:20:01.840
So like basically that whole kind of review and collaboration workflow just turns into something
00:20:01.840 --> 00:20:06.240
that people are already pretty familiar with, with like Word docs and stuff like that, which is a
00:20:06.240 --> 00:20:11.360
thing that Jupyter notebooks generally don't have. So I think that's kind of the, one of the most
00:20:11.360 --> 00:20:13.200
interesting differentiators between them.
00:20:13.200 --> 00:20:20.800
Yeah. I just like an hour and a half ago, released the episode on notebook seven and Jupyter
00:20:20.800 --> 00:20:23.840
lab four, which comes with the real time collaboration and stuff.
00:20:23.840 --> 00:20:24.960
Yes. Which is really exciting.
00:20:24.960 --> 00:20:27.520
Yeah. It's quite exciting. So it's, it's on the horizon.
00:20:27.520 --> 00:20:27.920
Yeah.
00:20:27.920 --> 00:20:33.440
If it hasn't been like a common thing, but even so again, a lot of these people are not going to be in
00:20:33.440 --> 00:20:34.800
the notebook to collaborate with you.
00:20:34.800 --> 00:20:35.920
Right. Right.
00:20:35.920 --> 00:20:41.760
It's fine. And you played with this for a while. How much of this is going to be a gateway experience
00:20:41.760 --> 00:20:47.120
to doing more like, ah, I couldn't remember that Excel formula, but I asked Copilot and it told me
00:20:47.120 --> 00:20:52.080
me to do this Python thing and I, it totally made more sense to me. Maybe I should just learn a
00:20:52.080 --> 00:20:56.480
little more Python and, and kind of grow beyond Excel. Like, what do you think of this as a,
00:20:56.480 --> 00:21:01.120
a first step for many people who would go, I'm not a programmer, which no way, I'm not a geek. No,
00:21:01.120 --> 00:21:02.160
but then they are.
00:21:02.160 --> 00:21:06.400
Yeah, they totally are. Like if you're playing around with a bunch of numbers and making fancy
00:21:06.400 --> 00:21:12.080
spreadsheets and stuff, I'm sorry, you're, you're nerdy about data. And so, yeah, I think it's a really,
00:21:12.080 --> 00:21:15.360
cause there are lots of really cool things you can do with Excel. But as you say,
00:21:15.360 --> 00:21:21.600
like some of the plotting features and stuff you can't do, like you can maybe do, but it'd be really
00:21:21.600 --> 00:21:25.440
hard. And some, it might just be straight up impossible to make, you know, like some of the
00:21:25.440 --> 00:21:32.080
matplotlib multi-paneled, aligned plots with like interesting statistical plotting things that aren't
00:21:32.080 --> 00:21:37.120
just your standard bar and chart sorts of stuff. So like, if you're trying to do some of those
00:21:37.120 --> 00:21:41.920
statistical modeling things, or the other big thing is like the Python and Excel, one of the,
00:21:41.920 --> 00:21:46.880
it has PyTorch and stuff like that. So if you want to do just kind of like entry level machine
00:21:46.880 --> 00:21:52.400
learning sorts of stuff, and you don't want to have to like set up your big data source and SQL servers,
00:21:52.400 --> 00:21:57.440
you know, whatever, what have you to work with it. If you just got your spreadsheet of like your home
00:21:57.440 --> 00:22:02.160
sensor data or something like that, you can actually just have it make a regression model.
00:22:02.160 --> 00:22:07.120
And you don't have to like spin up a whole thing, you know, and environments and stuff like that.
00:22:07.120 --> 00:22:11.440
When you predict the battery in your gate will be dead next week, you're going to need to take an
00:22:11.440 --> 00:22:12.080
action on this.
00:22:12.080 --> 00:22:16.080
Which will be great when then I dismiss the notification and still don't remember to do
00:22:16.080 --> 00:22:16.080
it.
00:22:16.080 --> 00:22:20.000
Why can't we leave the house? Well, remember that notification said you're not going to be able
00:22:20.000 --> 00:22:21.840
to open the gate in a week. That was a week ago.
00:22:21.840 --> 00:22:22.240
Mm.
00:22:22.240 --> 00:22:23.520
Mm. Sounds familiar.
00:22:23.520 --> 00:22:23.760
Yeah.
00:22:23.760 --> 00:22:26.960
I know we were talking earlier that you have some cool home assistant automation and stuff.
00:22:26.960 --> 00:22:27.840
Yeah, yeah, yeah.
00:22:27.840 --> 00:22:28.160
So.
00:22:28.160 --> 00:22:30.000
Awesome. And I have a gate that has a dead battery.
00:22:30.000 --> 00:22:35.600
So let's just jump into it. I mean, let's talk about what is Python in Excel? Like,
00:22:35.600 --> 00:22:38.080
how do people use it? What can it do? What can it do?
00:22:38.080 --> 00:22:43.600
In short, basically having the Python in Excel feature means that you can, when you're in a
00:22:43.600 --> 00:22:47.840
cell, if you type the equal sign, which is usually how you start a function in Excel.
00:22:47.840 --> 00:22:50.960
Right. You might type equal sum parenthesis and then off you go. Something like that.
00:22:50.960 --> 00:22:51.280
Exactly.
00:22:51.280 --> 00:22:52.080
Drag it around.
00:22:52.080 --> 00:22:58.000
So you type equals and then you type PY, open parentheses, and that basically turns the
00:22:58.000 --> 00:23:04.400
cell now into a Python cell. So it'll have a little visual change. It'll turn green on the left.
00:23:04.400 --> 00:23:06.880
And there's also a keyboard shortcut to do this.
00:23:06.880 --> 00:23:09.520
It should put the Python logo. It should be like a blue, yellow.
00:23:09.520 --> 00:23:10.480
You know? Come on.
00:23:10.480 --> 00:23:13.600
Yeah. Green is kind of the Excel color.
00:23:13.600 --> 00:23:13.600
Yeah, it turns green.
00:23:13.600 --> 00:23:14.000
I don't know.
00:23:14.000 --> 00:23:14.480
It turns green.
00:23:14.480 --> 00:23:18.000
Anyway, we can file feedback on this.
00:23:18.000 --> 00:23:22.480
Yeah, well, that's community feedback from the Python people. We need the Python logo in there.
00:23:22.480 --> 00:23:23.360
Let's make it happen.
00:23:23.360 --> 00:23:29.520
The idea is that basically you can change a cell in the Excel notebook into one that basically
00:23:29.520 --> 00:23:35.040
is kind of like a Jupyter cell. You can type Python code. You can import modules. You can write functions.
00:23:35.040 --> 00:23:41.760
You can print things like basically anything that would be valid in a Jupyter notebook cell. Maybe
00:23:41.760 --> 00:23:44.640
not necessarily the magic commands, but like...
00:23:44.640 --> 00:23:44.880
Sure.
00:23:44.880 --> 00:23:45.600
It's fair game.
00:23:45.600 --> 00:23:47.840
Get to the shell as a hacker, I just do.
00:23:47.840 --> 00:23:53.520
I did actually try a lot of that. So I should say, so once you've typed in your code there and
00:23:53.520 --> 00:24:00.400
you run the cell with Ctrl+Enter, it's not Shift+Enter because that's a very established keyboard shortcut in Excel,
00:24:00.400 --> 00:24:07.200
which moves you around in the grid. So pro tip, remember it's Ctrl+Enter. But what happens then is
00:24:07.200 --> 00:24:16.160
basically Excel connects to a container instance in Azure and which has a standardized Anaconda provided
00:24:16.160 --> 00:24:23.280
environment. So it's an Anaconda environment that has a fixed declarative set of packages, which is
00:24:23.280 --> 00:24:29.680
basically kind of what 80 to 90% of projects use. We can talk about like which ones that are that,
00:24:29.680 --> 00:24:32.320
which ones are in that environment in a second. Yeah.
00:24:32.320 --> 00:24:38.880
But your code then runs in this, you know, it referred to as like an iron box in this container instance.
00:24:38.880 --> 00:24:40.000
So it does not...
00:24:40.000 --> 00:24:41.680
It doesn't know who you are.
00:24:41.680 --> 00:24:47.600
It doesn't know who you... Well, I mean, you are... And it's basically running a Jupyter kernel in that box.
00:24:47.600 --> 00:24:47.920
Right, okay.
00:24:47.920 --> 00:24:54.160
So like the kind of communication, if you have multiple cells in your sheet that have Python code in it,
00:24:54.160 --> 00:24:58.560
every time you make a change, you know, just like an Excel would normally kind of like recalculate
00:24:58.560 --> 00:25:04.560
everything in the sheet, it will basically ping that iron box and as if it was kind of like a Jupyter notebook.
00:25:04.560 --> 00:25:11.120
That's the thing that surprised me most is that it doesn't just run locally because you could embed the Python runtime
00:25:11.120 --> 00:25:13.280
into Excel without any trouble.
00:25:13.280 --> 00:25:14.240
Yes.
00:25:14.240 --> 00:25:17.120
And in terms of making the good path work.
00:25:17.120 --> 00:25:17.600
Yes.
00:25:17.600 --> 00:25:23.280
But I think part of the, you know, part of the design philosophy with this as a feature
00:25:23.280 --> 00:25:31.040
to Excel is this is an enterprise product that enterprises want to use and IT admins want to feel good about,
00:25:31.680 --> 00:25:35.840
you know, what security permissions things, you know, where data can come from,
00:25:35.840 --> 00:25:39.680
like all the permissioning compliance good stuff.
00:25:39.680 --> 00:25:39.840
You don't want to see so...
00:25:39.840 --> 00:25:40.720
To go, it does what?
00:25:40.720 --> 00:25:41.120
Yeah, no.
00:25:41.120 --> 00:25:42.480
No, we're not doing that.
00:25:42.480 --> 00:25:44.720
And we just recovered from the ransomware thing.
00:25:44.720 --> 00:25:46.560
So we're definitely not, we're done.
00:25:46.560 --> 00:25:47.680
So what are the restrictions?
00:25:47.680 --> 00:25:49.680
It has no network access.
00:25:49.680 --> 00:25:49.920
Yeah.
00:25:49.920 --> 00:25:54.480
So that iron box does not talk to, the only thing it can talk to is your, you know,
00:25:54.480 --> 00:25:55.360
instance of Excel.
00:25:55.360 --> 00:25:56.960
It can receive commands, but it doesn't.
00:25:56.960 --> 00:25:58.720
It does not touch the internet otherwise.
00:25:58.720 --> 00:25:59.680
Interesting.
00:25:59.680 --> 00:26:05.920
So that automatically changes maybe what some people think, like, for example, pandas read
00:26:05.920 --> 00:26:08.960
CSV, give it a URL and say, I want the third table.
00:26:08.960 --> 00:26:10.880
Like it's not going to do those types of things.
00:26:10.880 --> 00:26:14.080
Or you can't do request.get against some external data.
00:26:14.080 --> 00:26:14.720
Mm-hmm.
00:26:14.720 --> 00:26:16.960
Which initially I was like really sad about.
00:26:17.840 --> 00:26:20.480
I understand the security things, but was a bit sad.
00:26:20.480 --> 00:26:25.920
But then some of my Excel friends showed me, because this is also kind of a challenge in
00:26:25.920 --> 00:26:26.480
Excel too.
00:26:26.480 --> 00:26:28.720
Like, you know, how do you get data?
00:26:28.720 --> 00:26:32.800
People have things in SQL servers, people are databases more generally.
00:26:32.800 --> 00:26:33.680
Data that updates every minute.
00:26:33.680 --> 00:26:34.800
I want to have it refreshed.
00:26:34.800 --> 00:26:35.280
Yeah.
00:26:35.280 --> 00:26:40.880
Like that kind of, it's not concurrency, but like keeping everything up to date is a really
00:26:40.880 --> 00:26:41.600
big headache.
00:26:41.600 --> 00:26:44.640
And the thing is, Excel is already very good at addressing this.
00:26:44.640 --> 00:26:48.640
So there's something called Power Query, which is another feature of Excel.
00:26:48.640 --> 00:26:53.200
And that's basically, so the scenarios like you described, like if there's a CSV somewhere
00:26:53.200 --> 00:26:58.880
in a GitHub repo that I want to pull in, you can use the Power Query feature, can go fetch
00:26:58.880 --> 00:27:02.400
that and bring that into your Excel file that then you can use with Python and Excel.
00:27:02.400 --> 00:27:02.880
Okay.
00:27:02.880 --> 00:27:09.600
So it kind of separates that task of getting the data to work with, kind of prize that out
00:27:09.600 --> 00:27:13.120
of the Python piece, but puts it in much safer hands.
00:27:13.120 --> 00:27:13.520
Sure.
00:27:13.520 --> 00:27:16.400
So secure hands, I guess, if that makes sense.
00:27:16.400 --> 00:27:16.640
Yeah.
00:27:16.640 --> 00:27:17.440
Restricted hands.
00:27:17.440 --> 00:27:17.840
Mm-hmm.
00:27:17.840 --> 00:27:18.400
Okay.
00:27:18.400 --> 00:27:20.080
It doesn't have access to your account.
00:27:20.080 --> 00:27:23.440
It doesn't have a token to your account, like your Office 365 or whatever.
00:27:23.440 --> 00:27:23.920
Yeah.
00:27:23.920 --> 00:27:24.960
It's pretty locked down.
00:27:24.960 --> 00:27:26.320
You can't pip install things.
00:27:26.320 --> 00:27:28.400
It comes free, free can.
00:27:28.400 --> 00:27:28.960
Yeah.
00:27:28.960 --> 00:27:31.920
It does what it says on the tin and like, okay, these are the things I can do.
00:27:31.920 --> 00:27:32.320
Yeah.
00:27:32.320 --> 00:27:38.960
And that, again, sounds kind of sad, but honestly, like I've been trying to make a bunch of samples
00:27:38.960 --> 00:27:46.480
and stuff and I haven't run into too many, like it's got numpy, pandas, scipy, seaborne,
00:27:46.480 --> 00:27:47.600
astropy.
00:27:47.600 --> 00:27:48.240
Yeah.
00:27:48.240 --> 00:27:49.920
Astropy is pretty awesome.
00:27:49.920 --> 00:27:54.160
It's got matplotlib, pytorch, as you said, pywavelets.
00:27:54.160 --> 00:27:57.040
How's that for a throwback to my mathematical days?
00:27:57.040 --> 00:27:59.680
That's some like fast forward transforms are way more complicated.
00:27:59.680 --> 00:28:00.240
Mm-hmm.
00:28:00.240 --> 00:28:03.120
scikit-learn, scipy, simpy.
00:28:03.120 --> 00:28:03.120
Yep.
00:28:03.120 --> 00:28:03.120
Yep.
00:28:03.120 --> 00:28:03.680
Yep.
00:28:03.680 --> 00:28:04.960
There's your matlab, right?
00:28:04.960 --> 00:28:06.640
Or your mathematical right there.
00:28:06.640 --> 00:28:07.200
And yeah.
00:28:07.200 --> 00:28:08.320
And others, right?
00:28:08.320 --> 00:28:09.520
I'll link to like the options.
00:28:09.520 --> 00:28:10.000
Mm-hmm.
00:28:10.000 --> 00:28:17.440
This portion of Talk Python To Me is brought to you by the Pybytes Python Developer Mindset
00:28:17.440 --> 00:28:17.920
Program.
00:28:17.920 --> 00:28:23.520
It's run by my two friends and frequent guests, Bob Belderbos and Julian Sequira.
00:28:23.520 --> 00:28:26.960
And instead of me telling you about it, let's hear them describe their program.
00:28:27.840 --> 00:28:33.200
2024 is unfolding and with it comes a world of opportunities in the Python landscape.
00:28:33.200 --> 00:28:38.800
Opportunities for your Python career and journey can come from anywhere and at any time.
00:28:38.800 --> 00:28:41.040
Are you ready to seize them when they do?
00:28:41.040 --> 00:28:44.720
Don't let this be the year when opportunities pass you by.
00:28:44.720 --> 00:28:51.280
Our Python coaching is designed to prepare you for the unexpected, to equip you with the skills and
00:28:51.280 --> 00:28:57.600
confidence needed to tackle any Python challenge. Whether it's a dream job, a complex project,
00:28:57.600 --> 00:29:00.880
or a new venture, you need to be ready for it.
00:29:00.880 --> 00:29:11.280
With Pybytes coaching, 2024 won't just be another year. Check out our PDM program and take the first step towards a year filled with Python success.
00:29:11.280 --> 00:29:15.360
Don't just dream about being a developer. Achieve it with Pybytes.
00:29:15.360 --> 00:29:25.920
Apply for the Python developer mindset today. It's quick and free to apply. The link is in your podcast player show notes. Thanks to Pybytes for sponsoring the show.
00:29:25.920 --> 00:29:42.320
Yeah. So yeah, like it really does cover most of like, obviously, there are still plenty of cases where you will just want to use Python and that's okay. Like that's this is in no way, shape or form is this supposed to like take over your entire Python workflow. It's really,
00:29:42.320 --> 00:29:48.960
you know, I still am doing my Python development. But basically, if there are people who I know I want to,
00:29:48.960 --> 00:30:02.720
like, especially communicate what I'm doing to like who that is their language, I now have a tool that I can kind of put the things that I would normally have in my development environment in a tool that they can access and interact with, too.
00:30:02.720 --> 00:30:05.700
So that, you know, also doesn't feel super bad to me.
00:30:05.700 --> 00:30:11.340
Well, what's the alternative? It's the right like Excel functions and VBA macros.
00:30:11.340 --> 00:30:12.840
Yeah, no, I'm not doing that.
00:30:12.840 --> 00:30:22.800
Yeah, it's a pretty massive upgrade. And I do think it's going to be a bit of a gateway like, you know what, this Python stuff's not that complicated. It's kind of cool. Maybe what do you do with those notebooks again?
00:30:23.360 --> 00:30:41.940
I think that'll be cool. Yeah, it'll happen. When I think about writing Python, I'll use something like PyCharm with like really cool code, like analysis. And then I'll think about it like with rough, maybe running as like an integration and autocomplete. And there might even be like an AI magic, or maybe this is happening in VS Code in a real similar way.
00:30:41.940 --> 00:30:49.720
Then when I think about writing Excel formulas, it's like one line across. These are not the same. So what is it like to write Python in Excel?
00:30:50.220 --> 00:31:06.920
Yeah, it definitely. I do generally try to keep like when I write note, like in Jupyter notebooks, I try to keep myself small, not just have giant things. But yeah, you're totally right. Like even just from the view, you can't really drag it much bigger than a couple lines.
00:31:06.920 --> 00:31:08.820
You can expand it. So it's at least multi line.
00:31:08.820 --> 00:31:17.960
Yeah, you can you can see multi line and that and that's fine. And it has syntax highlighting and it does have like tab completes. It's got like the do like the language server language server.
00:31:17.960 --> 00:31:19.700
Yeah, that's actually pretty good.
00:31:19.700 --> 00:31:41.680
As I think was in the keynote stuff today, there's copilot in Excel. And so like, we'll see, I think, some of these AI features also kind of help speed up some of those things. So like, if you're like, I just I want a pandas table here. And, you know, to convert this part of, you know, the diction, the column, that's all dictionaries to like their own columns and stuff like that.
00:31:41.680 --> 00:31:51.300
It'll just write it for you. And you don't have to that editor editing experience is definitely not the same as you know, working in my beloved VS Code.
00:31:51.300 --> 00:32:05.880
But well, if the keynote here at this conference was a drinking game and AI was a word, it wouldn't have lasted 15 minutes. And it was two and a half hour. I mean, there was so much AI this and AI that and the AI is talking to the AI is and here's and it's it's quite something.
00:32:05.880 --> 00:32:16.340
But they did show a really cool thing of like, I've got some Python code in here and I've got some related data and just asking like, all right, help me graph this in Python.
00:32:16.340 --> 00:32:20.860
And it would show you like a seaborne graph or matplotlib or something like that, which is pretty excellent, honestly.
00:32:21.100 --> 00:32:32.680
Yeah. And then in this case here, then it would be in a format that you could share with somebody that they could then Excel has what if scenario features where it makes it really easy to be like, well, you know, here's the forecast or whatever.
00:32:32.680 --> 00:32:34.320
But what if this change?
00:32:34.320 --> 00:32:35.920
What if interest rates go up?
00:32:35.920 --> 00:32:36.500
Yeah.
00:32:36.880 --> 00:32:40.820
And you have like these little dropdowns, like all of those actually work.
00:32:40.820 --> 00:32:45.960
And so like I can use cells that are like dropdown data validated dropdown cells.
00:32:45.960 --> 00:32:54.580
And that just feeds as feeds into my Python code and changes, you know, like the theme I'm using for my matplotlib plots or something like that.
00:32:54.580 --> 00:32:55.680
Go a little meta for a moment.
00:32:55.680 --> 00:33:08.820
Could I use pandas or XLXLX writer, whatever the extension is to write an Excel workbook, Reggie, that itself does has Python in it?
00:33:08.820 --> 00:33:10.100
In principle, yes.
00:33:10.100 --> 00:33:10.980
Are there just formulas?
00:33:10.980 --> 00:33:12.720
Are there just strings that go into formulas?
00:33:12.720 --> 00:33:13.200
Yes.
00:33:13.200 --> 00:33:21.040
And that's precisely what I've been working on recently is to try and figure out how to integrate into some of those, the existing Python packages.
00:33:21.040 --> 00:33:29.240
Because basically what you need to do is make sure all of that data is inserted into the XML document model in the right way and stuff.
00:33:29.240 --> 00:33:31.240
Like it's pretty cool.
00:33:31.240 --> 00:33:32.100
Like you can take it.
00:33:32.100 --> 00:33:34.580
You can take an Excel, you know, an Excel notebook.
00:33:34.580 --> 00:33:38.380
If you just change extension to zip, then you get to see all the XML that's inside of it.
00:33:38.380 --> 00:33:39.820
And you can just see.
00:33:39.820 --> 00:33:40.060
A red pill.
00:33:40.060 --> 00:33:45.920
You can just see the sheets that are like, here's the Python functions and you can just see the strings and stuff like that.
00:33:45.920 --> 00:33:47.900
So I don't think there's anything blocking that.
00:33:48.000 --> 00:33:57.420
And that's something that I want to be able to help contribute if I can to the community so that you could actually programmatically write your Excel sheet with Python from Python.
00:33:57.420 --> 00:33:57.440
Right.
00:33:57.440 --> 00:34:07.700
And so things that are not allowed, like screen scraping or talking to a database, you could kind of burn that into Excel as like fixed database and keep rebuilding that workbook or something like that.
00:34:07.700 --> 00:34:07.900
Yeah, you could do that.
00:34:08.900 --> 00:34:09.500
All right.
00:34:09.500 --> 00:34:11.400
Integration between Excel and Python.
00:34:11.400 --> 00:34:18.860
In Excel, I can say I want to take the sum and I select the big section and it's like a 1 colon a 20 and that sums those up.
00:34:18.860 --> 00:34:23.220
But in Python, data science land, I don't even know what that is.
00:34:23.220 --> 00:34:31.000
So how do I make, obviously the point of having Python there is to access chunks of data, process it, do other things to it, turn it into graphs.
00:34:31.400 --> 00:34:32.640
What's the interop story?
00:34:32.640 --> 00:34:38.420
Part of the Python and Excel feature is that in that content environment, there is a module called Excel.
00:34:38.420 --> 00:34:43.400
The abbreviation as it's loaded is X, the letter X and then the letter L.
00:34:43.400 --> 00:34:50.820
And basically that allows you to access all kinds of data and properties of the worksheet in Python.
00:34:50.820 --> 00:35:03.860
So that's basically, you know, whether anything from like the cell styling to you can literally just put XL parentheses and then in a string, the Excel selection, you know, A1 colon whatever selections.
00:35:04.040 --> 00:35:07.480
And it will just then is already available in Python.
00:35:07.480 --> 00:35:16.900
So there's kind of this interop package that allows you to transition data models between what Excel thinks of as data and what Python thinks of as data.
00:35:16.900 --> 00:35:17.180
Yeah.
00:35:17.180 --> 00:35:20.180
And basically that comes out as a Pandas data frame, right?
00:35:20.180 --> 00:35:20.520
Yeah.
00:35:20.520 --> 00:35:27.980
In general, once you run a Python or a Python, an Excel cell that has Python code in it, you run it.
00:35:29.000 --> 00:35:31.100
There's a lot of cells, sheets, code.
00:35:31.100 --> 00:35:34.880
After you run it, you have the option of two different output formats, actually.
00:35:34.880 --> 00:35:39.580
So you can, by default, I think it outputs as a Python object.
00:35:39.580 --> 00:35:39.840
Yeah.
00:35:39.840 --> 00:35:44.980
If it recognizes that it's a string or a list, it might just display that.
00:35:44.980 --> 00:35:49.440
Or you can kind of intentionally cast it to an Excel data type.
00:35:49.440 --> 00:35:56.420
And again, if it can automagically understand that, oh, this dictionary should just be in a list or something like that.
00:35:56.460 --> 00:36:01.560
And a lot of that functionality is kind of provided by the collaboration with Anaconda.
00:36:01.560 --> 00:36:10.900
So you'll see, like, if you hover over, if you have it return, like, a Python object, if you hover over it, it'll show you, like, you know, if it's a class, it might have some show you the properties.
00:36:10.900 --> 00:36:15.260
And then you could drill in and say, turn, you know, turn the output into displaying this property.
00:36:15.260 --> 00:36:17.700
But it shows you a little pop up with the Anaconda logo.
00:36:17.700 --> 00:36:21.580
Yeah, that's some interesting branding there.
00:36:21.580 --> 00:36:23.040
I did notice that as well.
00:36:23.140 --> 00:36:27.980
In Jupyter, I can have a cell, and I can say, like, X equals something.
00:36:27.980 --> 00:36:32.500
And then some other point in time, I can say X squared, whatever.
00:36:32.500 --> 00:36:44.580
That assumes I'm running them top to bottom, or at least those two cells, the one that defines the X is run before the one that uses the X, which makes notebooks a little bit non-deterministic.
00:36:44.860 --> 00:36:49.300
And if you don't just go run all cells, but Excel takes it to a whole nother level, right?
00:36:49.300 --> 00:36:53.300
This refers to that, which points over there, and then that goes up, and then it goes back down, and it's all over.
00:36:53.300 --> 00:36:55.520
How do we make sense of, like, the connections?
00:36:55.520 --> 00:37:03.600
Yeah, it is something that I've, like, always is very impressive about Excel, because I'll, like, copy and paste chunks of things, and somehow all the references stay correct.
00:37:03.600 --> 00:37:07.060
You know, like, that's not what would happen if I refactored my Python code.
00:37:07.060 --> 00:37:11.340
By default, the execution order goes left to right and then down.
00:37:11.340 --> 00:37:17.020
So that's something I got to, I generally just kind of, by convention, use single columns.
00:37:17.020 --> 00:37:18.500
We're just going down.
00:37:18.620 --> 00:37:20.440
And left to right on the number of worksheets.
00:37:20.440 --> 00:37:24.520
So it evaluates everything in the first, like, if you have tabs at the bottom for separate sheets.
00:37:24.520 --> 00:37:26.280
Yeah, you can go cross sheet as well.
00:37:26.280 --> 00:37:27.040
Oh, yeah, yeah.
00:37:27.040 --> 00:37:39.120
And so, like, honestly, one of the things, kind of habits I've developed working with this is the first sheet in, like, the top left corner, I put any sort of, like, inits or things that, like, things I want to have defined everywhere.
00:37:39.120 --> 00:37:42.520
I just put in literally the first cell that I could ever possibly evaluate.
00:37:42.520 --> 00:37:46.400
And then everything else from there will be what it's going to be.
00:37:46.900 --> 00:37:49.120
But that's how I make sure those things.
00:37:49.120 --> 00:37:54.500
And actually, when it spins up, there is a predefined kind of startup definition block.
00:37:54.500 --> 00:37:59.040
So it will automatically import pandas as PD, numpy as NP.
00:37:59.040 --> 00:38:03.420
Like, so you don't have to, like, a lot of that kind of standard boilerplate, you don't have to.
00:38:03.420 --> 00:38:05.800
But if I'm doing a notebook where I'm doing a lot of.
00:38:05.800 --> 00:38:06.560
Like scikit learners.
00:38:06.560 --> 00:38:07.140
Yeah, yeah.
00:38:07.140 --> 00:38:08.620
I just always want to have that there.
00:38:08.620 --> 00:38:10.380
I just got to copy pasta for that.
00:38:10.380 --> 00:38:14.220
Yeah, make an incredibly thin column.
00:38:14.940 --> 00:38:16.600
That's the viewer to expand it.
00:38:16.600 --> 00:38:16.660
Yeah.
00:38:16.660 --> 00:38:17.200
That's where you're right.
00:38:17.200 --> 00:38:18.740
That's actually, I've done that.
00:38:18.740 --> 00:38:19.120
Yeah.
00:38:19.120 --> 00:38:19.780
Awesome.
00:38:19.780 --> 00:38:29.960
But just like in a Jupyter notebook, though, that kernel, like, if you can define variables like that X, and you can use that anywhere else, and then you can also just put it in a cell, and you can have Excel refer to it.
00:38:29.960 --> 00:38:32.280
And it will also know what data is there.
00:38:32.480 --> 00:38:32.700
Right.
00:38:32.700 --> 00:38:36.340
You might have to do the Py or the XL to, like, transform it.
00:38:36.340 --> 00:38:36.640
Yeah, yeah, yeah.
00:38:36.640 --> 00:38:37.640
Otherwise, then you just use it.
00:38:37.640 --> 00:38:37.800
Yeah.
00:38:37.800 --> 00:38:38.320
Yeah.
00:38:38.320 --> 00:38:38.580
Awesome.
00:38:38.580 --> 00:38:43.840
So you said this runs in Azure, and it's some lockdown container.
00:38:43.840 --> 00:38:45.640
But what version of Python?
00:38:45.640 --> 00:38:48.860
Like, what OS is the container running?
00:38:48.860 --> 00:38:49.760
What's the story?
00:38:49.760 --> 00:38:51.840
What do you know about where this actually runs?
00:38:51.840 --> 00:38:54.220
Or, like, to the extent that people will care?
00:38:54.220 --> 00:38:54.860
Yeah, yeah.
00:38:54.940 --> 00:38:58.680
So I don't know the specific version.
00:38:58.680 --> 00:39:00.360
I'm guessing it's probably 3.11.
00:39:00.360 --> 00:39:08.460
But the idea is that it is a well-known and highly descriptive and fixed container description.
00:39:08.460 --> 00:39:13.780
It's running, I believe it's just kind of running on a standard Ubuntu, like, Docker.
00:39:13.780 --> 00:39:15.560
It's an Azure container service.
00:39:15.560 --> 00:39:19.360
Somebody could do a sys.platform and get that and get it out, right?
00:39:19.360 --> 00:39:22.480
Interestingly, yeah, you can try some of that.
00:39:22.480 --> 00:39:27.380
But how they've kind of helped lock down some of that is there's actually hooks.
00:39:27.380 --> 00:39:29.020
Because I was actually trying to do this.
00:39:29.020 --> 00:39:32.080
I was trying to exfiltrate and or escape the box.
00:39:32.080 --> 00:39:33.400
But they actually...
00:39:33.400 --> 00:39:33.680
Can't contain me.
00:39:33.680 --> 00:39:42.020
They have hooks in the Python interpreter that basically can catch anything before it touches anything outside of Python.
00:39:42.020 --> 00:39:44.920
And so it's, I mean, I am not a security expert.
00:39:44.920 --> 00:39:46.120
I am not a security expert.
00:39:46.120 --> 00:39:51.560
But any, like, naive sort of, like, ha, ha, ha, I'm going to import sys and, like, manipulate permissions.
00:39:51.920 --> 00:39:52.400
Yeah.
00:39:52.400 --> 00:39:53.080
OS.run.
00:39:53.080 --> 00:39:53.680
Yeah.
00:39:53.680 --> 00:39:54.880
Open pip install.
00:39:54.880 --> 00:39:55.760
Exactly.
00:39:55.760 --> 00:39:58.280
So all of that is pretty safe.
00:39:58.280 --> 00:39:58.500
Yeah.
00:39:58.500 --> 00:40:00.880
It's going to be, you know, a modern version of Python.
00:40:00.880 --> 00:40:02.740
It's going to have everything up to date.
00:40:02.740 --> 00:40:08.100
And from a supply chain standpoint, it's all provided by Anaconda, which they have assertions
00:40:08.100 --> 00:40:10.080
about code origin and stuff like that.
00:40:10.080 --> 00:40:12.920
So really, it should be a reprodu...
00:40:12.920 --> 00:40:19.140
The most best definition of a reproducible environment that will literally...
00:40:19.140 --> 00:40:30.100
So, like, even in perpetuity, like, if you hand the worksheet to somebody else and you 10 years later come back to it, assuming, you know, the earth hasn't blown up, presumably it'll run in a container of exactly the same description.
00:40:30.100 --> 00:40:36.600
So you don't have to worry about, oh, shoot, I wrote this in a version of Python that now the package was built with Python 2.7.
00:40:36.600 --> 00:40:38.280
Can you believe I wrote it in Python 3?
00:40:38.280 --> 00:40:38.780
Yeah.
00:40:38.780 --> 00:40:40.200
It's just like, we're in Python 14.
00:40:40.200 --> 00:40:40.880
It doesn't work.
00:40:40.880 --> 00:40:43.200
And so many things are still in 2.7.
00:40:43.540 --> 00:40:44.080
Yeah, I know.
00:40:44.080 --> 00:40:44.580
It's wrong.
00:40:44.580 --> 00:40:45.160
Yeah.
00:40:45.160 --> 00:40:53.860
Like, so the statement that I saw and sort of the stability says, existing workbooks will still calculate against the version of the environment the workbook was created on.
00:40:53.860 --> 00:40:59.920
And users can be prompted to upgrade if they want, like, new runtimes, new Python versions or whatever.
00:40:59.920 --> 00:41:04.780
So basically, there's no requirements.txt or pyproject.tom all pin in your versions.
00:41:04.780 --> 00:41:10.460
But at the time of creation, it kind of snapshots effectively a pinned virtual environment.
00:41:10.460 --> 00:41:10.840
Yeah.
00:41:11.200 --> 00:41:22.320
And as I mentioned, there is kind of like a fixed startup script for it that does those kind of suggested imports and, you know, settings for things, which you can actually view in Excel.
00:41:22.320 --> 00:41:24.420
It shows you and it's read only right now.
00:41:24.420 --> 00:41:28.100
But we that might be a thing that you might be able to edit at the beginning.
00:41:28.100 --> 00:41:34.780
So if, you know, there are the things in that one cell that I always put at the beginning, maybe I can change that for my notebooks, sorts of things.
00:41:34.780 --> 00:41:35.140
Yeah.
00:41:35.140 --> 00:41:36.960
So it's still in preview.
00:41:36.960 --> 00:41:37.800
It's not.
00:41:37.940 --> 00:41:44.100
If I go to my Excel that I've got installed on my Mac and just try to type this, it's not going to like it.
00:41:44.100 --> 00:41:44.820
It's not going to work.
00:41:44.820 --> 00:41:45.140
Okay.
00:41:45.140 --> 00:41:46.080
How do you get it?
00:41:46.080 --> 00:41:46.920
Yes.
00:41:47.060 --> 00:41:50.640
So right now you it's in the Windows Insider program.
00:41:50.640 --> 00:41:55.580
So you have to have a M365 sort of subscription for Excel.
00:41:55.580 --> 00:42:02.580
You have to enroll in the Insiders program, which you can do if you go to your account settings in the app.
00:42:02.580 --> 00:42:08.300
Right now, like because it is a gradual rollout, I think the initial public preview was only in September.
00:42:08.300 --> 00:42:09.320
Yeah, it's pretty new.
00:42:09.440 --> 00:42:17.280
Yeah, it's it's quite new, like pretty much every week when I start working on this, I'm like, all right, what, you know, does this look the same as it did last week?
00:42:17.280 --> 00:42:20.520
So but right now it's only available for Windows machines.
00:42:20.520 --> 00:42:26.800
It is fully intended to be available on all platforms, including the web based version, which will be super cool.
00:42:26.800 --> 00:42:27.120
Yeah.
00:42:27.240 --> 00:42:39.440
But yeah, right now, Windows initially and the feature itself is will have some sort of cost, what that is and how much and when is TBD.
00:42:39.440 --> 00:42:40.820
Just because this is a very good.
00:42:40.820 --> 00:42:41.280
Is it extra?
00:42:41.280 --> 00:42:41.760
Who knows?
00:42:41.760 --> 00:42:42.600
Yeah, exactly.
00:42:42.600 --> 00:42:43.320
Yeah.
00:42:43.320 --> 00:42:43.680
Awesome.
00:42:43.680 --> 00:42:45.860
Well, really cool to get a look at this.
00:42:45.860 --> 00:42:54.580
I think for me personally, I think the coolest thing about it is that it's going to expose more people to Python, give them a little bit of confidence and then they can go only 20 libraries.
00:42:54.580 --> 00:43:00.000
I heard, oh, over on PyPI.org, we're just under 500,000.
00:43:00.000 --> 00:43:02.220
Maybe I could expand my world of it, right?
00:43:02.220 --> 00:43:03.060
That's awesome.
00:43:03.060 --> 00:43:03.420
Yeah.
00:43:03.420 --> 00:43:08.540
And you don't have to like, all right, download a version of Python now or download a version of Anaconda.
00:43:08.540 --> 00:43:11.220
Now make sure you have all the things installed.
00:43:11.220 --> 00:43:14.340
Make sure you have permissions, things on path, like all of that just goes away.
00:43:14.340 --> 00:43:16.460
Create a virtual environment, activate it.
00:43:16.460 --> 00:43:17.420
Yeah, all this stuff.
00:43:17.420 --> 00:43:17.600
Yeah.
00:43:17.600 --> 00:43:19.500
It's just already handled for you.
00:43:19.500 --> 00:43:20.560
You can get right to the fun stuff.
00:43:20.560 --> 00:43:21.480
Yeah, that's super cool.
00:43:21.480 --> 00:43:22.160
All right.
00:43:22.300 --> 00:43:27.340
Well, I always ask at the end of the show for some PyPI package that you think is cool.
00:43:27.340 --> 00:43:30.100
You've come across like anything that you're like, oh, that's interesting.
00:43:30.100 --> 00:43:30.900
People should know about.
00:43:30.900 --> 00:43:32.260
It doesn't have to be popular.
00:43:32.260 --> 00:43:33.560
Oh, no, no, no.
00:43:33.560 --> 00:43:39.000
There's one I can't think of the name generates a bunch of fake data for you to use.
00:43:39.000 --> 00:43:39.560
Okay.
00:43:39.800 --> 00:43:41.400
There's, is it, Makaroo?
00:43:41.400 --> 00:43:42.520
And Makaroo is the website.
00:43:42.520 --> 00:43:43.080
Yeah.
00:43:43.080 --> 00:43:43.720
And there's Faker.
00:43:43.720 --> 00:43:46.680
Faker is good, but it's like hypothesis.
00:43:46.680 --> 00:43:47.260
Okay.
00:43:47.260 --> 00:43:48.460
Come up with a different one, though.
00:43:48.460 --> 00:43:54.540
Well, one that is near and dear to my heart is actually called PyQIR.
00:43:54.540 --> 00:43:55.360
Okay.
00:43:55.360 --> 00:43:57.980
So one of the, I mentioned the quantum computing stuff before.
00:43:57.980 --> 00:44:02.800
One of the open source communities I'm still really engaged with is actually an open source
00:44:02.800 --> 00:44:10.960
consortium to basically create a LLVM style machine agnostic, language agnostic representation
00:44:10.960 --> 00:44:11.860
for quantum programs.
00:44:11.860 --> 00:44:12.180
Wow.
00:44:12.180 --> 00:44:12.440
Okay.
00:44:12.560 --> 00:44:13.760
Which is really cool.
00:44:13.760 --> 00:44:18.480
And like coming most of programming right now has kind of was born in Python.
00:44:18.480 --> 00:44:24.120
Like Python was the host for all of this, but as it's growing up and, you know, kind of leaving
00:44:24.120 --> 00:44:29.120
the nest as it were from Python, basically we need ways to interrupt like this huge existing
00:44:29.120 --> 00:44:34.860
tool ecosystem with actual hardware and hardware that supports things that basically the Python
00:44:34.860 --> 00:44:37.860
like mental model doesn't really match up.
00:44:37.860 --> 00:44:41.360
Like, and that's why you kind of need domain specific languages for quantum computing.
00:44:41.540 --> 00:44:49.340
But this PyQIR package gives you kind of an easy way to connect and like, like bootstrap
00:44:49.340 --> 00:44:54.380
that interop from your quantum programs that you've written in Python to this intermediate
00:44:54.380 --> 00:45:00.340
representation, QIR, quantum intermediate representation, which is actually LLVM.
00:45:00.340 --> 00:45:07.980
So you can, you don't have to figure out how to write and export LLVM files, which is both
00:45:07.980 --> 00:45:11.120
very fun and informative and you can do it through Python and Rust.
00:45:11.480 --> 00:45:14.300
The PyO3 Rust crate is my favorite.
00:45:14.300 --> 00:45:14.560
Yeah.
00:45:14.560 --> 00:45:14.860
Yeah.
00:45:14.860 --> 00:45:20.240
So that's, if you're at all interested in quantum computing stuff and you have a Python
00:45:20.240 --> 00:45:24.440
skill set or interest, that would definitely be a package I'd say go check out.
00:45:24.440 --> 00:45:24.760
Excellent.
00:45:24.760 --> 00:45:25.420
All right.
00:45:25.420 --> 00:45:26.780
Well, final call to action.
00:45:26.780 --> 00:45:27.940
People want to get started with this stuff.
00:45:27.940 --> 00:45:28.400
What do they do?
00:45:28.400 --> 00:45:29.040
Yeah, definitely.
00:45:29.040 --> 00:45:34.240
There is a great blog post with the original announcement that's got all of the detailed
00:45:34.240 --> 00:45:38.600
steps for how you find and enroll in the insiders programs.
00:45:38.600 --> 00:45:40.940
But other than that, there is a GitHub repo.
00:45:40.940 --> 00:45:45.700
If you do get a chance to play with this and find all the things that you hate about it and
00:45:45.700 --> 00:45:50.640
want to come yell at people about, I field a lot of those questions and enjoy it.
00:45:51.120 --> 00:45:56.480
So yeah, you can just come file issues on the GitHub repo and, you know, like if something
00:45:56.480 --> 00:46:00.740
isn't working the way you thought or you find a bug, that's kind of the best way that the
00:46:00.740 --> 00:46:01.620
team is very active.
00:46:02.620 --> 00:46:07.840
And so that's a great way to kind of get some good interaction with the folks who are building
00:46:07.840 --> 00:46:08.560
this in real time.
00:46:08.560 --> 00:46:09.760
Very cool.
00:46:09.760 --> 00:46:10.320
All right.
00:46:10.320 --> 00:46:11.440
Well, Sarah, thanks for being here.
00:46:11.440 --> 00:46:12.080
Yeah, absolutely.
00:46:12.080 --> 00:46:13.040
Thanks so much for having me.
00:46:13.040 --> 00:46:13.500
Yeah, you bet.
00:46:13.500 --> 00:46:13.720
Cheers.
00:46:13.720 --> 00:46:13.940
Cheers.
00:46:13.940 --> 00:46:17.360
This has been another episode of Talk Python To Me.
00:46:17.360 --> 00:46:19.180
Thank you to our sponsors.
00:46:19.180 --> 00:46:20.780
Be sure to check out what they're offering.
00:46:20.780 --> 00:46:22.200
It really helps support the show.
00:46:22.200 --> 00:46:27.000
This episode is sponsored by Posit Connect from the makers of Shiny.
00:46:27.000 --> 00:46:31.500
Publish, share, and deploy all of your data projects that you're creating using Python.
00:46:32.080 --> 00:46:38.100
Dreamlet, Dash, Shiny, Bokeh, FastAPI, Flask, Quarto, Reports, Dashboards, and APIs.
00:46:38.100 --> 00:46:40.480
Posit Connect supports all of them.
00:46:40.480 --> 00:46:46.140
Try Posit Connect for free by going to talkpython.fm/Posit, P-O-S-I-T.
00:46:46.140 --> 00:46:49.780
Are you ready to level up your Python career?
00:46:49.780 --> 00:46:54.680
And could you use a little bit of personal and individualized guidance to do so?
00:46:54.680 --> 00:46:58.500
Check out the PyBytes Python Developer Mindset program.
00:46:58.500 --> 00:46:59.960
Want to level up your Python?
00:46:59.960 --> 00:47:04.100
We have one of the largest catalogs of Python video courses over at Talk Python.
00:47:04.100 --> 00:47:09.200
Our content ranges from true beginners to deeply advanced topics like memory and async.
00:47:09.200 --> 00:47:11.860
And best of all, there's not a subscription in sight.
00:47:11.860 --> 00:47:14.780
Check it out for yourself at training.talkpython.fm.
00:47:14.780 --> 00:47:16.880
Be sure to subscribe to the show.
00:47:16.880 --> 00:47:19.660
Open your favorite podcast app and search for Python.
00:47:19.660 --> 00:47:20.960
We should be right at the top.
00:47:21.360 --> 00:47:26.140
You can also find the iTunes feed at /itunes, the Google Play feed at /play,
00:47:26.140 --> 00:47:30.340
and the direct RSS feed at /rss on talkpython.fm.
00:47:30.340 --> 00:47:33.300
We're live streaming most of our recordings these days.
00:47:33.300 --> 00:47:36.700
If you want to be part of the show and have your comments featured on the air,
00:47:36.700 --> 00:47:41.080
be sure to subscribe to our YouTube channel at talkpython.fm/youtube.
00:47:41.800 --> 00:47:43.180
This is your host, Michael Kennedy.
00:47:43.180 --> 00:47:44.480
Thanks so much for listening.
00:47:44.480 --> 00:47:45.640
I really appreciate it.
00:47:45.640 --> 00:47:47.540
Now get out there and write some Python code.
00:47:47.540 --> 00:47:48.540
Bye.
00:47:48.540 --> 00:47:49.540
Bye.
00:47:49.540 --> 00:47:50.540
Bye.
00:47:50.540 --> 00:47:51.540
Bye.
00:47:51.540 --> 00:47:52.540
Bye.
00:47:52.540 --> 00:47:53.540
Bye.
00:47:53.540 --> 00:47:54.540
Bye.
00:47:54.540 --> 00:47:55.540
Bye.
00:47:55.540 --> 00:47:56.540
Bye.
00:47:56.540 --> 00:47:57.540
Bye.
00:47:57.540 --> 00:47:58.540
Bye.
00:47:58.540 --> 00:47:59.540
Bye.
00:47:59.540 --> 00:48:00.540
Bye.
00:48:00.540 --> 00:48:01.540
Bye.
00:48:01.540 --> 00:48:02.540
Bye.
00:48:02.540 --> 00:48:03.540
Bye.
00:48:03.540 --> 00:48:04.540
Bye.
00:48:04.540 --> 00:48:05.040
you
00:48:05.040 --> 00:48:05.540
you
00:48:05.540 --> 00:48:06.040
you
00:48:06.040 --> 00:48:06.540
you
00:48:06.540 --> 00:48:07.040
you
00:48:07.040 --> 00:48:07.540
you
00:48:07.540 --> 00:48:08.040
you
00:48:08.040 --> 00:48:09.040
Thank you.