id <- c(1:8,1:8)
age1 <- c(7.5,6.7,8.6,9.5,8.7,6.3,9,5)
age2 <- age1 + round(runif(1,1,3),1)
age <- c(age1, age2)
tanner <- sample(1:2, 16,replace=T)
df <- data.frame(id,age,tanner)
id age tanner
1 1 7.5 2
2 2 6.7 1
3 3 8.6 2
4 4 9.5 2
5 5 8.7 1
6 6 6.3 1
7 7 9.0 1
8 8 5.0 1
9 1 10.0 1
10 2 9.2 1
11 3 11.1 1
12 4 12.0 2
13 5 11.2 2
14 6 8.8 2
15 7 11.5 1
16 8 7.5 1
Above is a sample data frame. I would like to convert it into the below format.
id age at tanner=1 age at tanner=2
1 10 7.5
2 6.7 NA
3 11.1 8.6
4 NA 9.5
...
If at both ages, the tanner record is the same, I want it to keep the younger age.
For example,
id age tanner
2 6.7 1
2 9.2 1
In this case, 6.7 will be kept for id=2 in the new dataset.
aggregate
then reshape
(using a copied and pasted version of your df
rather than your code, that doesn't match):
reshape(
aggregate(age ~ ., data=df, FUN=min),
idvar="id", timevar="tanner", direction="wide"
)
# id age.1 age.2
#1 1 10.0 7.5
#2 2 6.7 NA
#3 3 11.1 8.6
#4 5 8.7 11.2
#5 6 6.3 8.8
#6 7 9.0 NA
#7 8 5.0 NA
#10 4 NA 9.5
We can use dcast
to convert from 'long' to 'wide' and use the fun.aggregate
as min
. Here I converted the 'data.frame' to 'data.table' (setDT(df)
) as the dcast
from data.table
would be fast.
library(data.table)
res <- dcast(setDT(df), id~paste('age',tanner,sep='.'), value.var='age', min)
res
# id age.1 age.2
#1: 1 10.0 7.5
#2: 2 6.7 Inf
#3: 3 11.1 8.6
#4: 4 Inf 9.5
#5: 5 8.7 11.2
#6: 6 6.3 8.8
#7: 7 9.0 Inf
#8: 8 5.0 Inf
If we want to change the 'Inf' to 'NA'
res[,(2:3) := lapply(.SD, function(x)
replace(x, is.infinite(x), NA)),.SDcols= 2:3]
A little dplyr
and tidyr
does the trick here. arrange
by age so lowest age appear first then use a filter
for duplicated id/tanner then utilize tidyr::spread
df<-
data.frame(
id = c(1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8)
,age = c(7.5, 6.7, 8.6, 9.5, 8.7, 6.3, 9.0, 5.0,10.0, 9.2,11.1,12.0,11.2, 8.8,11.5, 7.5)
,tanner = c(2,1,2,2,1,1,1,1,1,1,1,2,2,2,1,1)
)
library(dplyr)
library(tidyr)
wide <-
df %>%
arrange(age) %>%
filter(!duplicated(paste(id, tanner))) %>%
spread(tanner, age)
colnames(wide) = c('id', 'tanner1', 'tanner2')
wide
# id 1 2
# 1 10.0 7.5
# 2 6.7 NA
# 3 11.1 8.6
# 4 NA 9.5
# 5 8.7 11.2
# 6 6.3 8.8
# 7 9.0 NA
# 8 5.0 NA