CODEWITHSUNDEEP
rdplyrtidyversetidyr
Vaibhav Singh
Vaibhav Singh

Reputation: 1209

Merge two columns into duplicate row in R

I know about pivot_longer though this got me stumped since not sure what should I add in values_to arugment. Below image is pretty much all I need

enter image description here

Code:

df <- structure(list(A = 1:3, B = c("a", "b", "c"), C = c("d", "e", 
"f"), D = c(10L, 15L, 20L), E = c(20L, 30L, 40L)), class = "data.frame", row.names = c(NA, 
-3L))

My try:

library(tidyverse)
df %>% pivot_longer(cols=3,names_to="new",values_to="value")

** Will be really helpful if you can add how to convert from output to input as well.

Should I just do bind_rows() instead ?

Upvotes: 0

Views: 256

Answers (1)

Ray
Ray

Reputation: 2288

I. input --> output

You can simply split your dataframe and bind it again

library(dplyr)

df1 <- df %>% select(A, B, D, E)
df2 <- df %>% select(A, C, D, E) %>% rename(B = C)

bind_rows(df1, df2)
  A B  D  E
1 1 a 10 20
2 2 b 15 30
3 3 c 20 40
4 1 d 10 20
5 2 e 15 30
6 3 f 20 40

If you want to use pivot_longer, this is how you could do it:

library(tidyr)

df %>% 
  pivot_longer( cols = B:C                  # the cols we want to combine
              , names_to = "old_col_names"  # the col where we store the old names
              , values_to = "B"             # and we rename the new col to B
              ) %>% 
# ------------- reoder columns and arrange to make it look like output
   select(A, B, D, E) %>%                   # this removes the 'old_col_names' you could also do select(-old_col_names)
   arrange(B)                               # arrange in alphabetical order

II output --> input

For the reverse operation - assuming you do not want to split the df and recombine it - you can use {tidyr}'s pivot_wider() using a "new" name column that meets your requirements. For more complex data sets you may have to be creative here.

library(tidyr)

output %>% 
# --------- introduce a "name" vector -------------
## -------- we use rep() to create set of 3s ... adapt as required!
  mutate(group = c(rep("B",3), rep("C",3)) ) %>% 
# --------- spread the data frame and rearrange the columns
  pivot_wider( id_cols = c(A,D,E)    # these columns are "constant"
             , names_from  = group   # pull "new" column names from our group var 
             , values_from = B) %>%  # spread the values we aggregated in B
  select(A, B, C, D, E)              # rearrange column order to input style

Upvotes: 1

Related Questions