Part 1

1.

(2.5 pts) Create a dataframe with 4 variables. The 4 variables should be doctor_type, doctor_lastname, location, AVG_Rating . The variable doctor_type should contain 4 inputs (PCP, Psychiatrist, Surgeon, Anesthesia) The variable doctor_lastname should contain 4 inputs (Smith, Dame, Jones, Zayas) The variable location should contain 4 inputs (MA, ME, NH, VT) The variable AVG_Rating should contain 4 inputs (7,9,8,9) Print the dataframe.

rm(list=ls())
# dataframe with four variables 
data<-data.frame(
  doctor_type=c("PCP","Psychiatrist","Surgeon","Anesthesia"),
  doctor_lastname=c("Smith","Dame","Jones","Zayas"),
  location=c("MA","ME","NH","VT"),
  AVG_rating=c(7,9,8,9)
)
# printing the dataframe
data

2.

(2.5 pts) Using the dataframe above… Select row 1 in column 2, what was selected? Select rows 2 through 4, what was selected? Select the last column, what was selected?

#selecting row 1 in column 2
data[1,2]
## [1] "Smith"

Smith is selected

#selecting rows 2 through 4
data[2:4,]

The above rows were selected.

# selecting the last column
data[,4]
## [1] 7 9 8 9

Last column AVG_rating were selected

3.

(2.5pts) Using the dataset Mtcars create a scatterplot showing the relations between any two variables of your choosing. Explain why you picked those variables. Each point should also be based on a color scheme of your choosing.

library(ggplot2)
data(mtcars)
# drawing the plot
ggplot(mtcars,aes(x=wt,y=mpg,color=factor(cyl)))+geom_point()+labs(x="weight(wt)",y="Miles/gallon(mpg)",caption="Relationship between mpg and wt")

We have picked weight and mpg variables, so that we could find the relationship between both these variables and they have been colored by the number of cylinders.We want to analyse how the mpg variable varies depending on the weight and number of cylinders. We can see that as the weight increases the mpg decreases no matter how many cylinders they have.Weight(wt) and mpg have a negative relationship

4.

(2.5pts) Using the dataset MTcars perform a summary statistic on the dataset and calculate the pearson coefficient of the correlation R picking two variables of choice. Explain why you picked those variables and explain the purpose of a pearson coefficient.

# performing summary statistics on the data
summary(mtcars)
##       mpg             cyl             disp             hp       
##  Min.   :10.40   Min.   :4.000   Min.   : 71.1   Min.   : 52.0  
##  1st Qu.:15.43   1st Qu.:4.000   1st Qu.:120.8   1st Qu.: 96.5  
##  Median :19.20   Median :6.000   Median :196.3   Median :123.0  
##  Mean   :20.09   Mean   :6.188   Mean   :230.7   Mean   :146.7  
##  3rd Qu.:22.80   3rd Qu.:8.000   3rd Qu.:326.0   3rd Qu.:180.0  
##  Max.   :33.90   Max.   :8.000   Max.   :472.0   Max.   :335.0  
##       drat             wt             qsec             vs        
##  Min.   :2.760   Min.   :1.513   Min.   :14.50   Min.   :0.0000  
##  1st Qu.:3.080   1st Qu.:2.581   1st Qu.:16.89   1st Qu.:0.0000  
##  Median :3.695   Median :3.325   Median :17.71   Median :0.0000  
##  Mean   :3.597   Mean   :3.217   Mean   :17.85   Mean   :0.4375  
##  3rd Qu.:3.920   3rd Qu.:3.610   3rd Qu.:18.90   3rd Qu.:1.0000  
##  Max.   :4.930   Max.   :5.424   Max.   :22.90   Max.   :1.0000  
##        am              gear            carb      
##  Min.   :0.0000   Min.   :3.000   Min.   :1.000  
##  1st Qu.:0.0000   1st Qu.:3.000   1st Qu.:2.000  
##  Median :0.0000   Median :4.000   Median :2.000  
##  Mean   :0.4062   Mean   :3.688   Mean   :2.812  
##  3rd Qu.:1.0000   3rd Qu.:4.000   3rd Qu.:4.000  
##  Max.   :1.0000   Max.   :5.000   Max.   :8.000
# calculating the pearson coefficient 
cor(x=mtcars$wt,y=mtcars$mpg,method="pearson")
## [1] -0.8676594

We have chosen these variables to check if our analysis from the above graph is correct or not. There is a strong negative linear relationship between the two variables, as indicated by the Pearson correlation coefficient of -0.8676594. This implies that the other variable tends to decrease as one variable increases and vice versa.The purpose of pearson coefficient is a way to measure linear correlation. The values are between -1 and 1.Positive values indicate positive linear relationship and negative values indicate negative linear relationship.

Part 2

1.

(5 pts) Load the data

library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ lubridate 1.9.2     ✔ tibble    3.2.1
## ✔ purrr     1.0.2     ✔ tidyr     1.3.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
# loading the data
substance_abuse<-read.csv("substance.csv")

2.

(10 pts) Evaluate the dataset to determine what data preparation steps are needed and perform them. At a minimum, ensure that you discuss the distribution of the data, outliers and prepare any helpful summary statistics to support your analysis.

# inspecting the data
# finding the dimensions of the dataset
dim(substance_abuse)
## [1] 99367     7
# determining the structure of the dataset
str(substance_abuse)
## 'data.frame':    99367 obs. of  7 variables:
##  $ Year                      : int  2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 ...
##  $ County.of.Program.Location: chr  "Albany" "Albany" "Albany" "Albany" ...
##  $ Program.Category          : chr  "Crisis" "Crisis" "Crisis" "Crisis" ...
##  $ Service.Type              : chr  "Medical Managed Detoxification" "Medical Managed Detoxification" "Medical Managed Detoxification" "Medical Managed Detoxification" ...
##  $ Age.Group                 : chr  "Under 18" "18 through 24" "18 through 24" "18 through 24" ...
##  $ Primary.Substance.Group   : chr  "Heroin" "All Others" "Other Opioids" "Heroin" ...
##  $ Admissions                : int  4 2 6 132 35 8 1 11 276 135 ...
# summary statistics of the dataset
summary(substance_abuse)
##       Year      County.of.Program.Location Program.Category  
##  Min.   :2007   Length:99367               Length:99367      
##  1st Qu.:2010   Class :character           Class :character  
##  Median :2014   Mode  :character           Mode  :character  
##  Mean   :2014                                                
##  3rd Qu.:2018                                                
##  Max.   :2021                                                
##  Service.Type        Age.Group         Primary.Substance.Group
##  Length:99367       Length:99367       Length:99367           
##  Class :character   Class :character   Class :character       
##  Mode  :character   Mode  :character   Mode  :character       
##                                                               
##                                                               
##                                                               
##    Admissions     
##  Min.   :   1.00  
##  1st Qu.:   2.00  
##  Median :   8.00  
##  Mean   :  41.91  
##  3rd Qu.:  28.00  
##  Max.   :2861.00
# Calculate mean and standard deviation of admissions column
mean_admissions <- mean(substance_abuse$Admissions)
std_dev_admissions <- sd(substance_abuse$Admissions)
md_admissions<-median(substance_abuse$Admissions)
print(paste("Mean:", mean_admissions))
## [1] "Mean: 41.9138647639558"
print(paste("Standard Deviation:", std_dev_admissions))
## [1] "Standard Deviation: 122.875779616837"
print(paste("Median:", md_admissions))
## [1] "Median: 8"

The admissions appear to have a right-skewed distribution, as indicated by the median being significantly lower than the mean. The median, which is the midway number, is less impacted by these extreme values, indicating that some higher values are pushing the mean upward.The high standard deviation may also be caused by the existence of extreme values or outliers. These excessive numbers may distort the mean and widen the data distribution.

The dimensions of the data is 99367 observations and 7 variables. Looking at the summary we can see that there are no missing values. The next step is to identify outliers. To identify outliers we draw a box plot.

# drawing a boxplot
ggplot(substance_abuse,aes(y=Admissions))+geom_boxplot(fill="lightblue")+scale_y_log10()+labs(title="Box Plot",caption="Graph to analyse outliers")

# calculating outliers for tip amount
mean_tipa<-mean(substance_abuse$Admissions)
sd_tipa<-sd(substance_abuse$Admissions)
substance_abuse$z_ta<-abs((mean_tipa-substance_abuse$Admissions)/sd_tipa)
# outliers of tip_amount
out_ta<-substance_abuse[substance_abuse$z_ta>3,]
# no of outliers
print(paste("The number of outliers are",nrow(out_ta)))
## [1] "The number of outliers are 1917"
# imputing the data with the median
substance_abuse_imputed <- substance_abuse %>%
  mutate(Admissions = ifelse(z_ta > 3, median(Admissions, na.rm = TRUE), Admissions))%>%
   mutate(Admissions = round(Admissions, digits = 0))

The no of outliers identified are 1917.

I have chosen to impute the data with median as the data is right skewed and using mean for imputation will affect the data. Median won’t be influenced by extreme values and is a better estimate. We have decided to find out the mean,median and standard deviation as we can understand if the distribution is skewed or not and also is it left skewed or right skewed. Standard deviation might help us understand if we can expect outliers or not as explained above and it will also help us to decide if we should impute the outliers or remove them. Boxplot helps in identifying outliers.

3.

(30 pts) Structure the data relationally, at a minimum, you should have four tibbles or data frames

# creating the county tibble
county<-substance_abuse_imputed%>%
  select(County.of.Program.Location)%>%
  mutate(county_code = toupper(str_extract(County.of.Program.Location, pattern = "^....")))%>%
  distinct()
colnames(county)<-c("county_name","county_code")
head(county,2)
# creating the program_category tibble
program_category <- substance_abuse_imputed %>% select(Program.Category) %>% 
  # creating a unique identifier of first two letters for the program category
  mutate(program_code = toupper(str_extract(Program.Category, pattern = "^.."))) %>%
  distinct(program_code, .keep_all = TRUE)
colnames(program_category) = c("program_category", "program_code")
head(program_category,2)
# creating the primary substance group tibble
primary_substance_group <- substance_abuse_imputed %>% select(Primary.Substance.Group) %>% 
  # creating a unique identifier of first three letters for the primary substance group
  mutate(substance_code = toupper(str_extract(Primary.Substance.Group, pattern = "^..."))) %>%
  distinct(substance_code, .keep_all = TRUE)
colnames(primary_substance_group) = c("primary_substance_group", "substance_code")
head(primary_substance_group,2)
# creating the admissions_data tibble
admissions_data <- substance_abuse_imputed %>% 
 left_join(county, by = c("County.of.Program.Location" = "county_name")) %>%
 left_join(program_category, by = c("Program.Category" = "program_category")) %>%
left_join(primary_substance_group, by = c("Primary.Substance.Group" = "primary_substance_group")) %>%
  select(year = "Year", county_of_program_location = "county_code", program_category = "program_code", service_type = "Service.Type", age_group = "Age.Group", primary_substance_group = "substance_code", admissions = "Admissions")
head(admissions_data,2)

4.

(15 pts) Create a function called annualAdmissions() that derives the total number of reported admissions that transpired each year, for the entire state of NY and displays the results using a line chart. Annotate the chart to show the year with the highest number of admissions. Note: the year should be on the x-axis and the number of admissions on the y-axis. Explain the chart.

library(ggplot2)
annualAdmissions <- function(x) {
  # assigning the data to an other variable to print the year with the maximum admissions
  y<-x
   y<-y%>%
    select(year,admissions)%>%
     # grouping by year
    group_by(year)%>%
     # summing the admissions
      summarize(total_admissions=sum(admissions))
  max_admissions <-  max(y$total_admissions)
  max_year<-y$year[which.max(y$total_admissions)]
     # printing out the highest number of admissions and the corresponding year
  print(paste("The maximum admissions are" ,max_admissions, "and the corresponding year is ",max_year))
    
  x<-x%>%
    select(year,admissions)%>%
    group_by(year)%>%
    summarize(total_admissions=sum(admissions)) %>%
    # plotting the line chart
    ggplot( aes(x= year, y = total_admissions)) + geom_line(color="darkgreen") + labs(x = "Year", y = "Total Number of Admissions",caption="Line chart for analysing the maximum admissions") +  annotate("text", x = 2016, y = max_admissions + 10000, label = "Max admissions(2016)", color = "blue", size = 4) + geom_segment(aes(x = 2016, y = max_admissions, xend = 2016, yend = max_admissions + 8000), arrow = arrow(length = unit(0.1, "cm")), color = "red") + 
    theme_classic()
   
  
}

print(annualAdmissions(admissions_data))
## [1] "The maximum admissions are 191086 and the corresponding year is  2016"

The above graph is a line chart showing the relationship between the year and the total admissions. The x axis is for the Year and the y axis is for the total number of admissions. The highest number of admissions are for the year 2016.

5.

(15 pts) Analyze the percentage of admissions for each county and visualize the results for the top 5 counties using a bar chart. Explain the results. Note: ensure that you join any related dataframes/ tibbles.

# total number of admissions in the whole New York State
total=sum(admissions_data$admissions)
percentage_admisssions<-admissions_data%>%
  select(county_of_program_location,admissions)%>%
  group_by(county_of_program_location)%>%
  # calculating the percentage of admissions for each county
  summarize(percentage=((sum(admissions)/total)*100))
percentage_top5_admissions<-percentage_admisssions%>%
  slice_max(n=5,percentage)
# top 5 counties with high percentage
print(percentage_top5_admissions)
## # A tibble: 5 × 2
##   county_of_program_location percentage
##   <chr>                           <dbl>
## 1 "NEW "                           7.70
## 2 "QUEE"                           6.50
## 3 "SUFF"                           6.26
## 4 "KING"                           5.56
## 5 "BRON"                           5.19
ggplot(percentage_top5_admissions,aes(x=reorder(county_of_program_location,desc(percentage)),y=percentage))+geom_col(fill="lightblue") + labs(x = "County", y = "Percentage",caption="Bar Graph for analysis of top 5 counties with maximum percentage of admissions per county") + theme_dark()+scale_x_discrete(labels = c("NEW "="NEW YORK","QUEE"="QUEENS","SUFF"="SUFFOLK","KING"="KINGS","BRON"="BRONX"))

We have calculated the percentage of admissions for each county and created a new dataframe with the top 5 counties with high percentage. We plotted a bar graph with county being our x - axis and percentage being y-axis. In our graph and the dataframe we can see that New York has the highest percentage follwed by Queens, Suffolk,Kings and BRONX. The least percentage comparitively is for the county BRONX.

6.

(15 pts) Filter the data, using a regular expression, and extract all admissions to the various “Rehab” facilities; i.e. your regex should match all facilities that include the word rehab, rehabilitation, etc. Using the filtered data, identify which substance is the most prominent among each age group.

library(plotly)
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
rehab_data <- admissions_data %>% 
  filter(str_detect(service_type, ("\\w.[Rr]ehab")))

rehab_data<-rehab_data %>% select(age_group, primary_substance_group,admissions) %>%
left_join(primary_substance_group, by = c("primary_substance_group" = "substance_code")) %>%
  group_by(age_group,primary_substance_group.y) %>%
  summarise(total_admissions = sum(admissions))
## `summarise()` has grouped output by 'age_group'. You can override using the
## `.groups` argument.
rehab_data%>%
  group_by(age_group)%>%
  top_n(1,total_admissions)
plot <- ggplot(rehab_data, aes(primary_substance_group.y, total_admissions,fill=primary_substance_group.y)) + 
  geom_bar(stat = "identity", position = "dodge") + facet_wrap(~age_group) + theme_dark() +  theme(axis.text.x = element_text(angle = 55, hjust = 1, vjust = 1),axis.text.y = element_text(size = 8))+labs(y="count")+theme(axis.title.x = element_blank())
ggplotly(plot)

The above graph is a interactive graph, you can click on the bars to see the value of the count.

By seeing the dataframe and the graph we can see that for : the age group 18 through 24, Heroin is prominent the age group 25 through 34, Heroin is prominent the age group 35 through 44, Alcohol is prominent the age group 45 through 54, Alcohol is prominent the age group 55 and Older, Alcohol is prominent the age group Under 18, Marijuana is prominent