GPTable¶
Mapping¶
The GPTable
Class is used to map your data and metadata to table elements.
The supported table elements are represented like this in the output .xlsx file:
Notes¶
Notes are text elements that appear on the separately generated Notesheet
.
Notes can be referenced in the title
, subtitles
, scope
, source
and legend
elements. Notes corresponding to entries in the data can be
referenced using the table_notes
element. This will add a note reference to
the relevant column heading. Note references cannot be added to data cells, as
inserting references here would reduce the usability of the data. We use double
dollar symbols ($$
) to denote notes in text. For example, a note could be
referenced as "My table title $$Reference$$"
.
References in text are replaced with numbers, in increasing order from the top- left corner of the first sheet containing a data table.
See this in practice under Example Usage.
Note
Deprecated in v1.1.0: Ability to reference notes within
GPTable.table.columns
will be removed in v2 of gptables. Please use
GPTable.table_notes
to ensure references are correctly placed and ordered.
Links¶
Links can added to text using the format [display text](link)
. Links are
supported in the title
, subtitles
, scope
, source``and ``legend
elements. They will also be applied to cells within the data table that use
this format. Links should start with one of the following prefixes:
http://
, https://
, ftp://
, mailto:
, internal:
or
external:
. For more information about the usage of the local URIs, see the
XlsxWriter documentation.
Note
Excel does not support links being applied to specific words within cells. The link will be applied to the whole cell, not just the display text.
Rich Text¶
Rich text is text that contains mixed formatting. You shouldn’t use formatting to represent data or important information, as most formatting is neither accessible nor machine readable. You can still use to make things look appealing for sighted people.
Rich text is supported in the title
, subtitles
, scope
, source
and legend
elements. Where you would normally provide a string to a
parameter, you can instead provide a list of strings and dictionaries.
Dictionaries in this list should contain valid XlsxWriter format properties
and values. The formatting defined in these dictionaries will be applied to the
next string in the list. This formatting is applied in addition to the
formatting of that element specified in the Theme
.
["It is ", {"bold": True}, "inevitable"]
would give you “It is inevitable”.
See this in practice under Example Usage.
Note
Rich text is not currently supported if the cell also contains note references or links. This may be changed in the future if there is sufficient user need, so please raise an issue if this is functionality you need.
Additional formatting¶
In some cases you may want to apply one-off formatting on specific rows, columns or cells of the data. As mentioned above, this formatting should not be used to represent data or important information.
Bespoke formatting can be applied to an individual GPTable
via the additional_formatting
parameter,
when creating a GPTable
instance. This parameter takes a list of dictionaries, where each dictionary
defines formatting for one or more rows, columns or cells.
These dictionaries have a single key indicating the type of selection, from “column”, “row” or “cell”. Their value is another dictionary, which specifies the indexing, formatting and whether row and column indexes are included in the selection.
Indexing supports selection of columns by name or 0-indexed number, but rows and cells can only be indexed by number. Numeric indexing refers to position within the data element of the table (column headings, row indexes and data), not position in the output Excel sheet.
This additional_formatting
parameter is best demonstrated by example:
additional_formatting = [
# Align data center, but not column indexes
{"column":
{"columns": ["some_column", "another_column"], # str, int or list of either
"format": {"align": "center"},
"include_names": False # Whether to include column headings (optional)
}
},
# Align column left, including column index
{"column":
{"columns": [3],
"format": {"left": 1},
"include_names": True
}
},
# Underline the bottom of the table, including row index
{"row":
{"rows": -1, # Numbers only, but can refer to last row using -1
"format": {"bottom": 1}, # Underline row
"include_names": True # Whether to include row indexes
}
},
# A bad example, turning a single cell's font red
{"cell":
{"cells": (3, 3), # tuple or list of tuples (numbers only)
"format": {"font_color": "red"}
}
}
]
For any formatting beyond this, if the package should support it then please raise an issue
or create a pull request. Otherwise, you will need to modify the underlying
GPWorkbook
or GPWorksheet
objects
before they are written to Excel.
See this in practice under Example Usage.
GPTable
Class¶
- class gptables.core.gptable.GPTable(table, table_name, title, scope=None, source=None, units=None, table_notes=None, subtitles=[], instructions='', legend=[], index_columns={2: 0}, additional_formatting=[])[source]¶
A Good Practice Table. Stores a table and metadata for writing a table to excel.
- table¶
table to be written to an Excel workbook
- Type
pandas.DataFrame
- table_name¶
name for table. Should be unique with no spaces and start with a character or underscore.
- Type
str
- title¶
title of the table
- Type
str
- subtitles¶
subtitles as a list of strings
- Type
List[str], optional
- instructions¶
instructions on how to read the sheet. If not provided, defaults to “This worksheet contains one table. Some cells may refer to notes, which can be found on the notes worksheet.”
- Type
str, optional
- scope¶
description of scope/basis of data in table if not included in title
- Type
str, optional
- source¶
description of the source of the data in table if not included in cover
- Type
str, optional
- units¶
units used in each (dict) column of table
- Type
dict, optional
- legend¶
descriptions of special notation used in table
- Type
list, optional
- index_columns¶
mapping an index level to a 0-indexed column as {level: column}. Default is a level two index in the first column ({2: 0}).
- Type
dict, optional
- additional_formatting¶
table-specific formatting for columns, rows or individual cells
- Type
dict, optional
- set_additional_formatting(new_formatting)[source]¶
Set a dictionary of additional formatting to be applied to this table.
- set_index_columns(new_index_columns)[source]¶
Set the index_columns attribute. Overwrites any existing values. A dict must be supplied. This dict should map index level to a single 0-indexed column number. All other columns will be considered as data columns.
- set_legend(new_legend, overwrite=True)[source]¶
Set a list of legend entries to the legend attribute. Overwrites existing legend entries by default. If overwrite is False, new entries are appended to the legend list.
- set_subtitles(new_subtitles, overwrite=True)[source]¶
Set a list of subtitles to the subtitles attribute. Overwrites existing ist of subtitles by default. If overwrite is False, new list is appended to existing list of subtitles.
- set_table(new_table, new_index_columns=None, new_units=None, new_table_notes=None)[source]¶
Set the table, index_columns, units and table_notes attributes. Overwrites existing values for these attributes.