R function to cast to wide format

Question

Possibly a fairly simple question, however I have not been able to solve it so far. My goal is to redesign a data frame in R that has the following format:

var1	year	Jan	Feb	March
x1	2019	1	2	3
x2	2020	2	2	3
x1	2020	1	2	2
x2	2019	3	1	1

to wide format: In the way that it results in the following table:

year	2020	2020	2020	2019	2019	2019
	Jan	Feb	March	Jan	Feb	March
x1	1	2	2	1	2	3
x2	2	2	3	3	1	1

I am highly thankful for any advice or help!!

All elements in a df column vector have to be the same type. Jan-2020 as a column name with numeric values would work. Jan, Feb... as row values would not unless you are satisfied with all numeric values maintained as character. — SteveM, Dec 11 '20 at 00:54

score 1 · Accepted Answer · answered Dec 11 '20 at 00:57

This can be useful:

library(tidyverse)
#Code
new <- df %>% pivot_wider(names_from = year,values_from=Jan:March)

Output:

# A tibble: 2 x 7
  var1  Jan_2019 Jan_2020 Feb_2019 Feb_2020 March_2019 March_2020
  <chr>    <int>    <int>    <int>    <int>      <int>      <int>
1 x1           1        1        2        2          3          2
2 x2           3        2        1        2          1          3

Some data used:

#Data
df <- structure(list(var1 = c("x1", "x2", "x1", "x2"), year = c(2019L, 
2020L, 2020L, 2019L), Jan = c(1L, 2L, 1L, 3L), Feb = c(2L, 2L, 
2L, 1L), March = c(3L, 3L, 2L, 1L)), class = "data.frame", row.names = c(NA, 
-4L))

Wow. Thanks for your super fast response. This works just as expected. Thanks alot!!!! — JuliusS, Dec 11 '20 at 01:05

score 0 · Answer 2 · answered Dec 11 '20 at 02:43

data.table way would be :

library(data.table)
dcast(setDT(df), var1~year, value.var = c('Jan', 'Feb', 'March'))

#   var1 Jan_2019 Jan_2020 Feb_2019 Feb_2020 March_2019 March_2020
#1:   x1        1        1        2        2          3          2
#2:   x2        3        2        1        2          1          3

R function to cast to wide format

2 Answers2