<jupyter_start><jupyter_text>![](https://assets.datacamp.com/production/course_723/shields/thumb/shield_image_course_723_20190828-1-1qucbk7?1567003204) Introduction and Exproling Raw dataAn overview of the process of data cleaning with R and the basics of exploring raw data. Introduction to Cleaning Data in RThe Essential Parts of a Data Cleaning Process--------------------------------------------------1. Exploring raw data2. Tidying data3. Preparing data for analysis>  No one likes missing data, but it is dangerous to assume that it can simply be removed or replaced. Sometimes missing data tells us something important about whatever it is that we're measuring (i.e. the value of the variable that is missing may be related to - the reason it is missing). Such data is called Missing not at Random, or MNAR.We are presented with a messy, real-world dataset containing an entire year's worth of weather data from Boston, USA. Among other things, we'll be presented with variables that contain column names, column names that should be values, numbers coded as character strings, and values that are missing, extreme, and downright erroneous!  Exploring raw data Getting a feel for your data<jupyter_code>library(readr)
library(dplyr)
library(lubridate)
library(stringr)
library(installr)
weather = readRDS(gzcon(url('https://assets.datacamp.com/production/repositories/34/datasets/b3c1036d9a60a9dfe0f99051d2474a54f76055ea/weather.rds')))
class(weather)
head(weather)
tail(weather)
str(weather)
dim(weather)
bmi=read_csv('https://assets.datacamp.com/production/repositories/34/datasets/a0a569ebbb34500d11979eba95360125127e6434/bmi_clean.csv')
class(bmi)
dim(bmi)
head(bmi)
tail(bmi)
str(bmi)
glimpse(bmi)
# View the first 6 rows
head(bmi, n=6)

# View the first 15 rows
head(bmi, n=15)

# View the last 6 rows
tail(bmi, n=6)

# View the last 10 rows
tail(bmi, n=10)<jupyter_output><empty_output><jupyter_text>Visualizing data<jupyter_code># Histogram of BMIs from 2008
hist(bmi$Y2008)

# Scatter plot comparing BMIs from 1980 to those from 2008
plot(bmi$Y1980, bmi$Y2008)<jupyter_output><empty_output><jupyter_text>Tidying data an overview of the principles of tidy data, how to identify messy data, and what to do about it.  Introduction to tidy datain `bmi`, All of the year column names could be expressed as values of a new variable called `year`. Gathering columns into key-value pairsThe most important function in tidyr is `gather()`. It should be used when you have columns that are not variables and you want to collapse them into key-value pairs.The easiest way to visualize the effect of `gather()` is that it makes wide datasets long. Running the following command on `wide_df` will make it long:```pythongather(wide_df, my_key, my_val, -col)```  Spreading key-value pairs into columnsThe opposite of `gather()` is `spread()`, which takes key-values pairs and spreads them across multiple columns. This is useful when values in a column should actually be column names (i.e. variables). It can also make data more compact and easier to read.The easiest way to visualize the effect of spread() is that it makes long datasets wide. As you saw in the video, running the following command will make long_df wide:```pythonspread(long_df, my_key, my_val)```  Separating columnsThe `separate()` function allows you to separate one column into multiple columns. Unless you tell it otherwise, it will attempt to separate on any character that is not a letter or number. You can also specify a specific separator using the sep argument.treatments dataset obeys the principles of tidy data, but we'd like to split the treatment dates into two separate columns: year and month. This can be accomplished with the following:```pythonseparate(treatments, year_mo, c("year", "month"))```  Uniting columnsThe opposite of separate() is unite(), which takes multiple columns and pastes them together. By default, the contents of the columns will be separated by underscores in the new column, but this behavior can be altered via the sep argument.treatments but this time the `year_mo` column has been separated into `year` and `month`. The original column can be recreated by putting year and month back together:```pythonunite(treatments, year_mo, year, month)```  Column headers are values, not variable namesYou saw earlier, how we sometimes come across datasets where column names are actually values of a variable (e.g. months of the year). This is often the case when working with repeated measures data, where measurements are taken on subjects of interest on multiple occasions over time. The `gather()` function is helpful in these situations.  Variables are stored in both rows and columnsSometimes you'll run into situations where variables are stored in both rows and columns. Although it may not be immediately obvious, if we treat the values in the type column as variables and create a separate column for each of them, we can set things straight. To do this, we use the `spread()` function.  Multiple values are stored in one columnIt's also fairly common that you will find two variables stored in a single column of data. These variables may be joined by a separator like a dash, underscore, space, or forward slash.The `separate(`) function comes in handy in these situations.  Keep in mind that the `into` argument, which specifies the names of the 2 new columns being formed, must be given as a character vector (e.g. ```c("column1", "column2"))```.  Preparing data for analysis Type conversionsLoosely speaking, the `class()` function tells you what type of object you're working with. (There are subtle differences between the `class`, `type`, and `mode` of an object,<jupyter_code># Make this evaluate to "character"
class(as.character(TRUE))

# Make this evaluate to "numeric"
class(as.numeric("8484.00"))

# Make this evaluate to "integer"
class(as.integer(99L))

# Make this evaluate to "factor"
class(as.factor("factor"))

# Make this evaluate to "logical"
class(as.logical("FALSE"))<jupyter_output><empty_output><jupyter_text>Common type conversionsIt is often necessary to change, or coerce, the way that variables in a dataset are stored. This could be because of the way they were read into R (with `read.csv()`, for example) or perhaps the function you are using to analyze the data requires variables to be coded a certain way.Only certain coercions are allowed, but the rules for what works are generally pretty intuitive. For example, trying to convert a character string to a number gives an error: `as.numeric("some text")`.There are a few less intuitive results. For example, under the hood, the logical values `TRUE` and `FALSE` are coded as `1` and `0`, respectively. Therefore, `as.logical(1)` returns `TRUE` and `as.numeric(TRUE)` returns `1`.  Working with datesDates can be a challenge to work with in any programming language, but thanks to the lubridate package, working with dates in R isn't so bad. Since this project is about cleaning data, we only cover the most basic functions from lubridate to help us standardize the format of dates and times in our data. these functions combine the letters `y`, `m`, `d`, `h`, `m`, `s`, which stand for year, month, day, hour, minute, and second, respectively. The order of the letters in the function should match the order of the date/time you are attempting to read in, although not all combinations are valid. Notice that the functions are "smart" in that they are capable of parsing multiple formats.<jupyter_code>students = read_csv('https://assets.datacamp.com/production/repositories/34/datasets/f75a87dbbdf2cf79e2286f97b2af22146cb717b1/students_with_dates.csv')
head(students)
str(students)
# Coerce Grades to character
students$Grades <- as.character(students$Grades)

# Coerce Medu to factor
students$Medu <- as.factor(students$Medu)

# Coerce Fedu to factor
students$Fedu <- as.factor(students$Fedu)
    
# Look at students once more with str()
str(students)

# Preview students2 with str()
str(students)

# Parse as date
dmy("17 Sep 2015")

# Parse as date and time (with no seconds!)
mdy_hm("July 15, 2012 12:56")

# Coerce dob to a date (with no time)
students$dob <- ymd(students$dob)

# Coerce nurse_visit to a date and time
students$nurse_visit <- ymd_hms(students$nurse_visit)
    
# Look at students2 once more with str()
str(students)<jupyter_output>tibble [395 x 33] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
 $ X1         : num [1:395] 1 2 3 4 5 6 7 8 9 10 ...
 $ school     : chr [1:395] "GP" "GP" "GP" "GP" ...
 $ sex        : chr [1:395] "F" "F" "F" "F" ...
 $ dob        : Date[1:395], format: "2000-06-05" "1999-11-25" ...
 $ address    : chr [1:395] "U" "U" "U" "U" ...
 $ famsize    : chr [1:395] "GT3" "GT3" "LE3" "GT3" ...
 $ Pstatus    : chr [1:395] "A" "T" "T" "T" ...
 $ Medu       : Factor w/ 5 levels "0","1","2","3",..: 5 2 2 5 4 5 3 5 4 4 ...
 $ Fedu       : Factor w/ 5 levels "0","1","2","3",..: 5 2 2 3 4 4 3 5 3 5 ...
 $ Mjob       : chr [1:395] "at_home" "at_home" "at_home" "health" ...
 $ Fjob       : chr [1:395] "teacher" "other" "other" "services" ...
 $ reason     : chr [1:395] "course" "course" "other" "home" ...
 $ guardian   : chr [1:395] "mother" "father" "mother" "mother" ...
 $ traveltime : num [1:395] 2 1 1 1 1 1 1 2 1 1 ...
 $ studytime  : num [1:395] 2 2 2 3 2 2 2 2 2 2 ...
 $ failures   : num [1:395] 0 0 3 0[...]<jupyter_text>String manipulation Trimming and padding stringsOne common issue that comes up when cleaning data is the need to remove leading and/or trailing white space. The `str_trim()` function from stringr makes it easy to do this while leaving intact the part of the string that you actually want.```python> str_trim("  this is a test     ")[1] "this is a test"```A similar issue is when you need to pad strings to make them a certain number of characters wide. One example is if you had a bunch of employee ID numbers, some of which begin with one or more zeros. When reading these data in, you find that the leading zeros have been dropped somewhere along the way (probably because the variable was thought to be numeric and in that case, leading zeros would be unnecessary.)```python> str_pad("24493", width = 7, side = "left", pad = "0")[1] "0024493"```<jupyter_code># Load the stringr package
library(stringr)

# Trim all leading and trailing whitespace
str_trim(c("   Filip ", "Nick  ", " Jonathan"))

# Pad these strings with leading zeros
str_pad(c("23485W", "8823453Q", "994Z"), width=9, side='left', pad='0')<jupyter_output><empty_output><jupyter_text>> Examples like this are certainly handy in R. For example, the `str_pad()` function is useful when importing a dataset with US zip codes. Occasionally R will drop the leading 0 in a zipcode, thinking it's numeric.  Upper and lower caseIn addition to trimming and padding strings, you may need to adjust their case from time to time. Making strings uppercase or lowercase is very straightforward in (base) R thanks to `toupper()` and `tolower()`. Each function takes exactly one argument: the character string (or vector/column of strings) to be converted to the desired case.<jupyter_code>states <- c("al", "ak", "az", "ar", "ca", "co", "ct", "de", "fl", "ga", "hi", "id", "il", "in", "ia", "ks", "ky", "la", "me", "md", "ma", "mi", "mn", "ms", "mo", "mt", "ne", "nv", "nh", "nj", "nm", "ny", "nc", "nd", "oh", "ok", "or", "pa", "ri", "sc", "sd", "tn", "tx", "ut", "vt", "va", "wa", "wv", "wi", "wy")
# Print state abbreviations
states

# Make states all uppercase and save result to states_upper
states_upper <- toupper(states)
states_upper 

# Make states_upper all lowercase again
tolower(states_upper)<jupyter_output><empty_output><jupyter_text>Finding and replacing stringsThe stringr package provides two functions that are very useful for finding and/or replacing patterns in strings: `str_detect()` and `str_replace()`.Like all functions in stringr, the first argument of each is the string of interest. The second argument of each is the pattern of interest. In the case of `str_detect()`, this is the pattern we are searching for. In the case of `str_replace()`, this is the pattern we want to replace. Finally, `str_replace()` has a third argument, which is the string to replace with.```python> str_detect(c("banana", "kiwi"), "a")[1]  TRUE FALSE> str_replace(c("banana", "kiwi"), "a", "o")[1] "bonana" "kiwi"```<jupyter_code># Copy of students2: students3
students3 <- students

# Look at the head of students3
head(students3)

# Detect all dates of birth (dob) in 1997
str_detect(students3$dob, "1997")

# In the sex column, replace "F" with "Female" ...
students3$sex <- str_replace(students3$sex, "F", "Female")

# ... and "M" with "Male"
students3$sex <- str_replace(students3$sex, "M", "Male")

# View the head of students3
head(students3)<jupyter_output><empty_output><jupyter_text>Missing and special values Finding missing values<jupyter_code>social_df = data.frame(name=c('Sarah', 'Tom', 'David', 'Alice'), n_friends=c(244,NA,145,43),status=c('going out', "",'Movie Night', ""))
# Call is.na() on the full social_df to spot all NAs
is.na(social_df)

# Use the any() function to ask whether there are any NAs in the data
any(is.na(social_df))

# View a summary() of the dataset
summary(social_df)

# Call table() on the status column
table(social_df$status)<jupyter_output><empty_output><jupyter_text>Dealing with missing valuesMissing values can be a rather complex subject, but here we'll only look at the simple case where you are simply interested in normalizing and/or removing all missing values from your data. For more information on why this is not always the best strategy, search online for "missing not at random."Looking at the `social_df` dataset again, we asked around a bit and figured out what's causing the missing values that you saw in the last exercise. Tom doesn't have a social media account on this particular platform, which explains why his number of friends and current status are missing (although coded in two different ways). Alice is on the platform, but is a passive user and never sets her status, hence the reason it's missing for her.<jupyter_code># Replace all empty strings in status with NA
social_df$status[social_df$status == ""] <- NA

# Print social_df to the console
social_df

# Use complete.cases() to see which rows have no missing values
complete.cases(social_df)

# Use na.omit() to remove all rows with any missing values
na.omit(social_df)<jupyter_output><empty_output><jupyter_text>Outliers and obvious errors Dealing with outliers and obvious errorsWhen dealing with strange values in your data, you often must decide whether they are just extreme or actually erroneous. Extreme values show up all over the place, but you, the data analyst, must figure out when they are plausible and when they are not.<jupyter_code># Look at a summary() of students3
summary(students3)

# View a histogram of the studytime variable
hist(students3$studytime)

# View a histogram of the failures variable
hist(students3$failures)

# View a histogram of absences, but force zeros to be bucketed to the right of zero
hist(students3$failures, right=FALSE)<jupyter_output><empty_output><jupyter_text>Another look at strange valuesAnother useful way of looking at strange values is with boxplots. Simply put, boxplots draw a box around the middle 50% of values for a given variable, with a bolded horizontal line drawn at the median. Values that fall far from the bulk of the data points (i.e. outliers) are denoted by open circles.<jupyter_code>boxplot(students3$studytime)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09859516325614215, 'fraction_numerical': 0.02585156199884534, 'mean_word_length': 4.817164179104478, 'pattern_counts': {'":': 0, '<': 55, '<?xml version=': 0, '>': 50, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '183117', 'license': 'MIT', 'n_tokens_mistral': 4874, 'n_tokens_neox': 4473, 'n_words': 2128, 'path': 'notebooks/Cleaning Data in R.ipynb', 'repo_name': 'VictorOmondi1997/Cleaning-Data-in-R', 'size': '15589'}