Let's say I have multiple columns in a data frame that measure the same concept, but in different methods (e.g. there are multiple kinds of IQ tests, and students could have any one of them, or none at all). I want to combine the various methods into a single column (obvious use case for tidyr).
If the data is something like this:
mydata <- data.frame(ID = 55:64,
age = c(12, 12, 14, 11, 20, 10, 13, 15, 18, 17),
Test1 = c(100, 90, 88, 115, NA, NA, NA, NA, NA, NA),
Test2 = c(NA, NA, NA, NA, 100, 120, NA, NA, NA, NA),
Test3 = c( NA, NA, NA, NA, NA, NA, 110, NA, 85, 150))
I would naturally want to perform something like this (note that I use na.rm = TRUE in order to not have the many many NA's in my data set get their own rows):
library(tidyr)
tests <- gather(mydata, key=IQSource, value=IQValue, c(Test1, Test2, Test3), na.rm = TRUE)
tests
Giving me:
ID age IQSource IQValue
1 55 12 Test1 100
2 56 12 Test1 90
3 57 14 Test1 88
4 58 11 Test1 115
15 59 20 Test2 100
16 60 10 Test2 120
27 61 13 Test3 110
29 63 18 Test3 85
30 64 17 Test3 150
The problem is that I have a student (ID=62) that doesn't have any IQ scores in any of the three, and I don't want to lose her other data (the data in the ID and age columns).
Is there a way to distinguish, in tidyr, that yes, I want to remove NA's where I do have data in at least one column I'm gathering, yet at the same time want to prevent data loss when all of the columns to gather are NA?)