Sum Specific Rows by Multiple Groups

user_14315772 0
Viewed 60

I have a dataframe like the one below...

df <- data.frame(row.names = c(1,2,3,4,5,6,7,8), Week = c(1,1,2,2,52,52,53,53), State = c("Florida", "Georgia","Florida", "Georgia","Florida", "Georgia","Florida", "Georgia"), Count_2001 = c(25,16,83,45,100,98,22,34), Count_2002 = c(3, 78, 22, 5, 78, 6, 88, 97))

I am now trying to manipulate this dataset such that only weeks 52 and 53 get summed together for each state in the list, across all of the Count columns. Similar to this example.. GROUP BY for specific rows

The new dataset should have these rows summed together to create the new Week 52 row for each state, like this example below...

df2 <- data.frame(row.names = c(1,2,3,4,5,6), Week = c(1,1,2,2,52,52), State = c("Florida", "Georgia","Florida", "Georgia","Florida", "Georgia"), Count_2001 = c(25,16,83,45,122,132), Count_2002 = c(3, 78, 22, 5, 166, 103))

Is there an easy solution for this in R?

dataframe r group-by
3 Answers

Change your 53s to 52s and do a sum by group:

library(dplyr)
df %>%
  mutate(Week = case_when(Week == 53 ~ 52, TRUE ~ Week)) %>%
  group_by(State, Week) %>%
  summarize(across(everything(), sum))
# # A tibble: 6 x 4
# # Groups:   State [2]
#   State    Week Count_2001 Count_2002
#   <chr>   <dbl>      <dbl>      <dbl>
# 1 Florida     1         25          3
# 2 Florida     2         83         22
# 3 Florida    52        122        166
# 4 Georgia     1         16         78
# 5 Georgia     2         45          5
# 6 Georgia    52        132        103
user_9030610

Using aggregate.

s <- 52:53
tp <- transform(aggregate(cbind(Count_2001, Count_2002) ~ State, df[df$Week %in% s, ], sum),
          Week=52)
df <- merge(df[!df$Week %in% s, ], tp, all=T)
df
#   Week   State Count_2001 Count_2002
# 1    1 Florida         25          3
# 2    1 Georgia         16         78
# 3    2 Florida         83         22
# 4    2 Georgia         45          5
# 5   52 Florida        122        166
# 6   52 Georgia        132        103
user_65740380

A simple alternative to using anything state specific would just be to create a new column with weeks at the level of aggregation that works!

I'd get this by doing: (using the tidyverse library)

df <- df %>%
    mutate(week1 = if_else(week %in% c(52,53),52,week)


and then you can summate as 

dfsumm <- df %>%
    group_by(state, week1)%>%
    summarise()
user_99412580
Related