How to build Interactive Excel Dashboard with Python - Dash

hi everybody welcome back to charming data we will learn all about analytic web apps in python so in this tutorial i'm going to show you how to take a simple excel sheet and build an interactive dashboard with this information so we're going to take pandas and plotly which are python libraries and we're going to build these pie charts where you can have slices and different um what do you call these hover tool tips and legends that you can turn on and off and we're going to build a bar graph and we're also going to build a histogram with these libraries in the part two of this tutorial i'm going to show you how to build an interactive dashboard using putting inside your dashboard a title a drop down and that every time the user changes the dropdown option this graph will change accordingly so this is done with dash which is also all in python and i'm teaching you this because i've built um some graphs before in in excel it's not too complicated you can go in here and build the graphs but i find that that once you know how to use python it becomes a lot simpler and you have many more options of graphs and things that you can do data visualization wise so if you go for example to the plotly page which is going to be under the video you can see all the different fundamentals and artificial intelligent graphs and basic graphs all these graphs and many more that you can do and you can have at your disposal once you know plotly so i'm going to teach you the basics of pandas and poly right now so you'll be able to expand on that and build more charts on your own if you want to jump to certain parts of the video under the video here just go into this video layout section and just click on any on any part you want to see if you want to jump back to it or see it again okay before we get started um this data is um data on video game sales over the last 35 years from different uh parts of the world and these represent millions so this is 41 million there's 29 million of all the different video games and the different types of video games that the world has come up with in the last 35 years all right let's get started i'm going to go into here erase this because i did this on my own to practice but now we'll show you how to do this so what you want to do is open your python ide sublime atom visual code visual studio code or pycharm i'm using pycharm and um open your terminal okay because we have to you have to install two things to be able to do this you have to install i have to do pip install dash let me make this bigger for you pip install dash and after you pip install dash it will take about five minutes then you pip install pandas okay these two libraries dash and pandas so you can close this once you have it installed in your ide and then we're going to start all right so the first thing you want to do is import dash the second thing we're going to import plotly dot express as px so we only can refer to it as px and then we're going to import pandas as pd so it's shorter okay now we're going to do some data exploration so let's make this section data exploration with pandas let's call it that and let us create our first pandas data frame our pandas data frame is going to be our excel sheet so it's excel sheet on steroids it's pretty pretty powerful we're going to call it data frame i'm going to call it df equals pd remember from the pd on top dot read csv and it's going to be read csv because this excel sheet is saved in a csv format and it's called vg sales so read this csv and what is the vg cell vg sales okay dot csv if it was excel we would just do instead of csv you would do excel okay and then you would put here dot excel all right so this is csv and now we have our pandas data frame now we can go ahead and print the first couple of rows let's print the first five rows like this save it and then just hit run and run the excel with python it's a little bit bigger so you can see okay so now you can see the first five rows um from zero all the way to four and several columns we see the rank column the video game column and the world sales column rank video grain and then the world sales column it's not going to show all of them instead of these you'll see like the three dot dots because there's only limited amount of space if you want to see certain columns you can actually do that so this is part of your data exploration phase so go to print df do dot i lock and in here open the parentheses open the brackets and you will see the first five rows again comma and then open another bracket and let's see column two column 3 column 5 and column 10. so if you print this let me hashtag this out to hashtag this out with pycharm is ctrl question mark and we'll only have this to run and now when we run this you'll see the platform column the year column publisher and world sales and these are respectively column number 2 3 5 and 10. okay now if you don't want to count the columns if you want to just see what they're called then you can do just copy world sales and instead of numbers put the string of world sales but you have to change this from i lock changes to lock okay it can't be i lock as for numbers lock is for strings now if you play it you'll see five rows of world sales perfect the next thing we're going to see is genre so let's do here we see on the excel sheet on our panda data frame there is many different categories of video games i don't know how many there are 16 000 rows here but i'm sure there's not 16 000 categories so let's count the number of categories we'll do print we use the n unique for that print df dot genre that's what it's called dot n u make and then open the parentheses so now if you save this and if you run it it will tell you how many unique categories 12 unique categories so now if we want to see them just copy this go under here paste and instead the end unique just say unique take out the end now play it again it'll give you all the names of all the categories that the pandas data frame has right these are all the categories we can do the same thing with year there are many years here but we don't know how many so let's do print jean instead of genre we'll print year right and then end unique well instead of number we'll just do we want to see all of them but we're going to sort them so they'll go in order from lowest to highest sorted run it and let's see how many is from 1980 all the way to 19 to 2016. okay so now that we've done a little bit of data exploration let's do a little bit of data visualization and that we're going to do with plotly so let's go in here let's copy this and let's do a new section and we'll call it data visualization with visualization with plotly okay so the first thing we're going to do is let's do a pie chart of the types of video games and the sales in japan so we'll do these two columns e and i um to do that all you have to do is um use right here we're going to call our our pie chart let's call it fig pie equal and then we'll just call the the pi px dot pi open parenthesis px comes from polyexpress and pi is what you need to create a pie chart how do you know this you know this because i told you but also because there are many graphs that i don't know that you can see by going into this link below the video you'll see high level interface and you can see all the different graphs that you can create with plotly express okay so if you want to create a violin you'll do violin and we are creating the pie chart clicking here in the pie chart and here you'll see all the different parameters that you need to use some of which in order to create the pie chart usually you only need three parameters the data frame and the the second one and the third one so here we have names and values in the data frame so let's use them and create this pie chart so the data frame is going to be r df because we this is our pandas data frame okay this is in pandas and then comma and then the name is another parameter and the names is the name of this column the genre column right because it's the category so you can just copy it and put it as a string don't don't forget to put it as a string and then the values is going to be the the value is usually the numbers right so the numbers is going to be j pan sales values not value values okay and now that we have our pie the pie was created all we want to do is to show it so let's show our pie and you just do show fake pie dot show save it run it and now is to generate a pie chart with all the different the legend has all the 12 different types of um video games and their sales over all the years this is taking all the years combined so you can see that 27 percent or role-playing video games were 27 percent of all japanese sales which was equal to 350 million sales 12 percent was action and then sports was 10 with 134 million sales let's say we want to see in a different country let's see in north america so we'll copy this paste let's hashtag this out so we don't have it twice and instead of japan we'll put north american copy paste save this and then run it again and let's see what we get with north american with north american we get um 19 the first category is action and then sports and then shooter shooter is 13 in third place of all sales of video game sales over the last 35 years just for reference shooter in japan is last so in america we really like games with guns okay um let's move on to um a different graph we just did the pie chart let's do a graph that is a histogram um or maybe let's do a graph that's a bar before before that let's do a bar graph um a bar graph is let's see how we create a bar graph you can go to the um same high level link below the video and look for the bar graph it's right here and look for all the parameters right so it's px dot bar and then all these parameters again we're only going to use the first three parameters data frame x and y and let's do that of the of the genre as well so we'll do um fig underscore uh we'll call it bar call this fig bar you can call it whatever you want px.bar and then we'll use exactly the same not exactly but we'll make this faster categories so instead of names we'll use x x is going to be the x axis is going to be the genre and instead of values we'll put y y axis is going to be north american sales so we that's it one line of code three parameters and we have our bar chart and now we want to show it on the on the page so we'll do fig bar dot show run it and now we'll see that in um we'll have all the different sales this is different colors because it breaks down over the years all the different sales and you see that action and shooter and sports have the highest bars in north american sales over the last 35 years let's see let's say instead of um genre instead of video game types let's see x-axis will be the year so we'll see the distribution over the years and when you want to see a distribution of a continuous value years you usually want to use a histogram so let's do exactly that let's hashtag this i copy this hashtag it out and now let's see histogram we'll call this fig hist we'll call this fig history show later and in the histogram i think it's called that's why i like pycharm but auto autocompletes everything for you or many things and histogram also has an x axis and a y axis so this we can leave exactly the same and we can run it and now we see that we have a histogram oops one second we don't want genre we said that we wanted year so let's do a year in north america and now we see over the years how in north america it really started the video games really started selling very well until 2010 and then it started um and then it started dropping to 2005 until 2015. um so great we built a pie chart a pie chart we built bar graphs and then we build a histogram now in part two of this tutorial i'm going to show you how to make this interactive so remember when i first showed you how we had the bar graph and the title we're going to build this and we're going to build together with a drop down this is done with dash okay to make an interactive dashboard in python you got to use dash which we imported here above this is going to be our last section data visualization was plotly and now we're going to do data um not data we'll put interactive graphing with dash interactive graphing with dash okay so to do drafted graphing the first thing you have to do is you have to import a few things and i'll explain these later but you have to import dash html components as html will be faster to write import dash core components as dcc and then from dash dependencies dot dependencies we're just going to import output and input because we don't need all the dependencies we'll just need a few things so we're just going to import outputted input i'll explain this later and usually you want to put this on top okay i'm going to leave this for now here but usually let's just move it you want to put it on top the libraries you want to import at the top let's erase this okay so now to start our our page layout or to start our app we have to do app equals dash dot dash to call our our dash and because we're using flask under the hood you just have to put underscore underscore name underscore underscore doesn't really matter why you don't really have to remember this you always use the same thing you're always going to write this so just copy paste from this code and then we're going to create the layout okay so the layout is going to be the app layout equals html dot div open the parentheses open up bracket enter and now anything that's going to go in here is going to be the layout so remember in the layout we need a few things we need the html the title we need a drop down component with several options and we need a place to put our graph so we need three different components so let's start with the first one the first component oops first component to build a title there's different ways to do it but i'm going to use html.h and if you're like what where is he getting this h1 from what is h1 i am taking this from this dash main page they have great documentation so going under the video and click on this main page the what's that and then go into dash core components and html components you see this right here the html components will give you all the html components in python so you can write that in python i'm using the h1 and we're going to use in the core components we're going to use a dcc graph and we're going to use the dcc drop down to create the drop down and the graph components okay so you can read more about this here so html1 and here we don't really need any parameters we're just going to put the direct title what is our title going to be is our title is going to be graph analysis with charming data so let's do that right here so now we have our title before we create the the drop down on the graph let's just start this app and see what it gives us so put if and this you don't have to remember either this is going to be the same for every single app that you create if unless you underscore name underscore underscore equals main i'm just going to score main on the squad discord um a lot of underscores run app run app run server app run server so this again you owe this and this part you only have to copy paste to every single app that you create so this inside is the only thing that changes so now you save this let's stop our other graph and let's start this one if you save it's going to give you a link you click on this link and this is your html this is your web page this is your link and this is your web page it's not on the world wide web yet it's just your web page and so far we have the title so what are we missing we're missing the drop down and we're missing the graph so let's put that in there right now the next thing you need you need comma don't forget this comma this this devilish comma i always forget about it and it always screws up my app i'm sorry a little bit about the noise i think it's my heating system working but i'm sure you can hear me very well so the next thing we want to create is the graph so let's create a dcc graph and open parenthesis and we're going to give it an id because most of our things that we need to interact with are going to need an id so let's give an id and we'll call it my graph and to create the graph you just have to put figure the figure parameter equals and this will take plotly express graphs so we're just going to put the plotly express graph that we want inside of here and the one that we want let's take the [Music] let's take the histogram so we'll take just this and we'll just copy paste this into here there we go if you can't see this let's do it easier so you can see it and now we have our histogram inside of here and the graph that ends here all right let's save this let's run it again refresh it and now we should be able to see when we refresh the page a title and a histogram all right now we need to add the drop down a drop down component with all the different options so the at the drop then we're going to add it here in between we'll do dcc drop down open parentheses don't forget the comma here at the very end let's give it an id that always need an id we'll call it uh we'll do a drop in of genre choices we'll do genre choice okay and then the next parameter we're going to use the options the options parameter is all the options that we're going to see inside the drop-down the options is always a list of dictionaries so a list of dictionaries we're just going to do this with the for loop so we're going to say for every every genre type every video game type um create an option inside the drop down so really what you can do is just copy paste from here for every df genre you need because you got to make sure that it's unique so because there's multiple you don't want to have 16 000 options you just wanna have several for every oops for x in all of these unique genres let's let's sort them so they're alphabetized sorted okay for x and sorted this genre unique we're going to put it's always you're always inside the options in the dictionary you're always going to have a label we'll call it x because it's going to be that genre and then you're going to have a value every single one of them is the same thing label and a value okay so we have our options and now we need our comma now we need our value the value that is going to be chosen by default when the page reloads we'll call that let's do action is going to be action i'm taking from here is the first value that's going to be chosen well let's do sports spurs we can do sports doesn't matter which one is the first one we'll just do sports is the first one that's chosen when the page reloads and that's it you can save this and you can refresh it and now we should that page should have also a drop down perfect and the sports is the first one that we load because our value was sports and we have the for loop right here of all the different options of the unique category so we only have 12 options in here from action all the way to sports now this is not interactive yet this if you change the drop down it's not doing anything it's not changing the graph this is just the layout title drop down and graph just goes inside the whole layout to make this interactive you need to use the callback so let me show you how to do that right now enter enter let us use the callback the callback has a callback decorator and a callback function the callback decorator is always going to look like this app.callback don't forget to use this um at sign at that callback open the parentheses and we're going to have an output output and we're going to have oops parentheses and we're going to have an input ah what's wrong with my spelling input okay this output input comes from here that we we um that we imported right i'll put an input and under the output input we're going to fill this in a sec under the output input we're going to have a the callback function so this is the callback decorator and this is the callback function define interactive graphing you can call it whatever you want talk about android graphing parentheses and then we're going to return something okay this is a basic structure of a callback that allows you to to make an interactive dashboard so the first thing we want to do is we want to take the in the the drop down we want to put inside the input because it's the input that is going to change what the graph is going we're going to take the input of the drop down and change the the graph which is the output okay so to do that all you have to do is say you don't need to use this but i'm going to show you for tutorial purposes component id the idea of the drop down is genre choice okay so we know what we're talking about right there on a choice and the property is going to be the component property is going to be the value because we're going to take the value of that drop down whatever that value is we're going to take that and since we have one value in here one input we're going to we have to put this inside here as an argument so let's call this value choice value of genre let's call it volume genre all right so now what we have to do is um we're going to say let's just print let's print the value that we see value let's print and see what we get okay whatever we see that's all we're going to say and then we're going to turn right now we're going to return an empty dictionary inside the output okay so output also has component id so we're going to return an empty dictionary inside the component id which is going to be is going to be the graph so it's going to be my graph is going to be the id and then the component property is going to be the figure because we're going to return something inside that figure okay so whatever we turn here this empty dictionary is going to return here which is going to return here okay right now empty dictionary doesn't mean anything but i printed this out so every time i choose a drop down i'm going to see something inside my terminal here let's refresh and now if i click on racing i'm going to see racing right here see if i click on fighting i'm going to see fighting down here perfect so this works what i want to do now is instead of returning an empty dictionary i actually want to return a figure okay so what i'm going to do is first of all i want to return the figure first of all what i want to do is i want to filter the data frame so only the data frame with this genre that was chosen fighting or racing of sports is is what appears only those rows appear and not the whole data frame so to do that i just have to make a copy always make a copy dff equals df where df genre equals the value that was chosen in the drop down right so we just did our our filtering of the data frame and now we only have these rows that um pertain to the drop down now that we have our data frame or new data from df now we can actually create our figure so let's create a figure here equals and let's create a bar figure px bar open parenthesis and the same thing we need a data frame let's just copy paste from above a data frame and let's do it over years over the years the apron is not going to be df it's going to be dff because it's the copy here and it's not going to be genre we're going to do year as the x-axis and we'll do north america let's do world sales instead of north america world sales which is one of the columns right here and now that i have a figure that was generated instead of an empty dictionary i can put a figure in here and remember this figure is going to go inside this output which is going to go inside of this figure right here so i don't need i have to take this out you have to take this figure out of here you can put here an empty dictionary if you want because something has to go inside of here if you put a figure in there it's static you want that to change so just take this out you can do this you can just take the whole thing out whatever you prefer so you save it and you run it again and now you'll see that when we refresh this we have sports and if it changes and all this is in world world sales over the years we want to see action we'll see the action generated up to 100 billio 100 million sales at a certain point adventure 25 million um racing generated up to like 770 million this is probably mario karts or whatever um so there you go i just taught you how to create an interactive interactive dashboard using pandas and plotly and dash if you want to learn more about how to create more complex dashboards i really recommend taking the watching the video above my head which is an introduction to dash that will teach you all about how i'm doing this and much more especially the layout and the callbacks to to get a better understanding of how to work with them i hope you enjoyed the video if you did hit the like button share it with others and subscribe below and turn on your notifications so every week you'll get notified of a new video that comes out on analytic web apps with python thank you very much and i'll see you next week

Channel: Charming Data

Views: 35,720

Rating: 4.9565215 out of 5

Keywords: How to build Interactive Excel Dashboards, interactive Excel dashboard from scratch using python, Applies to Excel 2007 onward, Automate Multiple Sheet Excel Reporting, how to automate your excel reports, get started using pandas and plotly, build excel dashboard with dash plotly, excel with pandas plotly dash, tutorial with python in dash, Plotly Dash Tutorial, Dash series for Python, web app dashboards, dash, ploty, dash plotly callback, Python Automation Tutorial, plotly dash

Id: acFOhdo_bxw

Length: 29min 40sec (1780 seconds)

Published: Sun Nov 01 2020