0

not sure if spread() is the right function here, but I am trying to change a table that looks like the following:

ID   Name   Value
1    A      100
1    B      100
1    C      100
2    D      250
2    E      250
4    F      350

Into something like this:

ID   Value   Name_1   Name_2   Name_3   Name_4
1    100     A        B        C        NA
2    250     D        E        NA       NA
4    350     F        NA       NA       NA

Highlighting that this differs from other examples of changing long data to wide data because the name values are all inconsistent; the only thing I know is that there are at most 4 unique names per ID. I know this isn't best practices by any means (one-to-many ID matches, the null values, etc.) but the data is messy and the wide format is slightly easier to read/digest. How should I go about this? Thanks in advance

Robin
  • 11
  • 2
  • Try `df1 %>% mutate(nm = str_c('Name', rowid(ID))) %>% spread(nm, Name)` – akrun Aug 17 '22 at 17:51
  • Thank you so much @akrun for the quick response! Is the rowid function from a specific package? When I run it I get an error that it couldn't find that function. – Robin Aug 17 '22 at 18:07
  • sorry, i should have mentioned packages `library(dplyr);library(tidyr);library(data.table);library(stringr)` it is from data.table. If you don't want an extra package, do `df1 %>% group_by(ID) %>% mutate(nm = paste0('Name', row_number())) %>% spread(nm, Name)` – akrun Aug 17 '22 at 18:12
  • 1
    That worked! Really appreciate the help here – Robin Aug 17 '22 at 18:27

0 Answers0