6  Data frames

6.1 Defining a data.frame

6.1.1 Defining a data.frame from vectors

In R, the principal object is the data. Hence the data.frame object, which is basically a table of vectors. A data.frame is a list presented under the form of a table – i.e. a spreadsheet. On a day-to-day basis, you will either define data.frame from existing vectors or other data.frame, or define a data.frame from a file (text, Excel…). In this example, we use test.dat and test.xlsx.

To define a data.frame from known vectors, we just have to do:

x  <- seq(-pi, pi, length = 6) 
y  <- sin(x)
df <- data.frame(x, y) # df is a data.frame (a table)
df
#>            x             y
#> 1 -3.1415927 -1.224647e-16
#> 2 -1.8849556 -9.510565e-01
#> 3 -0.6283185 -5.877853e-01
#> 4  0.6283185  5.877853e-01
#> 5  1.8849556  9.510565e-01
#> 6  3.1415927  1.224647e-16

Then some information about our table are readily accessible, like:

  • The dimension, the number of rows and columns:
dim(df); nrow(df); ncol(df)
#> [1] 6 2
#> [1] 6
#> [1] 2
  • Print the first and last 3 values
head(df, 3); tail(df, 3)
#>            x             y
#> 1 -3.1415927 -1.224647e-16
#> 2 -1.8849556 -9.510565e-01
#> 3 -0.6283185 -5.877853e-01
#>           x            y
#> 4 0.6283185 5.877853e-01
#> 5 1.8849556 9.510565e-01
#> 6 3.1415927 1.224647e-16
  • Print some information on df 
str(df)
#> 'data.frame':    6 obs. of  2 variables:
#>  $ x: num  -3.142 -1.885 -0.628 0.628 1.885 ...
#>  $ y: num  -1.22e-16 -9.51e-01 -5.88e-01 5.88e-01 9.51e-01 ...
  • Print some statistics on df 
#>        x                y          
#>  Min.   :-3.142   Min.   :-0.9511  
#>  1st Qu.:-1.571   1st Qu.:-0.4408  
#>  Median : 0.000   Median : 0.0000  
#>  Mean   : 0.000   Mean   : 0.0000  
#>  3rd Qu.: 1.571   3rd Qu.: 0.4408  
#>  Max.   : 3.142   Max.   : 0.9511

If not defined when creating the data.frame, the column names will be by default the vector names. To specify your own column names, do it when creating the data.frame:

df <- data.frame(xxx = x, yyy = y)
head(df, 2)
#>         xxx           yyy
#> 1 -3.141593 -1.224647e-16
#> 2 -1.884956 -9.510565e-01

Or, once it’s created, do it using names()

names(df)
#> [1] "xxx" "yyy"
names(df) <- c("X", "Y")
head(df, 2)
#>           X             Y
#> 1 -3.141593 -1.224647e-16
#> 2 -1.884956 -9.510565e-01

6.1.2 Defining a data.frame from a file

6.1.2.1 A text file

Let’s say we have test.dat that looks like this:

# Bash code:
head Data/test.dat
#> x   y
#> 1   2
#> 2   3
  • Then, to read this file into a data.frame, we will use read.table(). If you don’t specify that the file contains a header, read.table() will default to attributing column names that will be V1, V2, V3, etc:
read.table("Data/test.dat")
#>   V1 V2
#> 1  x  y
#> 2  1  2
#> 3  2  3
  • If your file contains column names, you can use the first line as column names, like so:
read.table("Data/test.dat", header=TRUE)
#>   x y
#> 1 1 2
#> 2 2 3
  • If you want to skip some lines before starting the reading, use skip:
read.table("Data/test.dat", skip=1)
#>   V1 V2
#> 1  1  2
#> 2  2  3
  • You can specify your own column names using col.names:
read.table("Data/test.dat", skip=1, col.names = c("A","B"))
#>   A B
#> 1 1 2
#> 2 2 3

6.1.2.2 An Excel file

Now, to read an Excel file, use the readxl library:

library(readxl) # load readxl from tidyverse to read Excel files
read_excel("Data/test.xlsx", sheet=1)
#> # A tibble: 10 × 2
#>        x      y
#>    <dbl>  <dbl>
#>  1     1  5.21 
#>  2     2  6.55 
#>  3     3  3.71 
#>  4     4  0.216
#>  5     5  0.205
#>  6     6  4.60 
#>  7     7 10.3  
#>  8     8 12.9  
#>  9     9 11.1  
#> 10    10  7.28
read_excel("Data/test.xlsx", sheet=2)
#> # A tibble: 4 × 2
#>   hello  world   
#>   <chr>  <chr>   
#> 1 ac     th      
#> 2 asc    thh     
#> 3 ascsa  dthdh   
#> 4 ascacs dthtdhdh

6.2 Accessing values

  • Like with vectors, accessing values is done using the [] notation, except that here there are two indexes: df[row, column]:
df[3,1]
#> [1] -0.6283185
  • In general however, what you want is to access a given column, by its index:
df[,1]
#> [1] -3.1415927 -1.8849556 -0.6283185  0.6283185  1.8849556  3.1415927
df[[1]]# this is a vector too
#> [1] -3.1415927 -1.8849556 -0.6283185  0.6283185  1.8849556  3.1415927
  • Or, preferably, by its name using the $ notation:
df$X
#> [1] -3.1415927 -1.8849556 -0.6283185  0.6283185  1.8849556  3.1415927
  • Finally, you may want to apply filters on your table:
df[df$X < 0, ]
#>            X             Y
#> 1 -3.1415927 -1.224647e-16
#> 2 -1.8849556 -9.510565e-01
#> 3 -0.6283185 -5.877853e-01
  • Using the function subset(), the conditions are applied on column names (no need for df$col_name here, while you need it in the above expression):
subset(df, X>1)
#>          X            Y
#> 5 1.884956 9.510565e-01
#> 6 3.141593 1.224647e-16
subset(df, X>1, select = c(X))
#>          X
#> 5 1.884956
#> 6 3.141593

6.3 Adding columns or rows

6.3.1 Adding columns

  • To add a column, just attribute a value to an column that do not exist yet, it will be created:
# Adding columns
df   <- data.frame(x,y)
df$z <- df$x^2
df
#>            x             y         z
#> 1 -3.1415927 -1.224647e-16 9.8696044
#> 2 -1.8849556 -9.510565e-01 3.5530576
#> 3 -0.6283185 -5.877853e-01 0.3947842
#> 4  0.6283185  5.877853e-01 0.3947842
#> 5  1.8849556  9.510565e-01 3.5530576
#> 6  3.1415927  1.224647e-16 9.8696044
  • You can also create a data.frame of a data.frame:
data.frame(df, z=df$x^2, u=cos(df$x))
#>            x             y         z       z.1         u
#> 1 -3.1415927 -1.224647e-16 9.8696044 9.8696044 -1.000000
#> 2 -1.8849556 -9.510565e-01 3.5530576 3.5530576 -0.309017
#> 3 -0.6283185 -5.877853e-01 0.3947842 0.3947842  0.809017
#> 4  0.6283185  5.877853e-01 0.3947842 0.3947842  0.809017
#> 5  1.8849556  9.510565e-01 3.5530576 3.5530576 -0.309017
#> 6  3.1415927  1.224647e-16 9.8696044 9.8696044 -1.000000
  • Finally, you can use the cbind() function to bind two data.frame column-wise:
df2 <- data.frame(a = 1:length(x), b = 1:length(x))
cbind(df, df2)
#>            x             y         z a b
#> 1 -3.1415927 -1.224647e-16 9.8696044 1 1
#> 2 -1.8849556 -9.510565e-01 3.5530576 2 2
#> 3 -0.6283185 -5.877853e-01 0.3947842 3 3
#> 4  0.6283185  5.877853e-01 0.3947842 4 4
#> 5  1.8849556  9.510565e-01 3.5530576 5 5
#> 6  3.1415927  1.224647e-16 9.8696044 6 6

6.3.2 Adding rows

For this, use the rbind() function.

Attention

The two data.frame must have the same number of columns and the same column names.

rbind(df, df)
#>             x             y         z
#> 1  -3.1415927 -1.224647e-16 9.8696044
#> 2  -1.8849556 -9.510565e-01 3.5530576
#> 3  -0.6283185 -5.877853e-01 0.3947842
#> 4   0.6283185  5.877853e-01 0.3947842
#> 5   1.8849556  9.510565e-01 3.5530576
#> 6   3.1415927  1.224647e-16 9.8696044
#> 7  -3.1415927 -1.224647e-16 9.8696044
#> 8  -1.8849556 -9.510565e-01 3.5530576
#> 9  -0.6283185 -5.877853e-01 0.3947842
#> 10  0.6283185  5.877853e-01 0.3947842
#> 11  1.8849556  9.510565e-01 3.5530576
#> 12  3.1415927  1.224647e-16 9.8696044

6.3.3 Deleting rows/columns

This works like with vectors:

df[-1,]
#>            x             y         z
#> 2 -1.8849556 -9.510565e-01 3.5530576
#> 3 -0.6283185 -5.877853e-01 0.3947842
#> 4  0.6283185  5.877853e-01 0.3947842
#> 5  1.8849556  9.510565e-01 3.5530576
#> 6  3.1415927  1.224647e-16 9.8696044
df[,-1]
#>               y         z
#> 1 -1.224647e-16 9.8696044
#> 2 -9.510565e-01 3.5530576
#> 3 -5.877853e-01 0.3947842
#> 4  5.877853e-01 0.3947842
#> 5  9.510565e-01 3.5530576
#> 6  1.224647e-16 9.8696044

6.4 Tidy up!

6.4.1 What is tidy data?

A good practice in R is to tidy your data. R follows a set of conventions that makes one layout of tabular data much easier to work with than others. Your data will be easier to work with in R if it follows three rules:

  • Each variable in the data set is placed in its own column
  • Each observation is placed in its own row
  • Each value is placed in its own cell

Illustration of tidy data.

Data that satisfies these rules is known as tidy data: you see that thanks to this representation, a 2D table can handle an arbitrary number of variables – this avoids using multi-dimensional arrays or multi-tab Excel documents. Note that it does’t matter if a value is repeated in a column.

Here is an example:

df <- read.csv("Data/population.csv")
df # is not tidy
Show output 
#>   year Angers Bordeaux  Brest  Dijon Grenoble LeHavre LeMans  Lille   Lyon
#> 1 1962 115273   278403 136104 135694   156707  187845 132181 239955 535746
#> 2 1968 128557   266662 154023 145357   161616  207150 143246 238554 527800
#> 3 1975 137591   223131 166826 151705   166037  217882 152285 219204 456716
#> 4 1982 136038   208159 156060 140942   156637  199388 147697 196705 413095
#> 5 1990 141404   210336 147956 146703   150758  195854 145502 198691 415487
#> 6 1999 151279   215363 149634 149867   153317  190905 146105 212597 445452
#> 7 2007 151108   235178 142722 151543   156793  179751 144164 225789 472330
#> 8 2012 149017   241287 139676 152071   158346  173142 143599 228652 496343
#>   Marseille Montpellier Nantes   Nice   Paris  Reims Rennes Saint.Etienne
#> 1    778071      118864 240048 292958 2790091 134856 151948        210311
#> 2    889029      161910 260244 322442 2590771 154534 180943        223223
#> 3    908600      191354 256693 344481 2299830 178381 198305        220181
#> 4    874436      197231 240539 337085 2176243 177234 194656        204955
#> 5    800550      207996 244995 342439 2152423 180620 197536        199396
#> 6    798430      225392 270251 342738 2125246 187206 206229        180210
#> 7    852395      253712 283025 348721 2193030 183500 207922        175318
#> 8    852516      268456 291604 343629 2240621 181893 209860        171483
#>   Strasbourg Toulon Toulouse
#> 1     228971 161797   323724
#> 2     249396 174746   370796
#> 3     253384 181801   373796
#> 4     248712 179423   347995
#> 5     252338 167619   358688
#> 6     264115 160639   390350
#> 7     272123 166537   439453
#> 8     274394 164899   453317


library(tidyr)
df <- pivot_longer(df, cols=-year, names_to="city", values_to="pop")
df #is tidy
Show output 
#> # A tibble: 160 × 3
#>     year city         pop
#>    <int> <chr>      <int>
#>  1  1962 Angers    115273
#>  2  1962 Bordeaux  278403
#>  3  1962 Brest     136104
#>  4  1962 Dijon     135694
#>  5  1962 Grenoble  156707
#>  6  1962 LeHavre   187845
#>  7  1962 LeMans    132181
#>  8  1962 Lille     239955
#>  9  1962 Lyon      535746
#> 10  1962 Marseille 778071
#> # ℹ 150 more rows


# is not tidy
pivot_wider(df, names_from="city", values_from="pop")
Show output 
#> # A tibble: 8 × 21
#>    year Angers Bordeaux  Brest  Dijon Grenoble LeHavre LeMans  Lille   Lyon
#>   <int>  <int>    <int>  <int>  <int>    <int>   <int>  <int>  <int>  <int>
#> 1  1962 115273   278403 136104 135694   156707  187845 132181 239955 535746
#> 2  1968 128557   266662 154023 145357   161616  207150 143246 238554 527800
#> 3  1975 137591   223131 166826 151705   166037  217882 152285 219204 456716
#> 4  1982 136038   208159 156060 140942   156637  199388 147697 196705 413095
#> 5  1990 141404   210336 147956 146703   150758  195854 145502 198691 415487
#> 6  1999 151279   215363 149634 149867   153317  190905 146105 212597 445452
#> 7  2007 151108   235178 142722 151543   156793  179751 144164 225789 472330
#> 8  2012 149017   241287 139676 152071   158346  173142 143599 228652 496343
#> # ℹ 11 more variables: Marseille <int>, Montpellier <int>, Nantes <int>,
#> #   Nice <int>, Paris <int>, Reims <int>, Rennes <int>, Saint.Etienne <int>,
#> #   Strasbourg <int>, Toulon <int>, Toulouse <int>


You can find more information on data import and tidyness on the data-import cheatsheet and on the tidyr package.

Understanding long and wide data with an animation. Source: tidyexplain

6.4.2 Tibbles

A tibble is an enhanced version of the data.frame provided by the tibble package (which is part of the tidyverse). The main advantage of tibble is that it has easier initialization and nicer printing than data.frame.

Moreover, the performance are also enhanced for the reading from files with read_csv(), read_tsv(), read_table() and read_delim() that do the same things as their read.xx() counterparts and return a tibble. Otherwise, the handling is basically the same.

More on tibbles here.

  • Note that when initializing tibbles, the construction is iterative. It means that when creating a second column, one can refer to the first one that was created. This does’t work with data.frames.
# won't work unless a `x` vector was created before
data.frame(x=runif(1e3), y=cumsum(x)) 
#> Error in eval(expr, envir, enclos): object 'x' not found
library(tidyverse)
tib <- tibble(x=runif(1e3), y=cumsum(x))
tib
#> # A tibble: 1,000 × 2
#>         x     y
#>     <dbl> <dbl>
#>  1 0.359  0.359
#>  2 0.836  1.19 
#>  3 0.0292 1.22 
#>  4 0.515  1.74 
#>  5 0.162  1.90 
#>  6 0.533  2.43 
#>  7 0.148  2.58 
#>  8 0.883  3.46 
#>  9 0.832  4.30 
#> 10 0.679  4.97 
#> # ℹ 990 more rows
Attention

Tibbles are quite strict about subsetting. [ always returns another tibble. Contrast this with a data frame: sometimes [ returns a data frame and sometimes it just returns a vector:

head(tib[[1]]) # is a vector
#> [1] 0.35911371 0.83552551 0.02919648 0.51505461 0.16189219 0.53265729
head(tib[,1])  # is a tibble
#> # A tibble: 6 × 1
#>        x
#>    <dbl>
#> 1 0.359 
#> 2 0.836 
#> 3 0.0292
#> 4 0.515 
#> 5 0.162 
#> 6 0.533

Unless you want to get a tibble, I recommend always using the $ notation when you want to get a column as a vector to avoid problems.

  • Another interesting feature of tibbles is that their columns can contain vectors, like usual, but also lists of any R objects like other tibbles, nls() objects, etc. This is called “nesting”, and you can nest and un-nest tibbles using these explicit functions:
tib1 <- tibble(x=1:3, y=1:3)
tib2 <- tibble(x=1:5, y=1:5)
tib  <- tibble(number=1:2, data=list(tib1, tib2))
tib
#> # A tibble: 2 × 2
#>   number data            
#>    <int> <list>          
#> 1      1 <tibble [3 × 2]>
#> 2      2 <tibble [5 × 2]>
Figure 6.1: Excel equivalent to a nested tibble. 
tib_unnested <- unnest(tib, data)
tib_unnested
#> # A tibble: 8 × 3
#>   number     x     y
#>    <int> <int> <int>
#> 1      1     1     1
#> 2      1     2     2
#> 3      1     3     3
#> 4      2     1     1
#> 5      2     2     2
#> 6      2     3     3
#> 7      2     4     4
#> 8      2     5     5
tib_unnested_renested <- nest(tib_unnested, data = c(number, y))
tib_unnested_renested
#> # A tibble: 5 × 2
#>       x data            
#>   <int> <list>          
#> 1     1 <tibble [2 × 2]>
#> 2     2 <tibble [2 × 2]>
#> 3     3 <tibble [2 × 2]>
#> 4     4 <tibble [1 × 2]>
#> 5     5 <tibble [1 × 2]>
tib_unnested_renested$data # The `data` column is a list
#> [[1]]
#> # A tibble: 2 × 2
#>   number     y
#>    <int> <int>
#> 1      1     1
#> 2      2     1
#> 
#> [[2]]
#> # A tibble: 2 × 2
#>   number     y
#>    <int> <int>
#> 1      1     2
#> 2      2     2
#> 
#> [[3]]
#> # A tibble: 2 × 2
#>   number     y
#>    <int> <int>
#> 1      1     3
#> 2      2     3
#> 
#> [[4]]
#> # A tibble: 1 × 2
#>   number     y
#>    <int> <int>
#> 1      2     4
#> 
#> [[5]]
#> # A tibble: 1 × 2
#>   number     y
#>    <int> <int>
#> 1      2     5

6.5 Operations in the tidyverse

In the end, base R and the tidyverse package provide many efficient functions to perform most of the tasks you would want to perform recursively, thus allowing avoiding explicit for loops (that are slow).

Here are some examples, and you will find much more here. Take a look at the cheatsheets on tidyr and on dplyr, it’s really helpful.

Let’s work on this tibble:

# Let's create a random tibble
library(tidyverse)
N <- 500
dt <- tibble(x     = rep(runif(N, -1, 1), 3), 
             y     = runif(N*3, -1, 1), 
             signx = ifelse(x>0, "positive", "negative"),
             signy = ifelse(y>0, "positive", "negative")
)
dt
#> # A tibble: 1,500 × 4
#>          x      y signx    signy   
#>      <dbl>  <dbl> <chr>    <chr>   
#>  1 -0.974   0.907 negative positive
#>  2  0.738   0.717 positive positive
#>  3  0.0657  0.825 positive positive
#>  4 -0.446  -0.298 negative negative
#>  5  0.729   0.150 positive positive
#>  6 -0.557   0.913 negative positive
#>  7 -0.368  -0.557 negative negative
#>  8  0.911  -0.959 positive negative
#>  9 -0.0884 -0.779 negative negative
#> 10  0.140  -0.283 positive negative
#> # ℹ 1,490 more rows

6.5.1 The pipe operator

In the following, we will introduce the pipe operator |>, that was introduced in the version 4.1 of R. This operator allows a clear syntax for successive operations, as “what is on the left of the operator is given as first argument of what is on the right”. It is thus a good habit to write each operation on a separate line to facilitate the reading. This is particularly helpful when performing multiple nested operations. For example, summary(head(tail(dt),2)), which is hard to read, would translate to:

dt |> 
    tail() |> 
    head(2) |> 
    summary()

Note that before the 4.1 version of R, the pipe operator was only present thanks to the magrittr package, and was written %>%. In magrittr’s pipe, retrieving the piped object is done with the operator ., while it is done with the operator _ in base R’s.

Silly example:

"Hello" |> gsub("o", "e") # replace the substring "Hello" by "o" in string "e"
#> [1] "e"
"Hello" |> gsub("o", "e", x=_) # replace the substring "o" by "e" in string "Hello"
#> [1] "Helle"
"Hello" %>% gsub("o", "e") # replace the substring "Hello" by "o" in string "e"
#> [1] "e"
# /!\ In base R pipe, the '_' needs to  be for a named argument, 
# while it is not necessary for the '%>%' pipe
"Hello" %>% gsub("o", "e", .) # replace the substring "o" by "e" in string "Hello"
#> [1] "Helle"

6.5.2 Sampling data

dt |> slice(1:3)  # by index
#> # A tibble: 3 × 4
#>         x     y signx    signy   
#>     <dbl> <dbl> <chr>    <chr>   
#> 1 -0.974  0.907 negative positive
#> 2  0.738  0.717 positive positive
#> 3  0.0657 0.825 positive positive
dt |> sample_n(3) # randomly
#> # A tibble: 3 × 4
#>        x      y signx    signy   
#>    <dbl>  <dbl> <chr>    <chr>   
#> 1 -0.188  0.745 negative positive
#> 2 -0.360  0.638 negative positive
#> 3  0.217 -0.834 positive negative

6.5.3 Operations on groups of a variable

group_by(column) groups by similar values of the wanted column(s) and performs the next operations on each element of the group successively.

dt |> 
    group_by(signx)
#> # A tibble: 1,500 × 4
#> # Groups:   signx [2]
#>          x      y signx    signy   
#>      <dbl>  <dbl> <chr>    <chr>   
#>  1 -0.974   0.907 negative positive
#>  2  0.738   0.717 positive positive
#>  3  0.0657  0.825 positive positive
#>  4 -0.446  -0.298 negative negative
#>  5  0.729   0.150 positive positive
#>  6 -0.557   0.913 negative positive
#>  7 -0.368  -0.557 negative negative
#>  8  0.911  -0.959 positive negative
#>  9 -0.0884 -0.779 negative negative
#> 10  0.140  -0.283 positive negative
#> # ℹ 1,490 more rows
dt |> 
    group_by(signx) |> 
    sample_n(3)
#> # A tibble: 6 × 4
#> # Groups:   signx [2]
#>         x       y signx    signy   
#>     <dbl>   <dbl> <chr>    <chr>   
#> 1 -0.294   0.377  negative positive
#> 2 -0.892  -0.377  negative negative
#> 3 -0.0259 -0.410  negative negative
#> 4  0.772   0.0371 positive positive
#> 5  0.158  -0.172  positive negative
#> 6  0.781  -0.107  positive negative
dt |> 
    group_by(signx, signy) |> 
    sample_n(3)
#> # A tibble: 12 × 4
#> # Groups:   signx, signy [4]
#>          x       y signx    signy   
#>      <dbl>   <dbl> <chr>    <chr>   
#>  1 -0.265  -0.588  negative negative
#>  2 -0.749  -0.240  negative negative
#>  3 -0.689  -0.816  negative negative
#>  4 -0.910   0.770  negative positive
#>  5 -0.502   0.167  negative positive
#>  6 -0.942   0.341  negative positive
#>  7  0.124  -0.566  positive negative
#>  8  0.970  -0.0998 positive negative
#>  9  0.467  -0.0997 positive negative
#> 10  0.486   0.597  positive positive
#> 11  0.0685  0.138  positive positive
#> 12  0.224   0.490  positive positive

6.5.4 Summary by groups of a variable

summarise() returns a single value for each element of the groups.

dt |> 
    group_by(signx) |> 
    summarise(count  = n(),
              mean_x = mean(x), 
              sd_x   = sd(x))
#> # A tibble: 2 × 4
#>   signx    count mean_x  sd_x
#>   <chr>    <int>  <dbl> <dbl>
#> 1 negative   807 -0.516 0.296
#> 2 positive   693  0.491 0.296
dt |> 
    group_by(signx, signy) |> 
    summarise(count  = n(),
              mean_x = mean(x), 
              mean_y = mean(y))
#> # A tibble: 4 × 5
#> # Groups:   signx [2]
#>   signx    signy    count mean_x mean_y
#>   <chr>    <chr>    <int>  <dbl>  <dbl>
#> 1 negative negative   382 -0.510 -0.499
#> 2 negative positive   425 -0.521  0.487
#> 3 positive negative   375  0.494 -0.532
#> 4 positive positive   318  0.487  0.495

6.5.5 Sorting

dt |> arrange(x)
#> # A tibble: 1,500 × 4
#>         x      y signx    signy   
#>     <dbl>  <dbl> <chr>    <chr>   
#>  1 -0.996 -0.848 negative negative
#>  2 -0.996 -0.478 negative negative
#>  3 -0.996 -0.438 negative negative
#>  4 -0.996  0.565 negative positive
#>  5 -0.996  0.103 negative positive
#>  6 -0.996 -0.545 negative negative
#>  7 -0.991 -0.931 negative negative
#>  8 -0.991  0.598 negative positive
#>  9 -0.991 -0.793 negative negative
#> 10 -0.987  0.173 negative positive
#> # ℹ 1,490 more rows
dt |> arrange(x, desc(y))
#> # A tibble: 1,500 × 4
#>         x      y signx    signy   
#>     <dbl>  <dbl> <chr>    <chr>   
#>  1 -0.996 -0.438 negative negative
#>  2 -0.996 -0.478 negative negative
#>  3 -0.996 -0.848 negative negative
#>  4 -0.996  0.565 negative positive
#>  5 -0.996  0.103 negative positive
#>  6 -0.996 -0.545 negative negative
#>  7 -0.991  0.598 negative positive
#>  8 -0.991 -0.793 negative negative
#>  9 -0.991 -0.931 negative negative
#> 10 -0.987  0.583 negative positive
#> # ℹ 1,490 more rows

6.5.6 Merge tables column-wise

At least one column with the exact same name must be present in each table to use the xx_join() functions. There are more possibilities than inner_join() that I show here, see the help for more information.

dt2 <- tibble(signx=c("positive","positive","negative","negative"), 
              signy=c("positive","negative","positive","negative"), 
              value=c(TRUE, FALSE, FALSE, TRUE))
dt2
#> # A tibble: 4 × 3
#>   signx    signy    value
#>   <chr>    <chr>    <lgl>
#> 1 positive positive TRUE 
#> 2 positive negative FALSE
#> 3 negative positive FALSE
#> 4 negative negative TRUE
inner_join(dt, dt2)
#> # A tibble: 1,500 × 5
#>          x      y signx    signy    value
#>      <dbl>  <dbl> <chr>    <chr>    <lgl>
#>  1 -0.974   0.907 negative positive FALSE
#>  2  0.738   0.717 positive positive TRUE 
#>  3  0.0657  0.825 positive positive TRUE 
#>  4 -0.446  -0.298 negative negative TRUE 
#>  5  0.729   0.150 positive positive TRUE 
#>  6 -0.557   0.913 negative positive FALSE
#>  7 -0.368  -0.557 negative negative TRUE 
#>  8  0.911  -0.959 positive negative FALSE
#>  9 -0.0884 -0.779 negative negative TRUE 
#> 10  0.140  -0.283 positive negative FALSE
#> # ℹ 1,490 more rows

6.5.7 Merge tables row-wise

This works even if there are missing rows.

dt3 <- tibble(a=1:3, b=3:5, c=6:8)
dt4 <- tibble(a=3:1, c=3:5)
bind_rows(dt3, dt4)
#> # A tibble: 6 × 3
#>       a     b     c
#>   <int> <int> <int>
#> 1     1     3     6
#> 2     2     4     7
#> 3     3     5     8
#> 4     3    NA     3
#> 5     2    NA     4
#> 6     1    NA     5

6.5.8 Add/modify a column

mutate(), like $, adds a column if it doesn’t exist, and modifies it if it does.

dt |> mutate(w=seq_along(x), z=sin(x))
#> # A tibble: 1,500 × 6
#>          x      y signx    signy        w       z
#>      <dbl>  <dbl> <chr>    <chr>    <int>   <dbl>
#>  1 -0.974   0.907 negative positive     1 -0.827 
#>  2  0.738   0.717 positive positive     2  0.673 
#>  3  0.0657  0.825 positive positive     3  0.0656
#>  4 -0.446  -0.298 negative negative     4 -0.431 
#>  5  0.729   0.150 positive positive     5  0.666 
#>  6 -0.557   0.913 negative positive     6 -0.528 
#>  7 -0.368  -0.557 negative negative     7 -0.359 
#>  8  0.911  -0.959 positive negative     8  0.790 
#>  9 -0.0884 -0.779 negative negative     9 -0.0883
#> 10  0.140  -0.283 positive negative    10  0.140 
#> # ℹ 1,490 more rows
dt |> mutate(x=seq_along(x))
#> # A tibble: 1,500 × 4
#>        x      y signx    signy   
#>    <int>  <dbl> <chr>    <chr>   
#>  1     1  0.907 negative positive
#>  2     2  0.717 positive positive
#>  3     3  0.825 positive positive
#>  4     4 -0.298 negative negative
#>  5     5  0.150 positive positive
#>  6     6  0.913 negative positive
#>  7     7 -0.557 negative negative
#>  8     8 -0.959 positive negative
#>  9     9 -0.779 negative negative
#> 10    10 -0.283 positive negative
#> # ℹ 1,490 more rows

6.5.9 Selecting columns

dt |> select(x)  # only x
#> # A tibble: 1,500 × 1
#>          x
#>      <dbl>
#>  1 -0.974 
#>  2  0.738 
#>  3  0.0657
#>  4 -0.446 
#>  5  0.729 
#>  6 -0.557 
#>  7 -0.368 
#>  8  0.911 
#>  9 -0.0884
#> 10  0.140 
#> # ℹ 1,490 more rows
dt |> select(-x) # all but x
#> # A tibble: 1,500 × 3
#>         y signx    signy   
#>     <dbl> <chr>    <chr>   
#>  1  0.907 negative positive
#>  2  0.717 positive positive
#>  3  0.825 positive positive
#>  4 -0.298 negative negative
#>  5  0.150 positive positive
#>  6  0.913 negative positive
#>  7 -0.557 negative negative
#>  8 -0.959 positive negative
#>  9 -0.779 negative negative
#> 10 -0.283 positive negative
#> # ℹ 1,490 more rows
dt |> select(starts_with("sign"))
#> # A tibble: 1,500 × 2
#>    signx    signy   
#>    <chr>    <chr>   
#>  1 negative positive
#>  2 positive positive
#>  3 positive positive
#>  4 negative negative
#>  5 positive positive
#>  6 negative positive
#>  7 negative negative
#>  8 positive negative
#>  9 negative negative
#> 10 positive negative
#> # ℹ 1,490 more rows
dt |> select(contains("x"))
#> # A tibble: 1,500 × 2
#>          x signx   
#>      <dbl> <chr>   
#>  1 -0.974  negative
#>  2  0.738  positive
#>  3  0.0657 positive
#>  4 -0.446  negative
#>  5  0.729  positive
#>  6 -0.557  negative
#>  7 -0.368  negative
#>  8  0.911  positive
#>  9 -0.0884 negative
#> 10  0.140  positive
#> # ℹ 1,490 more rows

6.5.10 Filtering columns

dt |> filter(signx=="positive")
#> # A tibble: 693 × 4
#>         x      y signx    signy   
#>     <dbl>  <dbl> <chr>    <chr>   
#>  1 0.738   0.717 positive positive
#>  2 0.0657  0.825 positive positive
#>  3 0.729   0.150 positive positive
#>  4 0.911  -0.959 positive negative
#>  5 0.140  -0.283 positive negative
#>  6 0.330  -0.275 positive negative
#>  7 0.147   0.498 positive positive
#>  8 0.718  -0.122 positive negative
#>  9 0.153   0.242 positive positive
#> 10 0.108  -0.311 positive negative
#> # ℹ 683 more rows
dt |> filter(x<0, y>.1) # multiple filters can be applied at once
#> # A tibble: 379 × 4
#>          x     y signx    signy   
#>      <dbl> <dbl> <chr>    <chr>   
#>  1 -0.974  0.907 negative positive
#>  2 -0.557  0.913 negative positive
#>  3 -0.840  0.724 negative positive
#>  4 -0.207  0.903 negative positive
#>  5 -0.539  0.193 negative positive
#>  6 -0.263  0.528 negative positive
#>  7 -0.0762 0.656 negative positive
#>  8 -0.676  0.409 negative positive
#>  9 -0.0550 0.312 negative positive
#> 10 -0.267  0.667 negative positive
#> # ℹ 369 more rows

6.5.11 Reorder columns

dt |> relocate(y, .after = signy)
#> # A tibble: 1,500 × 4
#>          x signx    signy         y
#>      <dbl> <chr>    <chr>     <dbl>
#>  1 -0.974  negative positive  0.907
#>  2  0.738  positive positive  0.717
#>  3  0.0657 positive positive  0.825
#>  4 -0.446  negative negative -0.298
#>  5  0.729  positive positive  0.150
#>  6 -0.557  negative positive  0.913
#>  7 -0.368  negative negative -0.557
#>  8  0.911  positive negative -0.959
#>  9 -0.0884 negative negative -0.779
#> 10  0.140  positive negative -0.283
#> # ℹ 1,490 more rows

6.5.12 Separate columns

The separation is based on standard separators such as “-”, “_”, “.”, ” “, etc. A single separator can be specified with the argument sep, otherwise all separators are used. One must provide the resulting vector of new column names: if one value is NA, this column will be discarded. Examples:

dt5 <- tibble(file=list.files(path="Exo/FTIR/Data/", pattern=".xls"))
dt5
#> # A tibble: 10 × 1
#>    file             
#>    <chr>            
#>  1 sample_0_25C.xls 
#>  2 sample_0_300C.xls
#>  3 sample_1_25C.xls 
#>  4 sample_1_300C.xls
#>  5 sample_2_25C.xls 
#>  6 sample_2_300C.xls
#>  7 sample_3_25C.xls 
#>  8 sample_3_300C.xls
#>  9 sample_4_25C.xls 
#> 10 sample_4_300C.xls
dt5 |> separate(file, c(NA, "sample", "temperature", NA), convert = TRUE)
#> # A tibble: 10 × 2
#>    sample temperature
#>     <int> <chr>      
#>  1      0 25C        
#>  2      0 300C       
#>  3      1 25C        
#>  4      1 300C       
#>  5      2 25C        
#>  6      2 300C       
#>  7      3 25C        
#>  8      3 300C       
#>  9      4 25C        
#> 10      4 300C
dt5 |> separate(file, 
                c("name", "extension"), 
                sep = "\\.")
#> # A tibble: 10 × 2
#>    name          extension
#>    <chr>         <chr>    
#>  1 sample_0_25C  xls      
#>  2 sample_0_300C xls      
#>  3 sample_1_25C  xls      
#>  4 sample_1_300C xls      
#>  5 sample_2_25C  xls      
#>  6 sample_2_300C xls      
#>  7 sample_3_25C  xls      
#>  8 sample_3_300C xls      
#>  9 sample_4_25C  xls      
#> 10 sample_4_300C xls

6.5.13 Apply a function recursively on each element of a column

Take a look at the cheatsheet on the purrr package for more options and a visual help on the map() family. I show here a use of purrr::map(vector, function) that returns a list. map(x, f) applies the function f() to each element of the vector x, putting the result in a separate element of a list: map(x, f) -> list(f(x1), f(x2), ... f(xn)). In case f(xi) returns a single value, you might want to use map_dbl() or map_chr(), for example, that will return a vector of doubles or of characters, respectively.

x <- c(pi, pi/3, pi/2)
map(x, sin)     # returns a list
#> [[1]]
#> [1] 1.224647e-16
#> 
#> [[2]]
#> [1] 0.8660254
#> 
#> [[3]]
#> [1] 1
x |> map_dbl(sin) # returns a vector
#> [1] 1.224647e-16 8.660254e-01 1.000000e+00

Of course, in the above case, it’s a stupid use of the power of map(). A typical use case is when you want to read multiple files, for example:

dt6 <- tibble(file=list.files(path="Exo/spectro2/Data", 
                              pattern = ".txt", 
                              full.names = TRUE)) |> 
    slice(1:5) |> 
    mutate(data = map(file, read_table, col_names = c("w", "Int"))) |> 
    mutate(file = basename(file))
dt6
#> # A tibble: 5 × 2
#>   file         data                  
#>   <chr>        <list>                
#> 1 rubis_01.txt <spc_tbl_ [1,015 × 2]>
#> 2 rubis_02.txt <spc_tbl_ [1,015 × 2]>
#> 3 rubis_03.txt <spc_tbl_ [1,015 × 2]>
#> 4 rubis_04.txt <spc_tbl_ [1,015 × 2]>
#> 5 rubis_05.txt <spc_tbl_ [1,015 × 2]>

This is (almost) equivalent to:

dt6 <- tibble(file=list.files(path="Exo/spectro2/Data", 
                              pattern = ".txt", 
                              full.names = TRUE)) |> 
    slice(1:5) |> 
    mutate(data = map(file, ~read_table(., col_names = c("w", "Int")))) |> 
    mutate(file = basename(file))

You see that you can create the function directly within the call to map using the shortcut map(vector, ~ function(.)). This is useful to provide more arguments to the function – another solution is to write your own function before the call to map() and then call this function in map().

Note that in case you need more parameters, you can use purrr::map2(vector1, vector2, ~function(.x, .y)), where .x and .y refer to vector1 and vector2, respectively (it’s always .x and .y whatever the name of vector1 and vector2).

tibble(x=1:3, y=5:7) |> 
    mutate(sum = map2_dbl(x, y, sum))
#> # A tibble: 3 × 3
#>       x     y   sum
#>   <int> <int> <dbl>
#> 1     1     5     6
#> 2     2     6     8
#> 3     3     7    10
tibble(a=list(tibble(x=1:3, y=5:7), 
              tibble(x=0:3, y=4:7)), 
       b=list(tibble(x=10:13, y=15:18), 
              tibble(x=-1:2,  y=-14:-17))) |> 
    mutate(sumx = map2_dbl(a, b, ~sum(.x$x, .y$x)),
           sumy = map2_dbl(a, b, ~sum(.x$y, .y$y)))
#> # A tibble: 2 × 4
#>   a                b                 sumx  sumy
#>   <list>           <list>           <dbl> <dbl>
#> 1 <tibble [3 × 2]> <tibble [4 × 2]>    52    84
#> 2 <tibble [4 × 2]> <tibble [4 × 2]>     8   -40

6.5.14 Nesting and un-nesting data

dt7 <- dt6 |> 
    mutate(file = basename(file)) |> 
    unnest(data)
dt7
#> # A tibble: 5,075 × 3
#>    file             w   Int
#>    <chr>        <dbl> <dbl>
#>  1 rubis_01.txt 3064.  43.9
#>  2 rubis_01.txt 3064.  47.9
#>  3 rubis_01.txt 3064.  44.5
#>  4 rubis_01.txt 3065.  50.5
#>  5 rubis_01.txt 3065.  50.5
#>  6 rubis_01.txt 3065.  44.5
#>  7 rubis_01.txt 3065.  44.9
#>  8 rubis_01.txt 3066.  39.9
#>  9 rubis_01.txt 3066.  49.5
#> 10 rubis_01.txt 3066.  48.9
#> # ℹ 5,065 more rows
# Nesting data per repeated values in a column (~equivalent to grouping)
dt7 |> nest(data=-file)
#> # A tibble: 5 × 2
#>   file         data                
#>   <chr>        <list>              
#> 1 rubis_01.txt <tibble [1,015 × 2]>
#> 2 rubis_02.txt <tibble [1,015 × 2]>
#> 3 rubis_03.txt <tibble [1,015 × 2]>
#> 4 rubis_04.txt <tibble [1,015 × 2]>
#> 5 rubis_05.txt <tibble [1,015 × 2]>

6.5.15 Providing data to ggplot

dt |> 
    filter(abs(y) > 0.1) |> 
    ggplot(aes(x=x, y=y, color=signy))+
        geom_point()

6.6 Exercises

Download the archive with all the exercises files, unzip it in your R class RStudio project, and edit the R files.

Exercise 1
  • Create a 3 column data.frame containing 10 random values, their sinus, and the sum of the two first columns.
  • Print the 4 first lines of the table
  • Print the second column
  • Print the average of the third column
  • Using plot(x,y) where x and y are vectors, plot the 2nd column as a function of the first
  • Look into the function write.table() to write a text file containing this data.frame
  • Do the all the same things with a tibble
Solution 
# Create a 3 column `data.frame`{.R} containing 10 random values, their sinus, 
# and the sum of the two first columns.
x <- runif(10)
y <- sin(x)
z <- x + y
df <- data.frame(x=x, y=y, z=z)
# Print the 4 first lines of the table
head(df, 4)
#>           x         y         z
#> 1 0.6239788 0.5842688 1.2082477
#> 2 0.8901950 0.7771944 1.6673894
#> 3 0.5120477 0.4899633 1.0020110
#> 4 0.4831712 0.4645896 0.9477608
# Print the second column
df[,2]
#>  [1] 0.58426884 0.77719445 0.48996331 0.46458965 0.16971538 0.25127129
#>  [7] 0.06476331 0.01040769 0.69257419 0.28421762
# Print the average of the third column
mean(df$z); mean(df[3]); mean(df[,3])
#> [1] 0.7851351
#> [1] NA
#> [1] 0.7851351
# Using `plot(x,y)`{.R} where `x` and `y` are vectors, 
# plot the 2nd column as a function of the first
plot(df[,1], df[,2])

plot(df$x, df$y)

# Look into the function `write.table()`{.R} to write a text file 
# containing this `data.frame`{.R}
write.table(df, "Data/some_data.dat", quote = FALSE, row.names = FALSE)
# # # # # # # # # # # # # # # # # 
# Tibble version
library(tidyverse)
df_tib <- tibble(a = runif(10), b = sin(a), c = a + b)
head(df_tib, 4)
#> # A tibble: 4 × 3
#>        a      b      c
#>    <dbl>  <dbl>  <dbl>
#> 1 0.715  0.656  1.37  
#> 2 0.0255 0.0255 0.0510
#> 3 0.139  0.138  0.277 
#> 4 0.277  0.273  0.550
df_tib[,2]; df_tib[[2]];
#> # A tibble: 10 × 1
#>         b
#>     <dbl>
#>  1 0.656 
#>  2 0.0255
#>  3 0.138 
#>  4 0.273 
#>  5 0.580 
#>  6 0.768 
#>  7 0.726 
#>  8 0.475 
#>  9 0.0708
#> 10 0.291
#>  [1] 0.65587415 0.02548567 0.13827791 0.27324058 0.57997938 0.76832493
#>  [7] 0.72563845 0.47476270 0.07075553 0.29056731
mean(df_tib$c); mean(df_tib[3]); mean(df_tib[,3]); mean(df_tib[[3]])
#> [1] 0.8326432
#> [1] NA
#> [1] NA
#> [1] 0.8326432
write.table(df_tib, "Data/some_data.dat", quote = FALSE, row.names = FALSE)
plot(df_tib$a, df_tib$b)

Exercise 2
  • Download the files:
  • Load them into separate data.frames. Look into the options of read.table(), read.csv(), readxl::read_excel(), to get the proper data fields.
  • Add column names to the data.frame containing rubis_01.txt.
  • Print their dimensions.
  • Do the same things with tibbles.
Solution 
rubis_01   <- read.table("Data/rubis_01.txt", col.names = c("w", "intensity"))
population <- read.csv("Data/population.csv")
FTIR_rocks <- readxl::read_excel("Data/FTIR_rocks.xlsx")
dim(rubis_01); names(rubis_01)
#> [1] 1015    2
#> [1] "w"         "intensity"
dim(population); names(population)
#> [1]  8 21
#>  [1] "year"          "Angers"        "Bordeaux"      "Brest"        
#>  [5] "Dijon"         "Grenoble"      "LeHavre"       "LeMans"       
#>  [9] "Lille"         "Lyon"          "Marseille"     "Montpellier"  
#> [13] "Nantes"        "Nice"          "Paris"         "Reims"        
#> [17] "Rennes"        "Saint.Etienne" "Strasbourg"    "Toulon"       
#> [21] "Toulouse"
dim(FTIR_rocks); names(FTIR_rocks)
#> [1] 4718    4
#> [1] "wavenumber, cm-1" "rock 1"           "rock 2"           "rock 3"
library(tidyverse)
rubis_01 <- read_table("Data/rubis_01.txt", col_names = c("w", "intensity"))
population <- read_csv("Data/population.csv")
Exercise 3
  • Download the TGA data file ATG.txt
  • Load it into a data.frame. Look into the options of read.table() to get the proper data fields.
  • Do the same with a tibble
Solution 
d <- read.table("Data/ATG.txt", 
                skip=12,
                header=FALSE, 
                nrows=4088)
names(d) <- c("Index", "t", "Ts", "Tr", "Value")
head(d)
#>   Index  t      Ts Tr   Value
#> 1     0  0 32.3769 25 32.9680
#> 2     3  3 32.4051 25 32.9655
#> 3     6  6 32.4332 25 32.9619
#> 4     9  9 32.4726 25 32.9582
#> 5    12 12 32.5066 25 32.9544
#> 6    15 15 32.5221 25 32.9504
d <- read.table("Data/ATG.txt", 
                skip=10,
                comment.char="[",
                header=TRUE, 
                nrows=4088)
head(d)
#>   Index  t      Ts Tr   Value
#> 1     0  0 32.3769 25 32.9680
#> 2     3  3 32.4051 25 32.9655
#> 3     6  6 32.4332 25 32.9619
#> 4     9  9 32.4726 25 32.9582
#> 5    12 12 32.5066 25 32.9544
#> 6    15 15 32.5221 25 32.9504
library(tidyverse)
d <- read_table("Data/ATG.txt", 
                skip    = 10,
                comment = "[") |> 
        drop_na()
d
#> # A tibble: 4,088 × 5
#>    Index     t    Ts    Tr Value
#>    <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1     0     0  32.4    25  33.0
#>  2     3     3  32.4    25  33.0
#>  3     6     6  32.4    25  33.0
#>  4     9     9  32.5    25  33.0
#>  5    12    12  32.5    25  33.0
#>  6    15    15  32.5    25  33.0
#>  7    18    18  32.6    25  32.9
#>  8    21    21  32.6    25  32.9
#>  9    24    24  32.6    25  32.9
#> 10    27    27  32.7    25  32.9
#> # ℹ 4,078 more rows
Exercise 4

Download population.csv and load it into a tibble.

  • What are the names of the columns?
  • Are the data tidy? make the table tidy if needed
  • Create a subset containing the data for Montpellier
    • What is the max and min of population in this city?
    • The average population over time?
  • What is the total population in 2012?
  • What is the total population per year?
  • What is the average population per city over the years?
Solution 
# Download population.txt and load it into a `data.frame`{.R}.
library(tidyverse)
popul <- read_csv("Data/population.csv")
# What are the names of the columns and the dimension of the table?
names(popul); dim(popul)
#>  [1] "year"          "Angers"        "Bordeaux"      "Brest"        
#>  [5] "Dijon"         "Grenoble"      "LeHavre"       "LeMans"       
#>  [9] "Lille"         "Lyon"          "Marseille"     "Montpellier"  
#> [13] "Nantes"        "Nice"          "Paris"         "Reims"        
#> [17] "Rennes"        "Saint-Etienne" "Strasbourg"    "Toulon"       
#> [21] "Toulouse"
#> [1]  8 21
# Are the data tidy?
head(popul) # no
#> # A tibble: 6 × 21
#>    year Angers Bordeaux  Brest  Dijon Grenoble LeHavre LeMans  Lille   Lyon
#>   <dbl>  <dbl>    <dbl>  <dbl>  <dbl>    <dbl>   <dbl>  <dbl>  <dbl>  <dbl>
#> 1  1962 115273   278403 136104 135694   156707  187845 132181 239955 535746
#> 2  1968 128557   266662 154023 145357   161616  207150 143246 238554 527800
#> 3  1975 137591   223131 166826 151705   166037  217882 152285 219204 456716
#> 4  1982 136038   208159 156060 140942   156637  199388 147697 196705 413095
#> 5  1990 141404   210336 147956 146703   150758  195854 145502 198691 415487
#> 6  1999 151279   215363 149634 149867   153317  190905 146105 212597 445452
#> # ℹ 11 more variables: Marseille <dbl>, Montpellier <dbl>, Nantes <dbl>,
#> #   Nice <dbl>, Paris <dbl>, Reims <dbl>, Rennes <dbl>, `Saint-Etienne` <dbl>,
#> #   Strasbourg <dbl>, Toulon <dbl>, Toulouse <dbl>
popul.tidy <- popul |> 
    pivot_longer(cols=-year,
                 names_to = "city",
                 values_to = "pop"
                )
popul.tidy
#> # A tibble: 160 × 3
#>     year city         pop
#>    <dbl> <chr>      <dbl>
#>  1  1962 Angers    115273
#>  2  1962 Bordeaux  278403
#>  3  1962 Brest     136104
#>  4  1962 Dijon     135694
#>  5  1962 Grenoble  156707
#>  6  1962 LeHavre   187845
#>  7  1962 LeMans    132181
#>  8  1962 Lille     239955
#>  9  1962 Lyon      535746
#> 10  1962 Marseille 778071
#> # ℹ 150 more rows
# Create a subset containing the data for Montpellier
mtp <- subset(popul.tidy, city == "Montpellier")
# I prefer the tidyverse version
mtp <- popul.tidy |> filter(city == "Montpellier")
# What is the max and min of population in this city?
max(mtp$pop)
#> [1] 268456
min(mtp$pop)
#> [1] 118864
range(mtp$pop)
#> [1] 118864 268456
# The average population over time?
mean(mtp$pop)
#> [1] 203114.4
# What is the total population in 2012?
sum(popul.tidy[popul.tidy$year == 2012, "pop"])
#> [1] 7334805
popul.tidy |> 
    filter(year==2012) |> 
    select(pop) |> 
    sum()
#> [1] 7334805
# What is the total population per year?
popul.tidy |> 
    group_by(year) |> 
    summarise(pop_tot=sum(pop))
#> # A tibble: 8 × 2
#>    year pop_tot
#>   <dbl>   <dbl>
#> 1  1962 7349547
#> 2  1968 7550999
#> 3  1975 7298183
#> 4  1982 6933230
#> 5  1990 6857291
#> 6  1999 6965325
#> 7  2007 7235114
#> 8  2012 7334805
# What is the average population per city over the years?
popul.tidy |> 
    group_by(city) |> 
    summarise(pop_ave=mean(pop))
#> # A tibble: 20 × 2
#>    city           pop_ave
#>    <chr>            <dbl>
#>  1 Angers         138783.
#>  2 Bordeaux       234815.
#>  3 Brest          149125.
#>  4 Dijon          146735.
#>  5 Grenoble       157526.
#>  6 LeHavre        193990.
#>  7 LeMans         144347.
#>  8 Lille          220018.
#>  9 Lyon           470371.
#> 10 Marseille      844253.
#> 11 Montpellier    203114.
#> 12 Nantes         260925.
#> 13 Nice           334312.
#> 14 Paris         2321032.
#> 15 Reims          172278 
#> 16 Rennes         193425.
#> 17 Saint-Etienne  198135.
#> 18 Strasbourg     255429.
#> 19 Toulon         169683.
#> 20 Toulouse       382265.
Exercise 5
  • First, load the tidyverse and lubridate package
  • Load people1.csv and people2.csv into pp1 and pp2
  • Create a new tibble pp by using the pipe operator (%>%) and successively:
    • joining the two tibbles into one using inner_join()
    • adding a column age containing the age in years (use lubridate::time_length(x, 'years') with x a time difference in days) by using mutate()
  • Display a summary of the table using str()
  • Using groupe_by() and summarize():
    • Show the number of males and females in the table (use the counter n())
    • Show the average age per gender
    • Show the average size per gender and institution
    • Show the number of people from each country, sorted by descending population (arrange())
  • Using select(), display:
    • only the name and age columns
    • all but the name column
  • Using filter(), show data only for
    • Chinese people
    • From institution ECL and UCBL
    • People older than 22
    • People with a e in their name
Solution 
# First, load the `tidyverse` package
library(tidyverse)
# Load people1.csv and people2.csv
pp1  <- read_csv("Data/people1.csv")
pp2  <- read_csv("Data/people2.csv")
# Create a new tibble `pp` by using the pipe operator (`%>%`)
# and successively:
# - joining the two tibbles into one using `inner_join()`
# - adding a column `age` containing the age in years 
#   (use lubridate's `time_length(x, 'years')` with x a time
#   difference in days) by using `mutate()`
pp <- pp1 |> 
        inner_join(pp2) |> 
        mutate(age=time_length(today()-dateofbirth,'years'))
# Display a summary of the table using `str()`
str(pp)
#> tibble [20 × 7] (S3: tbl_df/tbl/data.frame)
#>  $ name       : chr [1:20] "Salem" "Dilruwan-Shanaka-Perera" "Hanna" "Sabin" ...
#>  $ gender     : chr [1:20] "Male" "Male" "Female" "Male" ...
#>  $ origin     : chr [1:20] "Yemen" "Sri Lanka" "Ukraine" "India" ...
#>  $ institution: chr [1:20] "UCBL" "INSA" "ECL" "INSA" ...
#>  $ dateofbirth: Date[1:20], format: "1997-12-26" "1997-03-28" ...
#>  $ size       : num [1:20] 161 172 165 186 176 ...
#>  $ age        : num [1:20] 26.2 26.9 26.1 28.6 28.8 ...
# Using `groupe_by()` and `summarize()`:
# - Show the number of males and females in the table 
#   (use the counter `n()`)
pp |> 
    group_by(gender) |> 
    summarize(count=n())
#> # A tibble: 2 × 2
#>   gender count
#>   <chr>  <int>
#> 1 Female     4
#> 2 Male      16
# - Show the average age per gender
pp |> 
    group_by(gender) |> 
    summarize(age=mean(age))
#> # A tibble: 2 × 2
#>   gender   age
#>   <chr>  <dbl>
#> 1 Female  28.2
#> 2 Male    27.9
# - Show the average size per gender and institution
pp |> 
    group_by(gender, institution) |> 
    summarize(size=mean(size))
#> # A tibble: 4 × 3
#> # Groups:   gender [2]
#>   gender institution  size
#>   <chr>  <chr>       <dbl>
#> 1 Female ECL          178.
#> 2 Male   ECL          168.
#> 3 Male   INSA         174.
#> 4 Male   UCBL         174.
# - Show the number of people from each country, 
#   sorted by descending population
pp |> 
    group_by(origin) |> 
    summarize(count=n()) |> 
    arrange(desc(count))
#> # A tibble: 13 × 2
#>    origin      count
#>    <chr>       <int>
#>  1 China           4
#>  2 Ukraine         4
#>  3 USA             2
#>  4 Afghanistan     1
#>  5 Austria         1
#>  6 Brazil          1
#>  7 Colombia        1
#>  8 Cyprus          1
#>  9 India           1
#> 10 Iran            1
#> 11 Sri Lanka       1
#> 12 Tunisia         1
#> 13 Yemen           1
# Using `select()`, display:
# - only the name and age columns
pp |> select(c(name, age))
#> # A tibble: 20 × 2
#>    name                      age
#>    <chr>                   <dbl>
#>  1 Salem                    26.2
#>  2 Dilruwan-Shanaka-Perera  26.9
#>  3 Hanna                    26.1
#>  4 Sabin                    28.6
#>  5 Benedikt                 28.8
#>  6 Jordyn                   27.0
#>  7 Jennifer                 28.7
#>  8 Yiran                    29.1
#>  9 Leran                    31.1
#> 10 Aymen                    34.0
#> 11 Pavlo                    26.9
#> 12 Saulo                    29.4
#> 13 Nicolas-Estevan          30.2
#> 14 Farzad                   27.2
#> 15 Roein                    24.6
#> 16 Paraskevas               24.7
#> 17 Ihor                     24.4
#> 18 Iryna                    31.0
#> 19 Peng                     27.2
#> 20 Mingyuan                 26.5
# - all but the name column
pp |> select(-name)
#> # A tibble: 20 × 6
#>    gender origin      institution dateofbirth  size   age
#>    <chr>  <chr>       <chr>       <date>      <dbl> <dbl>
#>  1 Male   Yemen       UCBL        1997-12-26   161.  26.2
#>  2 Male   Sri Lanka   INSA        1997-03-28   172.  26.9
#>  3 Female Ukraine     ECL         1997-12-30   165.  26.1
#>  4 Male   India       INSA        1995-08-04   186.  28.6
#>  5 Male   Austria     UCBL        1995-04-25   176.  28.8
#>  6 Female USA         ECL         1997-02-19   176.  27.0
#>  7 Female USA         ECL         1995-05-28   179   28.7
#>  8 Male   China       UCBL        1995-02-04   188.  29.1
#>  9 Male   China       UCBL        1992-12-30   186   31.1
#> 10 Male   Tunisia     INSA        1990-03-03   160.  34.0
#> 11 Male   Ukraine     ECL         1997-04-12   151.  26.9
#> 12 Male   Brazil      ECL         1994-09-24   184.  29.4
#> 13 Male   Colombia    INSA        1993-11-25   184.  30.2
#> 14 Male   Iran        INSA        1996-12-27   183   27.2
#> 15 Male   Afghanistan INSA        1999-07-11   155.  24.6
#> 16 Male   Cyprus      INSA        1999-06-25   176.  24.7
#> 17 Male   Ukraine     ECL         1999-10-03   170.  24.4
#> 18 Female Ukraine     ECL         1993-02-27   192   31.0
#> 19 Male   China       UCBL        1996-12-14   171   27.2
#> 20 Male   China       UCBL        1997-08-21   164.  26.5
# Using `filter()`, show data only for
# - Chinese people
pp |> filter(origin=='China')
#> # A tibble: 4 × 7
#>   name     gender origin institution dateofbirth  size   age
#>   <chr>    <chr>  <chr>  <chr>       <date>      <dbl> <dbl>
#> 1 Yiran    Male   China  UCBL        1995-02-04   188.  29.1
#> 2 Leran    Male   China  UCBL        1992-12-30   186   31.1
#> 3 Peng     Male   China  UCBL        1996-12-14   171   27.2
#> 4 Mingyuan Male   China  UCBL        1997-08-21   164.  26.5
# - From institution ECL and UCBL
pp |> filter(institution %in% c('ECL', 'UCBL'))
#> # A tibble: 13 × 7
#>    name     gender origin  institution dateofbirth  size   age
#>    <chr>    <chr>  <chr>   <chr>       <date>      <dbl> <dbl>
#>  1 Salem    Male   Yemen   UCBL        1997-12-26   161.  26.2
#>  2 Hanna    Female Ukraine ECL         1997-12-30   165.  26.1
#>  3 Benedikt Male   Austria UCBL        1995-04-25   176.  28.8
#>  4 Jordyn   Female USA     ECL         1997-02-19   176.  27.0
#>  5 Jennifer Female USA     ECL         1995-05-28   179   28.7
#>  6 Yiran    Male   China   UCBL        1995-02-04   188.  29.1
#>  7 Leran    Male   China   UCBL        1992-12-30   186   31.1
#>  8 Pavlo    Male   Ukraine ECL         1997-04-12   151.  26.9
#>  9 Saulo    Male   Brazil  ECL         1994-09-24   184.  29.4
#> 10 Ihor     Male   Ukraine ECL         1999-10-03   170.  24.4
#> 11 Iryna    Female Ukraine ECL         1993-02-27   192   31.0
#> 12 Peng     Male   China   UCBL        1996-12-14   171   27.2
#> 13 Mingyuan Male   China   UCBL        1997-08-21   164.  26.5
# - People older than 22 
pp |> filter(age>22)
#> # A tibble: 20 × 7
#>    name                    gender origin     institution dateofbirth  size   age
#>    <chr>                   <chr>  <chr>      <chr>       <date>      <dbl> <dbl>
#>  1 Salem                   Male   Yemen      UCBL        1997-12-26   161.  26.2
#>  2 Dilruwan-Shanaka-Perera Male   Sri Lanka  INSA        1997-03-28   172.  26.9
#>  3 Hanna                   Female Ukraine    ECL         1997-12-30   165.  26.1
#>  4 Sabin                   Male   India      INSA        1995-08-04   186.  28.6
#>  5 Benedikt                Male   Austria    UCBL        1995-04-25   176.  28.8
#>  6 Jordyn                  Female USA        ECL         1997-02-19   176.  27.0
#>  7 Jennifer                Female USA        ECL         1995-05-28   179   28.7
#>  8 Yiran                   Male   China      UCBL        1995-02-04   188.  29.1
#>  9 Leran                   Male   China      UCBL        1992-12-30   186   31.1
#> 10 Aymen                   Male   Tunisia    INSA        1990-03-03   160.  34.0
#> 11 Pavlo                   Male   Ukraine    ECL         1997-04-12   151.  26.9
#> 12 Saulo                   Male   Brazil     ECL         1994-09-24   184.  29.4
#> 13 Nicolas-Estevan         Male   Colombia   INSA        1993-11-25   184.  30.2
#> 14 Farzad                  Male   Iran       INSA        1996-12-27   183   27.2
#> 15 Roein                   Male   Afghanist… INSA        1999-07-11   155.  24.6
#> 16 Paraskevas              Male   Cyprus     INSA        1999-06-25   176.  24.7
#> 17 Ihor                    Male   Ukraine    ECL         1999-10-03   170.  24.4
#> 18 Iryna                   Female Ukraine    ECL         1993-02-27   192   31.0
#> 19 Peng                    Male   China      UCBL        1996-12-14   171   27.2
#> 20 Mingyuan                Male   China      UCBL        1997-08-21   164.  26.5
# - People with a `e` in their name
pp |> filter(grepl('e',name))
#> # A tibble: 10 × 7
#>    name                    gender origin     institution dateofbirth  size   age
#>    <chr>                   <chr>  <chr>      <chr>       <date>      <dbl> <dbl>
#>  1 Salem                   Male   Yemen      UCBL        1997-12-26   161.  26.2
#>  2 Dilruwan-Shanaka-Perera Male   Sri Lanka  INSA        1997-03-28   172.  26.9
#>  3 Benedikt                Male   Austria    UCBL        1995-04-25   176.  28.8
#>  4 Jennifer                Female USA        ECL         1995-05-28   179   28.7
#>  5 Leran                   Male   China      UCBL        1992-12-30   186   31.1
#>  6 Aymen                   Male   Tunisia    INSA        1990-03-03   160.  34.0
#>  7 Nicolas-Estevan         Male   Colombia   INSA        1993-11-25   184.  30.2
#>  8 Roein                   Male   Afghanist… INSA        1999-07-11   155.  24.6
#>  9 Paraskevas              Male   Cyprus     INSA        1999-06-25   176.  24.7
#> 10 Peng                    Male   China      UCBL        1996-12-14   171   27.2

For more interesting exercises in the tidyverse, look at: