library(dplyr)
Attaching package: ‘dplyr’
The following objects are masked from ‘package:stats’:
filter, lag
The following objects are masked from ‘package:base’:
intersect, setdiff, setequal, unionlibrary(tidyr)
library(magrittr)
Attaching package: ‘magrittr’
The following object is masked from ‘package:tidyr’:
extractlibrary(outliers)
library(stats)
library(ggplot2)Warning: package ‘ggplot2’ was built under R version 4.1.1library(knitr)Warning: package ‘knitr’ was built under R version 4.1.1library(MVN)Warning: package ‘MVN’ was built under R version 4.1.1
Registered S3 method overwritten by 'data.table':
method from
print.data.table library(stats)
library(ggplot2)
The purpose of this assignment is to apply Data Wrangling methods learnt during this course and use it in real-world data. The process includes following data pre-processing steps:
The first dataset contains raw counts of all cancer incidence and mortality data by year, age,sex and type.The data was published by Australian Institute of Health and Welfare (AIHW), and accessed from https://data.gov.au/ .
Dataset Name - Cancer counts Its comprises counts by cancer types for incidents and mortalities, the data range is from 1968 to 2010.
The second dataset is cancer rates among all Australian population for all cancer incidence and mortality data by sex, year, age, and type.The data was published by Australian Institute of Health and Welfare (AIHW), and accessed from https://data.gov.au/
Dataset Name - Cancer Rates
Both datasets have 9 main variables that can be used for further analysis and are described as follow:
Both these 2 datasets were in CSV format.
cancer_counts <- read_csv("~/Desktop/practice/data/cancer_counts.csv")Rows: 9240 Columns: 23
── Column specification ────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (3): Sex, Type, Cancer_Type
dbl (20): Year, Age_0_to_4, Age_5_to_9, Age_10_to_14, Age_15_to_19, Age_20_to_24, Age_25_to_29, Age_30_to_34...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.str(cancer_counts)spec_tbl_df [9,240 × 23] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
$ Year : num [1:9240] 1968 1969 1970 1971 1972 ...
$ Sex : chr [1:9240] "Male" "Male" "Male" "Male" ...
$ Type : chr [1:9240] "Incidence" "Incidence" "Incidence" "Incidence" ...
$ Cancer_Type : chr [1:9240] "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" ...
$ Age_0_to_4 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_5_to_9 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_10_to_14: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_15_to_19: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_20_to_24: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_25_to_29: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_30_to_34: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_35_to_39: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_40_to_44: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_45_to_49: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_50_to_54: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_55_to_59: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_60_to_64: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_65_to_69: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_70_to_74: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_75_to_79: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_80_to_84: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_85+ : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_Unknown : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
- attr(*, "spec")=
.. cols(
.. Year = col_double(),
.. Sex = col_character(),
.. Type = col_character(),
.. Cancer_Type = col_character(),
.. Age_0_to_4 = col_double(),
.. Age_5_to_9 = col_double(),
.. Age_10_to_14 = col_double(),
.. Age_15_to_19 = col_double(),
.. Age_20_to_24 = col_double(),
.. Age_25_to_29 = col_double(),
.. Age_30_to_34 = col_double(),
.. Age_35_to_39 = col_double(),
.. Age_40_to_44 = col_double(),
.. Age_45_to_49 = col_double(),
.. Age_50_to_54 = col_double(),
.. Age_55_to_59 = col_double(),
.. Age_60_to_64 = col_double(),
.. Age_65_to_69 = col_double(),
.. Age_70_to_74 = col_double(),
.. Age_75_to_79 = col_double(),
.. Age_80_to_84 = col_double(),
.. `Age_85+` = col_double(),
.. Age_Unknown = col_double()
.. )
- attr(*, "problems")=<externalptr> cancer_rates <- read_csv("~/Desktop/practice/data/cancer_rates.csv")Rows: 9240 Columns: 26
── Column specification ────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (3): Sex, Type, Cancer_Type
dbl (23): Year, Age_0_to_4, Age_5_to_9, Age_10_to_14, Age_15_to_19, Age_20_to_24, Age_25_to_29, Age_30_to_34...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.str(cancer_rates)spec_tbl_df [9,240 × 26] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
$ Year : num [1:9240] 1968 1969 1970 1971 1972 ...
$ Sex : chr [1:9240] "Male" "Male" "Male" "Male" ...
$ Type : chr [1:9240] "Incidence" "Incidence" "Incidence" "Incidence" ...
$ Cancer_Type : chr [1:9240] "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" "Acute lymphoblastic leukaemia" ...
$ Age_0_to_4 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_5_to_9 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_10_to_14 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_15_to_19 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_20_to_24 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_25_to_29 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_30_to_34 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_35_to_39 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_40_to_44 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_45_to_49 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_50_to_54 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_55_to_59 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_60_to_64 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_65_to_69 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_70_to_74 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_75_to_79 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_80_to_84 : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_85+ : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_Unknown : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_Std_Rate_Aust: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_Std_Rate_Segi: num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
$ Age_Std_Rate_WHO : num [1:9240] NA NA NA NA NA NA NA NA NA NA ...
- attr(*, "spec")=
.. cols(
.. Year = col_double(),
.. Sex = col_character(),
.. Type = col_character(),
.. Cancer_Type = col_character(),
.. Age_0_to_4 = col_double(),
.. Age_5_to_9 = col_double(),
.. Age_10_to_14 = col_double(),
.. Age_15_to_19 = col_double(),
.. Age_20_to_24 = col_double(),
.. Age_25_to_29 = col_double(),
.. Age_30_to_34 = col_double(),
.. Age_35_to_39 = col_double(),
.. Age_40_to_44 = col_double(),
.. Age_45_to_49 = col_double(),
.. Age_50_to_54 = col_double(),
.. Age_55_to_59 = col_double(),
.. Age_60_to_64 = col_double(),
.. Age_65_to_69 = col_double(),
.. Age_70_to_74 = col_double(),
.. Age_75_to_79 = col_double(),
.. Age_80_to_84 = col_double(),
.. `Age_85+` = col_double(),
.. Age_Unknown = col_double(),
.. Age_Std_Rate_Aust = col_double(),
.. Age_Std_Rate_Segi = col_double(),
.. Age_Std_Rate_WHO = col_double()
.. )
- attr(*, "problems")=<externalptr> The Dataset contains 9240 observations and 26 Variable.
By merging the two datasets,it combines the common variables in cancer counts and cancer rates by year, type,sex and cancer_type.
cancer_merged <- left_join(cancer_counts, cancer_rates, by = c("Year" = "Year", "Cancer_Type" = "Cancer_Type", "Type" = "Type", "Sex" = "Sex" ))
str(cancer_merged)
dim(cancer_merged)
The two datasets are merged into one using the left_join() function by using the common variables in the both dataset.
After the datasets were merged, we ended up with up with 45 variables and 9240 observations.
Variable descriptions / data dictionary 1. Year- each year the statistics were gathered for cancer mortality (numeric value to be converted to interger) 2. Sex - gender male or female(character value - categorical to be converted to factor) 3. Type- If its a incident or a mortality by the cancer type reported (character value - categorical) 4. Cancer Type - fatalities resulting by the type of cancer (character value - categorical) 5. Age.x and Age.y - cancer incident or mortality count by the age band (character value - categorical) 6. Age_Std_Rate* - further clarification on the cancer incident/mortality rates.
By using str(cancer_merged) we can deteremind the structure of the dataset and to check the dimension of the datset dim(cancer_merged).
Sex variable has been changed from character to factor,ordered using levels(). Year varible has been changed from numeric to factor.
##Sex variable from character to factor
cancer_merged$Sex <- factor(cancer_merged$Sex ,levels = c("Male", "Female", "Persons"))
str(cancer_merged$Sex)
## Year varible has been changed from numeric to factor.
cancer_merged$Year <- as.integer(cancer_merged$Year)
is.integer(cancer_merged$Year)
newcancer_merged <- cancer_merged
newcancer_merged %<>% select(-c(Age_Unknown.x, Age_Unknown.y,Age_Std_Rate_WHO,Age_Std_Rate_Aust, Age_Std_Rate_Segi))
#glimpe(newcancer_merged) or str(newcancer_merged) will confirm the new subset dataframe, it contains 40 varibles and 9,240 observations.
For the data to meet tidy data principles, tidy data needs to adhere to the following rules (Wickham and Grolemund (2016)):
Each variable must have its own column. Each observation must have its own row. Each value must have its own cell.
Further scrutiny of the merged dataset confirms the data has several columns for the age band therefore it does not meet the tidy principals, hence all these columns should be in one column.
To amend the dataset we will use pivot_longer function, however as there are many age bands, we will need to consilidate the age bands to the below categories using the transmute function:
Age 0 to 9 years old
Age 10 to 19 years old
Ages 20 to 64 years old
Ages 65 years to 84 years old
Tidy and Manipulate Data I & II has been combined to one, transmute and pivot_longer has been action-ed below.
Age.band x and y represent the same age band for cancer counts and cancer rates however the contain different values such as cancer counts and cancer rates.
# Age categories have been grouped together in the below bands, Senior band is
newcancer_merged1 <- newcancer_merged
newcancer_merged1 %<>%
transmute(Year, Sex, Type, Cancer_Type,
Age0_9x = Age_0_to_4.x + Age_5_to_9.x,
Age10_19x = Age_10_to_14.x + Age_15_to_19.x,
Age20_64x = Age_20_to_24.x + Age_25_to_29.x +
Age_30_to_34.x + Age_35_to_39.x + Age_40_to_44.x +
Age_45_to_49.x + Age_50_to_54.x + Age_55_to_59.x +
Age_60_to_64.x,
Age65_84x = Age_65_to_69.x + Age_70_to_74.x + Age_75_to_79.x + Age_80_to_84.x,
Age0_9y = Age_0_to_4.y + Age_5_to_9.y,
Age10_19y = Age_10_to_14.y + Age_15_to_19.y,
Age20_64y = Age_20_to_24.y + Age_25_to_29.y +
Age_30_to_34.y + Age_35_to_39.y + Age_40_to_44.y +
Age_45_to_49.y + Age_50_to_54.y + Age_55_to_59.y +
Age_60_to_64.y,
Age64_84y = Age_65_to_69.y + Age_70_to_74.y + Age_75_to_79.y + Age_80_to_84.y)
str(newcancer_merged1)
# convering to data.frame()
newcancer_merged1 <-as.data.frame(newcancer_merged1)
The age band columns have now been merged into one category by group them to a age range, however to meet the tidy date age range will need to be placed into a single “Age” column.
# a) Creating counts dataset to combine all of the age groups into one age column.
Age_counts <- newcancer_merged1
Age_counts %<>%
select(Year, Sex, Type, Cancer_Type, Age0_9x, Age10_19x,Age20_64x,Age65_84x) %>%
pivot_longer(- c(Year, Sex, Type, Cancer_Type),
names_to = "Age_Category", values_to = "Count")
# b) Create rates dataset to combine all of the age groups into one age column.
Age_rates <- newcancer_merged1
Age_rates %<>%
select(Year, Sex, Type, Cancer_Type, Age0_9x, Age10_19x,Age20_64x,Age65_84x) %>%
pivot_longer(- c(Year, Sex, Type, Cancer_Type),
names_to = "Age_Category", values_to = "Rate")
# c) Rejoining datasets counts and rates
newcancer_merged2 <- left_join(Age_counts,Age_rates , by = c("Year" = "Year", "Sex" = "Sex", "Type" = "Type",
"Cancer_Type" = "Cancer_Type",
"Age_Category" = "Age_Category" ))
# Converting Age columns to factor
newcancer_merged2$Age_Category <- factor(newcancer_merged2$Age_Category, levels = c("Age0_9x", "Age10_19x","Age20-64x","Age65_84x"))
"
str(newcancer_merged2)
The below actions are taken to determine missing values,inconsistencies and obvious errors.
The following steps can be taken to confirm the missing values in the dataset.
summarise(count = n()). function sum(is.na()) - to get missing values for the entire dataset names(which(colSums(is.na())>0)) -This can determined which variables have missing values completed.cases() function excludes missing values from dataset
# To get missing values (NA's) are for the entire dataset
sum(is.na(newcancer_merged2))
# Obtaining missing values per each varible
colSums(is.na(newcancer_merged2))
# Determing the years with missing values.
newcancer_merged2%>%
filter(is.na(Count) | is.na(Rate)) %>%
select(Year, Sex, Type, Cancer_Type, Age_Category, Count, Rate) %>%
group_by(Year) %>%
summarise(count = n())
Between 1968 -1981 and 2011 there was 472 missing values. Between 1982 -2000 there was 116 missing value and from 2002 to 2011 there was 104 missing values , gradually decreasing.
# Treatment of missing values using completed.cases function
newcancer_merged2 <- newcancer_merged2[complete.cases(newcancer_merged2),]
nrow(newcancer_merged2) # Get number of non-missing rows
# check that all NA have been excluded
sum(is.na(str(newcancer_merged2)))
# OR
Summary(newcancer_merged2)
With Scan II we check the numeric data for outliers.
As both counts and rates are the individual numeric variables, univariate outlier detection will be implemented.
# Generate boxplot - Count variable
newcancer_merged2$Count %>%
boxplot(main ="Cancer Counts", ylab = "Count", col = "red")
# Mean and Median values for Count
summary(newcancer_merged2$Count)
# Generate boxplot - Rate variable
newcancer_merged2$Rate %>%
boxplot(main = "Cancer Rates", ylab = "Count", col = "blue")
# Mean and Median values for Rate variable
summary(newcancer_merged2$Rate)
We have detected following outliers for each of the variables Count - has 197.6 outliers above the mean Rates - has 197.6 outliers above the mean
# Verifying IQR for upper and lower fences for Count and Rate variables
# Count variable IQR
count_iqr <- IQR(newcancer_merged2$Count)
count_iqr
rate_iqr <- IQR(newcancer_merged2$Rate) # Rate variable IQR
rate_iqrIQR for Counts and Rates are 71.
#To determine upper and lower fences for Count and Rate variable.
# Lower Count quantile
q1 <- quantile(newcancer_merged2$Count, probs = 0.25)
# Upper Count quantile
q3 <- quantile(newcancer_merged2$Count, probs = 0.75)
# Lower fence value for Count variable
count_low_fence <- q1 - (1.5 * count_iqr)
# Upper fence value for Count variable
count_up_fence <- q3 + (1.5 * count_iqr)
count_low_fence
count_up_fence
As can be seen above, the fence values for both variables are as follows:
Fence Count Rate Low -106.5 -106.5 Up 177.5 177.5
# Count outliers below low fence
count_up_low_outliers <- which(newcancer_merged2$Count < count_low_fence)
# Count outliers above up fence
count_up_outliers <- which(newcancer_merged2$Count > count_up_fence)
# Number of Count outliers below low fence and above low fence
length(count_up_low_outliers)
length(count_up_outliers)
# Rate outliers below low fence
rate_up_low_outliers <- which(newcancer_merged2$Rate < count_low_fence)
# Rate outliers above up fence
rate_up_outliers <- which(newcancer_merged2$Rate > count_up_fence)
# Number of Count outliers below low fence and above low fence
length(rate_up_low_outliers)
length(rate_up_outliers)Outliers for both variables Fence Count Rate Low 0 0 Up 3841 3841
Imputing these outliers with the mean of the Count and Rate values.
#Impute Count outliers with mean of Count
newcancer_merged2$Count[count_up_outliers] <- mean(newcancer_merged2$Count)
length(newcancer_merged2$Count[count_up_outliers])
#Impute Rate outliers with mean of Rate
newcancer_merged2$Rate[rate_up_outliers] <- mean(newcancer_merged2$Rate)
length(newcancer_merged2$Rate[rate_up_outliers])
Both variables aligns with the initial outlier amount.
newcancer_merged2$Count %>%
boxplot(main = "Cancer Counts", ylab = "Count", col = "blue")
newcancer_merged2$Rate %>%
boxplot(main = "Cancer Rate", ylab = "Rate", col = "yellow") Applying log transformation is the best way to reduce outliers and decrease the skewness of the empirical distribution.
When applying log transformation to the boxplot, the outliers are reduced and only 1 outlier is detected which is normal as it is not fall far away from the Maximum value.
hist(newcancer_merged2$Rate, main = "Histogram of Cancer Rates",
xlab="Cancer Rate")
log_rate<- log10(newcancer_merged2$Rate)
hist(log_rate,
main = "Histogram of log 10 log Cancer Rates",
xlab = "Cancer Rate", col = "blue", border = "grey")We conclude with mortality rates varying between age groups and the lowest spectrum cancer related cause of death for Age 0-9 and Age 10-19 is Acute lymphoblastic leukaemia.
Adults are at high risk from colon cancer. And in Seniors Rectal cancer is the biggest killer with Non-Hodgkin lymphoma having the highest rates of incidents.
``` # Reference
Boehmke, BC 2016, Data Wrangling with R (Links to an external site.), Springer International Publishing, Cham, Switzerland.
Wickham, H 2019, Advanced R (Links to an external site.), CRC press.