How to Prepare Time-Series Data for Analysis

Seven checks come before the first computation. The dates are read as dates and sorted on. The gap between rows is confirmed and a missing one looked for. The level or the change is settled as the reading in use. A repeating calendar pattern is looked for. The spread is checked across the whole record. Whether the series comes back to a level is checked. Last, what was known when is marked.

Seven checks are the whole of it, and none of the seven is difficult. Each one is worth setting out because each one has a cheap version that takes under a minute, each one has a quiet way of failing that produces no error message at all, and the order they run in is not the order they first occur to a reader.

Every worked figure below runs on one record. The Nakshatra unit, an invented traded unit, is a nameless something with a price and nothing else settled about it. Its six year record is seventy two dated monthly observations, the month ending 31 January 2019 through to the month ending 31 December 2024, measured against an opening markThe starting price a record is measured against, sitting one period before the first observation in it. Nothing is observed on that date; it is there so the first change has something to be a change from. of Rs 100.00/- at the end of December 2018. The record has already been read three ways elsewhere, as a price, as a monthly change and as a rebased index.

What decides the order the seven checks run in?

Think about a stall outside one office building, the kind that sells one thing and closes at four. At the end of the month the person running it tips out a box of paper slips, one for each day, and adds them up. The month's takings come out right. Now suppose two of the slips got shuffled on the way into the box. The total is still right, to the rupee. But the busiest day of the month is now the wrong day, the pattern across the week is wrong, and the run of three quiet days that would have told them something is not there any more.

The figures that do not depend on order come out exactly as they should, so if the rows are in the wrong order, every figure that depends on order is wrong and nothing warns of it. That is why the date check goes first. The date check is not the most interesting of the seven, and it is the only one that can invalidate the other six. A calendar pattern read off a shuffled record is noise. A spread compared between two halves is comparing two arbitrary piles. A check on whether the series comes back to a level is answering a question about a sequence that is not the sequence.

After the first check the ordering is looser but still considered. The gap check comes second because it changes the count of rows, and every later figure is computed over some count. The level or change decision comes third because every check after it reads one of the two, and which one is being read has to be settled. The calendar pattern, the spread and the pull back come next in any order. The boundary check comes last for a reason worth stating plainly: the first six run on the columns as handed over, and the seventh runs on the columns just built.

How is it checked that the dates are really dates?

What to look at: the column holding the dates, and specifically what the software thinks that column contains. What a good answer looks like: every value has been turned from whatever was typed into an actual date, the count of values that failed to convert is zero, and the rows have then been sorted on the converted value rather than on the text.

The failure here is almost boring, and it is the most common one there is. A date column that has never been converted is a text column, and text sorts alphabetically. Under alphabetical rules 28 comes before 29 comes before 30 comes before 31, so every February in the record arrives before every April, and the year at the end of the string never gets a look in.

Take the six year record of the Nakshatra unit with its dates written day first, and that sort puts 71 of the 72 observations in the wrong place. The scrambled record opens at 28-02-2019 instead of 31-01-2019, and the second row is 28-02-2021, and the third is 28-02-2022. Exactly one row survives in its correct position, 31-12-2024, and it survives by accident rather than by anything meaningful. Then here is the part that makes this check worth running before every other one: compound the seventy two monthly changes in that scrambled order and the closing price is Rs 187.4539/-, agreeing with the honest closing price to the paisaA hundredth of a rupee. Two amounts that agree to the paisa agree at the second decimal place, which on a figure of this size is agreement to about one part in twenty thousand.. Seventy two numbers multiplied together give the same product in any order. Nothing on the screen changes colour. The mechanism behind why a date column arrives as text, and the other ways a timestamp quietly breaks an analysis, are covered separately.

How is the gap between one observation and the next checked?

What to look at: the distance from each row's date to the next row's date, all the way down, and the count of rows against the span the first and last dates cover. What a good answer looks like: one distinct gap value for the whole record, and a count of rows that matches the span exactly.

Both halves of that matter and they catch different things. Reading the gaps catches a record that changes its own spacing partway. Two files stacked into one do that more often than anyone expects. Counting rows against the span catches the missing observation. Reading the gaps catches that one too; a summary never does. A missing month is invisible in an average and obvious in a count, so count. An average over 71 months looks exactly as reasonable as an average over 72, and no software anywhere will mention which one it did.

Run both readings over the six year record of the Nakshatra unit and both come back clean. There are 71 consecutive pairs, every one of them exactly one month apart, so the record has one gap value and not two. The first date is the end of January 2019 and the last is the end of December 2024, a span of 71 months, and a monthly record covering 71 months of span holds 72 rows. It holds 72. Nothing missing. A gap of a day rather than a month, and the effect on a figure when the spacing changes underneath it, are covered separately.

Is the work on the level or on the change?

What to look at: first the question actually asked, then whether the reading that question needs holds still across the record. What a good answer looks like: a decision written down, with the two half averages that supported it sitting beside it.

The cheap version of this check takes about thirty seconds. The record is cut in half, each half of the level is averaged, then each half of the change, and the four numbers are put side by side. A household that has run on one salary for six years makes the point. A balance drifts, so the bank balance averaged over the first three years and over the last three gives two quite different answers. A rate of change does not have to drift just because the thing it is changing does, so the salary's rise each year can come out the same over the first three years and over the last three.

Do that to the six year record of the Nakshatra unit and the price comes out at Rs 115.0525/- for the first three years against Rs 190.4591/- for the last three, a distance of Rs 75.4066/-. Its monthly change comes out at 1.00 per cent for the first three years and 1.00 per cent for the last three, agreeing to the last decimal place on offer. When the two halves of the level disagree and the two halves of the change do not, the change is the thing to work with. The rule is a working one and not a proof, and the check has a sharper version worth knowing about. The sharper version is check six. Why a drifting level makes trouble, how a level is turned into a change, and why a change is often written as a log differenceA change written with logarithms in place of a plain percentage, which lets a run of them add up rather than multiply together. Weighed up in full elsewhere. rather than a plain percentage, are all covered separately.

How is a repeating calendar pattern checked for?

What to look at: the observations grouped by the position they occupy in the calendar, with each group averaged. For a monthly record that is twelve groups, one per calendar month. What a good answer looks like: twelve averages that sit close together, or twelve that plainly do not, and a note of which it was.

The calendar check is the easiest of the seven to run and the one most often skipped. The same check is also the easiest to talk yourself out of. Six observations per group feels thin. Everyday life says otherwise: a sweet shop knows its Diwali fortnight is not its March fortnight without needing six years of slips to prove it, and a school canteen knows May is empty. The pattern is in the calendar, so group by the calendar.

Group the six year record of the Nakshatra unit that way and the twelve averages spread out between a January high of 5.00 per cent and a June low of minus 3.00 per cent, with November and December both sitting at 4.00 per cent and April, May, July and August all below zero. The twelve averages cover a range of 8.00 percentage points between the highest group and the lowest, on a record whose typical month is 1.00 per cent. A range that wide is not noise.

Now the warning, and it is the single most useful thing in this guide. Do not use a single correlation figure as the test for a calendar pattern. The obvious shortcut is to take one autocorrelationA single figure reporting how much a series looks like its own past: line the record up against a version of itself pushed back by a set number of periods, and see how well the two track. Built and argued over separately. reading at a shift of twelve months and treat a large value as proof a calendar pattern is present and a small one as proof it is not. On the six year record that reading is 0.0022, computed the usual way: the record's own average comes out of both columns and the record's entire squared deviation goes underneath. A reading of 0.0022 is about as close to zero as a figure gets. Run it instead as an ordinary correlation over just the sixty surviving pairs and it comes out at 0.0000, closer still. Two conventions, two digits, one conclusion, and the conclusion is wrong: this record has a calendar pattern eight percentage points wide. The calendar month averages are exact on this record and the shortcut is not, so use the averages. A repeating calendar pattern, and what a seasonal adjustmentSubtracting a repeating calendar effect from a series so that what is left carries no calendar in it. Set out in full elsewhere, along with what the subtraction does and does not remove. takes out of a series when one is removed, are covered separately.

Does the spread hold across the whole record?

What to look at: the record cut into two halves, with the average and the spread of each half computed separately and then set against the same two figures for the whole record. What a good answer looks like: two half spreads close enough to each other that one figure can stand for both, or a plain statement that they are not.

The spread check does something a comparison of averages cannot. Ten shops in one shopping centre can have the same average daily takings as ten shops spread across a city and a completely different range around it, and only one of those two situations allows anything useful to be said about tomorrow. On this record the averages are exactly equal and the spreads are not, so a check that compares only the averages passes the six year record cleanly and says nothing.

Over the first three years of the Nakshatra unit's record the average is 1.00 per cent and the spread is 3.00 per cent. Over the last three the average is 1.00 per cent again and the spread is 6.4031 per cent. Taken whole, the record gives an average of 1.00 per cent and a single spread figure of 5.00 per cent, too wide to describe the first stretch and too narrow to describe the second. The single figure is wrong in both directions at once, and an eye running down a column of figures does not catch it for exactly that reason: the two errors point opposite ways and the average of them looks like no error at all. The name for a changing spread, what it costs a figure computed on top of it, and what is done about it, are covered separately.

Does the series come back to a level?

What to look at: each month's change in the series set against that series' own previous value, fitted with the straight line built earlier, and then the sign and the size of the coefficient on that previous value. What a good answer looks like: a coefficient clearly below zero, saying the series pulls back, or a coefficient sitting at about zero, saying it does not.

The pull back check is check three's question asked properly rather than by eye, and it is one regression run twice on the same record: once on the price and once on the monthly change. Nothing new is being built to run it. Run it on the six year record. The price gives a coefficient of minus 0.026109 with a t of minus 1.0683, and the monthly change gives minus 0.777838 with a t of minus 6.2898.

Read those two in one line: the price of the Nakshatra unit does not come back to any level and its monthly change does. The check produces nothing else, and the two readings are enough to decide what may be computed next. The name for the property the price has, the formal test that puts a confidence figure on the reading, what it costs when the property is present and ignored, and why a unit rootThe name given to a series that wanders on without being pulled back towards any level. Named, tested and taken apart elsewhere, along with what its presence rules out. matters at all, are all covered separately. The preparation keeps the number and the sentence, written down before anything is computed on top of them.

How is the boundary of what was known when marked?

What to look at: every computed column, one at a time, asking on what date its value in each row could first have been worked out, and whether that is the date the row is filed under. What a good answer looks like: each computed column carrying a date on which it first becomes computable, and no column carrying a value that needed something later than its own row.

The boundary check goes last because the first six run on the columns as handed over and this one runs on the columns just built. A shop's ledger cannot record November's takings in an October column, and everyone accepts that instantly. A formula reaching one cell downwards looks exactly like a formula reaching one cell upwards, so the same error inside a spreadsheet is invisible.

Take the simplest computed column there is: a three month average of the price of the Nakshatra unit, which is a rolling windowA fixed number of consecutive rows that slides down a record one row at a time, so the same figure is worked out again at every date. Built and read separately. three rows long. Written as this month and the two before it, the earliest row it can fill is March 2019. January and February do not have two months behind them. The average reads Rs 103.7068/- in March 2019, and it can be filled every month after that, all the way to December 2024, where it reads Rs 169.9987/-. The month before, this month and the month after is an ordinary and perfectly respectable way to describe history. Written that way, every value is one row too early for the date it is filed under. And here is the tell that costs nothing. If a computed column's last row is empty, it used something that has not happened. There is no January 2025 in the record, so the second version cannot produce a value for December 2024. The damage that error does to a figure, how large the flattery gets, and why it is a dating fault rather than anything to do with what anyone would have done about it, are covered separately, under the name lookaheadThe name for a figure worked out from something that had not happened yet at the date the figure is filed under. Taken apart elsewhere, with the size of the flattery measured..

What did the six year record answer, check by check?

Here are the seven run end to end on one record, with the answer each one gave. The table repeats the seven blocks above with their prose taken away, and the stripped form is what the answers should end up as when the seven are run on any other record.

Seven checks, seven answers, one record, and four of the seven came back needing work on a record whose overall average and overall spread are both perfectly tidy. A record can be tidy in summary and faulty in four places at once, and the sentence above is worth reading twice before any of this is run on another record.

What is written down beside the prepared record?

An analyst inside a lending team, a research desk or a household budgeting spreadsheet has the same problem at the end of this procedure, and it is not the arithmetic. The problem is that the seven answers live in the head of whoever ran them, and a record whose preparation lives in somebody's head is a record only that person can defend. The fix is seven lines of writing, kept beside the data rather than inside a report.

The note records the count of rows and the first and last dates. The note records the gap. The note records whether the work is on the level or on the change, and the two half averages that decided it. The note records the twelve calendar averages, or the equivalent for whatever period repeats in the record. The note records the two half spreads. The note records each computed column's first computable date. Nothing else goes in. Seven lines is the difference between a record somebody else can rebuild and a record they can only take on somebody's word.

A preparation note also earns its keep in an argument. When somebody four weeks later says the pattern in the data is just noise, nothing has to be remembered: the twelve calendar averages are there to point at. When somebody says the model was tested on a stable period, the figures to point at are 3.00 per cent and 6.4031 per cent. And when somebody asks whether a column could have been known at the time, the date beside it answers. None of that requires being right. It requires having written down what was looked at.