Spliting rows into multiple rows and adding NA when needed

user_11279262 0
Viewed 18

I have the following dataframe:

A      B       C       D       E       F       G      
1/2    3/4     4/5/6   7/8     9/10    11      12/13/14/15

And want to split to:

A      B       C       D       E       F       G      
1      3       4       7       9       11      12
2      4       5       8       10      NA      13   
NA     NA      6       NA      NA      NA      14
NA     NA      NA      NA      NA      NA      15

Is there any compact way to do it?

I've tought about separating each column into a list, using something such as

 list_of_dfs

and for each df do:

 modified_dfs %>% separate_rows(colnames(each_df), sep = "/")

then doing a merge of all dataframes created in the process...

 merge(modified_dfs)
dataframe r split
1 Answers

It is more easier with cSplit

library(splitstackshape)
cSplit(df1, names(df1), sep = "/", "long")

-output

     A     B     C     D     E     F     G
   <int> <int> <int> <int> <int> <int> <int>
1:     1     3     4     7     9    11    12
2:     2     4     5     8    10    NA    13
3:    NA    NA     6    NA    NA    NA    14
4:    NA    NA    NA    NA    NA    NA    15

data

df1 <- structure(list(A = "1/2", B = "3/4", C = "4/5/6", D = "7/8", 
    E = "9/10", F = 11L, G = "12/13/14/15"), class = "data.frame", 
row.names = c(NA, 
-1L))
user_37322710
Related