Grouped pivot_longer dplyr

user_10830290 0
Viewed 1176

This is an example dataframe. My real dataframe is larger. I highly prefer a tidyverse solution.

#my data
age <- c(18,18,19)
A1 <- c(3,5,3)
A2 <- c(4,4,3)
B1 <- c(1,5,2)
B2 <- c(2,2,5)
df <- data.frame(age, A1, A2, B1, B2)

I want my data to look like this:

#what i want
new_age <- c(18,18,18,18,19,19)
A <- c(3,5,4,4,3,3)
B <- c(1,5,2,2,2,5)
new_df <- data.frame(new_age, A, B)

I want to pivot longer and stack columns A1:A2 into column A, and B1:B2 into B. I also want to have the responses to match the correct age. For example, the 19 year old person in this example has only responded with 3's in columns A1:A2.

dplyr r
3 Answers
tidyr::pivot_longer(df, cols = -age, names_to = c(".value",'groupid'), 
                    #1+ non digits followed by 1+ digits
                    names_pattern = "(\\D+)(\\d+)")
# A tibble: 6 x 4
    age groupid   A     B
  <dbl> <chr> <dbl> <dbl>
1    18 1         3     1
2    18 2         4     2
3    18 1         5     5
4    18 2         4     2
5    19 1         3     2
6    19 2         3     5
user_84670420

in Base R you will use reshape then select the columns you want. You can change the row names also

reshape(df,2:ncol(df),dir = "long",sep="")[,-c(2,5)] # 
    age A B
1.1  18 3 1
2.1  18 5 5
3.1  19 3 2
1.2  18 4 2
2.2  18 4 2
3.2  19 3 5
user_83802720

As you have a larger dataframe, maybe a solution with data.table will be faster. Here, you can use melt function from data.table package as follow:

library(data.table)
colA = grep("A",colnames(df),value = TRUE)
colB = grep("B",colnames(df),value = TRUE)
setDT(df)

df <- melt(df, measure = list(colA,colB), value.name = c("A","B"))
df[,variable := NULL]
dt <- dt[order(age)]

   age A B
1:  18 3 1
2:  18 5 5
3:  18 4 2
4:  18 4 2
5:  19 3 2
6:  19 3 5

Does it answer your question ?

EDIT: Using patterns - suggestion from @Wimpel

As @Wimpel suggested it in comments, you can get the same result using patterns:

melt( setDT(df), measure.vars = patterns( A="^A[0-9]", B="^B[0-9]") )[, variable:=NULL][]

   age A B
1:  18 3 1
2:  18 5 5
3:  19 3 2
4:  18 4 2
5:  18 4 2
6:  19 3 5
user_122598530
Related