-
Notifications
You must be signed in to change notification settings - Fork 18
/
Copy pathREADME.Rmd
270 lines (202 loc) · 11.9 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
---
title: "pivottabler"
output: github_document
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```
[](https://github.com/cbailiss/pivottabler/actions)
[](https://cran.r-project.org/package=pivottabler)
[](https://r-pkg.org/pkg/pivottabler)
The `pivottabler` package enables pivot tables to be created with just a few lines of R.
The `pivottabler` package aims to:
* Provide an easy way of creating pivot tables, without requiring the user to specify low-level layout logic.
* Provide multiple ways of specifying calculation logic to cover both simple and more sophisticated requirements.
* Provide styling options so the pivot tables can be themed/branded as needed.
All calculations for the pivot tables take place inside R, enabling the use of a wide-range of R functions in the calculation logic.
Pivot tables are rendered as htmlwidgets, Latex or plain text. The HTML/Latex/text can be exported for use outside of R.
Pivot tables can be converted to a standard R matrix or data frame. Pivot tables can be exported to Excel. Pivot tables can also be converted to a `basictabler` table for further manipulation.
Using the `flextabler` package it is also possible to output tables to Word and PowerPoint.
`pivottabler` is a companion package to the `basictabler` package. `pivottabler` is focussed on generating pivot tables and can aggregate data. `basictabler` does not aggregate data but offers more control of table structure.
For more detailed information see http://www.pivottabler.org.uk/articles.
### Installation
You can install:
* the latest released version from CRAN with
```{r eval=FALSE}
install.packages("pivottabler")
```
* the latest development version from GitHub with
```{r eval=FALSE}
devtools::install_github("cbailiss/pivottabler", build_vignettes = TRUE)
```
### Example
`pivottabler` has many styling and formatting capabilities when rendering pivot tables in HTML / as htmlwidgets using `pt$renderPivot()`, however the most basic output is simply as plain text.
#### Plain Text Output
A simple example of creating a pivot table - summarising the types of trains run by different train companies:
```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
# arguments: qpvt(dataFrame, rows, columns, calculations, ...)
qpvt(bhmtrains, "TOC", "TrainCategory", "n()") # TOC = Train Operating Company
```
`pivottabler` also offers a more verbose syntax that is more self-describing and offers additional options that aren't available with the quick-pivot functions. The equivalent verbose commands to output the same pivot table as above are:
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.
pt$addColumnDataGroups("TrainCategory") # e.g. Express Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt
```
Multiple levels can be added to the pivot table row or column headings, e.g. looking at combinations of TOC and PowerType:
```{r, eval=FALSE}
library(pivottabler)
qpvt(bhmtrains, c("TOC", "PowerType"), "TrainCategory", "n()")
```
```{r message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Train
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt
```
#### HTML Output
The HTML rendering of the same two pivot tables shown above (each constructed using both a quick-pivot function and verbose syntax) is:
```{r, eval=FALSE}
library(pivottabler)
qhpvt(bhmtrains, "TOC", "TrainCategory", "n()")
```
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
```
```{r, eval=FALSE}
library(pivottabler)
qhpvt(bhmtrains, c("TOC", "PowerType"), "TrainCategory", "n()")
```
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.
pt$addColumnDataGroups("TrainCategory") # e.g. Express Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Train
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
```
Outline layout is an alternative way of rendering the row groups, e.g. for the same pivot table as above:
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC",
outlineBefore=list(isEmpty=FALSE, groupStyleDeclarations=list(color="blue")),
outlineTotal=list(isEmpty=FALSE, groupStyleDeclarations=list(color="blue")))
pt$addRowDataGroups("PowerType", addTotal=FALSE)
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
```
Outline layout can also be used to build a pivot table with a variable depth hierarchy on the rows, e.g. a simple balance sheet:
The R for generating the above pivot table can be found in the Regular Layout vignette at http://www.pivottabler.org.uk/articles.
#### Multiple Calculations
Multiple calculations are supported. Calculations can be based on other calculations in the pivot table. Calculations can be hidden - e.g. to hide calculations that only exist to provide values to other calculations.
For example, looking at the total number of trains and the percentage of trains that arrive more than five minutes late for combinations of train operating company (TOC) and train category:
```{r, eval=FALSE}
library(pivottabler)
library(dplyr)
library(lubridate)
# derive train delay data
trains <- mutate(bhmtrains,
ArrivalDelta=difftime(ActualArrival, GbttArrival, units="mins"),
ArrivalDelay=ifelse(ArrivalDelta<0, 0, ArrivalDelta),
DelayedByMoreThan5Minutes=ifelse(ArrivalDelay>=5,1,0))
# create the pivot table
pt <- PivotTable$new()
pt$addData(trains)
pt$addRowDataGroups("TOC", totalCaption="All TOCs")
pt$addColumnDataGroups("TrainCategory", totalCaption="All Trains")
pt$defineCalculation(calculationName="TotalTrains", caption="Train Count",
summariseExpression="n()")
pt$defineCalculation(calculationName="DelayedTrains", caption="Trains Arr. 5+ Mins Late",
summariseExpression="sum(DelayedByMoreThan5Minutes, na.rm=TRUE)",
visible=FALSE)
pt$defineCalculation(calculationName="DelayedPercent", caption="% Late Trains",
type="calculation", basedOn=c("DelayedTrains", "TotalTrains"),
format="%.1f %%",
calculationExpression="values$DelayedTrains/values$TotalTrains*100")
pt$renderPivot()
```
It is also possible to change the axis (rows or columns) and level in which the calculations appear. See the "Calculations" vignette for details.
More advanced calculations such as % of row total, cumulative sums, etc are possible. See the "A2. Appendix: Calculations" vignette for details.
#### Styling Example
Styling can be specified when creating the pivot table. The example below shows specifying styling using a quick-pivot function and using the more verbose syntax.
```{r, eval=FALSE}
library(pivottabler)
qhpvt(bhmtrains, "TOC", "TrainCategory", "n()",
tableStyle=list("border-color"="maroon"),
headingStyle=list("color"="cornsilk", "background-color"="maroon",
"font-style"="italic", "border-color"="maroon"),
cellStyle=list("color"="maroon", "background-color"="cornsilk",
"border-color"="maroon"),
totalStyle=list("color"="maroon", "background-color"="cornsilk",
"border-color"="maroon", "font-weight"="bold"))
```
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new(tableStyle=list("border-color"="maroon"),
headingStyle=list("color"="cornsilk", "background-color"="maroon",
"font-style"="italic", "border-color"="maroon"),
cellStyle=list("color"="maroon", "background-color"="cornsilk",
"border-color"="maroon"),
totalStyle=list("color"="maroon", "background-color"="cornsilk",
"border-color"="maroon", "font-weight"="bold"))
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
```
It is also possible to change the styling of single cells and ranges of cells after the pivot table has been created. See the "Styling" and "Finding and Formatting" vignettes for more details.
#### Excel Output
The same styling/formatting used for the HTML output is also used when outputting to Excel - greatly reducing the amount of script that needs to be written to create Excel output.
```{r eval=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.
pt$addColumnDataGroups("TrainCategory") # e.g. Express Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Train
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
library(openxlsx)
wb <- createWorkbook(creator = Sys.getenv("USERNAME"))
addWorksheet(wb, "Data")
pt$writeToExcelWorksheet(wb=wb, wsName="Data",
topRowNumber=2, leftMostColumnNumber=2, applyStyles=TRUE)
saveWorkbook(wb, file="C:\\test.xlsx", overwrite = TRUE)
```
In the screenshot above, Gridlines have been made invisible to make the styling easier to see (by clearing the checkbox on the 'View' ribbon). Columns were also auto-sized - though the widths of columns could also be manually specified from R. See the Excel Export vignette for more details.
### More Information
More complex pivot tables can also be created, e.g. with irregular layouts, using multiple data frames, using multiple calculations and/or custom R calculation functions.
See http://www.pivottabler.org.uk/articles for more detailed information.
### More Examples
The following are a few of the example pivot tables constructed in the package vignettes (click to open full sized picture):
