How to create a new data frame where existing columns become a new column in R

Question

I'm still very new to R so bare with me. I have an existing dataset with 33 unique participant Id's and activity dates of 30 days for each of these participants. I'm interested in the columns VeryActiveMinutes, FairlyActiveMinutes, LightlyActiveMinutes, and SedentaryMinutes.

I want to create a new data frame where these columns become a new column called "Activity Levels" and have another column for their average minutes. So it would look something like this:

activity levels avg_activity_minutes

very active
fairly active
lightly active
sedentary

Is it possible to create an output like this?

This is the closest code I've been to getting the data I want but the output looks like its in a wide format rather than long:

avg_active_min_levels <- activity %>%
summarize(avg_very = mean(VeryActiveMinutes), avg_fairly = mean(FairlyActiveMinutes), avg_lightly = mean(LightlyActiveMinutes), avg_sedentary = mean(SedentaryMinutes))

Answer 1

Here this is how you can make a quick small example:

participants <- 33

my_dat <- data.frame(
    id = 1:participants,
    VeryActiveMinutes = rnorm(participants, 0, 5),
    FairlyActiveMinutes = rnorm(participants, 0, 5),
    LightlyActiveMinutes = rnorm(participants, 0, 5),
    SedentaryMinutes = rnorm(participants, 0, 5)
)

This format is called "wide" you want it "tidy" or "long". It look like you are using the tidyverse so I used it.

columns <- names(my_dat)[-1] # vector of columns you want to pivot

library(tidyr)

my_dat_tidy <- my_dat %>% 
    tidyr::pivot_longer(cols =  columns,
                        names_to = "activity levels avg_activity_minutes",
                        values_to = "I_do_not_know")