Melting a dataframe in R

Question

I have dataframe of active users where it's 1 when the user was active this specific date and 0 when he was not.
I am trying to modify my dataframe from this:

df <- data.frame(userID=c("user123","user124","user125"),
                 `2021-03-01`=c(1,0,1),
                 `2021-03-08`=c(1,1,1),
                 `2021-03-15`=c(0,0,1))

to this dataframe in an elegant way:

df <- data.frame(userID=c("user123", "user125", "user123","user124","user125","user125"),
                 Active_WeekDate=c("2021-03-01","2021-03-01", "2021-03-08", "2021-03-08","2021-03-08","2021-03-15"))

I found this solution but is there a more elegant way?

library(tidyr)
df <- pivot_longer(df, cols = -userID, names_to="Active_WeekDate")
df <- df[ df$value==1, ]
df <- df[ , c(1,2)]
df

Any help much appreciated!

score 3 · Accepted Answer · answered Jun 04 '21 at 16:35

I always find dplyr approaches much more elegant:

library(tidyverse)
df %>%
        pivot_longer(cols = -userID, names_to="Active_WeekDate") %>%
        filter(value == 1) %>%
        select(1:2) %>%
        mutate(Active_WeekDate = gsub("X", "", Active_WeekDate))

Output:

  userID  Active_WeekDate
  <chr>   <chr>          
1 user123 2021.03.01     
2 user123 2021.03.08     
3 user124 2021.03.08     
4 user125 2021.03.01     
5 user125 2021.03.08     
6 user125 2021.03.15

ahh, you beat me by a minute! But I totally concur with you, `{dplyr}` is an elegant and readable way to deal with these problems. — Ray, Jun 04 '21 at 16:43

score 2 · Answer 2 · answered Jun 04 '21 at 16:43

what about this?

df <- data.frame(userID=c("user123","user124","user125"),
                 `2021-03-01`=c(1,0,1),
                 `2021-03-08`=c(1,1,1),
                 `2021-03-15`=c(0,0,1))
library(tidyverse)
df %>% pivot_longer(starts_with('X'), names_to = 'Active_week_date',names_prefix = 'X', values_transform = list(value = as.logical)) %>%
  filter(value) %>% select(-value)
#> # A tibble: 6 x 2
#>   userID  Active_week_date
#>   <chr>   <chr>           
#> 1 user123 2021.03.01      
#> 2 user123 2021.03.08      
#> 3 user124 2021.03.08      
#> 4 user125 2021.03.01      
#> 5 user125 2021.03.08      
#> 6 user125 2021.03.15

^{Created on 2021-06-04 by the reprex package (v2.0.0)}

Melting a dataframe in R

2 Answers2