CODEWITHSUNDEEP
rdata.tablereshape
Sumit
Sumit

Reputation: 2250

Spread data table by id

I have the following data.table: 

> df
   month student A B  C  D
1:     1     Amy 9 6  1 11
2:     1     Bob 8 5  5  2
3:     2     Amy 7 7  2  4
4:     2     Bob 6 6  6  6
5:     3     Amy 6 8 10  7
6:     3     Bob 9 7 11  3

I want to transform this data.table to this format: > df1

    month cols Amy Bob
 1:     1    A   9   8
 2:     1    B   6   5
 3:     1    C   1   5
 4:     1    D  11   2
 5:     2    A   7   6
 6:     2    B   7   6
 7:     2    C   2   6
 8:     2    D   4   6
 9:     3    A   6   9
10:     3    B   8   7
11:     3    C  10  11
12:     3    D   7   3

I tried multiple ways using dcast etc. but I couldn't transform the data. Help please!

Upvotes: 1

Views: 135

Answers (2)

phil_t
phil_t

Reputation: 861

You have to melt the dataframe and then dcast - 

tmp = melt(df, id = c("month", "student"), , variable.name = "cols")
df1 = dcast(tmp, month + cols ~ student, value.var = "value")

Both are from the data.table library

Upvotes: 2

Jilber Urbina
Jilber Urbina

Reputation: 61164

A tidyr approach.

> library(tidyr)
> df %>%
    gather(cols, values,  A:D) %>% 
    spread(student, values)
   month cols Amy Bob
1      1    A   9   8
2      1    B   6   5
3      1    C   1   5
4      1    D  11   2
5      2    A   7   6
6      2    B   7   6
7      2    C   2   6
8      2    D   4   6
9      3    A   6   9
10     3    B   8   7
11     3    C  10  11
12     3    D   7   3

Upvotes: 1

Related Questions