DATA 607: Week 3

Recommender Systems, Window Functions, and Character Data

Author

Darwhin Gomez

DATA 607

Week 3: Character Data and String Manipulation

Tonight we will work with text as data. Our examples use Jane Austen novels, but the same tools work for names, survey responses, addresses, product titles, and log files.

Tonight’s agenda

Due this week and next week
Global baseline estimates for recommenders
Window functions in SQL
Why character data needs cleaning
Working with strings in R
janeaustenr examples
A first look at regular expressions
Practice and next steps

Due this week

Thursday	Assignment 3A Global Baseline Estimate: Approach \|
Thursday	Assignment 3B Window Functions: Approach
Sunday	Quiz: Data Centric AI
Sunday	Discussion: Data Centric AI
Sunday	Assignment 3A Global Baseline Estimate: Code Base
Sunday	Assignment 3B Window Functions: Code Base
Monday	Assignment 3A Global Baseline Estimate: Video Explainer
Monday	Assignment 3B Global Baseline Estimate: Video Explainer

Due next week

Thursday: Project 1 approach
Sunday: Project 1 code base
Sunday: Scenario Design quiz and discussion
Monday: Project 1 video explainer

Global baseline estimates

A recommender system estimates how a user might rate an item they have not yet rated.

The simplest useful prediction starts with a baseline:

the typical rating across the whole dataset
how a particular critic tends to rate
how a particular item tends to be rated

The bias-based formula

\[ \widehat{r}_{ui} = \mu + b_u + b_i \]

Term	Meaning
$\mu$	Global mean rating
$b_u$	User bias: this user rates above or below average
$b_i$	Item bias: this item receives above or below average ratings

The hat means “predicted,” not an observed rating.

Critic-movie ratings data

Your ratings matrix has one row per critic and one column per movie. Blank cells mean a rating was not observed; the question mark is the rating we want to predict.

Code

# Load dplyr and tidyr functions for this example.
library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.1     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Code

ratings_wide <- tribble(
  ~critic, ~CaptainAmerica, ~Deadpool, ~Frozen, ~JungleBook, ~PitchPerfect2, ~StarWarsForce,
  "Burton", NA, NA, NA, 4, NA, 4,
  "Charley", 4, 5, 4, 3, 2, 3,
  "Dan", NA, 5, NA, NA, NA, 5,
  "Dieudonne", 5, 4, NA, NA, NA, 5,
  "Matt", 4, NA, 2, NA, 2, 5,
  "Mauricio", 4, NA, 3, 3, 4, NA,
  "Max", 4, 4, 4, 2, 2, 4,
  "Nathan", NA, NA, NA, NA, NA, 4,
  "Param", 4, 4, 1, NA, NA, 5,
  "Parshu", 4, 3, 5, 5, 2, 3,
  "Prashanth", 5, 5, 5, 5, NA, 4,
  "Shipra", NA, NA, 4, 5, NA, 3,
  "Sreejaya", 5, 5, 5, 4, 4, 5,
  "Steve", 4, NA, NA, NA, NA, 4,
  "Vuthy", 4, 5, 3, 3, 3, NA,
  "Xingjia", NA, NA, 5, 5, NA, NA
)
ratings_wide

# A tibble: 16 × 7
   critic  CaptainAmerica Deadpool Frozen JungleBook PitchPerfect2 StarWarsForce
   <chr>            <dbl>    <dbl>  <dbl>      <dbl>         <dbl>         <dbl>
 1 Burton              NA       NA     NA          4            NA             4
 2 Charley              4        5      4          3             2             3
 3 Dan                 NA        5     NA         NA            NA             5
 4 Dieudo…              5        4     NA         NA            NA             5
 5 Matt                 4       NA      2         NA             2             5
 6 Mauric…              4       NA      3          3             4            NA
 7 Max                  4        4      4          2             2             4
 8 Nathan              NA       NA     NA         NA            NA             4
 9 Param                4        4      1         NA            NA             5
10 Parshu               4        3      5          5             2             3
11 Prasha…              5        5      5          5            NA             4
12 Shipra              NA       NA      4          5            NA             3
13 Sreeja…              5        5      5          4             4             5
14 Steve                4       NA     NA         NA            NA             4
15 Vuthy                4        5      3          3             3            NA
16 Xingjia             NA       NA      5          5            NA            NA

Code

ratings <- ratings_wide |>
  pivot_longer(-critic, names_to = "movie", values_to = "rating") |>
  drop_na(rating)

ratings

# A tibble: 61 × 3
   critic  movie          rating
   <chr>   <chr>           <dbl>
 1 Burton  JungleBook          4
 2 Burton  StarWarsForce       4
 3 Charley CaptainAmerica      4
 4 Charley Deadpool            5
 5 Charley Frozen              4
 6 Charley JungleBook          3
 7 Charley PitchPerfect2       2
 8 Charley StarWarsForce       3
 9 Dan     Deadpool            5
10 Dan     StarWarsForce       5
# ℹ 51 more rows

pivot_longer() changes the ratings matrix into one row per observed critic-movie rating.

Step 1: global mean

Code

global_mean <- mean(ratings$rating)
global_mean

[1] 3.934426

If we know nothing else, this is our prediction for every user-movie pair.

Step 2a: critic bias

A critic bias measures whether that critic usually rates above or below the global mean.

Code

critic_bias <- ratings |>
  group_by(critic) |>
  summarize(b_u = mean(rating) - global_mean)

critic_bias

# A tibble: 16 × 2
   critic        b_u
   <chr>       <dbl>
 1 Burton     0.0656
 2 Charley   -0.434 
 3 Dan        1.07  
 4 Dieudonne  0.732 
 5 Matt      -0.684 
 6 Mauricio  -0.434 
 7 Max       -0.601 
 8 Nathan     0.0656
 9 Param     -0.434 
10 Parshu    -0.268 
11 Prashanth  0.866 
12 Shipra     0.0656
13 Sreejaya   0.732 
14 Steve      0.0656
15 Vuthy     -0.334 
16 Xingjia    1.07

A positive value means the critic tends to give higher ratings than the typical rating.

Step 2b: movie bias

A movie bias measures whether a movie usually receives ratings above or below the global mean.

Code

movie_bias <- ratings |>
  group_by(movie) |>
  summarize(b_i = mean(rating) - global_mean)

movie_bias

# A tibble: 6 × 2
  movie              b_i
  <chr>            <dbl>
1 CaptainAmerica  0.338 
2 Deadpool        0.510 
3 Frozen         -0.207 
4 JungleBook     -0.0344
5 PitchPerfect2  -1.22  
6 StarWarsForce   0.219

A negative value means the movie tends to receive lower ratings than the typical rating.

Predict Param’s `PitchPerfect2` rating

Do the arithmetic by hand before writing any code.

Quantity	Calculation	Rounded value
Global mean, $\mu$	mean of all observed ratings	3.93
Param bias, $b_u$	$3.50 - 3.93$	-0.43
`PitchPerfect2` bias, $b_i$	$2.71 - 3.93$	-1.22

Prediction: 3.93 + (-0.43) + (-1.22) = 2.28

Your task is to create the tibble of biases and implement this arithmetic. Then score every movie Param has not rated and recommend the highest predicted rating.

Recommend a movie for Param

First, identify movies Param has not rated. Then calculate the baseline prediction for every candidate and sort from highest to lowest.

Code

param_seen <- ratings |>
  filter(critic == "Param") |>
  distinct(movie)
param_seen

# A tibble: 4 × 1
  movie         
  <chr>         
1 CaptainAmerica
2 Deadpool      
3 Frozen        
4 StarWarsForce

Code

candidate_predictions <- ratings |>
  distinct(movie) |>
  anti_join(param_seen, by = "movie") |>
  mutate(critic = "Param") |>
  left_join(critic_bias, by = "critic") |>
  left_join(movie_bias, by = "movie") |>
  mutate(predicted_rating = global_mean + b_u + b_i) |>
  arrange(desc(predicted_rating))

candidate_predictions

# A tibble: 2 × 5
  movie         critic    b_u     b_i predicted_rating
  <chr>         <chr>   <dbl>   <dbl>            <dbl>
1 JungleBook    Param  -0.434 -0.0344             3.47
2 PitchPerfect2 Param  -0.434 -1.22               2.28

Recommend the first row: JungleBook. It has the highest global-baseline prediction among the movies Param has not rated.

When is a global baseline useful?

Useful for

a transparent first model
sparse data or cold-start comparisons
checking a more complex recommender

Not enough for

user-item interactions
content similarity
changing preferences over time

Window functions: PostgreSQL to R

We will work with the afc_east table in the NFL607 PostgreSQL database.

Connect to PostgreSQL from R.
Import the table into afc_east_df.
Send SQL window-function queries to PostgreSQL.
Bring each query result back to R as a tibble.

The rendered examples use the local NFL607 database so you can inspect each returned result.

Connect to the NFL607 database

Code

# Install once if needed:
# install.packages(c("DBI", "RPostgres"))

library(DBI)

Warning: package 'DBI' was built under R version 4.4.3

Code

library(RPostgres)

Warning: package 'RPostgres' was built under R version 4.4.3

Code

library(tidyverse)

pg_pw <- Sys.getenv("NFL607_DB_PASSWORD")
if (!nzchar(pg_pw)) stop("Set NFL607_DB_PASSWORD in .Renviron before rendering.")

con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "NFL607",
  host = "localhost",
  port = 5432,
  user = "postgres",
  password = pg_pw
)

Store the password in an environment variable. Do not put a real password in a Quarto file or Git repository.

Import the SQL table into an R data frame

Code

afc_east_df <- dbReadTable(con, "afc_east") |>
  as_tibble() |>
  mutate(game_date = as.Date(game_date))

head(afc_east_df)

# A tibble: 6 × 5
  game_date  home_team away_team home_points away_points
  <date>     <chr>     <chr>           <int>       <int>
1 2019-12-21 Patriots  Bills              24          17
2 2019-12-29 Patriots  Dolphins           24          27
3 2019-10-21 Jets      Patriots            0          33
4 2019-09-29 Bills     Patriots           10          16
5 2019-09-22 Patriots  Jets               30          14
6 2019-09-15 Dolphins  Patriots            0          43

afc_east_df is now an R tibble. The PostgreSQL table remains unchanged in the database.

Window functions keep every game row

GROUP BY reduces many game rows to one row per team. A window function adds a calculation while preserving every game row.

AVG(home_points) OVER (PARTITION BY home_team)

Read the query result directly into a tibble with dbGetQuery().

Overall average and differential

Code

overall_scores_df <- dbGetQuery(con, "
  SELECT home_team,
         game_date,
         home_points,
         ROUND(AVG(home_points) OVER (), 1) AS home_avg,
         ROUND(home_points - AVG(home_points) OVER (), 1) AS home_differential
  FROM afc_east
  ORDER BY home_team, game_date
") |>
  as_tibble()

overall_scores_df

# A tibble: 12 × 5
   home_team game_date  home_points home_avg home_differential
   <chr>     <date>           <int>    <dbl>             <dbl>
 1 Bills     2019-09-29          10     17.4              -7.4
 2 Bills     2019-10-20          31     17.4              13.6
 3 Bills     2019-12-29           6     17.4             -11.4
 4 Dolphins  2019-09-15           0     17.4             -17.4
 5 Dolphins  2019-11-03          26     17.4               8.6
 6 Dolphins  2019-11-17          20     17.4               2.6
 7 Jets      2019-09-08          16     17.4              -1.4
 8 Jets      2019-10-21           0     17.4             -17.4
 9 Jets      2019-12-08          22     17.4               4.6
10 Patriots  2019-09-22          30     17.4              12.6
11 Patriots  2019-12-21          24     17.4               6.6
12 Patriots  2019-12-29          24     17.4               6.6

OVER () means every row belongs to one window: the full result set.

Average by home team

Code

team_average_df <- dbGetQuery(con, "
  SELECT home_team,
         game_date,
         home_points,
         ROUND(AVG(home_points) OVER (PARTITION BY home_team), 1) AS home_team_avg
  FROM afc_east
  ORDER BY home_team, game_date
") |>
  as_tibble()

team_average_df

# A tibble: 12 × 4
   home_team game_date  home_points home_team_avg
   <chr>     <date>           <int>         <dbl>
 1 Bills     2019-09-29          10          15.7
 2 Bills     2019-10-20          31          15.7
 3 Bills     2019-12-29           6          15.7
 4 Dolphins  2019-09-15           0          15.3
 5 Dolphins  2019-11-03          26          15.3
 6 Dolphins  2019-11-17          20          15.3
 7 Jets      2019-09-08          16          12.7
 8 Jets      2019-10-21           0          12.7
 9 Jets      2019-12-08          22          12.7
10 Patriots  2019-09-22          30          26  
11 Patriots  2019-12-21          24          26  
12 Patriots  2019-12-29          24          26

PARTITION BY home_team restarts the calculation for each team.

Rank scores within each team

Code

ranked_games_df <- dbGetQuery(con, "
  SELECT home_team,
         game_date,
         home_points,
         RANK() OVER (
           PARTITION BY home_team
           ORDER BY home_points DESC
         ) AS home_rank
  FROM afc_east
  ORDER BY home_team, home_rank
") |>
  as_tibble()

ranked_games_df

# A tibble: 12 × 4
   home_team game_date  home_points home_rank
   <chr>     <date>           <int>   <int64>
 1 Bills     2019-10-20          31         1
 2 Bills     2019-09-29          10         2
 3 Bills     2019-12-29           6         3
 4 Dolphins  2019-11-03          26         1
 5 Dolphins  2019-11-17          20         2
 6 Dolphins  2019-09-15           0         3
 7 Jets      2019-12-08          22         1
 8 Jets      2019-09-08          16         2
 9 Jets      2019-10-21           0         3
10 Patriots  2019-09-22          30         1
11 Patriots  2019-12-29          24         2
12 Patriots  2019-12-21          24         2

The highest home score for each team receives rank 1.

Running total by team

Code

running_scores_df <- dbGetQuery(con, "
  SELECT home_team,
         game_date,
         home_points,
         SUM(home_points) OVER (
           PARTITION BY home_team
           ORDER BY game_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS cumulative_home_points
  FROM afc_east
  ORDER BY home_team, game_date
") |>
  as_tibble()

running_scores_df

# A tibble: 12 × 4
   home_team game_date  home_points cumulative_home_points
   <chr>     <date>           <int>                <int64>
 1 Bills     2019-09-29          10                     10
 2 Bills     2019-10-20          31                     41
 3 Bills     2019-12-29           6                     47
 4 Dolphins  2019-09-15           0                      0
 5 Dolphins  2019-11-03          26                     26
 6 Dolphins  2019-11-17          20                     46
 7 Jets      2019-09-08          16                     16
 8 Jets      2019-10-21           0                     16
 9 Jets      2019-12-08          22                     38
10 Patriots  2019-09-22          30                     30
11 Patriots  2019-12-21          24                     54
12 Patriots  2019-12-29          24                     78

The window starts with each team’s first game and ends at the current game row.

The R equivalent after import

After afc_east_df is in R, dplyr can perform a similar running-total calculation.

Code

afc_east_df |>
  arrange(home_team, game_date) |>
  group_by(home_team) |>
  mutate(cumulative_home_points = cumsum(home_points)) |>
  ungroup()

# A tibble: 12 × 6
   game_date  home_team away_team home_points away_points cumulative_home_points
   <date>     <chr>     <chr>           <int>       <int>                  <int>
 1 2019-09-29 Bills     Patriots           10          16                     10
 2 2019-10-20 Bills     Dolphins           31          21                     41
 3 2019-12-29 Bills     Jets                6          13                     47
 4 2019-09-15 Dolphins  Patriots            0          43                      0
 5 2019-11-03 Dolphins  Jets               26          18                     26
 6 2019-11-17 Dolphins  Bills              20          37                     46
 7 2019-09-08 Jets      Bills              16          17                     16
 8 2019-10-21 Jets      Patriots            0          33                     16
 9 2019-12-08 Jets      Dolphins           22          21                     38
10 2019-09-22 Patriots  Jets               30          14                     30
11 2019-12-21 Patriots  Bills              24          17                     54
12 2019-12-29 Patriots  Dolphins           24          27                     78

The SQL version calculates in PostgreSQL. The R version calculates after the table has been imported.

PostgreSQL window functions mapped to dplyr

A PostgreSQL window function is usually a dplyr mutate() after the data has been grouped and ordered. Both approaches add a value to every original row.

PostgreSQL idea	PostgreSQL pattern	`dplyr` counterpart	What it does
All rows in one window	`AVG(x) OVER ()`	`mutate(avg_x = mean(x))`	Repeats one overall value on every row.
A window for each group	`PARTITION BY team`	`group_by(team)`	Restarts a calculation for each team.
Order inside a window	`ORDER BY game_date`	`arrange(game_date, .by_group = TRUE)`	Sets the order used by ranks, lags, and running totals.
Keep every original row	`... OVER (...)`	`mutate(...)`	Adds a calculated column without reducing rows.
Reduce to one row per group	`GROUP BY team`	`group_by(team) \|> summarise(...)`	Produces a summary table instead of a row-by-row result.

Aggregate functions: PostgreSQL to dplyr

PostgreSQL	`dplyr` inside `mutate()`	`dplyr` inside `summarise()`
`AVG(x) OVER (...)`	`mean(x, na.rm = TRUE)`	`mean(x, na.rm = TRUE)`
`SUM(x) OVER (...)`	`sum(x, na.rm = TRUE)`	`sum(x, na.rm = TRUE)`
`MIN(x) OVER (...)` / `MAX(x) OVER (...)`	`min(x, na.rm = TRUE)` / `max(x, na.rm = TRUE)`	Same functions
`COUNT(*) OVER (...)`	`n()`	`n()`

Use these functions inside mutate() when you want to keep each game row. Use them inside summarise() when you want one result per group.

Ranks, offsets, and running totals: PostgreSQL to dplyr

PostgreSQL	`dplyr` counterpart	Meaning
`RANK() OVER (ORDER BY x DESC)`	`min_rank(desc(x))`	Equal values share a rank, and later ranks have gaps.
`DENSE_RANK() OVER (ORDER BY x DESC)`	`dense_rank(desc(x))`	Equal values share a rank, with no gaps afterward.
`ROW_NUMBER() OVER (ORDER BY x DESC)`	`row_number(desc(x))`	Gives every row a unique position, including tied values.
`LAG(x) OVER (ORDER BY date)`	`lag(x)` after `arrange(date)`	Reads the previous row’s value.
`LEAD(x) OVER (ORDER BY date)`	`lead(x)` after `arrange(date)`	Reads the next row’s value.
`SUM(x) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`	`cumsum(x)` after `arrange()`	Adds values from the first ordered row through the current row.

One dplyr pipeline that matches the SQL examples

Code

window_map_df <- afc_east_df |>
  group_by(home_team) |>
  arrange(game_date, .by_group = TRUE) |>
  mutate(
    home_team_avg = mean(home_points, na.rm = TRUE),
    home_rank = min_rank(desc(home_points)),
    prior_home_points = lag(home_points),
    next_home_points = lead(home_points),
    cumulative_home_points = cumsum(home_points)
  ) |>
  ungroup()

window_map_df

# A tibble: 12 × 10
   game_date  home_team away_team home_points away_points home_team_avg
   <date>     <chr>     <chr>           <int>       <int>         <dbl>
 1 2019-09-29 Bills     Patriots           10          16          15.7
 2 2019-10-20 Bills     Dolphins           31          21          15.7
 3 2019-12-29 Bills     Jets                6          13          15.7
 4 2019-09-15 Dolphins  Patriots            0          43          15.3
 5 2019-11-03 Dolphins  Jets               26          18          15.3
 6 2019-11-17 Dolphins  Bills              20          37          15.3
 7 2019-09-08 Jets      Bills              16          17          12.7
 8 2019-10-21 Jets      Patriots            0          33          12.7
 9 2019-12-08 Jets      Dolphins           22          21          12.7
10 2019-09-22 Patriots  Jets               30          14          26  
11 2019-12-21 Patriots  Bills              24          17          26  
12 2019-12-29 Patriots  Dolphins           24          27          26  
# ℹ 4 more variables: home_rank <int>, prior_home_points <int>,
#   next_home_points <int>, cumulative_home_points <int>

Read this pipeline from top to bottom: group_by() matches PARTITION BY; arrange() matches ORDER BY; and mutate() matches the OVER (...) calculation that keeps each game row.

When SQL and dplyr differ

Use PostgreSQL when the data is stored in the database and you want the database to do the work. Use dplyr after importing data when you need to continue analyzing it in R. In both tools, choose mutate()/OVER() to keep rows and summarise()/GROUP BY to collapse rows.

Close the database connection

Code

dbDisconnect(con)

Close the connection when you are done querying the database.

Window-function checklist

Before writing the query, state in words:

What does one row represent?
Which rows should be compared together?
What order matters?
Should the output preserve every original row?

For example: “For each team, ordered by game date, calculate home points scored to date.” ## Why character data matters

Text is everywhere in data work:

names, emails, and addresses
survey comments and reviews
categories imported from files
dates stored as text
IDs such as NY-2026-0142

Before analysis, text often needs to be standardized, searched, split, or combined.

Learning goals

By the end of this session, you should be able to:

recognize a character vector in R
clean inconsistent capitalization and whitespace
find text with str_detect()
extract or replace part of a string
use simple regular expressions to describe a pattern

Packages for tonight

Code

# Install once if needed:
# install.packages(c("tidyverse", "janeaustenr"))

library(tidyverse)
library(janeaustenr)

stringr is included when you load the tidyverse.

What is a string?

A string is text inside quotation marks.

Code

movie <- "Pride and Prejudice"
year <- "1813"
rating <- "4.5"

All three objects are character strings, even when their contents look like a number.

Character vectors

A character vector stores multiple strings.

Code

characters <- c("Elizabeth", "Darcy", "Jane", "Bingley")
characters

[1] "Elizabeth" "Darcy"     "Jane"      "Bingley"

Code

class(characters)

[1] "character"

Code

length(characters)

[1] 4

Each position is one string; the whole object is one vector.

Load a novel as data

janeaustenr provides complete Austen novels as character vectors.

Code

pride <- prideprejudice

length(pride)

[1] 13030

Code

head(pride, 8)

[1] "PRIDE AND PREJUDICE" ""                    "By Jane Austen"     
[4] ""                    ""                    ""                   
[7] "Chapter 1"           ""

Each element of pride is one line from Pride and Prejudice.

Make a small tibble

Tibbles work especially well with dplyr and stringr.

Code

pride_tbl <- tibble(
  line_number = seq_along(pride),
  text = pride
)

pride_tbl |> slice(1:6)

# A tibble: 6 × 2
  line_number text                 
        <int> <chr>                
1           1 "PRIDE AND PREJUDICE"
2           2 ""                   
3           3 "By Jane Austen"     
4           4 ""                   
5           5 ""                   
6           6 ""

Count characters

str_length() counts the characters in each string.

Code

str_length(c("Darcy", "Elizabeth", "Mr. Bingley"))

[1]  5  9 11

Code

pride_tbl |>
  mutate(characters = str_length(text)) |>
  arrange(desc(characters)) |>
  slice(1:3)

# A tibble: 3 × 3
  line_number text                                                    characters
        <int> <chr>                                                        <int>
1        6504 declaration affected me in any other way, than as it s…         74
2        2010 convenience of the world. I cannot forget the follies …         73
3        3507 that he should have attentive and conciliatory manners…         73

Change capitalization

Use a predictable case before matching names or categories.

Code

names <- c("ELIZABETH", "mr. darcy", "JaNe")

str_to_lower(names)

[1] "elizabeth" "mr. darcy" "jane"

Code

str_to_upper(names)

[1] "ELIZABETH" "MR. DARCY" "JANE"

Code

str_to_title(names)

[1] "Elizabeth" "Mr. Darcy" "Jane"

Do not change case if capitalization carries meaning in your data.

Remove extra whitespace

Whitespace can make identical-looking values fail to match.

Code

messy_names <- c("  Elizabeth", "Darcy  ", "  Jane  Bennet  ")

str_trim(messy_names)      # remove edges

[1] "Elizabeth"    "Darcy"        "Jane  Bennet"

Code

str_squish(messy_names)    # remove edges and repeat spaces

[1] "Elizabeth"   "Darcy"       "Jane Bennet"

Replace text

str_replace() changes the first match. str_replace_all() changes every match.

Code

line <- "Mr. Darcy spoke to Mr. Bingley."

str_replace(line, "Mr\\.", "Mister")

[1] "Mister Darcy spoke to Mr. Bingley."

Code

str_replace_all(line, "Mr\\.", "Mister")

[1] "Mister Darcy spoke to Mister Bingley."

The double backslash tells R to send a literal backslash to the pattern engine.

Combine strings

str_c() joins strings together.

Code

first <- c("Elizabeth", "Fitzwilliam")
last  <- c("Bennet", "Darcy")

str_c(first, last, sep = " ")

[1] "Elizabeth Bennet"  "Fitzwilliam Darcy"

Code

str_c("Chapter ", 1:3)

[1] "Chapter 1" "Chapter 2" "Chapter 3"

sep is placed between pieces. collapse joins a whole vector into one string.

Find a word

str_detect() returns TRUE or FALSE for each string.

Code

str_detect(
  c("Elizabeth smiled.", "Darcy was silent.", "Jane arrived."),
  "Elizabeth"
)

[1]  TRUE FALSE FALSE

This is useful because filter() keeps rows where a condition is TRUE.

Search the novel

Find lines that mention Elizabeth.

Code

pride_tbl |>
  filter(str_detect(text, "Elizabeth")) |>
  slice(1:5)

# A tibble: 5 × 2
  line_number text                                                              
        <int> <chr>                                                             
1         139 "\"But you forget, mamma,\" said Elizabeth, \"that we shall meet …
2         308 "Elizabeth Bennet had been obliged, by the scarcity of gentlemen,…
3         337 "Elizabeth, till catching her eye, he withdrew his own and coldly…
4         343 "Mr. Bingley followed his advice. Mr. Darcy walked off; and Eliza…
5         352 "her mother could be, though in a quieter way. Elizabeth felt Jan…

This search is case-sensitive: "elizabeth" would not match "Elizabeth".

Ignore capitalization in a search

Wrap the pattern in regex(..., ignore_case = TRUE).

Code

pride_tbl |>
  filter(str_detect(text, regex("darcy", ignore_case = TRUE))) |>
  slice(1:5)

# A tibble: 5 × 2
  line_number text                                                              
        <int> <chr>                                                             
1         281 "looked the gentleman; but his friend Mr. Darcy soon drew the att…
2         298 "themselves. What a contrast between him and his friend! Mr. Darc…
3         309 "down for two dances; and during part of that time, Mr. Darcy had…
4         314 "\"Come, Darcy,\" said he, \"I must have you dance. I hate to see…
5         330 "Darcy, looking at the eldest Miss Bennet."

Use this when you want Darcy, DARCY, and darcy to count as the same text.

Count matches

str_count() counts how often a pattern appears in each string.

Code

str_count(
  c("ha", "ha ha", "perhaps"),
  "ha"
)

[1] 1 2 1

Unlike str_detect(), this returns a number rather than TRUE or FALSE.

Which lines mention Darcy most?

Code

pride_tbl |>
  mutate(darcy_mentions = str_count(text, "Darcy")) |>
  filter(darcy_mentions > 0) |>
  arrange(desc(darcy_mentions)) |>
  select(line_number, darcy_mentions, text) |>
  slice(1:5)

# A tibble: 5 × 3
  line_number darcy_mentions text                                               
        <int>          <int> <chr>                                              
1        6918              2 of seeing Mr. Darcy--that Mr. Darcy might leave th…
2        8849              2 to Mr. Darcy were by no means over. Miss Darcy, on…
3         281              1 looked the gentleman; but his friend Mr. Darcy soo…
4         298              1 themselves. What a contrast between him and his fr…
5         309              1 down for two dances; and during part of that time,…

This keeps the original line and adds a new measurement.

Extract part of a string

str_sub() selects characters by position.

Code

books <- c("Emma", "Persuasion", "Mansfield Park")

str_sub(books, 1, 3)

[1] "Emm" "Per" "Man"

Code

str_sub(books, -4, -1)

[1] "Emma" "sion" "Park"

Negative positions count backward from the end of a string.

Split into columns

When every value has the same structure, separate_wider_delim() is often clearer.

Code

tibble(code = c("NY-2026-0142", "CA-2026-0143")) |>
  separate_wider_delim(
    code,
    delim = "-",
    names = c("state", "year", "record_id")
  )

# A tibble: 2 × 3
  state year  record_id
  <chr> <chr> <chr>    
1 NY    2026  0142     
2 CA    2026  0143

This converts one packed character column into three usable columns.

Useful regex building blocks

Pattern	Meaning	Example match
`.`	any one character	`c.t` matches `cat`
`\\d`	a digit	`2026` contains digits
`\\s`	whitespace	a space or tab
`^`	start of a string	`^Mr`
`$`	end of a string	`ing$`

Use regex one small piece at a time and test your pattern.

Practice: find a pattern

Use str_detect() to find IDs that begin with NY-.

Code

ids <- c("NY-2026-0142", "CA-2026-0143", "NY-2025-0088")

# Write your pattern here:
str_detect(ids, "...")

[1] TRUE TRUE TRUE

Hint: the start-of-string symbol is ^.

A dependable text-cleaning workflow

Inspect the original strings.
Decide what consistent means for this task.
Add a cleaned column; preserve the original.
Test a few known examples.
Count or filter to check the result.
Document any rule that removes or changes data.

Key functions to remember

Goal	Function
Count characters	`str_length()`
Clean spaces	`str_trim()`, `str_squish()`
Change case	`str_to_lower()`, `str_to_title()`
Find text	`str_detect()`
Count matches	`str_count()`
Replace text	`str_replace_all(), str_replace()`
Extract text	`str_sub()`
Split text	`str_split()`

Term	Meaning
\(\mu\)	Global mean rating
\(b_u\)	User bias: this user rates above or below average
\(b_i\)	Item bias: this item receives above or below average ratings

Quantity	Calculation	Rounded value
Global mean, \(\mu\)	mean of all observed ratings	3.93
Param bias, \(b_u\)	\(3.50 - 3.93\)	-0.43
`PitchPerfect2` bias, \(b_i\)	\(2.71 - 3.93\)	-1.22

DATA 607

Week 3: Character Data and String Manipulation

Tonight’s agenda

Due this week

Due next week

Global baseline estimates

The bias-based formula

Critic-movie ratings data

Step 1: global mean

Step 2a: critic bias

Step 2b: movie bias

Predict Param’s PitchPerfect2 rating

Recommend a movie for Param

When is a global baseline useful?

Window functions: PostgreSQL to R

Connect to the NFL607 database

Import the SQL table into an R data frame

Window functions keep every game row

Overall average and differential

Average by home team

Rank scores within each team

Running total by team

The R equivalent after import

PostgreSQL window functions mapped to dplyr

Aggregate functions: PostgreSQL to dplyr

Ranks, offsets, and running totals: PostgreSQL to dplyr

One dplyr pipeline that matches the SQL examples

When SQL and dplyr differ

Close the database connection

Window-function checklist

Text is everywhere in data work:

Before analysis, text often needs to be standardized, searched, split, or combined.

Learning goals

Packages for tonight

What is a string?

Character vectors

Load a novel as data

Make a small tibble

Count characters

Change capitalization

Remove extra whitespace

Replace text

Combine strings

Find a word

Search the novel

Ignore capitalization in a search

Count matches

Which lines mention Darcy most?

Extract part of a string

Split into columns

Useful regex building blocks

Practice: find a pattern

A dependable text-cleaning workflow

Key functions to remember

Predict Param’s `PitchPerfect2` rating