CODEWITHSUNDEEP
rif-statementcategorical-data
MShields
MShields

Reputation: 23

r - categorizing continuous data by existing groups

I'm a relative novice in R and I have a series of Census Tracts' socioeconomic scores (SES) over a 5-yr period and I'm trying to categorize each year's SES scores into three categories of "High", "Medium", and "Low" without having to subset the data.

     CT_ID_10 year SESindex SESindex_z SEStercile
1 42101009400 2012 11269.54 -1.0445502         NA
2 42101009400 2013 11633.63 -1.0256920         NA
3 42101009400 2014 15773.60 -0.8112616         NA
4 42101009400 2015 15177.28 -0.8421481         NA
5 42101009400 2016 21402.55 -0.5197089         NA
6 42101014000 2012 21448.06 -0.5173519         NA

I want to use the mean and standard deviations as my cutoff points (i.e. anything above the mean(x[per year]) + sd(x[per year]) is "High" while anything below the mean(x[per year]) - sd(x[per year]) is "Low". I tried the following code:

for (year in 2012:2016) {
  df$SEStercile <- ifelse(df$SESindex_z[which(df$year==year)] > (mean(df$SESindex_z[which(df$year==year)])+sd(df$SESindex_z[which(df$year==year)])), "HIGH",
  ifelse(df$SESindex_z[which(df$year==year)] < (mean(df$SESindex_z[which(df$year==year)])-sd(df$SESindex_z[which(df$year==year)])), "LOW","MEDIUM"))
}

However, I received the following error:

Error in `$<-.data.frame`(`*tmp*`, "SEStercile", value = c("LOW", "LOW", :  
replacement has 367 rows, data has 1839

Any advice or simple functions would be greatly appreciated!

Upvotes: 1

Views: 70

Answers (1)

Dan
Dan

Reputation: 12084

This solution uses dplyr. Here, I create a data frame with random data for demonstration purposes:

df <- data.frame(year = sample(2010:2018, 100, replace = TRUE),
                 z = runif(100))

Next, I group by year and cut using standard deviations as breaks. Then, I ungroup the resulting tibble.

df %>% 
  group_by(year) %>% 
  mutate(category = cut(z, 
                        breaks = c(-Inf, mean(z) - sd(z), mean(z) + sd(z), Inf),
                        labels = c("Low", "Medium", "High"))) %>% 
  ungroup

The result looks something like this (for 2010, for example):

# # A tibble: 11 x 3
#      year     z category
#     <int> <dbl> <fct>   
# 1   2010 0.585 Medium  
# 2   2010 0.951 High    
# 3   2010 0.747 Medium  
# 4   2010 0.802 Medium  
# 5   2010 0.673 Medium  
# 6   2010 0.662 Medium  
# 7   2010 0.102 Low     
# 8   2010 0.129 Low     
# 9   2010 0.934 High    
# 10  2010 0.270 Medium  
# 11  2010 0.270 Medium

Your code might specifically look like this:

df %>% 
  group_by(year) %>% 
  mutate(SEStercile = cut(SESindex_z,
                          breaks = c(-Inf, mean(SESindex_z) - sd(SESindex_z), mean(SESindex_z) + sd(SESindex_z), Inf),
                          labels = c("Low", "Medium", "High"))) %>% 
  ungroup

Upvotes: 2

Related Questions