How to create editable data tables in Shiny for Python
You built a beautiful Shiny app…but someone just asked you where the Excel doc is. Well, what if they had the ability to edit a table within the app, as if it were a spreadsheet?! Editable tables are a recently developed feature for Shiny for Python, allowing the table itself to become an input value, so your app can react to—and use—the data your user enters. On Wednesday, July 31st at 11 am, Garrett Grolemund will walk through building a basic app using an editable table and discuss the many potential use cases now enabled. Helpful links: WSJF App - https://github.com/garrettgman/wsjf-app Interest Calculator App - https://github.com/garrettgman/mortgage-app Value Change App - https://github.com/garrettgman/value-changer-app To do a free trial of Posit Connect: https://pos.it/chat-with-us Posit Connect Cloud: https://connect.posit.cloud/ Q&A Session: https://youtube.com/live/-nG5aco-g84?feature=share Ps. We host these workflow demo events the last Wednesday of every month! We'd love to have you join us again! You can add it to your calendar here: https://pos.it/team-demo If you have any questions or suggestions for future sessions, let us know in the comments below :) #python
image: thumbnail.jpg
Transcript#
This transcript was generated automatically and may contain errors.
Hi, my name is Garrett and I'd like to give you a tour of the editable data tables feature that is now available in Shiny for Python. And the tour will start here. This is a blog post from last May where we announced that this feature is ready for Shiny for Python.
Then Shiny for Python is 0.9.0. Now it's a little higher version than that and it retains the feature. If you want to know about editable tables in Shiny for Python, you could read this blog post. But today we're going to go a little deeper than this blog post does so you'll find out all about this.
Before we get started though, I want to speak to those of you who primarily use Shiny for R. If you'd like to put a data table into your app and make it editable for your users to change the values, you can do that with the DT package using that alongside Shiny. And if you want to know more about it, come here to the DT development page, go to the Shiny tab and then scroll down at section 2.4 and it'll tell you how to make editable tables on the R side.
Motivating use cases for editable tables
Now for the rest of the day, we're going to focus on Shiny for Python. And before we start making the app, I sort of wanted to suggest, I wanted to motivate why you might use an editable table in a Shiny app. I made a few sample apps that demonstrate some ideas. This first app here is an app that you can use to prioritize different tasks against each other. Wait as short as jobs first is a very popular algorithm for prioritizing tasks. It requires you to set some values for each task and then it'll use an algorithm to score the tasks and the idea is you would do the task with the highest score.
Now I've done this work before in an Excel spreadsheet and this app just recreates what you might do in an Excel spreadsheet. And that in itself is incredibly useful and that's a reason you might use an editable table. So for example, if I wanted to add another job to this mix and reprioritize them, I could add a row. I can type in, you know, the name of the job. I could decide how big is this job as part of the algorithm. How much value does it provide? Is it urgent? Does it reduce some risk? Does it create some opportunities? And you can see the app responds by changing the rating over here to tell me which job I should do first.
Let's change the rating in a way that hopefully changes the job I should do first up here and see the effect. So yes, now the first job I should do is do work. I like using editable tables like this because when I give someone an Excel spreadsheet, even if it's like a worksheet, I feel like I'm asking them to do some work, fill out the spreadsheet. But if I put it into an app and I share it with them, I feel like I'm showing off that I did work for them and they can now just benefit by using my tool, even though the same thing's happening under the hood.
But if I put it into an app and I share it with them, I feel like I'm showing off that I did work for them and they can now just benefit by using my tool, even though the same thing's happening under the hood.
So that's one reason you might use editable tables to recreate workbook type features. Another reason is you might have a task where the input just makes sense to come as a table. So here's an app that tries to explore the dynamics of loan payments. As you know, when you take out a loan, you pay interest on that loan to service the loan. And over the lifetime of the loan, that interest could really add up. In fact, here we're looking at a 30 year loan with a reasonable interest rate, and we end up paying quite a bit in interest. More than half of our payments ultimately go to interest.
Now we might be interested in seeing what can we do to our payment schedule to change that, maybe lower the amount we pay over time on interest. So in the first part of this app, we pick a loan, it generates this payment schedule, and then for the second part of the app, we can actually go in here and alter these payments. So say we know that we're going to have a windfall on this month, and we know we'll have another windfall maybe a few months later. We just want to see what effect that will have. We could edit this table and then update our payments. And then we see the amount of money that accumulates on paying this app is very different. In fact, we finish paying it off pretty early and then things stay flat.
If we look at the composition of our payments, we see that much less is going towards interest and we're spending a lot less interest over the lifetime of this loan. Essentially, we're paying this loan off a little bit early. But this is the sort of task that it just doesn't make sense to imagine how many values your user wants to change and then provide that many widgets on the side for them to change. It just makes sense to let them change the whole schedule as a table and then have the Shiny app react to it. So that's the second reason you might want to use editable tables. It's just the most natural input.
And then finally, here's a much slimmer app. It doesn't look fancy because this is the app I'm going to step you through the code of in just a few minutes. I didn't want us to get too distracted on rabbit holes about theming and styling and layout. What this app does is it allows you to upload a file, correct missing values in that file, and then download the result. So first I have an upload widget. I could pick any CSV file. I have one for us as a variety of missing value types. I can then filter down to rows that have particular missing values.
So these are the rows that have not a number, but maybe I think this file has recorded missing values as NAs. This one has. Or maybe I think it's just completely missing values. This one has one of those too. And once I find one of those, I can go in here. Let's switch to NA. It'll be a little easier. And I could change the missing values. Maybe I know that this date is actually the next day. And maybe I could look up in my records and say, why didn't this get to date? I'll put in the date right here. We sold this many, this much dollars and this many units on that date. Once I've made those changes now, I could download the whole file again, but those edits I've made now appear in the file.
Now this is a really lightweight app, but I'm going to go through it with you because I think it's adjacent to the sort of things that you might want to do with editable tables. You might want to pull in a table from some sort of data store. It could be a local file like a CSV, but maybe it's a database or something. And then you make changes to that table. And then you might want to send it back to the data store or download it or forward it on to a customer.
And then one last thing that's a little subtle, but we'll take a look at is this app includes some rules that save us from making mistakes. So if I want to edit this value here, I could do that. It's a missing value. But if I want to edit this value here, it's going to say you can only edit cells that contain missing values. Maybe a fat finger did or something. So it will only let me edit the values that appear up here. But you can imagine making a rule for your apps that maybe asks the person for some credentials at the start of the app. And based on what those credentials are, they either get permission to make edits or they don't. Or they could edit some columns but not others. Or we record who made those edits. These are all rules that you could use with an editable table.
Building the app in VS Code
So let's take a look at what's involved in making this. This app and the others that I showed you are all available on GitHub. And we'll put the links below the YouTube video so you can find them. And I'm going to use this GitHub repo to sort of walk us through this app. But I'm not going to do it here on GitHub. I'm going to do it where we would build the app in VS Code. This is VS Code running inside of Posit Workbench.
So if you've used Posit Workbench primarily as an R user, you might not know that when you add a new session, you can open up a VS Code IDE or a JupyterLab IDE or a Jupyter Notebook. It doesn't have to be RStudio. And that's exactly what I've done. I've opened up a VS Code IDE. I've cloned the GitHub repo. I've created a new Python environment and installed the packages from requirements.txt. And since we're working with Shiny, I've also installed the Shiny extension for VS Code and, of course, the Python extensions.
Which means I have the app right here for us to work with. I can click this play button to run the app. So it's firing up a Shiny session in the background. And I'm having a little trouble with my preview window today, but I'll open it in a browser. And I can see the app here in my browser.
Let's make a new file. We'll put the app there. And I'll move stuff over bit by bit. It'll be a little better than watching me type everything from scratch, but it'll allow us to build this app together. All right. So we have a blank app.py file. This is how you'd start to build any Shiny app in Python. This app is written in Shiny Express, which is a more lightweight syntax for Shiny than Shiny Core. You can read all about it on the Shiny website. But I'll step you through it as we go.
So first we want to import the packages and modules that we'll use. And today we're going to use Pandas. That's no surprise. We're also going to use Shiny. We're making a Shiny app. And specifically we're going to use some modules from the Shiny Express module Shiny. And one thing from the Shiny types, because we're importing a file and this file info will help us do that.
Uploading a file and reading it as a data frame
So task number one, let's make it so this app can upload a file and then do something with the file. First, I want to let the user upload a file. And we could do that with this widget, which comes in the UI module. It's called input file. Let's copy it over and then take a look at it.
All right. This is an input widget. It's going to save an object. And we're telling it, save it under the name file. It's going to display a label to the viewer. The labels choose CSV file. And then for this particular widget, it helps to tell the widget what file types to accept and whether or not to accept multiple files. So now we've put the widget in here. And because this is Shiny Express, that's all we need to do to start building this app.
We can see we've now added that widget. Shiny Express sort of encapsulates both the server and the UI side in single commands. And so by writing that one command, we have this widget. Of course, if we use the widget to upload a file, the app won't really do anything with it yet.
So let's go there next. The next thing I want to do is take this file object, which contains many, many things, and extract from it a data frame that we can start manipulating. And that's what this reactive calc does.
Reactive calc creates an object that updates reactively to the things it uses to build itself. But reactive calcs just float around on the server side for us to use. They don't display anywhere. When you create a reactive calculation, you have to provide a function that rebuilds that calculation whenever Shiny needs to rebuild it. And here's our function. It says take this file input object that we're collecting up here. It should return either none if nothing's been uploaded or something of the type file info, which we have up here from Shiny types. If no one's uploaded anything and it's none, we're just going to return an empty data frame created with pandas. But if someone has uploaded something, we're going to extract the data from it and we're going to read it in as a CSV and return that CSV. And because this app works with missing values and pandas has some built-in behavior related to missing values, I'm going to ask pandas not to do anything with missing values basically. Don't consider any strings as missing values. We'll take care of that ourselves.
So now at this point, the app would read in a file and turn that file into a data frame floating around on the server, but the user wouldn't know it or be able to benefit from it. Next, we want to show the user the data frame.
We could get that over here from display the data assemble table. Now, I'll go backtrack just a little bit because we don't have the finished app. We have our app. Our app has a table named parts data. Let's go render it as a data grid. Let's go use this function to turn into datagrid. And then it's going to pass that as a rendered data frame object to Shiny.
All right. Now it seems to be running in the background. And if I go look at the preview, all right, it might be working before actually. I forgot I have to upload something.
All right. So there's my file. It's a small file. It has some not a numbers, has some NAs, and it has some just purely blank values. Okay. So that's great. So we've just learned how to upload a file. But we want to talk about editable data tables. So let's make this a complete app.
Adding filtering and making the table editable
The first thing I'm going to do before editing those table tables is use this day frame to manipulate with a filter function. So let's just add this in here. This creates a text box that we can use to filter down the specific rows. This takes the parse data that we create up here and returns the filter data set. And if that's what I want to show, then I will change this back to filter data. The reason I'm doing this is you can imagine uploading a dataset with a million rows. You wouldn't want to see all of that. So now we can filter down to what we want.
But that table we looked at is not editable. And let's satisfy ourselves with that. This is the default behavior. You get a table, highlight things, but you can't change them. If you want to make it editable, there's just one argument you need to pass. And that is in render.datagrid or render.datatable, the function you use to build that table, just set editable equals true. And then your table is editable. So now I could change this to whatever. And it stays. It persists.
If you want to make it editable, there's just one argument you need to pass. And that is in render.datagrid or render.datatable, the function you use to build that table, just set editable equals true.
Downloading the edited data
Great. So what can we do with this? We want to save those changes as a file to download.
And the way you make that download button is use render.download. We're going to label the download download. Or go propose a default file name. This is the file name that it will be saved to unless the user changes it during the download process. And then in Shiny for Python, the way you make download helpers is you use this yield function to return back the dataset that you want the user's computer to download.
Where do we have data? Okay. So here, the data frame that contains the table that is editable, we named data. And that's what we're going to pay attention to to give it back to the user. This data table is now an object named data that not only gets displayed to the user, but is available on the server side for us to use and has certain attributes. The data view attribute is the data frame with the user's edits in it. So this is probably the most important view of the data that you want to pay attention to when you have an editable table. Data underscore view is what the user sees. It's the data table with all of their changes in it. If you want the original data table with no changes in it, that attribute is called data.
So we could download the original data table by using the dot data attribute. It's a function that calls back, I guess I should be saying it's a method, calls back this CSV or this data frame. There's no sense in using the original table here, the whole point is edit. So we're going to use the data view method to return the editable table. It's a data frame, a past day frame, so we're going to use the two CSV method. I'm going to take out the index. So this will create a CSV file and that's what will be downloaded on user's computer.
All right, so let's see if we can take that round trip now. There it is. Let's make an edit. Let's download it and here's that update data file name. I've already downloaded one, so there's one behind it and I get that file.
Adding a patch function to enforce editing rules
There's one last thing I wanted to take us through and that was how to make a rule that guides how the values are replaced when a user edits the data. And the way you make a rule or you handle parsing the user's inputs to make something different is you create a function for that editable table using the set patch fn function.
That might seem a little out of the blue, so first let's paste this in here and go through it and then I'll tell you where you'd find out about this. So the set patch fn function will be run on the values that the user edits into the table and the output of this function is what will go into the table. So here I'm writing a function that says first let's collect the original data set as it was. That's data.data and then it receives this patch value. That is the cell that the user is contributing and it has a number of attributes as a row number and a column number so I'm using those to find out where the user made the edit and I'm retrieving the original value from the original table at that location. The reason I want to do that is I want to create a rule that says if the original value wasn't a missing value they don't get to change it.
So now I just have a test that says okay is it this input value this is the value they told us was a missing value if so return the value of the patch the value that the user actually submitted and that's what will be put into the cell. But if it's not that original that missing value the user is talking about instead I'm going to ask if I need to show a notification the notification is going to have a message that says you know you can only edit cells that contain that value you put in there and all this format to include that value and it's going to be an error and then it's just going to return the original value so nothing will be changed and that's what created that behavior for us and now my app is complete.
But like I said this seems a little out of the blue so I wanted to take us to Shiny to show where you should look for things related to editable tables. Go to the reference for Shiny. I'm using Shiny Express today so I'd go to Shiny Express and then these things are documented under the render.dataframe section. There's a whole area about editing cells and that's where you find out that there is a function called dot set patches underscore fn and that you use that to create the rules and the things that we just did.
That is how you could easily make a data table or data grid in your Shiny app editable — use that argument editable equals true. And then if you want to have some sort of special behavior use the set patch function of the object that you just made the data grid or data table. And if you want to access the edits that the user created use the data underscore view method for that object. With those things there you just open up a whole new world of letting your user provide information to your app in a new way and the sky is the limit. So I'll stop it right there and then we'll go to the Q&A so we could take some questions.
