R Make table out of three columns

Question

Hopefully I can explain my question well enough; So, I have a data frame like this:

sample = data.frame("Room" = c("A1", "B2","A1","A3","A2"), "Name"=c("Peter","Tom","Peter","Anna","Peter"), "Class"=c("E","E","F","D","E"), "FY"=c(1,2,3,4,6))

Now I would like to create a new data frame, which has only the unique values of column "Class" as column names and the columns "Room" and "Name" as columns. The values should then be sums of "FY".

If there wasn´t the column "Room", I would do this:

test=as.data.frame(unclass(with(sample, tapply(FY, list(Name, Class), FUN=sum))))

But how can I do this with two columns?

This is my desired output:

output = data.frame(c("A1", "B2","A3", "A2"), c("Peter","Tom","Anna","Peter"), c(1,2,NA,6),c(3,NA,NA,NA),c(NA,NA,4,NA))
colnames(output) = c("Room", "Name","E","F","D")

score 2 · Accepted Answer · answered Jan 12 '18 at 14:59

2

A simple dcast with reshape2 should do the trick:

library(reshape2)
dcast(sample, Room + Name ~ Class, value.var = "FY")

#  Room  Name  D  E  F
#1   A1 Peter NA  1  3
#2   A2 Peter NA  6 NA
#3   A3  Anna  4 NA NA
#4   B2   Tom NA  2 NA

answered Jan 12 '18 at 14:59

Mike H.

13,960
2
29
39

score 1 · Answer 2 · answered Jan 12 '18 at 14:55

In this specific example, I believe you can get away with the following:

library(tidyverse)

sample %>%
  spread(Class, FY)

However, based on your description, I think this should cover a broader example:

sample %>%
  group_by(Room, Name, Class) %>% 
  summarise(FY = sum(FY)) %>% 
  spread(Class, FY)

score 0 · Answer 3 · answered Jan 12 '18 at 15:03

Using reshape:

reshape(sample, idvar = c("Name", "Room"), timevar = "Class", direction = "wide")

Output:

    Room  Name FY.E FY.F FY.D
1   A1 Peter    1    3   NA
2   B2   Tom    2   NA   NA
4   A3  Anna   NA   NA    4
5   A2 Peter    6   NA   NA

Column names can be changed later on if you want.

R Make table out of three columns

3 Answers3