Split a column value into Multiple columns based on an ID in R

Question

I have a dataset that has different timepoints for each ID. I want one record per ID where I want the timepoints to be split in different columns . I dont want to use spread as I want the actual value of the column. Some of the IDs have 14 records per ID and I want 14 them split across 14 columns. How can I achieve this in R ?

sample data

ID	Timepoint	Value
A	1	yes
A	2	yes
A	3	yes
A	4	yes
B	7	yes
B	11	yes
C	4	yes
C	5	yes
D	7	yes

ID	Timepoint1	Timepoint2	Timepoint3	Timepoint4	Value
A	1	2	3	4	yes
B	7	11			yes
C	4	5			yes
D	7				yes

score 4 · Answer 1 · answered Jul 12 '21 at 17:09

We may use dcast

library(data.table)
dcast(setDT(df1), ID + Value ~ paste0("Timepoint",
       rowid(ID)), value.var = 'Timepoint')

-ouptut

  ID Value Timepoint1 Timepoint2 Timepoint3 Timepoint4
1:  A   yes          1          2          3          4
2:  B   yes          7         11         NA         NA
3:  C   yes          4          5         NA         NA
4:  D   yes          7         NA         NA         NA

data

df1 <- structure(list(ID = c("A", "A", "A", "A", "B", "B", "C", "C", 
"D"), Timepoint = c(1L, 2L, 3L, 4L, 7L, 11L, 4L, 5L, 7L), Value = c("yes", 
"yes", "yes", "yes", "yes", "yes", "yes", "yes", "yes")),
 class = "data.frame", row.names = c(NA, 
-9L))

score 3 · Accepted Answer · answered Jul 12 '21 at 17:29

tidyverse

df1 %>%
  group_by(ID) %>%
  mutate(id_rows = row_number()) %>%
  pivot_wider( 
    id_cols = c(ID, Value),
    names_from = id_rows,
    values_from = Timepoint,
    names_prefix = "Timepoint"
  ) %>% 
  ungroup()

output

# A tibble: 4 x 6
  ID    Value Timepoint1 Timepoint2 Timepoint3 Timepoint4
  <chr> <chr>      <int>      <int>      <int>      <int>
1 A     yes            1          2          3          4
2 B     yes            7         11         NA         NA
3 C     yes            4          5         NA         NA
4 D     yes            7         NA         NA         NA

score 2 · Answer 3 · answered Jul 12 '21 at 17:33

in Base R:

 reshape(transform(df1, time = ave(ID, ID, FUN = seq)), 
          dir = 'wide', idvar = c('ID', 'Value'), sep='')

  ID Value Timepoint1 Timepoint2 Timepoint3 Timepoint4
1  A   yes          1          2          3          4
5  B   yes          7         11         NA         NA
7  C   yes          4          5         NA         NA
9  D   yes          7         NA         NA         NA

score 1 · Answer 4 · answered Jul 12 '21 at 18:02

Not the finest way, but it works: combination of splitstackshape and data.table. The other solution are already presented:

library(splitstackshape)
library(data.table)

df <- dcast(getanID(df1, 'ID'), ID~.id, value.var='Timepoint')
colnames(df)[2:5] <- paste("Timepoint", colnames(df)[2:5], sep = "")

Output:

   ID Timepoint1 Timepoint2 Timepoint3 Timepoint4
1:  A          1          2          3          4
2:  B          7         11         NA         NA
3:  C          4          5         NA         NA
4:  D          7         NA         NA         NA

Split a column value into Multiple columns based on an ID in R

4 Answers4

data