Principles and practices for managing and structuring data

Facundo Muñoz

Data storage

Somme common issues

• I have several copies of the data but I’m not sure which is the latest version.

• I have the data, but I’m not sure I have the latest version.

• I have a data file but I can’t remember what it contains.

• I have data, but I can’t find it.

• I don’t know how to interpret some of the variables.

• I have corrected some errors and have been sent a new version based on the original data.

Wasted time

Unnoticed errors

Some principles

1. Limit Avoid duplication of data

2. Manage versions.

3. Document data (metadata)

4. Adopt a filename convention

Start a document of guidelines to follow

• A guide for our actions. Not always possible, but can help us make decisions.

• Better to refer to a central data repository than to send copies by e-mail.

• Data can evolve (add, correct). Use any system. Either a tool or a procedure. A date in the file name may be sufficient. But you must be consistent and sytematic.

• A quoted text file, for example, or a tool (repository, see 1). Description of variables, data source, etc.

• Think about the names of the files (and variables) so that they make sense, reflect their content and speak for themselves.

Every situation is different, and I can’t suggest a system that will suit you all.

The choice of system is not as important as having one.

Spreadsheets

Combine :

• data storage

• data entry

• visualisation (tables, format)

• analysis (formulas, conditions, results, summaries, etc.)

• figures

• I love spreadsheets, I honestly think they’re a fantastic tool.

• But I don’t use them for storing or manipulating data

The requirements for data entry, storage and visualisation are fundamentally different

Stored data must be touched as little as possible to minimise the risk of accidental modification.

Horror stories

• MS Excel interprets dates and stores them internally as a number… with different conventions for Mac and Windows

• MS Excel interprets automatically certain texts as dates. E.g. the symbol for the gene “Oct-4” can be overwritten without notification.

  A 2016 study found this type of error in 20% of published gene lists.

• Excel has a bad habit of interpreting data (to improve visualisation) but changing it in the process.

• Data interpretation can depend on the operating system, the system language and other parameters.

You will use the tools you master,

not necessarily those you need.

You can use them, but think about the principles of data management

Ok. That’s fair enough. I understand that.

You can do an analysis on Excel, but do it on a separate sheet. Don’t touch the data. It’s better to use a separate file (even if it violates the principle of avoiding data duplication!!).

Naming files (and variables)

There are only two hard things in Computer Science: cache invalidation and naming things.

Phil Karlton

NO

myabstract.docx
Jane's Filenames Use Spaces and Punctuation.xlsx
figure I.png
fig 2.png
JW7d^(2sl@deletethisandyourcareerisoverWx2*.txt

Yes

2014-06-08_abstract-for-sla.docx
janes-filenames-are-getting-better.xlsx
fig01_scatterplot-talk-length-vs-interest.png
fig02_histogram-talk-attendance.png
1986-01-28_raw-data-from-challenger-o-rings.txt

3 principles for file names

1. Machine-readable

2. Human-readable

3. Works well with display order

• Avoid accents, punctuation and spaces.

• Use a regular, systematic structure

• delimit words and “groups” with - and _

• Dates in standard ISO-8601 format

• machine: facilitates file searches and the extraction of metadata from file names.

• human: describes content

Format standard ISO 8601

xkcd

Excellent file names

datacarpentry

Naming files (and variables)

Tools to help you develop a file naming convention tailored to your needs:

• Kristin Briney (2020) File naming convention worksheet

• Minnesota Historical Society

• University of Edinburgh, Records Management

Organising files

datacarpentry

My basic project structure

.
├── data/
├── doc/
├── reports/
├── src/
└── Readme.md

This one is mine. Not necessarily adapted to your needs. This is an example.

Depending on the case, there may be additional directories or files.

E.g. raw_data, derived_data, img/figures, paper

Principles

1. I never modify files in data.

2. All documentation to be read in doc (e.g. description of data, articles, etc.)

3. The analysis work takes place in src.

4. The results in reports.

It doesn’t matter what the system is. But having one.

Structuring data

Principles

Facilitate:

1. Importing and processing of data using different methods and tools

2. Understanding the structure of data

Some bad habits

Using multiple tables

It’s a nightmare to import!

It’s great for viewing and entering data, but this is data storage. Let’s keep things separate!

Encode information as cell formatting

This information is not retrieved if the data is read into R, for example. All information must be encoded in a variable.

Once again, we’re mixing storage and visualisation.

Empty cells

• Confusing zeros with missing values

• Blank = previous value

• Aggregate headers relating to several rows/columns

Mix storage and display

Non-rectangular structures

Again…

Good practices

• Maintain consistency (coding, capitalisation, format, etc.)

• ISO 8601 standard for dates (YYYY-MM-DD)

• Describing variables (metadata)

• Separating data from derived calculations

• Names: meaningful, short and descriptive

• Back up

• Store data in text files

Broman KW, Woo KH (2018)

Tidy data

openscapes

In addition to respecting these practices, there is a data organisation method based on the initial principles.

Julie Lowndes and Allison Horst

Julie Lowndes and Allison Horst

This structure makes it easier to understand and process the data.

It avoids the need for ad-hoc procedures and processing, depending on the nature of each situation.

Julie Lowndes and Allison Horst

This allows us to use standard tools designed for rectangular data. We learn to master the variables, the columns, and the observations, the rows. We can filter, select, combine, summarise and calculate.

The methods and tools are always useful.

Julie Lowndes and Allison Horst

Also facilitates collaboration.

Julie Lowndes and Allison Horst

Makes it easy to automate and sequence processing and analysis, updates, correct errors, etc.

The description of a data set

Principles

1. To be able to transfer data with all the information needed to work with it.

2. To avoid errors in interpretation

“Pass on” to another person or to yourself in the future!

Your primary collaborator is your past self,

but he doesn’t answer emails

Meta-data

• Readme file

  • Brief description of the project, data source and collection methods, context, objectives, references, contacts, etc.

• Description of variables (data-dictionary)

  • The name of the variable as it is used
  • Version of the name adapted for display
  • Type of variable (categorical, quantitative, etc.)
  • Units of measurement
  • Possible range of variation, possible values
  • Description of the variable

Data dictionary

Itself a data set!!

Conclusions

• Very vague suggestions, several choices and options

• No matter what system or tools:

  • Associated text files

  • Repository with integrated metadata (e.g. Dataverse)

  • Specific formats

  • Data Management Plan

• Choose one and respect the principles

References

• Kristin Briney (2020) File naming convention worksheet

• Data Carpentry (2018) Lesson on file organisation

• Data Carpentry (2020) Tidy data for efficiency, reproducibility, and collaboration

• Data Carpentry (2019) Data Organization in Spreadsheets for Ecologists

• Karl W. Broman & Kara H. Woo (2018). Data organisation in Spreadsheets. The American Statistician, 72:1, 2-10.

• Hadley Wickham (2014). Tidy Data. Journal of Statistical Software 58 (10).

Thank you

Slides made with quarto.

Based on reveal.js and knitr.

This work is licensed under Attribution - Share alike Creative Commons License 4.0 International.