Data to Insight is not a software supplier in the traditional sense -- we're here to help local authority (LA) colleagues get work done and share it with each other. The best thing about Data to Insight is the community of analysts who are doing and sharing great work through this network.
We recognise that different LAs want different levels of engagement with our work. As such, we offer four key collaboration approaches to suit different users’ information needs.
Drift Tool
The drift tool uses data about referrals, Child Protection (CP) plans and Child Looked After (CLA) statuses to provide visualisations of and insight into drift. Drift is the amount of time children spend between stages of Children's Services (CS) provision, for example, the time between a referral and CP plan. It allows users to see, in their LA, how drift changes year on year, allowing users to see how wait times for CS provision is changing. This is useful alone, but if used in conjunction with knowledge of policy changes and current events, can provide a good insight into how these things impact drift year on year.
​
The only version of the Drift tool currently at release stage is the Usability version. It can be found using the button below. The guide for using it can also be found below.
Drift tool - usability version guide
The above image uses fake data to show the kind of output you can get from the drift tool. There is a bar chart showing average wait times, by year, from referral to CLA status, with a dotted line for overall average. There is also a box plot of the same data which helps to demonstrate the spread of these wait times. You will notice that only the most recent five years worth of data are boldly coloured, this is because the data model builds over time, and we consider the most recent few years to provide the best indication of drift.
To use the drift tool, users will need three separate Excel sheets or .CSVs, one containing Person IDs and referral dates, one containing Person IDs and CP plan dates, and a final one containing Person IDs and CLA status start dates. Note: the file formats MUST be .csv or excel comma separated values file. We recommend using around 20 years worth of data. The more years of data you use, the more accurate the model is. Each of these tables must have two columns. In every table column column 1 should have the header Person ID and should contain Person IDs. In the referrals table, column 2 should be headed Referral Date and should contain the referral dates matching the Person IDs in column 1. In the other two tables, the CP plan and CLA status tables, column 2 should be labelled Start Date and should contain start dates matching Person IDs. Many person IDs will have multiple dates in each table, this is fine as children may have multiple referrals, CP plans, or CLA statuses. If these tables are incorrectly formatted, the Drift tool will not work. The top of the correctly formatted tables can be seen in the image below, the tables shown are, in order: the referrals table, the CP table, and the CLA table.
The tables can be saved with any name, but it is recommended you name them something memorable. In the example image below, the referral table starts with 1, the CP plan table starts with 2, and the CLA table starts with 3, this is so they are ordered like this in the file explorer, making it easier to put them into Python in the correct order. Making these three tables may mean copy/pasting data from other tables, but this is left to analysts as it will involve different processes in different LAs.
Once you have your data, you can start using the tool. If you don't know how to use Jupyter Notebooks, or how to get files from our Github use our Replit and Github guide. To use the tool, navigate to the first cell with code in. It will look like the image below. When you run the cell, the buttons to upload your files (circled in the image below) will appear. Click each of these in turn and upload the appropriate file, they are in the order: referrals, CP plans, CLA status.
Once you've uploaded your files, ensure the next cell with code in is highlighted. Do not re-run the cell that produces the buttons or your uploads will be gone and you will have to re-upload the files. With the next cell highlighted (image below shows this cell), run the cell, and every cell with code in it until they have all run.
Once you've run every cell, you should be see some checkboxes and sliders, like in the image below. These allow you to select time periods over which to perform calculations and plot graphs, and which graphs to display. Move the sliders to change calculation and display periods and use the check boxes to select graphs. The Notebook produces bar charts of average wait times between selected stages of Children's Services provision by year, box plots of the same information, and histograms of wait times.
As you click checkboxes, plots will appear, if you click them again, they will disappear, so long as you then select another. As you move the sliders, the years over which the plots are calculated for will change. It is recommended to only have one graph active at a time as they are quite large and can take up a lot of screen. One you have the graphs and calculation periods you want, you can copy and paste the graphs into any report you need.
Drift tool - development version guide
To use the drift tool, users will need three separate Excel sheets or .CSVs, one containing Person IDs and referral dates, one containing Person IDs and CP plan dates, and a final one containing Person IDs and CLA status start dates. Note: the file formats MUST be .csv or excel comma separated values file. We recommend using around 20 years worth of data. The more years of data you use, the more accurate the model is. Each of these tables must have two columns. In every table column column 1 should have the header Person ID and should contain Person IDs. In the referrals table, column 2 should be headed Referral Date and should contain the referral dates matching the Person IDs in column 1. In the other two tables, the CP plan and CLA status tables, column 2 should be labelled Start Date and should contain start dates matching Person IDs. Many person IDs will have multiple dates in each table, this is fine as children may have multiple referrals, CP plans, or CLA statuses. If these tables are incorrectly formatted, the Drift tool will not work. The top of the correctly formatted tables can be seen in the image below, the tables shown are, in order: the referrals table, the CP table, and the CLA table.
The tables can be saved with any name, but it is recommended you name them something memorable. In the example image below, the referral table starts with 1, the CP plan table starts with 2, and the CLA table starts with 3, this is so they are ordered like this in the file explorer, making it easier to put them into Python in the correct order. Making these three tables may mean copy/pasting data from other tables, but this is left to analysts as it will involve different processes in different LAs.
In the above image you can see the filenames I used, they are 1referral20yrs, 2CP20yrs, and 3CLA20yrs. When you have these files and they are formatted correctly, open up the Drift tool Jupyter Notebook you downloaded from GitHub. Read through the markdown if you want to understand the code and how it works until you reach the first cell with code in. In the first cell of code, after the imports, there are some variables that you need to fill in, these are filepath, ref, cp, and cla. The filepath variable needs to be passed the file path where the three CSVs are stored. My own filepath is left in as an example. To find this, navigate to the folder containing your CVSs and click the bar at the top of the file explorer that has the filepath in it, then highlight this filepath, and copy/paste it over mine. Ensure it's between the quotes ('') and that you haven't deleted the r that precedes the quotes. In the images below you can see indicated with a 1, the location of the filepath variable. You will also see, indicated with a 2, the other variables you'll need to pass your file names to. Indicated, in the image following that is the location of the bar you can click to get your filepath.
Having replaced my stand-in filepath with your own, if you were to run the cell, Python would look in your computer to that filepath when you tell it to open files. Next, you need to replace the ref, cp, and cla variables with the filenames for your relevant CSVs. The ref variable should be passed the name of your table containing referral dates, cp the name of your table with CP plan dates, and CLA the name of your table with CLA status start dates. Make sure you pass the filenames to the lower case versions of the variables, ensure they end with .csv, and that they are surrounded by single quotes. The easiest way to do this is to copy and paste the filenames over my own, and end them with.csv, this ensures correct spellings etc. If spellings or spaces or capitalisation is not correct, the notebook wont work.
​
If you've correctly followed all of these steps, the notebook will be ready to run, use the restart and run all button in the toolbar, it looks like a fast-forward button, wait a little while, and then scroll down to see your visualisations.
​
The point of the development version of the tool is that the code is the most simple, making it easier to make changes and re-run it than the usability versions. This being the case, feel free to tinker with the code and make changes to suit your needs, or even just practice your Python. Some interesting examples are mentioned in the markdown.
Drift tool - Learning version
To get started with the learning version of the tool, follow the guide for the development version up until the point it tells you that you should be able to use the restart and run all button, you won't be able to use that button! Following the guide that far will help you get the right data into the right CSVs so that you can start getting the tool to work. Following this, start by reading the markdown before the code starts and then proceed with this guide.
​
The guide for the learning version of the tool is very minimal, by design. Working out how code works and how to use it yourself is a key part of learning to code. You'll notice when trying to run any cell in the learning version that it probably won't run, and you'll have all sorts of errors. This is because the cells are not completed, that is for the user to do by following the prompts and examples in the code. The idea is for the user to read through the code, and then, using the examples from the code, put that into practice to complete similar lines of code further through the worksheet. As you work through completing the worksheet version, you may want to refer to the dev version guide to help you get set up if things don't make sense. You can also refer to the completed code in the dev version of the tool if you get stuck.
​
Some tips for completing the worksheet:
-
When writing bits of code in cells, you may not be able to test the code you've just written or the line you've just completed because there are lines lower down in the cell that are uncompleted and waiting for the user to complete that will cause errors. Where this is the case, it might be worth commenting out lines of code below where you are working so you can properly run and test every line as you go.
-
A really nice and commonly used way to test that bits of code you've written work is to use print statements and check that what is printed is what you expected, for instance, check that a data frame you've made has the data you expected.
-
When writing and testing functions, you'll likely call the function later in the code, not in the cell you wrote the function in. To test that the function works, however, it's worth calling the function at the bottom of the cell you're writing and testing the function in so it's easy to run, check outputs, and make changes.
-
It's probably a good idea to keep a version of the worksheet downloaded with no changes so, if you mess up, you can revert back to it.
-
It's a good idea to check the official documentation or google when you get stuck before checking the answers, as this is a skill you'll need to develop and will use all the time when coding.