![](../hanze/hanze.png)

---

[Go back to the main page](../index.html)  
[Go back to the R overview page](../R/R_01_index.html)  

---

```{r setup, include=FALSE} 
knitr::opts_chunk$set(warning = FALSE, message = FALSE) 
```

# R

## Data Cleaning Solutions

Loading Tidyverse:

```{r}
library(tidyverse)
```

>The following function is used to print tibbles in a proper way for the web. You can skip the use of this function to print tibbles to your screen in R Markdown documents.


```{r}
library(knitr)
library(kableExtra)
library(pillar)

formatted_table <- function(df) {
  col_types <- sapply(df, pillar::type_sum)
  new_col_names <- paste0(names(df), "<br>", "<span style='font-weight: normal;'>", col_types, "</span>")
  kbl(df, 
      col.names = new_col_names, 
      escape = FALSE, # This is crucial to allow the <br> tag to work
      format = "html" # Ensure HTML format, although often auto-detected
      ) %>%
    kable_styling(bootstrap_options = c("striped", "hover", "responsive"))
}
```

This file can be downloaded [here](./R_08_data_cleaning_solutions.Rmd)

### Exercise 1

[This](./files_07_data_cleaning_exercises/exercise01/Heart_Disease_Prediction.xlsx) first data set was not loaded well in Excel. Load the Excel file as a tibble in R. Separate the columns correctly.
*Hint:* use the `separate_wider_delim` function from tidyverse. Consult the information on this function with `?separate_wider_delim`. On the bottom you will find examples you can run. Take a close look at the first example. This should give you a clue how to solve this problem.

```{r}
library(readxl)
file_path <- "./files_07_data_cleaning_exercises/exercise01/Heart_Disease_Prediction.xlsx"
df <- read_excel(file_path)
colname <- "index$Age$Sex$Chest pain type$BP$Cholesterol$FBS over 120$EKG results$Max HR$Exercise angina$ST depression$Slope of ST$Number of vessels fluro$Thallium$Heart Disease"
df <- df %>% 
  separate_wider_delim(colname, delim = "$", names = c("index", "Age", "Sex", "Chest pain type" ,"BP", "Cholesterol", "FBS over 120", "EKG results", "Max HR", "Exercise angina", "ST depression", "Slope of ST", "Number of vessels fluro", "Thallium", "Heart Disease"))
formatted_table(head(df, c(6, 13)))
```


### Exercise 2

The data in [this](./files_07_data_cleaning_exercises/exercise02/fish_data_mod.csv) file contains the units in the cells. This makes it impossible to perform calculations (as the data type is a text string). Remove the units in order to make calculations possible.

```{r}
file_path <- "./files_07_data_cleaning_exercises/exercise02/fish_data_mod.csv"
df <- read_csv2(file_path)
df <- df %>% 
  separate_wider_delim(average_length, delim = " ", names = c("average_length", "length_unit")) %>% 
  separate_wider_delim(average_weight, delim = " ", names = c("average_weight", "weight_unit")) %>%
  separate_wider_delim(life_span, delim = " ", names = c("life_span", "time_unit")) %>%
  select (-c(length_unit, weight_unit, time_unit)) %>% #Note that this is a negative selection. Selects all but the listed columns.
  mutate_at(c('average_length', 'average_weight', 'life_span'), as.numeric) #Creates numbers from strings. mutate_at acts on multiple columns
formatted_table(head(df))
```

### Exercise 3

[This](./files_07_data_cleaning_exercises/exercise03/Heart_Disease_Prediction_mod.xlsx) data set contains rows with duplicate data. Load the data and remove the duplicates from the data table.

```{r}
file_path <- "./files_07_data_cleaning_exercises/exercise03/Heart_Disease_Prediction_mod.xlsx"
df <- read_excel(file_path)
formatted_table(head(df, c(6, 13)))
```

Let's first find duplicate elements in the first column. The Index should be a unique number, so duplicates should be easily found in this column:

```{r}
df$index[duplicated(df$index)]
```

Or in tidyverse style:

```{r}
df %>%
  filter(duplicated(index)) %>%
  pull(index)
```


Yes, there seems to be duplicates of the rows with index numbers 8 (2 extra duplicates) and 10 (5 extra duplicates).  
NOTE: R shows the extra duplicates here (in total there are 3 rows with index 8 and 6 rows with index 10; see below).

Do the rows with index numbers 8 and 10 contain the same data?

```{r}
df %>%
  filter(index == 8) %>%
  formatted_table()
```

```{r}
df %>%
  filter(index == 10) %>%
  formatted_table()
```



Yes, the rows with index numbers 8 and 10 contain the same data in the rows.
Before we delete the duplicates, let us count the number of rows:

```{r}
nrow(df)
```


Now we can delete the duplicated rows:

```{r}
my_data_cleaned <- df[!duplicated(df$index), ]
formatted_table(head(my_data_cleaned, c(6,13)))
```

Or tidyverse style:

```{r}
my_data_cleaned <- df %>%
  # 1. Remove rows with duplicated 'index' values
  distinct(index, .keep_all = TRUE) %>%
  # 3. Select the first 13 columns
  select(1:13)
formatted_table(head(my_data_cleaned))
```


```{r}
nrow(my_data_cleaned)
```

Note that there are 270 rows in the above tibble. Seven rows less compared to the tibble we started with (277).

Check if we still have duplicated rows:

```{r}
my_data_cleaned$index[duplicated(my_data_cleaned$index)]
```

This yielded an empty vector meaning the tibble does not contain duplicated rows anymore.

### Exercise 4

[This](./files_07_data_cleaning_exercises/exercise04/CHEMBL1989.csv) data set contains empty data.
Make the empty elements more explicit in R by converting them to `NA`.

```{r}
file_path <- "./files_07_data_cleaning_exercises/exercise04/CHEMBL1989.csv"
df <- read_csv(file_path)
formatted_table(head(df, c(6, 5)))
```

You see that also the `read_csv` function automatically replaces empty fields with `NA`.

Count how many elements you have:

- in total
- with missing data
- without missing data

```{r}
paste("total elements:", nrow(df), "x", ncol(df), "=", nrow(df) * ncol(df))
paste("empty elements:", sum(is.na(df)))
paste("non-empty elements:", sum(!is.na(df)))
```


### Exercise 5

[This](./files_07_data_cleaning_exercises/exercise05/CHEMBL1989_mod.csv) also contains missing data in many fields. However, instead of leaving the cells empty, the author used a character to indicate a missing value. Open the file in a text editor to find what character has been used for missing data and replace this character with `NA`.

```{r}
file_path <- "./files_07_data_cleaning_exercises/exercise05/CHEMBL1989_mod.csv"
df <- read_csv2(file_path)
df <- df %>% 
  replace(. == "-", NA)
formatted_table(head(df, c(6, 5)))
```

---

[Go back to the main page](../index.html)  
[Go back to the R overview page](../R/R_01_index.html)  
<a href="#top">⬆️ Back to Top</a>  

---


>This web page is distributed under the terms of the Creative Commons Attribution License which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited.
>Creative Commons License: CC BY-SA 4.0.

