Overall Goals

• Into to Text Mining
• Keyword searches in Excel
• Getting started with R
• Text mining in R
• Text mining in Python

Poll

1. What do you think of when I say text mining?
2. How much experience do you have with coding languages like R or Python?
3. What types of things are you most interested in? (keywords, sentiment, ngrams, word clouds…)

Intro to Text Mining

What is text mining?

Text mining is the process of extracting information and insights from unstructured text.

Terms to Know

• Natural Language: A natural language, as opposed to an artificially created language such as R or Python, develops gradually and often without thought, over time. (Examples include English or Italian.)
• Natural Language Processing (NLP): refers to the creation and use of computational power in order to process content in natural language.

Terms to Know

• Segmentation/Tokenization: the process of splitting apart pieces of language. Sentence segmentation is the process of breaking sentences apart using tokenizers to examine punctuation, abbreviations, and capitalization. Word tokenization involves breaking out the individual words of a sentence.

Terms to Know

• Text Normalization: involves standardizing text prior to analysis. Examples include the expansion of contractions (don’t -> do not), removal of stop words (of, and, it), correcting misspellings, and stemming (if required)

Terms to Know

• Term Frequency: measures how often a term appears in a document.
• Document Frequency: measures how often a term appears in a corpus of documents.

Terms to Know

• Inverse Document Frequency: a number computed by dividing the total number of documents in the corpus by the number of documents containing the target term and applying a log scale.
• TF-IDF: “Term Frequency-Inverse Document Frequency”. Higher term frequency and a lower document frequency leads to a higher TF-IDF.

Levels of Analysis

• Lexical Analysis: The most basic form of NLP, lexical analysis is focused on analyzing individual words.
• Syntactic Analysis: concerned with processing the grammar of written words.
• Semantic Analysis: builds on lexical and syntactic analyses in order to understand the meanings of words.
• Discourse Analysis: Understanding inferences in language is the domain of discourse analysis.

Choosing a Tool

• Experience: what do you know best?
• Availability: what do you have access to?
• Support: where can you turn when you have problems?
• Longevity: if you win the lottery tomorrow can someone take over?

Excel

Excel Agenda

• Pros and Cons
• Formulas
• Using ChatGPT to help with formulas
• Keyword Search
• Word Count

Pros and Cons

Pros - Universally available - Not going anywhere - Low barrier to entry - Scales in complexity

:::

:::

Cons - Can be difficult to reproduce - Potentially destructive - Doesn’t scale well - Limited options for text mining

:::

::::

Formula: vlookup

=VLOOKUP(Value to look up, Range to look for value, Column for the return value, Type of match)
=VLOOKUP(A1, Lookup!A1:B7, 2, TRUE)

Formula: absolute/relative references

Relative references can move.

A1

Absolute references can’t move.

$A$1

Formula: absolute/relative references

$A$2	The column and the row do not change when copied.
A$2	The row does not change when copied.
$A2	The column does not change when copied.

Formula: countif

=COUNTIF(Where are we looking?, What are we looking for?)

=COUNTIF(A:A, “Washington”)

ChatGPT for Formula Help

Warning

Assume that everything you put into ChatGPT (or other LLMs) is public. Do not enter confidential or proprietary information into it.

Tell ChatGPT what is is you need assistance with:

ChatGPT for Formula Help

Formula: keyword search

Source: ChatGPT

=IF(SUMPRODUCT(–ISNUMBER(SEARCH(Keywords_Range, Cell)))>0, “Found”, “Not Found”)

Formula: keyword search

=IF(SUMPRODUCT(–ISNUMBER(SEARCH(Keywords_Range, Cell)))>0, “Found”, “Not Found”)

1. Replace Keywords_Range with the actual range of keywords you want to search for. For example, if your keywords are in cells A1 to A10, the range would be A1:A10.

Formula: keyword search

=IF(SUMPRODUCT(–ISNUMBER(SEARCH(Keywords_Range, Cell)))>0, “Found”, “Not Found”)

2. Replace Cell with the cell reference of the cell in the column you want to search. This is the cell where you want to check if any of the keywords are present. For example, if you want to search in column B, and the first cell is B2, you would use B2 as the cell reference.

Formula: keyword search

Source: ChatGPT

=IF(SUMPRODUCT(–ISNUMBER(SEARCH(Keywords_Range, Cell)))>0, “Found”, “Not Found”)

3. Enter the formula in the cell where you want the search result to appear.

The formula will return “Found” if any of the keywords in the range are found in the specified cell, and “Not Found” if none of the keywords are found.

Note: This formula is case-insensitive, so it will match keywords regardless of their case. If you want case-sensitive matching, you can use the FIND function instead of SEARCH in the formula.

R

Getting Started

1. What is R?
2. R vs. Python?

R is a programming language started by professors Ross Ihaka and Robert Gentleman as a programming language to teach introductory statistics at the University of Auckland. It was developed in the early 1990’s and open sourced in 1995.

For the purposes of this workshop, there are no significant differences in R vs. Python other than the language and packages/libraries used. I chose to start with R because that’s where I’m most comfortable, it’s the language that I see Apra promoting, and the community is incredibly welcoming and diverse.

Getting Started

Tip

Everything in this workshop can be done through the virtual project. Scan the QR code to get started.

Discuss the layout of RStudio and what people are looking at.

RStudio IDE/Posit Cloud

RStudio IDE/Posit Cloud

Packages

• Libraries of code
• Expand the “base R” code

The tidyverse

• What is it?
• How does it work?

The tidyverse vs ‘Base R’

Source: dplyr base R

tidytext

• What is it?
• How does it work?

The Data

42,656 reviews from Disney California, Hong Kong, and Paris

Source: Kaggle

Getting Started

# Install libraries
# install.packages("tidyverse")
# install.packages("tidytext")

# Load libraries
library(tidyverse)
library(tidytext)

# Read in the data
df <- read_csv("DisneylandReviews.csv")

Examine the Data

# head(df)
# str(df)
glimpse(df)

Rows: 42,656
Columns: 6
$ Review_ID         <dbl> 670772142, 670682799, 670623270, 670607911, 67060729…
$ Rating            <dbl> 4, 4, 4, 4, 4, 3, 5, 3, 2, 5, 5, 5, 4, 5, 5, 3, 4, 3…
$ Year_Month        <chr> "2019-4", "2019-5", "2019-4", "2019-4", "2019-4", "2…
$ Reviewer_Location <chr> "Australia", "Philippines", "United Arab Emirates", …
$ Review_Text       <chr> "If you've ever been to Disneyland anywhere you'll f…
$ Branch            <chr> "Disneyland_HongKong", "Disneyland_HongKong", "Disne…

Look at the Parks

unique(df$Branch)

[1] "Disneyland_HongKong"   "Disneyland_California" "Disneyland_Paris"     

Clean up Park Names

df <- df %>% 
  rename(Park = Branch) %>% 
  mutate(Park = recode(Park,
                       "Disneyland_California" = "California",
                       "Disneyland_HongKong" = "Hong Kong",
                       "Disneyland_Paris" = "Paris"
  ))

unique(df$Park)

[1] "Hong Kong"  "California" "Paris"     

Examine the Data

df %>% 
  ggplot(aes(Rating)) +
  geom_bar(fill = "steelblue", color = "black") +
  labs(title = "Distribution of Ratings",
       x = "Rating",
       y = "Count") +
  scale_y_continuous(labels = scales::comma) +
  theme_minimal()

Examine the Data

df %>% 
  ggplot(aes(Rating, fill = Park)) +
  geom_bar(color = "black") +
  labs(title = "Distribution of Ratings by Park",
       x = "Rating",
       y = "Count",
       fill = "Park") +
  scale_y_continuous(labels = scales::comma) +
  scale_fill_discrete() +
  theme_minimal()

Examine the Data

df %>% 
  ggplot(aes(Park, Rating, color = Park)) +
  geom_boxplot(color = "black") +
  geom_jitter(alpha = 0.3) +
  labs(title = "Distribution of Ratings by Park",
       x = "Rating",
       y = "Count") +
  theme_minimal()

Sample Data

Sample Review

df$Review_Text[15]

[1] "This place is HUGE! Definately need more than one day. We had 3 children aged 11, 9 & 6 and they loved it. A great variety of rides and attractions for all ages. Food options were fantastic with 3D models of what you were ordering. Staff were fantastic, very helpful. An awesome family experience."

Sample Review

# Convert it to a tibble
sample <- tibble(line = 1, text = df$Review_Text[15])
sample

# A tibble: 1 × 2
   line text                                                                    
  <dbl> <chr>                                                                   
1     1 This place is HUGE! Definately need more than one day. We had 3 childre…

Sample Review: Unnest Tokens

# Unnest tokens
tidy_sample <- sample %>% 
  unnest_tokens(word, text)
tidy_sample

# A tibble: 53 × 2
    line word      
   <dbl> <chr>     
 1     1 this      
 2     1 place     
 3     1 is        
 4     1 huge      
 5     1 definately
 6     1 need      
 7     1 more      
 8     1 than      
 9     1 one       
10     1 day       
# ℹ 43 more rows

Sample Review: Word Count

# Word count
tidy_sample %>% 
  count(word, sort = TRUE) %>% 
  head()

# A tibble: 6 × 2
  word          n
  <chr>     <int>
1 were          3
2 and           2
3 fantastic     2
4 of            2
5 11            1
6 3             1

Sample Review: Word Count w/o Stop Words

# Word count without stop words
tidy_sample %>% 
  filter(!word %in% stop_words$word) %>% 
  count(word, sort = TRUE) %>% 
  head()

# A tibble: 6 × 2
  word          n
  <chr>     <int>
1 fantastic     2
2 11            1
3 3             1
4 3d            1
5 6             1
6 9             1

Processing the Data

Number Each Review

# Number each review for each park
reviews <- df %>%
  group_by(Park) %>%
  mutate(linenumber = row_number()) %>% 
  ungroup() %>% 
  select(Park, linenumber, text = Review_Text) %>% 
  arrange(Park, linenumber)

head(reviews)

# A tibble: 6 × 3
  Park       linenumber text                                                    
  <chr>           <int> <chr>                                                   
1 California          1 This place has always been and forever will be special.…
2 California          2 A great day of simple fun and thrills. Bring cash, noth…
3 California          3 All and all a great day was had. The crowds are huge an…
4 California          4 Having been to the Florida location numerous times over…
5 California          5 Had the 4 day pass, spent 3 at DL and one at CA. Great …
6 California          6 Oh my god you can really forget your self and enjoy eve…

Unnest Tokens & Remove Stop Words

# Unnest tokens and remove stop words
tidy_reviews <- reviews %>% 
  unnest_tokens(word, text) %>% 
  anti_join(stop_words)

head(tidy_reviews)

# A tibble: 6 × 3
  Park       linenumber word      
  <chr>           <int> <chr>     
1 California          1 forever   
2 California          1 special   
3 California          1 feeling   
4 California          1 entering  
5 California          1 park      
6 California          1 characters

Word Counts

# Perform word count
tidy_reviews %>% 
  count(word, sort = TRUE) %>% 
  head()

# A tibble: 6 × 2
  word           n
  <chr>      <int>
1 park       44557
2 disney     36187
3 rides      34508
4 disneyland 32935
5 time       29432
6 day        28332

Word Clouds

N-Grams

• A continuous sequence of n words
• Can be used ‘as is’, or run through a stemmer to get morphemes
• Offers greater context than single words

Bigrams (prep)

# Unnest into bigrams
tidy_bigrams <- reviews %>% 
  unnest_tokens(bigram, text, token = "ngrams", n = 2) %>% 
  filter(!is.na(bigram))

# Separate words
tidy_bigrams <- tidy_bigrams %>%
  separate(bigram, c("word1", "word2"), sep = " ")

# Remove stop words
tidy_bigrams <- tidy_bigrams %>%
  filter(!word1 %in% stop_words$word) %>%
  filter(!word2 %in% stop_words$word)

# Reunite terms
tidy_bigrams <- tidy_bigrams %>%
  unite(bigram, word1, word2, sep = " ")

Bigrams

tidy_bigrams %>% 
  group_by(Park) %>% 
  count(bigram) %>% 
  arrange(desc(n)) %>% 
  head()

# A tibble: 6 × 3
# Groups:   Park [3]
  Park       bigram                   n
  <chr>      <chr>                <int>
1 California fast pass             3123
2 Hong Kong  hong kong             3025
3 Paris      disneyland paris      2932
4 California california adventure  2293
5 Paris      fast pass             1940
6 California disney world          1926

Word & Document Frequencies

Sentiment Analysis

An introduction using the ‘joy’ sentiment.

# Get 'joy' sentiment
nrc_joy <- tidytext::get_sentiments("nrc")
nrc_joy <- nrc_joy %>% 
  filter(sentiment == "joy")

head(nrc_joy)

# A tibble: 6 × 2
  word          sentiment
  <chr>         <chr>    
1 absolution    joy      
2 abundance     joy      
3 abundant      joy      
4 accolade      joy      
5 accompaniment joy      
6 accomplish    joy      

Sentiment Analysis

# Most common 'joy' words in the reviews
tidy_reviews %>%
  inner_join(nrc_joy) %>%
  count(word, sort = TRUE) %>% 
  head()

# A tibble: 6 × 2
  word        n
  <chr>   <int>
1 food    14322
2 fun      9952
3 parade   8468
4 love     5967
5 magical  5190
6 enjoy    4807

Sentiment Analysis

tidy_reviews_sentiment <- tidy_reviews %>%
  inner_join(get_sentiments("bing")) %>%
  count(Park, index = linenumber %/% 80, sentiment) %>%
  pivot_wider(names_from = sentiment, values_from = n, values_fill = 0) %>%
  mutate(sentiment = positive - negative)

head(tidy_reviews_sentiment)

# A tibble: 6 × 5
  Park       index negative positive sentiment
  <chr>      <dbl>    <int>    <int>     <int>
1 California     0      144      313       169
2 California     1      174      310       136
3 California     2      149      340       191
4 California     3      143      283       140
5 California     4      170      399       229
6 California     5      123      266       143

Sentiment Analysis

ggplot(tidy_reviews_sentiment, aes(index, sentiment, fill = Park)) +
  geom_col(show.legend = FALSE) +
  facet_wrap(~ Park, ncol = 1, scales = "free_x") +
  labs(title = "Sentiment Analysis by Park") +
  theme_minimal()

Topic Modeling

Python

Python Libraries

Recreating R Work

Learning More

Related to this Workshop

• R for Data Science
• Text Mining with R

Other R Links

• Big Book of R

Get in Touch

matt.farrow@childrens.com