I have the following panel data:
idNum date salePrice
1 01.2018 1
1 02.2018 2
2 01.2018 3
2 02.2018 4
... ... ...
I want a new column which shows me the quartile rank for each date like this:
idNum date salePrice quartilerank
1 01.2018 1 1
1 02.2018 2 1
2 01.2018 3 2
2 02.2018 4 2
... ... ...
Working with the function:
TER <- within(TER, quartile <- as.integer(cut(salesPrice, quantile(salesPrice, probs=0:4/4), include.lowest=TRUE)))
Gives me only the quartile rank based on all sales prices and doesn't distinguish for dates.
If I've understood well, you need to calculate the quartile intra data, so maybe this can help:
# some fake data
data <- data.frame(idNum=c(1,1,2,2,3,3,4,4),
date=c('01.2018','02.2018','01.2018','02.2018','01.2018','02.2018','01.2018','02.2018'),
salePrice=c(1,2,3,4,5,6,7,8))
data
idNum date salePrice
1 1 01.2018 1
2 1 02.2018 2
3 2 01.2018 3
4 2 02.2018 4
5 3 01.2018 5
6 3 02.2018 6
7 4 01.2018 7
8 4 02.2018 8
# an empty list to populate
qlist <- list()
# the loop that create the list with quartile for each date
for(k in data$date) {
subdata = subset(data, date == k)
subdata$quartile = cut(subdata$salePrice,4,labels=F)
qlist[[k]] <- subdata
}
# have it as a df
df <- do.call("rbind",qlist)
df
idNum date salePrice quartile
01.2018.1 1 01.2018 1 1
01.2018.3 2 01.2018 3 2
01.2018.5 3 01.2018 5 3
01.2018.7 4 01.2018 7 4
02.2018.2 1 02.2018 2 1
02.2018.4 2 02.2018 4 2
02.2018.6 3 02.2018 6 3
02.2018.8 4 02.2018 8 4