R help - Oct 2018 - Transformations in Tidyverse (renaming and ordering columns)

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]]

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 at 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 at 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

Hi Jeff,

Thanks for the input, however it does not seem we are on the same
wavelength.  Maybe I should have distilled my example down even further to
solidify context, although given what I was trying to accomplish I believe
it is all in context.

At this point I am not concerned about presenting multiple sections
(agreed, looping will be important) - my emphasis right now is to present
just one piece in a manner that makes sense.  As a result, I am finding
that using `spread` to convert the flat data into a "crosstab" does
not.

The flat data has (IMO - at least the wider version I have locally) all the
info needed to allow a sort to be enforced, and any miscellaneous data for
that section, without having to customize each section individually (in
essence, allow for looping).  With regard to sorting, in the past hour or
so I have tried the `seq` parameter (after changing `key` to the `Order`
column) in `spread`, as well as the `arrange` function after `spread` but
neither are helpful.

I have considered a leading character in the headings to force a sort, with
something to trim it from the final output, but I am not fond of that
solution.  Also, having the section heading outside the table to me feels
like a waste of space.  With 13 proposed sections in the end (120 to 140
observations), having that all in only two pages may already be a challenge.

--
Cheers,
Joel Maxuel

	[[alternative HTML version deleted]]