Transposing data frame based on date and factor column in R-CodePudding

I have a table like that:

date	device	v1	v2
jan-22	mobile	1	11
jan-22	pc	2	12
may-22	mobile	3	13
may-22	pc	4	14

and I would like something like that:

		jan-22	may-22
mobile
	v1	1	3
	v2	11	13
pc
	v1	2	4
	v2	12	14

CodePudding user response：

ftable(xtabs(v~device time date, reshape(df, -(1:2), idvar = 1:2, sep='', dir='long')))
            date jan-22 may-22
device time                   
mobile 1              1      3
       2             11     13
pc     1              2      4
       2             12     14

or even:

ftable(xtabs(values~.,cbind(df[1:2], stack(df[3:4]))), row.vars = 2:3)

         date jan-22 may-22
device ind                   
mobile v1            1      3
       v2           11     13
pc     v1            2      4
       v2           12     14

which is:

ftable(xtabs(values~.,cbind(df[1:2], stack(df[3:4]))), row.vars = c('device', 'ind'))

or even:

ftable(xtabs(values~device ind date,cbind(df[1:2], stack(df[3:4]))))
           date jan-22 may-22
device ind                   
mobile v1            1      3
       v2           11     13
pc     v1            2      4
       v2           12     14

CodePudding user response：

Here is a tested solution for reference.

df=data.frame( date =c("jan-22", "jan-22", "may-22","may-22"),
            device=c("mobile","pc","mobile","pc"), 
             v1=c(1,12,3,4), v2=c(11,12,13,14))

library(tidyr)
a1=spread(df[,c(1,2,3)], key = date, value = v1)
a1$type='v1'
a2=spread(df[,c(1,2,4)], key = date, value = v2)
a2$type='v2'

a=rbind(a1, a2)
a=a[, c(1,4,2,3)]
a[order(a$device,a$type), ]

The answer will look like below

    device  type    jan-22  may-22
    <fct>   <chr>   <dbl>   <dbl>
1   mobile  v1  1   3
3   mobile  v2  11  13
2   pc  v1  12  4
4   pc  v2  12  14