I am designing a data capture method for a client for inplay sporting events and he wants to record the odds movements for later analysis in Excel once every half second. I want to get this right so that it's easy to use the data down the line for analysis in other packages.

A bit more background and assumptions.

  • Each event can have between 4 - 40 contenders (c)
  • Each event has 10 variables that apply equally to all contenders (e)
  • Each contender has 20 variables of same heading/type with values unique to contender (i)

In essence I need to choose between

  • 1. Having 1 timeframe on 1 row, so each timeframe capture has

Columns required = e+max(c)i = 810

Rows required = 1

Good: Easy to manipulate, data on one row, 1 row describes all contenders in event per row.

Bad: Huge number of columns, lots of blank column data if c is less than max(c), hard to search names across multiple columns


  • 2. Having 1 timeframe on multiple rows, so each timeframe has

Columns required = e+i =30

Rows required = c

Good: Less columns, easy to search/filter as all names in the same column

Bad: Timeframes in different rows for different contenders

Does it matter? Is it easy for packages to handle data in both forms? My client doesn't know the answer but wants the best solution! I'm tending towards 2. as it's much easier to manage and search in database terms but not sure about preparation for time series analysis? Can anyone one with experience offer some advice?

Thanks Os


Option #2 is much more flexible than #1, particularly if you plan on using Excel pivot tables and/or R packages such as Hadley Wickham's excellent reshape package. I would store the data so that each row contains measured (event-level and contender-level) variables and any variables necessary to uniquely identify an instance of the measured variables (contender ID, event ID, measurement occasion ID [e.g., half-second increments]) for a single measurement occasion within an event. This allows for the most flexible reshaping of data into any other format desired, a process Wickham describes as melting and casting. You can export the data into an comma-separated value (CSV) spreadsheet, which of course can be read into Excel and most other statistical software.

If you have long-format data in Excel, aggregating, summarizing, and tabulating data is also easy using Pivot Tables. This enables you to create different views of the data that might be of interest to your client, such that as the data are updated you can update these useful views as well.

IMO, the most robust solution for very large amounts of structured data is one you didn't mention: store them in a relational database (using, e.g., MS Access or open-source databases such as PostgreSQL) and use Structured Query Language (SQL) to perform the above operations. Here, your data would be broken up into separate tables containing information that is unique to events (e.g., event ID, event type, etc.), contenders (e.g., contender ID, contender name, etc.), unique event-contender combinations (since a single contender might participate in more than one event, and each event certainly has more than one contender), and the measured data in half-second intervals. This avoids storing redundant data and allows you to enforce the integrity of the data that you articulated in your question as data are added, deleted, or updated. There are methods for calling SQL queries from Excel, R, Matlab, and other statistical programs to extract just the information your client wants. A useful introductory text on relational database theory and application is "Inside Relational Databases" by Whitehorn and Marklyn.

  • $\begingroup$ I agree with the database suggestion and the plan is that the data will reside in a database eventually but the method of data collection must be in Excel. In terms of database management this is the easiest method and I will write a short script to transpose the data from storage method #2 to #1. Thanks $\endgroup$
    – user3484
    Apr 1 '11 at 15:37

In my experience, #1 is the better option. If you store the data in any flatfile setup (as you're suggesting) and don't put the rows as your time variable, it becomes that much harder to import into selected programs.

For example, I work primarily in Fortran/C, with secondary applications occasionally done in R or MATLAB. To be compatible with all of these, I use ASCII flatfiles to store most of my data, with fixed-column width, fixed-precision reporting. Any time I have to work with something that isn't set up in this way, it always ends up being a hassle, regardless of how sexy or novel the method for storing the data was.

Having empty columns isn't actually a problem, so long as you figure out a method for flagging them properly. Leaving them blank isn't actually the best option, as this can read (i.e. in Fortran) as a 0, which is decidedly different for most applications to an empty value. If you think your client will want to use any sort of programming language-based analysis, you'll want to try to come up with a consistent way to store / flag missing values. For example, if all of your data samples are positive real numbers, then storing a -99.99 is a good way to flag an entry as missing.

Conclusion: figure out what your client is likely to require. If you really don't know, then go with #1, because it is the most general and the easiest to read in for multiple programs and programming languages. Remember to store the dimension information at the top of the file if you're using ASCII flatfiles, or in a defined data block if you're using binary files.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy