• What the wet-lab scientists wanted us to spend time looking at

• What we actually spent time looking at (Disclaimer: not actual data from this project)

No matter how much of the analysis is automated, some manual steps are inevitably involved

Cartoon by Sidney Harris (The New Yorker)

• At some point in the future, someone, somewhere, might want to repeat your analysis for themselves or re-use your data.
  • which will most likely be you!
• Assuming that you’ll be able to remember all the steps involved is dangerous, so making sure that everything is well-documented is key.

• An analysis needs to be automated to be properly reproducible
• Tools like R, Python, will help with this but the data needs to be formatted correctly in the first place
  • R, Python cannot magically read any type of file you give them
• Making spreadsheets tidy at the start of the analysis will save a lot of time in the long-run

• ….Not neccesarily.
• Often much more convenient to eye-ball a spreadsheet and get an overall impression of your data.
• But they have limitations making them not ideal for large-scale analyses.
• Doing things by-hand only invites you to make copy-and-paste errors etc

• Never work directly on the raw data
• Hard to reverse all the manual steps performed and invites errors
• Store the original data somewhere safe
  • see later

• Consistency: F, female, f, fem, 2, …
• Units
  • cm or mm; days, months or years
• You can introduce inconsistencies without realising it
  • blank spaces (whitespace) at the end of text
  • “Male” is not the same as “Male”
• Document choices you make about units in a README file

credit: @myusuf3

Figure showing locations of visitors to my Prostate Cancer data portal

• Zero values are data!
  • Sometimes extreme values such as 999 are sometimes used
• NA is Ok, but what if NA is a valid category in your data?
  • R will recognise NA as a missing value and can ignore it in calculations
• Safest to leave the cell empty
  • but you need to be careful with blank spaces

Fill in all the cells

• It is tempting to make the table look cleaner by not repeating some values
• Fill in all cells!
  • otherwise, problems when sorting
• Empty cell:
  • missing value?
  • value meant to be repeated multiple times?
• Make sure it’s clear that the data is missing and not unintentionally left blank

Make it rectangle

• The computer expects a very rigid shape of data with rows and columns
• Each column is a variable being examined
• Each row is an observation
• A concept commonly known as tidy data

• Don’t put too much information in one cell
  • 1 cell = 1 piece of information
• Don’t include units such as “30 g” → “g” in the column name
  • http://unitsofmeasure.org/ucum.html
• Write notes in a separate column or data dictionary or metadata
  • “0 (below threshold)”

• NO calculations
• NO font colours
• NO highlighting

Computer doesn’t recognize it!

Mac

• Right click on the file in Finder
• Select “Get Info”
• Sharing and permission
• Priviledge
• Read only

Windows

• Right click on the file in windows explorer
• Properties
• General tab
• Attributes
• Select the box for “read only”

• Excel data validation feature
• Select a column
  • In the menu bar, choose “Data”
  • Validation
• Integer or decimal number
• Range
• List of possible values
• Limited length text

• When identifiers are long integers
  • 1000000 = 1e06
  • Issue with Illumina microaray chip IDs
• Excel can convert gene names to dates
  • SEPT2 (Septin 2) → ‘2-Sep’
  • MARCH1 (Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase) → ‘1-Mar’

• Download the file patient-data.csv and open in Excel, or equivalent software
  • this is a simulated, but representative, example of bad data
  • discuss with the members of your breakout group what aspects of the dataset would make it difficult for a computer to process (around 10 minutes)
• The next step is to look at how to clean the data with Open Refine