tidyr::spread but keep original data

Question

data:

test = data.frame(case=c("cyl","eng","mon"),
worst=c(1,0,1),money=c(123,42,13),girl=c("no","yes","no"))

desired_test= data.frame(case=c("cyl","eng","mon"), worst=c(1,0,1),money=c(123,42,13),girl=c("no","yes","no"),
                       worst_cyl=c(1,0,0),worst_eng=c(0,0,0),worst_mon=c(0,0,1))

Attempt:

test %>% group_by(case) %>% spread(case,worst,fill=0,sep="_")

Why is not my attempt working?

At first glance, your desired_test does not seem to make a lot of sense. What are you trying to achieve? In your desired result, is row 2 really about case eng? What is the meaning of "worst_cyl" being 0 on row 2? — asachet
@antoine-sac: It is just the original data + 3 new columns with name of "worst" + values in "case", and filled with values in column called worst, i.e. column case is transposed and filled with values in worst, and name is values of "worst_"+case — Helen
I get what you want to do, I am wondering why you want to do it, i.e. what you are trying to achieve. It may well make perfect sense for you to do that operation, but I wanted to double check because the meaning of your result is unclear to me. For example, what is the meaning of "worst_cyl" being 0 on row 2? What is the added value of the table? — asachet
@antoine-sac: Ah, sorry! Well, I want to aggregate my data, but still be able to see the information that lies in the columns I want to transpose :) — Helen

Ritchie Sacramento Ritchie Sacramento · Accepted Answer · 2019-06-06T09:58:25

I'm assuming this makes more sense in the context of a larger dataset but you can achieve the desired result by adding rowid_to_column and binding the key/value columns used in spread:

library(tidyr)
library(dplyr)

test %>%
  rowid_to_column() %>%
  spread(case,worst,fill=0,sep="_") %>%
  cbind(test[c("case", "worst")], .) %>%
  rename_at(vars(starts_with("case_")), ~sub("case", "worst", .x)) %>%
  select(-rowid)

  case worst money girl worst_cyl worst_eng worst_mon
1  cyl     1   123   no         1         0         0
2  eng     0    42  yes         0         0         0
3  mon     1    13   no         0         0         1

tidyr::spread but keep original data

1 Answers