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:

Cells A1 to A6 contain the title, subtitles, instructions, legend, source and scope. These parameters are mapped individually. The next row contains the column headings. Within the same row but on a new line are the units. The table note references are within the same row on a new line under the units. In columns 1, 2 and 3 of the next row down are index levels 1, 2 and 3. In the next columns are the data. Column headings, indices and data are supplied as a pandas DataFrame. Units and table note references are mapped individually.

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.

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

add_legend(new_legend)[source]

Add a single legend entry to the existing legend list.

add_subtitle(new_subtitle)[source]

Add a single subtitle to the existing list of subtitles.

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_instructions(new_instructions)[source]

Set instructions attribute.

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_scope(new_scope)[source]

Set the scope attribute.

set_source(new_source)[source]

Set the source attribute to the specified str.

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.

set_table_name(new_table_name)[source]

Set the table_name attribute.

set_table_notes(new_table_notes)[source]

Adds note references to column headers. table_notes should be in the format {column: “$$note_reference$$”}. Column can be column name or 0-indexed column number in table.

set_title(new_title)[source]

Set the title attribute.

set_units(new_units)[source]

Adds units to column headers. Units should be in the format {column: units_text}. Column can be column name or 0-indexed column number in table.