National Travel Survey 2022: Technical report chapter 5
Published 30 August 2023
Applies to England
Chapter 5: Data Processing
5.1 Creating the NTS database
The edited survey data was prepared for analysis and reporting before being delivered to DfT. This chapter outlines the protocols followed during post-processing based on methods and scripts originally created by DfT.
The data for the survey year was imported into an annual database where a series of processing tasks were carried out to prepare the data for analysis. The database was divided into separate sections, as shown in Table 5.1 below.
Table 5.1: NTS SQL database schemas
Name | Purpose |
---|---|
Data | For importing questionnaire data |
Param | For parameterising data |
DVLA | For processing data from the DVLA database |
Imptn | For imputing data |
Weights | For importing the weighting data |
Random | For storing random numbers used in imputations |
In addition to creating the NTS annual database, two across-years databases were added to or amended as required: the NTS_Info database which stores information such as Retail Prices Index (RPI), school and bank holiday data and concessionary travel schemes, and the NTS_Lookup database that stores each of the look up tables that are used to attach description labels during analysis.
5.2 Importing the questionnaire data into tables
The metadata documents were used to create SQL scripts to import the questionnaire data into the NTS annual database. A script was created for each analysis level of the database (PSU, Household, Vehicle, Individual, LDJ, Trip and Stage) and for each multi-coded question. This stage was automated using SQL Server Integration Services (SSIS), which creates the tables and imports the data files without the need for user intervention.
Each record of each table was assigned a unique identifier during the import process. Once the import was complete the identifiers were cascaded down to the lower levels, allowing tables to be linked using a single identifier field.
5.3 Parameterisation
Parameterisation is the process of converting variables into a format that is more useful for analysis. Before the parameterisation routines were run, year-on-year changes to variables were identified and the routines amended to deal with the changes. Updates to the routines were reflected in the post-processing documentation. Many of the variables were passed unprocessed into the parameterised tables. For the remainder, several different transformations were applied, such as:
- creating a banded version of continuous variables
- combining several variables into a single analysis variable
- creating summary variables
Some variables that were parameterised were themselves used in the construction of subsequent variables, so these were created first. This stage was also automated using SSIS to run the parameterisation scripts in the required order. The relationship between the data in the import and parameterisation tables was recorded in the dependency documentation.
As part of this process, data from the DVLA database was linked to vehicles for which a registration mark was provided.
5.4 Imputation
Several variables underwent an imputation process where missing values were derived by looking at other known data. Again, this stage of the post-processing was automated using SSIS, which ran the imputation routines in a specific order due to the dependencies between variables. A variety of techniques were used in the imputation routines. Each routine was documented individually giving details of the methods used. Some routines required the use of random numbers to determine how cases should be allocated. These routines used random number tables that were created at the beginning of the process and retained, so that the results would be repeatable should the imputations need to be carried out again.
5.5 Adding weights
The weighting data was imported into the NTS annual database. Each set of weights was imported into a separate table, as shown in Table 5.2 below.
Table 5.2: Importing weights
Name | Level | Table |
---|---|---|
Interview sample weights | Household | Weights.Interview Sample |
Fully responding weights | Household | Weights.FC Sample |
Long-distance journey weights | LDJ | Weights.LDJ |
Short walk weights | Trip | Weights.Short Walks |
Diary drop off weights | Trip | Weights.Diary |
Self-completion weights | Individual | Weights.Self Completion |
5.6 Creating trip and stage numerics
To enable analysis of trip and stage level data with the correct handling of short walks and series of calls, the following grossing factors were attached to the trip and stage imputation tables, as shown in Table 5.3 below.
Table 5.3: Grossing factors for trip and stage imputation tables
Table | Variable | Description |
---|---|---|
Imptn.Stage | SSXSC | No. of stages, grossed for short walks, excluding ‘series of calls’ trips |
Imptn.Stage | SD | Stage distance travelled, grossed for short walks |
Imptn.Stage | STTXSC | Travelling time grossed for short walks, excl. ‘series of calls’ trips |
Imptn.Trip | JJXSC | No. of trips, grossed for short walks, excluding ‘series of calls’ trips |
Imptn.Trip | JD | Trip distance travelled, grossed for short walks |
Imptn.Trip | JOTXSC | Overall trip time, grossed for short walks, excl. ‘series of calls’ trips |
Imptn.Trip | JTTXSC | Travelling time, grossed for short walks, excluding ‘series of calls’ |
5.7 Combining long-distance journey data
Due to the infrequency of longer distance trips, additional long-distance journey (LDJ) data is collected for the week preceding the placement interview. To allow analysis of all long-distance trips, these LDJ trips were combined with those trips over 50 miles from the diary data into a single table.
5.8 Creating household income semi-deciles and quintiles
To allow analysis of trip behaviour by income on a comparable basis, households were categorised into income bands based on a measure of household affluence known as real household income equivalence. This adjusts a household’s stated income so that the household’s size and composition are considered. This adjustment was carried out using a measure called the McClements Scale.
Incomes were also adjusted for inflation to facilitate analysis across time periods.
To adjust for inflation the equivalised income was multiplied by the RPI value from the month the interview was carried out.
The conversion from household income band to value used the median values from the household income bands of the Family Resources Survey (2021 to 2022).
5.9 Adding holidays data
The holidays database was extended to incorporate dates up to the end of March 2023, using data supplied by DfT. Prior to 2016 this data was provided for each local authority, but this level of detail is no longer available. Consequently, the school holiday dates from 2016 onwards represent the national average.
A code to indicate holiday status (that is, weekend, bank holiday, school holiday or term time) was then added to each day record in the annual NTS database to enable analysis of trip data by travel day type.
5.10 Adding concessionary travel data
The following variables from the DfT’s latest annual concessionary travel survey were added to the NTS_Info database for each Local Authority, as shown in Table 5.4 below.
Table 5.4: Concessionary travel data variables
Variable | Description |
---|---|
ConcTravElig | Eligibility for elderly person concessionary travel scheme |
ConcTravFare | Type of bus fare concession (free since 2008) |
ConcTravTimes | Times offered for concessionary bus travel |
ConcTravAreas | Areas offered for concessionary bus travel (national concession since 2008) |
ConcTravOther | Any other concessions offered to elderly people |
ConcTravModes | Any additional modes offered to elderly people (multi-coded) |
These variables were then appended to the records of the Household table of the annual NTS database using the Local Authority code to link to the relevant data.
5.11 Incorporating new method of travel modes in 2022
In total four new modes of travel have been added to the NTS in recent years. Additional codes were added for e-bikes and e-scooters in 2021 and for ferries and mobility scooters in 2022. The existing variables and imputation routines were adapted to include the new travel modes. This involved adding the four new travel mode codes to the existing StageMode and MainMode series of variables and revising the imputation routines to carry out imputations using the updated variables. In addition, the imputations for dependent variables were also reviewed and revised where needed.
For the 2021 data existing variables and imputations were not updated to include the new mode codes. However, four new variables were created (StageModeN_B01ID and StageModeN_B02ID at the Stage level and MainModeN_B01ID and MainModeN_B02ID at the Trip level) and the imputation routines applied.
5.12 Additional derived variables for the accident module
As outlined in the 2021 technical report, substantial changes were made to the questions in the accident module for the 2021 survey. Additional variables were derived for the 2022 data to enable some comparison with previous years data (2020 and previous survey years).
Injury3c_B01ID was derived by combining Injury3a_B01ID and Injury3b_B01ID to form one longer list of possible injuries, including the following categories, as shown in Table 5.5 below.
Table 5.5: Derivation of categories for variable Injury3c_B01ID
Category | Source variable |
---|---|
Whiplash or neck pain | Injury3a_B01ID |
Sprains, strains and dislocation | Injury3a_B01ID |
Bruising | Injury3a_B01ID |
Shock | Injury3a_B01ID |
Any cuts or lacerations or abrasions | Injury3a_B01ID |
Any loss of consciousness | Injury3b_B01ID |
Any fractures or broken bones | Injury3b_B01ID |
Any injury resulting in the loss of a limb (include partial loss) | Injury3b_B01ID |
Any severe head injury (must include loss of consciousness) | Injury3b_B01ID |
Any other head injury | Injury3b_B01ID |
Any severe chest injury (with issues breathing) | Injury3b_B01ID |
Any other chest injury (not just bruising) | Injury3b_B01ID |
Any internal injury | Injury3b_B01ID |
None of the above | Injury3a_B01ID and Injury3b_B01ID |
NA: non-response (don’t know or refusal) | Injury3a_B01ID and Injury3b_B01ID |
DNA: did not ask (question not on route) | Injury3a_B01ID and Injury3b_B01ID |
InjRdAccid_B01ID was derived by combining Injury3a_B01ID (with the exception of ‘any cuts and lacerations or abrasions’), Injury3b_B01ID (with the exception of ‘any fractures or broken bones’), Cut1_B01ID and Broken1_B01ID to form one longer list of possible injuries, including the following categories, as shown in Table 5.6 below.
Table 5.6: Derivation of categories for variable InjRdAccid_B01ID
Category | Source variable |
---|---|
Whiplash or neck pain | Injury3a_B01ID |
Sprains, strains and dislocation | Injury3a_B01ID |
Bruising | Injury3a_B01ID |
Shock | Injury3a_B01ID |
Any loss of consciousness | Injury3b_B01ID |
Any injury resulting in the loss of a limb (include partial loss) | Injury3b_B01ID |
Any severe head injury (must include loss of consciousness) | Injury3b_B01ID |
Any other head injury | Injury3b_B01ID |
Any severe chest injury (with issues breathing) | Injury3b_B01ID |
Any other chest injury (not just bruising) | Injury3b_B01ID |
Any internal injury | Injury3b_B01ID |
Deep penetrating wound | Cut1_B01ID |
Deep cuts or lacerations | Cut1_B01ID |
Shallow cuts or lacerations or abrasions | Cut1_B01ID |
Broken neck or back | Broken1_B01ID |
Fractured pelvis or upper leg | Broken1_B01ID |
Fractured lower leg or ankle or foot | Broken1_B01ID |
Fractured arm or collarbone or hand | Broken1_B01ID |
Other fractured or broken bone | Broken1_B01ID |
None of the above | Injury3a_B01ID and Injury3b_B01ID |
NA: non-response (don’t know or refusal) | Injury3a_B01ID, Injury3b_B01ID, Cut1_B01ID and Broken1_B01ID |
DNA: did not ask (question not on route) | Injury3a_B01ID, Injury3b_B01ID, Cut1_B01ID and Broken1_B01ID |
Injury3_B01ID was reinstated for 2022 although the derivation of this variable was based on the new variables introduced in 2021.
An additional category was added to Medical2_B01ID called ‘No medical attention received’.
These variables should be used with caution because the substantial changes to the questions introduced in 2021 mean that they are not directly comparable to the previous variables created from the older question set.
Chapter 1 – Fieldwork approach in 2022
Chapter 4 – Fieldwork Procedures and Response Rate
Instructions for printing and saving
Depending on which browser you use and the type of device you use (such as a mobile or laptop) these instructions may vary.
You will find your print and save options in your browser’s menu. You may also have other options available on your device. Tablets and mobile device instructions will be specific to the make and model of the device.
How to search
Select Ctrl and F on a Windows laptop or Command and F on a Mac
This will open a search box in the top right-hand corner of the page. Type the word you are looking for in the search bar and press enter.
Your browser will highlight the word, usually in yellow, wherever it appears on the page. Press enter to move to the next place it appears.
Further information
National Travel Survey statistics