CODEWITHSUNDEEP
rpivot-table
user1658170
user1658170

Reputation: 858

R Pivot multiple columns from wide to long

I am unsure how to use pivot longer when I need to pivot multiple columns(but not all columns) from wide to long.

Starting Data:

    dat <- tibble(aColumn = c("stuff", "stuff", "stuff"),
               anotherColumn = c("more stuff", "more stuff", "more stuff"),
               `Day a Time` = c("12:00", "13:00", "14:00"),
               `Day a Value` = c("R", "I", "O"),
               `Day b Time` = c("12:45", "14:00", "15:35"),
               `Day b Value` = c("R", "P", "O"))

FinalData:

desiredDat <- tibble(aColumn = rep("stuff", 6),
       anotherColumn = rep("more stuff", 6),
       DayID = c("a", "a", "a", "b", "b", "b"),
       DayTime = c("12:00", "13:00", "14:00", "12:45", "14:00", "15:35"),
       DayValue = c("R","I","O","R","P","O"))

Does anyone know how I can accomplish this?

Upvotes: 1

Views: 396

Answers (2)

Onyambu
Onyambu

Reputation: 79188

In base R, you could do:

dat1 <- setNames(dat, sub(" (\\w+) (\\w+)$",'\\2 \\1', names(dat)))

tms <- unique(sub('\\w+ (\\w) \\w+', '\\1', names(df)[-(1:2)]))

reshape(dat1, -(1:2), dir='long', times = tms, sep=' ')

    aColumn anotherColumn time DayTime DayValue id
1.a   stuff    more stuff    a   12:00        R  1
2.a   stuff    more stuff    a   13:00        I  2
3.a   stuff    more stuff    a   14:00        O  3
1.b   stuff    more stuff    b   12:45        R  1
2.b   stuff    more stuff    b   14:00        P  2
3.b   stuff    more stuff    b   15:35        O  3

Then you could change the column names to what you want

Upvotes: 1

akrun
akrun

Reputation: 886928

Here is an option with pivot_longer after rearranging the column names substring. We rename the columns that starts with 'Day' by rearraning the the words i.e. the substring 'a', 'b' in the column name is shifted to the end with the delimiter _ by capturing those words ((\\w+)) and then in the replacement for str_replace, changing the backreference order (\\1, \\2 etc)

library(dplyr)
library(stringr)
library(tidyr)
dat %>%
     rename_at(vars(starts_with('Day')), 
       ~ str_replace(., '(\\w+) (\\w+) (\\w+)', "\\1\\3_\\2")) %>% 
     pivot_longer(cols = starts_with('Day'), 
           names_to = c( ".value", "DayID"), names_sep="_")

-output

# A tibble: 6 x 5
#  aColumn anotherColumn DayID DayTime DayValue
#  <chr>   <chr>         <chr> <chr>   <chr>   
#1 stuff   more stuff    a     12:00   R       
#2 stuff   more stuff    b     12:45   R       
#3 stuff   more stuff    a     13:00   I       
#4 stuff   more stuff    b     14:00   P       
#5 stuff   more stuff    a     14:00   O       
#6 stuff   more stuff    b     15:35   O

Upvotes: 2

Related Questions