[R] Transformations in Tidyverse (renaming and ordering columns)

[Jeff Newmiller]

IMO your question is flawed in that it does not present a desired output.

One thought that occurs to me is that perhaps you should stop treating 
everything like a nail and use some different tools, like nest and a for 
loop:

############################
library(knitr)
library(tibble)
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#>     filter, lag
#> The following objects are masked from 'package:base':
#>
#>     intersect, setdiff, setequal, union
library(tidyr)

testset <- as_tibble(tribble(~Section, ~Observation, ~Order, ~Fiscal, ~Value,
                              "A", "One", 1, "2016-17", 419,
                              "A", "One", 1, "2017-18", 499,
                              "A", "One", 1, "2018-19", 463,
                              "A", "Two", 2, "2016-17", 391,
                              "A", "Two", 2, "2017-18", 445,
                              "A", "Two", 2, "2018-19", 423,
                              "A", "Three", 3, "2016-17", 348,
                              "A", "Three", 3, "2017-18", 353,
                              "A", "Three", 3, "2018-19", 357,
                              "B", "One", 1, "2016-17", 418,
                              "B", "One", 1, "2017-18", 498,
                              "B", "One", 1, "2018-19", 462,
                              "B", "Two", 2, "2016-17", 390,
                              "B", "Two", 2, "2017-18", 444,
                              "B", "Two", 2, "2018-19", 422,
                              "B", "Three", 3, "2016-17", 347,
                              "B", "Three", 3, "2017-18", 352,
                              "B", "Three", 3, "2018-19", 356
                              ))

sections <- (   testset
             %>% select( -Order )
             %>% spread( Observation, Value )
             %>% nest( -Section )
             )
# look at one of the sub-tables
sections[[ "data" ]][[1]]
#> # A tibble: 3 x 4
#>   Fiscal    One Three   Two
#>   <chr>   <dbl> <dbl> <dbl>
#> 1 2016-17   419   348   391
#> 2 2017-18   499   353   445
#> 3 2018-19   463   357   423

# print out the tables with captions
for ( rw in seq.int( nrow( sections ) ) ) {
     print( knitr::kable( sections[[ "data" ]][ rw ]
                        , caption = paste( "Section"
                                         , sections[[ "Section" ]][ rw ] )
                        , format = "pandoc"
                        )
          )
}
#>
#> Section A
#>
#> Fiscal     One   Three   Two
#> --------  ----  ------  ----
#> 2016-17    419     348   391
#> 2017-18    499     353   445
#> 2018-19    463     357   423
#>
#>
#> Section B
#>
#> Fiscal     One   Three   Two
#> --------  ----  ------  ----
#> 2016-17    418     347   390
#> 2017-18    498     352   444
#> 2018-19    462     356   422
#>
############################

On Mon, 22 Oct 2018, Joel Maxuel wrote:

> Hello all,
>
> I am slowly picking away at a factsheet that will include several result
> sets from a single tibble (there is a column identifying the section title)
> and would like to have that value in the top left corner of each resulting
> table.  When using the `spread` function, I see that any remaining columns
> not identified by `key` nor `value` will be placed to the far left.
>
> Like the example below, I would like the column label Fiscal be renamed to
> the data value "A", so the top-left of the result is "A".  However, if I
> try to progmatically set the column name, I run into issues:
>
>
> library(knitr)
> library(tidyverse)
> library(tibble)
> library(dplyr)
> library(magrittr)
>
> testset <- as_tibble(tribble(~Section, ~Observation, ~Order, ~Fiscal,
> ~Value,
>                             "A", "One", 1, "2016-17", 419,
>                             "A", "One", 1, "2017-18", 499,
>                             "A", "One", 1, "2018-19", 463,
>                             "A", "Two", 2, "2016-17", 391,
>                             "A", "Two", 2, "2017-18", 445,
>                             "A", "Two", 2, "2018-19", 423,
>                             "A", "Three", 3, "2016-17", 348,
>                             "A", "Three", 3, "2017-18", 353,
>                             "A", "Three", 3, "2018-19", 357))
> sectionA <- as_tibble(filter(testset, Section == "A")) # Mind you for this
> example, does very little
> spread(select(sectionA, Observation, "A" = Fiscal, Value), key=Observation,
> value=Value) # Works, with reservations
> spread(select(sectionA, Observation, slice(sectionA,1) %>%
> magrittr::extract2("Section") = Fiscal, Value), key=Observation,
> value=Value) # Produces error:
> # Error: unexpected '=' in "spread(select(sectionA, Observation,
> slice(sectionA,1) %>% magrittr::extract2("Section") ="
>
>
> I presume my premise is very wrong.
>
> Also, I am curious how to sort columns (and rows) with extra columns
> (/rows) not meant for display.  In this case, the columns One, Two, and
> Three (in real life won't be linguistic representations of numbers), are
> out of order - however I do have the Order column to fix that (if I knew
> how to include it for sorting purposes, without displaying it).  A few
> hints here would help.  (Perhaps there is column/row hiding in kableExtra -
> which I will be using later on?)
>
> --
> Cheers,
> Joel Maxuel
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil using dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                       Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k