Split a column into multiple and store a 0 or 1 depending on if its present

Question

so I currently have a dataset of user_ids and medication type, I am trying to format the set so all of the medication to columns and if the user takes them it will have a 1 and if not a 0. Im not sure the best way to implement this i have tried spread() but it doesnt like the dataset due to the user_ids not being unique.

This is what I am trying to create:

#starting df 
df1 <- data.frame (user_id =c("1", "1", "2", "3","3"),
                   medication_name =c("Carbamzepine", "Clonazepam", "Lamotrigine", "Zonisamide", "Gabapentin"))

#The df im trying to make

df2 <- data.frame(user_id = c("1", "2", "3"),
                  Carbamzepine = c("1","0","0"),
                  Clonazepam = c("1", "0","0"),
                  Lamotrigine = c("0","1","0"),
                  Zonisamide = c("0", "0", "1"),
                  Gabapentin = c("0", "0", "1"))

G. Grothendieck · Accepted Answer · 2021-05-21T14:24:34.450

Use table:

table(df1)

giving:

       medication_name
user_id Carbamzepine Clonazepam Gabapentin Lamotrigine Zonisamide
      1            1          1          0           0          0
      2            0          0          0           1          0
      3            0          0          1           0          1

That gives a table object but if it is important that it be a data frame then convert it to a data frame and then move the row names to a user_id column.

library(tibble)
rownames_to_column(as.data.frame.matrix(table(df1)), "user_id")

giving:

  user_id Carbamzepine Clonazepam Gabapentin Lamotrigine Zonisamide
1       1            1          1          0           0          0
2       2            0          0          0           1          0
3       3            0          0          1           0          1

This works great! What if the user has used the mediation multiple times, how do i ensure it stays 1 and does not go to 2 or above? — Metel Stairs, May 21 '21 at 14:30
If you want to eliminate duplicates then `table(unique(df1))` — G. Grothendieck, May 21 '21 at 14:33

score 1 · Answer 2 · answered May 21 '21 at 14:20

You can use the following solution:

library(dplyr)
library(tidyr)

df %>%
  mutate(id = 1) %>%
  pivot_wider(names_from = medication_name, values_from = id, 
              values_fill = 0)


# A tibble: 3 x 6
  user_id Carbamzepine Clonazepam Lamotrigine Zonisamide Gabapentin
  <chr>          <dbl>      <dbl>       <dbl>      <dbl>      <dbl>
1 1                  1          1           0          0          0
2 2                  0          0           1          0          0
3 3                  0          0           0          1          1

Split a column into multiple and store a 0 or 1 depending on if its present

2 Answers2