Issue with pivot_longer command

Question

I am trying to convert a long dataset to a wide set set using pivot longer, the column headers are "Program ID" and ‘Participant_Count_22’, ‘Participant_Count_21’, ‘Participant_Count_20’, ‘Participant_Count_19’ for four years 2019-2022.

   program_tot %>% pivot_longer(cols=c(‘Participant_Count_22’, ‘Participant_Count_21’, ‘Participant_Count_20’, ‘Participant_Count_19’), 
               names_to='year',
               values_to='Participant_Count')

I am getting an unexpected input error message. Any tips?

You seem to have fancy single quotes around your column names. Try converting them to normal single quotes, or to normal double quotes. Or even try `cols = -1` to pivot all columns except the first — Allan Cameron, Sep 28 '22 at 18:23

score 1 · Answer 1 · answered Sep 28 '22 at 18:35

You seem to have fancy single quotes around your column names. Note carefully the difference between quotes in ‘Participant_Count_22’, which R doesn't recognise as a string, and 'Participant_Count_22', which it does recognise as a string.

In any case, you will save yourself some time and complexity if you use the selection helpers to select columns. In your case you could use contains("Participant_Count")

library(tidyverse)

program_tot %>% 
  pivot_longer(cols = contains("Participant_Count"),
               names_to = 'year',
               values_to = 'Participant_Count') %>%
  mutate(year = as.numeric(paste0("20", sub("Participant_Count_", "", year))))
#> # A tibble: 20 x 3
#>    Program.ID  year Participant_Count
#>    <fct>      <dbl>             <dbl>
#>  1 1           2022                 5
#>  2 1           2021                 5
#>  3 1           2020                 6
#>  4 1           2019                 2
#>  5 2           2022                 7
#>  6 2           2021                 2
#>  7 2           2020                 8
#>  8 2           2019                 5
#>  9 3           2022                 4
#> 10 3           2021                 8
#> 11 3           2020                 1
#> 12 3           2019                 7
#> 13 4           2022                 3
#> 14 4           2021                 1
#> 15 4           2020                 3
#> 16 4           2019                 3
#> 17 5           2022                 5
#> 18 5           2021                 3
#> 19 5           2020                 2
#> 20 5           2019                 1

Data used

program_tot <- data.frame('Program ID' = factor(1:5),
                           Participant_Count_22 = c(5, 7, 4, 3, 5), 
                           Participant_Count_21 = c(5, 2, 8, 1, 3), 
                           Participant_Count_20 = c(6, 8, 1, 3, 2), 
                           Participant_Count_19 = c(2, 5, 7, 3, 1))

^{Created on 2022-09-28 with reprex v2.0.2}

Issue with pivot_longer command

1 Answers1