How can I transform my dataset into wide format using spread function?

Question

I downloaded this data frm eurostat package in R, which is in long format:

structure(list(geo = c("DE", "EA20", "ES", "EU27_2020", "FR", 
"IT", "DE", "EA20", "ES", "EU27_2020", "FR", "IT", "DE", "EA20", 
"ES", "EU27_2020", "FR", "IT", "DE", "EA20", "ES", "EU27_2020", 
"FR", "IT", "DE", "EA20", "ES", "EU27_2020", "FR", "IT"), time = structure(c(17532, 
17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 
17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 
17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 
17532, 17532), class = "Date"), values = c(2.9, 9.1, 18.2, 8.2, 
9.3, 12.2, 3.7, 8.3, 14.9, 7.5, 9.2, 10.2, 3.3, 8.7, 16.4, 7.8, 
9.2, 11.1, 2.6, 8.2, 17.1, 7.3, 7.7, 10.7, 3.3, 7.3, 13.3, 6.6, 
7.8, 8.6)), row.names = c(NA, -30L), class = c("tbl_df", "tbl", 
"data.frame"))

I would like a table with these columns: time, geo, and values. To do so, I've tried:

taxaatur_EU_m_wide <- spread(taxaatur_EU_m, geo, values) %>% #Passar a format wide.
  select("time", "UE", "ZE", "DE", "ES", "FR", "IT") 

But it does not create any wide table.

Answer 1

Please try the pivot_wider function as below

library(tidyverse)

df %>% mutate(row=row_number(), .by = c(geo , time)) %>% pivot_wider(id_cols = c(row,time), names_from = geo, values_from = values)

^{Created on 2023-08-04 with reprex v2.0.2}

# A tibble: 5 × 8
    row time          DE  EA20    ES EU27_2020    FR    IT
  <int> <date>     <dbl> <dbl> <dbl>     <dbl> <dbl> <dbl>
1     1 2018-01-01   2.9   9.1  18.2       8.2   9.3  12.2
2     2 2018-01-01   3.7   8.3  14.9       7.5   9.2  10.2
3     3 2018-01-01   3.3   8.7  16.4       7.8   9.2  11.1
4     4 2018-01-01   2.6   8.2  17.1       7.3   7.7  10.7
5     5 2018-01-01   3.3   7.3  13.3       6.6   7.8   8.6