Guidance

Pensioners’ Incomes data on Stat-Xplore: user guide

Updated 21 March 2024

The Pensioners’ Incomes (PI) Stat-Xplore Database provides information regarding the amounts and sources of the incomes of pensioners in the United Kingdom. Variables are available at a family (benefit unit) level.

Please add “Source: Pensioners’ Incomes Stat-Xplore” to any analysis shared or published.

1. What is Stat-Xplore?

Stat-Xplore is a free tabulation tool available at gov.uk. Users can access DWP data via databases to create their own analysis. PI data is also available via:

	GOV.UK Publication	UK Data Service
Access	Unlimited	Members only
Content	Main report Tables Background Information and Methodology report	Rounded variables Ages of over 80s set to 80 (unless using safe room)

Read more about PI annual reports and accompanying tables, research and technical papers. Versions of the data set are available from the UK Data Service.

2. Benefits of using the PI Stat-Xplore Database

The benefits of using the PI Stat-Xplore Database are:

• that it’s free and accessible to all, with user guidance and virtual tour
• the new analysis of PI data, with a user-friendly interface and quick export of tables and graphs to Excel
• that the data is unrounded, so users can produce more accurate analysis (final estimates must be rounded as described below)
• the open data API functionality that allows users to dynamically create their own tables and data visualisations

3. What are the constraints?

The constraints of using the PI Stat-Xplore Database are:

• that confidence intervals around estimates cannot be produced in Stat-Xplore
• that the analysis based on three-year averages is not possible, so the map feature is not available
• that careful selection of row and column categories is needed, for more information see section 10

4. PI estimates rounding rules and disclosure

Once the user has produced PI estimates using unrounded outputs, the:

• percentages must be rounded to the nearest one per cent
• population numbers must be rounded to the nearest 100,000
• weekly amounts must be rounded to the nearest £1
• annual amounts must be rounded to the nearest £100

This reflects that PI estimates are based on the Family Resources Survey (FRS) and are not actual records of individuals in the UK. Some breakdowns are provided as bands or grouped to further protect against disclosure.

5. PI Stat-Xplore Database: breakdowns available

Stat-Xplore allows users to create their own analysis across all PI years and the following breakdowns:

Time Characteristic:

The time characteristics are broken down into financial year, 1994-95 to the latest year.

Measures of income:

The mean, median and range are provided for:

• gross income
  • benefit income, which can be broken into State Pension income, income-related benefits income and disability benefit income
  • occupational pension income
  • personal pension income
  • investment income
  • earnings income
  • other income
• net income before housing costs (BHC)
• net income after housing costs (AHC)

Characteristics:

The characteristics are broken down into age of head of pensioner unit, sex of head of pensioner unit and family type.

In receipt flags:

The receipt flags are broken down into:

• benefit income:

  • state pension income

  • income-related benefits income

  • disability benefit income

• occupational pension income

• personal pension income

• investment income

• earnings income

Quintile of the pensioner singles income distribution:

The quintile of the pensioner singles income distribution is broken down into before housing costs (BHC) and after housing costs (AHC).

Quintile of the pensioner couples income distribution:

The quintile of the pensioner couples income distribution is broken down into before housing costs (BHC) and after housing costs (AHC).

Descriptions of these breakdowns and any data issues can be found by selecting the ‘i’ icon next to it in the database or via the front page list.

Variations of these breakdowns are also possible using the ‘Add derivation’ feature. More information on this is in section 9.

6. PI Stat-Xplore Database: how it works

Please note the following images and examples relate to 2021-22 data as the latest year. Data for 2022-23 is now available but the following guidance remains the same.

1.Once you have logged in, please take the tour to learn about how to use the Stat-Xplore database. The tour can be accessed by selecting the three dots in the top right-hand corner of the page. Further useful guidance can be found by selecting the ‘?’ icon, also in the top right-hand corner:

A screenshot of the Stat-Xplore homepage

2.Select the Pensioners’ Incomes database and take the time to read the front page for important information on rounding final figures and known issues.

A screenshot of the Pensioners’ Incomes database front page on Stat-Xplore

3.Double-select the database icon or a ready-made table.

7. PI Stat-Xplore Database: ready-made tables

This example demonstrates the ready-made table for the percentage of pensioner couples in each quintile of the AHC pensioner couples income distribution by age.

1. The user can select which age band to view in the table by selecting from the ‘Wafers’ list.

A screenshot of how to select the age band for a PI table on Stat-Xplore

2.The user can export to Excel, which will produce tables for all age bands.

8. PI Stat-Xplore Database: ready-made tables

1.When the user double-selects the database icon (as shown in section 6 of this guide), a page containing a variable list and an empty table is displayed:

A screenshot showing how to access the variable list on Stat-Xplore

2.The following table provides steps for producing some common PI analysis:

Analysis	Filter	Wafer	Row	Column	Numbers to Percentages
Mean income by income source by family type by financial year	Not applicable	Not applicable	Choose the ‘mean’ box under ‘measures’ for as many types of income as you’re interested in. Then add family types.	Financial year (tick the boxes for the years you’re interested in)	Not applicable
Percentage of pensioner units with earnings income by age	Not applicable	Age of head of pensioner unit	In receipt of earnings income	Financial year (tick the boxes for the years you’re interested in)	Select Table options then Percentages then Column
Percentage of pensioners in each quintile of the AHC pensioner singles income distribution by sex	Select Family type then single pensioner	Sex of head of pensioner unit	Quintile of the AHC pensioner singles income distribution (choose all except not applicable)	Financial year (tick the boxes for the years you’re interested in)	Select Table options then Percentages then Column
The occupational pension income distribution for those in receipt	Not applicable	Not applicable	Under ‘occupational pension income’ select ‘Range’ and create your desired range (for example from 0 to 500, increment 20). Select this range, choosing all boxes except ‘0 or less’.	Financial year (tick the boxes for the years you’re interested in)	Select Table options then Percentages then Column

9. PI Stat-Xplore Database: user-defined analysis (cont.)

The user can use the ‘Add Derivation’ feature which allows the user to create their own variation of a category. Here we add a simple derivation to compare half the median income (AHC) of pensioner couples to the median income for pensioner singles.

1.The median income (AHC) of pensioner couples and pensioner singles is added as a row. Note that it is necessary to choose the measure(s) first, select ‘Row’, and then do the same for the family types. Next, ‘Add Derivation’ can be used by selecting the three dots next to the label for Family type:

A screenshot showing how to add a derivation to Stat-Xplore

2.Create a name for the derivation and add the formula (in this case, V1*0.5). Select ‘Advanced’ and choose where you want the new row to display. Then press the ‘Create’ button.

A screenshot showing how to modify the details of the derivation on Stat-Xplore

3.Select ‘Retrieve Data’ and the numbers, including your new derivation, will appear.

A screenshot showing how to retrieve the data for your derivation on Stat-Xplore

10. PI Stat-Xplore Database: top tips

Stat-Xplore vs Published Tables

When deciding whether to use Stat-Xplore or Published Tables, you need to:

• check whether the breakdown you require is currently available in the published tables and use the published tables where possible.

• know that not all breakdowns are available, more information on this is in ‘Exclusions’ in the next section.

Build a table in the following order:

1. Filter
2. Wafer
3. Column
4. Row

Select ‘Family Type’ or another classification variable as a wafer to produce the same cross-tabulations for each type.

Convert from percentages

To convert from percentages to population numbers, choose ‘Table Options’ then ‘Percentages’ and select ‘None’.

Convert a table into a graph

Once the table has been created, select the Graph view at the top of the page. You may need to change the ‘Graph by’ to ‘Column’

A screenshot of how to convert a table into a graph in Stat-Xplore

Removing total

For some tables, the ‘Total’ column does not add useful information. In these cases, select the three dots next to the variable label and untick ‘Total’.

A screenshot showing how to remove the total column in Stat-Xplore

Unless you are averaging across multiple years, this should be done for ‘Financial year’ for all tables. There may also be other tables you create where the ‘Total’ column or row is not relevant.

Stat Xplore Default Options

There are a number of default functions on Stat Xplore platform. Not all are relevant to the PI database, including the RSE function. RSE stands for Relative Standard Error. The PI database is weighted to provide the correct figures. The weighted plugin also adds RSE figures. This has been set to zero so users can ignore.

11. PI Stat-Xplore Database: exclusions, important footnotes and user feedback

Current Database exclusions (available in published PI tables)

The following breakdowns are not included in this version, due to either small sample sizes or complexities involved with displaying them in Stat-Xplore. They are:

• survey sample sizes
• the percentage of pensioner units with more than 50% of gross income from private sources
• income from annual payments such as Winter Fuel Payments and free TV licences
• the position of pensioners in the overall UK income distribution (including non-pensioners)
• both members over State Pension age (SPa) vs one over SPa and one under SPa
• average incomes of single retired benefit units under SPa
• average incomes of pensioner units where at least one member is aged over 65
• married vs cohabiting couples

Three-year average estimates are not available in Stat-Xplore. As single-year PI estimates for the breakdowns are considered too volatile, estimates based on country, region or ethnicity must be calculated using three-year averages. Output at least three financial years and calculate a three-year average as follows: (yr1 estimate + yr2 estimate + yr3 estimate)/3.

Important footnotes

These important footnotes are displayed on tables, which users must comply with (while displaying footnotes on percentages tables is not possible, they still apply):

A screenshot of the PI Stat-Xplore footnotes

12. Worked example: average incomes of pensioners by income source, family type and age

1.Start with an empty table (select ‘Clear Table’ if necessary). Tick the boxes for ‘Pensioner couple’ and ‘Single pensioner’ and then select ‘Add to: Wafer’.

A screenshot of a worked example of how to add wafers on Stat-Xplore

2.Select the financial years you are interested in. For this example, we choose all years, so select the arrow next to Financial year, then on ‘Financial year’ in the dropdown menu, then ‘Add to: Column’.

A screenshot of a worked example of how to select the financial years of interest for your table on Stat-Xplore

3.Choose the measures you are interested in. Here, we select the means of each different source of income, as well as the medians of net income BHC and AHC. This is to replicate Table 2.1 of the Pensioners’ Incomes series publication tables. Then select ‘Add to: Row’.

A screenshot of a worked example of how to select the measures you want on Stat-Xplore

4.Finally, select ‘Retrieve data’ and the table will be shown. You can also view information about the measures or classification variables such as family type by selecting the ‘i’ buttons.

A screenshot of a worked example of how to retrieve the data for your Stat-Xplore table and also how to view additional information about the measures or classification variables

5.You can switch between pensioner couples, singles and all pensioners using the ‘Wafers’ dropdown menu.

A screenshot of a worked example of how to switch between wafers in Stat-Xplore

6.You can add additional breakdowns to the table, for example by selecting ‘Under 75’ and ‘Over 75’ and selecting ‘Add to: Row’. This produces the data in Table 2.6 of the Pensioners’ Incomes series publication tables.

A screenshot of a worked example of how to add additional breakdowns to your table in Stat-Xplore

7.To download the data into Excel, select ‘Go’ in the top-right corner of the page:

A screenshot of a worked example of how to download the data into Excel in Stat-Xplore

8.Producing a graph: For clarity of presentation, the measures except for ‘Median net income after housing costs’ have been removed by selecting all other measures and selecting ‘Remove’.

Select ‘Graph view’ at the top of the page and change the ‘Graph by:’ from ‘Row’ to ‘Column’.

A screenshot of a worked example of how to remove measures from the Stat-Xplore data output

A screenshot of a worked example of how to Graph by column instead of row in Stat-Xplore

9.You can export the graph in the same way as for the table – by selecting the ‘Go’ button in the top-right corner of the page.

13. Feedback

The PI team are actively seeking feedback from users.

Please email: [email protected] if you have any comments on how you have found using Stat-Xplore or if there are further tabulations you would like to produce.

ISBN: 978-1-78659-660-4