-2

I know questions like this have been answered a ton of times here, and trust me I have tried all the methods I could find of i.e. xts, dplyr, zoo etc. but nothing worked for me.

What I basically want to do is aggregating the calories so that they display the sum amount in hourly timeframe. In SQL that would mean using GROUP BY and SUM but I don't know how to translate that into R.

My current dataset min_cal_narrow: https://i.ibb.co/Db8Z6pX/Current-Form.png

What I want to achieve: https://i.ibb.co/HKcxHdt/Ideal.png

Could anyone kindly point me towards the right direction?

Thanks.

Kewei
  • 15
  • 7
  • 2
    Please do not post data as images. We cannot copy/paste that into R for testing. Use a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). What exactly is wrong with your code attempts so far? – MrFlick Jul 27 '21 at 04:57
  • @MrFlick Thanks for responding. The images are not codes, they are just the printed output of my current dataset vs my ideal output. Let me execute the codes and reply the error in the next reply. – Kewei Jul 27 '21 at 05:07
  • `min_cal_narrow$ActivityMinute <- min_cal_narrow %>% mdy_hms(ActivityMinute) %>% to.hourly(ActivityMinute) %>% rename(ActivityMinute, act_hour)` Error: Error in lapply(list(...), .num_to_date): object 'ActivityMinute' not found. Note: ActivityMinute is one of the columns inside the csv file. – Kewei Jul 27 '21 at 05:07
  • 3
    The code is fine. but the data itself should not be in images. Use `dput()` to we can easily copy into R to help. Add error messages to the post itself. Do not put that information in comments. – MrFlick Jul 27 '21 at 05:09

1 Answers1

1

I would do something like this.

library(dplyr)
library(lubridate)

test_data <- data.frame(minutes = c("4/12/2016 12:01:00", 
                                     "4/12/2016 12:02:00",
                                     "4/12/2016 12:03:00",
                                     "4/12/2016 13:01:00",
                                     "4/12/2016 13:02:00",
                                     "4/12/2016 13:03:00"),
                        calories = c(50, 
                                     40, 
                                     30, 
                                     10, 
                                     15, 
                                     60))

# Convert minute column to datetime. Find the hour of the minute column using floor_date 

test_data$hour <-  floor_date(as_datetime(test_data$minutes, 
                                          format =  "%d/%m/%Y %H:%M:%S"), 
unit = "hour")                                   

summarised <- test_data %>% 
  group_by(hour) %>% 
  summarise(sum_calories = sum(calories))

## Have the sum of calories for each hour. 
summarised
Tob
  • 245
  • 1
  • 9