Stat Stat - 3 months ago 12
R Question

Fill the same value for each group in R

I have data frame

ID <- c(1,1,2,2,2,3,3)
x <- c("1st","","1st","1st","","","")
y <- c("2nd","2nd","","","","2nd","2nd")
z <- c("","","3rd","3rd","","","3rd")
df <- data.frame(ID,x,y,z)
df
ID x y z
1 1 1st 2nd
2 1 2nd
3 2 1st 3rd
4 2 1st 3rd
5 2
6 3 2nd
7 3 2nd 3rd


I want to fill the same value by ID, the output

ID x y z x1 y1 z1
1 1 1st 2nd 1st 2nd
2 1 2nd 1st 2nd
3 2 1st 3rd 1st 3rd
4 2 1st 3rd 1st 3rd
5 2 1st 3rd
6 3 2nd 2nd 3rd
7 3 2nd 3rd 2nd 3rd


If the ID 1 have 1st, new variable x1 will have all of "1st" for ID1, and so on
Update data if I have more variables, but I need to use only x,y,z

ID <- c(1,1,2,2,2,3,3)
x <- c("1st","","1st","1st","","","")
y <- c("2nd","2nd","","","","2nd","2nd")
z <- c("","","3rd","3rd","","","3rd")
m <- c(10:16)
n <- c(20:26)
df <- data.frame(ID,x,y,z,m,n)

Answer

We can use dplyr

library(dplyr)
df %>% 
     group_by(ID) %>%
     mutate_each(funs((.[.!=""][1]))) %>%
     setNames(., c("ID", paste0(names(df)[-1], 1))) %>%
     select(-ID) %>% 
     bind_cols(df, .)
#ID   x   y   z ID   x1   y1   z1
#1  1 1st 2nd      1  1st  2nd <NA>
#2  1     2nd      1  1st  2nd <NA>
#3  2 1st     3rd  2  1st <NA>  3rd
#4  2 1st     3rd  2  1st <NA>  3rd
#5  2              2  1st <NA>  3rd
#6  3     2nd      3 <NA>  2nd  3rd
#7  3     2nd 3rd  3 <NA>  2nd  3rd
Comments