If in doubt, aim for consistency in every column. Never try to record more than one “type” of thing in a column.

Integers & Decimals

• ...,-3,-2,0,1,2,3,... versus 3.141529
• Integers are any whole number
• Decimals include any number with a decimal point

Strings

A string is any sequence of characters.

• Literally anything you can type can be represented as a string.
• Default type in Excel
• Be careful 180mg is not a number!

Date/Time objects

• A number as far as the computer is concerned
• Often (but not always)
  • Dates are integers counted from 1 Jan 1970
  • Times are fractions of a day
• Other possibilities
  • milliseconds since 1960
  • Days since January 0 1900 (Excel!)

Date gotcha’s

Booleans

• TRUE or FALSE statements.
• 1 or 0 is a common shorthand

Factors

• ‘Categorical’ (ordered or unordered)
• Integers with labels

Nominal

• An unordered (nominal) factor
• Named but not ordered

- apples
- oranges 
- pears

• R stores this as 1,2,3 for convenience but not because 1<2<3.

Ordinal

• An ordered (ordinal) factor such as a Likert scale
• Ordered and named

- Strongly disagree
- Disagree
- Neither agree/disagree
- Agree
- Strongly agree

• R stores this as 1,2,3,4,5 for convenience and understands that 1<2<3<4<5

Your turn …

In R studio,

integers <- as.integer(c(1, 3, 15, 16))
decimals <- c(1.4, 3.5, 15.55, 16.4)
bools <- c(T, T, F, T)
dates <- as.Date(c("22/04/2016", "13/05/1997"), format = "%d/%m/%Y")
strings <- c("These are", "Strings")
factors <- as.factor(c("Apples", "Pears", "Lemons"))
ordered_factors <- factor(c("Good", "Better", "Best"), ordered = TRUE)

then use str() to see the data ‘structure’

Answers

str(integers)

##  int [1:4] 1 3 15 16

str(decimals)

##  num [1:4] 1.4 3.5 15.6 16.4

str(bools)

##  logi [1:4] TRUE TRUE FALSE TRUE

str(dates)

##  Date[1:2], format: "2016-04-22" "1997-05-13"

str(strings)

##  chr [1:2] "These are" "Strings"

str(factors)

##  Factor w/ 3 levels "Apples","Lemons",..: 1 3 2

str(ordered_factors)

##  Ord.factor w/ 3 levels "Best"<"Better"<..: 3 2 1

Patient 1

• 36 yr-old primigravida, gestational diabetes, 38 weeks, category 2 Caesarean section: spinal.

Patient 2

• 28 yr-old 2 previous pregnancy, now third pregnancy, 40 weeks, epidural for analgesia.

Patient 3

• 30 yr-old, 1 previous pregnancy, twins on this pregnancy, 36 weeks, induction, epidural.
• Later patient goes for Category 1 caesarean section, failed epidural top-up, converted to GA.

Compare

• Now get up and compare with others at your table, does their spreadsheet look like yours?

Columns

Put all your variables in columns - the thing you’re measuring, like ‘weight’, ‘temperature’ or ‘SBP’. Break things down into their most basic constituents, and keep units in your headers only.

Rows

Put each observation in its own row. Think very carefully about what constitutes your basic observation. Often it’s your patient, but it may not be as intuitive as you think.

Headers

Have a single ‘header’ row to label your columns

Cells

Don’t combine multiple pieces of information in one cell.

Leave the raw data raw - don’t mess with it! That means no formulas anywhere in your spreadsheet!

Sharing

Export the cleaned data to a text based format like CSV. This ensures that anyone can use the data, and is the format required by most data repositories.

The computer doesn’t care about formatting

We do …

just add some white space and dividers

And Excel …

And all excel does is present it to us in an easy to use format.

But …

But you always need to remember that you need to go back and forth between both formats.

So merged cells, colours, comments will both be lost and confuse.

The data

• Raw data from an RCT on pain relief following mastectomy
• Download from FigShare.

Original (dirty)

Your mission …

Identify and fix these common mistakes

• Multiple tables
• Multiple tabs
• Not filling in zeros
• Using bad null values
• Using formatting to convey information
• Using formatting to make the data sheet look pretty
• Placing comments or units in cells
• More than one piece of information in a cell
• Field name problems
• Special characters in data
• Inclusion of metadata in data table
• Date formatting

Multiple nested tables

Null values and empty cells

Formatting to convey meaning

• 3 sheets: readme, dictionary, data … then export,share the data sheet
• Data validation in Excel
• Learn to export to CSV (next lesson)