How can I transpose a data frame in R so that a certain column becomes column names and another column fills the values?

I have a data set whereby I want to transpose the entries in a certain column as column names and then populate those columns with the corresponding entreis from the original dataframe.

My data frame looks as follows:

I want to transpose it so that I have the species as column names and the weight of each species per vessel and trip. And it also adds the values per species when it occurs across the same vessel and trip.

I have tried a couple of tidyverse, tidyr, dplyr options that I found online, but all results are telling me that these packages are built under previous versions of R and therfore not working. I've also used the spread function under tidyverse but it does not add the values like I want it to.

The version of R I am using is:
RStudio 2023.03.1+446 "Cherry Blossom"
Release (6e31ffc3ef2a1f81d377eeccab71ddc11cfbd29e, 2023-05-09) for windows
Mozilla/5.0 (Windows NT 10.0; Win64; x64)
AppleWebKit/537.36 (KHTML, like Gecko)
RStudio/2023.03.1+446
Chrome/108.0.5359.179
Electron/22.0.3
Safari/537.36

Thanks,

You'll need to use values_fn = sum and values_fill = 0 to sum the corresponding values and set missing entries to zero.

Also note the spread function was superseded by pivot_wider in the tidyr package.

library(tidyr)

pivot_wider(df, names_from = Species, values_from = Weight, values_fn = sum, values_fill = 0)

# A tibble: 8 × 6
   Trip Vessel   BET   YFT   SKJ   ALB
  <int> <chr>  <int> <int> <int> <int>
1     1 A         77     0     0     0
2     2 B          0    65    76     0
3     1 D          0     0    12     0
4     3 B          0    56     0     0
5     1 C          0     0     0    98
6     4 B          0    35     0     0
7     3 D         78     0     0     0
8     2 C          0     0     0    76

Maybe you should keep your both the R version and the related packages, e.g., dplyr, tidyr and tidyverse, update to date. Otherwise, you can try reshape from base R if you don't want any dependency to additional packages.

dfout <- reshape(
    aggregate(Weight ~ ., df, sum),
    direction = "wide",
    idvar = c("Trip", "Vessel"),
    timevar = "Species"
)


dfout[is.na(dfout)] <- 0

which gives

> dfout
  Trip Vessel Weight.ALB Weight.BET Weight.SKJ Weight.YFT
1    1      C         98          0          0          0
2    2      C         76          0          0          0
3    1      A          0         77          0          0
4    3      D          0         78          0          0
5    2      B          0          0         76         65
6    1      D          0          0         12          0
8    3      B          0          0          0         56
9    4      B          0          0          0         35