#446: Python in Excel Transcript

00:00 Why is Python so popular? There's plenty of room for debate on this, but one solid reason is it's

00:04 easy to adopt, easy to use, and caters to people who are not quite developers, data scientists, but

00:10 need to get some computing done. Do you know where the largest untap set of those people hang out?

00:16 Excel. That's why it's super exciting that Python is now going to be built directly into Excel.

00:22 Just go into a cell and type equals pi, and you're off writing full Python 3 code that is backed by a

00:29 lightweight anaconda distribution of Python. We have Dr. Sarah Kaiser here to give us the rundown on

00:35 Python and Excel. This is Talk Python to Me, episode 445, recorded live and on location at

00:42 Microsoft Ignite in Seattle, November 16th, 2023.

00:58 Welcome to Talk Python to Me, a weekly podcast on Python. This is your host, Michael Kennedy.

01:05 Follow me on Mastodon, where I'm @mkennedy, and follow the podcast using @talkpython,

01:10 both on fosstodon.org. Keep up with the show and listen to over seven years of past episodes at

01:16 talkpython.fm. We've started streaming most of our episodes live on YouTube. Subscribe to our YouTube

01:23 channel over at talkpython.fm/youtube to get notified about upcoming shows and be part of that

01:29 episode. This episode is sponsored by Posit Connect from the makers of Shiny. Publish, share, and deploy

01:35 all of your data projects that you're creating using Python. Streamlit, Dash, Shiny, Bokeh, FastAPI,

01:41 Flask, Quarto, Reports, Dashboards, and APIs. Posit Connect supports all of them. Try Posit Connect for free

01:48 by going to talkpython.fm/Posit, P-O-S-I-T. And it's brought to you by the PyBytes Developer

01:56 Mindset Program. PyBytes' core mission is to help you break the vicious cycle of tutorial paralysis

02:01 through developing real-world applications. The PyBites Developer Mindset Program will help you build

02:06 the confidence you need to become a highly effective developer. The link is in your podcast player show

02:11 notes. Sarah, welcome to Talk Python to me. Hey, how's it going? Hey, it's going super well, super well here

02:19 at Ignite for one more time to talk about cool stuff with Python and things going on. This time it's going

02:25 to be Excel and Python. It's like Reese's Pieces. You got your chocolate and my peanut butter. Like,

02:32 what's going on here? I mean, the cats and dogs, are they living together? What's happening?

02:37 The old school and the new school. That's right. The open source and the not?

02:42 Yeah, yeah. My background is I've been a data scientist for a long time. I did my PhD in quantum

02:47 computing, in like experimental physics stuff. So trying, I actually, my first programming language

02:52 was Mathematica. And that was actually what got me hooked on kind of the notebook sort of interface.

02:57 Yeah, okay. Well, normally people say their first programming language when that's the background is

03:02 math lab. Yeah. I would say Mathematica is like the proper mathematicians programming space because it

03:10 does symbolic math, right? Which is so remarkable when you see it go. Yeah. Like that was basically

03:16 like I could visualize. The thing was being able to do easy visualizations. So actually after undergrad,

03:23 I worked at Mathematica for a while on their visualizations team.

03:26 Yeah. What technologies was that in?

03:28 I was actually just writing Mathematica in Mathematica. Thankfully I didn't have to touch

03:32 any of the optimization stuff underneath, but designing new like gauges and plotting built-ins

03:38 and stuff like that. I really enjoy visualizing and understanding data that way. And so when I got

03:44 to grad school, realized that none of our instruments talked together. Like everything was an entirely

03:51 separate archaic non-documented protocol and we were having to somehow like either sit by hand and match

03:58 up all of these CSV dumps from the different tools and stuff like that. So basically I started learning

04:03 Python to make our lab work. So initially it was like using a serial and C types libraries to actually

04:10 interface with these APIs. But I realized someone once showed me a Jupyter notebooks and I was like,

04:16 Okay. This is how we're, this is the way.

04:18 This is Mathematica for Python.

04:20 Exactly. So I have been using Jupyter notebooks almost since they came out and really enjoyed that

04:28 both as a learning tool, a teaching tool, and a kind of reproducible science tool.

04:34 Yeah. It's just taken the world by storm. Really. They were here at Microsoft Ignite,

04:38 the keynote, they went, they had like basically one code oriented demo in the two and a half hour

04:44 our keynote. And it was now we have a Jupyter notebook with some Python code that's going to

04:49 do some machine learning and you know, back to cats and dogs.

04:53 Yeah. Yeah. Yeah. Yeah.

04:54 That's incredible. So yeah, very cool. These days, what are you doing?

04:57 I am a developer advocate at Microsoft on the Python team focusing on data science and machine learning

05:05 sorts of Python tools. So I get to, I get to do all the fun stuff with pandas, numpy,

05:11 scipy, all of those things. And that's kind of why when I heard about the Python and Excel preview,

05:18 I was like, Oh, I have done many pandas import from Excel sort of things and was kind of curious

05:26 to see. Cause initially I was like, Hmm, I'm pretty cool with my Python. I'm not sure why I want to go

05:32 that way, but suffice it to say, I think I I'm convinced now.

05:36 Are you? Okay. Yeah.

05:37 Yeah. Because it's kind of a different way than a Jupyter notebook. It's for pluses and minus. We'll

05:42 talk about some fun ones of those as we go. Who are you evangelizing Python on the behalf of Microsoft

05:49 too? So are you talking to like companies that do .NET saying, Hey, you should do Python. Are you talking to

05:54 companies that love Python and saying like, Hey, you should consider Azure or our, our offering? What

06:01 does your world look like? What do you do today?

06:02 Yeah. A lot of it is doing testing. I work a lot with the VS Code team and the Jupyter specifically,

06:08 like the Jupyter features in VS Code to make sure all the notebooks work to make sure, you know,

06:13 packages with kind of weird output visualizations, you know, are they rendering properly? Generally,

06:19 I think I work with a lot of the actual Python open source maintainer communities in the kind of scientific

06:26 Python space. So like with the num focus supported projects and really just try to kind of understand

06:32 what are they running into? And as we're definitely seeing both with all of the LLM and machine learning

06:39 stuff, like data is getting so big that I definitely can't open it in one Excel sheet. And so the data is,

06:46 you know, basically, we're to the it's all native on the cloud. And so there's, I think, a lot of

06:52 kind of the academic space that I'm, I come from of like, that isn't really part of the curriculum or

06:57 what's taught or, you know, people kind of have to learn it ad hoc. And so basically, I kind of like

07:04 to think of myself as someone who gets to go do all that learning about kind of how does science and

07:10 data interact with the cloud, and in this case, specifically Azure, and just try and help build

07:15 out scenarios, like build sample apps, things like that, that can make it so they don't have to waste

07:21 half their PhD time trying to understand cloud DevOps stuff.

07:26 Yeah, it sounds a little bit like, you know, a lot of people who are just brought into a lab and said,

07:31 here, you're going to learn Python, and then you can go do your, your actual research, they often sort of

07:36 skimp out on like, some of the very basic software practices, like what is Git? What is DevOps? How do I run

07:43 this? Like, yeah, those kinds of things, right? So maybe you've run a lot of workshops, like, have you?

07:49 Basically, in my postdocs, I got really tired of my teams and groups that I was working with not

07:56 knowing. So I basically made a standard two day workshop that was like, all of the computer science

08:01 skills you missed in your physics degree. We did have at one point data that just existed only on

08:08 a jump drive, and that jump drive walked away. And that does not feel super good from a research

08:13 standpoint. I can imagine.

08:14 Yeah, let's just say we, we had a review of that and instituted new policies.

08:20 We decided backups are good.

08:22 Yep.

08:23 Yeah, it's a bit of a divergent, but I worked on some math research before I graduated and got my

08:30 bachelor's. And it wasn't, it was just in C++, but we had these large silicon graphics computers,

08:37 like big mainframe thing. We had, you know, Onyx workstations that went into it. We came in one morning

08:41 and nobody could log in. The computers wouldn't respond or turn on us. It's like a quarter million

08:47 dollar computer that got used and it won't turn on. We're like, oh boy. What had happened is one of

08:53 the grad students, non developer types, just doing their research, had a problem with their program.

09:00 So they started logging to see what was going on and they left it running overnight. It filled the disk

09:05 to the very last bite. And apparently once the last bite was gone, the whole mainframe couldn't,

09:11 operate and just nobody could even figure out what was wrong with it forever. And eventually,

09:16 yeah. So, you know, practices.

09:18 Yeah. We also had a $5 million microscope get bricked because a younger grad student didn't

09:27 understand that Windows XP service pack zero should never be plugged into the internet.

09:31 Oh, so no firewall, none of that.

09:35 Oh, no, no, no, no. They were just like, well, it has an ethernet port and the company said they can,

09:39 you know, do support over TeamViewer or something like that.

09:42 Yeah. So they were like, yeah.

09:43 A TeamViewer. That's never going to go wrong.

09:44 Yeah. So we walked in and we had to yank it out of the wall and we had the police there the next day

09:50 because it had already been doing illegal financial transactions in about 30 seconds.

09:55 Wow. You mean a microscope. You're here to arrest a microscope.

09:58 Yeah. I was like, it's over there.

10:00 They're like, well, that's not a computer. It already is. All right. Another interesting thing

10:05 before we dive into our main topic here is I'm not sure if this is real or not. Did it happen or did

10:11 it not? This quantum work that you're doing? Yeah. So my PhD was on quantum cryptography

10:18 hardware. So both there exist commercial devices. So part of it was finding side channels and the

10:25 physical devices that you could buy off the shelf. And then the other part was designing satellite

10:30 optics for doing ground to satellite based key exchange. So, yeah, that was a lot of what I was

10:37 doing in the like prototyping things in the lab that would look like a satellite and we'd,

10:41 you know, satellite on one table, sender on the other. And we slowly worked out to,

10:46 we did a bunch of tests in an airplane because turns out like putting things on a satellite is

10:50 kind of expensive. And we just flew really high in a 747. Yeah. And weather balloons turn out to be

10:58 not super predictable in the path once you launch them. And so if you're trying to like aim telescopes,

11:04 yeah, it kind of ended up being messy, but it was pretty cool. We got to go,

11:07 I got to help with the design process for these single photon kind of collection systems that went

11:13 from literally, I'm just putting a couple lenses on the table and running Python stepper motor things in

11:19 my Jupyter notebook to something that was actually automatically tracking and working from the ground

11:24 to an airplane, hopefully eventually a satellite, but that's kind of out of my hands at this point.

11:28 Yeah. Space takes a long time to get to a lot of planning and a lot of, we worked on it for

11:34 eight years and then we had our launch window missed by a week, you know, or something like that. Right?

11:39 Yeah, that's rough. And as a grad student, you got to get out of there.

11:41 Yeah. I got to get a degree. I need to eat.

11:44 Yeah.

11:45 So let's get, let's get going. Cool. Well, that sounds like a really fun project. You must have

11:50 learned a lot. And whenever you're talking to hardware, I think that's a special kind of programming.

11:55 It's not just, I run the program and then it just goes, but like there's stuff it touches.

11:59 Yeah. Like you don't get to necessarily do your own architectural design. You have to kind of

12:05 first observe and usually reverse engineer what the original device engineers came up with and then

12:11 figure out how is this, you know, this device is streaming base. This device, how am I going to like,

12:18 these are entirely different, like application models.

12:21 You really chose the serial port. Okay. All right, then let's go with that. Cool. All right. Now,

12:27 before we get into Excel in Python, let's maybe talk about working with Excel from Python, like the

12:34 reverse, right? So you talked a lot about notebooks and many people go from kind of notebook into CSV,

12:41 into Excel. And there's some common tools for doing that. Like even built into Pandas, for example,

12:47 could be Wilson's what they can do there before they have to necessarily decide, like,

12:51 I need to run it in inside of Excel. I think there's still a special value to having structured code.

12:57 Oh yeah, for sure.

12:57 That you could have. I mean, notebooks is like kind of a fuzzy structured code. It's not like,

13:02 it's not like a proper PY file because you can run the stuff still out of order, but at least within

13:08 the cell, it runs within order. You know, it's like one dimensional out of order rather than like Excel,

13:13 Excel, which is two dimensional potentially, or three if you're taking to other workbooks. But

13:17 anyway, what can people do if they're not just to talk to Excel now?

13:21 Right. I think you kind of alluded to it, but the main way there are a couple of packages.

13:25 One is actually built into Pandas, but I think it's a dependency on OpenPy Excel. But yeah, that basically,

13:32 I've been playing around with that recently to try and see if I can get support for the

13:38 new Python based cells in that parser. It turns out parsing XML is not fun and not something I like

13:45 doing. But basically-

13:48 I've spent the last two days parsing Excel or XML. Oh my gosh, it's not the funnest.

13:53 And in particular, you know, standardized document specs, because that's basically,

13:57 so there are wonderful packages out there already that do this for you. And so what you would do is

14:02 you'd basically just hand your Excel file, CSV, whatever to these packages, and they would

14:07 return them as basically whatever you like. Usually I go to Pandas tables because there are data frames,

14:12 because why not? But yeah, so there's basically any format that you could pretty much want. There's

14:19 probably already really nice tooling to import that. And you can just go right on your merry way using

14:24 your Python. Yeah, you can use the XLSX writer if you want to create Excel. Yeah, yeah, yeah.

14:32 Right? Like-

14:32 Yeah, it's really cool. You can even like make plots. You can, from Python, make a plot in the Excel

14:39 worksheet so that when somebody opens it, they see the plot made in Excel. Kind of crazy.

14:45 This portion of Talk Python to Me is brought to you by Posit, the makers of Shiny, formerly RStudio,

14:51 and especially Shiny for Python. Let me ask you a question. Are you building awesome things? Of

14:57 course you are. You're a developer or data scientist. That's what we do. And you should check out Posit

15:02 Connect. Posit Connect is a way for you to publish, share, and deploy all the data products that you're

15:08 building using Python. People ask me the same question all the time. Michael, I have some cool data

15:14 science project or notebook that I built. How do I share it with my users, stakeholders, teammates?

15:19 Do I need to learn FastAPI or Flask or maybe Vue or ReactJS? Hold on now. Those are cool technologies,

15:26 and I'm sure you'd benefit from them, but maybe stay focused on the data project. Let Posit Connect

15:31 handle that side of things. With Posit Connect, you can rapidly and securely deploy the things you build

15:36 in Python. Streamlit, Dash, Shiny, Bokeh, FastAPI, Flask, Quarto, ports, dashboards,

15:42 and APIs. Posit Connect supports all of them. And Posit Connect comes with all the bells and

15:48 whistles to satisfy IT and other enterprise requirements. Make deployment the easiest

15:53 step in your workflow with Posit Connect. For a limited time, you can try Posit Connect for free

15:58 for three months by going to talkpython.fm/posit. That's talkpython.fm/posit. The link is in your

16:06 podcast player show notes. Thank you to the team at Posit for supporting Talk Python.

16:10 That's been around for a long time. That's not like an announcement or anything.

16:16 Yeah, yeah, yeah.

16:17 There's been different ways, and they've come in and out of support for different Python versions and stuff.

16:23 So, but there are ways to kind of do that. And I see that a lot of people are like, I'm working in

16:28 notebooks, but the final consumer of this information is not a data scientist or a Python person. They

16:34 live in Excel and they live in Outlook. The only thing I can give them is Excel spreadsheets or a PDF.

16:41 Yep. That is pretty much always the output of my notebooks is I either export it, like the whole

16:46 notebook as a PDF or yeah, basically organizing things such that I can use Pandas or something to

16:52 export it to an Excel workbook. But it's kind of a pain then because like you have your, there's that

16:59 translation step that you kind of have to keep going back and forth. If you're dialoguing, you know,

17:03 somebody says, oh, but what happens if you do this? And it's like, okay, I got to go rerun and remake a

17:08 new PDF for you. Could you just like do it?

17:11 I just need a quick change. Like, could you just incorporate that? Like, yeah, it's not the same change.

17:15 We got to read.

17:16 Ideally, we all have nice DevOps pipelines where, you know, you just commit a change to source and it

17:21 re-renders all the notebooks. That's in a perfect world.

17:24 Of course, that's how it works.

17:24 Yes, obviously that's how I always work.

17:26 It is. So I guess, you know, before we get in the details of how this works, like, why?

17:33 Right. Because we just laid out, it is possible to like go to notebooks, have Excel kind of as an

17:39 output and a save as in a sense.

17:41 The biggest thing for me is like, well, it's not intended, of course, as any sort of replacement.

17:46 It really is, as you were kind of mentioning before, it's like a different front end sort

17:51 of thing. Like we view there's Python scripts, there's Jupyter notebooks, which is kind of like

17:55 an alternative front end for writing that Python code. I kind of think of using Excel and Python

18:01 like this as kind of using a different kind of data first. Like whenever we're thinking about data,

18:07 it's usually in some form of grid or table or something like that. So like being able to work

18:13 with the data kind of visually in that sort of UI is really, really interesting and kind of to the

18:20 point that you just made about like the end consumer might only know how to use Excel and

18:25 doesn't know anything about Python or doesn't know anything about running DevOps pipeline.

18:29 I didn't mean that comment either to be disparaging because there are people that do incredible stuff

18:35 with Excel. Maybe they shouldn't, but they can and they do.

18:38 I have in kind of delving trying to understand what are like the weirdest things Excel can do as I've been

18:43 trying to make demos like people do some weird like competitions with Excel. Like there's the painting

18:49 in Excel.

18:50 Is there a flight simulator?

18:51 There's flight simulator. Like you can, you know, with macros, it's very scary. So I, yeah,

18:57 absolutely. Like I am, I am not an Excel pro. I am a Python developer who uses Excel when prompted.

19:05 Yeah. Well, there's no question. Absolutely. No debate about it, that the upper bound of what

19:11 you can do with Python is greater than the upper bound of what you can do with Excel. But so many

19:16 people just have, they have tabular data and they need reports and understanding and change this,

19:20 what happens there. And yeah.

19:22 Yeah. And I, and I think the biggest value, like kind of having played with this now for

19:27 a little over half a year is collaboration. You don't have that kind of, you don't have to

19:32 have to have that iteration loop between, okay, here we talked about it, or, you know,

19:36 you came and sat at my computer while I showed you, you know, my Jupyter notebook. So we can make

19:40 changes like that. All of the things like having multiple people in the Excel doc at once, you know,

19:45 you can just send the share link and they can join and they can run things in the notebook. They don't

19:50 have to install or set up Python and you can just, you can leave comments like you would in Word docs.

19:55 So like basically that whole kind of review and collaboration workflow just turns into something

20:01 that people are already pretty familiar with, with like Word docs and stuff like that, which is a

20:06 thing that Jupyter notebooks generally don't have. So I think that's kind of the, one of the most

20:11 interesting differentiators between them.

20:13 Yeah. I just like an hour and a half ago, released the episode on notebook seven and Jupyter

20:20 lab four, which comes with the real time collaboration and stuff.

20:23 Yes. Which is really exciting.

20:24 Yeah. It's quite exciting. So it's, it's on the horizon.

20:27 Yeah.

20:27 If it hasn't been like a common thing, but even so again, a lot of these people are not going to be in

20:33 the notebook to collaborate with you.

20:34 Right. Right.

20:35 It's fine. And you played with this for a while. How much of this is going to be a gateway experience

20:41 to doing more like, ah, I couldn't remember that Excel formula, but I asked Copilot and it told me

20:47 me to do this Python thing and I, it totally made more sense to me. Maybe I should just learn a

20:52 little more Python and, and kind of grow beyond Excel. Like, what do you think of this as a,

20:56 a first step for many people who would go, I'm not a programmer, which no way, I'm not a geek. No,

21:01 but then they are.

21:02 Yeah, they totally are. Like if you're playing around with a bunch of numbers and making fancy

21:06 spreadsheets and stuff, I'm sorry, you're, you're nerdy about data. And so, yeah, I think it's a really,

21:12 cause there are lots of really cool things you can do with Excel. But as you say,

21:15 like some of the plotting features and stuff you can't do, like you can maybe do, but it'd be really

21:21 hard. And some, it might just be straight up impossible to make, you know, like some of the

21:25 matplotlib multi-paneled, aligned plots with like interesting statistical plotting things that aren't

21:32 just your standard bar and chart sorts of stuff. So like, if you're trying to do some of those

21:37 statistical modeling things, or the, the other big thing is like the Python and Excel, one of the,

21:41 it has PyTorch and stuff like that. So if you want to do just kind of like entry level machine

21:46 learning sorts of stuff, and you don't want to have to like set up your big data source and SQL servers,

21:52 you know, whatever, what have you to work with it. If you just got your spreadsheet of like your home

21:57 sensor data or something like that, you can actually just have it make a regression model.

22:02 And you don't have to like spin up a whole thing, you know, and environments and stuff like that.

22:07 When you predict the battery in your gate will be dead next week, you're going to need to take an

22:11 action on this.

22:12 Which will be great when then I dismiss the notification and still don't remember to do

22:16 it.

22:16 Why can't we leave the house? Well, remember that notification said you're not going to be able

22:20 to open the gate in a week. That was a week ago.

22:21 Mm.

22:22 Mm. Sounds familiar.

22:23 Yeah.

22:23 I know we were talking earlier that you have some cool home assistant automation and stuff.

22:26 Yeah, yeah, yeah.

22:27 So.

22:28 Awesome. And I have a gate that has a dead battery.

22:30 So let's just jump into it. I mean, let's talk about what is Python in Excel? Like,

22:35 how do people use it? What can it do? What can it do?

22:38 In short, basically having the Python in Excel feature means that you can, when you're in a

22:43 cell, if you type the equal sign, which is usually how you start a function in Excel.

22:47 Right. You might type equal sum parenthesis and then off you go. Something like that.

22:50 Exactly.

22:51 Drag it around.

22:52 So you type equals and then you type PY, open parentheses, and that basically turns the

22:58 cell now into a Python cell. So it'll have a little visual change. It'll turn green on the left.

23:04 And there's also a keyboard shortcut to do this.

23:06 It should put the Python logo. It should be like a blue, yellow.

23:09 You know? Come on.

23:10 Yeah. Green is kind of the Excel color.

23:13 Yeah, it turns green.

23:13 I don't know.

23:14 It turns green.

23:14 Anyway, we can file feedback on this.

23:18 Yeah, well, that's community feedback from the Python people. We need the Python logo in there.

23:22 Let's make it happen.

23:23 The idea is that basically you can change a cell in the Excel notebook into one that basically

23:29 is kind of like a Jupyter cell. You can type Python code. You can import modules. You can write functions.

23:35 You can print things like basically anything that would be valid in a Jupyter notebook cell. Maybe

23:41 not necessarily the magic commands, but like...

23:44 Sure.

23:44 It's fair game.

23:45 Get to the shell as a hacker, I just do.

23:47 I did actually try a lot of that. So I should say, so once you've typed in your code there and

23:53 you run the cell with Ctrl+Enter, it's not Shift+Enter because that's a very established keyboard shortcut in Excel,

24:00 which moves you around in the grid. So pro tip, remember it's Ctrl+Enter. But what happens then is

24:07 basically Excel connects to a container instance in Azure and which has a standardized Anaconda provided

24:16 environment. So it's an Anaconda environment that has a fixed declarative set of packages, which is

24:23 basically kind of what 80 to 90% of projects use. We can talk about like which ones that are that,

24:29 which ones are in that environment in a second. Yeah.

24:32 But your code then runs in this, you know, it referred to as like an iron box in this container instance.

24:38 So it does not...

24:40 It doesn't know who you are.

24:41 It doesn't know who you... Well, I mean, you are... And it's basically running a Jupyter kernel in that box.

24:47 Right, okay.

24:47 So like the kind of communication, if you have multiple cells in your sheet that have Python code in it,

24:54 every time you make a change, you know, just like an Excel would normally kind of like recalculate

24:58 everything in the sheet, it will basically ping that iron box and as if it was kind of like a Jupyter notebook.

25:04 That's the thing that surprised me most is that it doesn't just run locally because you could embed the Python runtime

25:11 into Excel without any trouble.

25:13 Yes.

25:14 And in terms of making the good path work.

25:17 Yes.

25:17 But I think part of the, you know, part of the design philosophy with this as a feature

25:23 to Excel is this is an enterprise product that enterprises want to use and IT admins want to feel good about,

25:31 you know, what security permissions things, you know, where data can come from,

25:35 like all the permissioning compliance good stuff.

25:39 You don't want to see so...

25:39 To go, it does what?

25:40 Yeah, no.

25:41 No, we're not doing that.

25:42 And we just recovered from the ransomware thing.

25:44 So we're definitely not, we're done.

25:46 So what are the restrictions?

25:47 It has no network access.

25:49 Yeah.

25:49 So that iron box does not talk to, the only thing it can talk to is your, you know,

25:54 instance of Excel.

25:55 It can receive commands, but it doesn't.

25:56 It does not touch the internet otherwise.

25:58 Interesting.

25:59 So that automatically changes maybe what some people think, like, for example, pandas read

26:05 CSV, give it a URL and say, I want the third table.

26:08 Like it's not going to do those types of things.

26:10 Or you can't do request.get against some external data.

26:14 Mm-hmm.

26:14 Which initially I was like really sad about.

26:17 I understand the security things, but was a bit sad.

26:20 But then some of my Excel friends showed me, because this is also kind of a challenge in

26:25 Excel too.

26:26 Like, you know, how do you get data?

26:28 People have things in SQL servers, people are databases more generally.

26:32 Data that updates every minute.

26:33 I want to have it refreshed.

26:34 Yeah.

26:35 Like that kind of, it's not concurrency, but like keeping everything up to date is a really

26:40 big headache.

26:41 And the thing is, Excel is already very good at addressing this.

26:44 So there's something called Power Query, which is another feature of Excel.

26:48 And that's basically, so the scenarios like you described, like if there's a CSV somewhere

26:53 in a GitHub repo that I want to pull in, you can use the Power Query feature, can go fetch

26:58 that and bring that into your Excel file that then you can use with Python and Excel.

27:02 Okay.

27:02 So it kind of separates that task of getting the data to work with, kind of prize that out

27:09 of the Python piece, but puts it in much safer hands.

27:13 Sure.

27:13 So secure hands, I guess, if that makes sense.

27:16 Yeah.

27:16 Restricted hands.

27:17 Mm-hmm.

27:17 Okay.

27:18 It doesn't have access to your account.

27:20 It doesn't have a token to your account, like your Office 365 or whatever.

27:23 Yeah.

27:23 It's pretty locked down.

27:24 You can't pip install things.

27:26 It comes free, free can.

27:28 Yeah.

27:28 It does what it says on the tin and like, okay, these are the things I can do.

27:31 Yeah.

27:32 And that, again, sounds kind of sad, but honestly, like I've been trying to make a bunch of samples

27:38 and stuff and I haven't run into too many, like it's got numpy, pandas, scipy, seaborne,

27:46 astropy.

27:47 Yeah.

27:48 Astropy is pretty awesome.

27:49 It's got matplotlib, pytorch, as you said, pywavelets.

27:54 How's that for a throwback to my mathematical days?

27:57 That's some like fast forward transforms are way more complicated.

27:59 Mm-hmm.

28:00 scikit-learn, scipy, simpy.

28:03 Yep.

28:03 Yep.

28:03 Yep.

28:03 There's your matlab, right?

28:04 Or your mathematical right there.

28:06 And yeah.

28:07 And others, right?

28:08 I'll link to like the options.

28:09 Mm-hmm.

28:10 This portion of Talk Python to Me is brought to you by the Pybytes Python Developer Mindset

28:17 Program.

28:17 It's run by my two friends and frequent guests, Bob Belderbos and Julian Sequira.

28:23 And instead of me telling you about it, let's hear them describe their program.

28:27 2024 is unfolding and with it comes a world of opportunities in the Python landscape.

28:33 Opportunities for your Python career and journey can come from anywhere and at any time.

28:38 Are you ready to seize them when they do?

28:41 Don't let this be the year when opportunities pass you by.

28:44 Our Python coaching is designed to prepare you for the unexpected, to equip you with the skills and

28:51 confidence needed to tackle any Python challenge. Whether it's a dream job, a complex project,

28:57 or a new venture, you need to be ready for it.

29:00 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.

29:11 Don't just dream about being a developer. Achieve it with Pybytes.

29:15 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.

29:25 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,

29:42 you know, I still am doing my Python development. But basically, if there are people who I know I want to,

29:48 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.

30:02 So that, you know, also doesn't feel super bad to me.

30:05 Well, what's the alternative? It's the right like Excel functions and VBA macros.

30:11 Yeah, no, I'm not doing that.

30:12 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?

30:23 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.

30:41 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?

30:50 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.

31:06 You can expand it. So it's at least multi line.

31:08 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.

31:17 Yeah, that's actually pretty good.

31:19 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.

31:41 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.

31:51 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.

32:05 But they did show a really cool thing of like, I've got some Python code in here and I've got some some related data and just asking like, all right, help me graph this in Python.

32:16 And it would show you like a seaborne graph or matplotlib or something like that, which is pretty excellent, honestly.

32:21 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.

32:32 But what if this change?

32:34 What if interest rates go up?

32:35 Yeah.

32:36 And you have like these little dropdowns, like all of those actually work.

32:40 And so like I can use cells that are like dropdown data validated dropdown cells.

32:45 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.

32:54 Go a little meta for a moment.

32:55 Could I use pandas or XLXLX writer, whatever the extension is to write an Excel workbook, Reggie, that itself does has Python in it?

33:08 In principle, yes.

33:10 Are there just formulas?

33:10 Are there just strings that go into formulas?

33:12 Yes.

33:13 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.

33:21 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.

33:29 Like it's pretty cool.

33:31 Like you can take it.

33:32 You can take an Excel, you know, an Excel notebook.

33:34 If you just change extension to zip, then you get to see all the XML that's inside of it.

33:38 And you can just see.

33:39 A red pill.

33:40 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.

33:45 So I don't think there's anything blocking that.

33:48 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.

33:57 Right.

33:57 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.

34:07 Yeah, you could do that.

34:08 All right.

34:09 Integration between Excel and Python.

34:11 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.

34:18 But in Python, data science land, I don't even know what that is.

34:23 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.

34:31 What's the interop story?

34:32 Part of the Python and Excel feature is that in that content environment, there is a module called Excel.

34:38 The abbreviation as it's loaded is X, the letter X and then the letter L.

34:43 And basically that allows you to access all kinds of data and properties of the worksheet in Python.

34:50 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.

35:04 And it will just then is already available in Python.

35:07 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.

35:16 Yeah.

35:17 And basically that comes out as a Pandas data frame, right?

35:20 Yeah.

35:20 In general, once you run a Python or a Python, an Excel cell that has Python code in it, you run it.

35:29 There's a lot of cells, sheets, code.

35:31 After you run it, you have the option of two different output formats, actually.

35:34 So you can, by default, I think it outputs as a Python object.

35:39 Yeah.

35:39 If it recognizes that it's a string or a list, it might just display that.

35:44 Or you can kind of intentionally cast it to an Excel data type.

35:49 And again, if it can automagically understand that, oh, this dictionary should just be in a list or something like that.

35:56 And a lot of that functionality is kind of provided by the collaboration with Anaconda.

36:01 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.

36:10 And then you could drill in and say, turn, you know, turn the output into displaying this property.

36:15 But it shows you a little pop up with the Anaconda logo.

36:17 Yeah, that's some interesting branding there.

36:21 I did notice that as well.

36:23 In Jupyter, I can have a cell, and I can say, like, X equals something.

36:27 And then some other point in time, I can say X squared, whatever.

36:32 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.

36:44 And if you don't just go run all cells, but Excel takes it to a whole nother level, right?

36:49 This refers to that, which points over there, and then that goes up, and then it goes back down, and it's all over.

36:53 How do we make sense of, like, the connections?

36:55 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.

37:03 You know, like, that's not what would happen if I refactored my Python code.

37:07 By default, the execution order goes left to right and then down.

37:11 So that's something I got to, I generally just kind of, by convention, use single columns.

37:17 We're just going down.

37:18 And left to right on the number of worksheets.

37:20 So it evaluates everything in the first, like, if you have tabs at the bottom for separate sheets.

37:24 Yeah, you can go cross sheet as well.

37:26 Oh, yeah, yeah.

37:27 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.

37:39 I just put in literally the first cell that I could ever possibly evaluate.

37:42 And then everything else from there will be what it's going to be.

37:46 But that's how I make sure those things.

37:49 And actually, when it spins up, there is a predefined kind of startup definition block.

37:54 So it will automatically import pandas as PD, numpy as NP.

37:59 Like, so you don't have to, like, a lot of that kind of standard boilerplate, you don't have to.

38:03 But if I'm doing a notebook where I'm doing a lot of.

38:05 Like scikit learners.

38:06 Yeah, yeah.

38:07 I just always want to have that there.

38:08 I just got to copy pasta for that.

38:10 Yeah, make an incredibly thin column.

38:14 That's the viewer to expand it.

38:16 Yeah.

38:16 That's where you're right.

38:17 That's actually, I've done that.

38:18 Yeah.

38:19 Awesome.

38:19 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.

38:29 And it will also know what data is there.

38:32 Right.

38:32 You might have to do the Py or the XL to, like, transform it.

38:36 Yeah, yeah, yeah.

38:36 Otherwise, then you just use it.

38:37 Yeah.

38:37 Yeah.

38:38 Awesome.

38:38 So you said this runs in Azure, and it's some lockdown container.

38:43 But what version of Python?

38:45 Like, what OS is the container running?

38:48 What's the story?

38:49 What do you know about where this actually runs?

38:51 Or, like, to the extent that people will care?

38:54 Yeah, yeah.

38:54 So I don't know the specific version.

38:58 I'm guessing it's probably 3.11.

39:00 But the idea is that it is a well-known and highly descriptive and fixed container description.

39:08 It's running, I believe it's just kind of running on a standard Ubuntu, like, Docker.

39:13 It's an Azure container service.

39:15 Somebody could do a sys.platform and get that and get it out, right?

39:19 Interestingly, yeah, you can try some of that.

39:22 But how they've kind of helped lock down some of that is there's actually hooks.

39:27 Because I was actually trying to do this.

39:29 I was trying to exfiltrate and or escape the box.

39:32 But they actually...

39:33 Can't contain me.

39:33 They have hooks in the Python interpreter that basically can catch anything before it touches anything outside of Python.

39:42 And so it's, I mean, I am not a security expert.

39:44 I am not a security expert.

39:46 But any, like, naive sort of, like, ha, ha, ha, I'm going to import sys and, like, manipulate permissions.

39:51 Yeah.

39:52 OS.run.

39:53 Yeah.

39:53 Open pip install.

39:54 Exactly.

39:55 So all of that is pretty safe.

39:58 Yeah.

39:58 It's going to be, you know, a modern version of Python.

40:00 It's going to have everything up to date.

40:02 And from a supply chain standpoint, it's all provided by Anaconda, which they have assertions

40:08 about code origin and stuff like that.

40:10 So really, it should be a reprodu...

40:12 The most best definition of a reproducible environment that will literally...

40:19 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.

40:30 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.

40:36 Can you believe I wrote it in Python 3?

40:38 Yeah.

40:38 It's just like, we're in Python 14.

40:40 It doesn't work.

40:40 And so many things are still in 2.7.

40:43 Yeah, I know.

40:44 It's wrong.

40:44 Yeah.

40:45 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.

40:53 And users can be prompted to upgrade if they want, like, new runtimes, new Python versions or whatever.

40:59 So basically, there's no requirements.txt or pyproject.tom all pin in your versions.

41:04 But at the time of creation, it kind of snapshots effectively a pinned virtual environment.

41:10 Yeah.

41:11 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.

41:22 It shows you and it's read only right now.

41:24 But we that might be a thing that you might be able to edit at the beginning.

41:28 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.

41:34 Yeah.

41:35 So it's still in preview.

41:36 It's not.

41:37 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.

41:44 It's not going to work.

41:44 Okay.

41:45 How do you get it?

41:46 Yes.

41:47 So right now you it's in the Windows Insider program.

41:50 So you have to have a M365 sort of subscription for Excel.

41:55 You have to enroll in the Insiders program, which you can do if you go to your account settings in the app.

42:02 Right now, like because it is a gradual rollout, I think the initial public preview was only in September.

42:08 Yeah, it's pretty new.

42:09 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?

42:17 So but right now it's only available for Windows machines.

42:20 It is fully intended to be available on all platforms, including the web based version, which will be super cool.

42:26 Yeah.

42:27 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.

42:39 Just because this is a very good.

42:40 Is it extra?

42:41 Who knows?

42:41 Yeah, exactly.

42:42 Yeah.

42:43 Awesome.

42:43 Well, really cool to get a look at this.

42:45 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.

42:54 I heard, oh, over on PyPI.org, we're just under 500,000.

43:00 Maybe I could expand my world of it, right?

43:02 That's awesome.

43:03 Yeah.

43:03 And you don't have to like, all right, download a version of Python now or download a version of Anaconda.

43:08 Now make sure you have all the things installed.

43:11 Make sure you have permissions, things on path, like all of that just goes away.

43:14 Create a virtual environment, activate it.

43:16 Yeah, all this stuff.

43:17 Yeah.

43:17 It's just already handled for you.

43:19 You can get right to the fun stuff.

43:20 Yeah, that's super cool.

43:21 All right.

43:22 Well, I always ask at the end of the show for some PyPI package that you think is cool.

43:27 You've come across like anything that you're like, oh, that's interesting.

43:30 People should know about.

43:30 It doesn't have to be popular.

43:32 Oh, no, no, no.

43:33 There's one I can't think of the name generates a bunch of fake data for you to use.

43:39 Okay.

43:39 There's, is it, Makaroo?

43:41 And Makaroo is the website.

43:42 Yeah.

43:43 And there's Faker.

43:43 Faker is good, but it's like hypothesis.

43:46 Okay.

43:47 Come up with a different one, though.

43:48 Well, one that is near and dear to my heart is actually called PyQIR.

43:54 Okay.

43:55 So one of the, I mentioned the quantum computing stuff before.

43:57 One of the open source communities I'm still really engaged with is actually an open source

44:02 consortium to basically create a LLVM style machine agnostic, language agnostic representation

44:10 for quantum programs.

44:11 Wow.

44:12 Okay.

44:12 Which is really cool.

44:13 And like coming most of programming right now has kind of was born in Python.

44:18 Like Python was the host for all of this, but as it's growing up and, you know, kind of leaving

44:24 the nest as it were from Python, basically we need ways to interrupt like this huge existing

44:29 tool ecosystem with actual hardware and hardware that supports things that basically the Python

44:34 like mental model doesn't really match up.

44:37 Like, and that's why you kind of need domain specific languages for quantum computing.

44:41 But this PyQIR package gives you kind of an easy way to connect and like, like bootstrap

44:49 that interop from your quantum programs that you've written in Python to this intermediate

44:54 representation, QIR, quantum intermediate representation, which is actually LLVM.

45:00 So you can, you don't have to figure out how to write and export LLVM files, which is both

45:07 very fun and informative and you can do it through Python and Rust.

45:11 The PyO3 Rust crate is my favorite.

45:14 Yeah.

45:14 Yeah.

45:14 So that's, if you're at all interested in quantum computing stuff and you have a Python

45:20 skill set or interest, that would definitely be a package I'd say go check out.

45:24 Excellent.

45:24 All right.

45:25 Well, final call to action.

45:26 People want to get started with this stuff.

45:27 What do they do?

45:28 Yeah, definitely.

45:29 There is a great blog post with the original announcement that's got all of the detailed

45:34 steps for how you find and enroll in the insiders programs.

45:38 But other than that, there is a GitHub repo.

45:40 If you do get a chance to play with this and find all the things that you hate about it and

45:45 want to come yell at people about, I field a lot of those questions and enjoy it.

45:51 So yeah, you can just come file issues on the GitHub repo and, you know, like if something

45:56 isn't working the way you thought or you find a bug, that's kind of the best way that the

46:00 team is very active.

46:02 And so that's a great way to kind of get some good interaction with the folks who are building

46:07 this in real time.

46:08 Very cool.

46:09 All right.

46:10 Well, Sarah, thanks for being here.

46:11 Yeah, absolutely.

46:12 Thanks so much for having me.

46:13 Yeah, you bet.

46:13 Cheers.

46:13 Cheers.

46:13 This has been another episode of Talk Python to Me.

46:17 Thank you to our sponsors.

46:19 Be sure to check out what they're offering.

46:20 It really helps support the show.

46:22 This episode is sponsored by Posit Connect from the makers of Shiny.

46:27 Publish, share, and deploy all of your data projects that you're creating using Python.

46:32 Dreamlet, Dash, Shiny, Bokeh, FastAPI, Flask, Quarto, Reports, Dashboards, and APIs.

46:38 Posit Connect supports all of them.

46:40 Try Posit Connect for free by going to talkpython.fm/Posit, P-O-S-I-T.

46:46 Are you ready to level up your Python career?

46:49 And could you use a little bit of personal and individualized guidance to do so?

46:54 Check out the PyBytes Python Developer Mindset program.

46:58 Want to level up your Python?

46:59 We have one of the largest catalogs of Python video courses over at Talk Python.

47:04 Our content ranges from true beginners to deeply advanced topics like memory and async.

47:09 And best of all, there's not a subscription in sight.

47:11 Check it out for yourself at training.talkpython.fm.

47:14 Be sure to subscribe to the show.

47:16 Open your favorite podcast app and search for Python.

47:19 We should be right at the top.

47:21 You can also find the iTunes feed at /itunes, the Google Play feed at /play,

47:26 and the direct RSS feed at /rss on talkpython.fm.

47:30 We're live streaming most of our recordings these days.

47:33 If you want to be part of the show and have your comments featured on the air,

47:36 be sure to subscribe to our YouTube channel at talkpython.fm/youtube.

47:41 This is your host, Michael Kennedy.

47:43 Thanks so much for listening.

47:44 I really appreciate it.

47:45 Now get out there and write some Python code.

47:47 Bye.

47:48 Bye.

47:49 Bye.

47:50 Bye.

47:51 Bye.

47:52 Bye.

47:53 Bye.

47:54 Bye.

47:55 Bye.

47:56 Bye.

47:57 Bye.

47:58 Bye.

47:59 Bye.

48:00 Bye.

48:01 Bye.

48:02 Bye.

48:03 Bye.

48:04 you you you you you you you Thank you.