{r setup, include=FALSE} knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE) knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE, error = TRUE)
{r Packages} 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))
{r Load} 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")
```{r Clean} # 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,
“[0e20f0]”)) return(s)
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 != ““) %>%
distinct(uri, .keep_all = TRUE) %>%
distinct(text_fixed, .keep_all = TRUE)
cat(”After removing empty + duplicate posts:“, nrow(posts),”“)
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), “”)
posts <- posts %>% mutate( text_clean = text_fixed %>%
str_remove_all(“https?://\S+|www\.\S+”) %>%
str_remove_all(“@[\w\.\-]+”) %>% str_replace_all(“#”, ” “) %>%
str_replace_all(”019|018”, “’”) %>% str_replace_all(“[^\\x01-\\x7F]”,
” “) %>%
str_squish(), engagement = like_count + repost_count + reply_count +
quote_count, post_id = row_number() )
```{r Text_Analysis}
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))
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))
```{r Sentiment_Analysis} bing <- get_sentiments(“bing”)
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(“distribution:”); print(table(posts$sentiment_label)) cat(“sentiment score by search keyword:”) print(posts %>% group_by(search_keyword) %>% summarise(n = n(), mean_score = round(mean(sentiment_score), 2)) %>% arrange(mean_score))
top_sent_words <- scored %>% filter(!negated) %>% count(word, sentiment, sort = TRUE) %>% group_by(sentiment) %>% slice_head(n = 8) cat(“driving sentiment (check these make sense!):”); print(top_sent_words)
```{r Plotly_Chart}
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 = ""))
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"))
p_words; p_sent
saveWidget(p_words, "spectrum_plot_words.html", selfcontained = FALSE)
saveWidget(p_sent, "spectrum_plot_sentiment.html", selfcontained = FALSE)
```{r Verification} cat(“— 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") cat("Posts with zero lexicon hits:", sum(posts\)pos_words + posts$neg_words == 0), “of”, nrow(posts), “(these are all labelled Neutral by default)”) cat(“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)
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(“matched to hand coding:”, nrow(cmp), “of”, nrow(man), “”) 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”, 100 * acc, (acc - pe) / (1 - pe))) }
```{r Save}
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")