Lab 05 Modifying Dataframe in R

URBAN 520 Quantitative Methods in Urban Design and Planning

Pipe - A Unique Feature of R
Simple Sampling from Dataframes
Select Rows or Columns
Sort Based on Values
Create, Re-define, and Rename Variables
📚 TODO: Modifying Dataframes

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 on GitHub.

Recommended Reading
Chapter 4, 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')
#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 605,614 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.

# check all column names
colnames(kingco_sales)

##  [1] "sale_id"          "pinx"             "sale_date"        "sale_price"      
##  [5] "sale_nbr"         "sale_warning"     "join_status"      "join_year"       
##  [9] "latitude"         "longitude"        "area"             "city"            
## [13] "zoning"           "subdivision"      "present_use"      "land_val"        
## [17] "imp_val"          "year_built"       "year_reno"        "sqft_lot"        
## [21] "sqft"             "sqft_1"           "sqft_fbsmt"       "grade"           
## [25] "fbsmt_grade"      "condition"        "stories"          "beds"            
## [29] "bath_full"        "bath_3qtr"        "bath_half"        "garb_sqft"       
## [33] "gara_sqft"        "wfnt"             "golf"             "greenbelt"       
## [37] "noise_traffic"    "view_rainier"     "view_olympics"    "view_cascades"   
## [41] "view_territorial" "view_skyline"     "view_sound"       "view_lakewash"   
## [45] "view_lakesamm"    "view_otherwater"  "view_other"       "submarket"

?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

# example without pipe
mean(log(sqrt(c(1, 4, 9, 16))))

## [1] 0.7945135

# example with pipe
c(1, 4, 9, 16) %>%
  sqrt() %>%
  log() %>%
  mean()

## [1] 0.7945135

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.

random_sample <- kingco_sales %>%
  sample_n(3)
random_sample

## # A tibble: 3 × 48
##   sale_id     pinx       sale_date  sale_price sale_nbr sale_warning join_status
##   <chr>       <chr>      <date>          <int>    <int> <chr>        <chr>      
## 1 2000..38254 ..4018500… 2000-11-10     237000       NA "   "        nochg      
## 2 2021..21965 ..2919700… 2021-06-16     931000        6 "   "        nochg      
## 3 2016..28536 ..3755000… 2016-08-12     525000       NA "   "        nochg      
## # ℹ 41 more variables: join_year <dbl>, latitude <dbl>, longitude <dbl>,
## #   area <int>, city <chr>, zoning <chr>, subdivision <chr>, present_use <int>,
## #   land_val <int>, imp_val <int>, year_built <int>, year_reno <int>,
## #   sqft_lot <int>, sqft <int>, sqft_1 <int>, sqft_fbsmt <int>, grade <int>,
## #   fbsmt_grade <int>, condition <int>, stories <dbl>, beds <int>,
## #   bath_full <int>, bath_3qtr <int>, bath_half <int>, garb_sqft <int>,
## #   gara_sqft <int>, wfnt <int>, golf <dbl>, greenbelt <dbl>, …

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

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

## # A tibble: 3 × 48
##   sale_id     pinx       sale_date  sale_price sale_nbr sale_warning join_status
##   <chr>       <chr>      <date>          <int>    <int> <chr>        <chr>      
## 1 2012..24205 ..1543000… 2012-09-12     375900        3 "   "        new        
## 2 2006..46622 ..1796350… 2006-10-23     290000        3 "   "        nochg      
## 3 2000..42967 ..1337800… 2000-12-27     900000        1 " 41 "       reno - aft…
## # ℹ 41 more variables: join_year <dbl>, latitude <dbl>, longitude <dbl>,
## #   area <int>, city <chr>, zoning <chr>, subdivision <chr>, present_use <int>,
## #   land_val <int>, imp_val <int>, year_built <int>, year_reno <int>,
## #   sqft_lot <int>, sqft <int>, sqft_1 <int>, sqft_fbsmt <int>, grade <int>,
## #   fbsmt_grade <int>, condition <int>, stories <dbl>, beds <int>,
## #   bath_full <int>, bath_3qtr <int>, bath_half <int>, garb_sqft <int>,
## #   gara_sqft <int>, wfnt <int>, golf <dbl>, greenbelt <dbl>, …

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

# 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 pervious lab, 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. — 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.

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)

## [1] 17

Interesting, there were only 17 transactions of properties with views of both Lake Washington and Lake Sammammish after 2020. Let’s look at how many of them are below 1.5 millions.

kingco_sales_twolakes %>%
  filter(
    sale_price < 1500000, # price below 1.5 million
  )

## # A tibble: 4 × 6
##   sale_id     city        sale_price sale_date  view_lakewash view_lakesamm
##   <chr>       <chr>            <int> <date>             <int>         <int>
## 1 2021..8432  KING COUNTY    1390000 2021-03-25             1             1
## 2 2022..9059  KING COUNTY    1150000 2022-04-04             1             1
## 3 2023..360   BELLEVUE       1400000 2023-01-12             1             1
## 4 2023..21433 BELLEVUE       1468000 2023-08-07             1             1

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:

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

## # A tibble: 2 × 6
##   sale_id     city     sale_price sale_date  view_lakewash view_lakesamm
##   <chr>       <chr>         <int> <date>             <int>         <int>
## 1 2023..360   BELLEVUE    1400000 2023-01-12             1             1
## 2 2023..21433 BELLEVUE    1468000 2023-08-07             1             1

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

kingco_sales_twolakes_clean <- kingco_sales_twolakes %>% 
  select(-c(view_lakewash, view_lakesamm))
head(kingco_sales_twolakes_clean,3)

## # A tibble: 3 × 4
##   sale_id     city        sale_price sale_date 
##   <chr>       <chr>            <int> <date>    
## 1 2021..8432  KING COUNTY    1390000 2021-03-25
## 2 2025..16942 BELLEVUE       2600000 2025-07-18
## 3 2025..22544 BELLEVUE       1790000 2025-09-29

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.

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)

## # A tibble: 4 × 6
##   pinx         submarket sale_price  sqft year_built grade
##   <chr>        <chr>          <int> <int>      <int> <int>
## 1 ..7526400010 R            1390000  2760       1970     9
## 2 ..9429500255 R            1150000  2400       1955     7
## 3 ..7855800140 R            1400000  2500       1968     9
## 4 ..9323600550 R            1468000  3340       1979     9

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

kingco_sales_twolakes %>% 
  arrange(desc(sale_price))

## # A tibble: 17 × 6
##    sale_id     city        sale_price sale_date  view_lakewash view_lakesamm
##    <chr>       <chr>            <int> <date>             <int>         <int>
##  1 2024..27994 BELLEVUE       3550000 2024-12-06             1             1
##  2 2024..3565  BELLEVUE       3100000 2024-03-06             1             1
##  3 2024..22978 BELLEVUE       3000000 2024-10-01             1             1
##  4 2025..18464 BELLEVUE       2980000 2025-08-13             1             1
##  5 2022..20951 BELLEVUE       2850000 2022-06-30             1             1
##  6 2025..16942 BELLEVUE       2600000 2025-07-18             1             1
##  7 2021..22666 KIRKLAND       2220000 2021-06-22             1             1
##  8 2022..27463 BELLEVUE       2200000 2022-08-31             1             1
##  9 2024..6277  BELLEVUE       2165000 2024-04-08             1             1
## 10 2024..26859 BELLEVUE       2050000 2024-11-19             1             1
## 11 2020..33285 BELLEVUE       1800000 2020-10-20             1             1
## 12 2025..22544 BELLEVUE       1790000 2025-09-29             1             1
## 13 2021..31882 BELLEVUE       1710000 2021-08-17             1             1
## 14 2023..21433 BELLEVUE       1468000 2023-08-07             1             1
## 15 2023..360   BELLEVUE       1400000 2023-01-12             1             1
## 16 2021..8432  KING COUNTY    1390000 2021-03-25             1             1
## 17 2022..9059  KING COUNTY    1150000 2022-04-04             1             1

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. — The `mutate()` function. At right, the resulting data frame after adding a new column. Source: Modern Data Science with 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.

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

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

## # A tibble: 17 × 8
##    sale_id     city        sale_price sale_date  view_lakewash view_lakesamm
##    <chr>       <chr>            <int> <date>             <int>         <int>
##  1 2021..8432  KING COUNTY    1390000 2021-03-25             1             1
##  2 2025..16942 BELLEVUE       2600000 2025-07-18             1             1
##  3 2025..22544 BELLEVUE       1790000 2025-09-29             1             1
##  4 2021..22666 KIRKLAND       2220000 2021-06-22             1             1
##  5 2024..3565  BELLEVUE       3100000 2024-03-06             1             1
##  6 2024..22978 BELLEVUE       3000000 2024-10-01             1             1
##  7 2020..33285 BELLEVUE       1800000 2020-10-20             1             1
##  8 2022..9059  KING COUNTY    1150000 2022-04-04             1             1
##  9 2022..27463 BELLEVUE       2200000 2022-08-31             1             1
## 10 2023..360   BELLEVUE       1400000 2023-01-12             1             1
## 11 2023..21433 BELLEVUE       1468000 2023-08-07             1             1
## 12 2024..6277  BELLEVUE       2165000 2024-04-08             1             1
## 13 2021..31882 BELLEVUE       1710000 2021-08-17             1             1
## 14 2022..20951 BELLEVUE       2850000 2022-06-30             1             1
## 15 2024..26859 BELLEVUE       2050000 2024-11-19             1             1
## 16 2025..18464 BELLEVUE       2980000 2025-08-13             1             1
## 17 2024..27994 BELLEVUE       3550000 2024-12-06             1             1
## # ℹ 2 more variables: price_category <chr>, city_group <chr>

📚 TODO: Modifying Dataframes

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

Select the columns: pinx, city, land_val, imp_val, sqft, view_lakewash, view_lakesamm
Filter the rows: all properties in Bellevue
Add a new variable called total_val which represents the sum of land_val (land value) and imp_val (improvements value)
Sort the dataframe based on total_val in descending order, and show the first 10 records using head()
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
Report the difference in average and variance

# TODO