Collaborate on Generate Invoice from Excel Data for Healthcare with Ease Using airSlate SignNow

Generate invoice from excel data for Healthcare

hey what's going on everybody i want to do a quick video today showing a real world data analytics example based on a data set from the website kaggle the data set shows hospitalization data for a children's hospital in wisconsin it is real live data available on the kaggle website i'll put a link in the description here are the goals of the project that kaggle has listed there are five or six analytics objectives i want to show you exactly how i would go through and analyze this in excel i know some people are going to want to use python r other stats programs you can do most of this in excel perfectly fine i want to run through exactly how i would do that so let's jump in quickly talking about the data set here we have the age of the patient and it's going to be 0 to 17 years old since that's a children's hospital let's jump down there are 500 records in the data set there is a binary binary sex indicator so female anything with a 1 is a female a 0 is a male we have length of stay in days the race of the patient is a an indicator listing from one to six denoting the race we have the total charges of the patient stay and the diagnosis code associated with why they're at the hospital stub toe heart attack cancer stitches who knows i don't know what these codes are but they're in the data set we can still analyze them that's fine all right let's jump right in let's do number one here to record the patient statistics the agency wants to find the age category of people who frequent the hospital and have the maximum expenditure all right that's kind of a two-parter let's do a quick pivot table that shows the age the most common age group of patients that come to this hospital so i did control a to select the data set insert pivot table let's put it on a new worksheet let's pull in age and then let's do a count of age in the values i'm going to change value field setting to count and what i like to do is just highlight the values portion go to home conditional formatting data bars and we can get a fill on each cell that corresponds to the value so look at this this easily tells you that most patients at this hospital were age zero so less than one years of age and then it kind of ramps up again as you get into your teenage and later teenage years okay cool that's simple so now let's look at total charges by age and i want that to be a sum currency no decimal okay and it's no surprise that this age group has the highest total charges because they are they predominate the hospital so let's pull in total charges again but let's look at the average charge by age group currency no decimals and again let's put on those same data bars up here data bars value okay this is cool so now you can see just on an average charge basis so every three-year-old that came to this hospital they had an average charge of 10 grand every 9 year old that came to this hospital they had an average charge of 10 grand irregardless of how many the volume of patients in that category okay cool so that answers question one let's jump in question two and ex i did not write these questions so the grammar um is a little funny in order of severity of the diagnosis and treatments and to find out the expensive treatments the agency wants to find the diagnosis related group that has the maximum hospitalization and expenditure all right so what i'm hearing from that is we need a table that lists diagnosis group and we want maximum hospitalization that means length of stay and then the associate expenditure associated with that all right cool we can do that let's grab this same pivot table i'm just going to highlight it and make a copy down below to work from i'm going to clear out my old fields by just dragging them off now i have a blank pivot table to work from let's pull in diagnosis code it automatically orders it from low to high let's pull in length of stay whoop so that's average length of stay i want for each diagnosis code i want two decimals let's count up the length of stay actually to make it a little more clear let's do a count of diagnosis code first and again i want to get an understanding of the volume of patients that come in for each code so as i roll down boom diagnosis code 640 you have a ton of patience uh these other diagnosis codes you know not so much all right and let's pull in total charge and make let's leave that sum currency no decimal places and i'm pulling total charges again i want to because i want to make that an average currency two decimal places okay all right so left to right what do i have the diagnosis code how many patients came in their length of stay their total charges and then the average of those charges here we only had one patient so its total and average charge is the same so for example when we have this huge high volume of patients on this one diagnosis code of course the total charges are going to be higher because you know you're billing a ton of people so i'm more interested now in this average charge and the length of stay and to better visualize this data and the relationship between those two i want to create a scatter plot for those two variables so i'm going to make a scratch table off to the side that i'm going to use as the basis for my scatter plot so equal i want length of stay and let me drag down and just pull in all my length of stay data and here i want average total charges and let's make that currency okay so we have the length of stay the average total charge and of course we're going to link that to the diagnosis code so check this out here's what i'm talking about throwing this in a scatter plot and what a big help this is insert in my charts menu a scatter plot is right here scatter all right let's open this up in the way i like to name my scatter plot is average charge and i i always put the word average and i put the word verse los length of stay i put one variable verse the other because it's showing the relationship between the two okay boom it's jumping out of me right away whatever this diagnosis code is right here check this out the average length of stay is relatively short but it has a high average cost for that that service so anything up in this upper left quadrant is going to be high cost low length of stay so you've got one right here you got a couple right here and you might want to look at like this cluster right here those are the ones that jump out at me on the other end of the spectrum we have very high length of stay this diagnosis code people sit in the hospital 42 days or so and they get out paying 30 grand it's still a lot of money but for 40 days of service that that's nothing like this this is you're in the hospital seven days and you're paying 50 grand for this particular diagnosis code this illness all right cool let's jump on to the next one well let me back up to property utilize the cost the agency has to analyze the severity of hospital costs by age and gender for proper allocation of resources okay so we're going to be looking at charges by age by gender okay and i'm just going to keep copying pivot tables down and start a new one so let's copy this one start a new one down here i'm just going to clear out all my fields start fresh let's pull in gender or that female binary variable so we have zero are the males one are the females let's pull an age below that in the rose field so now we have a sub classification we have all the males which are code zero here's all the male ages and then the females and here are the female ages and then let's pull in charges twice i want to look at total charges as a currency and by that i mean sum total and then i want to look at the average charge by age by sex so average currency here okay so check this out we have the we have males of this age here are the total charges they received what i'm going to do is highlight that with conditional formatting color scales and i'm going to make the higher charges red and the lower charges blue now i'm going to do the same thing now but this is on an average charge basis kind of make these colors pop a little more same with female and you need to conditionally format each of these quadrants on its own so they don't it only colors itself relative to other values in this quadrant and not if i was to just highlight this whole thing in conditional format the red and blues would be relative to all the values in this table and we don't want that okay so let's check this out on a total charge basis across both sexes male and female that age zero is is the highest and we know why there were 267 patients of that age so it's no it's no surprise they would have the highest charges but let's look now going to average charge so for males the highest average charge was age 3 years old at 11 grand however for females it was five-year-olds who paid roughly 10 grand and on a total basis males pay 3 grand in average charges and females pay 2 500 so a little bit less in average charges so that's interesting that gives you your splits um pretty cool stuff let's move on all right let's move on to the next question the length of stay is crucial is a crucial factor for inpatients the agency wants to find if the length of stay can be predicted from age gender and race all right what they're talking here is multiple regression we can do that in excel no problem but first you need to enable the analysis tool pack in your version of excel here's what the tool pack looks like and you will just simply go to file options enable add-ins pick the analysis tool pack and you will get this option in your data ribbon if you need more info on that google it it's out there it's takes a minute to do really quick so to feed these data into a regression model we need to orient them a little bit differently so let me create some rows makes make a runway all right so we want to predict length of stay so let's pull that one in on the on the left most as a function of age gender and race so age that one's okay gender we have male female split that's fine and so for race if you remember they have the data set coded one through six each representing a particular race now those are just random assignments of variables when we're looking at regression modeling we don't need like the number one is not greater than the number two is not greater than three or four or five uh regarding this variable so we need to code it out into a dummy variable to make it work so what we're gonna have to do is say race one race two race three it's four that would be raise five and erase six make some room and the way we create a dummy variable is pretty easy so it's just equals if the race equals one because we're on race one then give us a one if not zero equals if race for this record equals two then one if not zero so we wanted to just co give us a one if race corresponds to this upper category if not give us a two so let me um just quickly fill out the rest of these oh all right so there we have our binary binary variable for race let me just whoop grab these columns clean them up and as i drag them down that's going to build the data feed that we're going to pass into the regression model i want this variable what we're going to predict length of stay on the far left and what are we going to predict it as a function of i want all those variables next to each other next to length of stay so let's pull this down now i'm just going to copy these cell references all the way to the bottom of the table whoops i need to select only one row all right there we got our feed and see as race is category four we get a one for category four that's correct that is our dummy variable series all right so let's go data analysis regression let's clear out any previous references input y range all right what is the dependent variable what do we want to predict we want to predict length of stay what do we want to predict it as a function of what is what are the independent variables that are all all of these okay labels yes our header row has labels let's output this in cell x26 that's fine click ok and what you'll get is is this table of stat outputs about the regression model now if you remember from class we want to look at the p-value for each variable these p-values need to be below 5 in order for us to say that this variable is significant and has predictive value in this model and as i'm looking through these none of them are are even close to 0.05 maybe age is a somewhat decent predictor of length of stay but the rest of these are just awful this what i would conclude from this is no you cannot predict length of stay from a kind of conglomeration of all these variables in tandem you know you could rerun this using just only age under the hypothesis that you know age is the determining factor the only thing that's significant in length of stay i mean maybe you can do sex after that that's still a high p value but yeah this isn't isn't looking too hot all right let's jump on to the next question to perform a complete analysis an agency wants to find a variable that mainly affects hospital costs all right that's another regression model uh question so let me just clear out my previous results and mainly affects hospital costs okay cool so we want hospital cost as our dependent variable or what we're predicting so let's just add on to our scratch sheet total charges and we have that as a function of length of stay age sex race okay looks like that's all of them so let's copy down total charges all right we're going to estimate another regression model using this scratch table we want to predict total charges as a function of all these other variables let's do it data data analysis regression let's clear that out clear that so input y what do we want to predict the total charge we want to predict it as a function of all these other variables yes we have labels let's output it right here click ok all right here are here's the stats again looking at p value bad bad bad bad sex is good that's a good predictor age is good and length of stay is a good a good predictor again predictor of total charge that the patient is likely to incur okay if we were only looking at these three significant statistically significant variables let's bold them the way you would read these coefficients is this and this gets to their question which variable mainly affects hospital costs okay think of it like this you're gonna go in this hospital and come out and you're gonna have some charge you know couple grand whatever it is this intercept is the base charge that someone's gonna have once they walk in that hospital your average person is going to be charged 679 bucks before we even consider how long they were there their age their gender their race things like that and then we start building on there okay then they got length of stay is this seven we interpret that as dollars 742 dollars increase per day of length of stay so you walk in you you get you're going to be paying 679 679 bucks for every additional day you stay in there your cost is going to go up by 742 dollars all right age is the next one so you have this charge profile so far age is 114 bucks increase per year of age you know so as you increase in age per year you're going to be charged 114 more dollars again that's based on this data set that we have from this wisconsin hospital and then finally we have uh sex so if you're a female you will recharge on average a thousand dollars less than if you are a male again using this data set in this hospital just within this confine of of time that we're analyzing so that is um less if you are a female and this is just called the intercept that is your starting point to to look at these charges as they all build on each other so this gets to their question of what is the variables that affect charge the most of length of stay age sex and finally race and after looking at which variables are statistically significant this is how you would walk through explaining these all right guys we got to the end that was the last question i know this is a bit lengthy this touched on some stats stuff that is a bit out of the scope of excel nonetheless i just wanted to show you how i would attack these questions and this data set using strictly excel again people might want to use r python stata spss whatever other program my goal i wanted to show how we would check this out in excel excel is a gold mine you're sitting on already super powerful super uh common to all to all workers all businesses so check it out try it on your own see what you think shoot me any questions stay tuned hope you enjoyed