CODEWITHSUNDEEP
r
just_rookie
just_rookie

Reputation: 893

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.

Upvotes: 2

Views: 807

Answers (3)

akrun
akrun

Reputation: 887881

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))

Upvotes: 1

prateek1592
prateek1592

Reputation: 547

This should work

data <- read.table(text= "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", header=T)

data1 <- data[,1:4]
data2 <- setdiff(data,data1)
names(data2) <- names(data1)[1:ncol(data2)]

combined <- plyr::rbind.fill(data1,data2)
n <- nrow(data1)
combined[kronecker(1:n, c(0, n), "+"),]

Though why you would need to do this beats me.

Upvotes: 0

Chris C
Chris C

Reputation: 1655

Here's a (very slow) pure R solution using no extra packages:

# Replicate your matrix
input_df <- data.frame(ID = rnorm(10000),
                           X = rnorm(10000),
                           Y = rnorm(10000),
                           Z = rnorm(10000),
                           value_1 = rnorm(10000),
                           value_2 = rnorm(10000))

# Preallocate memory to a data frame
output_df <- data.frame(
    matrix(
      nrow = nrow(input_df)*2,
      ncol = ncol(input_df)-2))

# Loop through each row in turn.
# Put the first four elements into the current 
# row, and the next two into the current+1 row
# with two NAs attached.
for(i in seq(1, nrow(output_df), 2)){
  output_df[i,] <- input_df[i, c(1:4)]
  output_df[i+1,] <- c(input_df[i, c(5:6)],NA,NA)
}

colnames(output_df) <- c("ID", "X", "Y", "Z")

Which results in

> head(output_df)
          X1          X2         X3         X4
1  0.5529417 -0.93859275  2.0900276 -2.4023800
2  0.9751090  0.13357075         NA         NA
3  0.6753835  0.07018647  0.8529300 -0.9844643
4  1.6405939  0.96133195         NA         NA
5  0.3378821 -0.44612782 -0.8176745  0.2759752
6 -0.8910678 -0.37928353         NA         NA

Upvotes: 0

Related Questions