Bringing long to wide data in R without getting missing values

Question

Hello I am trying to convert data from Long Format into wide Format with the spread() function. Actually it works, however it gives me half of the dataframe with NA's for each of the transformed columns.

A minimal example of my dataframe:

            Trial <- c(1, 2, 3, 4, 5,  6, 1, 2, 3, 4, 5, 6)
        Period <- c(4, 5, 6, 4, 5, 6)
        variable <- c("SpotRate", "SpotSpread")
        value <- c(0.5, 0.4, 0.2, 0.3, 0.6)

df <- cbind(Trial,Period,variable,value)
    Wide_data <- spread(df, variable, value, convert=TRUE)

I would like to transform variable into two columns "SpotRate" and "SpotSpread" and the corresponding values. This works, however as each value of the other variables is in there twice (Trial, Period etc), half of the SpotRate and SpotSpread column are filled with NA. How can I tell R that it should merge all the duplicate values to one once I transform it into wide data?

Thank you! :)

Answer 1

Maybe you are looking for this:

library(tidyverse)
#Data
Trial <- c(1, 2, 3, 4, 5,  6, 1, 2, 3, 4, 5, 6)
Period <- c(4, 5, 6, 4, 5, 6)
variable <- c(rep("SpotRate",6),rep("SpotSpread",6))
value <- c(0.5, 0.4, 0.2, 0.3, 0.6)
df <- as.data.frame(cbind(Trial,Period,variable,value),stringsAsFactors = F)
#Code
df %>% 
  arrange(Trial) %>%
  group_by(Trial) %>%
  pivot_wider(names_from = variable,values_from=value) %>% ungroup()

Output:

# A tibble: 6 x 4
  Trial Period SpotRate SpotSpread
  <chr> <chr>  <chr>    <chr>     
1 1     4      0.5      0.4       
2 2     5      0.4      0.2       
3 3     6      0.2      0.3       
4 4     4      0.3      0.6       
5 5     5      0.6      0.5       
6 6     6      0.5      0.4