Load column name like date and pivot them

[M_AcostaCH]

Hi community

Im try to load this data set but the columns name are in bad format. Im put the col_type as date but don't work.

When Im load the data, this is the result:

ACUTIF_R2 <- read_excel("path.xlsx", col_names = T)
# The name of column is a date format (check the link)

Loading Google Sheets

Next I'm want make a pivot for get better data for plot and statistics.

library(tidyverse)
ACUTIF_R2 %>% pivot_longer(cols=c('44873', '44874'),
                    names_to='FECHA',
                    values_to='PESO')

Thanks for any guide.

[andresrcs]

R doesn't have the concept of data types for column names, Excell can have different data types for each cell but that is not possible in R. I think the easiest solution would be to manually change the cell format of your column names to "text" in Excell before reading it into R because cleaning it in R afterward is going to be very hacky.

[M_AcostaCH]

Ok, Im understand.
But if I change in excel to text, the columns names is different. Like 44873, 44874, 44875, 44876, 44877...etc

[andresrcs]

I think you have to use a formula like =TEXTO(D1; "d-mmm-yy"). I'll take a look into an R solution anyways and post it later if no one beat me to the punch.

[andresrcs]

library(tidyverse)
library(readxl)

file_url <- "https://docs.google.com/spreadsheets/d/1RLl8mmZzoC_nyu20Y8iJ3cgSwLlUn2wc/export?format=xlsx"
download.file(file_url, "path.xlsx")

ACUTIF_R2 <- read_excel("path.xlsx", col_names = T)

names(ACUTIF_R2)[str_detect(names(ACUTIF_R2), "\\d{5}")] <- format(as.Date(as.numeric(names(ACUTIF_R2)[str_detect(names(ACUTIF_R2), "\\d{5}")]), origin = "1899-12-30"), "%d-%b-%y")

ACUTIF_R2 %>%
    mutate(across(matches("\\d{1,2}-"), as.numeric)) %>% 
    pivot_longer(cols = matches("\\d{1,2}-"),
                           names_to='FECHA',
                           values_to='PESO')
#> Warning: There were 42 warnings in `mutate()`.
#> The first warning was:
#> ℹ In argument: `across(matches("\\d{1,2}-"), as.numeric)`.
#> Caused by warning:
#> ! NAs introducidos por coerción
#> ℹ Run `dplyr::last_dplyr_warnings()` to see the 41 remaining warnings.
#> # A tibble: 780 × 5
#>    PLANTA GENOTIPO TRATAMIENTO FECHA      PESO
#>    <chr>  <chr>    <chr>       <chr>     <dbl>
#>  1 P1     G1       G1P1        08-nov-22  4503
#>  2 P1     G1       G1P1        09-nov-22  4553
#>  3 P1     G1       G1P1        10-nov-22  4551
#>  4 P1     G1       G1P1        11-nov-22  4542
#>  5 P1     G1       G1P1        12-nov-22  4542
#>  6 P1     G1       G1P1        15-nov-22  4482
#>  7 P1     G1       G1P1        16-nov-22  4501
#>  8 P1     G1       G1P1        17-nov-22  4509
#>  9 P1     G1       G1P1        18-nov-22  4520
#> 10 P1     G1       G1P1        19-nov-22  4513
#> # … with 770 more rows

^{Created on 2023-01-31 with reprex v2.0.2}

[M_AcostaCH]

Was excellent!
Run Perfect!