Transpose some column to create a matrix

[Hersh]

Hello,
I would like to try to transpose certain columns while keeping basic columns.
Here is my base dataset and the following table is what I would like to do.
My final goal is to be able to create matrices of species according to type from the main dataset (to avoid going to do it on excel and ending up with lots of different cvs)

df <- tribble(
  ~zone, ~type, ~station, ~species, ~number,
  'A1', 'Adult', 1, 'Atlanticus', 2,
  'A1', 'Adult', 1, 'Olrikii', 1,
  'A1', 'Larvae', 2, 'Medius', 5,
  'A2', 'Larvae', 1, 'Glacialis', 7,
  'A2', 'Larvae', 2, 'Unidentified', 3, 
  'A2', 'Adult', 2, 'Glacialis', 2, 
  'A2', 'Larvae', 2, 'Medius', 4, 
  'A3', 'Zoo', 1, 'Capilatta', 17,
  'A3', 'Adult', 3, 'Olrikii', 1
)

^{Created on 2022-05-09 by the reprex package (v2.0.1)}

What I would like to get :

Zone	station	type	Atlanticus	Capilatta	Glacialis	Medius	Olrikii	Unidentified	Total
A1	1	Adult	2	0	0	0	1	0	3
A1	2	Larvae	0	0	0	5	0	0	5
A2	1	Larvae	0	0	7	0	0	0	7
A2	2	Adult	0	0	2	0	0	0	2
A2	2	Larvae	0	0	0	4	0	3	7
A3	1	Zoo	0	17	0	0	0	0	17
A3	3	Adult	0	0	0	0	1	0	1

Thank you very much !
Hersh

[nirgrahamuk]

I recommend you use this approach. Examples are towards the bottom of the page.

Pivot data from long to wide — pivot_wider

[FJCC]

Here is an example of doing what you want with pivot_wider.

``` r
library(tidyr)
df <- tribble(
  ~zone, ~type, ~station, ~species, ~number,
  'A1', 'Adult', 1, 'Atlanticus', 2,
  'A1', 'Adult', 1, 'Olrikii', 1,
  'A1', 'Larvae', 2, 'Medius', 5,
  'A2', 'Larvae', 1, 'Glacialis', 7,
  'A2', 'Larvae', 2, 'Unidentified', 3, 
  'A2', 'Adult', 2, 'Glacialis', 2, 
  'A2', 'Larvae', 2, 'Medius', 4, 
  'A3', 'Zoo', 1, 'Capilatta', 17,
  'A3', 'Adult', 3, 'Olrikii', 1
)
dfWide <- df |> pivot_wider(names_from = "species",values_from = "number",values_fill = 0)
dfWide
#> # A tibble: 7 x 9
#>   zone  type   station Atlanticus Olrikii Medius Glacialis Unidentified Capilatta
#>   <chr> <chr>    <dbl>      <dbl>   <dbl>  <dbl>     <dbl>        <dbl>     <dbl>
#> 1 A1    Adult        1          2       1      0         0            0         0
#> 2 A1    Larvae       2          0       0      5         0            0         0
#> 3 A2    Larvae       1          0       0      0         7            0         0
#> 4 A2    Larvae       2          0       0      4         0            3         0
#> 5 A2    Adult        2          0       0      0         2            0         0
#> 6 A3    Zoo          1          0       0      0         0            0        17
#> 7 A3    Adult        3          0       1      0         0            0         0

^{Created on 2022-05-09 by the reprex package (v2.0.1)}

[Hersh]

Thanks for respone !
It's what I wanted
How can I do to have a column with the total number for each line?

[FJCC]

This code will append a column with the total of each row.

library(dplyr)
dfWide <- dfWide |> rowwise() |> 
  mutate(Total = sum(c_across(Atlanticus:Capilatta)))