0

I have a two level data (hospital level and region level), each hospital has an unique o:e:

hospid zipid  o:e
  1      1    0.8
  2      1    0.5
  3      1    0.4
  4      2    0.9
  5      2    1.2
  6      2    1.5

I want to generate the 25% quantiles and 75% quantiles of o:e by zipid, so that the output would be like this:

hospid zipid  o:e  q1    q3
  1      1    0.8  0.9  1.05
  2      1    0.5  0.9  1.05
  3      1    0.4  0.9  1.05
  4      2    0.9  1.0  1.10
  5      2    1.2  1.0  1.10
  6      2    1.5  1.0  1.10

I found a R code that can show the exact quantiles, but not sure how to extract those values and generating new variables based on those values. 

do.call("rbind", tapply(data$oe, data$zipid, quantile))
          0%       25%       50%       75%     100%
region1  0.93      0.99      1.02      1.04    1.11
region2  0.54      0.92      1.02      1.07    1.16

Any suggestions? Thanks!!

mandy
  • 483
  • 9
  • 20

2 Answers2

2
df$q1 = ave(df$o.e, df$zipid, FUN = function(x) quantile(x)[2])
df$q3 = ave(df$o.e, df$zipid, FUN = function(x) quantile(x)[4])
df
#  hospid zipid o.e   q1   q3
#1      1     1 0.8 0.45 0.65
#2      2     1 0.5 0.45 0.65
#3      3     1 0.4 0.45 0.65
#4      4     2 0.9 1.05 1.35
#5      5     2 1.2 1.05 1.35
#6      6     2 1.5 1.05 1.35

DATA

df = structure(list(hospid = 1:6, zipid = c(1L, 1L, 1L, 2L, 2L, 2L
), o.e = c(0.8, 0.5, 0.4, 0.9, 1.2, 1.5), q1 = c(0.45, 0.45, 
0.45, 1.05, 1.05, 1.05), q3 = c(0.65, 0.65, 0.65, 1.35, 1.35, 
1.35)), .Names = c("hospid", "zipid", "o.e", "q1", "q3"), row.names = c(NA, 
-6L), class = "data.frame")
d.b
  • 32,245
  • 6
  • 36
  • 77
  • 1
    I think it is better to have `df[c("q1", "q3")] <- lapply(c(2, 4), function(i) with(df, ave(o.e, zipid, FUN = function(x) quantile(x)[i])))` it in your answer. I willl delete that part – akrun Sep 22 '17 at 20:11
1

We can use data.table to create the columns 

library(data.table)
setDT(df)[, c('q1', 'q3') := as.list(quantile(o.e)[c(2, 4)]), zipid]
df
#   hospid zipid o.e   q1   q3
#1:      1     1 0.8 0.45 0.65
#2:      2     1 0.5 0.45 0.65
#3:      3     1 0.4 0.45 0.65
#4:      4     2 0.9 1.05 1.35
#5:      5     2 1.2 1.05 1.35
#6:      6     2 1.5 1.05 1.35
akrun
  • 874,273
  • 37
  • 540
  • 662