just_rookie just_rookie - 3 months ago 16
R Question

R split each row of a dataframe into two rows

I would like to splite each row of a data frame(numberic) into two rows. For example, part of the original data frame like this (nrow(original datafram) > 2800000):

ID X Y Z value_1 value_2
1 3 2 6 22 54
6 11 5 9 52 71
3 7 2 5 2 34
5 10 7 1 23 47


And after spliting each row, we can get:

ID X Y Z
1 3 2 6
22 54 NA NA
6 11 5 9
52 71 NA NA
3 7 2 5
2 34 NA NA
5 10 7 1
23 47 NA NA


the "value_1" and "value_2" columns are split and each element is set to a new row. For example, value_1 = 22 and value_2 = 54 are set to a new row.

Answer

Here is one option with data.table. We convert the 'data.frame' to 'data.table' by creating a column of rownames (setDT(df1, keep.rownames = TRUE)). Subset the columns 1:5 and 1, 6, 7 in a list, rbind the list element with fill = TRUE option to return NA for corresponding columns that are not found in one of the datasets, order by the row number ('rn') and assign (:=) the row number column to 'NULL'.

library(data.table)
setDT(df1, keep.rownames = TRUE)[]
rbindlist(list(df1[, 1:5, with = FALSE], setnames(df1[, c(1, 6:7),
   with = FALSE], 2:3, c("ID", "X"))), fill = TRUE)[order(rn)][, rn:= NULL][]
#    ID  X  Y  Z
#1:  1  3  2  6
#2: 22 54 NA NA
#3:  6 11  5  9
#4: 52 71 NA NA
#5:  3  7  2  5
#6:  2 34 NA NA
#7:  5 10  7  1
#8: 23 47 NA NA

A hadleyverse corresponding to the above logic would be

library(dplyr)
tibble::rownames_to_column(df1[1:4]) %>% 
         bind_rows(., setNames(tibble::rownames_to_column(df1[5:6]), 
                         c("rowname", "ID", "X"))) %>% 
         arrange(rowname) %>% 
         select(-rowname)
#   ID  X  Y  Z
#1  1  3  2  6
#2 22 54 NA NA
#3  6 11  5  9
#4 52 71 NA NA
#5  3  7  2  5
#6  2 34 NA NA
#7  5 10  7  1
#8 23 47 NA NA

data

df1 <- structure(list(ID = c(1L, 6L, 3L, 5L), X = c(3L, 11L, 7L, 10L
), Y = c(2L, 5L, 2L, 7L), Z = c(6L, 9L, 5L, 1L), value_1 = c(22L, 
52L, 2L, 23L), value_2 = c(54L, 71L, 34L, 47L)), .Names = c("ID", 
"X", "Y", "Z", "value_1", "value_2"), class = "data.frame",
 row.names = c(NA, -4L))