Add new Rows which sums & counts values

My data looks something like this:

and so on until December. I want to add a row below every month which give the values- Total no of calories, no of days worked out- a basic summary. I have tried to use dplyr & tidyverse but not getting the correct results & the format I want it in.

The below script gives me by Month but I cannot add a new row.
wd %>%
group_by(Month) %>%
summarise(Total_Cal = sum(Calories))

Please advise.

I do not understand exactly what you mean by

However I guess you may be having a problem with the NA's in March. Try this:

wd   %>%  group_by(Month) %>%
summarise(Total_Cal = sum(Calories, na.rm = TRUE), days = n())

For a new question please supply your sample data in something like dput() format (?dput) or have a look at FAQ: How to do a minimal reproducible example ( reprex ) for beginners

I hope this helps.


(raw1 <- tribble(~Month,~Date,~Day,~Mins,~Calories,~Type,
  "April",2, "Thursday","00:43:13",140,"Strength",
  "April",5, "Sunday","00:30:00,65",NA,"Core",
  "April",6, "Monday","00:30:09,90",NA,"Cardio") %>%

(smry2 <- group_by(raw1,
                   Month) %>% summarise(
                     calorie_sum = sum(Calories,na.rm = TRUE)
empty_raw <- slice(raw1, 1) %>%
  select(-Month) %>%
  mutate_all(.funs = function(x) {
    y <- NA
    class(y) <- class(x)

  (smry3 <- expand_grid(smry2,empty_raw))

(to_display <- bind_rows(raw1,smry3) %>% arrange(Month,Date))

Something worked but what?

Would you supply use with the code and some sample data ?

It would be helpfull to have same data. In any case see

for a suggestion.

This works. Thanks so much! I had already removed the na values so that was not an issue.
However, I have two new problems.

  1. The data arranges itself basis the first letter of the month while I want it to be sorted according to the Month. Eg: March, April, May, June....
  2. How do I save this in a new data frame since I need to plot them?