带有列组的pivot_longer [英] pivot_longer with groups of columns

问题描述

我有一个看起来像这样的数据集:

I've got a dataset that looks like this:

df_start <- tribble(
    ~name,   ~age, ~x1_sn_ctrl1, ~x1_listing2_2, ~x1_affect1, ~x2_sn_ctrl1, ~x1_listing2_2, ~x2_affect1, ~number,
    "John",   28,        1,            1,             9,           4,            5,                9,       6,
    "Paul",   27,        2,            1,             4,           1,            3,                3,       4,
    "Ringo",  31,        3,            1,             2,           2,            5,                8,       9)

我需要在处理列中的分组时pivot_longer():

I need to pivot_longer() while handling the groupings within my columns:

• 有 2 个 x 值(1 和 2)
• 每个 x 值有 3 个问题(sn_ctrl1、listing2_2、effect1)

在我的实际数据集中，有 14 个 x.

In my actual dataset, there are 14 x's.

本质上，我想做的是将 pivot_longer() 应用于 x 值，但留下我的 3 个问题(sn_ctrl1、listing2_2、affect1).

Essentially, what I'd like to do is to apply pivot_longer() to the x-values but leave my 3 questions (sn_ctrl1, listing2_2, affect1) wide.

我想要的结果是:

df_end <- tribble(
    ~name, ~age, ~xval, ~sn_ctrl1, ~listing2_2, ~affect1, ~number,
    "John", 28,    1,        1,         1,          9,       6,
    "John", 28,    2,        4,         5,          9,       6,
    "Paul", 27,    1,        2,         1,          4,       4,  
    "Paul", 27,    2,        1,         3,          3,       4, 
    "Ringo", 31,   1,        3,         1,          2,       9, 
    "Ringo", 31,   2,        2,         5,          8,       9)

我尝试了很多在 names_pattern 中使用正则表达式的非常不成功的尝试 &pivot_longer 但我完全出乎意料.

I have tried lots of very unsuccessful attempts playing with regex in names_pattern & pivot_longer but am completely striking out.

有人知道如何解决这个问题吗?

Anyone know how to tackle this?

谢谢！

PS:请注意，我试图制作一个简单的可重现示例.我的专栏的实际名称略有不同.例如，有 x1_sn_ctrl1 &x1_attr1_ctrl2.

PS: Note that I tried to make a straightforward reproducible example. The actual names of my columns vary slightly. For instance, there is x1_sn_ctrl1 & x1_attr1_ctrl2.

推荐答案

您可以使用:

tidyr::pivot_longer(df_start, 
                    cols = -c(name, age, number), 
                    names_to = c("xval", ".value"),
                    names_pattern = 'x(\d+)_(.*)')

哪个收益

# A tibble: 9 x 7
  name    age number xval  sn_ctrl1 listing2_2 affect1
  <chr> <dbl>  <dbl> <chr>    <dbl>      <dbl>   <dbl>
1 John     28      6 1            1          1       9
2 John     28      6 2            4         NA       9
3 John     28      6 1           NA          5      NA
4 Paul     27      4 1            2          1       4
5 Paul     27      4 2            1         NA       3
6 Paul     27      4 1           NA          3      NA
7 Ringo    31      9 1            3          1       2
8 Ringo    31      9 2            2         NA       8
9 Ringo    31      9 1           NA          5      NA

这篇关于带有列组的pivot_longer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！