Add new Rows which sums & counts values

[ashex08]

My data looks something like this:
|Month|Date|Day|Mins|Calories|Type|
|March|16|Monday|00:12:20||Running|
|March|17|Tuesday|00:44:11|140|Strength|
|March|19|Thursday|00:15:42||Running|
|April|2|Thursday|00:43:13|140|Strength|
|April|5|Sunday|00:30:00|65|Core|
|April|6|Monday|00:30:09|90|Cardio|

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.

[jrkrideau]

I do not understand exactly what you mean by

@ashex08

I cannot add a new row.

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.

[nirgrahamuk]

library(tidyverse)

(raw1 <- tribble(~Month,~Date,~Day,~Mins,~Calories,~Type,
  "March",16,"Monday","00:12:20",NA,"Running",
  "March",17,"Tuesday","00:44:11",140,"Strength",
  "March",19,"Thursday","00:15:42",NA,"Running",
  "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") %>%
    mutate(Month=as_factor(Month)))

(smry2 <- group_by(raw1,
                   Month) %>% summarise(
                     days=length(unique(Date)),
                     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)
    y
  })

  (smry3 <- expand_grid(smry2,empty_raw))

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

[ashex08]

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?

[jrkrideau]

Something worked but what?

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

@ashex08

The data arranges itself basis the first letter of the month while I want it to be sorted according to the Month

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

Sorting Month names chronologically in the tidyverse

for a suggestion.