Pivot longer with multiple named columns

[nviet]

Hello, I'm having trouble shifting the names into separate columns after I pivoted the data. As you can see in the screenshot, the AgeWave1 is supposed to in a separate column. Similarly, AgeWave2,... YPI1, and so on are all supposed to be in a separate column but it has to remain pivoted like this so I can run a repeated measures analysis. I tried using other like "names_sep = "_")" along with pivot longer i found on a Berkley site but that didnt work. Any information would be appreciated. Thank you.

Waves%>%
pivot_longer(!CaseID)->wave1

Waves <- tibble::tribble(
           ~CaseID, ~AgeWave1, ~AgeWave2, ~AgeWave3, ~AgeWave4, ~YPI1, ~YPI2, ~YPI3, ~YPI4, ~HIF1,  ~HIF2, ~HIF3, ~HIF4,
                1L,       12L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                2L,       13L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                3L,       14L,      23L,      24L,      25L,  232L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                4L,       12L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                5L,       14L,      23L,      23L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                6L,       13L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                7L,       14L,      23L,      23L,      25L,   32L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                8L,       14L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
                9L,       14L,      23L,      24L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
               10L,       13L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L

[HanOostdijk]

So Waves is your input. Please show (in code) a part of the output that you wish to get.

[nirgrahamuk]

I agree that your 'ask' is confusing.

1. you present wide data
2. you show code that pivots it to long
3. your complaint seems to be that you now have long rather than wide data

This seeming contradiction makes it hard to know how to assist you ...

[nviet]

Its like the one below

CASEID | name |value

4 S1AGE 16.00

4 S2AGE 16.00

4 S3AGE 17.00

when it should look like

CASEID | Age |Wave | YPI

4 16 1 NA

4 16 2 126

4 17 3 132

[nviet]

The format messed it up but those are supposed to align under the title

[StatSteph]

Are you looking for something like this? There are probably better ways to do this but I made it long and then wide again but not quite as wide.

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
#> Warning: package 'readr' was built under R version 4.1.2

Waves <- tibble::tribble(
   ~CaseID, ~AgeWave1, ~AgeWave2, ~AgeWave3, ~AgeWave4, ~YPI1, ~YPI2, ~YPI3, ~YPI4, ~HIF1,  ~HIF2, ~HIF3, ~HIF4,
   1L,       12L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   2L,       13L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   3L,       14L,      23L,      24L,      25L,  232L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   4L,       12L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   5L,       14L,      23L,      23L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   6L,       13L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   7L,       14L,      23L,      23L,      25L,   32L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   8L,       14L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   9L,       14L,      23L,      24L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   10L,       13L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L)


Waves%>%
   pivot_longer(!CaseID) %>%
   mutate(
      name1=str_sub(name, 1, str_length(name)-1),
      Wave=str_sub(name, str_length(name)),
   ) %>%
   select(-name) %>%
   pivot_wider( names_from="name1", values_from="value")
#> # A tibble: 40 x 5
#>    CaseID Wave  AgeWave   YPI   HIF
#>     <int> <chr>   <int> <int> <int>
#>  1      1 1          12   123   131
#>  2      1 2          23   124 12321
#>  3      1 3          24   132   112
#>  4      1 4          25   123   221
#>  5      2 1          13   123   131
#>  6      2 2          23   124 12321
#>  7      2 3          24   132   112
#>  8      2 4          26   123   221
#>  9      3 1          14   232   131
#> 10      3 2          23   124 12321
#> # ... with 30 more rows

^{Created on 2021-12-01 by the reprex package (v2.0.1)}

[StatSteph]

And here's the way to do it in only 1 step!

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
#> Warning: package 'readr' was built under R version 4.1.2

Waves <- tibble::tribble(
   ~CaseID, ~AgeWave1, ~AgeWave2, ~AgeWave3, ~AgeWave4, ~YPI1, ~YPI2, ~YPI3, ~YPI4, ~HIF1,  ~HIF2, ~HIF3, ~HIF4,
   1L,       12L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   2L,       13L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   3L,       14L,      23L,      24L,      25L,  232L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   4L,       12L,      23L,      24L,      26L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   5L,       14L,      23L,      23L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   6L,       13L,      23L,      24L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   7L,       14L,      23L,      23L,      25L,   32L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   8L,       14L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   9L,       14L,      23L,      24L,      26L,    2L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L,
   10L,       13L,      23L,      23L,      25L,  123L,  124L,  132L,  123L,  131L, 12321L,  112L,  221L)

Waves%>%
   pivot_longer(
      !CaseID,
      names_to = c(".value", "Wave"),
      names_pattern = "(\\w+)(\\d+)"   )
#> # A tibble: 40 x 5
#>    CaseID Wave  AgeWave   YPI   HIF
#>     <int> <chr>   <int> <int> <int>
#>  1      1 1          12   123   131
#>  2      1 2          23   124 12321
#>  3      1 3          24   132   112
#>  4      1 4          25   123   221
#>  5      2 1          13   123   131
#>  6      2 2          23   124 12321
#>  7      2 3          24   132   112
#>  8      2 4          26   123   221
#>  9      3 1          14   232   131
#> 10      3 2          23   124 12321
#> # ... with 30 more rows

^{Created on 2021-12-01 by the reprex package (v2.0.1)}