Organizing your data

Intro and Objectives

We often work with tabular data in spreadsheets - it’s kind of what spreadsheets were designed for. There are some data organization principles that can help you organize tabular data in a way that:

• makes it easier to analyze

• helps avoid errors

• makes it easier to share with collaborators

• makes it easier to move between other analysis tools (e.g. Tableau, R, Python)

• makes you a better Excel user

• makes you a better analyst

Readings

The following blog post does a terrific job of articulating important spreadsheet organization principles. The post led to a journal article in The American Statistician. You can find a PDF of the journal article at the link below (and in the Downloads file).

• Original blog post - https://kbroman.org/dataorg/

• Subsequent journal article - https://kbroman.org/Paper_DataOrg/manuscript.pdf

In addition, the folks at Data Carpentry have a freely accessible tutorial on spreadsheet organization principles that they aimed at ecologists. Many good tips to be found here.

• https://datacarpentry.org/spreadsheet-ecology-lesson/

Downloads

Downloads-Module04-DataOrg-MiscPowerExcel.zip

Activities

In the Downloads file you’ll find a subfolder named DataOrgPrinciples. Within there you’ll find a file named DataOrg.xlsm. Get that file open and this series of screencasts will cover some important data organization principles using this file.

We’ll start with an overview of the data org blog post by Broman and an intro to the file I’ve created to illustrate some of the important data org principles.

• SCREENCAST: Intro to data organization in spreadsheets (3:13)

Let’s learn about tidy data and also explore doing some computations with that data.

• SCREENCAST: Tidy data (10:47)

What’s the difference between wide and long data? How do we reshape data to get it into the form we need for analysis?

• SCREENCAST: Data reshaping (12:32)

What to do about missing data? What is #N/A?

• SCREENCAST: Missing data (12:04)

Dates - you really need to be careful with date formats, especially when sharing data. We’ve already learned how Excel stores dates as integers (or “serial values”). There’s more chapters to this story.

• SCREENCAST: Date formats (7:40)

I highly encourage you to read through the entire blog post by Broman. Much wisdom in there.

Explore (OPTIONAL)

Here are some more links to material on good (and bad) spreadsheet practices.

• https://luisdva.github.io/pls-don’t-do-this/

• https://github.com/jennybc/2016-06_spreadsheets

• https://datachampcam.github.io/avoid-data-disaster/

• http://okfnlabs.org/bad-data/