Unconventional data frame reshaping

Question

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.


Show source
| R   2017-10-04 23:10 5 Answers

Answers to Unconventional data frame reshaping ( 5 )

  1. 2017-10-04 23:10

    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.

  2. 2017-10-05 00:10
    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
    
  3. 2017-10-05 03:10

    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
    
  4. 2017-10-05 03:10

    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]),]
    
  5. 2017-10-05 07:10

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

Leave a reply to - Unconventional data frame reshaping

◀ Go back