---
subtitle: "LSE ME204 Week 03 Day 03 - Lecture Material"
title: "Basic text manipulation with R"
author: "Dr. Jon Cardoso-Silva"
date: "2023-07-26"
date-format: "DD MMMM YYYY"
format: 
    html:
        df-print: kable
        self-contained: true
        link-external-icon: true
        toc: true
        theme: simplex
        code-fold: true
        page-layout: full
        fig-width: 10
        fig-height: 6
 
---

# Relevant functions

- [readxl::read_excel()](https://readxl.tidyverse.org/reference/read_excel.html)
- [lubridate::dmy()](https://lubridate.tidyverse.org/reference/dmy.html)
- [dplyr::case_when()](https://dplyr.tidyverse.org/reference/case_when.html)
- [dplyr::if_else()](https://dplyr.tidyverse.org/reference/if_else.html)
- [stringr::str_replace()](https://stringr.tidyverse.org/reference/str_replace.html)
- [stringr::str_starts()](https://stringr.tidyverse.org/reference/str_starts.html)
- [stringr::str_split()](https://stringr.tidyverse.org/reference/str_split.html)
- [dplyr::coalesce()](https://dplyr.tidyverse.org/reference/coalesce.html)

# ⚙️ Setup

```r

library(dplyr)
library(lubridate) # lubridate is back!

library(stringr) # We have used it here and there but time to understand it bette

library(quanteda) # this is a new one
library(quanteda.textplots) # also new


library(countrycode) # not a popular package but very useful for this dataset
```

This dataset we are going to use today, **P**olitical **A**pologies across **C**ultures (**PAC**), was assembled by the [Political Apologies x Cultures project](https://www.politicalapologies.com). We downloaded it from their website.

The data consists of an inventory of political apologies offered by states or state representatives to a collective for human rights violations that happened in the recent or distant past.

# Part 1: Good data does not necessarily equal tidy data (1 hour)

Before we get to explore the text, let's do the usual: load and pre-process the data. When you download the spreadsheet from their website, you will get a file called `PAC_Apologies_Public-Version-2.xlsx` which contains multiple tabs. We will focus on the first one, which is called `PAC Apologies`.

## The raw data

🎯 **ACTION POINT:** (10 min)

Before we even open it in R, take some time to look at the data in its raw format. Open the file `PAC_Apologies_Public-Version-2-modified.xlsx` using MS Office.

What do you see? Which tabs and columns seem interesting?

```markdown
Write your notes here
```

## Create a `df_pac`

No need to export the spreadsheet to a CSV! There is a specific tidyverse package to read **\*.xlsx** files (Excel spreadsheets): `readxl`. We will use it to read the data into R.

🎯 **ACTION POINT:** (35 min)

Ideally, we would just pass the name of the file and an indication of which tab we want to read. However, something is not quite right... I thought the code below should work but it keeps throwing a weird error. 

Try to identify what is wrong with the code and how to fix it:

```r
df_pac <- 
  readxl::read_excel("data/raw/PAC_Apologies_Public-Version-2.xlsx", sheet=1) |>
  mutate(`Date of apology`=lubridate::dmy(`Date of apology`)) |>
  select(-contains("Database "),-contains("Source ")) |> # I don't really need these columns
  arrange(desc(`Date of apology`))
```

Aim to get a table that looks somewhat like this (**Render** to view the table):

|  id|date_apology |parties_involved                          |country_sender                                       |country_receiver                 |description_apology                                                                                                                            |
|---:|:------------|:-----------------------------------------|:----------------------------------------------------|:--------------------------------|:----------------------------------------------------------------------------------------------------------------------------------------------|
| 512|2022-07-11   |Netherlands to Srebrenica                 |Netherlands                                          |Bosnia Herzegovina               |Dutch Minister of Defence Kasja Ollongren offered 'deepest apologies' for the Dutch failure to protect the victims of the Srebrenica Genocide. |
| 511|2022-06-08   |Belgium to the Congo                      |Belgium                                              |Democratic Republic of the Congo |Belgian King Filip expresses regret over Belgium's brutal colonial rule in a speech before the Congolese parliament.                           |
| 509|2022-03-23   |UK to Jamaica                             |United Kingdom of Great Britain and Northern Ireland |Jamaica                          |Prince William expressed his 'profound sorrow' for slavery in a speech during a visit to Jamaica.                                              |
| 498|2022-03-10   |Denmark to Greenlandic Inuit              |Denmark                                              |Greenland                        |Prime Minister Mette Frederiksen apologized in person to six surviving Greenlandic Inuit who were taken from their families as children.       |
| 497|2022-02-17   |The Netherlands to Indonesia and citizens |Netherlands                                          |Indonesia                        |Prime Minister Mark Rutte offered apologies for systematic and excessive violence in Indonesia's 1945-49 war of independence.                  |

Things to think about:

- Can you change the way you read the data or maybe rename input columns?
- We don't have a lot of data, so let's not leave any date of apologies behind (no NA's when converting to date format, please!). 

_We're expert R developers now; we can afford to use the new pipe symbol now :)_

::: {.callout-tip}

💡 Tip #1: the documentation should always be your first port of call when you get an error. Go to the [readxl](https://readxl.tidyverse.org/) page and search for the documentation of the `read_excel` function and try to find out how you can change the call to make it work. Alternatively, you can use the `?` operator to get the documentation directly in RStudio.

💡 Tip #2: you might want to learn how to use a new function, [stringr::str_replace()](https://stringr.tidyverse.org/reference/str_replace.html), to help you with this task. It's a very useful function to manipulate strings.

:::

```{r}
# Your code here
```


(FULL SOLUTION)

```r
df_pac <- 
  readxl::read_excel("data/raw/PAC_Apologies_Public-Version-2.xlsx", sheet=1, .name_repair="universal") |>
  `colnames<-`(c('id', 'date_apology', 'date_apology_again', 'parties_involved', 
                 'country_sender', 'country_receiver', 'description_apology', 
                 'database1', 'database2', 'database3', 'source1', 'source2', 'source3')) |>
  select(-contains("database"),-contains("source"), -`date_apology_again`) |> # I don't really need these columns  
  mutate(across(date_apology, ~ dmy(str_replace(str_replace(., "^0\\.0\\.", "1.1."), "^0\\.", "1.")))) |>
  arrange(desc(`date_apology`))
```

A good reasoning process should be as follows: once you realise that read_excel handles duplicated columns automatically, you should realise that you can't refer to `Date of Apology` in the code, as this column name does not exist. One solution is to just provide entirely new names to all the columns, like I did above.

Then, the second step is to use a bunch of `if_else` to find a match for the `0.0.` with `1.1.`:

```r
library(stringr)

df_pac |> 
  mutate(date_apology=if_else(str_starts(date_apology, "0.0."), 
                                str_replace(date_apology, "0.0.", "1.1."),
                                if_else(str_starts(date_apology, "0."), 
                                          str_replace(date_apology, "0.", "1."), 
                                          date_apology)), 
        date_apology=dmy(date_apology))
```

T

```r
library(stringr)

df_pac |> 
  mutate(date_apology=dmy(str_replace(str_replace(date_apology, "^0\\.0\\.", "1.1."), "^0\\.", "1.")))
```

(Now, look at this beauty!)

```r
df_pac |> 
  mutate(across(date_apology, ~ dmy(str_replace(str_replace(., "^0\\.0\\.", "1.1."), "^0\\.", "1."))))
```

# Part 2: Shorten country names to ISO codes (~30 min)

👨🏻‍🏫 **TEACHING MOMENT**

(That's me speaking for hopefully not a long time)

This wasn't really super important but I thought it would be nice to show you how to do it and demonstrate another useful function from the `stringr` package: `str_split()` (and its counterpart `str_split_i()`).

The code below is a way I found out to get the ISO codes for the countries in the dataset. I will explain it in class. It's also a good opportunity to learn of a new function, [dplyr::case_when()](https://dplyr.tidyverse.org/reference/case_when.html).

```r
# Preamble: function countrycode::countrycode() receives a country name and
#           returns its abbreviated version. However, some countries' names in
#           our data are not recognised by the function, so we need to treat these cases. 

df_pac <- 
  df_pac %>%
  # Rename a few cases to match countrycode::countrycode() mapping
  mutate(across(contains("country"),
                ~ dplyr::case_when(. == "GDR" ~ "German Democratic Republic",
                                   . == "Antigua and Barbuda" ~ "Antigua & Barbuda",
                                   . == "Phillipines" ~ "Philipines",
                                   TRUE ~ .))) |>
  
  # If the iso3c abbreviation standard does not work, use the p4c
  # If nothing else works, keep 
  mutate(across(contains("country"),
                ~ if_else(is.na(countrycode::countrycode(., 
                                                         origin="country.name",
                                                         destination="iso3c", 
                                                         warn=FALSE)),
                          if_else(is.na(countrycode::countrycode(., 
                                                                 origin="country.name",
                                                                 destination="p4c",
                                                                 warn=FALSE)),
                                  .,
                                  countrycode::countrycode(., 
                                                           origin="country.name",
                                                           destination="p4c", 
                                                           warn=FALSE)),
                          countrycode::countrycode(., 
                                                   origin="country.name", 
                                                   destination="iso3c", 
                                                   warn=FALSE)),
                .names="{.col} (abbr.)"))
```

(Now that you know how to use `~` to create anonymous functions, I used it to make the code more concise)

Despite my best efforts, the above code still left some countries with NA's in the `country_receiver (abbr.)` columns:

```r
df_pac |> 
select(id, contains('country')) |> 
filter(if_any(everything(), ~ is.na(.))) |> 
summarise(across(everything(), ~ sum(is.na(.))))
```

produces:

```text
# A tibble: 1 × 5
     id country_sender country_receiver `country_sender (abbr.)` `country_receiver (abbr.)`
  <int>          <int>            <int>                    <int>                      <int>
1     0              0               66                        0                         66
```

I don't have any missing values in the `country_sender` column but I have 66 missing values in the `country_receiver` column and consequently in the `country_receiver (abbr.)` column.

It turns out that we can still extract the receiver of the apology from the `Parties involved` column! Let me show you:

```r
df_pac |> mutate(extracted_receiver=str_split_i(parties_involved, " to ", 2)) |> select(parties_involved, testing)
```

produces:

|parties_involved                          |extracted_receiver     |
|:-----------------------------------------|:----------------------|
|Netherlands to Srebrenica                 |Srebrenica             |
|Belgium to the Congo                      |the Congo              |
|UK to Jamaica                             |Jamaica                |
|Denmark to Greenlandic Inuit              |Greenlandic Inuit      |
|The Netherlands to Indonesia and citizens |Indonesia and citizens |

Beautiful! We can use this to fill in the missing values in the `country_receiver (abbr.)` column:

```r
df_pac <- df_pac |> 
  mutate(`country_receiver (abbr.)`=if_else(is.na(`country_receiver (abbr.)`, 
                                              str_split_i(parties_involved, " to ", 2), 
                                              `country_receiver (abbr.)`))) 
```

Alternatively, I could learn a new function [dplyr::coalesce()](https://dplyr.tidyverse.org/reference/coalesce.html) and be more concise:

```r
df_pac <- df_pac |> 
  mutate(`country_receiver (abbr.)`=coalesce(`country_receiver (abbr.)`, 
                                             str_split_i(parties_involved, " to ", 2)))
```

[COALESCE](https://www.sqlitetutorial.net/sqlite-functions/sqlite-coalesce/) is also a SQL function that does the same thing. It is very useful when you have multiple columns with the same information and you want to fill in the missing values in one column with the values from another column.

# Part 3: Summarising text data (1 hour)

## Step 3.1: Of corpora and tokens

The first step when performing quantitative text analysis is to create a `corpus`: 

```r
corp_pac <- quanteda::corpus(df_pac, text_field="description_apology")
quanteda::docnames(corp_pac) <- df_pac$`id`

corp_pac
```

_Once you have constructed this corpus, use the summary() method to see a brief description of the corpus._

```r
df_corpus <- summary(corp_pac)

head(df_corpus, n=5)
```


```r
g <- (
  ggplot(df_corpus, aes(x=Tokens))
  + geom_bar()
  + theme_minimal()
  
  + labs(x="Number of Tokens",
         y="Count",
         title="How many tokens are there in the Description of the Apology?")
)

g
```
### Tokens

To count word frequencies, we first need to split the text into words (or longer phrases) through a process known as _tokenization_:

```r
# This function extracts the tokens
tokens_pac <- quanteda::tokens(corp_pac)
tokens_pac
```

```r
tokens_pac[[1]] # to look at just the first one
```

## Step 3.2: Document-Frequency Matrix (`dfm`)

Now, let's **summarise** the tokens in our corpus. We use the document-frequency matrix to create, as the name implies, a matrix that contains the number of times a token appears in a document.

```r
dfm_pac <- quanteda::dfm(tokens_pac) 
dfm_pac
```
We can convert `dfm_pac` to a dataframe if we like:

```r
dfm_as_data_frame <- quanteda::convert(dfm_pac, to="data.frame")
dim(dfm_as_data_frame)
```

🎯 **ACTION POINT** Look at the dataframe that represents the document-frequency matrix with either `View(dfm_as_data_frame)` or `head(dfm_as_data_frame)`

🗣️ **CLASSROOM DISCUSSIONS**: What do you think all of these columns represent?


### Top features

What are the most frequent tokens?

```r
dfm_pac %>% quanteda::topfeatures()
```

**It is fun to look at wordclouds:**

```r
quanteda.textplots::textplot_wordcloud(dfm_pac)
```

🗣️ **CLASSROOM DISCUSSIONS**: Do you think these most frequent terms are useful in helping us understand the political apologies? Would you remove certain tokens?

_Have you heard of the term 'stop words' before? Ask your class teacher about this! Quanteda has a built-in list of commonly used stop words, view it by running the function `quanteda::stopwords("en")`._


## Step 3.3: Keywords In Context (`kwic`)

Before we revisit our tokens, let's take a look at an interesting feature of `quanteda`. We can search for a pattern (a keyword) in our corpus and see the text that surrounds it using the `kwic` function.

🎯 **ACTION POINT:** Use `kwic` to search for keywords associated with different forms of explicit descriptions of apologies:

```r
quanteda::kwic(tokens_pac %>% head(n=10), pattern="apolog*")
```
