ud.Rmd

---
title: "Underlying Data Standards"
---

```{r include=FALSE}
require(knitr)
require(readxl)

apprenticeship <- read_excel("data_examples/apprenticeship_example.xlsx")
metadata_data_example <- read_excel("data_examples/metadata_data_example.xlsx")

```

<p class="text-muted">Guidance on how to structure data files.</p>

---

# Introduction

---

These standards provide general principles of best practice that can be applied to any data that is produced. The aim is to apply a consistent, logical, structure to all data files so that they are easier to use and analyse, minimising the time spent cleaning the data. Adopting these principles will give us more power to serve the needs of the users, saving both us and them time when producing and using the data. This will also open up further opportunities for linking data, including connecting to cross government data initiatives such as the GSS data project. There is also an automated process for checking data against these standards, please see [how to check against the standards](#how-to-check-against-the-standards) at the bottom of the page. 

Our publication data is released under the terms of the [Open Government License](http://www.nationalarchives.gov.uk/doc/open-government-licence/){target="_blank" rel="noopener noreferrer"} and by following these standards will meet at least 3 stars for [Open Data](https://www.gov.uk/government/policies/improving-the-transparency-and-accountability-of-government-and-its-services/supporting-pages/releasing-data-in-open-formats){target="_blank" rel="noopener noreferrer"}.

---


> “Tidy datasets are all alike but every messy dataset is messy in its own way.” – Hadley Wickham


---

This guide aims to help statistical production teams understand and meet the standards required. This includes:

- [General file requirements](#general-requirements)

- [Observational units](#observational-units)

- [Filters](#filters)

- [Indicators](#indicators)

- [Metadata requirements for Explore Education Statistics](#ees-metadata)

- [How to check your data against these standards and next steps](#how-to-check-against-the-standards)


If you have any questions, or would like any further advice on what you read here, contact the [Statistics Development Team](mailto:statistics.development@education.gov.uk).

---

# General requirements

---

When publishing statistics, you should be following these standards for underlying data files.


For publishing on EES specifically, please note the following points:


- Data files uploaded to Explore Education Statistics will be downloadable, **everything in your file must be publishable**.
- You must remember to apply all of your usual suppression policies.
- The data files run the table and chart tools, so any data that you wish to create a table or chart with must be included.
- We expect that multiple data files may be provided for each release.
- Keep in mind that a data block can only have a single data source. However, many blocks can share one source. 
- The data published varies by release, in granularity of breakdowns and the indicators included within data files. 
- You should include all years of the data you available in your data file to facilitate time series analysis.
- Each data file must be accompanied by a corresponding .csv metadata file as outlined under [EES Metadata](#ees-metadata).

<div class="alert alert-dismissible alert-danger">
  In light of recent accessibility legislation, you should aim to include at least 3 years of data in EES where it exists.
</div>

---

## Tidy data structure

---

The move to the new platform provided the ideal opportunity to standardise and 'tidy' up our data to give users the consistency that they ask for. Our standards draw upon the ideas of tidy data - this means applying a logical structure to datasets so they are easier to use and analyse, minimising the time spent cleaning the data before use. 

Here is a quick summary video of what exactly tidy data is. 

<center>
<iframe width="560" height="315" src="https://www.youtube-nocookie.com/embed/oQuupzfX9OQ" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</center>

---

Further details on tidy data, can be found by reading Hadley Wickham's academic paper on [Tidy Data](https://www.jstatsoft.org/index.php/jss/article/view/v059i10/v59i10.pdf){target="_blank" rel="noopener noreferrer"}. The key principles to remember are:


- **Each variable forms a separate column.**
- **Each observation forms a separate row.**


The variables (columns) in each of the uploaded data files will fall in to the following three categories. The standards for each category are discussed in detail later on this page.


- **[Observational units](#observational-units)** - time and geography identifiers
- **[Filters](#filters)** - the things we filter our data on, e.g. school types or learner characteristics
- **[Indicators](#indicators)** - the things we measure, e.g. pupil numbers or percentage of online applications

---

## Data format

---

These standards give you the power to format the data in way that best meets the needs of the users. There are only a handful of formatting standards to follow to ensure best practice and consistency across all of our data.

Filenames should only include numbers, letters, underscores or hyphens. Special characters must be avoided; for example, the following characters `\ / : * ? " < > | [ ] & $ , . +` are all considered special characters and are used for specific tasks in an electronic environment, which can lead to confusion in some systems. The use of non-English language letters such as á, í, ñ, è, and õ, should also be avoided.

<div class="alert alert-dismissible alert-danger">
Data files must in comma separated values (.csv) format, and use UTF-8 encoding. You can specify this when saving the file in Excel, or exporting from elsewhere.
</div>

Variable names must be in the first row of your file as the header row, must not include spaces, and ideally be formatted in [snake_case](https://en.wikipedia.org/wiki/Snake_case){target="_blank" rel="noopener noreferrer"} for ease of use. **Avoid starting variable names with a numeric character.**

If you need to use commas within a cell, then you must add a text delimiter such as quotes to your file to define each cell - this is often done automatically for you, though if you're unsure then you can open up your csv file in a texteditor such as notepad to check. 

You should also ensure that your data follows the [GSS Standards on symbols](https://gss.civilservice.gov.uk/wp-content/uploads/2017/03/GSS-Website-Harmonised-Symbols-Supporting-Documentation.pdf){target="_blank" rel="noopener noreferrer"}, though be aware to ignore the ask that symbols are included in separate cells from the data, which is unpractical and unrealistic.

<div class="alert alert-dismissible alert-danger">
Data and metadata .csv file names must not contain any spaces. All file names should be in lower case. Any upper case characters in filenames will be forced to lower case by EES, and will appear as lower case to the users. 
</div>

---

### How to export data with UTF-8 encoding

---

Most of the time our data is exported as a .csv file it will have UTF-8 encoding by default. However, there are times when this isn't the case, and therefore we'll quickly run through how to check this below in each of Excel, SQL, and R.

**Excel**

Excel tends to save all .csv files as UTF-8, however this is not always the case, particularly if there are symbols in the file (such as £). To ensure that it saves with UTF-8 encoding you can select the following when saving a file:

`File > Save As > CSV UTF-8 (Comma delimited) (*.csv)`

**SQL**

For saving results out of SQL as a .csv file there isn't an option to specify the encoding, therefore the best bet is to either open the file in Excel and specify that as above, or to run your SQL query/read your data into R and follow the guidance below. 

**R**

When writing .csv files out of R, you'll mostly likely be using either `write.csv()` from base R, or `write_csv()` from the readr package. For the first one, you can specify encoding using `encoding = ` like the following example:


`write.csv(my_data, file = "my_data_file.csv", encoding = "UTF-8")`


For `write_csv()`, which some of you may be using for increased processing speed, automatically encodes as UTF-8 format, meaning that you don't have to do anything different!

---

## How much data to publish

---

You should publish as many years of data that you have and is practicable to do so. 

There will be scenarios were significant methodological changes impact the ability to have all of your data in a single file, and in these cases it may be practical to use a 'cut-off' point and link to any older publications containing data before that cut-off. These cut off points should be 4 or more years going backwards. We expect a minimum of 3 years of data in all time-series where it exists.

If you are not providing a full timeseries, make sure that any old published data is linked to from your publication, and that it's omission is explained in your methodology and metadata documents.

---

## Deciding what should be in a file

---

Explore Education Statistics is designed to give production teams the freedom of controlling what data users can access, and how they access it. It is expected that most releases on the platform will have multiple data files, and teams have control over how they break these files up.


The first key consideration is that the table tool will only create tables from a single a data file, and cannot use multiple files as sources. Therefore any data that you want to compare within a single table must in the same data file. The table tool itself is there to allow users to narrow down the amount of data they have to absorb and to be able to efficiently take away key statistics. 


A useful way to judge how to break up data files is to consider whether all of the data in the file is appropriate to show side-by-side in the same table. If there are data that are conceptually different or may be confusing to compare side by side, then these should be in separate data files. Any data file uploaded to EES is usable by all users in the table tool, and users will be able to download the exact same files as you upload.

---

<div class="jumbotron">

  <h3>Remember</h3>

  <hr class="my-4">

- Users will download a copy of the same file that you upload.
- As long as your data meets the standards, you can include as much as you want in a single file. 
- Remember that the larger the file is to upload, and also the larger it is for users to download and then have to process.
- Different types of years (academic/calendar) should not be mixed in the same file, see [observational units](#observational-units).
- If you have a lot of blank or empty indicator cells, consider breaking the files up so that the indicator columns are relevant to as many rows as possible.

</div>

---

## Data symbols

---

In line with the [GSS guidance](https://gss.civilservice.gov.uk/wp-content/uploads/2017/03/GSS-Website-Harmonised-Symbols-Supporting-Documentation.pdf){target="_blank" rel="noopener noreferrer"}, special values should be replaced with symbols in the following situations:


| Symbol      | Usage                                         | Example                                                   |        
|-------------|-----------------------------------------------|-----------------------------------------------------------|
| z           | When an observation is **not applicable**     | No data for at gender level for boys at an all-girls school|
| :           | When data is **unavailable** for other reasons| Data for an indicator is not collected in a certain region|
| c           | **Confidential** data | Data has been suppressed |
| ~        | Rounds to 0, but is not 0 | Rounding to the nearest thousand, 499 would otherwise show as 0. Only use 0 for true 0's |


If you have any other conventions you've used in previous publications, or a scenario that isn't covered above, check the [GSS guidance](https://gss.civilservice.gov.uk/wp-content/uploads/2017/03/GSS-Website-Harmonised-Symbols-Supporting-Documentation.pdf){target="_blank" rel="noopener noreferrer"} (ignoring the part around separate columns for symbols), and contact us.

--- 


## File size

---

There are no character or size limits in a csv file, but you should consider the restrictions the users will face when downloading and processing the data (excel has a cell character limit of 32,760 for example). There is no size limit for EES, we are still testing the service with large files. Ideally we want to develop the platform to work with the largest files we can produce. Do remember though that the larger a file is, the longer it will take to upload and process. Also remember that the files you upload are the files that users will download, consider the software they may access to (e.g. Excel) and whether the size of your files are compatible with this.

Excel has a row limit of 1,048,576. It is best to avoid exceeding this as some end users may struggle to open the file. One good way to cut the file down is to split after a certain number of years, or to separate out different geographic levels into separate files, providing school level data as a seperate file for example. With the data all being in a tidy format these are then easy enough for secondary analysts to stitch back together if needed.

People can tend to obsess over file size, however this is not everything, and the balance of rows and columns, and the contents of them, has a large impact on performance. We don't yet know the full extent of this, and with that caveat, a **rough guide** based on size would be:

- Anything under 10mb is relatively small
- 10mb to 100mb is a fairly common file size that most teams have
- 100mb to 500mb is fairly large file and may struggle to upload if not compressed - [contact us](mailto:mailto:statistics.development@education.gov.uk) if you have any issues
- 500mb and over are pretty large, and sometimes may struggle to compress small enough to upload, and can also start to be difficult to handle, as you may be aware if you have one!
- Files that are over 1gb in size will likely have issues in the platform, please [contact us](mailto:mailto:statistics.development@education.gov.uk) if you have any this large


We generally recommend fewer large files over a larger number of smaller files. **If you think you are having issues with file size please tell us** so that we can investigate and work towards a solution with you. **The platform is always in development**, and we do not want teams to arbitrarily make decisions about their data (and potentially waste a lot of time) due to something that could be addressed if we are told about it.

---

<div class="jumbotron">

  <h3>Remember</h3>

  <hr class="my-4">

- All files will include [observational units](#observational-units) and [indicators](#indicators).
- The overwhelming majority of files will also have [filters](#filters).
- A table in Explore Education Statistics can only be created from a single file.
- All data in a single data file can be directly compared in the table tool.
- Break up files for different collections to avoid lots of blank or empty cells.
- You cannot mix different types of year in the time_identifier column.

</div>

---

# Observational units

---

Every observation, or row, in all of the provided data files will have a set of observational units based on the time period and geographic level that the data relates to. The number of these columns will differ across files depending on the number of geographic levels included in the publication.


Across every single dataset of official statistics produced by DfE, the following column names MUST be present at a minimum (example cell values):

| time_period | time_identifier	| geographic_level | country_code | country_name |
|-------------|-----------------|------------------|--------------|--------------|
| 201819      | Academic year   | National         | E92000001    | England      |

---

## Time columns

---

We use the two columns, time_period and time_identifier, to generalise time across our underlying datasets. All data files must contain these. This is a important for general useability of our data, as well as being critical in driving the charts and tables in the Explore Education Statistics platform and making explicit reference to the time in which our measurements relate to. This is a compulsory element of any official statistics dataset. 

If you think that your data can't follow this format, please contact statistics.development@education.gov.uk with details so that we can discuss this.


- time_period must contain either a four digit year, or a 6 digit year. 
- time_period must be numeric. This allows the platform to understand ranges and order periods in a logical manner.
- six digit time_periods must represent consecutive years - e.g. 201718, not 201619.
- If you’re referring to a single term you should use the academic year not the calendar year in the time_period column.
- Conceptually different years cannot be mixed in the same dataset.
- Conceptually different year breakdowns (e.g. term, quarter, month), can be mixed with a full year of the same type using a filter column.


---

### Specific time standards

---

Producers should not mix different types of years in the same dataset. This is to prevent any chance of confusion for users selecting time periods with similar labels in the table tool. For example, you cannot have Academic year and Calendar year data in the same data file. 
You also cannot mix yearly breakdowns (e.g. full year, quarters, months, or terms) in the time identifier column. Instead, where it makes sense to mix these within a data file you should use a filter column as shown below. Note the use of ‘Total’, this is a part of the standards for filters.


| time_period | time_identifier | quarter |
|-------------|-----------------|---------|
| 201718      | Academic year   | Total   |
| 201718      |	Academic year   | Q1      |
| 201718      |	Academic year   | Q1-2    |


| time_period | time_identifier | month |
|-------------|-----------------|-------|
| 2017        |	Calendar year   | Total |
| 2017        |	Calendar year   | July  | 


If your row of data spans multiple years (e.g. is a cumulative sum between 2010 and 2018), the starting year should be made clear in the name of the indicator, with the year of the end of the time period listed as the time identifier. For example if you had been recording the number of enrolments in a Local authority since from the start of the 2010/11 Academic year to the end of the 2017/18 Academic year, your data would look like the example on the right.


| time_period | time_identifier | starts_since_201011 |
|-------------|-----------------|---------------------|
| 201718      | Academic year   | 190                 |
| 201617      |	Academic year   | 173                 |

---

### List of allowable time values

---

All time_period values should be numeric only, below the number of digits (either 4 or 6) is defined per time_identifier below. Do not include dashes or slashes in six digit years.

You can only mix time_identifiers if they appear within the same table below. If they are in separate tables then they should not be mixed.

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Calendar year                    | 4 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Reporting year                   | 4 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Academic year                    | 6 digits                  |

| acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Financial year                   | 6 digits                  |

| acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Financial year Q1                | 6 digits                  |
| Financial year Q2                | 6 digits                  |
| Financial year Q3                | 6 digits                  |
| Financial year Q4                | 6 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Tax year                         | 6 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Autumn term                      | 6 digits                  |
| Spring term                      | 6 digits                  |
| Summer term                      | 6 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Autumn and spring term           | 6 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| January                          | 4 digits                  |
| February                         | 4 digits                  |
| March                            | 4 digits                  |
| April                            | 4 digits                  |
| May                              | 4 digits                  |
| June                             | 4 digits                  |
| July                             | 4 digits                  |
| August                           | 4 digits                  |
| September                        | 4 digits                  |
| October                          | 4 digits                  |
| November                         | 4 digits                  |
| December                         | 4 digits                  |

| Acceptable time_identifier value | Corresponding time_period |
|----------------------------------|---------------------------|
| Week 1                           | 4 digits                  |
| Week ...                         | 4 digits                  |
| Week 52                          | 4 digits                  |

---

<div class="jumbotron">

  <h3>Remember</h3>

  <hr class="my-4">

- You must include time_period and time_identifier columns in your data files.
- Your data must match the allowable values above.
- Use ‘Reporting year’ if your data does not fit in other categories, i.e. collected on a specific day.
- If you have different types of year such academic, calendar, and financial, these should be in separate files.
- Use filters to add more detail if you have multiple time breakdowns in the same file (quarter/full year).
- Where a measure spans multiple years, you should name the starting year, and set the time_period as the year published.

</div>

---


## Geography columns

---

We publish at a number of different geography breakdowns and these vary from publication to publication. Every publication in the new platform must include the three compulsory geography columns - geographic_level, country_code and country_name in its data files. These are compulsory as the data we are producing must lie within a country boundary. 

The geographic_level column should describle the level of data present in that row. Therefore data for a collection from a specific local authority would have 'Local authority' as the geographic_level, while a National aggregation would have 'National' as the geographic_level.

Teams should make sure that they are regularly checking their geography codes if they are not using a lookup from a maintained database (such as in the PDR). ONS have the [Open Geography portal](http://geoportal.statistics.gov.uk/){target="_blank" rel="noopener noreferrer"}, which can be a useful way of checking these. There is a wealth of data on there, though Local authority boundaries can be hard to find, they can be found using the tabs at the top – Boundaries > Administrative Boundaries > Counties and Unitary Authorities. 

If you have data from an unknown location, the standard is to use 'Not available' as name, and ':' as the code/s, this clearly marks that the geographical data for that row is unavailable, and does so in a consistent way with the wider GSS.

We expect that any country codes used will come from [this list](https://geoportal.statistics.gov.uk/datasets/countries-december-2018-names-and-codes-in-the-united-kingdom/data){target="_blank" rel="noopener noreferrer"}, please contact us if you have data that doesn't match this.

<div class="alert alert-dismissible alert-info">
Where you have data for a legacy LA that does not have a 9-digit new code, leave those cells as blank instead. Aside from blanks, no two geographic locations should have the same code.
</div>

---

### Different measures of geography

---

When using geographies that can be measured in multiple ways, you can achieve this by including a [filter](#filters) such as level_methodology in the example below to state how you have measured the geographic level. For example, at Local authority level you may have data that was measured by the residence of the pupil and the location of the school:


| geographic_level | old_la_code | la_name    | new_la_code | level_methodology | headcount |
|------------------|-------------|------------|-------------|-------------------|-----------|
| Local authority  | 373         | Sheffield  | E08000019   | Pupil residence   | 689       |
| Local authority  | 373	     | Sheffield  | E08000019   | School location   | 567       |

---

### Allowable geographic levels

---

All rows must have country_code and country_name completed, regardless of geographic level. The additional required columns by level are shown below. You do not have to publish at every level, this is a guide that covers every level that can be published in the platform. Teams should use additional columns as relevant such as including Regional columns for Local authority level data.

<div class="alert alert-dismissible alert-info">
  Planning area, School, Provider, and Institution level data will upload as normal to EES, though will not be read into the table tool or data blocks, as they are not yet designed for these levels of data. All data, including these levels are accessible in the downloadable files for users to explore in the same format as they are uploaded. Any data files that **only** consist of these levels should be uploaded as an ancillary file, rather than as a data file.
</div>


| geographic_level | required columns | notes |
|------------------|------------------|-------|
| National | No additional columns | |
| Regional | region_code, region_name | |
| Local authority | old_la_code, new_la_code, la_name | It is usually good practice to include the Regional aggregations where possible given the direct link between Local authorities and Regions. |
| RSC region | rcs_region_lead_name | For RSC region data, we generally define them into lead RSC regions where the majority of the data is from. |
| Parliamentary constituency | pcon_code, pcon_name | |
| Local authority district | lad_code, lad_name | |
| Local enterprise partnership | local_enterprise_partnership_code, local_enterprise_partnership_name | |
| Mayoral combined authority | mayoral_combined_authority_code, mayoral_combined_authority_name | |
| Opportunity area | opportunity_area_code, opportunity_area_name | |
| Ward | ward_code, ward_name | |
| MAT | trust_id, trust_name | Note that Trust ID is shown as Group ID on GIAS when looking at a Trust. MAT’s also have a ‘company number’, this can be included but is not mandatory. |
| Sponsor | sponsor_id, sponsor_name | Note that Sponsor ID is shown as Group ID on GIAS when looking at a Sponsor. |
| Planning area | No required columns, though we recommend both planning_area_code and planning_area_name | |
| School | No required columns, you can use any you have, though we recommend you include laestab and/or urn with the name in the style of 'school_laestab' to make data matching easier | |
| Provider | No required columns, you can use any you have, though we recommend you include the name and at least one code (urn, ukprn, upin) in the style 'provider_name' if possible to make data matching easier | |
| Institution | No required columns, you can use any you have, though we recommend you include institution_id and institution_name to make data matching easier | |


<div class="alert alert-dismissible alert-warning">
  If you have a level that isn’t covered above, then you should contact statistics.development@education.gov.uk with details and example data.
</div>

---

<div class="jumbotron">

  <h3>Remember</h3>

  <hr class="my-4">
  
- You must have the minimum expected geography columns, country_code and country_name.
- You must also have the additional columns required for all geographies included in your file.
- The additional columns must exactly match the names above.
- You do not have to publish at every level in the guide, publish the selection of levels you feel appropriate.
- You should regularly check that you are using the most up to date geography names and codes.
- Aside from blanks, no two geographic locations should have the same code.
- You can mix geographies in the same file, and should avoid separating files by geography unless size is an issue.

</div>

---

# Filters

---

A filter is a variable that we break our data down by, such as school type, pupil characteristics, or NQT status. There are no required standards for these, you can include any filters that you feel benefit the users of your data. 


Aggregates should be included for all filters where possible. There will be some situations where aggregation does not work, this will not break the platform, though may make the data less user-friendly. In general, if you can't aggregate, e.g. if you have headcount and fte, then this is an indication that they are instead seperate measures and should have separate indicator columns rather than forcing them into a filter column. 

**For aggregate rows you should refer to these as ‘Total’.** When using filters such as school type or FSM eligibility this can be quite simple, as shown in the example below:


| time_period | time_identifier | geographic_level | country_code | country_name | school_type | FSM_status | headcount |
|-------------|-----------------|------------------|--------------|--------------|-------------|------------|-----------|
| 201819      |	Academic year   |	National         | E92000001    | England      | Total       | Eligible   | 590       |
| 201819      |	Academic year   |	National         | E92000001    |	England      | Primary     | Eligible   | 280       |
| 201819      |	Academic year   |	National         | E92000001    |	England      | Secondary   | Eligible   | 310       |


It is possible that you wish to include a filter that only has a single level, like FSM_status in the above example that only has 'Eligible'. This is to be expected, and you do not need to unnecessarily double the size of your file by adding a 'Total' that duplicates what is already there. Where this is the case, do not include this filter in the EES metadata as the platform does not need to read that column into the table tool.

---

### Grouping filters

---

For some filters, this can be more complicated. You might have a great number of different filters that you would rather group together in a two-level hierarchy. You can do this by grouping a filter by a filter group. This essentially uses another filter column to provide a higher group level. This also may be helpful for EES users when creating tables. For example, you may want to group percentages into wider rate bands.


| time_period | time_identifier | geographic_level | country_code | country_name | absence_band_group | absence_band | 
|-------------|-----------------|------------------|--------------|--------------|--------------------|--------------|
| 2018        | Calendar year   | National         | E92000001    | England      | 60.0 – 69.9        | 67.0 - 67.9  |
| 2018        |	Calendar year   | National         | E92000001    | England      | 60.0 – 69.9        | 62.0 - 62.9  |
| 2018        |	Calendar year   |	National         | E92000001    |	England      | 10.0 – 19.9        | 16.0 - 16.9  |

---

You can also see an example with apprenticeship data file, including the corresponding EES metadata, below:

---

`r knitr::kable(apprenticeship, caption = "Data file")`

---

: Corresponding EES metadata file.

| col_name | col_type | label | indicator_grouping | indicator_unit | filter_hint | filter_grouping_column |
|----------|----------|-------|--------------------|----------------|-------------|------------------------|
| starters | Indicator | Number of learners starting an apprenticeship | Apprentice starts | | | |
| drop_outs | Indicator | Number of learners not completing an apprencticeship | Apprentice drop-outs | | | |
| drop_out_rate | Indicator | Percentage of pupils not completing an apprenticeship | Apprentice drop-outs | % | | |
| **appr_exact** | Filter | Type of apprenticeship | | | Select the apprenticeship types you're interested in | **appr_type** | |

---

<div class="alert alert-dismissible alert-danger">
  <p>You must not include grouping filters as separate rows in the EES metadata, they should only be referenced in the filter_grouping_column.</p>
</div>

---

#### Creating a grouped filter in SQL

---

This example creates a characteristic and characteristic_group column from 'gender', 'fsm', and 'sen' columns. For this to work, each column must have a 'Total' aggregation. The column names will need to be tweaked to match your data, and depending on how much you cross-tab you may not need all of the rows, however this should show you the logic needed:

```
SELECT *,  -- select your data that includes the columns to combine below
    
CASE 
  when fsm !='Total' then Concat(gender,' and ', fsm)
  when sen !='Total' then Concat(gender,' and ', sen) 
  when sen = 'Total' and fsm = 'Total' then gender
else '' end AS characteristic,
    
CASE 
  when fsm = 'Total' and sen = 'Total' and gender = 'Total' then 'Total'
  when fsm != 'Total' and sen = 'Total' and gender = 'Total' then 'FSM'
  when fsm = 'Total' and sen != 'Total' and gender = 'Total' then 'SEN'
  when fsm = 'Total' and sen = 'Total' and gender != 'Total' then 'Gender'
  when fsm != 'Total' and sen != 'Total' and gender = 'Total' then 'FSM and SEN'
  when fsm != 'Total' and sen = 'Total' and gender != 'Total' then 'Gender and FSM'
  when fsm = 'Total' and sen != 'Total' and gender != 'Total' then 'Gender and SEN'
  when fsm != 'Total' and sen != 'Total' and gender != 'Total' then 'Gender and FSM and SEN'
else '' end as characteristic_group
```

---

<div class="jumbotron">

  <h3>Remember</h3>

  <hr class="my-4">

- All filters should have a ‘Total’ aggregation where possible.
- If a filter has one level, you should not include it in the EES metadata.
- Where filters do not aggregate, consider if you should have the levels as indicators instead.
- Grouping filters is an option, but only group filters where you decide it is better for your users.
- If a filter is used to group another, then it should not appear as a separate row in the EES metadata.

</div>

---

# Indicators

---

The indicators are the variables showing the measurements/statistics themselves, such as the number of pupils. These can be of different formats (e.g. text, numeric), although are numeric by default. The number of indicators will vary across publications and data files.


As an example, the number of teachers and pupil enrolments are the indicators in this dataset: 

| time_period | ... | country_name | school_type  | enrolments | teachers |
|-------------|-----|--------------|--------------|------------|----------|
| 201819      | ... | England      | Total        | 100        | 13       |
| 201819      | ... | England      | Primary      | 50         | 5        |
| 201819      | ... | England      | Secondary    | 50         | 8        |


In EES, the platform itself will format large numeric values to include commas, so you do not need to have commas for separating large numbers in your data file. 

If you are including percentages and want to show 79% in EES, you would have the value 79 in your data file and specify % as the unit for that column in the EES metadata. As best practice we recommend that you include as much detail as possible in your underlying files when it comes to decimal places. As a default EES will round decimals to 2 d.p. in the platform, though if you would prefer to display your data to a different number of d.p. in the platform please use the [indicator_dp](#ees-metadata) column to specify this in the .csv metadata.  

Likewise with financial data, for don't include £ in the values in the data file, leave those cells as numeric only, include it as an indicator unit instead. In this situation it would be good practice to also include a £ symbol at the end of the variable name like `total_expenditure_£` (or `total_expenditure_£m` for million pounds), to make it clear to users when downloading the csv file.

When communicating uncertainty, for confidence intervals you should report the upper and lower bounds as separate columns rather than as one confidence interval column:

| time_period | ... | country_name | school_type  | estimate | **est_lower_ci** | **est_upper_ci** |
|-------------|-----|--------------|--------------|------------|----------|----------------|
| 201819      | ... | England      | Total        | 100        | **96.7** | **103.3** |
| 201819      | ... | England      | Primary      | 50         | **48.9** | **51.1** |
| 201819      | ... | England      | Secondary    | 50         | **49.5** | **50.5** | 

---

### Indicator grouping

---

Many of our publications contain a large number of indicators. To improve the experience of the user in the platform we can group these under headings as shown on the right. In the example, you can see how the different indicators have been grouped into ‘Admissions’, ‘Applications’, and ‘Preferences breakdowns’ in the EES metadata, followed by how this will appear in the table tool for users.

| col_name | col_type | label | indicator_grouping | indicator_unit | filter_hint | filter_grouping_column |
|----------|----------|-------|--------------------|----------------|-------------|------------------------|
| nc_year | Filter | NC Year | | | Filter by national curriculum year | |
| admissions | Indicator | Number of admissions | **Admissions** | | | |
| applications | Indicator | Number of applications received | **Applications** | | | |
| online_apps | Indicator | Number of online applications | **Applications** | | | |
| online_apps_% | Indicator | Percentage of online applications | **Applications** | | | |

---

Example of how this would look in Explore Education Statistics:

---


```{r, echo=FALSE, fig.align='center'}
knitr::include_graphics("images/indicator_group.png")
```


---


# EES metadata


---

Metadata in a machine readable (.csv) format must accompany datasets uploaded to the Explore Education Statistics platform to ensure that the files can be processed correctly. This data will not be seen by users and is purely for EES to be able to understand and read your data.


This EES metadata is different to any metadata files you may provide alongside your data for your users.


We only need to provide EES metadata for filters and indicators, we do not need to provide any EES metadata for the compulsory observational units (time and geography), as this has been standardised and the platform is expecting pre-defined columns and values in those fields. 


We do not need to provide metadata for filters that only have a single level, as this data is not useful for EES, and is there for the benefit of end users downloading the files.


<div class="alert alert-dismissible alert-info">
Note that any extra geography columns not specified in the [allowable values](#allowable-geographic-levels) above, or any time columns extra to time_period and time_identifier, are [filters](#filters), and should be included in EES metadata if they have two or more levels.
</div>


With some files there may be more than one way to specify the metadata that will work in EES and give an accurate representation of the data itself. In these cases we recommend that publication teams test out different approaches and decide their approach based on what will best meet the needs of the users of their data.

---

## Mandatory EES metadata columns

---

| column                 | details                                                        |
|------------------------|----------------------------------------------------------------|
| col_name               | This must exactly match the name of the corresponding column in the dataset. |
| col_type               | This must be either ‘Filter’ or ‘Indicator’. |
| label                  | This is the version of the column name that the users will see on the platform, therefore you must fill this in and not leave it blank. For example, pupil_headcount may be ‘Number of pupil enrolments’. You have the freedom to decide what is best to do for your users. |
| indicator_grouping     | This column gives production teams the option to add subheadings to group indicators in order to benefit the user. If this column is left blank, all indicators will be presented as one list of individual square radio boxes with no subheadings.|
| indicator_unit         | If this column is left blank then this will be a number by default, alternatively you can use either of the following units for financial or percentage measures - "£", "£m", "%", "pp". | 
| indicator_dp           | This column allows you to set decimal place formatting for each of your indicators. If you leave it blank the platform will default to 2 d.p |
| filter_hint            | This column gives you the option to add in a hint such as 'Filter by school type' for the filter to make the service easier for the users to navigate. If you leave the column blank, no hint will appear. | 
| filter_grouping_column | This column should be blank unless you are wanting to group your filters. When you are wanting to group your filters this column should contain the exact name of the column/variable that you wish to group by. It is good practice to use the same variable name as that you are grouping, with _group appended at the end, i.e. 'filter' and 'filter_group' |


Note that if you are using percentage points (pp) you must include a clear explanation in your release and methodology, so that users can understand what you are referring to. 

---

## Example EES metadata

---

: Each row represents a corresponding column in the data file.

| col_name | col_type | label | indicator_grouping | indicator_unit | indicator_dp | filter_hint | filter_grouping_column |
|----------|----------|-------|--------------------|----------------|-------------|------------------------|
| gender | Filter | Gender | | | | Filter by pupil gender | |
| school_phase | Filter | School phase | | | | Filter by the phase of the school | |
| number_children | Indicator | Number of children | | | | |
| percent_children | Indicator | Percentage of children | | % | 1 | | |

---


`r knitr::kable(metadata_data_example, caption = "The corresponding data file.")`


---

# How to check against the standards

---

An [interactive app](https://rsconnect/rsc/dfe-published-data-qa/){target="_blank" rel="noopener noreferrer"} has been developed to automate checks against the standards as a final stage of automated quality assurance before upload to EES. 

This can be run on any data file, though requires the EES metadata to be able to process the file. The app runs on our rsconnect servers, and is only available when using DfE kit. The app is mostly self-explanatory, though if you have any questions about it, or are curious to know more about how it works, get in touch with us at statistics.development@education.gov.uk.

<div class="alert alert-dismissible alert-danger">
  All data and EES metadata files must be run through the [screening app](https://rsconnect/rsc/dfe-published-data-qa/){target="_blank" rel="noopener noreferrer"} before uploading to EES.
</div> 

---

# Next steps

---

Assuming you are publishing on EES, once your data has been screened and you have a report that shows your data has passed with 100% of tests you can then upload your data and EES metadata files to Explore Education Statistics. If your data fails for any reason on upload after passing the screening tests, please contact statistics.development@education.gov.uk, with copies of the files and the error message you receive.

---