Transformations in Tidyverse (renaming and ordering columns)

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Transformations in Tidyverse (renaming and ordering columns)

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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: 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]]
>
> ______________________________________________
> [hidden email] 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:<[hidden email]>        Basics: ##.#.       ##.#.  Live Go...
                                       Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

Jeff Newmiller
If you are willing to work in the context of LaTeX output then perhaps you will find the "tables" package useful. However, while you think you have communicated clearly enough regarding what you want to accomplish, I do not, so either someone else will intuit what you want or you will create a mock-up of what you want your output to look like to remove the guesswork.

On October 22, 2018 1:57:55 PM PDT, Joel Maxuel <[hidden email]> wrote:

>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

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

Joel Maxuel
For clarity sake.  More show (with example closer to reality), less tell.
:^)

## Current:

> library(knitr)
> library(tidyverse)
── Conflicts
─────────────────────────────────────────────────────────────────────
tidyverse_conflicts() ──
x dplyr::filter() masks stats::filter()
x dplyr::lag()    masks stats::lag()
> library(tibble)
> library(dplyr)
>
> testset <- as_tibble(tribble(~SN, ~Section, ~Order, ~Observation, ~Seq,
~Label, ~Value,
+                              2, "For Reporting Quarter", 1, "One", 1,
"Western", 163,
+                              2, "For Reporting Quarter", 1, "One", 2,
"Northern", 105,
+                              2, "For Reporting Quarter", 1, "One", 3,
"Eastern", 121,
+                              2, "For Reporting Quarter", 1, "One", 4,
"Southern", 74,
+                              2, "For Reporting Quarter", 2, "Two", 1,
"Western", 147,
+                              2, "For Reporting Quarter", 2, "Two", 2,
"Northern", 100,
+                              2, "For Reporting Quarter", 2, "Two", 3,
"Eastern", 106,
+                              2, "For Reporting Quarter", 2, "Two", 4,
"Southern", 70,
+                              2, "For Reporting Quarter", 3, "Three", 1,
"Western", 119,
+                              2, "For Reporting Quarter", 3, "Three", 2,
"Northern", 82,
+                              2, "For Reporting Quarter", 3, "Three", 3,
"Eastern", 90,
+                              2, "For Reporting Quarter", 3, "Three", 4,
"Southern", 65))
> testset %>% select(Observation, Label, Value) %>% spread(key=Observation,
value=Value)
# A tibble: 4 x 4
  Label      One Three   Two
  <chr>    <dbl> <dbl> <dbl>
1 Eastern    121    90   106
2 Northern   105    82   100
3 Southern    74    65    70
4 Western    163   119   147
>

## Intended:

# A tibble: 4 x 4
  For Reporting Quarter     One   Two Three
  <chr>                          <dbl> <dbl> <dbl>
1 Western                       163   147   119
2 Northern                       105   100    82
3 Eastern                        121   106    90
4 Southern                       74     70     65
>

##

Unfortunately I don't know how to get there from here.  Section, Order and
Seq are there to assist with getting the data to the right output
programmatically, however I don't know how to make use of them.

Hope this helps.

--
Cheers,
Joel Maxuel


On Mon, Oct 22, 2018 at 6:18 PM Jeff Newmiller <[hidden email]>
wrote:

> If you are willing to work in the context of LaTeX output then perhaps you
> will find the "tables" package useful. However, while you think you have
> communicated clearly enough regarding what you want to accomplish, I do
> not, so either someone else will intuit what you want or you will create a
> mock-up of what you want your output to look like to remove the guesswork.
>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

Bert Gunter-2
For clarity's sake:

** Stop posting in HTML.**


Bert Gunter

"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Mon, Oct 22, 2018 at 4:55 PM Joel Maxuel <[hidden email]> wrote:

> For clarity sake.  More show (with example closer to reality), less tell.
> :^)
>
> ## Current:
>
> > library(knitr)
> > library(tidyverse)
> ── Conflicts
> ─────────────────────────────────────────────────────────────────────
> tidyverse_conflicts() ──
> x dplyr::filter() masks stats::filter()
> x dplyr::lag()    masks stats::lag()
> > library(tibble)
> > library(dplyr)
> >
> > testset <- as_tibble(tribble(~SN, ~Section, ~Order, ~Observation, ~Seq,
> ~Label, ~Value,
> +                              2, "For Reporting Quarter", 1, "One", 1,
> "Western", 163,
> +                              2, "For Reporting Quarter", 1, "One", 2,
> "Northern", 105,
> +                              2, "For Reporting Quarter", 1, "One", 3,
> "Eastern", 121,
> +                              2, "For Reporting Quarter", 1, "One", 4,
> "Southern", 74,
> +                              2, "For Reporting Quarter", 2, "Two", 1,
> "Western", 147,
> +                              2, "For Reporting Quarter", 2, "Two", 2,
> "Northern", 100,
> +                              2, "For Reporting Quarter", 2, "Two", 3,
> "Eastern", 106,
> +                              2, "For Reporting Quarter", 2, "Two", 4,
> "Southern", 70,
> +                              2, "For Reporting Quarter", 3, "Three", 1,
> "Western", 119,
> +                              2, "For Reporting Quarter", 3, "Three", 2,
> "Northern", 82,
> +                              2, "For Reporting Quarter", 3, "Three", 3,
> "Eastern", 90,
> +                              2, "For Reporting Quarter", 3, "Three", 4,
> "Southern", 65))
> > testset %>% select(Observation, Label, Value) %>% spread(key=Observation,
> value=Value)
> # A tibble: 4 x 4
>   Label      One Three   Two
>   <chr>    <dbl> <dbl> <dbl>
> 1 Eastern    121    90   106
> 2 Northern   105    82   100
> 3 Southern    74    65    70
> 4 Western    163   119   147
> >
>
> ## Intended:
>
> # A tibble: 4 x 4
>   For Reporting Quarter     One   Two Three
>   <chr>                          <dbl> <dbl> <dbl>
> 1 Western                       163   147   119
> 2 Northern                       105   100    82
> 3 Eastern                        121   106    90
> 4 Southern                       74     70     65
> >
>
> ##
>
> Unfortunately I don't know how to get there from here.  Section, Order and
> Seq are there to assist with getting the data to the right output
> programmatically, however I don't know how to make use of them.
>
> Hope this helps.
>
> --
> Cheers,
> Joel Maxuel
>
>
> On Mon, Oct 22, 2018 at 6:18 PM Jeff Newmiller <[hidden email]>
> wrote:
>
> > If you are willing to work in the context of LaTeX output then perhaps
> you
> > will find the "tables" package useful. However, while you think you have
> > communicated clearly enough regarding what you want to accomplish, I do
> > not, so either someone else will intuit what you want or you will create
> a
> > mock-up of what you want your output to look like to remove the
> guesswork.
> >
> >
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

John Laing
In reply to this post by Joel Maxuel
I don't know much about the Tidyverse, but more generally the way to
represent ordered categorical data is with a factor. This seems to work:

> testset$Observation <- factor(testset$Observation, levels=c("One", "Two",
"Three"))
> testset$Label <- factor(testset$Label, levels=c("Western", "Northern",
"Eastern", "Southern"))
> testset %>% select(Observation, Label, Value) %>% spread(key=Observation,
value=Value)
# A tibble: 4 x 4
  Label      One   Two Three
  <fct>    <dbl> <dbl> <dbl>
1 Western    163   147   119
2 Northern   105   100    82
3 Eastern    121   106    90
4 Southern    74    70    65


JL

On Mon, Oct 22, 2018 at 7:55 PM Joel Maxuel <[hidden email]> wrote:

> For clarity sake.  More show (with example closer to reality), less tell.
> :^)
>
> ## Current:
>
> > library(knitr)
> > library(tidyverse)
> ── Conflicts
> ─────────────────────────────────────────────────────────────────────
> tidyverse_conflicts() ──
> x dplyr::filter() masks stats::filter()
> x dplyr::lag()    masks stats::lag()
> > library(tibble)
> > library(dplyr)
> >
> > testset <- as_tibble(tribble(~SN, ~Section, ~Order, ~Observation, ~Seq,
> ~Label, ~Value,
> +                              2, "For Reporting Quarter", 1, "One", 1,
> "Western", 163,
> +                              2, "For Reporting Quarter", 1, "One", 2,
> "Northern", 105,
> +                              2, "For Reporting Quarter", 1, "One", 3,
> "Eastern", 121,
> +                              2, "For Reporting Quarter", 1, "One", 4,
> "Southern", 74,
> +                              2, "For Reporting Quarter", 2, "Two", 1,
> "Western", 147,
> +                              2, "For Reporting Quarter", 2, "Two", 2,
> "Northern", 100,
> +                              2, "For Reporting Quarter", 2, "Two", 3,
> "Eastern", 106,
> +                              2, "For Reporting Quarter", 2, "Two", 4,
> "Southern", 70,
> +                              2, "For Reporting Quarter", 3, "Three", 1,
> "Western", 119,
> +                              2, "For Reporting Quarter", 3, "Three", 2,
> "Northern", 82,
> +                              2, "For Reporting Quarter", 3, "Three", 3,
> "Eastern", 90,
> +                              2, "For Reporting Quarter", 3, "Three", 4,
> "Southern", 65))
> > testset %>% select(Observation, Label, Value) %>% spread(key=Observation,
> value=Value)
> # A tibble: 4 x 4
>   Label      One Three   Two
>   <chr>    <dbl> <dbl> <dbl>
> 1 Eastern    121    90   106
> 2 Northern   105    82   100
> 3 Southern    74    65    70
> 4 Western    163   119   147
> >
>
> ## Intended:
>
> # A tibble: 4 x 4
>   For Reporting Quarter     One   Two Three
>   <chr>                          <dbl> <dbl> <dbl>
> 1 Western                       163   147   119
> 2 Northern                       105   100    82
> 3 Eastern                        121   106    90
> 4 Southern                       74     70     65
> >
>
> ##
>
> Unfortunately I don't know how to get there from here.  Section, Order and
> Seq are there to assist with getting the data to the right output
> programmatically, however I don't know how to make use of them.
>
> Hope this helps.
>
> --
> Cheers,
> Joel Maxuel
>
>
> On Mon, Oct 22, 2018 at 6:18 PM Jeff Newmiller <[hidden email]>
> wrote:
>
> > If you are willing to work in the context of LaTeX output then perhaps
> you
> > will find the "tables" package useful. However, while you think you have
> > communicated clearly enough regarding what you want to accomplish, I do
> > not, so either someone else will intuit what you want or you will create
> a
> > mock-up of what you want your output to look like to remove the
> guesswork.
> >
> >
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Transformations in Tidyverse (renaming and ordering columns)

Joel Maxuel
I am impressed.  Upon playing with factor, I took it a step further to
commonize (and this could very well be simpified):

>
> testset$Observation <- factor(testset$Order, levels=testset$Order,
labels=testset$Observation)
> testset$Label <- factor(testset$Seq, levels=testset$Seq,
labels=testset$Label)
>
>     testset %>% select(Observation, Label, Value) %>%
spread(key=Observation, value=Value)
# A tibble: 4 x 4
  Label      One   Two Three
  <fct>    <dbl> <dbl> <dbl>
1 Western    163   147   119
2 Northern   105   100    82
3 Eastern    121   106    90
4 Southern    74    70    65
>

All I see left now is to replace "Label" heading value with the appropriate
testset$Section value provided for the situation.  In this case...

# A tibble: 4 x 4
  For Reporting Quarter      One   Two Three
  <fct>                               <dbl> <dbl> <dbl>
...

FWIW, before sending this I cleared all formatting and dismissed the
formatting toolbar (been doing only the latter before) in the Gmail client,
which I presume will finally strip HTML from the message.

--
Cheers,
Joel Maxuel

On Mon, Oct 22, 2018 at 10:48 PM John Laing <[hidden email]> wrote:

> I don't know much about the Tidyverse, but more generally the way to
> represent ordered categorical data is with a factor. This seems to work:
>
> > testset$Observation <- factor(testset$Observation, levels=c("One",
> "Two", "Three"))
> > testset$Label <- factor(testset$Label, levels=c("Western", "Northern",
> "Eastern", "Southern"))
> > testset %>% select(Observation, Label, Value) %>%
> spread(key=Observation, value=Value)
> # A tibble: 4 x 4
>   Label      One   Two Three
>   <fct>    <dbl> <dbl> <dbl>
> 1 Western    163   147   119
> 2 Northern   105   100    82
> 3 Eastern    121   106    90
> 4 Southern    74    70    65
>
>>
>>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.