Unconventional data frame reshaping

user_4208407 0
Viewed 108

I'm trying to reshape table from

col1| col2|col3|id
v1  | v2  | v3 |1
v2  | v1  | v3 |2

to

col1|col2|id
v1  | v2 | 1
v2  | v3 | 1
v2  | v1 | 2
v1  | v3 | 2

Basically create pair of following columns for each id.

Sorry for not clear title, but not sure how to describe what I'm trying to do.

r
5 Answers

for the case presented:

library(dplyr)
bind_rows(
    select(df, -col3),
    select(df,  col1=col2, col2=col3, id)
)

for more columns, assuming id is the last column:

library(dplyr)

NcolsToReduce <- ncol(df) - 2

lapply(1:NcolsToReduce, function(i){
  x <- select(df, i, i+1, id)
  names(x) <- c("col1", "col2", "id")
  x
  }) %>% bind_rows()

I think that'd work.

user_62423840

The solution below should be working for an arbitrary number of columns and an id variable which can be any column (not just the last one).

It uses melt() to reshape from wide to long format and shift() to get the next row as second column of the column pair:

library(data.table)
melt(setDT(df), id.vars = "id")[
  , .(col1 = head(value, -1L), col2 = head(shift(value, 1L, type = "lead"), -1L)), by = id]

    id col1 col2
1:  1   v1   v2
2:  1   v2   v3
3:  2   v2   v1
4:  2   v1   v3

Data

library(data.table)
df <- fread(
  "col3| col2|col1|id
v1  | v2  | v3 |1
v2  | v1  | v3 |2",
  sep = "|")
user_38170040 
library(zoo)
library(tidyr)
library(stringr)
Dat$New=apply(Dat[,1:3],1,function(x) toString(rollapply(x, 2, FUN=function(z) paste(z,collapse =';'))))
NewDat=Dat[,c('id','New')]
NewDat=NewDat%>%
    transform(New = strsplit(New, ",")) %>%
    unnest(New)

NewDat[,c('col1','col2')]=str_split_fixed(NewDat$New,';',2)

NewDat
  id    New col1 col2
1  1  v1;v2   v1   v2
2  1  v2;v3   v2   v3
3  2  v2;v1   v2   v1
4  2  v1;v3   v1   v3
user_79645270

A solution from dplyr and tidyr. The idea is to first convert the data from wide format to long format. After that, duplicate the rows in the middle of each id group, change the column number, and then convert it back to wide format.

# Load packages
library(dplyr)
library(tidyr)

# Create example data frame
dt <- data_frame(col1 = c("v1", "v2"),
                 col2 = c("v2", "v1"),
                 col3 = c("v3", "v3"),
                 id = c(1L, 2L))


# Process the data
dt2 <- dt %>%
  gather(Cols, Value, -id) %>%
  arrange(id) %>%
  group_by(id) %>%
  slice(c(1, 2, 2, 3)) %>%
  mutate(Cols = paste0("col", rep(1:2, each = 2))) %>%
  mutate(id2 = rep(1:2, 2)) %>%
  spread(Cols, Value) %>%
  select(col1, col2, id)

# View the result
dt2
# A tibble: 4 x 3
# Groups:   id [2]
   col1  col2    id
* <chr> <chr> <int>
1    v1    v2     1
2    v2    v3     1
3    v2    v1     2
4    v1    v3     2
user_76698090

A data.table solution, assuming the id column is the last column.

library(data.table)
setDT(df)

rbindlist(lapply(1:(ncol(df)-2), function(x) df[, c(x,x+1,ncol(df)), with = FALSE]))[order(id)]

#    col1 col2 id
# 1:   v1   v2  1
# 2:   v2   v3  1
# 3:   v2   v1  2
# 4:   v1   v3  2

The equivalent using base is similar, but a bit messier and probably less efficient.

#setDF(df)
df2 <- do.call("rbind", lapply(1:(ncol(df)-2), function(x) setNames(df[, c(x,x+1,ncol(df))], names(df)[c(1,2,ncol(df))])))
df2[order(df2[,3]),]
user_53339940
Related