datatable.Rmd

---
title: "datatable"
author: "Maja Kuzman"
date: "7/1/2020"
output: html_document
---


```{r}
ddf <- data.table::fread("http://hex.bioinfo.hr/~mfabijanic/tidyData.txt", header=T)
ddf
``` 
I will show you some examples on this data, and you will do the exercise on data available in R (iris).  
```{r}
iris
```

---

# Loading the library  

If you don't have the package, install it with:  
(only once)  
```{r, eval=FALSE}
install.packages(data.table)
```

Once it is installed, you need to load it to R:  
(once per session)  

```{r}
library(data.table)
```

If you already have a data frame, convert it to data.table by:  

```{r}
ddf_dt <- as.data.table(ddf)
```

# Packages in R: data.table   


####All you need to know:  

#### DT[i,j,by]  
     
##### i: select those rows  
##### j: do this to them  
##### by: do it per groups  

#data.table: SELECTING ROWS  


Selecting rows:
How many people are taller then 180 ?  

In data.table:  
You select rows similar as you would select elements in vector:  

```{r}
ddf_dt[Height>180]
nrow(ddf_dt[Height>180])
```

#data.table: SELECTING COLUMNS   

Show the Age and Continent of the first 5 people.

In data.table use . instead of c,  
. is short for list()  
```{r}
ddf_dt[1:5,.(Age, Continent)]
```


If you want to use column names:
```{r}
cnames <- c("Age","Continent")
ddf_dt[1:5,..cnames]
```


#data.table: SELECTING COLUMNS

note on columns selections: this will also work:
```{r}
ddf_dt[1:3,Age:Continent]
```

```{r}
ddf_dt[1:3,-(Age:Continent)]
```

```{r}
ddf_dt
ddf_dt[1:3,!(Age:Gender)]
```

#data.table: exercise iris 1  

```{r, eval=FALSE}
ddf_dt <- as.data.table(ddt)
ddf_dt[1:5,.(Age,Continent)]
cnames <- c("Age","Continent")
ddf_dt[1:5,..cnames]
```

Mini exercise:  
```{r}
iris
```

- convert iris to data.table - call it iris_dt  
- Select all rows in iris_dt with Sepal.Length<6.7  
- Select as before, but show only columns Sepal.Length and Species  


#data.table: OPERATION ON COLUMNS

Calculations of mean:  
```{r}
ddf_dt[,mean(Height)]
```

By groups:
```{r}
ddf_dt[,mean(Height), by=Gender]
```

# data.table: OPERATION ON COLUMNS

Calculations of mean, sd, max , min for Height, and number of people per Gender, get a data frame order it by mean:  

```{r}
ddf_dt[ , 
        .(mean=mean(Height),
          sd  =sd(Height),
          min_x= min(Height),
          max_x= max(Height),
          N= .N),
        by=Gender][order(mean)]
```

# data.table: .N, by 

The .N gives number of observations:  
```{r}
ddf_dt[,.N]
ddf_dt[,.N, Gender]
```

You can group by multiple variables:  

```{r}
ddf_dt[,.N, by=.(Gender,Height>180)]
```
### Exercise: 

How many man and women are there from different continents?

# data.table: exercise iris 2  

```{r, eval=FALSE}
ddf_dt[,
       .(N=.N), 
       by=.(Gender,Height>180)]
```

Exercise on iris_dt:  
- Select all rows where Sepal.Length < 6.7 and Species=="virginica"   
- For those - use chaining - [][] to calculate mean Petal.Width for all flowers  
- How many of those flowers have Sepal.Width>3 and how many less then 3?  


# data.table:  add a new column 

Use ':=' to add a new column
```{r}
ddf_dt[,N:=.N]
ddf_dt[1:3]
```

If you want to add multiple columns, use ':='as a function:  
```{r}

ddf_dt[ ,
       ':='(N_grp=.N, mean=mean(Height)),
       by=.(Gender, Continent)]
ddf_dt
```

---

# data.table: Exercise add a new column 

```{r, eval=FALSE}
ddf_dt[ ,
       ':='(N_grp=.N, mean=mean(Height)),
       by=.(Gender,Continent)]
```

- Add columns to iris_dt that represent mean and sd of Sepal.Width grouped by species.  
- use function uniqueN to check how many unique mean Sepal Widths there are. 

# data.table:  .I, .GRP  

The .I holds row numbers:
```{r}
ddf_dt[,.(row_id=.I,Gender,Continent)]
```

--

The .GRP holds unique group number:  

```{r}
ddf_dt[,.GRP,.(Gender, Continent)]
```

---
# data.table Exercise :=, .N, .I, .GRP  


```{r, eval=FALSE}
ddf_dt[ ,
       ':='(N_grp=.N, mean=mean(Height)),
       by=.(Gender, Continent)]
ddf_dt[,.(row_id=.I,Gender,Continent)]
ddf_dt[,.GRP,.(Gender,Continent)]
```

- Add column to iris_dt that represent number of observations of all rows for which Petal.Length is smaller than 6.5 in iris_dt grouped by species.  
- One great benefit of data.table is the ability to sub-assign by reference:  Try it: select all rows that have species=="virginica" and rename those Species entries using := to new_virginica  


#data.table MORE ADVANCED USAGE: keys  

     You can "set a key" of data.table by using the setkey() function. This will result in data table that is ordered by the key/s and will allow for (much) faster manipulation (for example merge function!)!   
```{r}
setkey(ddf_dt, Gender, Continent, Height)
ddf_dt
```

# data.table MORE ADVANCED USAGE: .SD, .SDcols  

Select all columns with .SD. Select only a subset of all columns by .SDcols:  
```{r}
ddf_dt[,.SD, .SDcols=c(2,4,5)]
```

this is especially useful when you want to do the same operation on multiple columns: for example, calculate mean of x and y: 
```{r}
ddf_dt[,lapply(.SD,mean), by=Gender,.SDcols=c(2,4,5)]
```

#data.table MORE ADVANCED USAGE: .SD, .SDcols  

..Or for example select first and last row for each group:  
```{r}
ddf_dt[, .SD[c(1, .N)], by=Gender]
```
It is easier if you read it as: SelectedData  

#data.table exercise MORE ADVANCED USAGE

```{r, eval=FALSE}
ddf_dt[,lapply(.SD,mean), by=Gender,.SDcols=c(2,4,5)]
ddf_dt[, .SD[c(1, .N)], by=Gender]
```


Do the following in a single command:  
- order the results by Petal.Width and select first three (smallest) observations .  
- Calculate mean of first four columns for iris_dt for those observations      





#-----------------------------------------------------------------#
#                         MORE ADVANCED USAGE
#-----------------------------------------------------------------#

# data.table: dcast & melt  
```{r}
molten <- melt(ddf_dt,id.vars = c("Gender","Continent"))
dcast(ddf_dt,Gender+Age~Continent)
dcast(ddf_dt,Gender+Age~Continent, fun.aggregate = mean)
```

#data.table MORE ADVANCED USAGE {}

Suppressing Intermediate Output with {}:

If you want to do multiple things, but dont need to save all steps in separate columns, no problem! Check this out:  

Lets calculate BMI!
BMI = kg/m^2
heightinmeters = Height/100
heightinmeterssquared = heightinmeters^2
Weight is already in kg :)
BMI= heightinmeters/Weight

#data.table MORE ADVANCED USAGE {} solved  

```{r}
ddf_dt[,BMI:= {
          heightinmeters = Height/100
          heightinmeterssquared = heightinmeters^2
          Weight/heightinmeterssquared
     }]
```
### Exercise:  

How many obese imaginary people are there? (BMI>25)


#data.table exercise MORE ADVANCED USAGE: merging 


Lets define dummy data tables:  
```{r}
dt1 <- data.table(x = c("a", "b", "c", "d"), y = c(11.9, 21.4, 5.7, 18))
dt2 <- data.table(x= c("a","b","k"),y = c(10, 15, 20), z = c("one", "two", "three"))
dt1
dt2
```

---
#data.table exercise MORE ADVANCED USAGE: merging 

Merge those two data tables by variable x


```{r}
merge(dt1,dt2, by="x")
```

```{r, eval=FALSE}
merge(dt1,dt2, by="x", all.x = T)
merge(dt1,dt2, by="x", all.y = T)
```

---
#data.table exercise MORE ADVANCED USAGE: merging  

```{r, eval=FALSE}
merge(dt1,dt2, by="x", all.y = T)
```

```{r}
dt1[dt2, on = .(x)]
```

---

# data.table exercise MORE ADVANCED USAGE: roll

```{r}
dt1
dt2
```
---
#keep rolling! - FORWARD JOIN:   
Merge two data frames on CLOSEST SMALLER NUMERICAL VALUE in dt2 - keep all observations from dt1!:  

```{r}
dt2[dt1, on = .(y), roll = T]
```
---
#keep rolling! - BACKWARD JOIN:   
Merge two data frames on CLOSEST LARGER NUMERICAL VALUE in dt2 NOT LARGER THAN 4 - keep all observations from dt1!:  
```{r}
dt2[dt1, on = .(y), roll = -4]
```
roll can also take "nearest".

---

# data.table exercise MORE ADVANCED USAGE: foverlaps

```{r}
dt3 <- data.table(min_y = c(0, 10, 15, 20), max_y = c(10, 15, 20, 30))
setkey(dt3, min_y, max_y)
dt1[, `:=` (dt1_y_end = c(13, 25, 10, 22), dt1_y=y)]
setkey(dt1, dt1_y, dt1_y_end)
foverlaps(dt1, dt3, type = "any")
```

Each window does not have to be equal
```{r}
dt1
dt3
```