pivot_longer_complex-example.R

#;; Transform an input into shape fit for updating
#;; Here, each row has 3 observations, a modelnumber, serialnumber, and
#;; salesordernumber for each object.
library(readxl)
library(janitor)
#> 
#> Attaching package: 'janitor'
#> The following objects are masked from 'package:stats':
#> 
#>     chisq.test, fisher.test
library(tidyverse)

inputFilepath <- file.path('C:/Users/IRINZN/Downloads', 'TIS Chiller SN MN SON to load to DB 1 of 2-DEV.xlsx')
workWide_df <-
    read_excel(inputFilepath) %>%
    clean_names()

#;; Glimpse of raw input structure
workWide_df %>%
    glimpse()
#> Rows: 1,190
#> Columns: 9
#> $ tis_location_name                             <chr> "Virginia Tech Dietrick ~
#> $ tisobjectid                                   <dbl> 202130000, 200397296, 20~
#> $ tis_object_name                               <chr> "Dietrick Chiller 2", "C~
#> $ unit_model_number                             <chr> "CVRE", "CVHF077FA4W0PE0~
#> $ unit_serial_number                            <chr> "l17h03794", "L15K05387"~
#> $ unit_sales_order_number                       <chr> "003065H", "J5B395A", "L~
#> $ analytic_parameter_name_id_model_number       <dbl> 131085, 131085, 131085, ~
#> $ analytic_parameter_name_id_serial_number      <dbl> 131087, 131087, 131087, ~
#> $ analytic_parameter_name_id_sales_order_number <dbl> 131127, 131127, 131127, ~


names(workWide_df) <- c(
    "tis_location_name",
    "tisobjectid",
    "tis_object_name",
    "unit_modelnumber",
    "unit_serialnumber",
    "unit_salesordernumber",
    "analyticparameternameid_modelnumber",
    "analyticparameternameid_serialnumber",
    "analyticparameternameid_salesordernumber")

#;; Pivot to usable form.
#;; See: https://tidyr.tidyverse.org/articles/pivot.html#multiple-observations-per-row
work_df <-
    workWide_df %>%
    pivot_longer(
        !all_of(c("tis_location_name", "tisobjectid", "tis_object_name")),
        names_to = c(".value", "k"), 
        names_sep = "_", 
        values_drop_na = TRUE)

#;; Glimpse of output structure
work_df %>%
    glimpse()
#> Rows: 3,570
#> Columns: 6
#> $ tis_location_name       <chr> "Virginia Tech Dietrick Chiller Plant", "Virgi~
#> $ tisobjectid             <dbl> 202130000, 202130000, 202130000, 200397296, 20~
#> $ tis_object_name         <chr> "Dietrick Chiller 2", "Dietrick Chiller 2", "D~
#> $ k                       <chr> "modelnumber", "serialnumber", "salesordernumb~
#> $ unit                    <chr> "CVRE", "l17h03794", "003065H", "CVHF077FA4W0P~
#> $ analyticparameternameid <dbl> 131085, 131087, 131127, 131085, 131087, 131127~