change data into different format in R-CodePudding

I have a data like this:

df = data.frame(Variable=rep(c("f10","f21","f32"),each=5), 
                Year=rep(paste("20",11:15,sep=""),3),             
        V1=sample(1:5,15,replace=T),V2=sample(5:10,15,replace=T),V3=sample(5:10,15,replace=T))

   Variable Year V1 V2 V3
1       f10 2011  3  7 10
2       f10 2012  2  5  7
3       f10 2013  5  7  5
4       f10 2014  3  8  7
5       f10 2015  3  6  5
6       f21 2011  5  5  8
7       f21 2012  3  6  5
8       f21 2013  1  5  5
9       f21 2014  5  5  5
10      f21 2015  4  9  6
11      f32 2011  5 10  9
12      f32 2012  3  7  8
13      f32 2013  3  7  7
14      f32 2014  2  5  8
15      f32 2015  5  7  9

I want to transform Variable column into columns and take V1,V2,V3 as rows The output should look like this:

  Year County f10 f21 f32
1 2011     V1   4   3   2
2 2011     V2   9  10   7
3 2011     V3   5   6   8
4 2012     V1   4   3   3
5 2012     V2   9   9   7
6 2012     V3   5   9   5
7 2013     V1   5   2   2
8 2013     V2   5   8   8
9 2013     V3  10   7  10

I tried with pivot_wider : df%>% pivot_wider(names_from = Variable, values_from = -c(1,2)) But not getting the desired output!

Any help will be great!

CodePudding user response：

Using data.table -

library(data.table)

dcast(melt(setDT(df), id.vars = c('Variable', 'Year')), 
       Year   variable~Variable, value.var = 'value')

#    Year variable f10 f21 f32
# 1: 2011       V1   2   4   3
# 2: 2011       V2   9   6   5
# 3: 2011       V3   9  10   8
# 4: 2012       V1   5   3   1
# 5: 2012       V2   5   5   8
# 6: 2012       V3   5   9  10
# 7: 2013       V1   4   4   4
# 8: 2013       V2   9   5   7
# 9: 2013       V3   6   8   5
#10: 2014       V1   2   1   1
#11: 2014       V2   7   9  10
#12: 2014       V3  10   7  10
#13: 2015       V1   5   4   3
#14: 2015       V2  10  10   7
#15: 2015       V3   5   5   8

CodePudding user response：

Another way is to use dcast function from reshape2 package:

reshape2::recast(df, Year variable~Variable)

   Year variable f10 f21 f32
1  2011       V1   5   2   3
2  2011       V2   8   7   5
3  2011       V3   8   9   8
4  2012       V1   4   3   2
5  2012       V2   5  10   9
6  2012       V3   5   8   6
7  2013       V1   3   5   5
8  2013       V2  10   6   7
9  2013       V3   9   8   6
10 2014       V1   5   4   5
11 2014       V2   6   9   7
12 2014       V3   6   5   9
13 2015       V1   3   4   1
14 2015       V2   8   5   6
15 2015       V3   9   7   5