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

------------------------------------------------------------------------

<!-- start of the main lab content -->

Throughout the Lab 2, we will learn how to do data wrangling and explore two real estate datasets of King County maintained by [Andy Krause](https://www.andykrause.com/index.html) at Zillow:

1.  All single family and townhouse sales from 1999 through December 2023.
2.  All single family and townhouse properties as of December 31, 2023.

You can find the datasets and Readme file in [this repository](https://github.com/andykrause/kingCoData).

The due day for each lab can be found on the [course wesbite](https://www.yuehaoyu.com/data-analytics-visualization/). The submission should include Rmd, html, and any other files required to rerun the codes. **For Lab 1–3, the use of any generative AI tool (ChatGPT, Copilot, etc.) is prohibited**. We will run AI detection on each submission. More information about [Academic Integrity](https://www.yuehaoyu.com/data-analytics-visualization/syllabus/#academic-integrity) and [the Use of AI](https://www.yuehaoyu.com/data-analytics-visualization/syllabus/#use-of-ai).

> Recommended Reading: [Chapter 4-5, Modern Data Science with R, 3rd edition](https://mdsr-book.github.io/mdsr3e/)

## Lab 02-A: Modifying Dataframes

Let's first import the datasets and necessary packages. After installing this package with `devtools::install_github('andykrause/kingCoData')`, you can load the data with: `data(kingco_sales)` and `data(kingco_homes)`. You can find the datasets and Readme file in [this repository](https://github.com/andykrause/kingCoData).

```{r message=FALSE}
library('tidyverse')
#devtools::install_github('andykrause/kingCoData') # You only need to run the installation once
library('kingCoData') # load the data package
data(kingco_sales) # load the sale data
```

Have a look of this data through `View()` function or just click the dataset name in `Environment` panel, we can know there are 591,513 entries (records), 48 columns (features). Go ahead and see what features do we have for each record using `?`. Note: it only shows help if the dataset comes from a documented data package, like we did here.

```{r}
# check all column names
colnames(kingco_sales)
```

```{r}
?kingco_sales
```

We saw a brunch of variables but let's talk about the first two:

-   `sale_id`: The unique transaction identifying code. Primary key for this data! Serves as the **primary key** of the dataset — each row has a distinct `sale_id`.
-   `pinx`: The unique property identifying code (Major + Minor). Serves as a **foreign key** linking sales to property records, which is `kingco_homes` in our case.

### Pipe - A Unique Feature of R

One of the most distinctive features of R (especially through the tidyverse) is the pipe operator `%>%` or `|>`.

-   chain commands together in a clear, left-to-right order.
-   Instead of nesting multiple functions, you can read code step by step.
-   closer to natural language and easier to follow in data analysis workflows.

| System        | Windows/Linux    | Mac             |
|---------------|------------------|-----------------|
| Pipe Shortcut | Ctrl + Shift + M | Cmd + Shift + M |

```{r}
# example without pipe
mean(log(sqrt(c(1, 4, 9, 16))))
# example with pipe
c(1, 4, 9, 16) %>%
  sqrt() %>%
  log() %>%
  mean()
```

### Simple Sampling from Dataframes

We can randomly select some rows from the data using `sample_n()`, every transaction has the same chance of being picked.

```{r}
random_sample <- kingco_sales %>%
  sample_n(3)
random_sample
```

The default setting is **without replacement**, which means each row can appear only once. But we can add additional augment like `sample_n(5, replace = TRUE)`, so that the same row can appear more than once.

If we run the code above several time, we will get different results! Others will not get same results as you did, the codes is NOT reproducible. So, we usually fix the random number generator using `set.seed()`.

```{r}
set.seed(413) # set seed, then running the same sampling code again will give the same rows
random_sample <- kingco_sales %>%
  sample_n(3)
random_sample
```

### Select Rows or Columns

We use `select()` to select and create new dataframe with certain columns. Let's select a few columns and see whether there are some transactions for the property that can see both Lake Washington and Lake Sammammish.

![The `select()` function. At left, a data frame, from which we retrieve only a few of the columns. At right, the resulting data frame after selecting those columns. Source: Modern Data Science with R.](https://mdsr-book.github.io/mdsr3e/gfx/select-1.png)

```{r}
# select a few useful columns
kingco_sales_subset <- kingco_sales %>% 
  select(sale_id, city, sale_price, sale_date, view_lakewash, view_lakesamm)
```

We use `filter()` function to filter certain rows based on some conditions. We discussed the logical operations in Lab 1, such as `==`, `!=`, `>`, `<=`, `TRUE`, `&`, and `|`.

![The `filter()` function. At left, a data frame that contains matching entries in a certain column for only a subset of the rows. At right, the resulting data frame after filtering. Source: Modern Data Science with R.](https://mdsr-book.github.io/mdsr3e/gfx/filter-1.png)

```{r}
kingco_sales_twolakes <- kingco_sales_subset %>%
  filter(
    view_lakewash == 1, 
    view_lakesamm == 1,
    sale_date > as.Date("2020-01-01") # after year of 2020 
  ) 
nrow(kingco_sales_twolakes)
```

Interesting, there were only 10 transactions of properties with views of both Lake Washington and Lake Sammammish after the onset of the pandemic (2020). Let's look at how many of them are below 1.5 millions.

```{r}
kingco_sales_twolakes %>% 
  filter(
    sale_price < 1500000, # price below 1.5 million
  )
```

If I want to know if any of them are in the BRK region (Bellevue, Redmond, Kirkland), `%in%` checks whether a value belongs to a set of values:

```{r}
kingco_sales_twolakes %>% 
  filter(
    sale_price < 1500000, # price below 1.5 million
    city %in% c('BELLEVUE', 'REDMOND', 'KIRKLAND')
  )
```

We can also remove column(s) by using `select(-)`, for example, removing the columns `view_lakewash` and `view_lakesamm` from dataframe `kingco_sales_twolakes`:

```{r}
kingco_sales_twolakes_clean <- kingco_sales_twolakes %>% 
  select(-c(view_lakewash, view_lakesamm))
head(kingco_sales_twolakes_clean,3)
```

We can combine those operation into single chunk of code. Suppose we want to know where are those two properties and some basic information of those properties.

```{r}
kingco_sales %>%
  filter(
    view_lakewash == 1, 
    view_lakesamm == 1,
    sale_date >= as.Date("2020-01-01"), 
    sale_price < 1500000
  ) %>% 
  select(pinx, submarket, sale_price, sqft, year_built, grade)
```

If I want to have the view to both lakes, it will cost me a lot. Sad.

### Sort Based on Values

The function `sort()` will sort a vector but not a data frame. The `arrange()` function sorts a data frame.

![The `arrange()` function. At left, a data frame with an ordinal variable. At right, the resulting data frame after sorting the rows in descending order of that variable. Source: Modern Data Science with R.](https://mdsr-book.github.io/mdsr3e/gfx/arrange-1.png)

```{r}
kingco_sales_twolakes %>% 
  arrange(desc(sale_price)) 
```

Note: We use `desc()` because the default sort order is ascending. If we want ascending order, we don’t need to provide any argument.

### Create, Re-define, and Rename Variables

Currently, we have the columns for view to Lake Washington and Lake Sammammish separately, we can use `mutate` to create a new 1/0 binary variable to indicate. Meanwhile, it's hard to count the number like 1390000; let's add a new variable with million dollars as the unit.

![The `mutate()` function. At right, the resulting data frame after adding a new column. Source: Modern Data Science with R.](https://mdsr-book.github.io/mdsr3e/gfx/mutate-1.png)

```{r}
kingco_sales_add <- kingco_sales %>% 
  mutate(
    sale_price_million = sale_price / 1e6, # 1e6 is scientific notation for 1 * 10^6 = 1,000,000
    view_likes = ifelse(view_lakewash == 1 & view_lakesamm == 1, 1, 0)
  )
```

Here we use `ifelse(test, yes, no)`, which is a vectorized conditional function in R: if the test condition is TRUE, it returns the value in yes, and vice versa.

Oops, we wrote `view_likes` as the column name for the binary variable to indicate with the view to both LAKES. We can use `rename()` function to change.

```{r}
# `kingco_sales_add <- kingco_sales_add`: overwrite it with a modified version of itself after applying more transformations using %>%
kingco_sales_add <- kingco_sales_add %>% 
  rename(view_lakewash_lakesamm = view_likes)
```

We can recode variables based on some conditions. For example, we want, in `city` of `kingco_sales_twolakes`, BRK region can be represented by `BRK` rather than separate city names. This operation is based on `recode()`. Similarly, `case_when()` can be used for more flexible recoding, creating categories based on sale price ranges.

```{r}
kingco_sales_twolakes %>%
  mutate(
    price_category = case_when(
      sale_price < 500000   ~ "Low",
      sale_price < 1000000  ~ "Mid",
      TRUE                  ~ "High" # for others, we set as 'High'
    ),
    city_group = recode(
      city,
      "BELLEVUE" = "BRK",
      "REDMOND"  = "BRK",
      "KIRKLAND" = "BRK"
    )
  )
```

<!-- TODO: Modifying Dataframes-->

### 📚 TODO: Modifying Dataframes

**6 points**

Using the dataset `kingco_homes` and the operations we learned, especially pipe ( %\>% ), to finish following tasks:

1.  Select the columns: `pinx`, `city`, `land_val`, `imp_val`, `sqft`, `view_lakewash`, `view_lakesamm`
2.  Filter the rows: all properties in Bellevue
3.  Add a new variable called `total_val` which represents the sum of `land_val` (land value) and `imp_val` (improvements value)
4.  Sort the dataframe based on `total_val` in descending order, and show the first 10 records using `head()`
5.  Calculate the average (mean value) and variance of `total_val` for:
    -   all properties in Bellevue
    -   all properties with the view to both Lake Washington and Sammammish in Bellevue
6.  Report the difference in average and variance

```{r}
# TODO
```

------------------------------------------------------------------------

## Lab 02-B: Summarizing and Joining Dataframes

### Summarizing Data

`summary()` is a base R built-in function that provides quick descriptive statistics. For dataframes, it applies `summary()` to each variable.

```{r}
summary(kingco_sales[, c("pinx", "sale_date", "land_val")])
```

You may notice that we are using `kingco_sales[, c("pinx", "sale_date", "land_val")]` instead of `select()` to get the columns. This is the base R way of extracting something from vectors, lists, or even dataframes , while `select()` comes from `dplyr`. Both return the same result, but `select()` is usually preferred in a tidyverse workflow because it is more readable and flexible. You can learn more about `[]` by looking at `?Extract`.

We always want to know more than what `summary()` can provide, `summarize()` (same as `summarise()`) is a function in `dplyr` to summarise each group down to one row. But how do we want to summarise a dataframe? Let's test with our `kingco_sales` data.

![The summarize() function. At left, a data frame. At right, the resulting data frame after aggregating four of the columns. Source: Modern Data Science with R.](https://mdsr-book.github.io/mdsr3e/gfx/summarize-1.png) Suppose we want to explore all transactions in Seattle after Jan 1, 2020.

```{r}
kingco_sales %>% 
  filter( # find all transactions in Seattle after Jan 1, 2020 first
    city == "SEATTLE",
    sale_date >= as.Date("2020-1-1")
  ) %>% 
  summarise(
    N = n(), # number of rows
    avg_sale_price = mean(sale_price), # average sale price
    highest_sale_price = max(sale_price), # max sale price
    percent_view_rainier = sum(view_rainier)/n() * 100, # percentage of those sales with a Rainier view
    unique_property = length(unique(pinx)) # the number of unique property in all transactions
  )
```

```{r}
colnames(kingco_sales)
```

### Groupby

One important and useful function is `group_by`. We know that, before giving data to `summarise()`, we already select all transactions in Seattle. However, many times, we care about the differences across the groups, like Seattle vs. Redmond. Those city information are categorical data in the column `city`. We can easily use `group_by` to summarise by group. Now, we want to explore all transactions after Jan 1, 2020 across cities.

```{r}
kingco_sales %>% 
  filter( # find all transactions after Jan 1, 2020 first
    sale_date >= as.Date("2020-1-1")
  ) %>% 
  group_by(city) %>% # group by city and same `summarise()` will be carried out for each city
  summarise(
    N = n(), 
    avg_sale_price = mean(sale_price), 
    highest_sale_price = max(sale_price), 
    percent_view_rainier = sum(view_rainier)/n() * 100,
    unique_property = length(unique(pinx))
  ) %>% 
  arrange(desc(avg_sale_price))
```

### Joining Dataframes

We mentioned two variables are keys: `sale_id` and `pinx`. `pinx`, property ID, exists in both datasets, which serves as the **primary key** for `kingco_homes` — each row has a distinct `pinx`. Meanwhile, it is a **foreign key** in `kingco_sales`: in `kingco_sales`, same property can associate with more than one transactions.

| Variable | kingco_sales | kingco_homes |
|-------------------|--------------------------|--------------------------|
| sale_id | ✔️ (unique transaction ID, primary key) | ❌ (not included) |
| pinx | ✔️ (property ID, foreign key) | ✔️ (property ID, primary key) |

If we want to join those two dataframes, `pinx` is the only "shared knowledge" we have for both datasets. Joining them is a little bit complicated, let's use a simple example.

```{r}
df1 <- data.frame(
  id = c(1, 2, 3),
  name = c("Tom", "Jerry", "Spike") # Tom, Jerry, and the bulldog called Spike
)

df2 <- data.frame(
  id = c(2, 3, 4, 2),
  score = c(85, 90, 95, 87) # notice that we have two records for id = 2 (Jerry)
)
```

```{r}
# inner join: keeps only rows where id exists in both data frames
inner_join(df1, df2, by = "id")
# left join: keeps all rows from df1 (left table). If no match in df2, fill with NA.
left_join(df1, df2, by = "id")
# right join: keeps all rows from df2 (right table). If no match in df1, fill with NA.
right_join(df1, df2, by = "id")
# full join: keeps all rows from both tables. Missing values are filled with NA.
full_join(df1, df2, by = "id")
```

#### Joining King County Datasets

Back to our datasets, let's first check the differences between `pinx` in both datasets.

```{r}
# check whether kingco_sales$pinx are all in kingco_homes$pinx
all(kingco_sales$pinx %in% kingco_homes$pinx)
# check the number of `pinx` values from sales are not in homes
setdiff(kingco_sales$pinx, kingco_homes$pinx) %>% 
  length()
```

If we want to focus on `kingco_sales` and use `kingco_homes` as attributes of those transactions, we can use `left_join()`. (Also `right_join()` if you put `kingco_sales` on the right.)

```{r}
kingco_sales_w_info <- left_join(kingco_sales, kingco_homes, by = "pinx")
head(kingco_sales_w_info, 3)
```

#### We Have a Problem....

Oops, we received the warning from R: Detected an unexpected many-to-many relationship between `x` and `y`. We expect there are multiple `pinx` in transaction data but `pinx` should be unique in home data. That happens all the time when you work as a data analyst: all kinds of messy and unexpected data issues. Handling these challenges carefully is an essential part of real-world data analysis.

Let's investigate duplicates.

```{r}
kingco_homes %>%
  add_count(pinx) %>%  # add the count of `pinx` as a new column
  filter(n > 1) %>%  # find duplicated properties 
  head(3) # I add `head()` because otherwise all rows will be shown on the html when I knit this Rmd
```

We found `land_val` and `imp_val` are different while others are same in general. To simplify the problem, we can assume `land_val` and `imp_val` may be different while others are same. Also, we should select rows with high total values.

```{r}
kingco_homes_clean <- kingco_homes %>%
  distinct() %>% # drop any completely duplicate rows
  group_by(pinx) %>%
  slice_max(order_by = land_val + imp_val, n = 1, with_ties = FALSE) %>% # within each group, keep just the one row with the largest total
  ungroup() # remove the grouping so the result is a normal data frame again
```

Join them again and we will not receive the warning.

```{r}
kingco_sales_w_clean_info <- left_join(kingco_sales, kingco_homes_clean, by = "pinx")
head(kingco_sales_w_clean_info, 3)
```

### 📚 TODO: Summarizing and Joining Dataframes

**2 points**

Let's use the `kingco_homes_clean` data to do some summarise and analysis.

For all property in Seattle, what's the average total values per sqft [(`land_val` + `imp_val`)/`sqft`] by condition? Also, add the count of properties in each group in the summary table.

```{r}
# TODO

```

### 📚 TODO: Sampling for Central Limit Theorem

**4 points**

We are going to use the sale price data to understand the most important theorem in statistics, [Central Limit Theorem (CLT)](https://en.wikipedia.org/wiki/Central_limit_theorem). I **highly** recommend [this video from 3Blue1Brown](https://www.youtube.com/watch?v=zeJD6dqJ5lo&t=68s) to understand CLT. 

```{r}
# filter transactions after 2020 in Bellevue
bellevue_after2020 <- kingco_sales %>%
  filter(
    city == "BELLEVUE",
    sale_date >= as.Date("2020-01-01")
  )
```

Let's visualize it and we can find the sale price is highly skewed ([what is skewness?](https://en.wikipedia.org/wiki/Skewness)), not [normally distributed](https://en.wikipedia.org/wiki/Normal_distribution). Right now, we are using a histogram to display this distribution (will be covered in later class). 

```{r}
# visualize the distribution of sale prices
hist(
  bellevue_after2020$sale_price,
  breaks = 50,
  main = "Histogram of Bellevue Sale Prices after 2020", 
  xlab = "Sale Price (USD)"                         
)
```

```{r}
# calculate the average of bellevue_after2020$sale_price
sale_price_mean = mean(bellevue_after2020$sale_price)
```

The mean value of sale price in Bellevue is $1,888,176. Suppose we do not know the population (all transactions), we only have samples. We can use `sample_n()` to draw a random sample from Bellevue transactions, compute the sample mean, then repeat 1,000 times. 

```{r}
# create sample means; you don't have to anything
set.seed(79)
sample_means <- replicate(
  1000,  # we repeat the process (sample -> sample mean -> record) for 1000 times
  bellevue_after2020 %>%
    sample_n(100, replace = TRUE) %>% 
    summarise(avg_price = mean(sale_price)) %>%
    pull(avg_price)
)
```

Visualize the sample means with the red line as the 'real' average, we found the distribution of sample means approaches normality.

```{r}
hist(
  sample_means,
  breaks = 50,
  main = "Sampling Distribution of Mean Sale Price (n = 100)",
  xlab = "Sample Mean Sale Price (USD)"
)
abline(v = sale_price_mean, col = "red", lwd = 2)
```

Repeat the process with smaller sample size, such as `n = 10`, and smaller repeating time, such as 200. What did you find? 

```{r}
# TODO
```


### 📚 TODO: Explore More

**4 points**

So far, we've learned a lots functions, please use as many as possible to explore topics you are interested in about the King County datasets. Please use at least 5 functions below and brief discuss your results. 

- `select()` – choose columns  
- `filter()` – filter rows by conditions  
- `%in%` – check membership in a set  
- `arrange()` + `desc()` – sort data  
- `mutate()` – create or redefine variables  
- `rename()` – change column names  
- `recode()` and `case_when()` – recode variables and create categories  
- `sample_n()` + `set.seed()` – random sampling  
- `summary()` – base R quick statistics  
- `summarise()` / `summarize()` – compute group summaries  
- `group_by()` – aggregate by categories  
- joins (`inner_join`, `left_join`, `right_join`, `full_join`) – combine datasets  
- `distinct()` and `slice_max()` – handle duplicates  

```{r}
# TODO
```

## Acknowledgement

The materials are developed based materials from [Dr. Feiyang Sun at UC San Diego](https://fsun.ucsd.edu), Siman Ning and Christian Phillips at UW, [Dr. Charles Lanfear at University of Cambridge](https://clanfear.github.io).
