R spread dataframe

Question

IN R language how to convert data1 into data2

data1 = fread("
id year cost pf loss
A 2019-02 155 10 41
B 2019-03 165 14 22
B 2019-01 185 34 56
C 2019-02 350 50 0
A 2019-01 310 40 99") 
    
 

data2 = fread("
id item 2019-01 2019-02 2019-03
A cost 30 155 NA
A pf 40 10 NA
A loss 99 41 NA
B cost 185 NA 160
B pf 34 NA 14
B loss 56 NA 22
C cost NA 350 NA
C pf NA 50 NA
C loss NA 0 NA")

I try to use spread、gather、dplyr、apply..... but .....

Answer 1

First get the data in long format and then get it back in wide.

library(tidyr)
data1 %>%
  pivot_longer(cols = cost:loss) %>%
  pivot_wider(names_from = year, values_from = value)

Note that gather and spread have been retired and replace by pivot_longer and pivot_wider.

Using data.table :

library(data.table)
dcast(melt(data1, c('id', 'year')), id+variable~year, value.var = 'value')

#   id variable 2019-01 2019-02 2019-03
#1:  A     cost     310     155      NA
#2:  A       pf      40      10      NA
#3:  A     loss      99      41      NA
#4:  B     cost     185      NA     165
#5:  B       pf      34      NA      14
#6:  B     loss      56      NA      22
#7:  C     cost      NA     350      NA
#8:  C       pf      NA      50      NA
#9:  C     loss      NA       0      NA