My Workflow for Building any Streamlit Dashboard Project
Vložit
- čas přidán 5. 06. 2024
- Tired of manipulating Excel dashboards with complex VBA macros? In this tutorial, you'll discover a comprehensive workflow I use to build most of my Streamlit web dashboards with SQL.
It involves three key steps:
- How I load an Excel workbook into a Pandas DataFrame through a file upload widget, and display it into a Streamlit app with live-reloading
- How I format the dataframe with SQL through DuckDB to build Plotly interactive plots
- How I build the Streamlit app with horizontal layout, sidebar and expanders for easy reading
By the end of this video, you'll be equipped with the skills to quickly recreate Excel dashboards within Python web apps. No more struggling with complex VBA macros - empower yourself to generate interactive and visually appealing dashboards for financial data analysis.
📧 Want a free, slower paced live coding of this tutorial? Sign up to my email list to be alerted first, and keep up to date with the latest news, tutorials and resources about Streamlit & friends ➡ andfanilo-newsletter.streamli...
💰 Buy me a coffee to stay awake while editing www.buymeacoffee.com/andfanilo
🗣️ Find my socials on andfanilo.com
👉 Links
- Source code + Sample Data: github.com/andfanilo/social-m...
My tools (Affiliate links to support me!)
- 📷 Main Camera - amzn.to/3QvTosB
- 📷 Main Lens - amzn.to/3svYCwq
- 🎙️ Microphone - amzn.to/47l5ewA
- 🎵 Music & Sound Effects - www.epidemicsound.com/referra...
- 📕 Streamlit Official Book - amzn.to/3QuRPwl
00:00 Dashboard Mockup
00:38 Loading Data
03:57 Transforming Data
05:28 Visualizing Data
______
🪶 ...unexpected life events made me take a break. I hope CZcams keeps momentum even after a one month pause.
⚠️ Disclaimer: This video is not sponsored, I receive no compensation by any brand quoted in this video. Views are my own and do not represent my employer's.
Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide I may receive a small commission. Thank you for supporting my channel so I can continue providing you with free content!
#streamlit #python #datascience #dataapps
![Eminem - Houdini [Official Music Video]](http://i.ytimg.com/vi/22tVWwmTie8/mqdefault.jpg)
Still taking questions for my 5k AMA :) andfaniloama.streamlit.app/
This was unexpectedly entertaining. Great job Fanilo, thank you.
Thank you for the support 🙂 looking forward to your apps!
Very cool looking dashboard, Fanilo! *Great job!* 🎉
Thanks a lot 🙂 I kinda had fun solving this ahah
Wow this video is excellent. Great pace focusing on a real and complex use case
Ph wow thank you for the kind words, very appreciated 🙂
Man, you are amazing! Thank you very much for the guide, it was pretty useful and the video flows so nicely cause of the editing and the way you speak that learning from it was just smooth
Thanks for the feedback, it's really appreciated 🥹 I'll keep doing more, so hope to see you around!
You can change the playback speed to 0.75 by clicking on the settings
Wow, so many solutions in this video!
😆 I admit this is jam-packed with small nuggets ahah, I could probably make a 2h course out of it 🫠
Thanks for your educational video, very well done!
Thanks for watching, hope to see you around again :)
Great tutorial and sharing. I like your style of sharing alot
Thank you, it means a lot to me 🥹 I'll keep finetuning my style over the coming months so I hope to keep seeing you around
very clear and engaging! Subscribed.
Welcome aboard! Thanks for the support, very appreciated :D
Thank you Fanilo!
Thanks for watching, hope to see you on the next one :)
Thank you very much, as always!
Thanks again for the support :D see you around!
Excellent i love it.
Thanks for watching! If didn’t yet, check out my latest video too, it is another way of doing Google sheets Streamlit dashboard but in the same video style 😁
Great job dude 🎉
Thanks for the support, very grateful 😁
Great video!
Thanks for the support 🤗 hope to see you on the next one!
Thank You, Brah!
Thanks for watching, hope you'll be around for the next one :)
Really great video 😊. (just specify pip install pandas==2.0.3 for the moment)
Thank you so much !
Thank you for watching, see you on the next one!
Great video 🙌
Thank you :D I guess I have to plan a Panel version of this video one day 😁
Yes pls @@andfanilo
Thanks for such a great video. How to hide code from user of the app?
I understand nothing but it is so interesting!😃
Thanks for watching for the entertainment then 😁 how did you end up watching this I wonder??
@@andfanilo I am learning python, but I am at "scissors rock paper" code stage of learning 🤣 However, I hope in the future I will be able to prepare KPI dashboards with python just like you. Keep up the great job. Your new subscriber.
Daria para carregar o arquivo Excel no próprio dashboard e no código o Pandas AI ? Valeu 👍
Great video!!! what extension are you using to make your local host show in vs code?
Thank you for the support :D
Ah, actually my localhost is not embedded in VSCode, they are put side-by-side but I also configured VSCode so that the top bar matches the top bar height of the Streamlit app 🙂 it's a neat trick eheh (it's this config: gist.github.com/andfanilo/ae99aed9c454a46c1dbfe6a0b9fa0012 )
Hi Fanilo, very cool stuff! How do you share your apps with your colleagues? I’m trying to create streamlit apps for my colleagues but we haven’t found an optimal way to share our apps with others so far
Thanks for the support :)
I don't have a single solution, deployment I find is a case by case scenario depending on what your IT department authorizes:
- Can you build/deploy a Docker container in a hosted internal PaaS/IaaS environment? That would be the easiest way. If there is an internal Docker registry, your colleagues may even be able to pull your Docker image to run locally instead of connecting to your deployed instance.
- Otherwise, you can ask for a small dedicated server with Python installed and host your scripts there. The only problems you may encounter are whether this server has access to the Internet for a limited time to pip install the packages (some companies may ask for a full audit of those packages and then a copy of those to an internal Pypi, another question to ask), and eventual corporate firewall blocking Streamlit ports for your colleagues trying to connect to the server's IP, that would be conversations to have with IT.
Streamlit is "just" a long-running Tornado webserver, IT may have procedures to operate those (and maybe they even have load-balancer / reverse proxy recommendations in front of the Streamlit Tornado webserver for authentication github.com/andfanilo/streamlit-nginx-basicauth and caching www.tornadoweb.org/en/stable/guide/running.html#running-behind-a-load-balancer . I prefer that to managing authentication yourself at Streamlit level using a component like Streamlit-Authenticator, though you can do that for smaller-scale projects)
- I do have a video for using stlite to build an executable from a Streamlit app, but it still comes with a lot of constraints like no access to filesystem or not all Python dependencies being Pyodide-ready...I personally think of it as a last-resort solution. If your company has a hosted Gitlab, you may even use stlite to host a small Streamlit app in a Gitlab pages website ahah
- If nothing internal works, Streamlit Cloud or Huggingface Spaces to show it works and then long internal discussions 🙃
In my company, for some projects I have access to a small server and IT policies decide who has access to the IP of the server to connect to the Streamlit app. But it's not always the case...on some projects we can't have a server to share and I put instructions to my colleagues on installing Python/Conda/Streamlit to run the app on their workstations 🥲 which can be a pain
Hope it helps brainstorm solutions for you
Awesome
Thanks for watching, hope to keep seeing you around :)
Nice
Thanks for watching :)
Do you have any idea how i could host a dashboard like this internally in work for colleagues to use? All tutorials show how to deploy it to external hosts but i dont want to deal with that security headache.
Thats simple! Ask ur IT department to host a server. Then, create a .bat file that runs the streamlit run command in the terminal. Lastly, create a .vbs file that runs the .bat headless. Run the .vbs file. This way, a server is running the streamlit application on the network it is connected on. The webapp can be accessed via the IP provided by the terminal. Happy Streamlit-ing😄
@@bartgerritsen11199 Ok thanks. I hope IT will understand your instructions because I don't haha.. Also one more thing - Can I host multiple apps on that same server?
@@soundbeans Yes. However, a streamlit app may consume a lot of memory if it is not coded efficiently, so this is something you should consider before deploying the apps. What i usually do is deploying 1 app with multiple pages. Then i’ll add authentication to each page. This works well for me.
Thanks for chiming in Bart!
I will just add some worst-case scenarios I have encountered with IT policies:
- they may have corporate firewall rules that block ports like Streamlit's 8501, you may have to ask to open them for internal usage or configure your Streamlit app to use port 80 instead
- some prevent hosted servers from having access to the Internet, so you wouldn't be able to pip install anything. You'll have to ask for limited Internet access through a corporate proxy, they should know what to do.
- this one is extreme and I would advise not to bring it out yourself ahah...hopefully they don't ask you to audit the Python packages and then host them on an internal Pypi so that you can only pip install audited packages instead of installing from the Internet, I have seen this on very sensitive projects and I hope it doesn't happen to you because that's a painful tricky security measure to abide to =)
Generally it's just a long conversation to have with IT but as long as you tell them Streamlit is a long-running Python webserver (in Tornado) to which you want your colleagues to access to through an IP, or it in another company webpage, they should be able to guide you.
You are a god
Thanks for watching :) see you on the next video!
I am a big fan of your st_echarts component, but you usually prefer plotly?
Hello! Yeah, I'm just more used to Plotly so I naturally go for it when I'm in a rush 😅 maybe when I have more time for a project I'd give ECharts a go
well done ! one question : is it possible to reproduce the frames arround every chart of the original dashboard and center the charts in a way they look very well positioned like the original ones with streamlit/plotly ?
Hey!
Hmmm I haven't tried, but I'd say, with some crazy CSS Markdown trick maybe (look through my CSS and IFrame hack videos)...unfortunately I tried using the CSS at local Plotly level and it didn't seem to work :/ you really need to style the div container of the Plotly graph, not the plotly graph itself
Honestly, if you're looking to do an exact replica, a more flexible library like NiceGUI/Dash or going FastAPI+Plotly.js+a CSS framework would be way easier IMO. That's another video I'd like to make one day, not being scared to go from Streamlit to a JS/CSS framework
Have a nice day!
thx. do you have a best initial virtual env for streamlit ? please create a video for setup with poetry
I'm usually running a conda environments per Streamlit project (docs.streamlit.io/get-started/installation/anaconda-distribution but using Anaconda prompt command line). docs.streamlit.io/get-started/installation/command-line should be helpful too
I've heard good things about poetry and hatch, will add to my list of things to maybe make a video about one day, thanks for sharing!
I seem to get 2 clickable icons at the top-right of the gauge. 1 is a camera icon and other is a chart icon. How can I get rid of them?
some browsers always show and some only when you hover over. I tried hovermode=False on layout_update but no joy.
thoughts?
Hey! From memory if I understood correctly (can't test right now) it should be a config to add to the plotly Figure call, like in plotly.com/python/configuration-options/#preventing-the-modebar-from-appearing
LMK if it works
@@andfanilo thanks. close. I am using st.plotly_chart (as shown in your video) and not figure.show (used in the link above). so not sure how to define and pass the conf to a function. maybe a newbie question.
Oh yeah, st.plotly_chart has a **kwargs argument, which means any keyword argument is passed by streamlit into figure.show, so you can add config=config to the st.plotly_chart call too
```
config = {'displayModeBar': False }
st.plotly_chart(fig, config=config)
```
@@andfanilothat worked great! Thanks. is there a list of other config parameters I can use/set?
the gauges seem to have a big white space below the gauge. is this a st.column or st.container or a st.plotly thing? I tried looking at height with no luck. thoughts?
thanks again!
I love you
how could MathJax be integrated into Python by using streamlit to display LaTex as mathematical expressions in a web application?
I don't know a lot of MathJax. Have you tried docs.streamlit.io/library/api-reference/text/st.latex ? Or Markdown with LaTeX expressions, by wrapping them in "$" or "$$" (the "$$" must be on their own lines).
I thought most basic features of MathJax worked in there, but maybe for the more complex ones you'll have to use components.html docs.streamlit.io/library/components/components-api#stcomponentsv1html , inject the mathjax and use it in the same block (the following is not tested, it's just a draft I expect to be a good starting point)
```
components.html(f'''
mathjax code...
''')
```
can you please tell me what a sample xlsx looks like with a link to it?
Sorry, I forgot to include it 😅 as soon as my internet comes back I’ll push the file on the GitHub link in the description!
EDIT: sample data was pushed to Github repo: github.com/andfanilo/social-media-tutorials/tree/master/20230816-stdashboard
@@andfanilo thank you so much!
Very nice. but your code have some trouble with my environment. "ModuleNotFoundError: No module named 'duckdb'". Surely, I have install duckdb as 'pip install duckdb' and installed sucessfully.
Show me how fix the problem...Thank you...
I solved the problem. Thank you...^^
Nice! What was the issue?
can you hosting it
Could try on streamlit.io/cloud
All I heard were animals like penguins and pandas... (Joke aside, as a very interested newbie, I love your videos)
Ahahah yeah welcome to the Python Zoo 😁 hope you have fun!
Your editing is too fast and not enough pauses. Your words may be informative but it’s too dense for me to absorb without effort to undo that editing on my side.
Hello, thanks for the feedback.
You can still go grab the source code if you want, or you’ll find other good tutorials on CZcams that are slower paced (45mn-1h) with no editing that may fit your learning style better
Have a nice day!
Disagree. The pace is excellent covering the right topics at the right moment. I am very impressed
I feel that too but i mostly binge watch Fanilo for information and ideas, when I find something interesting, I deep dive into it.
I'm pretty sure Fanilo does this on purpose to keep the video lenght reasonable
BinderException: Binder Error: Referenced column "Year" not found in FROM clause! Candidate bindings: "df.YEAR_ID"