Data Cleaning Assignment

Question 1: Technically Correct Data

Answer: B - Technically correct data.

Question 2: Consistent Data

Answer: C - Consistent data.

Load the Dataset

dirty_iris <- read.csv(
  "https://raw.githubusercontent.com/edwindj/datacleaning/master/data/dirty_iris.csv"
)

head(dirty_iris)
##   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
## 1          6.4         3.2          4.5         1.5 versicolor
## 2          6.3         3.3          6.0         2.5  virginica
## 3          6.2          NA          5.4         2.3  virginica
## 4          5.0         3.4          1.6         0.4     setosa
## 5          5.7         2.6          3.5         1.0 versicolor
## 6          5.3          NA           NA         0.2     setosa
str(dirty_iris)
## 'data.frame':    150 obs. of  5 variables:
##  $ Sepal.Length: num  6.4 6.3 6.2 5 5.7 5.3 6.4 5.9 5.8 4.8 ...
##  $ Sepal.Width : num  3.2 3.3 NA 3.4 2.6 NA 2.7 3 2.7 3.1 ...
##  $ Petal.Length: num  4.5 6 5.4 1.6 3.5 NA 5.3 5.1 4.1 1.6 ...
##  $ Petal.Width : num  1.5 2.5 2.3 0.4 1 0.2 NA 1.8 1 0.2 ...
##  $ Species     : chr  "versicolor" "virginica" "virginica" "setosa" ...

Question 3: Missing Values in Petal.Length

missing_petal_length <- sum(is.na(dirty_iris$Petal.Length))

print(missing_petal_length)
## [1] 19

Question 4: Complete Observations

complete_count <- sum(complete.cases(dirty_iris))

complete_percentage <- mean(complete.cases(dirty_iris)) * 100

print(complete_count)
## [1] 96
print(complete_percentage)
## [1] 64

Question 5: Identify Special Values

# Identify numeric columns
numeric_cols <- sapply(dirty_iris, is.numeric)

# Count special values in each numeric column
special_values <- data.frame(
  Variable = names(dirty_iris)[numeric_cols],
  Missing_NA = sapply(dirty_iris[numeric_cols],
                      function(x) sum(is.na(x))),
  NaN_Count = sapply(dirty_iris[numeric_cols],
                     function(x) sum(is.nan(x))),
  Positive_Inf = sapply(dirty_iris[numeric_cols],
                        function(x) sum(x == Inf, na.rm = TRUE)),
  Negative_Inf = sapply(dirty_iris[numeric_cols],
                        function(x) sum(x == -Inf, na.rm = TRUE))
)

# Display results
print(special_values)
##                  Variable Missing_NA NaN_Count Positive_Inf Negative_Inf
## Sepal.Length Sepal.Length         10         0            0            0
## Sepal.Width   Sepal.Width         17         0            0            0
## Petal.Length Petal.Length         19         0            0            0
## Petal.Width   Petal.Width         12         0            1            0

Question 6: Replace Special Values with NA

# Locate infinite values
for (col in names(dirty_iris)[numeric_cols]) {
  print(which(is.infinite(dirty_iris[[col]])))
}
## integer(0)
## integer(0)
## integer(0)
## [1] 86
# Replace infinite values with NA
for (col in names(dirty_iris)[numeric_cols]) {
  dirty_iris[[col]][is.infinite(dirty_iris[[col]])] <- NA
}

# Verify corrections
print(sapply(dirty_iris[numeric_cols],
             function(x) sum(is.infinite(x))))
## Sepal.Length  Sepal.Width Petal.Length  Petal.Width 
##            0            0            0            0

Question 7: Identify Observations Violating Rules

Rules:

  1. Sepal.Width must be greater than 0.
  2. Sepal.Length cannot exceed 30 cm.
violations <- which(
  (!is.na(dirty_iris$Sepal.Width) &
     dirty_iris$Sepal.Width <= 0) |
  (!is.na(dirty_iris$Sepal.Length) &
     dirty_iris$Sepal.Length > 30)
)

print(dirty_iris[violations, ])
##     Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
## 16           5.0          -3          3.5         1.0 versicolor
## 28          73.0          29         63.0          NA  virginica
## 125         49.0          30         14.0         2.0     setosa
## 130          5.7           0          1.7         0.3     setosa
print(length(violations))
## [1] 4

Question 8: Correct Sepal.Width Errors

Negative values will be converted to positive values. Zero values will be replaced with NA.

# Locate negative values
negative_rows <- which(dirty_iris$Sepal.Width < 0)

print(dirty_iris[negative_rows, ])
##    Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
## 16            5          -3          3.5           1 versicolor
# Correct negative values
dirty_iris$Sepal.Width[negative_rows] <-
  abs(dirty_iris$Sepal.Width[negative_rows])

# Locate zero values
zero_rows <- which(dirty_iris$Sepal.Width == 0)

print(dirty_iris[zero_rows, ])
##     Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 130          5.7           0          1.7         0.3  setosa
# Replace zero values with NA
dirty_iris$Sepal.Width[zero_rows] <- NA

# Verify corrections
print(dirty_iris[c(negative_rows, zero_rows), ])
##     Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
## 16           5.0           3          3.5         1.0 versicolor
## 130          5.7          NA          1.7         0.3     setosa

Question 9: Missing Value Imputation

The following four methods will be used:

  • Sepal.Width: Mean
  • Petal.Length: Median
  • Sepal.Length: Linear Regression
  • Petal.Width: k-Nearest Neighbors

Option A: Mean Imputation - Sepal.Width

mean_width <- mean(dirty_iris$Sepal.Width, na.rm = TRUE)

dirty_iris$Sepal.Width[
  is.na(dirty_iris$Sepal.Width)
] <- mean_width

print(mean_width)
## [1] 3.462121
print(sum(is.na(dirty_iris$Sepal.Width)))
## [1] 0

Option B: Median Imputation - Petal.Length

median_length <- median(dirty_iris$Petal.Length, na.rm = TRUE)

dirty_iris$Petal.Length[
  is.na(dirty_iris$Petal.Length)
] <- median_length

print(median_length)
## [1] 4.5
print(sum(is.na(dirty_iris$Petal.Length)))
## [1] 0

Option C: Linear Regression - Sepal.Length

# Replace impossible values with NA
dirty_iris$Sepal.Length[
  !is.na(dirty_iris$Sepal.Length) &
  dirty_iris$Sepal.Length > 30
] <- NA

# Fit regression model
regression_model <- lm(
  Sepal.Length ~ Sepal.Width + Petal.Length,
  data = dirty_iris
)

# Identify missing values
missing_length <- which(is.na(dirty_iris$Sepal.Length))

# Predict missing values
predicted_length <- predict(
  regression_model,
  newdata = dirty_iris[missing_length, , drop = FALSE]
)

# Replace missing values
dirty_iris$Sepal.Length[missing_length] <- predicted_length

print(summary(regression_model))
## 
## Call:
## lm(formula = Sepal.Length ~ Sepal.Width + Petal.Length, data = dirty_iris)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -5.1336 -0.2965  0.0045  0.3674  1.8017 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   4.92945    0.60769   8.112 2.71e-13 ***
## Sepal.Width  -0.02919    0.17970  -0.162    0.871    
## Petal.Length  0.23477    0.03045   7.710 2.46e-12 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.7976 on 135 degrees of freedom
##   (12 observations deleted due to missingness)
## Multiple R-squared:  0.3272, Adjusted R-squared:  0.3172 
## F-statistic: 32.82 on 2 and 135 DF,  p-value: 2.415e-12
print(sum(is.na(dirty_iris$Sepal.Length)))
## [1] 0

Option D: kNN Imputation - Petal.Width

# Install package if needed
if (!requireNamespace("VIM", quietly = TRUE)) {
  install.packages("VIM", repos = "https://cloud.r-project.org")
}

library(VIM)

# Perform kNN imputation
imputed_data <- VIM::kNN(
  dirty_iris,
  variable = "Petal.Width",
  dist_var = c(
    "Sepal.Length",
    "Sepal.Width",
    "Petal.Length"
  ),
  k = 5,
  imp_var = FALSE
)

# Update dataset
dirty_iris$Petal.Width <- imputed_data$Petal.Width

# Verify imputation
print(sum(is.na(dirty_iris$Petal.Width)))
## [1] 0

Final Verification

# Remaining missing values
print(colSums(is.na(dirty_iris)))
## Sepal.Length  Sepal.Width Petal.Length  Petal.Width      Species 
##            0            0            0            0            0
# Remaining infinite values
print(sapply(dirty_iris[numeric_cols],
             function(x) sum(is.infinite(x))))
## Sepal.Length  Sepal.Width Petal.Length  Petal.Width 
##            0            0            0            0
# Display cleaned dataset
head(dirty_iris)
##   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
## 1          6.4    3.200000          4.5         1.5 versicolor
## 2          6.3    3.300000          6.0         2.5  virginica
## 3          6.2    3.462121          5.4         2.3  virginica
## 4          5.0    3.400000          1.6         0.4     setosa
## 5          5.7    2.600000          3.5         1.0 versicolor
## 6          5.3    3.462121          4.5         0.2     setosa
# Summary statistics
summary(dirty_iris)
##   Sepal.Length     Sepal.Width      Petal.Length     Petal.Width   
##  Min.   : 0.000   Min.   : 2.200   Min.   : 0.000   Min.   :0.100  
##  1st Qu.: 5.100   1st Qu.: 2.825   1st Qu.: 1.700   1st Qu.:0.300  
##  Median : 5.800   Median : 3.100   Median : 4.500   Median :1.300  
##  Mean   : 5.875   Mean   : 3.462   Mean   : 4.456   Mean   :1.201  
##  3rd Qu.: 6.400   3rd Qu.: 3.462   3rd Qu.: 5.100   3rd Qu.:1.800  
##  Max.   :18.873   Max.   :30.000   Max.   :63.000   Max.   :2.500  
##    Species         
##  Length:150        
##  Class :character  
##  Mode  :character  
##                    
##                    
##