spread function using symbols in dataframe

Question

I have a very simple dataset with two variables.

data <- data.frame(
             ID = c("A","A","B","C","D","D"),
             Service = c("Shop","Online","Shop","Online","Online","Shop"))

I want to use the spread() function on Service, but rather than spread by ID, I want the resulting table to include a "Y" symbol to indicate that ID operates that specific service. For example:

  ID       Shop    Online
  A         Y         Y
  B         Y         - 
  C         -         Y
  D         Y         Y

However, the spread() function won't work by just giving a key, so is there a way I can do this using the existing spread function or do I have to use something different?

markus · Accepted Answer · 2018-10-29T09:25:35.783

2

You would need to create a new column first

library(tidyr)
library(dplyr)
data %>% 
  mutate(spread_col = "Y") %>% 
  spread(Service, spread_col, fill = "-")
#  ID Online Shop
#1  A      Y    Y
#2  B      -    Y
#3  C      Y    -
#4  D      Y    Y

(This might be a duplicate of How to reshape data from long to wide format?)

You could also use dcast from data.table or reshape2:

reshape2::dcast(
  data,
  ID ~ Service,
  fun.aggregate = function(x) replace(x, x == x, "Y"),
  fill = "-"
)

edited Oct 29 '18 at 09:25

answered Oct 29 '18 at 09:05

markus

25,843
5
39
58

Sorry just one problem I'm finding now: I noticed I still have duplicate rows in my dataset, e.g. there are two rows for ID "A" and they are not merged. I keep getting Error: Duplicate identifiers for rows. Any ideas? – Pryore Oct 29 '18 at 09:46
1

No worries, I've just figured out that my main dataset had a couple of exact-match rows. Thank you for your help anyway :) – Pryore Oct 29 '18 at 09:59

score 0 · Answer 2 · answered Oct 29 '18 at 10:22

You could do this with base.

data: (use a factor variable)

data <- data.frame(
    ID = c("A","A","B","C","D","D"),
    Service = factor(c("Shop","Online","Shop","Online","Online","Shop")), levels = c("Online","Shop"))

code:

ans<-
do.call(
    rbind, tapply(data$Service, data$ID, table)
)

ans[ans == 1] = "Y"
ans[ans == 0] = "-"

result:

#> ans
#  Online Shop
#A "Y"    "Y" 
#B "-"    "Y" 
#C "Y"    "-" 
#D "Y"    "Y" 
#>

spread function using symbols in dataframe

2 Answers2