Collect your data carefully, a lesson

For the past couple of months I've been involved with an experiment that primarily involves taking a few moments every day to write down some numbers. I haven't gotten the clearance yet to quite say what exactly the data we are logging represents, but I can tell you about why I'm never again using spreadsheets to do this sort of stuff.

Over the weekend I started working on producing some graphs and useful understandable data out of two months of spreadsheets. The summer ones.

Turns out exporting to CSV then trying to import into a processable python data structure is less than easy when your tables were designed so that they look good as tables, are easy for humans to use and make sense when you look at them. The problem with python's handling of CSV is that it doesn't have any idea what my data actually looks like so it just produces a list of lists with values of all the cells.

My problem was that we were collecting three values for every day ... but until about a month in, we were only collecting two values. On top of that six rows translate into what is actually a single row of data with a certain range of values. In a table it's easier for people to use as six rows, but trying to draw a graph from that ... well you'd want a single row.

After quite a bit of muscling around and playing with more list comprehensions than fit comfortably in my head, that same data looks like this and is much more manageable.

{'A': [[2, 3, -1], [3, 1, -1], [2, 2, -1], [2, 0, -1], [1, 0, -1], ...],
'B': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, ...]
'C': [0, 0, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, ...], }

It pains me to admit it, but it took much too long to write a lens between the spreadsheet data and the data structure I can now actually use. Don't do this to yourselves and just make sure that whenever you are collecting data for a long lasting experiment, write some sort of easy interface that pops values right into the database.

It will make your life easier.