Calculate percent change over time by group base R

user_10100254 0
Viewed 194

Appreciate any help on this, I'm trying to re-learn some fundamentals.

Here is some sample code to work with for my problem, it is from a database of injured workers. 

Area <- c("Connecticut", "Maine", "Massachusetts", "New Hampshire", "Texas", "Arizona", "California", "Washington")
Region <- c("Northeast", "Northeast", "Northeast", "Northeast", "South", "South", "West", "West") 
X2004 <- c(0,1,4,1,3,4,2,2)
X2005 <- c(1,0,6,2,0,1,0,2)
X2006 <- c(0,0,1,1,2,1,0,0) 
df1 <- data.frame(Area, Region, X2004, X2005, X2006)

I would like to show the percent change from the two-year average of 2004-2005 to the single year 2006 in Base R. I was able to solve this with the tidyverse package, but that feels like using a crutch. Here is what I have so far:

df2 <- reshape(df1, 
              idvar=c("Area"), 
              v.names="count",
              varying=c("X2004","X2005","X2006"), 
              direction="long",
              times=2004:2006, 
              timevar="year")
df3 <- df2 %>% group_by(Region, year) %>% 
summarise(total_count = sum(count)) 
df3$pre <- ifelse(df3$year<=2005, 1, 0)
df3 %>% 
group_by(Region) %>%  
summarise(mean_count_pre = mean(total_count[pre==1]),
        mean_count_post = mean(total_count[pre==0]), 
        pct_change = 100*(mean_count_post - mean_count_pre) / mean_count_pre)

Any ideas on how to tackle this without relying on tidyverse or dplyr? Really appreciate help on this, I learned R in tidyverse, and I'm trying to get a better understanding of the fundamentals.

aggregate r
2 Answers

Using your df2 as input, we can use only R base functions this way:

> # creating `total_count`
> df3<- df2
> df3$total_count <- with(df2, ave(count, Region, year, FUN="sum"))
> 
> # creating `pre`
> df3$pre <- ifelse(df3$year<=2005, "pre", "post")
> 
> # creating "mean_count_pre" and "mean_count_post"
> output <- aggregate(total_count ~ Region+pre, data=df3, FUN="mean")
> colnames(output)[3] <- "mean_count"
> output_wide <- reshape(output, v.names="mean_count", idvar="Region", timevar = "pre", direction = "wide")
>
> # creating `pct_change`
> output_wide <- transform(output_wide, pct_change=(mean_count.post-mean_count.pre)/mean_count.pre)
> output_wide
     Region mean_count.post mean_count.pre pct_change
1 Northeast               2            7.5 -0.7333333
2     South               3            4.0 -0.2500000
3      West               0            3.0 -1.0000000
user_13157670

Consider aggregate as replacement of group_by and summarise and use of double aggregates for pre and post calculations merged by Region. Both within and transform are used for for inplace column assignments and setNames to rename columns which cannot be done during aggregation.

Tidyverse

df3 <- df2 %>% group_by(Region, year) %>% 
  summarise(total_count = sum(count)) 

df3$pre <- ifelse(df3$year<=2005, 1, 0)

aggdf <- df3 %>% 
  group_by(Region) %>%  
  summarise(mean_count_pre = mean(total_count[pre==1]),
            mean_count_post = mean(total_count[pre==0]), 
            pct_change = 100*(mean_count_post - mean_count_pre) / mean_count_pre)

Base R

df3_base <- setNames(aggregate(count~Region + year, df2, sum), 
                     c("Region", "year", "total_count"))

df3_base <- within(df3_base, {      
      pre <- ifelse(year <= 2005, 1, 0)
      count_pre <- ifelse(pre==1, total_count, NA)
      count_post <- ifelse(pre==0, total_count, NA)      
})

aggdf_base <- transform(setNames(merge(aggregate(count_pre ~ Region, df3_base, FUN = mean),
                                       aggregate(count_post ~ Region, df3_base, FUN = mean),
                                       by="Region"),
                                 c("Region", "mean_count_pre", "mean_count_post")),
                        pct_change = 100*(mean_count_post - mean_count_pre) / mean_count_pre)

Comparison

identical(data.frame(aggdf), aggdf_base)
# [1] TRUE
user_14224510
Related