library(tidyverse)
library(readxl)
library(janitor)4 Wide vs long format, summarise
In this chapter, we will examine various data formats and learn how to convert between them. We will define groups within the data and calculate several statistics for them using summarise functions.
4.1 Data formats
There are two main ways in which the data can be organised across rows and columns—wide or long format. We will show you an example of spruce forest data, where we recorded plant species in several sites and, at each site, we also estimated their abundance, here approximated as percentage cover (a higher value means that the species covered a larger area of the surveyed vegetation plot, but we do not give the area itself, but relatively to the total area, i.e. percentage of the total area). Note that the cover of species might overlap, as they grow at different heights.
Wide format is more conservative and used in many older packages for ecological data analysis. In our example, we list all species, and the columns correspond to individual sites/samples, and the values indicate species abundance at each site. This type of data is often called a species matrix, and you would need this arrangement e.g. for ordinations in vegan. However, the wide format also has many cons.
One of them is the file size. In the example above, there is an abundance of given plants in each of the sites. When a species is present at only one site, such as Trientalis europaea, it still occupies space across the entire table, where there can be hundreds or thousands of sites. The table code, of course, is memory-demanding.
One more point. For many ecological analyses, you cannot keep the empty cells empty, since they are recognised as NAs. Therefore, you must take an additional step and fill them with zeros.
Another disadvantage is that it is not easy to add new information to the listed species. For example, if you want to separate species that are in a tree vegetation layer (recognised in vegetation ecology as 1), a herb layer (6), and a moss layer (9), you would have to add this information to the species name, e.g., Picea_abies_1. Can you see a conflict with the basic principles of tidyverse?
Long format, in contrast, is excellent for handling large datasets. Imagine we have more sites than those shown in the example above. In this format, we list all the species for Site1, then for Site2, and so on. However, we list only the species that are actually present! By simply counting the rows belonging to each site, you have the information about the overall species richness.
We can also add any information that describes the data, such as vegetation layers, growth forms, native/alien status, etc. After that, we can very simply filter, summarise and calculate further statistics.
4.2 From long to wide
We will first start a new script for this chapter and load the libraries. Remember to keep the script tidy and include your remarks.
Now, we will import the data. In the first example, we will again use the Forest understory data from our folder. However, this time we will upload the species data saved in a long format, and we will prepare a species matrix in a wide format, so that it can be used in specific ecological analyses, e.g. in vegan.
spe <- read_csv("data/forest_understory/Axmanova-Forest-spe.csv")Rows: 2277 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): Species
dbl (3): RELEVE_NR, Layer, CoverPerc
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.tibble(spe)# A tibble: 2,277 × 4
RELEVE_NR Species Layer CoverPerc
<dbl> <chr> <dbl> <dbl>
1 1 Quercus petraea agg. 1 63
2 1 Acer campestre 1 18
3 1 Crataegus laevigata 4 2
4 1 Cornus mas 4 8
5 1 Lonicera xylosteum 4 3
6 1 Galium sylvaticum 6 3
7 1 Carex digitata 6 3
8 1 Melica nutans 6 2
9 1 Polygonatum odoratum 6 2
10 1 Geum urbanum 6 2
# ℹ 2,267 more rowsWe can see that there are plant species names sorted by RELEVE_NR, where each number indicates a vegetation record from a specific site (also known as a vegetation plot or sample). We will rename this to make it easier for us as PlotID. Furthermore, we may need to modify the species names to conform to the compact format, using underscores instead of spaces. For this, we will use the mutate() function with str_replace() (for string replacement), specifying that an underscore should replace each space, and apply it directly to the original column.
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(Species = str_replace_all(Species, " ", "_"))# A tibble: 2,277 × 4
PlotID Species Layer CoverPerc
<dbl> <chr> <dbl> <dbl>
1 1 Quercus_petraea_agg. 1 63
2 1 Acer_campestre 1 18
3 1 Crataegus_laevigata 4 2
4 1 Cornus_mas 4 8
5 1 Lonicera_xylosteum 4 3
6 1 Galium_sylvaticum 6 3
7 1 Carex_digitata 6 3
8 1 Melica_nutans 6 2
9 1 Polygonatum_odoratum 6 2
10 1 Geum_urbanum 6 2
# ℹ 2,267 more rows*If you want to play a bit, you can create a new column (e.g. SpeciesNew) to see both the original name and the changed name and test also other types of separators.
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(SpeciesNew = str_replace_all(Species, " ", "_"))# A tibble: 2,277 × 5
PlotID Species Layer CoverPerc SpeciesNew
<dbl> <chr> <dbl> <dbl> <chr>
1 1 Quercus petraea agg. 1 63 Quercus_petraea_agg.
2 1 Acer campestre 1 18 Acer_campestre
3 1 Crataegus laevigata 4 2 Crataegus_laevigata
4 1 Cornus mas 4 8 Cornus_mas
5 1 Lonicera xylosteum 4 3 Lonicera_xylosteum
6 1 Galium sylvaticum 6 3 Galium_sylvaticum
7 1 Carex digitata 6 3 Carex_digitata
8 1 Melica nutans 6 2 Melica_nutans
9 1 Polygonatum odoratum 6 2 Polygonatum_odoratum
10 1 Geum urbanum 6 2 Geum_urbanum
# ℹ 2,267 more rowsNote that, just as above, you can change different patterns, e.g., remove part of the strings. There are even more complex things we can do, but we will save them for later, as they require some knowledge of regular expression rules. Here, I created a new column where I removed the indication that the species belongs to a complex, i.e., the part of the string saying it is an aggregate. I also prepared a column “check” where I compare the new and old names to determine if they are equal or not, and filtered only those cases where they are not. This should show me exactly the changed rows. If I am satisfied with the result, I can then remove the lines using this check and even rewrite the original column.
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(SpeciesNew = str_replace_all(Species, " agg.", "")) %>%
mutate(check = (SpeciesNew == Species))%>%
filter(check == "FALSE") %>%
select(PlotID, Species, SpeciesNew, check) #select only relevant to see at a first glance# A tibble: 159 × 4
PlotID Species SpeciesNew check
<dbl> <chr> <chr> <lgl>
1 1 Quercus petraea agg. Quercus petraea FALSE
2 1 Rubus fruticosus agg. Rubus fruticosus FALSE
3 1 Quercus petraea agg. Quercus petraea FALSE
4 2 Quercus petraea agg. Quercus petraea FALSE
5 2 Quercus petraea agg. Quercus petraea FALSE
6 3 Quercus petraea agg. Quercus petraea FALSE
7 3 Quercus petraea agg. Quercus petraea FALSE
8 3 Galium pumilum agg. Galium pumilum FALSE
9 3 Senecio nemorensis agg. Senecio nemorensis FALSE
10 3 Veronica chamaedrys agg. Veronica chamaedrys FALSE
# ℹ 149 more rowsWe have the condensed name with underscores, but there are still more variables in the table. We can either remove them or merge them to be included in the final wide format. Here, we will deviate slightly from tidy rules and add the information about the vegetation layer directly to the variable Species using the unite() function from the tidyr package, which merges strings from two or more columns into a new one: A+B = A_B. Default separator is again underscore, unless you specify it differently by the sep argument e.g. sep= "."
Argument na.rm indicates what to do if in one of the combined columns there is no value but NA. We have set this argument to TRUE to remove the NA. If you keep it FALSE, it is possible that in some data, the new string will be a_NA or NA_b, or even NA_NA (see line 4 of our example).
The remove argument set to TRUE will remove the original columns that we used to combine the new one (in the example above, you will have only z). In our case, we will keep the original columns for visual checking, and we will use the select() function in the next step to remove them.
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(Species = str_replace_all(Species, " ", "_")) %>%
unite("SpeciesLayer", Species, Layer, na.rm = TRUE, remove = FALSE) # A tibble: 2,277 × 5
PlotID SpeciesLayer Species Layer CoverPerc
<dbl> <chr> <chr> <dbl> <dbl>
1 1 Quercus_petraea_agg._1 Quercus_petraea_agg. 1 63
2 1 Acer_campestre_1 Acer_campestre 1 18
3 1 Crataegus_laevigata_4 Crataegus_laevigata 4 2
4 1 Cornus_mas_4 Cornus_mas 4 8
5 1 Lonicera_xylosteum_4 Lonicera_xylosteum 4 3
6 1 Galium_sylvaticum_6 Galium_sylvaticum 6 3
7 1 Carex_digitata_6 Carex_digitata 6 3
8 1 Melica_nutans_6 Melica_nutans 6 2
9 1 Polygonatum_odoratum_6 Polygonatum_odoratum 6 2
10 1 Geum_urbanum_6 Geum_urbanum 6 2
# ℹ 2,267 more rowsNote that the function that works in the opposite direction is called separate() or, more recently, it is recommended to use separate_wider_delim(). To make it easier to later separate the information about layer, we can decide to use one separator within the species-name string (e.g. “.”) and another separator to attach the layer information (here I specify it in the sep argument, but the default would be the same).
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(Species = str_replace_all(Species, " ", ".")) %>%
unite("SpeciesLayer", Species, Layer, sep= "_", na.rm = TRUE, remove = FALSE) # A tibble: 2,277 × 5
PlotID SpeciesLayer Species Layer CoverPerc
<dbl> <chr> <chr> <dbl> <dbl>
1 1 Quercus.petraea.agg._1 Quercus.petraea.agg. 1 63
2 1 Acer.campestre_1 Acer.campestre 1 18
3 1 Crataegus.laevigata_4 Crataegus.laevigata 4 2
4 1 Cornus.mas_4 Cornus.mas 4 8
5 1 Lonicera.xylosteum_4 Lonicera.xylosteum 4 3
6 1 Galium.sylvaticum_6 Galium.sylvaticum 6 3
7 1 Carex.digitata_6 Carex.digitata 6 3
8 1 Melica.nutans_6 Melica.nutans 6 2
9 1 Polygonatum.odoratum_6 Polygonatum.odoratum 6 2
10 1 Geum.urbanum_6 Geum.urbanum 6 2
# ℹ 2,267 more rowsAt this point, we have everything we need to use it as input for the wide-format table: PlotID, SpeciesLayer, and the values of abundance saved as CoverPerc. One more step is to select only these or deselect (-) those that are not needed. Now we can finally use the pivot_wider() function to transform the data. We need to specify where we are obtaining the names of new variables (names_from) and where we should get the values that should appear in the table (values_from).
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(Species = str_replace_all(Species, " ", ".")) %>%
unite("SpeciesLayer", Species, Layer, na.rm = TRUE, remove = TRUE) %>%
pivot_wider(names_from = SpeciesLayer, values_from = CoverPerc)# A tibble: 65 × 370
PlotID Quercus.petraea.agg._1 Acer.campestre_1 Crataegus.laevigata_4
<dbl> <dbl> <dbl> <dbl>
1 1 63 18 2
2 2 63 NA NA
3 3 63 NA NA
4 4 38 NA NA
5 5 63 NA NA
6 6 38 NA NA
7 7 63 NA NA
8 8 38 NA NA
9 9 38 NA NA
10 10 63 NA NA
# ℹ 55 more rows
# ℹ 366 more variables: Cornus.mas_4 <dbl>, Lonicera.xylosteum_4 <dbl>,
# Galium.sylvaticum_6 <dbl>, Carex.digitata_6 <dbl>, Melica.nutans_6 <dbl>,
# Polygonatum.odoratum_6 <dbl>, Geum.urbanum_6 <dbl>,
# Anemone.species_6 <dbl>, Viola.mirabilis_6 <dbl>,
# Hieracium.murorum_6 <dbl>, Platanthera.bifolia_6 <dbl>,
# Convallaria.majalis_6 <dbl>, Hepatica.nobilis_6 <dbl>, …There are different combinations of species in each plot, with some species present and others absent. Since we changed the format, all species, even those not occurring at that particular site or plot, must have some values. In long format, abundance or other information is not stored for absent species, so they are assigned NAs. Therefore, one additional step is to fill the empty cells with zeros using values_fill. In this case, we can do that because we know that if the species were absent, its abundance was exactly 0. I will assign the results to a new object called spe_wide.
spe %>%
rename(PlotID = RELEVE_NR) %>%
mutate(Species = str_replace_all(Species, " ", ".")) %>%
unite("SpeciesLayer", Species, Layer, na.rm = TRUE, remove = TRUE) %>%
pivot_wider(names_from = SpeciesLayer, values_from = CoverPerc,
values_fill = 0) -> spe_widespe_wide %>% print()# A tibble: 65 × 370
PlotID Quercus.petraea.agg._1 Acer.campestre_1 Crataegus.laevigata_4
<dbl> <dbl> <dbl> <dbl>
1 1 63 18 2
2 2 63 0 0
3 3 63 0 0
4 4 38 0 0
5 5 63 0 0
6 6 38 0 0
7 7 63 0 0
8 8 38 0 0
9 9 38 0 0
10 10 63 0 0
# ℹ 55 more rows
# ℹ 366 more variables: Cornus.mas_4 <dbl>, Lonicera.xylosteum_4 <dbl>,
# Galium.sylvaticum_6 <dbl>, Carex.digitata_6 <dbl>, Melica.nutans_6 <dbl>,
# Polygonatum.odoratum_6 <dbl>, Geum.urbanum_6 <dbl>,
# Anemone.species_6 <dbl>, Viola.mirabilis_6 <dbl>,
# Hieracium.murorum_6 <dbl>, Platanthera.bifolia_6 <dbl>,
# Convallaria.majalis_6 <dbl>, Hepatica.nobilis_6 <dbl>, …4.3 From wide to long
Sometimes, it is necessary to transform data from wide to long format. Here, we need to say which column should not be changed, which is the PlotID (cols = -PlotID). Alternatively, -1 would also work here. It means skip the first column from pivoting and take all the other columns. Since this argument always goes first, you can write it even without “cols” e.g. …pivot_longer(-1, names_to = ‘species’, values_to = ‘cover’) .
Then we specify what to do with the column names, how they should be saved (names_to). And how to call the column with values (values_to).
spe_wide %>%
pivot_longer(cols = -PlotID, names_to = 'species', values_to = 'cover')# A tibble: 23,985 × 3
PlotID species cover
<dbl> <chr> <dbl>
1 1 Quercus.petraea.agg._1 63
2 1 Acer.campestre_1 18
3 1 Crataegus.laevigata_4 2
4 1 Cornus.mas_4 8
5 1 Lonicera.xylosteum_4 3
6 1 Galium.sylvaticum_6 3
7 1 Carex.digitata_6 3
8 1 Melica.nutans_6 2
9 1 Polygonatum.odoratum_6 2
10 1 Geum.urbanum_6 2
# ℹ 23,975 more rowsWe need to remove the empty rows. Since we previously filled them with zeros, we will first complete the transformation and then filter out the rows with a cover equal to zero.
spe_wide %>%
pivot_longer(cols = -PlotID, names_to = 'species', values_to = 'cover') %>%
filter(!cover == 0)# A tibble: 2,277 × 3
PlotID species cover
<dbl> <chr> <dbl>
1 1 Quercus.petraea.agg._1 63
2 1 Acer.campestre_1 18
3 1 Crataegus.laevigata_4 2
4 1 Cornus.mas_4 8
5 1 Lonicera.xylosteum_4 3
6 1 Galium.sylvaticum_6 3
7 1 Carex.digitata_6 3
8 1 Melica.nutans_6 2
9 1 Polygonatum.odoratum_6 2
10 1 Geum.urbanum_6 2
# ℹ 2,267 more rowsSometimes, we have data with NAs (not zeros). Here, it is very useful to use the argument values_drop_na= TRUE.
spe_wide %>%
pivot_longer(cols = -PlotID, names_to = 'species', values_to = 'cover', values_drop_na = TRUE)# A tibble: 23,985 × 3
PlotID species cover
<dbl> <chr> <dbl>
1 1 Quercus.petraea.agg._1 63
2 1 Acer.campestre_1 18
3 1 Crataegus.laevigata_4 2
4 1 Cornus.mas_4 8
5 1 Lonicera.xylosteum_4 3
6 1 Galium.sylvaticum_6 3
7 1 Carex.digitata_6 3
8 1 Melica.nutans_6 2
9 1 Polygonatum.odoratum_6 2
10 1 Geum.urbanum_6 2
# ℹ 23,975 more rowsSometimes you get the data in wide format but want to continue with analyses in long. And for that you want to separate the information about layer to another column.
spe_wide %>%
pivot_longer(cols = -PlotID,
names_to = 'species_layer', values_to = 'cover') %>%
separate_wider_delim(species_layer, "_", names = c("species", "layer"))# A tibble: 23,985 × 4
PlotID species layer cover
<dbl> <chr> <chr> <dbl>
1 1 Quercus.petraea.agg. 1 63
2 1 Acer.campestre 1 18
3 1 Crataegus.laevigata 4 2
4 1 Cornus.mas 4 8
5 1 Lonicera.xylosteum 4 3
6 1 Galium.sylvaticum 6 3
7 1 Carex.digitata 6 3
8 1 Melica.nutans 6 2
9 1 Polygonatum.odoratum 6 2
10 1 Geum.urbanum 6 2
# ℹ 23,975 more rows*When you check the result, you can also want to change the species name from the condensed form to a normal one e.g. Abies.alba to Abies alba. Do you know how to do it? Since we used “.” as a separator, we need to be careful with parts of the string like “agg.” where the dot has a meaning and should stay. We can play with the regex to specify that only the dots followed by some word should be replaced.
spe_wide %>%
pivot_longer(cols = -PlotID,
names_to = 'species_layer', values_to = 'cover') %>%
separate_wider_delim(species_layer, "_", names = c("species", "layer")) %>%
mutate(species_new =str_replace_all(species, "\\.(?=\\w)", " "))# A tibble: 23,985 × 5
PlotID species layer cover species_new
<dbl> <chr> <chr> <dbl> <chr>
1 1 Quercus.petraea.agg. 1 63 Quercus petraea agg.
2 1 Acer.campestre 1 18 Acer campestre
3 1 Crataegus.laevigata 4 2 Crataegus laevigata
4 1 Cornus.mas 4 8 Cornus mas
5 1 Lonicera.xylosteum 4 3 Lonicera xylosteum
6 1 Galium.sylvaticum 6 3 Galium sylvaticum
7 1 Carex.digitata 6 3 Carex digitata
8 1 Melica.nutans 6 2 Melica nutans
9 1 Polygonatum.odoratum 6 2 Polygonatum odoratum
10 1 Geum.urbanum 6 2 Geum urbanum
# ℹ 23,975 more rows4.4 group_by(), count()
Using the group_by() function, we can define how the data should be arranged into groups. Then we can proceed with asking questions about these groups. The basic function of summarising information is to count the number of rows.
In the first example, we are working with a species list in a long format. We can easily calculate the number of species in each plot/sample by counting the corresponding number of rows. First, you have to specify what the grouping variable is. In this case, it would be PlotID. Then you can directly count.
spe %>%
rename(PlotID = RELEVE_NR) %>%
group_by(PlotID) %>%
count()# A tibble: 65 × 2
# Groups: PlotID [65]
PlotID n
<dbl> <int>
1 1 44
2 2 17
3 3 16
4 4 22
5 5 15
6 6 22
7 7 23
8 8 24
9 9 28
10 10 19
# ℹ 55 more rowscount() returns the number of rows in a new variable called n. You can directly put an extra line in your code and rename it. e.g. rename(SpeciesRichness = n) or you can add an argument name= to say how it should be named in the count.
spe %>%
group_by(PlotID=RELEVE_NR) %>%
count(name = "richness")# A tibble: 65 × 2
# Groups: PlotID [65]
PlotID richness
<dbl> <int>
1 1 44
2 2 17
3 3 16
4 4 22
5 5 15
6 6 22
7 7 23
8 8 24
9 9 28
10 10 19
# ℹ 55 more rowsNote that in this case, we simply counted all the rows. However, we know that some woody species may be recorded in the tree vegetation layer as shrubs or as small juveniles, so we potentially calculated such species three times. We can experiment with the distinct() function and remove the layer information. Is there a difference in the resulting numbers?
spe %>%
distinct(PlotID = RELEVE_NR, Species) %>%
group_by(PlotID) %>%
count()# A tibble: 65 × 2
# Groups: PlotID [65]
PlotID n
<dbl> <int>
1 1 42
2 2 16
3 3 15
4 4 20
5 5 14
6 6 19
7 7 22
8 8 23
9 9 22
10 10 18
# ℹ 55 more rowsThe count() function can also be used in other cases. For example, we are interested in how many rows/plots/samples in the table are assigned to vegetation types. We will upload a table with descriptive characteristics for each forest plot, named env, which is a shortcut often used for an environmental data file.
env <- read_csv("data/forest_understory/Axmanova-Forest-env.csv")Rows: 65 Columns: 13
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): ForestTypeName
dbl (12): RELEVE_NR, ForestType, Biomass, pH_KCl, Canopy_E3, Radiation, Heat...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.tibble(env)# A tibble: 65 × 13
RELEVE_NR ForestType ForestTypeName Biomass pH_KCl Canopy_E3 Radiation Heat
<dbl> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 2 oak hornbeam f… 12.8 5.28 80 0.881 0.857
2 2 1 oak forest 9.9 3.24 80 0.933 0.814
3 3 1 oak forest 15.2 4.01 80 0.916 0.850
4 4 1 oak forest 16 3.76 75 0.930 0.948
5 5 1 oak forest 20.7 3.50 70 0.869 0.869
6 6 1 oak forest 46.4 3.8 65 0.918 0.883
7 7 1 oak forest 49.2 3.48 65 0.829 0.803
8 8 2 oak hornbeam f… 48.7 3.68 85 0.869 0.869
9 9 2 oak hornbeam f… 13.8 4.24 80 0.614 0.423
10 10 1 oak forest 79.1 4.00 70 0.905 0.930
# ℹ 55 more rows
# ℹ 5 more variables: TWI <dbl>, Longitude <dbl>, Latitude <dbl>,
# deg_lon <dbl>, deg_lat <dbl>And calculate the number of plots in each forest type.
env %>%
group_by(ForestTypeName) %>%
count() # A tibble: 4 × 2
# Groups: ForestTypeName [4]
ForestTypeName n
<chr> <int>
1 alluvial forest 11
2 oak forest 16
3 oak hornbeam forest 28
4 ravine forest 10What is great, we can even skip the group_by() step and directly specify what to count, which is very useful.
env %>%
count(ForestTypeName, name="number_of_plots")# A tibble: 4 × 2
ForestTypeName number_of_plots
<chr> <int>
1 alluvial forest 11
2 oak forest 16
3 oak hornbeam forest 28
4 ravine forest 10The count() function can be very useful for checking duplicate rows. You can ask to count, for example, IDs in the file where you expect a unique ID in each row and filter those results that have more occurrences than 1. In the example below, each ID was used just once, so the filter returns no rows. Such a check is especially important if you need to append additional information using join functions.
env %>%
count(RELEVE_NR) %>%
filter(n > 1)# A tibble: 0 × 2
# ℹ 2 variables: RELEVE_NR <dbl>, n <int>4.5 summarise()
Using group_by() and summarise(), we can calculate, for example, the mean values or the total sum of values within a group. See more in the further reading. Here is an example of a summarise() for environmental data:
env %>%
group_by(ForestTypeName) %>%
summarise(meanBiomass = mean(Biomass))# A tibble: 4 × 2
ForestTypeName meanBiomass
<chr> <dbl>
1 alluvial forest 148.
2 oak forest 26.7
3 oak hornbeam forest 44.4
4 ravine forest 79.1Another option is to add the grouping directly to the function - which gives us more flexibility later. See also further chapters.
env %>%
summarise(meanBiomass = mean(Biomass), .by = ForestTypeName)# A tibble: 4 × 2
ForestTypeName meanBiomass
<chr> <dbl>
1 oak hornbeam forest 44.4
2 oak forest 26.7
3 alluvial forest 148.
4 ravine forest 79.1We can also define more variables we want to summarise and more functions we want to apply. Try to find out more here. Below is an example of the iris dataset, where we calculated the minimum, mean, and maximum values for sepal length and width in different species.
glimpse(iris)Rows: 150
Columns: 5
$ Sepal.Length <dbl> 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5.0, 4.4, 4.9, 5.4, 4.…
$ Sepal.Width <dbl> 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.4, 3.4, 2.9, 3.1, 3.7, 3.…
$ Petal.Length <dbl> 1.4, 1.4, 1.3, 1.5, 1.4, 1.7, 1.4, 1.5, 1.4, 1.5, 1.5, 1.…
$ Petal.Width <dbl> 0.2, 0.2, 0.2, 0.2, 0.2, 0.4, 0.3, 0.2, 0.2, 0.1, 0.2, 0.…
$ Species <fct> setosa, setosa, setosa, setosa, setosa, setosa, setosa, s…iris %>%
group_by(Species) %>%
summarise(across(c(Sepal.Length, Sepal.Width),
list(min_value = min,
mean_value = mean,
max_value = max)))# A tibble: 3 × 7
Species Sepal.Length_min_value Sepal.Length_mean_va…¹ Sepal.Length_max_value
<fct> <dbl> <dbl> <dbl>
1 setosa 4.3 5.01 5.8
2 versicol… 4.9 5.94 7
3 virginica 4.9 6.59 7.9
# ℹ abbreviated name: ¹Sepal.Length_mean_value
# ℹ 3 more variables: Sepal.Width_min_value <dbl>,
# Sepal.Width_mean_value <dbl>, Sepal.Width_max_value <dbl>summarise() is also very useful for species data. For example, we can calculate the total abundance, approximated as cover, of all plants in the plot/sample. Later on, we can ask about the relative share of some species groups, endangered species, alien species etc. Alternatively, we can calculate the community means or community weighted means of specific traits, such as the mean height of the plants or their mean leaf area index. For this, we would have to append new information and therefore we will go into more detail in the next chapter.
spe %>%
group_by(PlotID = RELEVE_NR) %>%
summarise(totalCover = sum(CoverPerc))# A tibble: 65 × 2
PlotID totalCover
<dbl> <dbl>
1 1 164
2 2 115
3 3 101
4 4 96
5 5 109
6 6 121
7 7 147
8 8 185
9 9 128
10 10 174
# ℹ 55 more rows*Note that sometimes you might encounter situations where there are NAs in the data. For this, you will need to use the argument na.rm = TRUE, which indicates that you want to skip the NAs from counting. Otherwise, a single NA would mean that the result will also be NA. For more, see the next chapter.
4.6 *n_distinct
Another useful function is the n_distinct(). It can be used to get unique combinations of selected variables directly in summarise(). Like here, a unique number of species in each plot:
spe %>%
summarise(unique_species = n_distinct(Species), .by = RELEVE_NR)# A tibble: 65 × 2
RELEVE_NR unique_species
<dbl> <int>
1 1 42
2 2 16
3 3 15
4 4 20
5 5 14
6 6 19
7 7 22
8 8 23
9 9 22
10 10 18
# ℹ 55 more rowsOr unique pairs or combinations of selected variables, such as species + vegetation layers in which they appeared across the whole dataset.
spe %>%
group_by(Species) %>%
summarise(unique_species_layer = n_distinct(interaction(Species, Layer)))# A tibble: 316 × 2
Species unique_species_layer
<chr> <int>
1 Abies alba 1
2 Acer campestre 3
3 Acer platanoides 3
4 Acer pseudoplatanus 3
5 Achillea millefolium agg. 1
6 Aconitum lycoctonum 1
7 Actaea spicata 1
8 Adoxa moschatellina 1
9 Aegopodium podagraria 1
10 Aesculus hippocastanum 2
# ℹ 306 more rows4.7 Advanced mutate() and .by
Below are a few examples of how we used mutate() so far
env %>%
mutate(author = "Axmanova",#add one column with specified value
forestType = as.character(ForestType), #change the type of variable
biomass = Biomass * 1000, # multiply to change to mg
forestTypeName = str_replace_all(ForestTypeName, " ", "_")) %>% #change string
select(PlotID = RELEVE_NR, author, forestType, biomass, forestTypeName) # A tibble: 65 × 5
PlotID author forestType biomass forestTypeName
<dbl> <chr> <chr> <dbl> <chr>
1 1 Axmanova 2 12800 oak_hornbeam_forest
2 2 Axmanova 1 9900 oak_forest
3 3 Axmanova 1 15200 oak_forest
4 4 Axmanova 1 16000 oak_forest
5 5 Axmanova 1 20700 oak_forest
6 6 Axmanova 1 46400 oak_forest
7 7 Axmanova 1 49200 oak_forest
8 8 Axmanova 2 48700 oak_hornbeam_forest
9 9 Axmanova 2 13800 oak_hornbeam_forest
10 10 Axmanova 1 79100 oak_forest
# ℹ 55 more rowsWe also used mutate() to adjust more variables at once:
env %>% mutate(across(c(Radiation, Heat), ~ round(.x, digits = 2))) %>%
select(PlotID = RELEVE_NR, Radiation, Heat)# A tibble: 65 × 3
PlotID Radiation Heat
<dbl> <dbl> <dbl>
1 1 0.88 0.86
2 2 0.93 0.81
3 3 0.92 0.85
4 4 0.93 0.95
5 5 0.87 0.87
6 6 0.92 0.88
7 7 0.83 0.8
8 8 0.87 0.87
9 9 0.61 0.42
10 10 0.91 0.93
# ℹ 55 more rows.x refers to the entire column (vector) being transformed inside a function like mutate(across(...)) or map().
Alternatively, if you have a large table with only measured variables, you can specify which variable the mutate should not be applied to, here Species, and it will be applied to everything else. This is especially useful for changing formats, e.g., from character to numeric (using the function as.numeric()).
iris %>%
as_tibble() %>% #first change from dataframe to tibble
mutate(across(-Species,~round(.x, digits = 1)))# A tibble: 150 × 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <fct>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
7 4.6 3.4 1.4 0.3 setosa
8 5 3.4 1.5 0.2 setosa
9 4.4 2.9 1.4 0.2 setosa
10 4.9 3.1 1.5 0.1 setosa
# ℹ 140 more rowsYou can even create a list of functions which should be applied. *The relocate() function was used here to shift all the Sepal variables before the first variable (just for an easier check).
iris%>%
as_tibble() %>%
mutate(across(starts_with("Sepal"),
list(rounded = round, log = log1p))) %>%
relocate(starts_with("Sepal"), .before = 1)# A tibble: 150 × 9
Sepal.Length Sepal.Width Sepal.Length_rounded Sepal.Length_log
<dbl> <dbl> <dbl> <dbl>
1 5.1 3.5 5 1.81
2 4.9 3 5 1.77
3 4.7 3.2 5 1.74
4 4.6 3.1 5 1.72
5 5 3.6 5 1.79
6 5.4 3.9 5 1.86
7 4.6 3.4 5 1.72
8 5 3.4 5 1.79
9 4.4 2.9 4 1.69
10 4.9 3.1 5 1.77
# ℹ 140 more rows
# ℹ 5 more variables: Sepal.Width_rounded <dbl>, Sepal.Width_log <dbl>,
# Petal.Length <dbl>, Petal.Width <dbl>, Species <fct>We can further mutate a variable using the group_by() function. This can be illustrated step by step, as shown below…
env %>%
group_by(ForestType) %>%
select(PlotID = RELEVE_NR, ForestType, Biomass) %>%
mutate(meanBiomass = mean(Biomass)) %>%
mutate(comparison = (case_when(
Biomass > meanBiomass ~ "higher",
Biomass < meanBiomass ~ "lower",
TRUE ~ "equal"))) %>%
arrange(ForestType, desc(Biomass))# A tibble: 65 × 5
# Groups: ForestType [4]
PlotID ForestType Biomass meanBiomass comparison
<dbl> <dbl> <dbl> <dbl> <chr>
1 10 1 79.1 26.7 higher
2 7 1 49.2 26.7 higher
3 6 1 46.4 26.7 higher
4 44 1 34.1 26.7 higher
5 82 1 33.9 26.7 higher
6 28 1 29.7 26.7 higher
7 42 1 21.5 26.7 lower
8 5 1 20.7 26.7 lower
9 81 1 20.6 26.7 lower
10 4 1 16 26.7 lower
# ℹ 55 more rowsOr like this, where we do not use a standalone group_by() but rather include it directly in the mutate() function with the .by argument.
env %>%
select(PlotID=RELEVE_NR, ForestType, Biomass)%>%
mutate(meanBiomass = mean(Biomass, na.rm = TRUE),
comparison = case_when(
Biomass > meanBiomass ~ "higher",
Biomass < meanBiomass ~ "lower",
TRUE ~ "equal"), .by = ForestType)# A tibble: 65 × 5
PlotID ForestType Biomass meanBiomass comparison
<dbl> <dbl> <dbl> <dbl> <chr>
1 1 2 12.8 44.4 lower
2 2 1 9.9 26.7 lower
3 3 1 15.2 26.7 lower
4 4 1 16 26.7 lower
5 5 1 20.7 26.7 lower
6 6 1 46.4 26.7 higher
7 7 1 49.2 26.7 higher
8 8 2 48.7 44.4 higher
9 9 2 13.8 44.4 lower
10 10 1 79.1 26.7 higher
# ℹ 55 more rowsWe need to realise that after you use group_by(), the data stay grouped until you explicitly call ungroup(), or use a verb that drops grouping automatically by default settings (usually summarise() or count()). While summarise() removes the grouping, mutate() does not. Therefore, grouping still applies until the end of that call.
The advantage of .by is that it works only within the mutate() function and the grouping is therefore valid just for this function (no need to use ungroup).
4.8 *rowSums
*Another useful thing is knowing how to sum values across multiple columns and save the result in another column, i.e., row-wise sum. The relevant function is called rowSums(). We will examine the example of frozen fauna data availability. First, we will transform the table into a tidy format as you can see that we cannot work with the data directly.
read_xlsx("data/frozen_fauna/FrozenFauna_metadata.xlsx", sheet = 1) %>%
clean_names() %>%
print()# A tibble: 27 × 6
id code species pollen_spores macrofossils dna
<dbl> <chr> <chr> <chr> <chr> <chr>
1 1 D-M5 Mammuthus primigenius <NA> <NA> x
2 2 D-BP4 Bison priscus <NA> <NA> x
3 3 D-MP11 Mammuthus primigenius x x <NA>
4 4 SK2 Stephanorhinus kirchbergensis <NA> x <NA>
5 5 MP5 Mammuthus primigenius x x <NA>
6 6 CA2 Coelodonta antiquitatis <NA> x <NA>
7 7 MP9 Mammuthus primigenius x x x
8 8 D-MP10 Mammuthus primigenius x x <NA>
9 9 MP3 Mammuthus primigenius x x <NA>
10 10 MP4 Mammuthus primigenius x x <NA>
# ℹ 17 more rowsWe need to fill all the NAs with 0 and change the “x” value to 1. For the first step, we could use replace_na, but we will do this in the second part of the case_when, where we set what should happen to the remaining values after the previous conditions are resolved (i.e. TRUE~ 0).
read_xlsx("data/frozen_fauna/FrozenFauna_metadata.xlsx", sheet = 1) %>%
clean_names() %>%
mutate(across(c(pollen_spores, macrofossils, dna),
~ case_when(.x == "x" ~ 1,
TRUE ~ 0))) %>%
mutate(dataAvailableSum = rowSums(across(pollen_spores:dna))) %>%
print()# A tibble: 27 × 7
id code species pollen_spores macrofossils dna dataAvailableSum
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 1 D-M5 Mammuthus pri… 0 0 1 1
2 2 D-BP4 Bison priscus 0 0 1 1
3 3 D-MP11 Mammuthus pri… 1 1 0 2
4 4 SK2 Stephanorhinu… 0 1 0 1
5 5 MP5 Mammuthus pri… 1 1 0 2
6 6 CA2 Coelodonta an… 0 1 0 1
7 7 MP9 Mammuthus pri… 1 1 1 3
8 8 D-MP10 Mammuthus pri… 1 1 0 2
9 9 MP3 Mammuthus pri… 1 1 0 2
10 10 MP4 Mammuthus pri… 1 1 0 2
# ℹ 17 more rows4.9 Exercises
1. Forest Understory Data - download the data from the repository (see chapter 1 intro) This dataset is used in the chapter above; please use it to prepare your own script with remarks, copy, and train what is described above. Note that you will use species data in long format: Axmanova-Forest-spe.csv, and environmental data Axmanova-Forest-env.csv
2. Use the Spruce forest data used in the graphical example at the beginning of this chapter. Import spruce_forestWIDE.xlsx (use library readxl) >> transform wide to long format >> keep only true presences >> arrange sites so that all the species from site 1 are listed, then form site2… >> calculate species richness for each site (count of number of species). *Are there cases where one species is present in more layers and is counted more times in the species richness? How can this be changed?
3. Import also spruce_forestLONG.xlsx >> check the structure >> merge the information about species name and layer >> transform long to wide format >> fill empty values as zeros.
4. Train the transformation of wide to long with another dataset, Acidophilous grasslands, namely the species file called SW_moravia_acidgrass_species.csv. Import the data and check the structure. Then, transform the format to long and remove absences. Use the separate_wider_delim() function to separate information about the species name and layer into two variables.
5. Work with the dataset Lepidoptera, namely spe_matrix_MSvejnoha, which is the species file with counts of moths in forest steppe localities. Import the data and check the structure >> transform to long format >> summarise the number of all individuals of all species recorded at each site into variable all_individuals >> create a barplot to visualise the differences (ggplot2, geom_col). Tip: use aes(x = reorder(site, -all_individuals)
*For comparison, you can calculate the number of species (unique names) and visualise this as well.
6. Use Forest understory environmental data Axmanova-Forest-env.xlsx> prepare a new variable “forest_type” which will combine Forest type code and name (use unite function) >> calculate mean values of biomass for these forest_types (group_by, summarise) >> prepare a graph with boxplots of biomass in the forest_types (ggplot2, geom_boxplot). *You can also play more with the data, calculate min, mean and max values for biomass and soil pH at once.
7. We will use the iris dataset integrated in R. Use glimpse(iris) to check the structure > if needed, change the format to tibble using the as_tibble() function (iris.data <- iris %>% as_tibble()) >> calculate median values for selected parameters within different iris species.
8*. We will now check the examples of messy_data again. “Tidy datasets are all alike, but every messy dataset is messy in its own way.” Hadley Wickham
>>Import Example4 >>try to find duplicate rows and remove them.
9. * Import Example2 from the messy data and try to make the data tidy with the use of the separate_wider_delim() function.
4.10 Further reading
Summarise: https://dplyr.tidyverse.org/reference/summarise.html
Summarise multiple columns: https://dplyr.tidyverse.org/reference/summarise_all.html
Data transformation in R for Data Science: https://r4ds.hadley.nz/data-transform.html
Data tidying, including pivoting: https://r4ds.hadley.nz/data-tidy.html