-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathREADME.Rmd
309 lines (218 loc) · 17.6 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
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "man/figures/README-",
out.width = "100%"
)
```
# accessibletablesR <a href="https://minifiemj.github.io/accessibletablesR/"><img src="man/figures/logo.png" align="right" height="139"/></a>
```{r, echo = FALSE}
# badge_repostatus from GuangchuangYu's badger package on Github
badge_repostatus <- function(status, alt = NULL) {
status <- tolower(status)
svg <- paste0("https://www.repostatus.org/badges/latest/", status, ".svg")
url <- paste0("https://www.repostatus.org/#", status)
if (is.null(alt)) {
alt <- switch(
status,
concept = "Concept - Minimal or no implementation has been done yet, or the repository is only intended to be a limited example, demo, or proof-of-concept.",
wip = "WIP - Initial development is in progress, but there has not yet been a stable,
usable release suitable for the public.",
suspended = "Suspended - Initial development has started, but there has not yet been a
stable, usable release; work has been stopped for the time being but the
author(s) intend on resuming work.",
abandoned = "Abandoned - Initial development has started, but there has not yet been a
stable, usable release; the project has been abandoned and the author(s) do not
intend on continuing development.",
active = "Active - The project has reached a stable, usable state and is being actively
developed.",
inactive = "Inactive - The project has reached a stable, usable state but is no longer
being actively developed; support/maintenance will be provided as time
allows.",
unsupported = "Unsupported - The project has reached a stable, usable state but the author(s)
have ceased all work on it. A new maintainer may be desired.",
moved = "Moved to http://example.com - The project has been moved to a new location,
and the version at that location should be considered authoritative.",
stop("invalid status: ", status)
)
alt <- paste("Project Status:", alt)
}
badge_out <- paste0("[![", alt, "](", svg, ")](", url, ")")
if (status == "moved") {
warning("Please remember to replace 'http://example.com' with the new URL")
paste(badge_out, "to [http://example.com](http://example.com)")
} else {
paste(badge_out)
}
}
```
`r badge_repostatus("Active")`
`r badger::badge_github_version("minifiemj/accessibletablesR", "green")`
`r badger::badge_cran_release("accessibletablesR", "orange")`
`r badger::badge_license("MIT")`
`r badger::badge_last_commit("minifiemj/accessibletablesR")`
accessibletablesR is designed to produce Excel workbooks that align as closely as possible to the UK Government Analysis Function recommendations for publishing statistics in spreadsheets.
[Releasing statistics in spreadsheets](https://analysisfunction.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/)
accessibletablesR was developed using R 4.1.3, dplyr version 1.1.2, openxlsx version 4.2.5.2, conflicted version 1.2.0, stringr version 1.5.0, purrr version 1.0.1 and rlang version 1.1.0. It is unknown if the package will work with earlier versions of R, dplyr, openxlsx, conflicted, stringr and purrr. accessibletablesR will install the latest versions of dplyr, openxlsx, conflicted, stringr and purrr if these packages are not currently installed or if earlier versions of dplyr (<1.1.2), openxlsx (<4.2.5.2), conflicted (<1.2.0), stringr (<1.5.0), purrr (<1.0.1) and rlang (<1.1.0) are currently installed.
## Installation
To install accessibletablesR:
```{r, eval = FALSE}
if (!("devtools" %in% utils::installed.packages()))
{utils::install.packages("devtools", dependencies = TRUE, type = "binary")}
devtools::install_github("minifiemj/accessibletablesR", build_vignettes = TRUE)
```
If a firewall prevents install_github from working (a time out message may appear) then install the package manually. On the GitHub repo, go to the green "Code" icon and choose "Download ZIP". Copy the ZIP folder to a network drive. Use devtools::install_local(<link to the zipped folder>) to install the package.
## Final output
accessibletablesR allows for a workbook to have a cover page, a table of contents, a notes page, a definitions page and as many other tabs that the user requires (subject to the maximum
number allowed by Excel). Only one table of data can be present on each tab. accessibletablesR
cannot work with multiple tables on a tab.
The final output can be xls, xlsx or ods. It is not recommended to produce xls files. An ods file
can be produced only after an xlsx file has been produced first. Please consider producing ods files for accessibility reasons.
## Functions
accessibletablesR contains nine main functions - workbook, creatingtables, contentstable, coverpage, addnote, notestab, adddefinition, definitionstab and savingtables.
## workbook
```{r, eval = FALSE}
workbook <- function(covertab = NULL, contentstab = NULL, notestab = NULL, autonotes = NULL,
definitionstab = NULL, fontnm = "Arial", fontcol = "black",
fontsz = 12, fontszst = 14, fontszt = 16, title = NULL, creator = NULL,
subject = NULL, category = NULL)
```
This function needs to be run first. It creates the workbook within the R environment. All
parameters are optional. The user can specify whether they want a cover page, table of contents, a notes page and a definitions page. The font name, colour and sizes can be modified if desired. Information regarding the final spreadsheet (File > Info > Properies: title, creator, subject and category) can also be specified. There is an option for the automatic display of notes on the applicable tabs.
The default font (fontnm) is Arial, the default colour (fontcol) is black, the default normal size (fontsz) is 12, the default subtitle size (fontszst) is 14 and the default title size (fontszt) is
16.
If a coverpage is wanted set covertab = "Yes". If a table of contents is wanted set contentstab = "Yes". If a notes page is wanted set notestab = "Yes". If a definitions page is wanted set definitionstab = "Yes". If the automatic display of notes on the applicable tabs is wanted set autonotes = "Yes".
To set some of the spreadsheet information, amend title, creator, subject or category.
## creatingtables
```{r, eval = FALSE}
creatingtables <- function(title, subtitle = NULL, extraline1 = NULL, extraline2 = NULL,
extraline3 = NULL, extraline4 = NULL, extraline5 = NULL,
extraline6 = NULL, sheetname, table_data, headrowsize = NULL,
numdatacols = NULL, numdatacolsdp = NULL, othdatacols = NULL,
datedatacols = NULL, datedatafmt = NULL, datenondatacols = NULL,
datenondatafmt = NULL, tablename = NULL, gridlines = "Yes",
columnwidths = "R_auto", width_adj = NULL, colwid_spec = NULL)
```
This function takes the raw data and transfers them into an accessible data table for the final
Excel workbook. The raw data must be in a dataframe within the global environment. The raw data need
to be ordered as desired and contain the columns desired in the right position and named
accordingly.
The creatingtables function needs to be run after the workbook function and run as many times as
there are tables which are to be put in tabs in the final workbook.
Three of the parameters (title, sheetname, table_data) are compulsory, the others are optional.
title is the title of the table that will be displayed in the Excel workbook tab. sheetname is the
name of the tab wanted for the Excel workbook. table_data is the dataframe in the R global
environment to be outputted.
As well as a title, it is possible to include a subtitle and six additional lines above the table in the final Excel workbook tab. Populate the parameters subtitle, extraline1, extraline2, extraline3, extraline4, extraline5 and extraline6 if a subtitle and/or extra lines are wanted. If a link to the contents page or notes page or definitions page is desired, then set one of the extraline parameters to "Link to contents" or "Link to notes" or "Link to definitions". The extraline parameters can be supplied as vectors and so there is no maximum limit to the number of rows that can come before the main data other than the limit of rows in an Excel spreadsheet.
headrowsize adjusts the height of the row containing the table column names.
If the automatic formatting of columns containing data in the form of numbers is required then the
user should populate numdatacols and numdatacolsdp. numdatacols should contain the numerical
positions of the columns in the dataframe. numdatacolsdp is the required number of decimal places
for each column containing data in the form of numbers. Populating numdatacols and numdatacolsdp ensures that decimal places and thousand commas will be sorted for the final Excel workbook
regardless of if the numbers are numerical or stored as text.
If there are columns containing data not in the form of numbers (e.g., text) then the user can
populate othdatcols with the appropriate numerical positions of columns to ensure the automatic formatting of the columns in the final workbook. Columns with dates will not be properly formatted
using othdatacols. Instead of using othdatacols, the parameters datedatacols, datedatafmt,
datenondatacols and datenondatafmt should be used. Populating datedatacols and datedatafmt will
ensure that dates are aligned top and right in data columns. Populating datenondatacols and
datenondatafmt will ensure that dates are aligned top and left in non-data columns.
If the user wants to give a name to a table in the final Excel workbook which is different to the
tab name (sheetname) then populate tablename.
If the gridlines are not desired in the final workbook, set gridlines = "No".
Automatic column widths can be a bit hit or miss. The default position of the creatingtables
function is to allow openxlsx to automatically determine the column widths. The user can instead use the maximum number of characters in a column by setting columnwidths = "characters". width_adj can
be adjusted as an extra bit of width to add on to the width determined by the number of characters.
If the user knows the desired widths of all columns then they should set columnwidths = "specified"
and populate colwid_spec with the width of each column in a numerical vector. If default column
widths are wanted then set columnwidths = NULL.
extralines1-6 can be set to hyperlinks if desired. An example of how to do this is:
```{r, eval = FALSE}
extraline5 = "[BBC](https://www.bbc.co.uk)"
```
It is recommended not to set the link to the contents, notes or definitions page in this way.
## contentstable
```{r, eval = FALSE}
contentstable <- function(gridlines = "Yes", colwid_spec = NULL, extracols = NULL)
```
If a contents page is wanted then run contentstable(). Run the function after all the data tables
have been processed using the creatingtables function.
The parameters are optional. If no gridlines are wanted in the contents page in the final workbook
set gridlines = "No". Column widths are determined automatically but the user can specify the widths
by populating colwid_spec. Extra columns can be provided. To do so, set extracols = "Yes" and create
a dataframe called extracols_contents in the global environment before running the contentstable function. The extracols_contents dataframe must contain the desired extra columns and have the same number of rows as the contents table.
## coverpage
```{r, eval = FALSE}
coverpage <- function(title, intro = NULL, about = NULL, source = NULL, relatedlink = NULL,
relatedtext = NULL, dop = NULL, blank = NULL, names = NULL, email = NULL,
phone = NULL, reuse = NULL, govdept = NULL, gridlines = "Yes",
extrafields = NULL, extrafieldsb = NULL, additlinks = NULL, addittext = NULL,
colwid_spec = NULL, order = NULL)
```
title is the only compulsory parameter. It is the title to be displayed on the cover sheet. Other optional sections of a cover page that can be populated are "Introductory information" (info),
"About these data" (about), "Source" (source), "Related publications" (relatedlink, relatedtext),
"Date of publication" (dop), "Blank cells" (blank), "Contact" (names, email, phone),
"Additional links" (additlinks) and "Reusing this publication" (reuse, govdept). Extra fields can be added using extrafields. One row is allowed for each extra field. The text to populate the extra
fields can be provided in extrafieldsb. If no gridlines are wanted on the cover page in the final workbook set gridlines = "No". The column width is automatically set but can be altered by using colwid_spec.
The ordering of the fields can be amended by populating order. order can be set to a vector where
the field names are provided in speech marks.
The "Reusing this publication" section has been designed for UK government departments and will not apply for other organisations. If a user is from the Office for National Statistics (ONS) and wants
a "Reusing this publication" section then set reuse = "Yes". If a user is from a UK government department but not the Office for National Statistics (ONS) set reuse = "Yes" and govdept = "name
of organisation".
intro, about, source, dop, blank, names and phone can be set to hyperlinks if desired. An example of how to do so is:
```{r, eval = FALSE}
source = "[ONS](https://www.ons.gov.uk)"
```
## addnote
```{r, eval = FALSE}
addnote <- function(notenumber, notetext, applictabtext = NULL, linktext1 = NULL, linktext2 = NULL)
```
Run this function for as many notes as are needed.
notenumber is the number of the note and should be written as "note" followed by a number (e.g., note1).
notetext is the description associated with the note.
notenumber and notetext are the only compulsory parameters. If an additional column is wanted to specify which table (sheet name) a note applies to then populate applictabtext (e.g.,
applictabtext = c("Table_1", "Table_2")).
An optional column can be included that provides a link to a piece of information. To do so,
populate linktext1 and linktext2. For example, set linktext1 = "General health information" and set linktext2 to the relevant URL address.
## notestab
```{r, eval = FALSE}
notestab <- function(contentslink = NULL, gridlines = "Yes", colwid_spec = NULL, extracols = NULL)
```
Run this function if a notes page is wanted.
Run the function after all the data tables have been processed using the creatingtables function and after all the notes have been added using the addnote function.
The parameters are optional. If a link to the contents page is not wanted on the notes page set contentslink = "No". If no gridlines are wanted on the notes page then set gridlines = "No". Column widths are determined automatically but can be altered to specific widths by the user in
colwid_spec. Extra columns can be provided. To do so, set extracols = "Yes" and create a dataframe called extracols_notes in the global environment before running the notestab function. The extracols_notes dataframe must contain the desired extra columns and have the same number of rows as the notes table.
## adddefinition
```{r, eval = FALSE}
adddefinition <- function(term, definition, linktext1 = NULL, linktext2 = NULL)
```
Run this function for as many definitions as are needed.
term is the item that needs defining and definition is the definition of the item.
An optional column can be included that provides a link to a piece of information. To do so,
populate linktext1 and linktext2. For example, set linktext1 = "General health information" and set linktext2 to the relevant URL address.
## definitionstab
```{r, eval = FALSE}
definitionstab <- function(contentslink = NULL, gridlines = "Yes", colwid_spec = NULL, extracols = NULL)
```
Run this function if a definitions page is wanted.
Run the function after all the definitions have been added using the adddefinition function.
The parameters are optional. If a link to the contents page is not wanted on the definitions page
set contentslink = "No". If no gridlines are wanted on the definitions page then set
gridlines = "No". Column widths are set automatically but can be altered using colwid_spec. Extra columns can be provided. To do so, set extracols = "Yes" and create a dataframe called extracols_definitions in the global environment before running the definitionstab function. The extracols_definitions dataframe must contain the desired extra columns and have the same number of
rows as the definitions table.
## savingtables
```{r, eval = FALSE}
savingtables <- function(filename, odsfile = "No", deletexlsx = NULL)
```
This function should be run last and will output the final xlsx workbook and/or ods workbook. Note
that it is possible to save as a xls workbook, but this is not advised. Initially saving as a xls
file will also not allow for an ods workbook to be created.
filename is the location and name of the final workbook.
The default setting is to keep the xlsx workbook. If an ods file is wanted, set odsfile = "Yes". If both the ods and xlsx files are wanted, set odsfile = "Yes" and deletexlsx = "No".
## Contact
Please submit any suggestions or report bugs: https://github.com/minifiemj/accessibletablesR/issues
Or email me: minifiemj@gmail.com