{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),”“)

2b. Keep only posts about Spectrum the company (not radio spectrum, ZX Spectrum,

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(”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”)

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(“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)

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(“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")