Full Table Download (CSV) User Guide

DOC version (DOCX, 116.79 KB)

Table of contents

Revision History

Purpose

The purpose of this document is to provide users with a guide to the full table downloadable output files available from the Statistics Canada website.

Objectives/Requirements

The general requirements for the full table download are

• Users can access and download an entire updated data table in CSV (.csv) format from Statistics Canada's website, via a download options button.
• Updated data is available at 8:30am on release day
• Users receive all data and relevant metadata.

General Information

• Download files are unilingual; there is a separate English and French version for each table.
• Previously used CANSIM table numbers have been replaced by numeric Product Identification codes, called PIDs. Each table has a unique PID.
• English downloads are in CSV format and use commas as the separator. French downloads are also in CSV format but use semi-colons as the separator. This is by design to accommodate the decimal display standard in French, which uses a comma (,) instead of the period (.) used in English.
• Data is 'bundled' in a .zip format containing 2 .csv files; a data file containing both data and textual 'stub' information, and a corresponding metadata file. Users receive both on download.
• The .zip filename has the format 'PID-eng.zip' in English and 'PID-fra.zip' in French. The filenames for the data and metadata files have the structures "PID.csv and PID_Metadata.csv', in both English and French.
• Some information (i.e. dimension names, member names) appears in both the data and metadata file. This redundancy is by design. It accommodates the advanced user who requires the ability to sort and filter on the data, and it also allows the novice user to understand the information being presented through the presence of basic descriptions.
• For 2021 Census cubes (98-XXXX-XX) there have been changes introduced in both the metadata and data files, which makes the record layouts for both files different than non-Census cubes. These differences have been described in this document.

Data File (non-Census cubes)

• A Statistics Canada data table contains a minimum of 1 dimension (variable) and a maximum of 10. A standard record layout for the .csv data output file includes a minimum of 14 fields, and always includes a geography dimension. These fields are listed below and described in the record layout section of this document
  • REF_DATE
  • GEO
  • DGUID
  • UOM
  • UOM_ID
  • SCALAR_FACTOR
  • SCALAR_ID
  • VECTOR
  • COORDINATE
  • VALUE
  • STATUS
  • SYMBOL
  • TERMINATED
  • DECIMALS
• The number of fields in the data output file varies based on the complexity of the table. For example, a table with 6 dimensions contains at least 4 more fields in the output file as compared to a table with only 2 dimensions.
• The sort order within the data files has been modified from previous .csv outputs. Data will now be sorted by ref_date in ascending order.
• For data points requiring decimal representation, the value in the data file has the decimal applied. However, by design, an additional separate, specific decimal precision field is also included in the file in order to explicitly identify the decimal precision for each data point.
• The scalar factor description and scalar identification (ID) fields are paired together and are systematically included in the data output file.
• The unit of measure description and unit of measure identification (ID) fields are paired together and are systematically included in the data output file.
• Where symbols are used to replace data on the web (i.e. F and X), these same symbols appear in designated status fields in the downloadable file. When such symbols appear in the status field, the corresponding value field will remain blank.

Data File Record Layout (non-Census cubes)

Ref_date

Reference period for the series being released. Spanned reference periods (2011/12) will be displayed in the same format as on the website.

Dimension name

Name of dimension. There can be up to 10 dimensions in a data table.
(i.e. Geography)

DGUID

Dissemination Geography Unique Identifier - DGUID.
It is an alphanumeric code, composed of four components. It varies from 10 to 20 characters in length. The first 9 characters are fixed in composition and length.
Vintage (4) + Type (1) + Schema (4) + Geographic Unique Identifier (2-11) :
VVVV T SSSS GGGGGGGGGGG

Unit of measure

The unit of measure applied to a member given in text. There can be multiple units of measure in a table. See Appendix B.
(i.e. acres, hectares)

Unit of measure ID

The unique reference code associated with a particular unit of measure. See Appendix B.
(i.e. 28 = acres)

Scalar factor

The scalar factor associated with a data series, displayed as text. There can be multiple scalar factors in a table. See Appendix C.
(i.e. hundreds)

Scalar_ID

The unique numeric reference code associated with a particular scalar factor. See Appendix C.
(i.e. 2 = hundreds)

Vector

Unique variable length reference code time-series identifier, consisting of the letter 'V', followed by up to 10 digits. (i.e. V1234567890, V1, etc.)

Coordinate

Concatenation of the member ID values for each dimension.
One value per dimension.
Maximum of 10 dimensions.
(i.e. 1.1.1.36.1)

Value

Data point value (the decimal is applied to the value where applicable)
(i.e. 12397.13)

Status

Shows various states of a data value using symbols. These symbols are described in the symbol legend and notes contained in the metadata file. Some symbols accompany a data value while others replace a data value. i.e. – A, B, C, D, E, F,.., X, 0s
Standard table symbols

Symbol

Describes data points that are preliminary or revised, displayed using the symbols p and r. These symbols accompany a data value.

Terminated (where applicable)

Describes a data value that has been terminated (no longer updated) by Statistics Canada. Termination of a data point is displayed using the symbol t.

Decimals

This field displays the decimal precision for a given value.

Metadata File (non-Census cubes)

• The archived status of a file is indicated in the metadata file. The options for this field are
  • ARCHIVED - a table publicly available but no longer being updated
  • CURRENT - a table available to the public and that is still being updated
• A comprehensive symbol legend describing the symbols is presented in the metadata file, regardless of whether a selected symbol appears in a particular table.
• Data quality symbols A through D found in the data file are described in a note at the bottom of the metadata file.
• Notes and their unique reference numbers appear in the metadata file.
• Notes can be applied at the table, dimension, and member level.
• If a table undergoes a correction, the correction is recorded in the in the metadata file in three fields; correction ID, correction date, and correction note.

Metadata File Record Layout (non-Census cubes)

*please note that the word "cube" is used interchangeably with the word 'table'

Cube Title

The title of the table. The output files are unilingual and thus will contain either the English or French title.

Product Id (PID)

The unique 8 digit product identifier for the table.

CANSIM Id

The ID number which formally identified the table in CANSIM. (where applicable)

URL

The URL for the representative (default) view of a given data table.

Cube Notes

Each note is assigned a unique number. This field indicates which notes, if any, are applied to the entire table.

Archive Status

Describes the status of a table as either 'Current' or 'Archived'. Archived tables are those that are no longer updated.

Frequency

Frequency of the table.
(i.e. annual)
See Appendix A.

Start Reference Period

The starting reference period for the table.

End Reference Period

The end reference period for the table.

Total Number of Dimensions

The total number of dimensions contained in the table.

Dimension Name

The name of a dimension in a table. There can be up to 10 dimensions in a table.
(i.e. – Geography)

Dimension ID

The reference code assigned to a dimension in a table. A unique reference Dimension ID code is assigned to each dimension in a table.

Dimension Notes

Each note is assigned a unique number. This field indicates which notes are applied to a particular dimension.

Dimension Definitions

Reserved for future development.

Member Name

The textual description of the members in a dimension.
(i.e. – Nova Scotia, Ontario (members of the Geography dimension))

Member ID

The code assigned to a member of a dimension. There is a unique ID for each member within a dimension. These IDs are used to create the coordinate field in the data file. (see the 'coordinate' field in the data record layout).

Classification (where applicable)

Classification code for a member.
Definitions, data sources and methods

Parent Member ID

The code used to display the hierarchical relationship between members in a dimension.
(i.e. – The member Ontario (5) is a child of the member Canada (1) in the dimension 'Geography')

Terminated

Indicates whether a member has been terminated or not. Terminated members are those that are no longer updated.

Member Notes

Each note is assigned a unique number. This field indicates which notes are applied to each member.

Member definitions

Reserved for future development.

Symbol Legend

The symbol legend provides descriptions of the various symbols which can appear in a table. This field describes a comprehensive list of all possible symbols, regardless of whether a selected symbol appears in a particular table.
Standard table symbols

Survey Code

The unique code associated with a survey or program from which the data in the table is derived. Data displayed in one table may be derived from one or multiple surveys or programs.

Survey Name

The name of the survey (s)/program. Data displayed in one table may derived from one or multiple surveys or programs.

Subject Code

The unique 2 digit ID associated with the subject or subjects for which the table is associated. The code can be from 2 to 6 digits depending on where the table has been tagged in the subject hierarchy.

Subject Name

The descriptive text of the subject (s) for which a table is associated. This text will show the hierarchal relationship between subjects if such a relationship exists.
 (i.e. Health/Diabetes)

Note ID

The code assigned to a particular note in a table.

Note

The actual textual note which can contain information at the table, dimension, and members levels. These notes are linked using the table notes, dimension notes, and member notes fields.

Definitions

Reserved for future development.

Correction ID

The code assigned to a correction.

Correction date

The date of a correction.

Correction note

The description of a correction that has taken place on a table.

Data File (Census of Population cubes: 98-XXXX-XX)

Transposition

The primary difference in the layout of the data file for Census of Population cubes and non-Census cubes is the 'transposing 'of the data that has been applied. This has been done to reduce the size and maximize the usability of the data file. Simply put, for 2021 Census of Population files, the last dimension in a cube is transposed so that the data points appear vertically (in columns when viewing the file in Excel). This differs from non-Census cubes, which have one datapoint per row.

2021 Census of Population files are transposed by moving the last dimension of a cube and its associated members into columns. The headers for these data columns will express the total number of members in a dimension as well as the specific member, including the applicable coordinate value, within that dimension which the column represents. A user can determine the last dimension in a cube by viewing its metadata file.

98-10-0001 metadata file (this image does not represent the entire file)

To illustrate, see the full table download .csv data file for cube 98-10-0001 (2021 Census of Population). In this image, you can see that the last dimension, in this case 'Population and dwelling counts', has been transposed into columns.

98-10-0001 data file (this image does not represent the entire file)

The headings of each column are now structured in the following manner. Population and dwelling counts (11): Population, 2021 [1] where;

In this example, there will be 11 columns of data representing the 11 members of the 'Population and dwelling counts' dimension. The coordinate for any datapoint will be the concatenation of the coordinate for the GEO dimension (this value appears in the coordinate field) and the value of coordinate for a specific member, as shown in the column header.

Record Layout

• The transposing of 2021 Census of Population cubes has resulted in a record layout that differs from non-Census cubes. The record layout for these cubes contains the following fields.
  • REF_DATE
  • GEO
  • DGUID
  • COORDINATE
  • DIMENSION NAME AND MEMBER NAME
  • SYMBOLS
• The number of fields in the data output file varies based on the number of members in the transposed last dimension.
• Symbols fields (columns) have been added for each data column to allows for instances where symbols may be necessary (data quality, suppression etc.)

Data File Record Layout (Census of Population cubes: 98-XXXX-XX)

Ref_date

Reference period for the series being released.

GEO

The geographic dimension. The members of this dimension will differ based on the particular cube.

DGUID

Dissemination Geography Unique Identifier - DGUID.

It is an alphanumeric code, composed of four components. It varies from 10 to 20 characters in length. The first 9 characters are fixed in composition and length.

Vintage (4) + Type (1) + Schema (4) + Geographic Unique Identifier (2-11) :

VVVV T SSSS GGGGGGGGGGG

Coordinate

Concatenation of the member ID values for all dimensions, excluding the last 'transposed' dimension.
To get the full coordinate for a datapoint, a user will have to concatenate the value from the 'Coordinate field' and the coordinate value from the column header for a specific member from the last 'transposed' dimension.

Dimension/Member

Transposed last dimension and member name.

Symbol

Field used to associate any necessary symbols to specific datapoints.

Metadata File (Census of Population cubes: 98-XXXX-XX)

The metadata file for 2021 Census of Population cubes is very similar to the metadata file for non-Census cubes, with some notable exceptions. Additional fields included within the Census cubes include;

• Universe
• Variable List
• Dimension Correction Notes
• Member Correction Notes
• Member Geo Attribute Keys
• Various Geographic Attributes

*please note that the word "cube" is used interchangeably with the word 'table'

Metadata File Record Layout (Census of Population cubes: 98-XXXX-XX)

Cube Title

The title of the table. The output files are unilingual and thus will contain either the English or French title.

Product Id (PID)

The unique 8 digit product identifier for the table.

CANSIM Id

The ID number which formally identified the table in CANSIM. (where applicable)

URL

The URL for the representative (default) view of a given data table.

Cube Notes

Each note is assigned a unique number. This field indicates which notes, if any, are applied to the entire table.

Archive Status

Describes the status of a table as either 'Current' or 'Archived'. Archived tables are those that are no longer updated.

Frequency

Frequency of the table.
(i.e. annual)
See Appendix A.

Start Reference Period

The starting reference period for the table.

End Reference Period

The end reference period for the table.

Total Number of Dimensions

The total number of dimensions contained in the table.

Universe

The Universe label found within Census data tables provides three main pieces of information.
1) The population of interest or focus of the table i.e. Population aged 15 years and over in private households;
2) The Census years the data are representing i.e. 2021 Census; 2021 and 2016 Censuses;
3) The Census form that data was collected i.e. short form 100% Data or long form 25% Sample Data.

Variable List

The Variable label found within census tables, indicates the Variable name along with the number of categories i.e. Age (127) indicates it is an age variable with 127 categories for age.

Dimension Name

The name of a dimension in a table. There can be up to 10 dimensions in a table.

(i.e. – Geography)

Dimension ID

The reference code assigned to a dimension in a table. A unique reference Dimension ID code is assigned to each dimension in a table.

Dimension Notes

Each note is assigned a unique number. This field indicates which notes are applied to a particular dimension.

Dimension Correction Notes

Corrections applied at the dimension level.

Dimension Definitions

Reserved for future development.

Member Name

The textual description of the members in a dimension.

(i.e. – Nova Scotia, Ontario (members of the Geography dimension))

Member ID

The code assigned to a member of a dimension. There is a unique ID for each member within a dimension. These IDs are used to create the coordinate field in the data file. (see the 'coordinate' field in the data record layout).

Member Correction Notes

Corrections applied at the member level.

Member Geo Attribute Keys

Numeric code indication which geographic attributes apply to individual members.

Classification (where applicable)

Classification code for a member.
Definitions, data sources and methods

Parent Member ID

The code used to display the hierarchical relationship between members in a dimension.

(i.e. – The member Ontario (5) is a child of the member Canada (1) in the dimension 'Geography')

Terminated

Indicates whether a member has been terminated or not. Terminated members are those that are no longer updated.

Member Notes

Each note is assigned a unique number. This field indicates which notes are applied to each member.

Member definitions

Reserved for future development.

Symbol Legend

The symbol legend provides descriptions of the various symbols which can appear in a table. This field describes a comprehensive list of all possible symbols, regardless of whether a selected symbol appears in a particular table.

Standard table symbols

Survey Code

The unique code associated with a survey or program from which the data in the table is derived. Data displayed in one table may be derived from one or multiple surveys or programs.

Survey Name

The name of the survey (s)/program. Data displayed in one table may derived from one or multiple surveys or programs.

Note ID

The code assigned to a particular note in a table.

Note

The actual textual note which can contain information at the table, dimension, and members levels. These notes are linked using the table notes, dimension notes, and member notes fields.

Definitions

Reserved for future development.

Correction ID

The code assigned to a correction.

Correction date

The date of a correction.

Correction note

The description of a correction that has taken place on a table.

Various Geographic Attributes

Within the various download tables a variety of geographic attributes may be available for the geography member, these could include:

• GeoName – the name of the geographic area;
• GeoTypeDesc – if a geography has a type description, this with be the description in full i.e. City for Ottawa, City;
• GeoTypeAbbr – if a geography has a type, this will be the abbreviation of the geography's type i.e. CV for City; Ottawa, CV
• GeoLevelDesc – this is the level of the geography i.e. Census Subdivision;
• DGUID – this is a unique geography identifier, that includes the vintage of the geographic boundaries, whether is it an administrative or statistical unit, a four digit geographic level identifier then the geography's geographic code;
• AltGeocode – is an alternate geographic code that is usually equal to the ending digits of the DGUID This code is often the geographic code found in previous census cycle products;
• DqfCode – this is a five digit data quality string about data quality information for the geography i.e. whether an area is suppressed due to confidentiality;
• DqfNote – this can be the written expression of the five digit data quality string;
• TnrShortForm – this value would be the total non-response rate to the short-form questionnaire.

Appendices

Appendix A - Frequency

Appendix B - Units of Measure

Appendix C - Scalar Factors