R How to calculate the sum of existing rows to add new rows of data

Question

How do i add a new Category into comb_df where the value of this new newcat is sum of

Category = New Jan 2014 + Category = Old Jan 2014

Category = c("New")
value = c(4,7,6)
Date= c ("Jan 2014", "Feb 2014", "Mar 2014")
#NonDaily = c(4, 7,6 ,8,9,10)

df1 <- data.frame(Category, Date,value,  check.names = FALSE)

Category = c("Old")
value = c(1,2,4)
Date= c ("Jan 2014", "Feb 2014", "Mar 2014")
df2 <- data.frame(Category,Date, value,check.names = FALSE)

comb_df <- rbind(df1,df2)

  Category     Date     value
1      New    Jan 2014     4
2      New    Feb 2014     7
3      New    Mar 2014     6
4      Old    Jan 2014     1
5      Old    Feb 2014     2
6      Old    Mar 2014     4

Expected Outcome

newcat = Category = New Jan 2014 + Category = Old Jan 2014

  Category     Date     value  
1      New    Jan 2014     4    
2      New    Feb 2014     7    
3      New    Mar 2014     6     
4      Old    Jan 2014     1
5      Old    Feb 2014     2
6      Old    Mar 2014     4
7   newcat    Jan 2014     5
8   newcat    Feb 2014     9
9   newcat    Mar 2014     10

Answer 1

  library(dplyr)
  comb_df %>% 
    add_row(Category = "newcat",
            comb_df %>% 
              summarise(value = sum(value), .by = "Date")
            )
  Category     Date value
1      New Jan 2014     4
2      New Feb 2014     7
3      New Mar 2014     6
4      Old Jan 2014     1
5      Old Feb 2014     2
6      Old Mar 2014     4
7   newcat Jan 2014     5
8   newcat Feb 2014     9
9   newcat Mar 2014    10

Base R

rbind(comb_df, data.frame(Category = "newcat", aggregate(value ~ Date, FUN = sum, data = comb_df)))
  Category     Date value
1      New Jan 2014     4
2      New Feb 2014     7
3      New Mar 2014     6
4      Old Jan 2014     1
5      Old Feb 2014     2
6      Old Mar 2014     4
7   newcat Feb 2014     9
8   newcat Jan 2014     5
9   newcat Mar 2014    10

Answer 2

You could pivot wider then longer:

library(dplyr)
library(tidyr)

comb_df %>%
  pivot_wider(names_from = Category, values_from = value) %>%
  mutate(newcat = sum(New, Old), .by = Date) %>%
  pivot_longer(-Date, names_to = "Category") %>%
  arrange(Category, Date)

Output

#   Date     Category value
#   <chr>    <chr>    <dbl>
# 1 Feb 2014 New          7
# 2 Jan 2014 New          4
# 3 Mar 2014 New          6
# 4 Feb 2014 Old          2
# 5 Jan 2014 Old          1
# 6 Mar 2014 Old          4
# 7 Feb 2014 newcat       9
# 8 Jan 2014 newcat       5
# 9 Mar 2014 newcat      10