---
title: "Handling tables in R"
author: "Martin Schweinberger"
date: ""
output:
  bookdown::html_document2
bibliography: bibliography.bib
link-citations: yes
---

```{r uq1, echo=F, fig.cap="", message=FALSE, warning=FALSE, out.width='100%'}
knitr::include_graphics("https://slcladal.github.io/images/uq1.jpg")
```

# Introduction{-}

This tutorial shows how to work with tables and how to tabulate data in R. A more advanced and detailed but also truly excellent and highly recommendable resource on processing data in R is @wickham2016r [see also @wickham2019welcome]. Alternative but also very useful resources are @stander2017enthusing and @estrellado2020data.

```{r diff, echo=FALSE, out.width= "15%", out.extra='style="float:right; padding:10px"'}
knitr::include_graphics("https://slcladal.github.io/images/gy_chili.jpg")
```

This tutorial is aimed at beginners and intermediate users of R with the aim of showcasing how to work with and process tabulated data (tables) using R. The aim is not to provide a fully-fledged analysis but rather to show and exemplify selected useful functions and methods associated with tabular data. 



<div class="warning" style='padding:0.1em; background-color:#f2f2f2; color:#51247a'>
<span>
<p style='margin-top:1em; text-align:center'>
To be able to follow this tutorial, we suggest you check out and familiarize yourself with the content of the following **R Basics** tutorials:<br>
</p>
<p style='margin-top:1em; text-align:left'>
<ul>
  <li>[Getting started with R](https://ladal.edu.au/intror.html) </li>
  <li>[Loading, saving, and generating data in R](https://ladal.edu.au/load.html) </li>
  <li>[String Processing in R](https://ladal.edu.au/string.html) </li>
  <li>[Regular Expressions in R](https://ladal.edu.au/regex.html) </li>
</ul>
</p>
<p style='margin-top:1em; text-align:center'>
Click [**here**](https://ladal.edu.au/content/table.Rmd)^[If you want to render the R Notebook on your machine, i.e. knitting the document to html or a pdf, you need to make sure that you have R and RStudio installed and you also need to download the [**bibliography file**](https://slcladal.github.io/content/bibliography.bib) and store it in the same folder where you store the Rmd file.] to download the **entire R Notebook** for this tutorial.<br><br>
[![Binder](https://mybinder.org/badge_logo.svg)](https://binderhub.atap-binder.cloud.edu.au/v2/gh/SLCLADAL/interactive-notebooks-environment/main?urlpath=git-pull%3Frepo%3Dhttps%253A%252F%252Fgithub.com%252FSLCLADAL%252Finteractive-notebooks%26urlpath%3Dlab%252Ftree%252Finteractive-notebooks%252Fnotebooks%252Ftable_cb.ipynb%26branch%3Dmain)<br>
Click [**here**](https://binderhub.atap-binder.cloud.edu.au/v2/gh/SLCLADAL/interactive-notebooks-environment/main?urlpath=git-pull%3Frepo%3Dhttps%253A%252F%252Fgithub.com%252FSLCLADAL%252Finteractive-notebooks%26urlpath%3Dlab%252Ftree%252Finteractive-notebooks%252Fnotebooks%252Ftable_cb.ipynb%26branch%3Dmain) to open an interactive Jupyter notebook that allows you to execute, change, and edit the code as well as to upload your own data. <br>
</p>
<p style='margin-left:1em;'>
</p></span>
</div>

<br>

## Preparation and session set up{-}

This tutorial is based on R. If you have not installed R or are new to it, you will find an introduction to and more information how to use R [here](https://slcladal.github.io/intror.html). For this tutorials, we need to install certain *packages* from an R *library* so that the scripts shown below are executed without errors. Before turning to the code below, please install the packages by running the code below this paragraph. If you have already installed the packages mentioned below, then you can skip ahead ignore this section. To install the necessary packages, simply run the following code - it may take some time (between 1 and 5 minutes to install all of the packages so you do not need to worry if it takes some time).

```{r prep1, eval = F, message=FALSE, warning=FALSE}
# install packages
install.packages("xlsx")
install.packages("tidyverse")
install.packages("flextable")
install.packages("openxlsx")
install.packages("here")
# install klippy for copy-to-clipboard button in code chunks
install.packages("remotes")
remotes::install_github("rlesur/klippy")
```

Now that we have installed the packages, we can activate them as shown below.

```{r prep2, message=FALSE, warning=FALSE} 
# load packages
library(dplyr)
library(tidyr)
library(flextable)
library(xlsx)
library(openxlsx)
library(here)
# activate klippy for copy-to-clipboard button
klippy::klippy()
```

Once you have installed RStudio and initiated the session by executing the code shown above, you are good to go.

# Getting started{-}

Tables are one of the most common and important ways to process, handle, and describe data. This tutorial introduces different types of tables, how to load and save different types of tables, as well as how to modify and process tables and tabulated data.

When working with R, there are different kinds or types of tables that have different characteristics. The most common types of tables in R are:

* matrices
* data frames
* tibbles

Matrices can only contain one type of data and all data points will be converted to the type of scale with the lowest information value. For instance, if at least one variables in a table represents characters (or strings), then all other variables are also converted into characters (although they may be numbers). 

Data frames can contain different types of scales and variables are not converted automatically. In this sense, data frames are more flexible and are preferable over matrices.

Tibbles are the tidyverse equivalent of data frames which offer new functions and possibilities of handling and inspecting the data. . 

# Loading tables into R{-}

There are several different functions that allow us to read tabulated data into R. In our case, we use the `readRDS` function which loads Rdata sets. 

```{r lt1, message=FALSE, warning=FALSE}
# load data with read.delim
mytable <- base::readRDS(url("https://slcladal.github.io/data/mld.rda", "rb"))
```


```{r line_0, echo = F, warning=F, message=F}
mytable %>%
  as.data.frame() %>%
  head(10) %>%
  flextable() %>%
  flextable::set_table_properties(width = .5, layout = "autofit") %>%
  flextable::theme_zebra() %>%
  flextable::fontsize(size = 12) %>%
  flextable::fontsize(size = 12, part = "header") %>%
  flextable::align_text_col(align = "center") %>%
  flextable::set_caption(caption = "First 10 rows of mytable.")  %>%
  flextable::border_outer()
```


If  the data is stored as a txt-file, there are various functions to read in the data. The most common functions are `read.delim` and `read.table`. 

```{r lt2, eval = F, message=FALSE, warning=FALSE}
# load data with read.delim
tab1 <- read.delim("https://slcladal.github.io/data/mlrdata.txt", 
                   sep = "\t", header = TRUE)
tab2 <- read.table("https://slcladal.github.io/data/mlrdata.txt", header = TRUE)
```

To load excel data from the web, you can use the `read.xlsx` function from the `openxlsx` package.

```{r lt3b, message=FALSE, warning=FALSE}
# load data
exceldata <- openxlsx::read.xlsx("https://slcladal.github.io/data/testdata1.xlsx", 
                                 sheet = 1)
```

```{r line_01b, echo = F, warning=F, message=F}
exceldata %>%
  as.data.frame() %>%
  head(10) %>%
  flextable() %>%
  flextable::set_table_properties(width = .5, layout = "autofit") %>%
  flextable::theme_zebra() %>%
  flextable::fontsize(size = 12) %>%
  flextable::fontsize(size = 12, part = "header") %>%
  flextable::align_text_col(align = "center") %>%
  flextable::set_caption(caption = "First 10 rows of the exceldata.")  %>%
  flextable::border_outer()
```

To load excel data from your own computer, you can use the `read_excel` function from the `readxl` package (which is part of the tidyverse and thus does not need to be installed separately.

***

<div class="warning" style='padding:0.1em; background-color:#51247a; color:#f2f2f2'>
<span>
<p style='margin-top:1em; text-align:center'>
<b>NOTE</b><br><br>You would need to adapt the path to the data (which currently points to the data folder on my computer!). Thus, the path below is specified to my computer.</p>
<p style='margin-left:1em;'>
</p></span>
</div>

<div class="question">` 


</div>`

***


```{r excel7, message=FALSE, warning=FALSE}
# load data
excelcomp <- readxl::read_excel(here::here("data", "testdata1.xlsx"), sheet = 1)
```

```{r excel8, echo = F, warning=F, message=F}
excelcomp %>%
  as.data.frame() %>%
  head(10) %>%
  flextable() %>%
  flextable::set_table_properties(width = .5, layout = "autofit") %>%
  flextable::theme_zebra() %>%
  flextable::fontsize(size = 12) %>%
  flextable::fontsize(size = 12, part = "header") %>%
  flextable::align_text_col(align = "center") %>%
  flextable::set_caption(caption = "First 10 rows of the excelcomp data.")  %>%
  flextable::border_outer()
```

# Inspecting tables{-}

The most common functions that are used to inspect tabular data are the `head()` and the `str()` functions. The `head()` function shows the first 6 lines (by default) but we can also specify the number of rows. The `str()` function provides a summary of the structure of the data.


Inspecting the first 6 rows of a table.

```{r tab_01b, warning=F, message=F}
head(mytable)
```

Inspecting the first 10 rows of a table.

```{r tab_02b, warning=F, message=F}
head(mytable, 10)
```

Checking the structure of tabulated data.

```{r tab_03b, warning=F, message=F}
str(mytable)
```

The following section shows how to access and manipulate tables. 

# Processing tabular data{-}

The tidyverse is a specific way of writing R code that builds on a family of libraries designed for efficient data science work flows which were developed initially by Hadley Wickham. This new way of writing R code builds on a shared and underlying design philosophy and grammar. Due to its popularity and ease of use, the tidyverse way to write R code is becoming increasingly popular and we will use it in the following to handle and manipulate tabulated data. 

If you have already loaded data into R and now want to process the data, you typically have to modify the data in some form or another to get the information or format you need. The tidyverse offers very user-friendly, intuitive, and handy functions for processing the data to match the needs of your analysis. To have access to the tidyverse functions for data processing, we load the `tidyverse` package and load and inspect another set of data using the `read.delim` function. The new data is stored as a txt file and has 100 observations (rows) and 3 variables (*status*, *attraction*, and *money*). The data set represents how much money people have spend in someone they were interested in or not (attraction: *Interested* versus *NotInterested*) and their own relationship status (status: *Single* versus *Relationship*).

```{r dp1, eval = T, message=FALSE, warning=FALSE}
# load new data
newdata <- base::readRDS(url("https://slcladal.github.io/data/mld.rda", "rb"))
```

```{r tidy11, echo = F, warning=F, message=F}
newdata %>%
  as.data.frame() %>%
  head(10) %>%
  flextable() %>%
  flextable::set_table_properties(width = .5, layout = "autofit") %>%
  flextable::theme_zebra() %>%
  flextable::fontsize(size = 12) %>%
  flextable::fontsize(size = 12, part = "header") %>%
  flextable::align_text_col(align = "center") %>%
  flextable::set_caption(caption = "First 10 rows of the excelcomp data.")  %>%
  flextable::border_outer()
```

The table represents 3 variables (status, attraction, and money) and each row contains information on the relationship status of 100 people and how much money these people would spend on a gift to someone of the opposite sex who they are or are not interested in. We will now check out different ways and functions to process this data. 

## Piping{-}

Piping, done with the sequence `%>%`, is a very easy, intuitive, quick, and handy way to process data. Essentially piping means that we take an element that is to the left of the piping symbol and then do something to it; that way, the piping symbol can be translated as *and then*. 

We could, for example, load data and then capitalize the column names and then group the data by status and attraction and then get the mean of money spend on deleting all observations except for the first one. A more formal way to write this would be:

> load %>% capitalize %>% group %>% summarize.

In R this command would look like this:

```{r dp2, eval = T, message=FALSE, warning=FALSE}
# example of a data processing pipeline
pipeddata <- base::readRDS(url("https://slcladal.github.io/data/mld.rda", "rb")) %>%
  dplyr::rename(Status = status, Attraction = attraction, Money = money) %>%
  dplyr::group_by(Status, Attraction) %>%
  dplyr::summarise(Mean = mean(Money))
# inspect summarized data
pipeddata
```

The pipe has worked and we get the resulting summary which shows the mean of the money spend based on  Attraction and Status.

## Selecting and filtering{-}

Among the most frequent procedures in data processing is selecting certain columns or extracting rows based on variable levels. In the tidyverse, this is done by using the `select` and `filter` functions. While `select` allows to extract columns, `filter` is used to extract rows, e.g. to get only observations that have a certain feature. Have a look at the example below.

```{r dp3, eval = T, message=FALSE, warning=FALSE}
# select and filter
reduceddata <- newdata %>%
  # select the columns attraction and money
  dplyr::select(attraction, money) %>%
  # extract rows which represent cases where the person was interested in someone
  dplyr::filter(attraction == "Interested")
# inspect new table
nrow(reduceddata); table(reduceddata$attraction)
```

We have now reduced the data by excluding status (we have only selected attraction and money) and we have removed those 50 data rows of people who were not interested. The `select` function (like most other tidyverse functions) can also be used together with a minus sign which causes a column to be removed, thus `dplyr::select(-money)` would remove the money column (see below).

```{r dp4, eval = T, message=FALSE, warning=FALSE}
# select and filter
datawithoutmoney <- newdata %>%
  # remove money
  dplyr::select(-money) 
# inspect data
head(datawithoutmoney)
```

Selecting and filtering are extremely powerful functions that can also be combined with other functions. But before we discuss more complex issues, we will have a look at how we can change columns.

## Changing data and adding columns{-}

Changing and adding data is done with the `mutate` function. The `mutate` functions requires that we specify a column name - if we use the same name as the column we are changing, then we change the column but if we specify another column name, then a new column is created. 

We will now create a new column (Spendalot) in which we encode if the person has spend a lot of money (100 AUD or more) on the present or not (less than 100 AUD).

```{r dp5, eval = T, message=FALSE, warning=FALSE}
# creating a new column
newdata <- newdata %>%
  dplyr::mutate(Spendalot = ifelse(money >= 100, "Alot", "Alittle")) 
# inspect data
head(newdata)
```

The table now has a new column (Spendalot) because we have specified a column name that did not exist yet - had we written `dplyr::mutate(money = ifelse(money >= 100, "Alot", "Alittle"))` then we would have changed the money column and replaced the money values with the labels *Alot* and *Alittle*.

## Renaming columns{-}

Oftentimes, column names are not really meaningful or incoherent which makes it easier to wrap your head around what the values in a column refer to. The easiest way around this is rename columns which is, fortunately very simple in the tidyverse. While the column names of our example table are meaningful, I want to capitalize the first letter of each column name. This can be done as follows.

```{r dp6, eval = T, message=FALSE, warning=FALSE}
# renaming columns
newdata <- newdata  %>%
  dplyr::rename(Status = status, Attraction = attraction, Money = money)
# inspect data
head(newdata)
```


The renaming was successful as all column names now begin with a capital letter.

## Grouping and summarising{-}

In contrast to mutate, which retains the number of rows, summarizing creates new columns but collapses rows and only provides the summary value (or values if more than one summary is specified). Also, columns that are not  grouping variables are removed. 

Summarizing is particularly useful when we want to get summaries of groups. We will modify the example from above and extract the mean and the standard deviation of the money spend on presents by relationship status and whether the giver was attracted to the giv-ee. 

```{r dp7, eval = T, message=FALSE, warning=FALSE}
#grouping and summarizing data 
datasummary <- newdata %>%
  dplyr::group_by(Status, Attraction) %>%
  dplyr::summarise(Mean = round(mean(Money), 2), SD = round(sd(Money), 1))
# inspect summarized data
datasummary
```

## Gathering and Spreading{-}

One very common problem is that data - or at least parts of it - have to be transformed from long to wide format or vice versa. In the tidyverse, this is done using the `gather` and `spread` function. We will convert the summary table shown above into a wide format (we also remove the SD column as it is no longer needed) 

```{r dp8, eval = T, message=FALSE, warning=FALSE}
# converting data to wide format 
widedata <- datasummary %>%
  # remove SD column
  dplyr::select(-SD) %>% 
  # convert into wide format
  tidyr::spread(Attraction, Mean)
# inspect wide data
widedata
```

We can re-convert the wide into a long format using the `gather` function. 

```{r dp9, eval = T, message=FALSE, warning=FALSE}
# converting data to long format 
longdata <- widedata %>%
  # convert into long format
  tidyr::gather(Attraction, Money, Interested:NotInterested)
# inspect wide data
longdata
```

There are many more useful functions for processing, handling, and summarizing tables but this should suffice to get you started.



# Citation & Session Info {-}

Schweinberger, Martin. 2022. *Handling tables in R*. Brisbane: The University of Queensland. url: https://ladal.edu.au/table.html (Version 2022.11.17).


```
@manual{schweinberger2022tabr,
  author = {Schweinberger, Martin},
  title = {Handling tables in R},
  note = {https://ladal.edu.au/table.html},
  year = {2022},
  organization = "The University of Queensland, School of Languages and Cultures},
  address = {Brisbane},
  edition = {2022.11.17}
}
```

```{r fin}
sessionInfo()
```



# References{-}

***

[Back to top](#introduction)

[Back to LADAL](https://ladal.edu.au)

***

