My Workflow for Building any Streamlit Dashboard Project

can we recreate this dashboard in a python web app I'm on a call with a financial data analyst here's a pro at pulling data from an SQL database and crafting those impressive Excel dashboards with data cleaning VBA macros and crazy and maintainable pivot table shots everywhere he's excited to convert those Excel macros to a python weapon so he showed me this blog post and I've been building streaming for the past three years now I can tell you this doesn't look anything like extremely this is how far I could get after 2 hours of duck dbsql plotly and nested streamit columns let me show you how I did open a folder in vs code with an empty streamit app python file and a random Excel with sales data I got one from the power bi website our first goal is to display that axon file in a streamit app that live reloads whenever you edit code create a new stream it conflict tunnel file this contains specific streamit configuration for this project folder especially setting that live reload on saving the file open a new terminal run the stream it run your file command and now your streaming web apps should appear in a new browser tab write a new Hello World markdown title in the script save the script and your app should live reload with a new text vs code and streamit are now configured and raring to go now we need to display that Excel file we should load it in a pandas data frame pandas as a dedicated read Excel method let's slide that inside a new load data method and decorate it with SD cache data so stream it stores and reuses the result from the method whenever it runs with the same input variable St Wright is like a python chameleon it defaults into whatever valuable type you give it now sure you can pass the data framing as to write to display it but you'll be missing out on Advanced Data from display configuration that comes with the SD data frame method get into the habit of browsing the streaming documentation for all existing Widgets or watch my streaming epic video it has a demo of almost every widget let's steer away from using a hard-coded path to our Excel file we place it with SD file uploader this will return your uploaded object which you can store in a python variable and load with pandas stream it will stream and despair when the user hasn't uploaded anything keep this in check with an if condition on the variable and a message info to guide the user into uploading something your app re-runs from top to bottom at every user interaction so when the script re-runs because you uploaded a new file the variable will now have an identity and pass the if check good work let's tidy up the app and make some space for the upcoming data plots stream it as an array of layout components to let us arrange hide and sort out widgets for instance that file uploader takes too much space in the center section you can set any widget in the sidebar by calling sidebar in between the St module and the method or include multiple elements to the same sidebar by invoking them under the with SD sidebar context manager let's also hide the data frame in a data preview block with st expander no one likes to see massive data tables with numbers right that data frame looks a little trapped in a bird cage while you can find a wide layout in the hamburger menu to free some space for the frame a preferable option is to initialize the app in wide mode using the set page config first thing in the app this is also a good opportunity to provide a tab title and icon to differentiate it from the Thousand other running stack Overflow tabs finally that year column doesn't look like a year column extremely has a new column config for data frames that lets you configure the display of named columns in this case display the UR column as a full number no commas included looking good now here comes the good part where the stage is all set to add dazzling plots or big raw data frame is ready but it's not easy to consume data like this do we need to clean it remove duplicates drop some columns and aggregate it before we can build plots that people can consume our Excel worksheet isn't a pandas data frame so you can run it through pandas processing but today I'd rather not my data analyst they turn away from Reading pandas code they need a daily fix of SQL code there are multiple libraries for running SQL of a pandas yet my current preferred way is duckdb it's an in-memory vectorized database that can run SQL on top of pandas data frame without any time consuming import to the DB tables let's process the data View for the bottom left line chart select the software sales for the year 2023 we now have a wide table where sails are spread into multiple month columns several plotting libraries accept a sequence of columns as input but I recommend you get used to Melting your white data frame into a long data frame where all the sales of the mounts are pulled into a single column and another month column differentiate the mount for the rows in the FDB SQL world this is done through the unpivot statement stored a previous query in a CTE and unpivot the sales into two new long columns sales and month this is now ready for plotting you could easily use the streamit line chart method to plot this it will produce a pretty decent altired chart for you the issue is this child is not terribly customizable I have no clue how to add a title or annotate the data points you could rebuild it in Altair but Altair doesn't seem to have a Gorge plot well I'll let you in my little secret for interactive extremely dashboards I use plotly for most of my interactive plots remotely has an easy to use plotly express API pretty similar to the Schmidt one but if you want more custom information plotting there's also the low level plotly graph objects in fact the only way I found to build this indicator I'll show you later in the video is with the low level graph objects anyway let's reveal the line chart with plotly Express using the correct x y and color column store the resulting plotly figure display it and stream it using the plotlet shot method and add a used Contour Rift so it fits the current column it's in yes imagine this as a very big column bloodly as an extensive documentation you'll find a plotly expressed line method as arguments to add a title markers and text annotations it will add the corresponding graph objects for you into the plotly figure we will update the position of all text annotations on the line shot Trace to top Center and there you are one good looking easy to update graph I'll blade through the other plots it's a different SQL query with a group by some and a bar chart sometimes grouped sometimes stacked for the top left corner we leave the easy to use plotly express API for the advanced low level plotly graph objects create two new methods one for the metric and one for The Gulch see we are building a plotly figure to which we're adding an indicator objects in the metric method we are leaving the default version of the indicator in the gulge method we are using the gauge mode of the indicator to transform the metric into a gauge pimp each gouge using the documentation with a maximum bound a bar color using the CSS values from the streamer color theme prefix and suffix for the displayed values and the titles for both the indicators now what is that sparkline hiding behind the indicator thankfully we are free to add any number of tracers to a given plus a figure add a new graph object field line chart with the values you need in the figure with the metric update the figure to hide all the axes and finally by playing a bit with the figure layout margins you can minimize the white space taken by both indicators all the graphs are now separated into distinct methods ready to be used by your streaming app and by the way you could decorate all of those with cache data so don't generated plotly graphs are stored for future rebrands well it's time to arrange all those plots in a grid to make a tasty looking app if you run every method in your app all the plots will be one over the other to solve this stream it has the concept of columns so you can fit multiple elements horizontal let's create two rows a row of five columns and a row of two columns you can call any number of streamlined widgets in the column context manager to sort them in and since we added the use container with on every plotly chart they should fit nicely in the small column you can control the rift of columns by using a list of length ratios instead stream it also accepts one level of column nesting so you can also divide the top row into two columns and then split the left one into four new columns and this is what you get after putting the right methods in the right colors there you can deploy the app and every of your colleagues can now upload their own Excel financial data to get their own dashboard without ever touching VBA macros again there are many ways to go further from making the loaded data frame editable to correct sales in real time from the app to custom plotly cross filtering that you can find about in this video I'll see you around bye

Channel: Fanilo Andrianasolo

Views: 54,485

Keywords: how to use streamlit, learn streamlit, python, python streamlit, python web app, streamlit, streamlit tips, streamlit tutorial, python dashboard, streamlit dashboard, streamlit project, dashboard

Id: yg0Y7w4AHhw

Length: 9min 40sec (580 seconds)

Published: Wed Aug 16 2023