Selected columns to new row

user_6640825 0
Viewed 56

I'm trying to split columns into new rows keeping the data of the first two columns.

d1 <- data.frame(a=c(100,0,78),b=c(0,137,117),c.1=c(111,17,91), d.1=c(99,66,22), c.2=c(11,33,44), d.2=c(000,001,002))

d1
    a   b c.1 d.1 c.2 d.2
1 100   0 111  99  11   0
2   0 137  17  66  33   1
3  78 117  91  22  44   2

Expected results would be:

    a   b  c    d
1 100   0  111  99
2 100   0  11   0
3   0 137  17   66  
4   0 137  33   1
5  78 117  91   22  
6  78 117  44   2

Multiple tries with dplyr, but in sees is not the right approach.

dplyr r
3 Answers

If you want to stay in dplyr/tidyverse, you want tidyr::pivot_longer with a special reference to .value -- see the pivot vignette for more:

library(tidyverse)
d1 <- data.frame(
  a = c(100, 0, 78),
  b = c(0, 137, 117),
  c.1 = c(111, 17, 91),
  d.1 = c(99, 66, 22),
  c.2 = c(11, 33, 44),
  d.2 = c(000, 001, 002)
)

d1 %>% 
  pivot_longer(
    cols = contains("."),
    names_to = c(".value", "group"),
    names_sep = "\\."
  )
#> # A tibble: 6 x 5
#>       a     b group     c     d
#>   <dbl> <dbl> <chr> <dbl> <dbl>
#> 1   100     0 1       111    99
#> 2   100     0 2        11     0
#> 3     0   137 1        17    66
#> 4     0   137 2        33     1
#> 5    78   117 1        91    22
#> 6    78   117 2        44     2

Created on 2020-05-11 by the reprex package (v0.3.0)

user_25724230

This could solve your issue:

#Try this
a1 <- d1[,c(1:4)]
a2 <- d1[,c(1,2,5,6)]
names(a1) <- names(a2) <- c('a','b','c','d')
DF <- rbind(a1,a2)
user_20808480

The posted answers are good, here's my attempt:

df <- data.frame(a=c(100,0,78),b=c(0,137,117),
             c.1=c(111,17,91), d.1=c(99,66,22), 
             c.2=c(11,33,44), d.2=c(000,001,002)) 

# Make 2 pivot long operations
df_c <- df %>% select(-d.1, -d.2) %>% 
  pivot_longer(cols = c("c.1", "c.2"), values_to = "c") %>% select(-name)
df_d <- df %>% select(-c.1, -c.2) %>%
  pivot_longer(cols=c("d.1","d.2"), values_to = "d") %>% select(-name)

# bind them without the "key" colums
bind_cols(df_c, select(df_d, -a, -b))

Which produces

# A tibble: 6 x 4
      a     b     c     d
  <dbl> <dbl> <dbl> <dbl>
1   100     0   111    99
2   100     0    11     0
3     0   137    17    66
4     0   137    33     1
5    78   117    91    22
6    78   117    44     2
user_32159400
Related