---
title: "II\\. Working with data in R (presentation)"
author: "Data Science Lab, University of Copenhagen"
date: today
format: 
  html:
    toc: true
    toc-depth: 3
    toc-location: left    
    embed-resources: true
    standalone: true
execute:
  warnings: true
  messages: true
  echo: true
---


## Tidyverse package

The tidyverse is a collection of R packages which, among other things, facilitate data handling and data transformation in R. See <https://www.tidyverse.org/> for details. 

We must install and load the R package **tidyverse** before we have access to the functions. 

* Install package: One option is to go via the _Tools_ menu: *Tools* &#8594; *Install packages* &#8594; write `tidyverse` in the field called _Packages_. This only has to be done once. 

* Load package: Use the `library` command below (preferred), or go to the _Packages_ menu in the bottom right window, find **tidyverse** in the list, and click it. This has to be done in every R-session where you use the package.

```{r}
library(tidyverse)
```

We can see that loading the **tidyverse** package here results in some technical messages from R. You do not have to worry about this (even if some of the text is coloured red).

---

## Import data, inspect data

Data from Excel files can be imported via the _Import Dataset_ facility. You may get the message that the package **readxl** should be installed. If so, then install it as explained for **tidyverse** above. 

* Find _Import Data_ in the upper right window in RStudio, and choose _From Excel_ in the dropdown menu.

* A new window opens. Browse for the relevant Excel file; then a preview of the dataset is shown. Check that it looks OK, and click _Import_. 

* Three things happened: Three lines of code was generated (and executed) in the Console, a new dataset now appears in the Environment window, and the dataset is shown in the top left window. Check again that it looks OK. 

* Copy the first two lines of code into your R script (or into an R chunk in your Markdown document), but delete line starting with `View` and write instead the name of the dataset, here **downloads**. Then the first 10 lines of the data set are printed. 

```{r}
library(readxl)
downloads <- read_excel("downloads.xlsx")
downloads
```

R has stored the data in a so-called _tibble_, a type of data frame. 
Rows are referred to as *observations* or *data lines*, columns as *variables*. The data rows appear in the order as in the Excel file.

Notice the line with data types below the variable names. _<chr>_ to character variables, _<dttm>_ to data-and-time variables, _<dbl>_ (double) refer to numerical variables. It is often useful to convert variables of _<chr>_ type to factor variables, as illustrated later in the document. 

It is often useful to get a summary of each of the variables in the dataset:
```{r}
summary(downloads)
```
At this point, the output is mainly useful for the numerical variables (and date variables). We will later see how categorical variables can be converted to so-called factors, and that the output is also useful for such variables. 


**Digression:** If data are saved in a csv file (comma separated values), possibly generated via an Excel sheet, then data can be read with the read.csv function. For example, if the data file is called `mydata.csv` and values are separated with commas, then the command 
```{r, echo=TRUE, eval=FALSE}
mydata <- read.csv("mydata.csv", sep=",")
```
creates a data frame in R with the data. The data frame is _not_ a tibble and some of the commands below would not work for such a data frame. Alternatively, you can also import csv files via the *From Text* item in the *Import Dataset* menu.

---

## About the data

The dataset is from Boston University and is about www data transfers from November 1994 to May 1995.

* It has 147,035 data lines and 6 variables

* We will use 3 of the variables: _machineName_ is the name of the server from which the file was downloaded, _size_ is the download size in bytes, and _time_ is the download time in seconds.

---

## Extracting variables, simple summary statistics

Variables can be extracted with the $-syntax, and we can use squared brackets to show only the first 40, say, values. 

```{r}
time_vector <- downloads$time
time_vector[1:40]
```

Summary statistics like mean, standard deviation, median are easily computed for a vector. 

Examples of R functions for computing summary statistics: `length`, `mean`, `median`, `sd`, `var`, `sum`, `quantile`, `min`, `max`, `IQR`.

```{r}
length(time_vector)
mean(time_vector)
sd(time_vector)
median(time_vector)
min(time_vector)
```

Notice that more than half the observations have time equal to zero (median is zero).

With the `summary` function, many of the above summary statistics (but not the standard deviation) are computed in one go: 

```{r}
summary(time_vector)
```

---

## Filter data (selecting rows): `filter`

The `filter` function is used to make sub-datasets where only certain datalines (rows) are maintained. It is described with _logical expressions_ which datalines should be kept in the dataset.

Say that we only want observations with download time larger than 1000 seconds; there happens to be eight such observations: 

```{r}
# Note: Here we use the filter() function from the tidyverse-package.
#       If the tidyverse-package was not loaded, then we would use
#       the filter() function from the stats-package. And actually
#       get an error message.
filter(downloads, time > 1000)
```

Or say that only want observations with strictly positive download size:

```{r}
downloads2 <- filter(downloads, size > 0)
downloads2
```

Notice that this result is assigned to **downloads2**. It has 36,708 data lines. The original data called **downloads** still exists with 147,035 data lines.

Filtering requires *logical predicates*. These are expressions in terms of columns, which evaluate to either `TRUE` or `FALSE` for each row. Logical expressions can be combined with logical operations.

* Comparisons: ` == `, ` != `, ` < `, ` > `, ` <= `, ` >= `, ` %in% `, ` is.na `

* Logical operations: `!` (not), `|` (or), `&` (and). A comma can be used instead of ´&´

Here comes two sub-datasets:


```{r}
# Rows from kermit, and with size greater than 200000 bytes are kept.
filter(downloads2, machineName == "kermit", size > 200000)

# Rows NOT from kermit, and with size greater than 200000 bytes are kept.
filter(downloads2, machineName != "kermit", size > 200000)
```

---

## Select variables: `select`

Sometimes, datasets has many variables of which only some are relevant for the analysis. Variables can be selected or skipped with the `select` function.

```{r}
# Without the date variable
select(downloads2, -date)

# Only include the three mentioned variable names
downloads3 <- select(downloads2, machineName, size, time)
downloads3
```

Notice that we have made a new dataframe, **downloads3** with only three variables. 


---

## Transformations of data: `mutate`

Tranformations of existing variables in the data set can be computed and included in the data set with the `mutate` function. 

We first compute two new variables, download speed (**speed**) and the logarithm of the download size (**logSize**):

```{r}
# Two new variables
downloads3 <- mutate(downloads3, speed = size / time, logSize = log10(size))
downloads3
```

We then make a new categorical variable, **speedCat**, which is "Slow" is speed < 150 and "Fast" otherwise:

```{r}
# New variable with if-else construction
downloads3 <- mutate(downloads3, speedCat = ifelse(speed < 150, "Slow", "Fast"))
downloads3
```

Categorical variables are coded as "character variables" when data are imported, but it is often preferable to code them as so-called factors instead. We can use the function `factor` in combination with `mutate` to obtain this. Here, we overwrite the old version of **machineName** with the new factor version. Notice how the output from `summary` now shown the number of observations for each machine. 

```{r}
# Change machineName to factor
downloads3 <- mutate(downloads3, machineName=factor(machineName))
summary(downloads3)
```

---

## Counting, tabulation of categorical variables: `count`

The `count` function is useful for counting datalines, posssibly according to certain criteria or for the different levels of categorical values.

```{r}
# Total number of observations in the current dataset
count(downloads3)

# Number of observations from each machine
count(downloads3, machineName)

# Number of observations which have/have not size larger than 5000
count(downloads3, size>5000)

# Number of observations for each combination of machine name and the *slow* variable.
count(downloads3, machineName, speedCat)
```

---

## Sort data: `arrange`

The `arrange` function can be used to sort the data according to one or more columns.

Let us sort the data according to download time (ascending order). The first lines of the sorted data set is printed on-screen, but the dataset **downloads3** has *not* been changed.

```{r}
arrange(downloads3, time)
```

Two different examples:

```{r}
# According to download size in descending order
arrange(downloads3, desc(time))

# After machine name and then according to download size in descending order
arrange(downloads3, machineName, desc(time))
```

---

## Group data: `group_by`

We can group the dataset by one or more categorical variables with `group_by`. The dataset is not changed as such, but - as we will see - grouping can be useful for computation of summary statistics and graphics. 

Here we group after machine name (first)  and after machine name _and_ the categorical speed variable (second). The only way we can see it at this point is in the second line in the output (`# Groups:`):

```{r}
# Group according to machine
group_by(downloads3, machineName)

# Group according to machine and slow
group_by(downloads3, machineName, speedCat)
```

---

## Summary statistics, revisited: `summarize`

Recall how we could compute summary statistics for a single variable in a dataset, e.g.

```{r}
mean(downloads3$size)
max(downloads3$size)
```

With `summarize` we can compute summary statistics for a variable for each level of a grouping variable or for each combination of several grouping variables. 

First, a bunch of summaries for the size variable for each machine name, where we give explicit names for the new variables:

```{r}
downloads.grp1 <- group_by(downloads3, machineName)
summarize(downloads.grp1, 
          avg = mean(size),
          med = median(size),
          stdev = sd(size),
          total = sum(size),
          n = n())
```

Second, the same thing but for each combination of machine name and the categorical speed variable:

```{r}
downloads.grp2 <- group_by(downloads3, machineName, speedCat)
summarize(downloads.grp2, 
          avg = mean(size),
          med = median(size),
          stdev = sd(size),
          total = sum(size),
          n = n())
```

Third, mean and standard deviation for several variables: 

```{r}
summarize(downloads.grp2, across(c("time", "size"), list(ave=mean,stdev=sd)))
```

The datasets with summaries can be saved as datasets themselves, for example to be used as the basis for certain graphs. 

---

## Merge datasets with different columns: `join`

Consider the situation where data from the same "individuals" are spread in different datasets, and we want to combine the data into one dataset. This is possible with the "join" functions which are available with the **tidyverse** package. There are several variants; here we just show how to use the one called _full_join_ in a tiny example. Say we want to merge the dataset consisting of the first five datalines from **downloads3** with another dataset which has six observations. They are linked together via a variable called **id** and we want a dataset which merges the two datasets, keeping observations with the same id together.  

```{r}
# Subset of the downloads3 data
downloads4 <- downloads3[1:5,]
# Make an id variable
downloads4$id <- 1:5
downloads4

# The "extra" dataset. It has two variables: id and x
extraData <- tibble(id=6:1, x = c(16,15,14,13,12,11))
extraData

# Merging
full_join(downloads4, extraData)
```

The merged dataset has six datalines, but the last line has NA's for all variables except **ID** and **x** because no observation with id=6 exists in **downloads4**. One can use different join functions to leave out this dataline.

## Stack datasets with the same variables: `bind_rows`

Consider another situation where we have two datasets with the same variables, and want to stack them together. The function `bind_rows` is useful for that. Here, for illustration, we first make two small subsets of the data, and then combine them.

```{r}
# Make small datasets and combine them 
part1 <- downloads3[1:3,]
part2 <- downloads3[10001:10003,]
part1
part2
bind_rows(part1, part2)
```

---

## The pipe operator: `%>%`

Two or more function calls can be evaluated sequentially using the so-called pipe operator, `%>%`. Nesting of function calls becomes more readable, and intermediate assignments are avoided.

Let us try it to do a bunch of things in one go, starting with the original dataset. Notice also the extended use of summarize.

```{r}
downloads %>% 
  filter(size>0) %>% # Subset of data
  group_by(machineName) %>% # Grouping 
  summarize(across(c("time","size"),list(avg=mean, sd=sd))) %>% # Compute all means and sd's
  arrange(size_avg) # Sort after mean
```

**End of presentation.**
