Lab 06 Summarizing and Joining Data in R

URBAN 520 Quantitative Methods in Urban Design and Planning

Summarizing Data
Groupby
Joining Dataframes
Very Simple Git and GitHub
📚 TODO: Summarizing and Joining Dataframes
📚 TODO: Sampling for Central Limit Theorem
📚 TODO: Explore More

Throughout the Lab 5-6, we will learn how to do data wrangling and explore two housing-related datasets of King County maintained by Andy Krause at Zillow:

All single family and townhouse sales from 1999 through December 2025
All single family and townhouse properties as of December 31, 2025

You can find the datasets and Readme file in this repository.

Recommended Reading
Chapter 5, Modern Data Science with R. Baumer et al. 2024.

Let’s first import the datasets and necessary packages. If you are using a Windows computer, you need to install RTools first. After installing this data 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.

library('tidyverse')
#install.packages("devtools") # You may also need to install this
#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

Summarizing Data

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

summary(kingco_sales[, c("pinx", "sale_date", "land_val")])

##      pinx             sale_date             land_val       
##  Length:605614      Min.   :1999-01-01   Min.   :       0  
##  Class :character   1st Qu.:2004-12-22   1st Qu.:  257000  
##  Mode  :character   Median :2012-09-13   Median :  397000  
##                     Mean   :2012-02-08   Mean   :  500171  
##                     3rd Qu.:2018-12-20   3rd Qu.:  647000  
##                     Max.   :2025-12-31   Max.   :35124000

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.

In addition, the skimr package provides the skim() function, which offers a richer summary than summary().

# install.packages("skimr") # you only need to install once
library(skimr)
# run `skim()` to get a quick overview of the dataset
skim(kingco_sales[, c("pinx", "sale_date", "land_val")])

Data summary
Name	kingco_sales[, c(“pinx”, …
Number of rows	605614
Number of columns	3
_______________________
Column type frequency:
character	1
Date	1
numeric	1
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
pinx	0	1	12	12	0	347905	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
sale_date	0	1	1999-01-01	2025-12-31	2012-09-13	9635

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
land_val	0	1	500170.6	428219.4	0	257000	397000	647000	35124000	▇▁▁▁▁

Note: complete_rate is the proportion of non-missing values in that variable. In the HTML, we may find it is 1 but it’s not the case because the value is very close to 1 (e.g., 0.9996) and is rounded up.

We always want to know more than what summary() or skim() 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. Suppose we want to explore all transactions in Seattle after Jan 1, 2020.

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
  )

## # A tibble: 1 × 5
##       N avg_sale_price highest_sale_price percent_view_rainier unique_property
##   <int>          <dbl>              <int>                <dbl>           <int>
## 1 39769       1076756.           17000000                 1.46           36408

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.

kingco_sales %>%
  filter(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)) # sort it by average sale price

## # A tibble: 41 × 6
##    city              N avg_sale_price highest_sale_price percent_view_rainier
##    <chr>         <int>          <dbl>              <int>                <dbl>
##  1 HUNTS POINT      38       7636513.           63000000                0    
##  2 YARROW POINT     75       5262360.           18000000                0    
##  3 MEDINA          264       5035193.           20000000                2.27 
##  4 CLYDE HILL      236       4387532.           12225000                1.69 
##  5 MERCER ISLAND  1514       2719226.           28500000                9.64 
##  6 BEAUX ARTS       29       2698817.            5295000                0    
##  7 BELLEVUE       6194       1938376.           26000000                1.00 
##  8 KIRKLAND       5278       1647958.           15400000                0.872
##  9 SAMMAMISH      4665       1600511.            7000000                0.386
## 10 NEWCASTLE       877       1568284.            4180000                0.456
## # ℹ 31 more rows
## # ℹ 1 more variable: unique_property <int>

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.

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)
)

# inner join: keeps only rows where id exists in both data frames
inner_join(df1, df2, by = "id")

##   id  name score
## 1  2 Jerry    85
## 2  2 Jerry    87
## 3  3 Spike    90

# left join: keeps all rows from df1 (left table). If no match in df2, fill with NA.
left_join(df1, df2, by = "id")

##   id  name score
## 1  1   Tom    NA
## 2  2 Jerry    85
## 3  2 Jerry    87
## 4  3 Spike    90

# right join: keeps all rows from df2 (right table). If no match in df1, fill with NA.
right_join(df1, df2, by = "id")

##   id  name score
## 1  2 Jerry    85
## 2  2 Jerry    87
## 3  3 Spike    90
## 4  4  <NA>    95

# full join: keeps all rows from both tables. Missing values are filled with NA.
full_join(df1, df2, by = "id")

##   id  name score
## 1  1   Tom    NA
## 2  2 Jerry    85
## 3  2 Jerry    87
## 4  3 Spike    90
## 5  4  <NA>    95

Joining King County Datasets

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

# check whether kingco_sales$pinx are all in kingco_homes$pinx
all(kingco_sales$pinx %in% kingco_homes$pinx)

## [1] FALSE

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

## [1] 6695

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.)

kingco_sales_w_info <- left_join(kingco_sales, kingco_homes, by = "pinx")

## Warning in left_join(kingco_sales, kingco_homes, by = "pinx"): Detected an unexpected many-to-many relationship between `x` and `y`.
## ℹ Row 11399 of `x` matches multiple rows in `y`.
## ℹ Row 218229 of `y` matches multiple rows in `x`.
## ℹ If a many-to-many relationship is expected, set `relationship =
##   "many-to-many"` to silence this warning.

head(kingco_sales_w_info, 3)

## # A tibble: 3 × 88
##   sale_id   pinx         sale_date  sale_price sale_nbr sale_warning join_status
##   <chr>     <chr>        <date>          <int>    <int> <chr>        <chr>      
## 1 1999..144 ..2734100475 1999-01-05     150000        1 "   "        demo       
## 2 1999..258 ..1535200725 1999-01-05     235000        1 "   "        demo       
## 3 1999..379 ..9214400005 1999-01-08     190000        1 "   "        demo       
## # ℹ 81 more variables: join_year <dbl>, latitude.x <dbl>, longitude.x <dbl>,
## #   area.x <int>, city.x <chr>, zoning.x <chr>, subdivision.x <chr>,
## #   present_use.x <int>, land_val.x <int>, imp_val.x <int>, year_built.x <int>,
## #   year_reno.x <int>, sqft_lot.x <int>, sqft.x <int>, sqft_1.x <int>,
## #   sqft_fbsmt.x <int>, grade.x <int>, fbsmt_grade.x <int>, condition.x <int>,
## #   stories.x <dbl>, beds.x <int>, bath_full.x <int>, bath_3qtr.x <int>,
## #   bath_half.x <int>, garb_sqft.x <int>, gara_sqft.x <int>, wfnt.x <int>, …

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.

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

##           pinx latitude longitude area     city zoning
## 1 ..2420069353 47.20694 -121.9872   41 ENUMCLAW    R-4
## 2 ..2420069353 47.20694 -121.9872   41 ENUMCLAW    R-4
## 3 ..6392000550 47.68469 -122.2892   45  SEATTLE    NR3
##                 subdivision present_use land_val imp_val year_built year_reno
## 1                                     2        0       0       1938         0
## 2                                     2   196000  295000       1938         0
## 3 ONEIDA GARDENS ADD REPLAT           2   320500   10500       1947         0
##   sqft_lot sqft sqft_1 sqft_fbsmt grade fbsmt_grade condition stories beds
## 1     5500 1440    960        480     6           5         5       1    3
## 2     5500 1440    960        480     6           5         5       1    3
## 3     6000  990    990          0     7           0         3       1    2
##   bath_full bath_3qtr bath_half garb_sqft gara_sqft wfnt golf greenbelt
## 1         1         1         0       240         0    0    0         0
## 2         1         1         0       240         0    0    0         0
## 3         1         0         0         0         0    0    0         0
##   noise_traffic view_rainier view_olympics view_cascades view_territorial
## 1             0            0             0             0                0
## 2             0            0             0             0                0
## 3             0            0             0             0                0
##   view_skyline view_sound view_lakewash view_lakesamm view_otherwater
## 1            0          0             0             0               0
## 2            0          0             0             0               0
## 3            0          0             0             0               0
##   view_other submarket n
## 1          0         M 2
## 2          0         M 2
## 3          0         C 2

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.

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.

kingco_sales_w_clean_info <- left_join(kingco_sales, kingco_homes_clean, by = "pinx")
head(kingco_sales_w_clean_info, 3)

## # A tibble: 3 × 88
##   sale_id   pinx         sale_date  sale_price sale_nbr sale_warning join_status
##   <chr>     <chr>        <date>          <int>    <int> <chr>        <chr>      
## 1 1999..144 ..2734100475 1999-01-05     150000        1 "   "        demo       
## 2 1999..258 ..1535200725 1999-01-05     235000        1 "   "        demo       
## 3 1999..379 ..9214400005 1999-01-08     190000        1 "   "        demo       
## # ℹ 81 more variables: join_year <dbl>, latitude.x <dbl>, longitude.x <dbl>,
## #   area.x <int>, city.x <chr>, zoning.x <chr>, subdivision.x <chr>,
## #   present_use.x <int>, land_val.x <int>, imp_val.x <int>, year_built.x <int>,
## #   year_reno.x <int>, sqft_lot.x <int>, sqft.x <int>, sqft_1.x <int>,
## #   sqft_fbsmt.x <int>, grade.x <int>, fbsmt_grade.x <int>, condition.x <int>,
## #   stories.x <dbl>, beds.x <int>, bath_full.x <int>, bath_3qtr.x <int>,
## #   bath_half.x <int>, garb_sqft.x <int>, gara_sqft.x <int>, wfnt.x <int>, …

Very Simple Git and GitHub

Every change you make is saved in history, so you can always go back.
If something breaks, you can roll back to a working version.
Multiple people can work on the same project without overwriting each other.
Your code and documents are safe, even if your computer crashes.
Most of the data science ecosystem (R packages, Python libraries, statistical models, tutorials) lives on GitHub.

There are two main ways to use GitHub:

Terminal (Command Line) – more powerful and professional, gives you full control.
GitHub Desktop – easier for beginners, has a user-friendly interface.

We will using GitHub Desktop:

Download and install GitHub Desktop.
Sign up a free GitHub account.
Sign in with your GitHub account on GitHub Desktop.
Create a new public repository.
Click “Clone a repository” to copy from GitHub to your computer.
Add this .Rmd and html to your local repository.
Commit changes with a short message (e.g., “Lab 3 for URBAN 520”).
Click “Push origin” to upload your changes back to GitHub.
You can check your changes on GitHub now.

We only covered every basic of Git and GitHub, you can learn more on the logic behind Git using Learn Git Branching.

📚 TODO: Summarizing and Joining Dataframes

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.

# TODO

📚 TODO: Sampling for Central Limit Theorem

We are going to use the sale price data to understand one of the most important theorems in statistics, Central Limit Theorem (CLT). We will talk about this in future lecture but you can watch the visualization and this video from 3Blue1Brown to understand CLT in advance.

# 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?), not normally distributed. Right now, we are using a histogram to display this distribution (will be covered in later class).

# 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)"                         
)

# 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.

# 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(200, 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.

hist(
  sample_means,
  breaks = 50,
  main = "Sampling Distribution of Mean Sale Price (n = 200)",
  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? What interpretation can you give for the central limit theorem from your simulation results?

# TODO

📚 TODO: Explore More

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 (or any other datasets you are interested in). 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

# TODO