dataframe - Spread record based on value in R

Question

i have a problem with spreading the data only based on ID suppose that i have this data frame

DF <- data.frame(ID=rep(c("1","2","3","1","2","1"), each=1),
             m=c(7,7,7,8,8,8),
             n=c(8,9,10,9,10,11),
             o=c(121,122,123,121,122,122),
             p=c(23,24,25,25,25,25)
             )

then, i want to spread it based on ID. Here is the output that i expect

DFR <- data.frame(ID=rep(c("1","2","3"), each=1),
                m1=c(7,7,7),
                n1=c(8,9,10),
                o1=c(121,122,123),
                p1=c(23,24,25),
                m2=c(8,8,0),
                n2=c(9,10,0),
                o2=c(121,122,0),
                p2=c(25,25,0),
                m3=c(8,0,0),
                n3=c(11,0,0),
                o3=c(122,0,0),
                p3=c(25,0,0)
)

i have tried some codes but mostly failed such as

DF %>% fill(-ID) %>% 
        spread(key=m:p, value=m:p, sep="", fill=""); 

chop(DF, ID) %>%
     spread(DF, m:p) %>%
     unnest(c(m:p));

DF %>% group_by(ID) %>%
       select(ID) %>%
       mutate(row = paste0("m","n","o","p",row_number())) %>%
       spread(row, m,n,o,p);

Any solutions would be appreciated. Thanks in advance

question from:https://stackoverflow.com/questions/65839126/spread-record-based-on-value-in-r

Answer 1

You can use :

library(dplyr)
library(tidyr)

DF %>%
  group_by(ID) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = row, values_from = m:p, values_fill = 0)

#   ID     m_1   m_2   m_3   n_1   n_2   n_3   o_1   o_2   o_3   p_1   p_2   p_3
#  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 1         7     8     8     8     9    11   121   121   122    23    25    25
#2 2         7     8     0     9    10     0   122   122     0    24    25     0
#3 3         7     0     0    10     0     0   123     0     0    25     0     0