I'm having difficulty pivotting paired columns. I understand there is the names_pattern
argument and I cannot figure out how to make this work.
My data looks like this:
structure(list(Q8_1_avg = 4.72562264837839, Q8_1_avg_se = 0.0595342202500642,
Q8_1_unweighted = 4.90473815461347, Q8_2_avg = 4.65508494735021,
Q8_2_avg_se = 0.0541589332376175, Q8_2_unweighted = 4.6498753117207,
Q8_3_avg = 5.4756060523178, Q8_3_avg_se = 0.0534895224170486,
Q8_3_unweighted = 5.57506234413965), row.names = c(NA, -1L
), class = "data.frame")->dat
And my desired output looks like this:
df<-data.frame(
Question=c('Q8_1', 'Q8_2','Q8_3'),
#Values taken from Q8_[123]_avg
Weighted_Average=c(4.72,4.65, 5.47),
#Values taken from Q8_[123]_avg_se
Weighted_SE=c(0.05,0.05 ,0.05),
#Values taken from Q8_[123]_unweighted
Unweighted_Average=c(4.90, 4.64, 5.57)
)
df
Thank you for any assistance.
CodePudding user response:
We can use pivot_longer
. If needed use rename
to change the column names. Specify the names_to
as a vector of 'Question' (which return the prefix part of the column name) and .value
returns the value in the long format. Then, in names_pattern
, capture the prefix part i.e. one or more characters not a _
([^_]
) followed by _
and some digits (\\d
) as a group ((...)
) then the _
and the second capture group involving the rest of the characters ((.*)
)
library(dplyr)
library(tidyr)
dat %>%
pivot_longer(cols = everything(), names_to = c("Question",
".value"), names_pattern = "^([^_] _\\d )_(.*)")
# A tibble: 3 × 4
Question avg avg_se unweighted
<chr> <dbl> <dbl> <dbl>
1 Q8_1 4.73 0.0595 4.90
2 Q8_2 4.66 0.0542 4.65
3 Q8_3 5.48 0.0535 5.58