pkgs <- c("readxl", "dplyr", "stringr", "tidyr", "tidytext", "plotly",
          "htmlwidgets", "readr")
new <- pkgs[!pkgs %in% rownames(installed.packages())]
if (length(new)) install.packages(new, repos = "https://cloud.r-project.org")
invisible(lapply(pkgs, library, character.only = TRUE))
## Error in `FUN()`:
## ! there is no package called 'plotly'
infile <- "Scrape2_Spectrum_Phone_2026-11-17_2336.xlsx" 
stopifnot("Put the xlsx in the same folder as this .Rmd" = file.exists(infile))
raw <- read_excel(infile)
cat("Raw rows:", nrow(raw), "\n")
## Raw rows: 655
# 2a. Repair mojibake (UTF-8 bytes that were read as Windows-1252)
fix_mojibake <- function(x) {
  out <- vapply(x, function(s) {
    if (is.na(s)) return(NA_character_)
    if (!str_detect(s, "[\u00e2\u00f0]")) return(s)               # only touch damaged text
    y <- iconv(s, from = "UTF-8", to = "CP1252", sub = "")
    if (is.na(y)) return(s)
    Encoding(y) <- "UTF-8"
    if (validUTF8(y)) y else iconv(y, "UTF-8", "UTF-8", sub = "")
  }, character(1), USE.NAMES = FALSE)
  out
}

posts <- raw %>%
  mutate(
    text_fixed = fix_mojibake(text),
    text_fixed = str_squish(text_fixed),
    created_at = as.POSIXct(created_at, format = "%Y-%m-%dT%H:%M:%OSZ", tz = "UTC")
  ) %>%
  filter(!is.na(text_fixed), text_fixed != "") %>%      # drop image-only posts
  distinct(uri, .keep_all = TRUE) %>%                   # same post twice
  distinct(text_fixed, .keep_all = TRUE)                # reposts / copy-paste spam
cat("After removing empty + duplicate posts:", nrow(posts), "\n")
## After removing empty + duplicate posts: 469
# 2b. Keep only posts about Spectrum the company (not radio spectrum, ZX Spectrum,
#     autism spectrum, FCC auctions, Musk/Starlink, etc.)
exclude_pat <- paste(c(
  "zx spectrum", "autis", "neurodiv", "adhd", "light spectrum", "political spectrum",
  "on the spectrum", "spectrum of", "spectrum (license|licence|auction)", "mhz", "ghz",
  "starlink", "spacex", "lowband", "\\bfcc\\b", "satellite", "sinclair",
  "spectrum news 1", "spectrum sherlock", "emotion", "spectrum disorder",
  "across the spectrum", "end of the spectrum", "wavelength", "radio", "frequenc",
  "re-farm", "nuclear", "tiny tim", "microbiology", "em spectrum",
  "(mobile|wireless|lte|3g|4g|5g|uk|exclusive|nationwide) spectrum", "acquire",
  "ofcom", "crtc", "ispreview",
  "spectrum (portfolio|policy|renewal|framework|holdings|acquisition|rights|sale|band)",
  "trump", "musk", "elon", "\\bdish\\b", "auction", "self-care", "mental health",
  "boost mobile is", "cash\\.?app", "echostar", "\\$spcx"
), collapse = "|")

include_pat <- paste(c(
  "spectrum (mobile|internet|wifi|wi-fi|bill|app|store|customer|service|phone|tv|cable|",
  "router|modem|unlimited|one|support|outage|account|promo|rep|contract|\\$|-)",
  "|charter",
  "|\\b(my|with|from|to|not|hate|hi|at|call|via|use|using|have|has|switch\\w*|leave|",
  "leaving|left|ditch\\w*) spectrum",
  "|spectrum (sucks|is|has|just|charges|doesn|wants|raised|gave|said|told|would|will|",
  "keeps|tried|offered|came|customers)"
), collapse = "")

posts <- posts %>%
  mutate(low = str_to_lower(text_fixed)) %>%
  filter(str_detect(low, "spectrum"),
         str_detect(low, include_pat),
         !str_detect(low, exclude_pat)) %>%
  select(-low)
cat("After relevance filter (Spectrum customer-experience posts):", nrow(posts), "\n")
## After relevance filter (Spectrum customer-experience posts): 80
stopifnot("Relevance filter removed every post" = nrow(posts) > 0)

# 2c. Text column for analysis: strip URLs, @handles, hashtag symbols, non-ASCII
posts <- posts %>%
  mutate(
    text_clean = text_fixed %>%
      str_remove_all("https?://\\S+|www\\.\\S+") %>%
      str_remove_all("@[\\w\\.\\-]+") %>%
      str_replace_all("#", " ") %>%
      str_replace_all("\u2019|\u2018", "'") %>%
      str_replace_all("[^\\x01-\\x7F]", " ") %>%       # emoji / stray symbols
      str_squish(),
    engagement = like_count + repost_count + reply_count + quote_count,
    post_id    = row_number()
  )
custom_stop <- c("spectrum", "mobile", "phone", "plan", "internet", "month", "bill",
                 "t", "s", "m", "ve", "re", "ll", "d", "amp", "don", "didn", "isn")
# Note: "mobile", "phone", "plan", "bill" are in every search keyword, so they
# dominate counts without telling you anything. Delete them from custom_stop to see.

tokens <- posts %>%
  select(post_id, text_clean) %>%
  unnest_tokens(word, text_clean, token = "words", to_lower = TRUE) %>%
  filter(!str_detect(word, "^\\d+$"))

word_counts <- tokens %>%
  anti_join(stop_words, by = "word") %>%
  filter(!word %in% custom_stop) %>%
  count(word, sort = TRUE)
cat("\nTop 20 words:\n"); print(head(word_counts, 20))
## 
## Top 20 words:
## # A tibble: 20 × 2
##    word          n
##    <chr>     <int>
##  1 switch       20
##  2 service      18
##  3 pay          10
##  4 unlimited    10
##  5 verizon      10
##  6 data          9
##  7 free          9
##  8 line          9
##  9 cable         8
## 10 wifi          8
## 11 price         7
## 12 switching     7
## 13 call          5
## 14 cell          5
## 15 cost          5
## 16 fine          5
## 17 guarantee     5
## 18 tv            5
## 19 carrier       4
## 20 faster        4
bigram_counts <- posts %>%
  select(post_id, text_clean) %>%
  unnest_tokens(bigram, text_clean, token = "ngrams", n = 2) %>%
  filter(!is.na(bigram)) %>%
  separate(bigram, c("w1", "w2"), sep = " ") %>%
  filter(!w1 %in% stop_words$word, !w2 %in% stop_words$word,
         !w1 %in% custom_stop, !w2 %in% custom_stop) %>%
  count(w1, w2, sort = TRUE) %>%
  unite(bigram, w1, w2, sep = " ")
cat("\nTop 10 bigrams:\n"); print(head(bigram_counts, 10))
## 
## Top 10 bigrams:
## # A tibble: 10 × 2
##    bigram              n
##    <chr>           <int>
##  1 1 gbps              2
##  2 auto pay            2
##  3 cable companies     2
##  4 dollar discount     2
##  5 fine print          2
##  6 public wifi         2
##  7 unlimited data      2
##  8 verizon network     2
##  9 verizon towers      2
## 10 0.09 check          1
bing <- get_sentiments("bing")

# Domain fixes: in telecom talk, "cheap" and "free" are good news, and "premium"
# or "unlimited" are product names, not feelings. Edit freely.
bing <- bing %>%
  filter(!word %in% c("cheap", "free", "premium", "unlimited", "plus", "work")) %>%
  bind_rows(tibble(word = c("cheap", "free"), sentiment = "positive"))

negators <- c("not", "no", "never", "dont", "don't", "cant", "can't", "wont", "won't",
              "didnt", "didn't", "isnt", "isn't", "wasnt", "wasn't", "without")

post_words <- posts %>%
  select(post_id, text_clean) %>%
  unnest_tokens(word, text_clean, token = "words", to_lower = TRUE) %>%
  group_by(post_id) %>%
  mutate(prev = lag(word), prev2 = lag(word, 2)) %>%
  ungroup()

scored <- post_words %>%
  inner_join(bing, by = "word") %>%
  mutate(value  = if_else(sentiment == "positive", 1, -1),
         negated = prev %in% negators | prev2 %in% negators,
         value  = if_else(negated, -value, value))

post_sent <- scored %>%
  group_by(post_id) %>%
  summarise(pos_words = sum(value > 0), neg_words = sum(value < 0),
            sentiment_score = sum(value), .groups = "drop")

posts <- posts %>%
  left_join(post_sent, by = "post_id") %>%
  mutate(across(c(pos_words, neg_words, sentiment_score), ~ replace_na(.x, 0)),
         sentiment_label = case_when(sentiment_score > 0 ~ "Positive",
                                     sentiment_score < 0 ~ "Negative",
                                     TRUE ~ "Neutral"),
         sentiment_label = factor(sentiment_label,
                                  levels = c("Negative", "Neutral", "Positive")))

cat("\nSentiment distribution:\n"); print(table(posts$sentiment_label))
## 
## Sentiment distribution:
## 
## Negative  Neutral Positive 
##       24       21       35
cat("\nMean sentiment score by search keyword:\n")
## 
## Mean sentiment score by search keyword:
print(posts %>% group_by(search_keyword) %>%
        summarise(n = n(), mean_score = round(mean(sentiment_score), 2)) %>%
        arrange(mean_score))
## # A tibble: 6 × 3
##   search_keyword               n mean_score
##   <chr>                    <int>      <dbl>
## 1 Spectrum Mobile bill        16       0.06
## 2 Spectrum Mobile             11       0.09
## 3 Spectrum phone plan          7       0.14
## 4 Spectrum Mobile plan        26       0.35
## 5 Spectrum Mobile switch      13       0.38
## 6 Spectrum unlimited phone     7       0.57
top_sent_words <- scored %>% filter(!negated) %>%
  count(word, sentiment, sort = TRUE) %>% group_by(sentiment) %>% slice_head(n = 8)
cat("\nWords driving sentiment (check these make sense!):\n"); print(top_sent_words)
## 
## Words driving sentiment (check these make sense!):
## # A tibble: 16 × 3
## # Groups:   sentiment [2]
##    word      sentiment     n
##    <chr>     <chr>     <int>
##  1 fuck      negative      3
##  2 shit      negative      3
##  3 suck      negative      3
##  4 worst     negative      3
##  5 dark      negative      2
##  6 fucking   negative      2
##  7 issue     negative      2
##  8 slow      negative      2
##  9 free      positive      9
## 10 like      positive      8
## 11 fine      positive      5
## 12 faster    positive      4
## 13 savings   positive      4
## 14 cheaper   positive      3
## 15 guarantee positive      3
## 16 well      positive      3
top15 <- head(word_counts, 15)
p_words <- plot_ly(top15, x = ~n, y = ~reorder(word, n), type = "bar",
                   orientation = "h", marker = list(color = "#2166ac")) %>%
  layout(title = "Most frequent words in Spectrum Mobile posts (Bluesky)",
         xaxis = list(title = "Count"), yaxis = list(title = ""))
## Error in `layout()`:
## ! unused arguments (title = "Most frequent words in Spectrum Mobile posts (Bluesky)", xaxis = list(title = "Count"), yaxis = list(title = ""))
sent_by_kw <- posts %>% count(search_keyword, sentiment_label)
p_sent <- plot_ly(sent_by_kw, x = ~search_keyword, y = ~n, color = ~sentiment_label,
                  colors = c(Negative = "#b2182b", Neutral = "#999999", Positive = "#2166ac"),
                  type = "bar") %>%
  layout(barmode = "stack", title = "Sentiment of Spectrum posts by search keyword",
         xaxis = list(title = "Search keyword"), yaxis = list(title = "Posts"))
## Error in `layout()`:
## ! unused arguments (barmode = "stack", title = "Sentiment of Spectrum posts by search keyword", xaxis = list(title = "Search keyword"), yaxis = list(title = "Posts"))
p_words; p_sent
## Error:
## ! object 'p_words' not found
try(saveWidget(p_words, "spectrum_plot_words.html", selfcontained = FALSE))
## Error in saveWidget(p_words, "spectrum_plot_words.html", selfcontained = FALSE) : 
##   could not find function "saveWidget"
try(saveWidget(p_sent,  "spectrum_plot_sentiment.html", selfcontained = FALSE))
## Error in saveWidget(p_sent, "spectrum_plot_sentiment.html", selfcontained = FALSE) : 
##   could not find function "saveWidget"
cat("\n--- Verification ---\n")
## 
## --- Verification ---
stopifnot(!anyNA(posts$text_clean), !anyDuplicated(posts$uri))
cat("Posts with mojibake left:", sum(str_detect(posts$text_fixed, "\u00e2\u20ac|\u00f0\u0178")), "(want 0)\n")
## Posts with mojibake left: 0 (want 0)
cat("Posts with zero lexicon hits:", sum(posts$pos_words + posts$neg_words == 0),
    "of", nrow(posts), "(these are all labelled Neutral by default)\n")
## Posts with zero lexicon hits: 16 of 80 (these are all labelled Neutral by default)
cat("Random 10 posts to read by hand against their labels:\n")
## Random 10 posts to read by hand against their labels:
set.seed(1)
print(posts %>% slice_sample(n = 10) %>%
        transmute(sentiment_label, sentiment_score, text = str_trunc(text_clean, 110)),
      width = 200)
## # A tibble: 10 × 3
##    sentiment_label sentiment_score
##    <fct>                     <dbl>
##  1 Positive                      1
##  2 Positive                      1
##  3 Positive                      3
##  4 Negative                     -1
##  5 Neutral                       0
##  6 Negative                     -2
##  7 Negative                     -3
##  8 Positive                      2
##  9 Positive                      2
## 10 Neutral                       0
##    text                                                                         
##    <chr>                                                                        
##  1 "This feels weird. I've had T-Mobile for 21 years, but tonight signed up to …
##  2 "I have Spectrum and with a certain plan you get Disney+, Paramount +, HBO M…
##  3 "For those curious, be wary of cable providers (hi Spectrum). When we first …
##  4 "I cut em loose winter of 2022 when I moved to Texas. Inwas paying over $80.…
##  5 "As part of its \"Life Unlimited\" campaign, Charter is including taxes and …
##  6 "service highest tier is about $100 less than I was paying for a piece of sh…
##  7 "We're moving next week and trying to get my internet service cancelled thro…
##  8 "Yeah I can't bash on them too much. My apartment only has AT&T and Spectrum…
##  9 "Spectrum Internet Customers: 256GB Samsung Galaxy S26+ Free w/ Eligible Tra…
## 10 "I had T-Mobile for 7 years and ATT before that. Paid a lot every month. Now…
# Compare with the hand-coded file (optional): spectrum_segmentation_clean.xlsx
manual_file <- "spectrum_segmentation_clean.xlsx"
if (file.exists(manual_file)) {
  man <- read_excel(manual_file, sheet = "Data") %>%
    left_join(read_excel(manual_file, sheet = "Post_Text") %>% select(ID, uri), by = "ID") %>%
    mutate(manual_label = factor(c("Positive", "Neutral", "Negative")[Sentiment],
                                 levels = c("Negative", "Neutral", "Positive"))) %>%
    select(uri, manual_label)
  cmp <- inner_join(posts, man, by = "uri")
  cat("\nPosts matched to hand coding:", nrow(cmp), "of", nrow(man), "\n")
  print(table(Lexicon = cmp$sentiment_label, Manual = cmp$manual_label))
  acc <- mean(cmp$sentiment_label == cmp$manual_label)
  pe  <- sum(prop.table(table(cmp$sentiment_label)) * prop.table(table(cmp$manual_label)))
  cat(sprintf("Agreement: %.0f%%   Cohen's kappa: %.2f\n", 100 * acc, (acc - pe) / (1 - pe)))
}
write_csv(posts %>% select(post_id, author_handle, created_at, search_keyword,
                           text_clean, engagement, pos_words, neg_words,
                           sentiment_score, sentiment_label, uri),
          "spectrum_clean_posts.csv")
## Error in `write_csv()`:
## ! could not find function "write_csv"
sessionInfo()
## R version 4.6.2 (2026-10-29)
## Platform: x86_64-pc-linux-gnu
## Running under: Ubuntu 24.04.5 LTS
## 
## Matrix products: default
## BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3 
## LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.26.so;  LAPACK version 3.12.0
## 
## locale:
##  [1] LC_CTYPE=C.UTF-8       LC_NUMERIC=C           LC_TIME=C.UTF-8       
##  [4] LC_COLLATE=C.UTF-8     LC_MONETARY=C.UTF-8    LC_MESSAGES=C.UTF-8   
##  [7] LC_PAPER=C.UTF-8       LC_NAME=C              LC_ADDRESS=C          
## [10] LC_TELEPHONE=C         LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C   
## 
## time zone: UTC
## tzcode source: system (glibc)
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] tidytext_0.4.3 tidyr_1.3.2    stringr_1.6.0  dplyr_1.2.1    readxl_1.6.0  
## 
## loaded via a namespace (and not attached):
##  [1] Matrix_1.7-6      jsonlite_2.0.0    janeaustenr_1.0.0 compiler_4.6.2   
##  [5] Rcpp_1.1.2        tidyselect_1.2.1  jquerylib_0.1.4   yaml_2.3.12      
##  [9] fastmap_1.2.0     lattice_0.23-1    R6_2.6.1          SnowballC_0.7.1  
## [13] generics_0.1.4    knitr_1.52        tibble_3.3.1      bslib_0.12.0     
## [17] pillar_1.11.1     tokenizers_0.3.0  rlang_1.3.0       utf8_1.2.6       
## [21] cachem_1.1.0      stringi_1.8.9     xfun_0.62         sass_0.4.10      
## [25] cli_3.6.6         withr_3.0.3       magrittr_2.0.5    digest_0.6.39    
## [29] grid_4.6.2        lifecycle_1.0.5   vctrs_0.7.3       evaluate_1.0.5   
## [33] glue_1.8.1        cellranger_1.1.0  rmarkdown_2.32    purrr_1.2.2      
## [37] tools_4.6.2       pkgconfig_2.0.3   htmltools_0.5.9
getwd()
## [1] "/cloud/project"