7: Data Cleaning

Readings

From R for Data Science by Drs. Hadley Wickham, Garrett Grolemund, and Mine Çetinkaya-Rundel:

Topics

  • Data cleaning
  • Duplicate observations
  • Missing values
  • Inconsistent formats

Data cleaning

  • Data cleaning refers to the process of preparing “dirty” or “messy” data for further exploration and modeling. It is often the most time-consuming step in a data science project.

  • Although data cleaning is open-ended in nature, it generally involves handling the following issues:

    • Duplicate observations

    • Outliers due to errors

    • Missing values

    • Inconsistent formats

Duplicate observations

  • Duplicate observations can lead to biased results, so they should be identified and discarded.
df <- data.frame(
  FirstName = c('Marco', 'Sharon', 'Martin', 'Marco', 'Tanya', 'Michael'),
  LastName = c('Bray', 'Turner', 'Lewis', 'Bray', 'Jones', 'Watson'),
  Age = c(45, 38, 55, 45, 28, 60),
  BirthPlace = c('New York', 'Chicago', 'Los Angeles', 'New York', 'Boston', 'Houston')
)
  • The duplicated() function in base R can be used to identify observations that already appear earlier.
# for a vector, TRUE if an observation is duplicated
duplicated(df$FirstName)   
[1] FALSE FALSE FALSE  TRUE FALSE FALSE
# for a data frame, TRUE if a row is duplicated
duplicated(df)   
[1] FALSE FALSE FALSE  TRUE FALSE FALSE
# return the duplicated observations
df[duplicated(df), ]
  FirstName LastName Age BirthPlace
4     Marco     Bray  45   New York
# return the non-duplicated (unique) observations
df[!duplicated(df), ] 
  FirstName LastName Age  BirthPlace
1     Marco     Bray  45    New York
2    Sharon   Turner  38     Chicago
3    Martin    Lewis  55 Los Angeles
5     Tanya    Jones  28      Boston
6   Michael   Watson  60     Houston
  • The distinct() function in dplyr is also a convenient function to discard duplicate observations.
library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.5.2
Warning: package 'ggplot2' was built under R version 4.5.1
Warning: package 'lubridate' was built under R version 4.5.1
df |> distinct()
  FirstName LastName Age  BirthPlace
1     Marco     Bray  45    New York
2    Sharon   Turner  38     Chicago
3    Martin    Lewis  55 Los Angeles
4     Tanya    Jones  28      Boston
5   Michael   Watson  60     Houston

Outliers due to errors

  • Outliers can be extreme but valid values but they can also be due to errors. In the latter case, we want to identify and remove them.

  • Consider data on sale price in millions of dollars and size in thousands of sq. ft. of some properties.

properties <- data.frame(
  Size = c(2166, 751, 5, 2422, 224, 3917, 2866, -451, 1698, 1046, 1108, 405),
  Price = c(  10.6, 2.6, 12, 30.5, 1.8, 20.0,  8.0, 12.5, 10.0,  6.7,  5.8, 4.5)
)
  • The data set has some unusual small properties. To remove them, we can do the following:
properties <- properties[properties >= 100, ]

Missing values

  • Missing values refer to data that was not recorded, unavailable, or unknown during data collection or entry. In R, missing values are represented as NA.

  • There are two common approaches to handling missing values:

    • Listwise deletion removes observations with missing values.

    • Imputation replaces missing values with new values. For example, mean imputation replaces missing values in a column by the mean of observed values in that column.

  • The pima data set, originated from the National Institute of Diabetes and Digestive and Kidney Diseases, consists of 768 female patients of Pima Indian heritage, all aged at least \(21\) and living near Phoenix, Arizona, USA. Among these patients, 268 were diagnosed with diabetes. Several medical variables were measured for these patients.

From Şafak Selim Sofioğlu
library(tidyverse)

load(url('https://github.com/hungtong/DS-01-101/raw/refs/heads/main/dataset/pima.rdata'))

pima <- as_tibble(pima)
glimpse(pima)
Rows: 768
Columns: 9
$ Outcome                  <int> 1, 0, 1, 0, 1, 0, 1, 0, 1, 1, 0, 1, 0, 1, 1, …
$ Pregnancies              <int> 6, 1, 8, 1, 0, 5, 3, 10, 2, 8, 4, 10, 10, 1, …
$ Glucose                  <int> 148, 85, 183, 89, 137, 116, 78, 115, 197, 125…
$ BloodPressure            <int> 72, 66, 64, 66, 40, 74, 50, NA, 70, 96, 92, 7…
$ SkinThickness            <int> 35, 29, NA, 23, 35, NA, 32, NA, 45, NA, NA, N…
$ Insulin                  <int> NA, NA, NA, 94, 168, NA, 88, NA, 543, NA, NA,…
$ BMI                      <dbl> 33.6, 26.6, 23.3, 28.1, 43.1, 25.6, 31.0, 35.…
$ DiabetesPedigreeFunction <dbl> 0.627, 0.351, 0.672, 0.167, 2.288, 0.201, 0.2…
$ Age                      <int> 50, 31, 32, 21, 33, 30, 26, 29, 53, 54, 30, 3…
  • is.na() is a useful function to identify missing values in a vector.
# for a vector, TRUE if an observation is a missing value
is.na(pima$Insulin)   
  [1]  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE
 [13]  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE
 [25] FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE
 [37]  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE
 [49]  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE
 [61]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE
 [73]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE
 [85]  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE
 [97]  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[109] FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE
[121] FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE
[133] FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE
[145] FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE
[157] FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
[169]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE
[181]  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE
[193]  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[205] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE
[217] FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE
[229] FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE
[241]  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE
[253] FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE
[265]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[277]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE
[289] FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE
[301]  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE
[313] FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE FALSE
[325]  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE
[337]  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[349] FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE
[361] FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
[373] FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE
[385] FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE
[397] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
[409]  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[421] FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE
[433] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[445]  TRUE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE
[457]  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE
[469]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[481] FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE
[493]  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
[505]  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE
[517] FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE
[529] FALSE  TRUE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE
[541] FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
[553]  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE
[565]  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
[577] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE
[589] FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE
[601]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE
[613] FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE
[625]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE
[637]  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE
[649] FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE
[661]  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[673] FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE
[685]  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE
[697] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE
[709]  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE
[721]  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE
[733] FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE
[745] FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[757]  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE
# number of missing values for Insulin
sum(is.na(pima$Insulin))   
[1] 374
# number of observed values for Insulin
sum(!is.na(pima$Insulin))
[1] 394
# all observed values in Insulin
pima$Insulin[!is.na(pima$Insulin)]  
  [1]  94 168  88 543 846 175 230  83  96 235 146 115 140 110 245  54 192 207
 [19]  70 240  82  36  23 300 342 304 110 142 128  38 100  90 140 270  71 125
 [37]  71 110 176  48  64 228  76  64 220  40 152 140  18  36 135 495  37 175
 [55]  51 100 100  99 135  94 145 168 225  49 140  50  92 325  63 284 119 204
 [73] 155 485  94 135  53 114 105 285 156  78 130  48  55 130 130  92  23 495
 [91]  58 114 160  94 210  48  99 318  44 190 280  87 130 175 271 129 120 478
[109] 190  56  32 744  53 370  37  45 192  88 176 194 680 402  55 258 375 150
[127] 130  67  56  45  57 116 278 122 155 135 545 220  49  75  40  74 182 194
[145] 120 360 215 184 135  42 105 132 148 180 205 148  96  85  94  64 140 231
[163]  29 168 156 120  68  52  58 255 171 105  73 108  83  74  43 167  54 249
[181] 325 293  83  66 140 465  89  66  94 158 325  84  75  72  82 182  59 110
[199]  50 285  81 196 415  87 275 115  88 165 579 176 310  61 167 474 115 170
[217]  76  78 210 277 180 145 180  85  60  50 120  14  70  92  64  63  95 210
[235] 105  71 237  60  56  49 105  36 100 140 191 110  75 328  49 125 250 480
[253] 265  66 122  76 145 193  71  79  90 170  76 210  86 105 165 326  66 130
[271]  82 105 188 106  65  56 210 155 215 190  56  76 225 207 166  67 106  44
[289] 115 215 274  77  54  88  18 126 126 165  44 120 330  63 130 600 156 140
[307] 115 230 185  25 120 126 293  41 272 182 158 194 321 144  15 160 115  54
[325]  90 183  66  91  46 105 152 440 144 159 130 100 106  77 135 540  90 200
[343]  70 231 130 132 190 100 168  49 240 265  45 105 205 180 180  95 125 480
[361] 125 155 200 100 335 160 387  22 291 392 185 178 200 127 105 180  79 120
[379] 165 120 160 150  94 116 140 105  57 200  74 510 110  16 180 112
# mean of all observed values in Insulin
mean(pima$Insulin[!is.na(pima$Insulin)])   
[1] 155.5482
  • complete.cases() allows us to quickly identify observations without missing values in a data frame.
# for a data frame, TRUE if a row has NO missing value
complete.cases(pima)  
  [1] FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE
 [13] FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE
 [25]  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE
 [37] FALSE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE
 [49] FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE
 [61] FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE
 [73] FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE
 [85] FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
 [97] FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE
[109]  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE
[121]  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE
[133]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE FALSE
[145]  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE
[157]  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE
[169] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE
[181] FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE
[193] FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[205]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE
[217]  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE
[229]  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE
[241] FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE
[253]  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE
[265] FALSE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE
[277] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE
[289]  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE
[301] FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE
[313]  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE  TRUE
[325] FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE
[337] FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE
[349]  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE
[361]  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[373]  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
[385]  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE
[397]  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE
[409] FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[421]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE
[433]  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
[445] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[457] FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE
[469] FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[481]  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE
[493] FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[505] FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE
[517]  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE
[529]  TRUE FALSE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE
[541]  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[553] FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE
[565] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE  TRUE
[577]  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE
[589]  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE
[601] FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE
[613]  TRUE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE
[625] FALSE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE
[637] FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE
[649]  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE
[661] FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[673]  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE
[685] FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE
[697]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE
[709] FALSE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE
[721] FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE
[733]  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE
[745]  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE
[757] FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE
# number of rows WITHOUT missing values
sum(complete.cases(pima)) 
[1] 392
# return rows WITHOUT missing values
pima[complete.cases(pima), ]  
# A tibble: 392 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       0           1      89            66            23      94  28.1
 2       1           0     137            40            35     168  43.1
 3       1           3      78            50            32      88  31  
 4       1           2     197            70            45     543  30.5
 5       1           1     189            60            23     846  30.1
 6       1           5     166            72            19     175  25.8
 7       1           0     118            84            47     230  45.8
 8       0           1     103            30            38      83  43.3
 9       1           1     115            70            30      96  34.6
10       0           3     126            88            41     235  39.3
# ℹ 382 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
  • If our interest is to identify missing values, we can pair complete.cases() with the not ! operator.
# TRUE if a row DOES HAVE a missing value
!complete.cases(pima)
  [1]  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE
 [13]  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE
 [25] FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE
 [37]  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE
 [49]  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE
 [61]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE
 [73]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE
 [85]  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE FALSE
 [97]  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[109] FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE
[121] FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE
[133] FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE
[145] FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE
[157] FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
[169]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE
[181]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE
[193]  TRUE  TRUE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[205] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE
[217] FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE
[229] FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE
[241]  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE  TRUE
[253] FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE
[265]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[277]  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE
[289] FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE
[301]  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE
[313] FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE FALSE
[325]  TRUE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE
[337]  TRUE  TRUE FALSE  TRUE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[349] FALSE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE
[361] FALSE  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[373] FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE
[385] FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE
[397] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE
[409]  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE
[421] FALSE FALSE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE
[433] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE
[445]  TRUE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE  TRUE
[457]  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE
[469]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[481] FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE
[493]  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
[505]  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE
[517] FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE FALSE FALSE
[529] FALSE  TRUE FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE FALSE
[541] FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
[553]  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE
[565]  TRUE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
[577] FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE
[589] FALSE  TRUE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE
[601]  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE
[613] FALSE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE FALSE
[625]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE
[637]  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE
[649] FALSE  TRUE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE  TRUE FALSE
[661]  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE
[673] FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE  TRUE FALSE  TRUE
[685]  TRUE FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE  TRUE FALSE
[697] FALSE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE
[709]  TRUE FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE  TRUE FALSE  TRUE
[721]  TRUE FALSE FALSE FALSE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE FALSE  TRUE
[733] FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE  TRUE
[745] FALSE FALSE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
[757]  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE
# number of rows WITH missing values
sum(!complete.cases(pima))
[1] 376
# return rows WITH missing values
pima[!complete.cases(pima), ]
# A tibble: 376 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       1           6     148            72            35      NA  33.6
 2       0           1      85            66            29      NA  26.6
 3       1           8     183            64            NA      NA  23.3
 4       0           5     116            74            NA      NA  25.6
 5       0          10     115            NA            NA      NA  35.3
 6       1           8     125            96            NA      NA  NA  
 7       0           4     110            92            NA      NA  37.6
 8       1          10     168            74            NA      NA  38  
 9       0          10     139            80            NA      NA  27.1
10       1           7     100            NA            NA      NA  30  
# ℹ 366 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
  • na.omit() removes rows with missing values.
na.omit(pima)
# A tibble: 392 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       0           1      89            66            23      94  28.1
 2       1           0     137            40            35     168  43.1
 3       1           3      78            50            32      88  31  
 4       1           2     197            70            45     543  30.5
 5       1           1     189            60            23     846  30.1
 6       1           5     166            72            19     175  25.8
 7       1           0     118            84            47     230  45.8
 8       0           1     103            30            38      83  43.3
 9       1           1     115            70            30      96  34.6
10       0           3     126            88            41     235  39.3
# ℹ 382 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
  • drop_na() is a more flexible function to remove rows with missing values
# remove all rows with missing values
pima |> drop_na()
# A tibble: 392 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       0           1      89            66            23      94  28.1
 2       1           0     137            40            35     168  43.1
 3       1           3      78            50            32      88  31  
 4       1           2     197            70            45     543  30.5
 5       1           1     189            60            23     846  30.1
 6       1           5     166            72            19     175  25.8
 7       1           0     118            84            47     230  45.8
 8       0           1     103            30            38      83  43.3
 9       1           1     115            70            30      96  34.6
10       0           3     126            88            41     235  39.3
# ℹ 382 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
# remove only rows with missing values on BMI
pima |> drop_na(BMI)
# A tibble: 757 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       1           6     148            72            35      NA  33.6
 2       0           1      85            66            29      NA  26.6
 3       1           8     183            64            NA      NA  23.3
 4       0           1      89            66            23      94  28.1
 5       1           0     137            40            35     168  43.1
 6       0           5     116            74            NA      NA  25.6
 7       1           3      78            50            32      88  31  
 8       0          10     115            NA            NA      NA  35.3
 9       1           2     197            70            45     543  30.5
10       0           4     110            92            NA      NA  37.6
# ℹ 747 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
# remove only rows with missing values on BMI and SkinThickness
pima |> drop_na(BMI, SkinThickness)
# A tibble: 539 × 9
   Outcome Pregnancies Glucose BloodPressure SkinThickness Insulin   BMI
     <int>       <int>   <int>         <int>         <int>   <int> <dbl>
 1       1           6     148            72            35      NA  33.6
 2       0           1      85            66            29      NA  26.6
 3       0           1      89            66            23      94  28.1
 4       1           0     137            40            35     168  43.1
 5       1           3      78            50            32      88  31  
 6       1           2     197            70            45     543  30.5
 7       1           1     189            60            23     846  30.1
 8       1           5     166            72            19     175  25.8
 9       1           0     118            84            47     230  45.8
10       0           1     103            30            38      83  43.3
# ℹ 529 more rows
# ℹ 2 more variables: DiabetesPedigreeFunction <dbl>, Age <int>
  • Mean imputation can be done as follow
# method 1
pima$Insulin[is.na(pima$Insulin)] <- mean(pima$Insulin[!is.na(pima$Insulin)]) 

# method 2
pima$Insulin[is.na(pima$Insulin)] <- mean(pima$Insulin, na.rm = TRUE)

Inconsistent Format

  • Inconsistent formats commonly occur with categorical and date/time variables.

  • The tips data set contains information about restaurant bills, tips, and customers. Each observation represents a single dining party.

  • tota_bill - Total bill amount (in dollars) for the meal, before the tip

  • tip - Tip amount (in dollars) left by the customer

  • sex - Gender of the person paying the bill.

  • day - Day of the week when the meal occurred.

  • time- Time of day the meal took place: 1 for lunch and 2 for dinner

  • size - Number of people in the dining party.

load(url('https://github.com/hungtong/DS-01-101/raw/refs/heads/main/dataset/tips.rdata'))

glimpse(tips)
Rows: 30
Columns: 6
$ tota_bill <dbl> 22.49, 35.83, 24.06, 20.65, 18.29, 41.19, 16.45, 18.28, 25.2…
$ tip       <dbl> 3.50, 4.67, 3.60, 3.35, 3.00, 5.00, 2.47, 4.00, 4.71, 3.41, …
$ sex       <chr> "male", "Female", "M", "male", "Male ", "MALE", "female", "m…
$ day       <chr> "Fri", "Saturday", "sat", "SAT", "Sun", "Thur", "Sat", "thur…
$ time      <dbl> 2, 2, 2, 2, 2, 1, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 1, 2, …
$ size      <dbl> 2, 3, 3, 3, 2, 5, 2, 2, 4, 3, 2, 3, 2, 2, 2, 2, 3, 4, 4, 4, …
  • The categories of sex and day are not consistent. To fix sex, we can do the following:
tips$sex[tips$sex %in% c('f', 'F', 'female', 'Female', 'FEMALE', 'female', 'Female', 'female ', 'Female ')] <- 'female'

tips$sex[tips$sex %in% c('m', 'M', 'male', 'Male', 'MALE', 'Male', 'Male ')] <- 'male'