---
session: "Session 2"
title: "Data Preparation"
session_id: session-02
format:
  html: default
---


In this exercise, we cover:

| Exercise part                          | Time (min) |
|----------------------------------------|------------|
| [Data structuring 1](#structuring_1)   |     15     |
| [Data structuring 2](#structuring_2)   |     20     |
| [Data cleansing](#cleansing)           |     20     |
| [Wrap-up](#wrap-up)                    |     10     |
| Overall                                |     65     |

**Recommended workflow:**

1. ✏️ **Think & plan first:** Draft your answers and outline the transformation steps before coding.
2. 💻 **Then code:** Work directly in this Quarto (`.qmd`) exercise using R in RStudio, Positron, or VS Code.

**Recommendation:** For data preparation, consult the [Tidyverse packages](https://www.tidyverse.org/packages/), in particular:

- [tidyr](https://tidyr.tidyverse.org/) for reshaping and restructuring data;
- [dplyr](https://dplyr.tidyverse.org/) for filtering, transforming, and summarizing data;
- [stringr](https://stringr.tidyverse.org/) for cleaning and manipulating text values.

## Package setup

Before starting this exercise, make sure the required R packages are installed.

Run this command once in the R console. You do not need to run it every time you open the exercise. You can skip this step if these packages are already installed.

```{r}
#| eval: false
install.packages("tidyverse")
```


# Part 1: Data structuring 1 {#structuring_1}

We start with the `market_data_log.csv` dataset.

```{r}
#| warning: false
library(tidyverse)

market_data_log_df <- read_csv(
  "https://raw.githubusercontent.com/fs-ise/machine-learning-for-big-data/main/exercises/data/market_data_log.csv",
  show_col_types = FALSE
)
market_data_log_df |> head()
```


## Tasks

1. Identify:

   * Values: *Write your answer here.*
   * Observations: *Write your answer here.*
   * Variables: *Write your answer here.*
   * Units of observation: *Write your answer here.*

2. Draft the **tidy structure** of the dataset:

   * Fill in the column names and at least 3 example rows.

**Your answer:** Enter the column names and example values in the empty table.

|   |   |   |   |   |   |   |   |
|---|---|---|---|---|---|---|---|
|   |   |   |   |   |   |   |   |
|   |   |   |   |   |   |   |   |
|   |   |   |   |   |   |   |   |


3. Describe how the dataset should be transformed.

**Your answer:** *Write your answer here.*

4. **(Optional challenge)**: Implement the transformation in R (`tidyverse`)

```{r}
market_data_df <- read_csv(
  "https://raw.githubusercontent.com/fs-ise/machine-learning-for-big-data/main/exercises/data/market_data_log.csv",
  show_col_types = FALSE
)

# YOUR CODE HERE
# Note: you may need string operations.
```

💡 If you didn’t get to this part in class, try working through it at home.





# Part 2: Data structuring 2 {#structuring_2}

Dataset `sales_data_channels_full_year.csv`

```{r}
sales_data_df <- read_csv(
  "https://raw.githubusercontent.com/fs-ise/machine-learning-for-big-data/main/exercises/data/sales_data_channels_full_year.csv",
  show_col_types = FALSE
)
sales_data_df |> head()
```


## Tasks

1. Identify:

   * Values: *Write your answer here.*
   * Observations: *Write your answer here.*
   * Variables: *Write your answer here.*
   * Units of observation: *Write your answer here.*

2. Draft the **tidy structure** of the dataset:


**Your answer:** Enter the column names and example values in the empty table.

|   |   |   |   |   |
|---|---|---|---|---|
|   |   |   |   |   |
|   |   |   |   |   |
|   |   |   |   |   |

3. Describe how the dataset should be transformed.

**Your answer:** *Write your answer here.*

4. **(Optional challenge)**: Implement the transformation in R (`tidyverse`)

```{r}
sales_data_df <- read_csv(
  "https://raw.githubusercontent.com/fs-ise/machine-learning-for-big-data/main/exercises/data/sales_data_channels_full_year.csv",
  show_col_types = FALSE
)

# YOUR CODE HERE
```

💡 In addition to the transformation, you may need string operations



# Part 3: Data cleansing {#cleansing}

Dataset `messy_customer_data.csv`

```{r}
customer_df <- read_csv(
  "https://raw.githubusercontent.com/fs-ise/machine-learning-for-big-data/main/exercises/data/messy_customer_data.csv",
  na = c("", "NA", "NaN"),
  show_col_types = FALSE
)
customer_df |> head()
```





## Tasks

In this part, move from **detecting problems** to **making the dataset analysis-ready**. The goal is not only to spot errors, but also to document a reproducible cleaning strategy.

### 1. Identify scales of variables

* What type of scale is each variable?

**Your answer:** *Write your answer here.*
  Run the following and assign `nominal`, `ordinal`, `interval`, or `ratio`.

```{r}
#| output: false

customer_df |> glimpse()
```


* What type of scale should each variable be?

**Your answer:** *Write your answer here.*
  Assign `nominal`, `ordinal`, `interval`, or `ratio`.

  - customer_id: ________________
  - date_of_birth: ________________
  - income: ________________
  - gender: ________________
  - signup_date: ________________
  - country: ________________



### 2. Analyze data quality

Examine the dataset `data/messy_customer_data.csv` and run the following:

```{r}
summary(customer_df)
```

* Summarize all data quality issues you observe (open the [data/messy_customer_data.csv](data/messy_customer_data.csv)).

**Your answer:** *Write your answer here.*

### 3. Propose data preparation steps

* For each data quality issue, suggest how you would address it.

**Your answer:** *Write your answer here.*

### 4. *(Optional challenge)* Implement in R (`tidyverse`)

* Implement the data preparation steps in R.
* Run the code after each step and inspect the results.
* For `income`, do not only remove the currency label. Extract the numeric amount
  and currency into separate variables, then harmonize all amounts to EUR before
  calculating the median or imputing missing values.
  - Treat an amount without an explicit currency label as EUR.
  - For this exercise, use the fixed illustrative rate **1 USD = 0.90 EUR**. This
    is a teaching assumption, not a current market exchange rate.
  - Treat `NA`, `NaN`, and empty strings as missing, and impute them with the
    median only after every observed amount has been converted to EUR.
  - Store the final numeric EUR values in `income` so that the later cleaning
    steps still work, and add a short check of the USD conversion and missing
    value handling.

```{r}
# YOUR CODE HERE
```

Note: Check how the `customer_df` changes after each preparation step!







## Wrap-up {#wrap-up}

🎉🎈 You have completed the exercise - good work! 🎈🎉

In this exercise, we have learned to

* Structure data in a tidy format
* Prepare and explore datasets