Pivot_longer error: Can't combine `01/01/2020` <character> and `03/01/2020` <double>

Question

I'm trying to transpose certain columns in my data to make it tidy, but having trouble due to column types. My data looks like this:

  Station    Polluant     Mesure  Unité `01/01/2020` `02/01/2020` `03/01/2020` `04/01/2020` `05/01/2020` `06/01/2020`
  <chr>      <chr>        <chr>   <chr> <chr>        <chr>               <dbl>        <dbl>        <dbl>        <dbl>
1 Grand Parc dioxyde d'a… Dioxyd… µg/m3 16           23                     26           16           30           24
2 Grand Parc ozone (O3)   Ozone   µg/m3 29           27                     24           41           28           10
3 Grand Parc particules … PM10    µg/m3 23           20                     20           13           26           21
4 Talence    dioxyde d'a… Dioxyd… µg/m3 15           24                     27           22           36           22
5 Talence    ozone (O3)   Ozone   µg/m3 26           21                     21           33           22            9
6 Talence    particules … PM10    µg/m3 24           25                     21           14           31           24

My desired output:

  Station    Polluant     Mesure  Unité   Date       Value 
  <chr>      <chr>        <chr>   <chr>   <chr>      <chr>  
1 Grand Parc dioxyde d'a… Dioxyd… µg/m3 01/01/2020     16                               
2 Grand Parc dioxyde d'a… Dioxyd… µg/m3 02/01/2020     23                               
3 Grand Parc dioxyde d'a… Dioxyd… µg/m3 03/01/2020     26                             

I've tried transposing but have difficulty transposing only certain columns. I've also tried pivot_longer but receiving the error: Error: Can't combine `01/01/2020` <character> and `03/01/2020` <double>.

Answer 1

The problem is that some of your columns are character and some are numeric. You can fix this with the values_transform argument.

library(tidyverse)
test_data <- tibble(id = 1:5,
                    d1 = c(2,3,6,2,1),
                    d2 = c("3","6","1","1","9"))

test_data |>
  pivot_longer(cols = c(d1,d2), 
               values_transform = as.numeric)
#> # A tibble: 10 x 3
#>       id name  value
#>    <int> <chr> <dbl>
#>  1     1 d1        2
#>  2     1 d2        3
#>  3     2 d1        3
#>  4     2 d2        6
#>  5     3 d1        6
#>  6     3 d2        1
#>  7     4 d1        2
#>  8     4 d2        1
#>  9     5 d1        1
#> 10     5 d2        9

Update: I'm not sure why that is not working for you, but you could transform the data first and then pivot:

test_data |>
  mutate(across(c(d1:d2), as.numeric)) |>
  pivot_longer(cols = c(d1,d2))

For your data, it would be

atmo |>
  mutate(across(c(`01/01/2020`:`01/01/2022`), as.numeric)) |>
  pivot_longer(cols = c(`01/01/2020`:`01/01/2022`))