Cumulative Sum using R
Parag Verma
Introduction
In almost all business problems, the focus is always to look at key takeaways in any analyses.Most Academic disciplines teach a concept known as Pareto Principle which states that 80% of the effect comes from 20% of the causes(also read as cases). If we look at an analyses standpoint, it is always advisable to look at the LOW HANGIN FRUITS first. According to me, these low hanging fruits are established keeping Pareto Principle in mind.
The purpose of writing this blog is to introduce the concept of Cumulative sum in R that helps you deal with practical scenarios while working with datasets. Some of problems we face while handling data set are listed below:
- Create cumulative sum of Sales/Gross Margin/Net Cash flow/Cases around key Attributes such as COuntry, Product Category etc
- Identify groups/Combination that contribute to 80% of the Impact(Sales, Gross Margin etc)
- Identify Products that can help Sales Rep achieve Sales Target
- Quota Allocation exerise where quota is given based on relative contribution of each Product or Region
In this blog, we will look at how to proceed for each of the cases mentioned above
Installing the library: dplyr and tidyr
if(!require("dplyr")){
install.packages("dplyr")
}else{
library(dplyr)
}
if(!require("tidyr")){
install.packages("tidyr")
}else{
library(tidyr)
}Importing the dataset
For this exercise we will look at the Cybersecurity breaches reported to the US Department of Health and Human Services.There are 1151 records in the data set with 9 variables. Our aim is to calculate the Percentage of Cases reported at each Sate at an Entity Level.Here we will look at the concept of cumulative sum in R
# Ecdat library for importing the dataset
if(!require("Ecfun")){
install.packages("Ecfun",dependencies = T)
}else{
library(Ecfun)
}
if(!require("Ecdat")){
install.packages("Ecdat",dependencies = T)
}else{
library(Ecdat)
}
data(HHSCyberSecurityBreaches)
df<-HHSCyberSecurityBreaches
head(df[,1:3])%>%
knitr::kable()| Name.of.Covered.Entity | State | Covered.Entity.Type |
|---|---|---|
| Brooke Army Medical Center | TX | Healthcare Provider |
| Mid America Kidney Stone Association, LLC | MO | Healthcare Provider |
| Alaska Department of Health and Social Services | AK | Healthcare Provider |
| Health Services for Children with Special Needs, Inc. | DC | Health Plan |
| L. Douglas Carlson, M.D. | CA | Healthcare Provider |
| David I. Cohen, MD | CA | Healthcare Provider |
The names of the columns are:
- Name.of.Covered.Entity:A character vector identifying the organization involved in the breach.
- State: State abbreviation
- Covered.Entity.Type: A factor giving the organization type of the covered entity
- Individuals.Affected: An integer giving the number of humans whose records were compromised in the breach.
- Breach.Submission.Date
- Type.of.Breach: A factor giving one of 29 different combinations of 7 different breach types,
- Location.of.Breached.Information: A factor giving one of 47 different combinations of 8 different location categories: “Desktop Computer”, “Electronic Medical Record”, “Email”, “Laptop”, “Network Server”, “Other”, “Other Portable Electronic Device”, “Paper/Films”
- Business.Associate.Present:Logical = (Covered.Entity.Type == “Business Associate”)
- Web.Description: A character vector giving a narrative description of the incident.
Percentage Cases within Each State from Entity
df.interim<-df%>%
group_by(Covered.Entity.Type,State)%>%
summarise(Total_Cases=sum(Individuals.Affected))%>%
arrange(Covered.Entity.Type,desc(Total_Cases))%>%
ungroup()%>%
group_by(Covered.Entity.Type)%>%
mutate(Perc_Contribution=round(100*Total_Cases/sum(Total_Cases),1))
knitr::kable(df.interim%>%
head())| Covered.Entity.Type | State | Total_Cases | Perc_Contribution |
|---|---|---|---|
| Business Associate | VA | 5093217 | 22.7 |
| Business Associate | TN | 4547412 | 20.3 |
| Business Associate | NJ | 2964033 | 13.2 |
| Business Associate | TX | 2582619 | 11.5 |
| Business Associate | NY | 2033804 | 9.1 |
| Business Associate | PR | 1069166 | 4.8 |
Here we can see that the records are only related to Business Associate since I took head of the resultant data frame. Lets now look at top 6 records within each group
Top 5 records from each COnvered Entity Type
Top5<-df.interim%>%
mutate(Index=1:n())%>%
filter(Index<6)%>%
select(-Index)
knitr::kable(Top5)| Covered.Entity.Type | State | Total_Cases | Perc_Contribution |
|---|---|---|---|
| Business Associate | VA | 5093217 | 22.7 |
| Business Associate | TN | 4547412 | 20.3 |
| Business Associate | NJ | 2964033 | 13.2 |
| Business Associate | TX | 2582619 | 11.5 |
| Business Associate | NY | 2033804 | 9.1 |
| Health Plan | FL | 1240326 | 23.0 |
| Health Plan | TN | 1110698 | 20.6 |
| Health Plan | MT | 1062509 | 19.7 |
| Health Plan | TX | 568640 | 10.6 |
| Health Plan | NY | 390830 | 7.3 |
| Healthcare Clearing House | FL | 10000 | 56.3 |
| Healthcare Clearing House | SC | 4204 | 23.7 |
| Healthcare Clearing House | PA | 2300 | 13.0 |
| Healthcare Clearing House | WA | 1250 | 7.0 |
| Healthcare Provider | IL | 4335571 | 32.5 |
| Healthcare Provider | CA | 2098208 | 15.7 |
| Healthcare Provider | FL | 1583608 | 11.9 |
| Healthcare Provider | AL | 977995 | 7.3 |
| Healthcare Provider | GA | 491932 | 3.7 |
Lets now look at States contributing to 80% of the Grievances within each Entity Type
States Contributing to 80% of the Cases
I will break this into two steps.
- First we will calcualte the Cumulative Percentage
- Second we will filter out values less than 80%
Perc_80<-df.interim%>%
mutate(CumulativePerc_Contribution=cumsum(Perc_Contribution))%>% # First Step
filter(CumulativePerc_Contribution < 80) # Second Step
knitr::kable(Perc_80)| Covered.Entity.Type | State | Total_Cases | Perc_Contribution | CumulativePerc_Contribution |
|---|---|---|---|---|
| Business Associate | VA | 5093217 | 22.7 | 22.7 |
| Business Associate | TN | 4547412 | 20.3 | 43.0 |
| Business Associate | NJ | 2964033 | 13.2 | 56.2 |
| Business Associate | TX | 2582619 | 11.5 | 67.7 |
| Business Associate | NY | 2033804 | 9.1 | 76.8 |
| Health Plan | FL | 1240326 | 23.0 | 23.0 |
| Health Plan | TN | 1110698 | 20.6 | 43.6 |
| Health Plan | MT | 1062509 | 19.7 | 63.3 |
| Health Plan | TX | 568640 | 10.6 | 73.9 |
| Healthcare Clearing House | FL | 10000 | 56.3 | 56.3 |
| Healthcare Provider | IL | 4335571 | 32.5 | 32.5 |
| Healthcare Provider | CA | 2098208 | 15.7 | 48.2 |
| Healthcare Provider | FL | 1583608 | 11.9 | 60.1 |
| Healthcare Provider | AL | 977995 | 7.3 | 67.4 |
| Healthcare Provider | GA | 491932 | 3.7 | 71.1 |
| Healthcare Provider | TN | 467261 | 3.5 | 74.6 |
| Healthcare Provider | TX | 341041 | 2.6 | 77.2 |
| Healthcare Provider | NY | 334068 | 2.5 | 79.7 |
Final Comments
We have seen how we can use group_by and cumsum function to get an idea of important things at an overall level.This helps us gain an understanding of States driving the numbers at Entity Level
Link to Previous R Blogs
List of Datasets for Practise
https://hofmann.public.iastate.edu/data_in_r_sortable.html
https://vincentarelbundock.github.io/Rdatasets/datasets.html
No comments:
Post a Comment