MATH2349 Semester 2, 2018

Assignment 3

Required packages

library("readr")
library("magrittr")
library("dplyr")
library("forecast")

Executive Summary

Please find below the list of actions taken as part of this assignment:

• Projects and Donations datasets are merged on Project ID column.
• Subset of first 10000 records from this dataset was taken to perform preprocessing.
• Data structure is checked and then the data type were changed as per the context of the data.
• Checked if there are any missing values and replaced them accordingly.
• Required columns were factorised and the necessary data type conversions have been done.
• Data was in a tidy format hence no actions were performed.
• New columns were mutated in the data frame.
• Missing values were removed using na.omit function.
• There were outliers in project funding balance which were dealt with using capping, after which the results were shown in a boxplot.
• Histogram of unnormalised data total project donated amount was shown before applying the transformation. We used boxcox transformation on project amount donated and showed the normalisation using histogram.

Data

A clear description of data sets, their sources, and variable descriptions should be provided. In this section, you must also provide the R codes with outputs (head of data sets) that you used to import/read/scrape the data set. You need to fulfil the minimum requirement #1 and merge at least two data sets to create the one you are going to work on. In addition to the R codes and outputs, you need to explain the steps that you have taken.

The data has been taken from metadata located at https://www.kaggle.com/donorschoose/io, we have considered the datasets Donations and Projects from the metadata for performing pre-processing activities. We are using the following columns for this assignment:

• Project ID (chr)
• Donation ID(chr)
• Donation Included Optional Donation(factor)
• Donation Amount (num)
• Donation Received Date(Date)
• Project Type(factor)
• Project Title (chr)
• Project Grade Level Category (factor)
• Project Resource Category(factor)
• Project Cost (num)
• Project Posted Date (Date)
• Project Expiration Date (Date)
• Project Current Status( Ord Factor)
• Project Fully Funded Date (Date)

donations <- read_csv("Donations.csv") %>% as.data.frame()
projects <- read_csv("Projects.csv") %>% as.data.frame()

par(mfcol=c(1,2))
head(donations)
head(projects)

Selecting necessary columns for pre-processing from Projects and Donations dataset

projects <- within(projects,rm("School ID","Teacher ID","Project Essay","Project Short Description",
                               "Project Need Statement","Project Subject Category Tree","Project Subject Subcategory Tree",
                               "Teacher Project Posted Sequence"))
donations <- within (donations,rm("Donor ID","Donor Cart Sequence"))

Merged donations and projects using left merge and named the new data set as project_donations

project_donations <- merge(donations,projects,"Project ID",all.x=T)

Took a subset of first 10000 records from this dataset and named it as project_donations_final

project_donations_final <- project_donations[1:10000,]
head(project_donations_final)

Understand

From the output of structure below, we have idenified that columns Donation Included Optional Donation,Project Type,Project Grade Level Category,Project Resource Category,Project Current Status are supposed to be as factors and ’Donation Received Date` is supposed to be a date

str(project_donations_final)

'data.frame':   10000 obs. of  14 variables:
 $ Project ID                         : chr  "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" ...
 $ Donation ID                        : chr  "38d2744bf9138b0b57ed581c76c0e2da" "dcf1071da3aa3561f91ac689d1f73dee" "688729120858666221208529ee3fc18e" "8cea27f0cc03f41f66aab96b284ae6a1" ...
 $ Donation Included Optional Donation: chr  "Yes" "Yes" "No" "Yes" ...
 $ Donation Amount                    : num  25 25 178 15 25 ...
 $ Donation Received Date             : POSIXct, format: "2016-05-15 10:23:04" "2016-06-06 20:05:23" "2016-08-23 13:15:57" "2016-06-04 17:58:55" ...
 $ Project Type                       : chr  "Teacher-Led" "Teacher-Led" "Teacher-Led" "Teacher-Led" ...
 $ Project Title                      : chr  "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" ...
 $ Project Grade Level Category       : chr  "Grades 6-8" "Grades 6-8" "Grades 6-8" "Grades 6-8" ...
 $ Project Resource Category          : chr  "Other" "Other" "Other" "Other" ...
 $ Project Cost                       : num  530 530 530 530 530 ...
 $ Project Posted Date                : Date, format: "2016-05-13" "2016-05-13" "2016-05-13" "2016-05-13" ...
 $ Project Expiration Date            : Date, format: "2016-09-12" "2016-09-12" "2016-09-12" "2016-09-12" ...
 $ Project Current Status             : chr  "Fully Funded" "Fully Funded" "Fully Funded" "Fully Funded" ...
 $ Project Fully Funded Date          : Date, format: "2016-08-23" "2016-08-23" "2016-08-23" "2016-08-23" ...

To identify the levels of all the factor columns we have used the distinct function as shown below

project_donations_final %>% distinct(`Donation Included Optional Donation`)

project_donations_final %>% distinct(`Project Type`)

project_donations_final %>% distinct(`Project Grade Level Category`)

project_donations_final %>% distinct(`Project Resource Category`)

project_donations_final %>% distinct(`Project Current Status`)

As we can see that there are missing values in the above columns, we have replaced the missing values as ‘Others’

project_donations_final$`Project Type`[is.na(project_donations_final$`Project Type`)] <- "Other"
project_donations_final$`Project Resource Category`[is.na(project_donations_final$`Project Resource Category`)] <- "Other"
project_donations_final$`Project Current Status`[is.na(project_donations_final$`Project Current Status`)] <- "Other"
project_donations_final$`Project Grade Level Category`[is.na(project_donations_final$`Project Grade Level Category`)] <- "Other"

As noted below, we have replaced the NA values as ‘Others’

project_donations_final %>% distinct(`Donation Included Optional Donation`)

project_donations_final %>% distinct(`Project Type`)

project_donations_final %>% distinct(`Project Grade Level Category`)

project_donations_final %>% distinct(`Project Resource Category`)

project_donations_final %>% distinct(`Project Current Status`)

All the columns have been factorised

project_donations_final$`Donation Included Optional Donation` <- project_donations_final$`Donation Included Optional Donation` %>% factor(levels = c("Yes","No"))
project_donations_final$`Project Type` <- project_donations_final$`Project Type` %>%
  factor(levels = c("Teacher-Led","Other","Professional Development","Student-Led"))
project_donations_final$`Project Grade Level Category` <- project_donations_final$`Project Grade Level Category` %>%
  factor(levels = c("Grades 6-8","Grades PreK-2","Grades 3-5","Grades 9-12","Other"))
project_donations_final$`Project Resource Category` <- project_donations_final$`Project Resource Category` %>% factor(levels = c("Other","Technology","Supplies","Books","Classroom Basics","Art Supplies","Musical Instruments","Computers & Tablets","Instructional Technology","Trips","Lab Equipment","Flexible Seating","Educational Kits & Games","Food, Clothing & Hygiene","Reading Nooks, Desks & Storage","Sports & Exercise Equipment","Visitors"))
project_donations_final$`Project Current Status`<- project_donations_final$`Project Current Status` %>% factor(levels = c("Fully Funded","Live","Expired","Other"),ordered = T)

Converting donation received date attribute as date

project_donations_final$`Donation Received Date` <- as.Date(project_donations_final$`Donation Received Date`)

As per below output, we can see that the necessary data type conversions have been done

str(project_donations_final)

'data.frame':   10000 obs. of  14 variables:
 $ Project ID                         : chr  "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" "000009891526c0ade7180f8423792063" ...
 $ Donation ID                        : chr  "38d2744bf9138b0b57ed581c76c0e2da" "dcf1071da3aa3561f91ac689d1f73dee" "688729120858666221208529ee3fc18e" "8cea27f0cc03f41f66aab96b284ae6a1" ...
 $ Donation Included Optional Donation: Factor w/ 2 levels "Yes","No": 1 1 2 1 1 1 1 1 1 1 ...
 $ Donation Amount                    : num  25 25 178 15 25 ...
 $ Donation Received Date             : Date, format: "2016-05-15" "2016-06-06" "2016-08-23" "2016-06-04" ...
 $ Project Type                       : Factor w/ 4 levels "Teacher-Led",..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Project Title                      : chr  "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" "OHMS Musician Chair Cart" ...
 $ Project Grade Level Category       : Factor w/ 5 levels "Grades 6-8","Grades PreK-2",..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Project Resource Category          : Factor w/ 17 levels "Other","Technology",..: 1 1 1 1 1 1 2 2 2 2 ...
 $ Project Cost                       : num  530 530 530 530 530 ...
 $ Project Posted Date                : Date, format: "2016-05-13" "2016-05-13" "2016-05-13" "2016-05-13" ...
 $ Project Expiration Date            : Date, format: "2016-09-12" "2016-09-12" "2016-09-12" "2016-09-12" ...
 $ Project Current Status             : Ord.factor w/ 4 levels "Fully Funded"<..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Project Fully Funded Date          : Date, format: "2016-08-23" "2016-08-23" "2016-08-23" "2016-08-23" ...

Tidy & Manipulate Data I

Our data is in tidy format. Hence, no action taken.

Tidy & Manipulate Data II

Created a new data frame with total donated amounts grouped by project titles and named it as donation_by_title

donation_by_title <- aggregate(project_donations_final$`Donation Amount`,list(project_donations_final$`Project Title`),sum)
donation_by_title %>% as.data.frame()

Renamed the column name of donation by title dataframe

colnames(donation_by_title) <- c("Project Title","Total Project donated amount")
head(donation_by_title)

Joined this data frame onto project_donation_final data frame by using left merge

project_donations_final <- merge(project_donations_final,donation_by_title,"Project Title",all.x=T)

Mutated the project donations final data frame by creating a new column called project_funding_balance

project_donations_final <- mutate(project_donations_final,Project_funding_balance= project_donations_final$`Project Cost`-project_donations_final$`Total Project donated amount`)
head(project_donations_final)

Scan I

In the context of this dataset, removing the missing values was appropriate. Hence we used the na.omit function to remove the missing values

project_donations_final <- na.omit(project_donations_final)

All the missing values have been removed as per below

sum(is.na(project_donations_final))

[1] 0

Scan II

As per the boxplot there are outliers in project funding balance which will be dealt with in the next step

boxplot(project_donations_final$Project_funding_balance,ylab="Amount",main="Pending Fund Balance boxplot before Removing Outliers")

Cap function has been created to identify and update the outliers to the nearest quartile

cap <- function(x){
  quantiles <- quantile( x, c(.05, 0.25, 0.75, .95 ))
  x[ x < quantiles[2] - 1.5*IQR(x) ] <- quantiles[1]
  x[ x > quantiles[3] + 1.5*IQR(x) ] <- quantiles[4]
  x}

Outliers for project funding attribute have been dealt with using capping

project_donations_final$Project_funding_balance <- project_donations_final$Project_funding_balance %>% cap()

Boxplot after dealing with the outliers

boxplot(project_donations_final$Project_funding_balance,ylab="Amount",main="Pending Fund Balance boxplot after Removing Outliers")

Transform

Histogram of total project donated amount before transformation (data is not normalised)

hist(project_donations_final$`Total Project donated amount`,main = "Total amount donated to projects",xlab = "Amount donated")

Applying boxcox transformation on project amount donated

boxcox_tot_don_amt <- BoxCox(project_donations_final$`Total Project donated amount`,lambda = "auto") 

Histogram after transformation (data is normalised)

hist(boxcox_tot_don_amt,main = "Donation amount after transformation",xlab = "Amount donated")