Data Structures in Finance: Cross-Sectional, Time-Series and Panel

Most people meet a record as a wall of numbers and go straight to working something out with it. A question comes one step earlier: what is any single row a row of? The question sounds like a formality and it is not. Before a single figure is touched, the answer fixes which questions the record is able to hold and which ones it will politely return nothing for. The answer decides something harder as well: which of the record's own faults are visible at all.

What is a data structure, and why does the shape decide the question?

A classroom photograph is a very good record of one moment: forty children, all of them measurable, and which of them is tallest can be stated with confidence. Asked instead which child is growing fastest, the photograph has nothing to say. Not because the camera was bad, and not because the children are unmeasurable, but because a photograph holds one moment and growth needs two. The other record a school keeps is a single child's height chart on the wall, five pencil marks over five years. Asked who is growing fastest in the class, it is just as helpless, for the opposite reason.

The photograph and the height chart hold the whole idea, and the rupees come later. A record has a shape, the shape is decided by what one row stands for, and the shape is what admits or refuses a question. However careful the figures inside it are, and however badly the answer is wanted, a question the shape cannot hold cannot be asked of that record. No amount of cleaning, no extra column and no clever handling changes it. The refusal is not a fault. The refusal is the layout.

Look at the three drawings and notice that the picked out box is a different kind of thing in each one. On the left it is a stall. In the middle it is a month. On the right it is a stall and a month together. A stall and a month together deserves a name of its own, and the name is a stall month. Three shapes, three answers to the same question about one row, and no arithmetic anywhere yet.

What is a cross section, and what can it never answer?

One moment, with every stall laid out beside every other. Month 1 of the Neelbagh stall record gives ten rows, one for each stall that traded, and the takings on those ten rows come to Rs 3,50,000/- between them. Those ten rows are a cross section, and a cross section is a genuinely useful thing. The cross section shows that NB-05 Bansi Flour took the most at Rs 55,000/-, that NB-09 Roshni Juice took the least at Rs 20,000/-, and that the spread between the biggest and the smallest is a factor of nearly three. The cross section also gives the average takings per stall month: the sum of the takings cells that carry a usable number, divided by how many carry one. All ten carry one, so the sum is Rs 3,50,000/- over 10, which is Rs 35,000/- exactly, with no rounding anywhere in it. The arithmetic averageThe figures are added up and the total divided by how many figures were added. Nothing else goes into it, which is exactly why the count used as the divisor has to be stated beside the answer. is the only quantity needed here.

Now ask the cross section the obvious next question. Was month 1 a good month? A cross section holds one moment, and there is nothing in it to compare that moment against, so the cross section cannot answer and never will be able to. Every one of those ten stalls could be having the best month it has ever had, or the worst, and the ten rows would look exactly the same either way. The picture is complete and the question is simply not in it.

One aside before moving on. The aside matters later. The file the market office actually handed over shows only eight rows for month 1, adding to Rs 3,08,000/-, not ten rows adding to Rs 3,50,000/-. Two of the stalls that traded that month are not in the file at all. Why that happened, and what it costs, is covered separately. A cross section of eight rows and a cross section of ten rows look equally complete. Neither one has a hole in it.

What is Time-Series Data, and what can one stall's four figures never show?

Turn the record ninety degrees. Hold one stall still and let the months run. NB-01 Kadamba Idli gives four rows: Rs 42,000/-, Rs 44,000/-, Rs 43,000/- and Rs 45,000/-. One row is now one month, the stall never changes, and the rows have an order that is not a matter of taste. Row two comes after row one because month 2 comes after month 1, and shuffling them destroys something real. Those four rows are time-series data: one thing, observed at many moments, in the order the moments happened.

The four figures are honest and they are complete and they still cannot answer the question anybody actually cares about. Suppose the fourth month is the best of the four. Was that NB-01 doing something right? If every stall in the market rose in month 4, then NB-01 rising proves nothing about NB-01 at all, so four figures from one stall cannot show whether what happened was about the stall. The stall's own record has no room in it for the market around the stall. The record is one column of the world.

Figures that run in order over time make a substantial subject with methods of its own, and every one of those methods is covered separately. How a series moves, what it tends towards, and what to expect from it next belong there. A series is just this: one thing, many moments, in order, and no company.

What is Panel Data, and what does the shape buy?

Put together, the two shapes give the third. Keeping every stall and keeping every month makes one row one stall and one month at once. Those rows make a panel, and a panel is what the Neelbagh stall record has been all along: 36 stall months, ten stalls living through four months, with the two that closed contributing two months each rather than four.

The panel buys one question that neither of the others can hold: whether one stall moved differently from the market around it. The cross section has the other stalls and only one moment. The series has the other moments and only one stall. The panel has both, so the question at last has somewhere to live. The methods that answer it properly are covered separately. Knowing that a question is askable is not the same as answering it.

The third column of that comparison is the one nobody asks for. People ask what a shape can do. Almost nobody asks what it structurally cannot do, and that second list is what saves a fortnight spent trying to squeeze an answer out of a record that was never able to hold it.

Which columns actually make this record a panel?

Naming a shape is one thing and proving it is another. The proof does not depend on anybody's description at all, and it is a short job done with the written column list to hand. Each of the eight columns is taken one at a time, with the question being what a single value in that column belongs to.

stall_name belongs to the stall. Change the month and it does not move. Same for licence_no, which is an identifierA value whose job is to point at something rather than to measure it. An identifier can be written entirely in digits and still not be a quantity, so adding two of them together produces nothing at all. rather than a quantity, same for category, and same for pitch_sqft, which records the size of the stall's pitchThe patch of floor a stall rents inside a covered market, measured in square feet and paid for whether the stall trades that month or not. in square feet. stall_id belongs to the stall by definition. Five columns, then, take one value per stall whatever the month. The month column is the mirror image: one value per month, whatever the stall. Which leaves two. takings_rupees plainly moves when either the stall or the month changes. And filed_on_day moves both ways too. The file proves it rather than asserting it: NB-01 filed on days 6, 7, 8 and 6 across its four months, so filed_on_day varies by month, and in month 2 one stall filed on day 7 while another filed on day 5, so it varies by stall as well.

A record with at least one column that varies by both the thing and the moment is a panel, and that is a fact about the columns rather than a description somebody wrote on the file. This record has two such columns. One would be enough. The test is blunt and mechanical, and it survives a badly named file, a missing column list and a spreadsheet somebody has already sorted twice.

What makes a panel balanced, and what do 40, 36 and 32 each mean?

A panel is balanced when every thing appears at every moment, so the record fills a complete grid with nothing left over. Ten stalls across four months would be a balanced grid of 40 cells. The Neelbagh stall record is not balanced, and the pleasing thing is that nobody need argue about it. The imbalance can be counted.

Three counts, and every one of them is a correct answer to a different question. 40 is what a full grid would hold, 36 is what the market really traded, and 32 is what the market office wrote down, and the distance between the three is where every argument about the size of this record comes from. The step from 40 to 36 is the two stalls that closed after month 2: those four cells are not missing, they never existed, and nobody took any money in them. The step from 36 to 32 is different in kind. The four missing ones are stall months that genuinely happened and are simply not in the file. One more wrinkle is worth carrying. One stall month got written down twice, so the 32 rows cover only 31 stall months.

A household version, if it helps. A block of ten shops signs a four year lease, so the landlord's rent book should have 40 lines. Two shops shut after two years, so only 36 lines of rent were ever really due. And the clerk who kept the book wrote down 32 of them. Somebody asking how big the rent book is will get three different true answers depending on who they ask, and the three answers are not a disagreement. The three answers are three questions.

What is the difference between long form and wide form?

A panel can be written down two ways, and both of them are the same record. Long form is one row per stall month: 36 rows, three columns wide, reading stall, month and takings. Wide form is one row per stall and one column per month: 10 rows, 4 month columns, 40 cells. Nothing is added and nothing is lost when an analyst comes to reshapeRewriting a record so the same values sit in a different arrangement of rows and columns. Nothing is added, removed or altered by it, and doing it with a tool is covered separately. one into the other, and that is exactly why the difference between the two layouts is so easy to miss.

The same record has 36 rows in one layout and 40 cells in the other, and the four cells of difference only become visible in the wide shape. In long form, a stall month that never happened and a stall month nobody wrote down are the same thing, an absence of a row, so the two look precisely identical. A list does not have a slot for a row that is not in it. A grid does. The wide layout has to put something in every position, so it has to show the positions it cannot fill.

Wide form is not the better layout. Long form takes an extra month without touching its columns, and wide form does not. Long form handles a record where different stalls were watched at different moments, and wide form makes an eye watering mess of it. The difference is narrower and it is only about seeing: an absence is invisible in a list and unavoidable in a grid, so anyone who needs to know what is not in a record should look at it wide at least once.

Which fault does the shape find, and which one does it create?

Take the file the market office handed over, all 32 rows of it, and lay it out wide across the ten stalls the market actually had. Two things happen at once, and they pull in opposite directions.

The first is a gift. Every hole in the record now has a position in the grid and a name. 30 of the 40 cells carry a figure and 10 of them print nothing at all, and each one can be pointed at. The grid also refuses to hide something a list hid happily: NB-03's month 2 has two rows in the file, filed on day 5 and on day 19 with different figures, and a wide grid has exactly one cell for that stall and that month. Two rows on one keyThe one column, or the small set of columns together, that is supposed to pick out a single row of a record and never two of them. cannot both be printed, so the reshape stalls until somebody decides which figure belongs there. The cost of that decision is covered separately. Structurally, the wide shape made a choice compulsory that the long shape allowed anybody to walk past.

The second thing is a trap, created by the shape rather than found by it: an empty cell in a tidy grid looks like a number waiting to be typed. A missing row in a list looks like nothing, because it is nothing. An empty cell with a border around it and three filled neighbours looks like a job. And the commonest job anybody does to it is to put a zero in.

What three questions should be asked of any record's shape?

Now for the part that travels. A lender looking at four years of a borrower's monthly sales and a fund analyst holding a table of forty companies for one quarter are doing the same job as the market office clerk, and all three of them get burnt the same way. The check follows, in the order that makes it useful, and it takes a couple of minutes on any record that arrives.

Question one, what is one row? The analyst with forty companies for one quarter has a cross section and cannot say a word about whether the quarter was ordinary. The lender with one borrower over 48 months has a series and cannot say whether a bad quarter was that borrower or everybody. Both of them know this the moment they answer question one, and both of them can then go and get the other shape rather than arguing with the one they have.

Question two, which columns vary by the thing, which by the moment, and which by both? Sorting a record into its levels exposes the columns that are about to be misread. A column that takes one value per thing has no business being averaged across moments as though it moved. Question two is also the test of whether the record in hand is a panel at all.

Question three is the one that turns a shape into a check: laid out wide, how many cells would be empty, and why is each one empty? The first two feel like the real work and the third feels like tidying, so nobody asks it. The truth is the opposite. The first two describe what is there. The third one is the only question in the set that shows what is not, and on the Neelbagh stall record it is the difference between a file with one hole in it and a record with nine stall months unaccounted for. The market office's day bookThe office's paper notebook of sentences about the day, kept beside the record and holding all the things that no column has room for. is where two of those reasons were eventually found, and a returnThe filled in form a stall hands to the market office each month. A return is a sheet of paper reporting what was taken, not a gain on money invested. that was filed on paper but never keyed in is where the third one was. None of that is in the file, and none of it would ever have been looked for by somebody who had not first asked why the cells were empty.