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"