Mutating a sentiment indicator using time conditions, rather than quarter/month (dplyr)

I have a reddit dataset where each row represents a single reddit post and I have a sentiment score for each reddit post by a given username. I also have a variable capturing the average sentiment for all posts written by the same username.

I am trying to create a sentiment indicator relevant to the timeline of a minimum wage policy, where I would like to categorize sentiment per username based on three periods:

1- Before the policy's announcement, let's say it's on "2021-03-01"
2- After the policy announcement yet before implementation, so after "2021-03-01" but before "2021-09-01"
3- after the policy's implementation, on "2021-09-01"

I have been able to compute sentiment for each username by month or quarter, as I show below but I would like to create sentiment per username based on the specific policy timeline above, and I am not sure how to do that.

Upload packages

library(tidyverse)
library(lubridate)
library(zoo)

Print data example with specific columns

dput(df[1:5,c(3,4,21, 22, 23)])

output:

structure(list(date = structure(c(15149, 15150, 15150, 15150, 
15150), class = "Date"), username = c("ax", "aa", 
"cartman", "abc", "aff"
), quarter_yr = c("2011 Q2", "2011 Q2", "2011 Q2", "2011 Q2", 
"2011 Q2"), sentiment_score = c("0", "-1", "1", "-1", "-1"), 
    avg_sentiment = c(0.0666666666666667, -0.777777777777778, 
    1, -1, -1)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -5L), groups = structure(list(username = c("ax", 
"cartman", "abc", "aff"), .rows = structure(list(5L, 4L, 1L, 2L, 3L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -5L), .drop = TRUE))

create a quarter/year variable

sentiment_df <- sentiment_df %>% 
  mutate(date = ymd(date),
         quarter_yr = paste(year(date), quarters(date)))

Compute an average sentiment score per username, based on the many observations/posts they have:

sentiment_df <-
df %>% group_by(username, quarter_yr) %>% summarise(avg_sentiment = mean(as.numeric(sentiment_score)))

Quarterly sentiment by username:

dput(sentiment_df[1:2,c(1,8)])

output

structure(list(username = c("cartman","aa"
), `2014 Q2` = c(NA_real_, NA_real_)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -2L), groups = structure(list(
    username = c("cartman","aa"), .rows = structure(list(
        1L, 2L), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE))

sentiment_df <- sentiment_df %>% 
  mutate(date = ymd(date),
         quarter_yr = paste(year(date), quarters(date)),
         phase = case_when(date < ymd(20210301) ~ "1 Before announcement",
                           date < ymd(20210901) ~ "2 Before implementation",
                           TRUE ~ "3 After implementation"))

sentiment_df <-
df %>% 
  group_by(username, phase) %>% 
  summarise(avg_sentiment = mean(as.numeric(sentiment_score)))

It seems like you simply create a new variable using mutate() and case_when(), and then group by the new variable. Here was my attempt. Is this what you are after?

library(dplyr)
library(lubridate)
library(zoo)
sentiment_df<-structure(list(date = structure(c(15149, 15150, 15150, 15150, 
                                  15150), class = "Date"), username = c("ax", "aa", 
                                                                        "cartman", "abc", "aff"
                                  ), quarter_yr = c("2011 Q2", "2011 Q2", "2011 Q2", "2011 Q2", 
                                                    "2011 Q2"), sentiment_score = c("0", "-1", "1", "-1", "-1"), 
               avg_sentiment = c(0.0666666666666667, -0.777777777777778, 
                                 1, -1, -1)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
                                 ), row.names = c(NA, -5L), groups = structure(list(username = c("ax", 
                                                                                                 "cartman", "abc", "aff"), .rows = structure(list(5L, 4L, 1L, 2L, 3L), ptype = integer(0), class = c("vctrs_list_of", 
                                                                                                                                                                                                     "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
                                                                                                                                                                                                     ), row.names = c(NA, -5L), .drop = TRUE))
sentiment_df <- sentiment_df %>%  mutate(date = ymd(date),
         quarter_yr = paste(year(date), quarters(date)),
         implementation_period = case_when(date < as.Date("2021-03-01") ~ "Before",
                            date >= as.Date("2021-03-01") & date < as.Date("2021-09-01") ~ "Pre_Implementation",
                            TRUE ~ "After"))

sentiment_df <-
  sentiment_df %>% group_by(username, implementation_period) %>% summarise(avg_sentiment = mean(as.numeric(sentiment_score)))

One quick note, in the data you provide there are only "Before" dates. But I think it should work on the whole dataset.