Overview
Teaching: 15 min
Exercises: 0 minQuestions
What are basic principles for using spreadsheets for good data organization?
Objectives
Understand how to organize data so computers can make the best use of the data
Good data organization is the foundation of any research project. Many curators and researchers maintain their data or perform data entry in spreadsheets. Spreadsheet programs are very useful graphical interfaces for designing data tables and handling very basic data quality control functions. This lesson highlights best practices for maintainting data tables in spreadsheets to faciliate downstream enhancement and analysis, by both humans and machines.
In this lesson, we’re going to talk about:
Two relevant references for further information about tidy data and spreadsheet best practices we recommend:
Overall good data practices
Spreadsheets are good for data entry. Therefore we have a lot of data in spreadsheets. Much of your time as a researcher will be spent in this ‘data wrangling’ stage making sure that the data is complete, clean, consistent, and clearly organized so that subsequent steps in your research pipeline will go smoothly and accurately. In this lesson we’ll teach you how to think about data organization and some practices for more effective data wrangling.
If you’re looking to do the above processes, a good reference is Head First Excel, published by O’Reilly
Data analysis in spreadsheets usually requires a lot of manual work. If you want to change a parameter or run an analysis with a new dataset, you usually have to redo everything by hand. (We do know that you can create macros, but see the next point.)
It is also difficult to track or reproduce statistical or plotting analyses done in spreadsheet programs when you want to go back to your work or someone asks for details of your analysis.
Many spreadsheet programs are available. Since most participants utilize Excel as their primary spreadsheet program, this lesson will make use of Excel examples. There are others (gnumeric, Calc from OpenOffice, Google Sheets), and their functionality is similar, but Excel seems to be a program most commonly used by researchers.
Spreadsheets are popular researcher tools because they encompass a lot of the things we need to be able to do. We can use them for:
Spreadsheets are good for data entry, but in reality we tend to use spreadsheet programs for much more than data entry. We use them to create data tables for publications, to generate summary statistics, and make figures.
Generating tables for publications in a spreadsheet is not optimal - often, when formatting a data table for publication, we’re reporting key summary statistics in a way that is not really meant to be read as data, and often involves special formatting (merging cells, creating borders, making it pretty). We advise you to do this sort of operation within your document editing software.
The latter two applications, generating statistics and figures, should be used with caution: because of the graphical, drag and drop nature of spreadsheet programs, it can be very difficult, if not impossible, to replicate your steps (much less retrace anyone else’s), particularly if your stats or figures require you to do more complex calculations. Furthermore, in doing calculations in a spreadsheet, it’s easy to accidentally apply a slightly different formula to multiple adjacent cells. When using a command-line based statistics program like R or SAS, it’s practically impossible to apply a calculation to one observation in your dataset but not another unless you’re doing it on purpose.
In this lesson we’re going to explore 5 primary spreadsheet practices:
Key Points
Good data organization is the foundation of any research project.