About Nesta

Nesta is an innovation foundation. For us, innovation means turning bold ideas into reality and changing lives for the better. We use our expertise, skills and funding in areas where there are big challenges facing society.

Data cleaning and merging involves transforming your data into a format that’s ready to use for analysis.

It’s likely that the data you need for analysis is held in different places. You might have a spreadsheet with participants’ group assignments (whether they are in the intervention or the control group) in one place, and a spreadsheet of outcomes (eg, whether or not they applied for childcare) in another. Merging means ensuring that all this information is in one place. You may do something similar to this in your local authority already.

How to clean and merge your data

Cleaning and merging your data involves the steps below:

Cleaning

  • Removing any duplicates
  • Making sure all the variables (eg, parent names) are in the same format

Merging

  • Decide which variable will be used for merging
  • Clean the merging variable in both datasets, if you haven’t already
  • Perform the merging itself
  • Check that everything worked as expected

Re-coding your variables

  • Transform verbal variables like ‘Applied / Did not apply’ to 0s and 1s for Excel to use for analysis.

We have produced two short guides to walk you through this process. You can download them below. Begin with the Word guide to explain the process, and then practice using the Excel guide:

  1. Word guide
  2. Excel guide

You can also watch the short video below, which walks you through how to do it.

Authors

Louise Bazalgette

Louise Bazalgette

Louise Bazalgette

Deputy Director, fairer start mission

Louise works as part of a multi-disciplinary innovation team focused on narrowing the outcome gap for disadvantaged children.

View profile
Dave Wilson

Dave Wilson

Dave Wilson

Advisor

Dave is an Advisor in the Education team at the Behavioural Insights Team (BIT) with a focus on early years projects.

View profile
Fionnuala O’Reilly

Fionnuala O’Reilly

Fionnuala O’Reilly

Lead Behavioural Scientist, fairer start mission

Fionnuala was the lead behavioural scientist in the fairer start mission, seconded from the Behavioural Insights Team (BIT).

View profile