Today I got hands on my first data analytics project which was a healthcare dataset.
First I got the dataset and then I did the following steps.
- I understood the data first as It was a healthcare data there was given name of patients,number of patients,their disease,their medication,their blood groups etc.
2.Then I uploaded the data in the power query to clean the data in power query I found in the gender category there was 3 options given as male,female and m where I replaced value m as male.In the bloodtype feature I found very inconsistency where there was O- and O-ve both were present even every blood type was like that so I replaced the value O-ve to O- and I did the same process for all blood types.I also corrected the alphabetical writing of medical condition by transforming them to capitalize each word cause I found alphabetical inconsitency in them,then in billing amount I found an error in the data where I remember the number it was written as 6452O which I replaced it with 64520 cause O is an alphabet.Even in the admission type there was two same like options which was emergency and emer then I replaced the values emer with emergency.Even I added an conditional column which was an age bucket column where the condition was if the person is below 30 consider him/her as young and if below 60 consider as middle and if these two conditions are not fulfilling then consider as senior.And then my data got cleaned and prepared.
3.Then I did the analysis using pivot tables and here is the analysis:
1.Admission type analysis:In this analysis I analyse the no of patients admitted on the basis of elective,emergency and urgent.And I found highest no of patients were admitted in emergency in the hospital and even I analysed the average billing amount in the above 3 scenarios and found the patients in emergency had more average billing amount as compared to elective and urgent.
2.Age bucket anlysis:Here is analysed the no of patients based upon the age bucket factor and found middle aged people were the most who were admitted into the hospital but I also analysed the average billing amount among these age groups and found senior patients have highest average billing amount this means the cost of treating the seniors is bit high as compared to young and middle ages.Even I also used filter you can see in my excel sheet.
3.Insurance provider analysis:Here I analysed how many patients has took the medical insurance of the 5 companies Aetna,Blue cross,Cigna,Medicare and United healthcare and found most of the patients took the insurance medicare but the average billing amount of the patients of Cigna was highest.I also filtered the insurance patients on the basis of their medical condition and age bucket.
4.Medication analysis:Here I analysed the number of patients taking 5 different medications and found most patients were taking penicilin but found the average billing amount was higher for patient who was taking lipitor medication.I also used the filter on the basis of medical condition you can see it in my excel sheet.
5.Bloodtype analysis:Here I analysed the no of patients on the basis of bloodtype and found patients with AB- bloodtype were the highest.I also filtered the bloodtype count of patients on the basis of medical condition.
6.Gender analysis:Here I analysed the no of patients on the basis of male and female and found female were the most who were admitted into the hosiptal.I also used the filter of medical condition to find the number of males and females admitted with specific disease.Even I filtered it with age to determine no male and females in specific age groups.
7.Test result analysis:Here I analysed the no of patients on the basis of their results after treatment and found most of the patients was abnormal after treatment.I also filtered it on the basis of medication to know the condition of patients of specific diseases after treatment.
And in this way I finished my first excel project.
Top comments (0)