How to spread one column into two based on ID

Question

I would like to spread one column of my dataframe into two columns based on an ID variable.

My data looks like this

df = data.frame(ID = c("M1", "M2", "M2", "M3", "M4", "M4", "M5"),
                Core = c("A", "A","B","B","A","B","A"))

> df
  ID Core
1 M1    A
2 M2    A
3 M2    B
4 M3    B
5 M4    A
6 M4    B
7 M5    A

I would like to make the "Core" column into two based on the "ID" so there are no duplicate IDs. I would like it to look like this:

  ID CoreA CoreB
1 M1     1     0
2 M2     1     1
3 M3     0     1
4 M4     1     1
5 M5     1     0

I'm sure there is an easy solution but I am stumped.

I'm not sure this wasn't really a request to `reshape` or `spread`. It looks more like a request for a tabulation. If KateN will edit the question to make clear whether there can ever be more than one M1 case associated with CoreA then we will know for sure. — IRTFM, May 31 '19 at 15:07

score 1 · Accepted Answer · answered May 31 '19 at 14:51

An option would be spread

library(tidyverse)
df %>%
   mutate(n = 1, Core = str_c("Core", Core)) %>% 
   spread(Core, n, fill = 0)
#   ID CoreA CoreB
#1 M1     1     0
#2 M2     1     1
#3 M3     0     1
#4 M4     1     1
#5 M5     1     0

Or with data.table

library(data.table)
dcast(setDT(df), ID ~ paste0("Core", Core), length)

How to spread one column into two based on ID

1 Answers1