How to group by a column and create different groups within that column with dplyr in R

user_9926677 0
Viewed 35

Hi I have a dataframe with a date column and some numeric columns

this is the data

      year_month total_visits search_brand_co~ search_non_bran~ facebook_cost display_cost total_organic_s~
  <date>            <int>            <dbl>            <dbl>         <dbl>        <dbl>            <int>
1 2020-11-01        91655           30314.           60676.        14548.        4555.           829852
2 2020-12-01        98227             327.            2027.            0         6895.          1047370
3 2021-01-01        91352               0                0           193.        7009.          1284317
4 2021-02-01        77060           15058.           18690.         6728.        6294.           668924
5 2021-03-01        96749           32883.           87256.            0         5587.           418764
6 2021-04-01        84738           29919.           71820.            0         2655.           297460

what I need is group by that will sum the columns where the date column "year_month" is smaller or equal than "2021-01-01" and the other where "year_month" is greater or equal than "2021-02-01"

the final dataframe should only have 2 rows with the same number of columns

Thanks for the help

dplyr r
1 Answers

We may need to create a grouping by just creating a logical vector or convert it to integer with + or as.integer and then summarise on the numeric columns to get the sum

library(dplyr)
df1 %>%
     group_by(grp = c('gt_2021_01_01', 'lt_2021_01_01')[1 + 
             (year_month < as.Date("2021-01-01"))]) %>%
     summarise(across(where(is.numeric), sum, na.rm = TRUE))

-output

# A tibble: 2 x 7
  grp           total_visits search_brand_co search_non_bran facebook_cost display_cost total_organic_s
  <chr>                <int>           <dbl>           <dbl>         <dbl>        <dbl>           <int>
1 gt_2021_01_01       349899           77860          177766          6921        21545         2669465
2 lt_2021_01_01       189882           30641           62703         14548        11450         1877222

data

df1 <- structure(list(year_month = structure(c(18567, 18597, 18628, 
18659, 18687, 18718), class = "Date"), total_visits = c(91655L, 
98227L, 91352L, 77060L, 96749L, 84738L), search_brand_co = c(30314, 
327, 0, 15058, 32883, 29919), search_non_bran = c(60676, 2027, 
0, 18690, 87256, 71820), facebook_cost = c(14548, 0, 193, 6728, 
0, 0), display_cost = c(4555, 6895, 7009, 6294, 5587, 2655), 
    total_organic_s = c(829852L, 1047370L, 1284317L, 668924L, 
    418764L, 297460L)), row.names = c("1", "2", "3", "4", "5", 
"6"), class = "data.frame")
user_37322710
Related