Reverse dataframe and merge columns in R

Question

Hello I have a df such as :

COL1 COL2 COL3 COL4
SP1  OK   2    87
SP2  NO   8    90
SP3  NO   2    56
SP4  OK   3    45

and I would like to put COL2,COL3 and COL4 into one unique column called Categorie and add the values associated into a column calles Value;

COL1 Categorie Value 
SP1  COL2     OK
SP2 COL2     NO
SP3 COL2     NO
SP4 COL2     OK
SP1 COL3     2
SP2 COL3     8
SP3 COL3     2
SP4 COL3     3
SP1 COL4     87
SP2 COL4     90
SP3 COL4     56
SP4 COL4     45

this seems like a bad idea since `COL2` is of class character and `COL3` and `COL4` are of type numeric. You could still do this by converting `COL3` and `COL4` to character but it seems like a bad idea — Cettt, May 28 '20 at 16:07

LC-datascientist · Accepted Answer · 2020-05-28T22:50:04.167

You can use gather from the tidyr package.

library(tidyr)

df <- data.frame(COL1 = c("SP1","SP2","SP3","SP4"), 
  COL2 = c("OK","NO","NO","OK"), 
  COL3 = c(2,8,2,3), 
  COL4 = c(87,90,56,45))

gather(df, Categorie, Value, COL2, COL3, COL4)

#>    COL1 Categorie Value
#> 1   SP1      COL2    OK
#> 2   SP2      COL2    NO
#> 3   SP3      COL2    NO
#> 4   SP4      COL2    OK
#> 5   SP1      COL3     2
#> 6   SP2      COL3     8
#> 7   SP3      COL3     2
#> 8   SP4      COL3     3
#> 9   SP1      COL4    87
#> 10  SP2      COL4    90
#> 11  SP3      COL4    56
#> 12  SP4      COL4    45

Edit

Because you want all columns except for COL1 to be in Categorie, you can also shorten it like this:

gather(df, Categorie, Value, -COL1)

score 1 · Answer 2 · answered May 28 '20 at 18:32

We can use pivot_longer

library(tidyr)
library(dplyr)
df %>% 
     mutate_at(vars(COL2:COL4), as.character) %>%
     pivot_longer(cols = -COL1, names_to = 'Categorie', values_to = 'Value')
# A tibble: 12 x 3
#   COL1  Categorie Value
#   <chr> <chr>     <chr>
# 1 SP1   COL2      OK   
# 2 SP1   COL3      2    
# 3 SP1   COL4      87   
# 4 SP2   COL2      NO   
# 5 SP2   COL3      8    
# 6 SP2   COL4      90   
# 7 SP3   COL2      NO   
# 8 SP3   COL3      2    
# 9 SP3   COL4      56   
#10 SP4   COL2      OK   
#11 SP4   COL3      3    
#12 SP4   COL4      45

data

df <- structure(list(COL1 = c("SP1", "SP2", "SP3", "SP4"), COL2 = c("OK", 
"NO", "NO", "OK"), COL3 = c(2, 8, 2, 3), COL4 = c(87, 90, 56, 
45)), class = "data.frame", row.names = c(NA, -4L))

Reverse dataframe and merge columns in R

2 Answers2

data