R is great for manipulating and running analysis on datasets. But maybe in R you have a data set and you want to fill in missing data, or register it as NA so that you don’t get errors when trying to run analysis.
Above is an example of some data with some empty cells. (See row 11.) You can download this csv file here. To view the file write something like this:
banking <- read.csv("CustomerBanking.csv")
View(banking)
Now say we wanted to register these blank spaces as systems NA’s. To do this we can write something like:
bankingNA <- read.csv("CustomerBanking.csv", na.strings = c(""))
head(bankingNA, 20) ## OR
View(bankingNA)
Now when we view the bankingNA object, the empty spaces should be assigned “NA”.
To view which cases in our file now have complete cases or not, that is, which cases have no missing values, we can do something like:
complete.cases(bankingNA)
bankingNA[complete.cases(bankingNA),]
bankingNA[!complete.cases(bankingNA),]
Note: Adding the “!” will return rows with missing values.
Now it is possible some of the NA’s will not be recognized by the system. Maybe they are spelled like N.A. or N/A. We can convert these to system NA’s by using something like:
bankingNA$balance <- gsub("NA", NA, bankingNA$balance)
bankingNA$balance <- gsub("no", NA, bankingNA$balance)
bankingNA$balance <- gsub("N/A", NA, bankingNA$balance)
bankingNA$balance <- gsub("n.a.", NA, bankingNA$balance)
bankingNA$contact <- gsub("unknown", NA, bankingNA$contact)
bankingNA$poutcome <- gsub("unknown", NA, bankingNA$poutcome)
bankingNA$education <- gsub("unknown", NA, bankingNA$education)
bankingNA$duration <- gsub("NA", NA, bankingNA$duration)
bankingNA$duration <- gsub("short", NA, bankingNA$duration)
Now that we have converted all of the NA’s to the proper system NA, R will recognize it properly. If we want to perform calculations with the ‘balance’ column in R, we will need to convert the data type to numeric. Use: str(bankingNA) to view the datatype of each column. We can see that ‘balance’ is type ‘chr’. To convert ‘balance’ to numeric we can do something like:
bankingNA$balance = as.factor(gsub(",", "", bankingNA$balance))
bankingNA$balance = as.numeric(gsub("\\$", "", bankingNA$balance))
This will take care of the decimals/commas and the dollar sign and leave us with only a number so we can perform calculations as needed.
Now that we have converted the type, maybe we want to calculate the mean, median, and standard deviation with this data, but we want to do it concisely with a package called “dplyr” using the summarise() method. We can do so with the following code:
library(dplyr)
bankingNA %>%
summarise(mean=mean(balance, na.rm = TRUE),
median=median(balance, na.rm = T),
Std=sd(balance, na.rm = T))
If we wanted to calculate the mean, median or std based on another column, we could do something like:
tapply(bankingNA$balance, bankingNA$marital, mean, na.rm=T)
tapply(bankingNA$duration, bankingNA$marital, mean, na.rm=T)
If we wanted to subset our data and find where the balance is greater than 1000 or maybe less than 0 for example… We could write:
which(bankingNA$balance > 10000)
which(bankingNA$balance < 0)
Data matching the criteria is returned.
If we wanted to calculate the median values of balance for married, single, and divorced,
and then replace the missing values with the calculated values for each
Category, we would first need to fix some of the columns so divorced, married, and single were all uniform.
bankingNA$marital <- gsub("DIVORCED", "divorced", bankingNA$marital)
bankingNA$marital <- gsub("Divorced", "divorced", bankingNA$marital)
bankingNA$marital <- gsub("MARRIED", "married", bankingNA$marital)
bankingNA$marital <- gsub("Married", "married", bankingNA$marital)
bankingNA$marital <- gsub("Single", "single", bankingNA$marital)
Then we calculate the median:
tapply(bankingNA$balance, bankingNA$marital, median, na.rm=T)
And replace the missing values for each category:
divorced <- median(bankingNA[bankingNA$marital=="divorced", "balance"], na.rm = TRUE)
bankingNA[is.na(bankingNA$balance) & bankingNA$marital=="divorced", "balance"] <- divorced
married <- median(bankingNA[bankingNA$marital=="married", "balance"], na.rm = TRUE)
bankingNA[is.na(bankingNA$balance) & bankingNA$marital=="married", "balance"] <- married
single <- median(bankingNA[bankingNA$marital=="single", "balance"], na.rm = TRUE)
bankingNA[is.na(bankingNA$balance) & bankingNA$marital=="single", "balance"] <- single
Now you’ve learned many different methods for handling missing variables in R, have fun and keep coding!
