R grouping and summarizing but conditional up to a given date, for each date-CodePudding

Here is my dataframe

    structure(list(Date = structure(c(19052, 19052, 19052, 19052, 
19052, 19052, 19052, 19052, 19052, 19052, 19052, 19052, 19053, 
19053, 19053, 19053, 19053, 19053, 19053, 19053, 19053, 19053, 
19053, 19053, 19054, 19054, 19054, 19054, 19054, 19054, 19054, 
19054, 19054, 19054, 19054, 19054, 19055, 19055, 19055, 19055, 
19055, 19055, 19055, 19055, 19055, 19055, 19055, 19055, 19056, 
19056, 19056, 19056, 19056, 19056, 19056, 19056, 19056, 19056, 
19056, 19056, 19057, 19057, 19057, 19057, 19057, 19057, 19057, 
19057, 19057, 19057, 19057, 19057, 19058, 19058, 19058, 19058, 
19058, 19058, 19058, 19058, 19058, 19058, 19058, 19058, 19059, 
19059, 19059, 19059, 19059, 19059, 19059, 19059, 19059, 19059, 
19059, 19059, 19060, 19060, 19060, 19060, 19060, 19060, 19060, 
19060, 19060, 19060, 19060, 19060, 19061, 19061, 19061, 19061, 
19061, 19061, 19061, 19061, 19061, 19061, 19061, 19061, 19062, 
19062, 19062, 19062, 19062, 19062, 19062, 19062, 19062, 19062, 
19062, 19062, 19063, 19063, 19063, 19063, 19063, 19063, 19063, 
19063, 19063, 19063, 19063, 19063, 19064, 19064, 19064, 19064, 
19064, 19064, 19064, 19064, 19064, 19064, 19064, 19064, 19065, 
19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 
19065, 19065, 19066, 19066, 19066, 19066, 19066, 19066, 19066, 
19066, 19066, 19066, 19066, 19066, 19067, 19067, 19067, 19067, 
19067, 19067, 19067, 19067, 19067, 19067, 19067, 19067, 19068, 
19068, 19068, 19068, 19068, 19068, 19068, 19068, 19068, 19068, 
19068, 19068, 19069, 19069, 19069, 19069, 19069, 19069, 19069, 
19069, 19069, 19069, 19069, 19069, 19070, 19070, 19070, 19070, 
19070, 19070, 19070, 19070, 19070, 19070, 19070, 19070, 19071, 
19071, 19071, 19071, 19071, 19071, 19071, 19071, 19071, 19071, 
19071, 19071, 19072, 19072, 19072, 19072, 19072, 19072, 19072, 
19072, 19072, 19072, 19072, 19072, 19073, 19073, 19073, 19073, 
19073, 19073, 19073, 19073, 19073, 19073, 19073, 19073, 19074, 
19074, 19074, 19074, 19074, 19074, 19074, 19074, 19074, 19074, 
19074, 19074, 19075, 19075, 19075, 19075, 19075, 19075, 19075, 
19075, 19075, 19075, 19075, 19075, 19076, 19076, 19076, 19076, 
19076, 19076, 19076, 19076, 19076, 19076, 19076, 19076, 19077, 
19077, 19077, 19077, 19077, 19077, 19077, 19077, 19077, 19077, 
19077, 19077, 19078, 19078, 19078, 19078, 19078, 19078, 19078, 
19078, 19078, 19078, 19078, 19078, 19079, 19079, 19079, 19079, 
19079, 19079, 19079, 19079, 19079, 19079, 19079, 19079, 19080, 
19080, 19080, 19080, 19080, 19080, 19080, 19080, 19080, 19080, 
19080, 19080, 19081, 19081, 19081, 19081, 19081, 19081, 19081, 
19081, 19081, 19081, 19081, 19081, 19082, 19082, 19082, 19082, 
19082, 19082, 19082, 19082, 19082, 19082, 19082, 19082, 19083, 
19083, 19083, 19083, 19083, 19083, 19083, 19083, 19083, 19083, 
19083, 19083, 19084, 19084, 19084, 19084, 19084, 19084, 19084, 
19084, 19084, 19084, 19084, 19084, 19085, 19085, 19085, 19085, 
19085, 19085, 19085, 19085, 19085, 19085, 19085, 19085, 19086, 
19086, 19086, 19086, 19086, 19086, 19086, 19086, 19086, 19086, 
19086, 19086, 19087, 19087, 19087, 19087, 19087, 19087, 19087, 
19087, 19087, 19087, 19087, 19087, 19088, 19088, 19088, 19088, 
19088, 19088, 19088, 19088, 19088, 19088, 19088, 19088, 19089, 
19089, 19089, 19089, 19089, 19089, 19089, 19089, 19089, 19089, 
19089, 19089, 19090, 19090, 19090, 19090, 19090, 19090, 19090, 
19090, 19090, 19090, 19090, 19090, 19091, 19091, 19091, 19091, 
19091, 19091, 19091, 19091, 19091, 19091, 19091, 19091, 19092, 
19092, 19092, 19092, 19092, 19092, 19092, 19092, 19092, 19092, 
19092, 19092, 19093, 19093, 19093, 19093, 19093, 19093, 19093, 
19093, 19093, 19093, 19093, 19093, 19094, 19094, 19094, 19094, 
19094, 19094, 19094, 19094, 19094, 19094, 19094, 19094, 19095, 
19095, 19095, 19095, 19095, 19095, 19095, 19095, 19095, 19095, 
19095, 19095), class = "Date"), Day_of_Week = c("Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Monday", "Monday", 
"Monday", "Monday", "Monday", "Monday", "Monday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday"), Hour = c("10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm", "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", 
"6pm", "7pm", "8pm", "9pm", "10am", "11am", "12pm", "1pm", "2pm", 
"3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", "11am", 
"12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", 
"9pm"), Date_Time = structure(c(1646128800, 1646132400, 1646136000, 
1646139600, 1646143200, 1646146800, 1646150400, 1646154000, 1646157600, 
1646161200, 1646164800, 1646168400, 1646215200, 1646218800, 1646222400, 
1646226000, 1646229600, 1646233200, 1646236800, 1646240400, 1646244000, 
1646247600, 1646251200, 1646254800, 1646301600, 1646305200, 1646308800, 
1646312400, 1646316000, 1646319600, 1646323200, 1646326800, 1646330400, 
1646334000, 1646337600, 1646341200, 1646388000, 1646391600, 1646395200, 
1646398800, 1646402400, 1646406000, 1646409600, 1646413200, 1646416800, 
1646420400, 1646424000, 1646427600, 1646474400, 1646478000, 1646481600, 
1646485200, 1646488800, 1646492400, 1646496000, 1646499600, 1646503200, 
1646506800, 1646510400, 1646514000, 1646560800, 1646564400, 1646568000, 
1646571600, 1646575200, 1646578800, 1646582400, 1646586000, 1646589600, 
1646593200, 1646596800, 1646600400, 1646647200, 1646650800, 1646654400, 
1646658000, 1646661600, 1646665200, 1646668800, 1646672400, 1646676000, 
1646679600, 1646683200, 1646686800, 1646733600, 1646737200, 1646740800, 
1646744400, 1646748000, 1646751600, 1646755200, 1646758800, 1646762400, 
1646766000, 1646769600, 1646773200, 1646820000, 1646823600, 1646827200, 
1646830800, 1646834400, 1646838000, 1646841600, 1646845200, 1646848800, 
1646852400, 1646856000, 1646859600, 1646906400, 1646910000, 1646913600, 
1646917200, 1646920800, 1646924400, 1646928000, 1646931600, 1646935200, 
1646938800, 1646942400, 1646946000, 1646992800, 1646996400, 1647000000, 
1647003600, 1647007200, 1647010800, 1647014400, 1647018000, 1647021600, 
1647025200, 1647028800, 1647032400, 1647079200, 1647082800, 1647086400, 
1647090000, 1647093600, 1647097200, 1647100800, 1647104400, 1647108000, 
1647111600, 1647115200, 1647118800, 1647165600, 1647169200, 1647172800, 
1647176400, 1647180000, 1647183600, 1647187200, 1647190800, 1647194400, 
1647198000, 1647201600, 1647205200, 1647252000, 1647255600, 1647259200, 
1647262800, 1647266400, 1647270000, 1647273600, 1647277200, 1647280800, 
1647284400, 1647288000, 1647291600, 1647338400, 1647342000, 1647345600, 
1647349200, 1647352800, 1647356400, 1647360000, 1647363600, 1647367200, 
1647370800, 1647374400, 1647378000, 1647424800, 1647428400, 1647432000, 
1647435600, 1647439200, 1647442800, 1647446400, 1647450000, 1647453600, 
1647457200, 1647460800, 1647464400, 1647511200, 1647514800, 1647518400, 
1647522000, 1647525600, 1647529200, 1647532800, 1647536400, 1647540000, 
1647543600, 1647547200, 1647550800, 1647597600, 1647601200, 1647604800, 
1647608400, 1647612000, 1647615600, 1647619200, 1647622800, 1647626400, 
1647630000, 1647633600, 1647637200, 1647684000, 1647687600, 1647691200, 
1647694800, 1647698400, 1647702000, 1647705600, 1647709200, 1647712800, 
1647716400, 1647720000, 1647723600, 1647770400, 1647774000, 1647777600, 
1647781200, 1647784800, 1647788400, 1647792000, 1647795600, 1647799200, 
1647802800, 1647806400, 1647810000, 1647856800, 1647860400, 1647864000, 
1647867600, 1647871200, 1647874800, 1647878400, 1647882000, 1647885600, 
1647889200, 1647892800, 1647896400, 1647943200, 1647946800, 1647950400, 
1647954000, 1647957600, 1647961200, 1647964800, 1647968400, 1647972000, 
1647975600, 1647979200, 1647982800, 1648029600, 1648033200, 1648036800, 
1648040400, 1648044000, 1648047600, 1648051200, 1648054800, 1648058400, 
1648062000, 1648065600, 1648069200, 1648116000, 1648119600, 1648123200, 
1648126800, 1648130400, 1648134000, 1648137600, 1648141200, 1648144800, 
1648148400, 1648152000, 1648155600, 1648202400, 1648206000, 1648209600, 
1648213200, 1648216800, 1648220400, 1648224000, 1648227600, 1648231200, 
1648234800, 1648238400, 1648242000, 1648288800, 1648292400, 1648296000, 
1648299600, 1648303200, 1648306800, 1648310400, 1648314000, 1648317600, 
1648321200, 1648324800, 1648328400, 1648375200, 1648378800, 1648382400, 
1648386000, 1648389600, 1648393200, 1648396800, 1648400400, 1648404000, 
1648407600, 1648411200, 1648414800, 1648461600, 1648465200, 1648468800, 
1648472400, 1648476000, 1648479600, 1648483200, 1648486800, 1648490400, 
1648494000, 1648497600, 1648501200, 1648548000, 1648551600, 1648555200, 
1648558800, 1648562400, 1648566000, 1648569600, 1648573200, 1648576800, 
1648580400, 1648584000, 1648587600, 1648634400, 1648638000, 1648641600, 
1648645200, 1648648800, 1648652400, 1648656000, 1648659600, 1648663200, 
1648666800, 1648670400, 1648674000, 1648720800, 1648724400, 1648728000, 
1648731600, 1648735200, 1648738800, 1648742400, 1648746000, 1648749600, 
1648753200, 1648756800, 1648760400, 1648807200, 1648810800, 1648814400, 
1648818000, 1648821600, 1648825200, 1648828800, 1648832400, 1648836000, 
1648839600, 1648843200, 1648846800, 1648893600, 1648897200, 1648900800, 
1648904400, 1648908000, 1648911600, 1648915200, 1648918800, 1648922400, 
1648926000, 1648929600, 1648933200, 1648980000, 1648983600, 1648987200, 
1648990800, 1648994400, 1648998000, 1649001600, 1649005200, 1649008800, 
1649012400, 1649016000, 1649019600, 1649066400, 1649070000, 1649073600, 
1649077200, 1649080800, 1649084400, 1649088000, 1649091600, 1649095200, 
1649098800, 1649102400, 1649106000, 1649152800, 1649156400, 1649160000, 
1649163600, 1649167200, 1649170800, 1649174400, 1649178000, 1649181600, 
1649185200, 1649188800, 1649192400, 1649239200, 1649242800, 1649246400, 
1649250000, 1649253600, 1649257200, 1649260800, 1649264400, 1649268000, 
1649271600, 1649275200, 1649278800, 1649325600, 1649329200, 1649332800, 
1649336400, 1649340000, 1649343600, 1649347200, 1649350800, 1649354400, 
1649358000, 1649361600, 1649365200, 1649412000, 1649415600, 1649419200, 
1649422800, 1649426400, 1649430000, 1649433600, 1649437200, 1649440800, 
1649444400, 1649448000, 1649451600, 1649498400, 1649502000, 1649505600, 
1649509200, 1649512800, 1649516400, 1649520000, 1649523600, 1649527200, 
1649530800, 1649534400, 1649538000, 1649584800, 1649588400, 1649592000, 
1649595600, 1649599200, 1649602800, 1649606400, 1649610000, 1649613600, 
1649617200, 1649620800, 1649624400, 1649671200, 1649674800, 1649678400, 
1649682000, 1649685600, 1649689200, 1649692800, 1649696400, 1649700000, 
1649703600, 1649707200, 1649710800, 1649757600, 1649761200, 1649764800, 
1649768400, 1649772000, 1649775600, 1649779200, 1649782800, 1649786400, 
1649790000, 1649793600, 1649797200, 1649844000, 1649847600, 1649851200, 
1649854800, 1649858400, 1649862000, 1649865600, 1649869200, 1649872800, 
1649876400, 1649880000, 1649883600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), Sales = c(0, 318025, 514564, 731381, 959657, 1185334, 
1416716, 1641935, 1864840, 2087205, 2275901, 2407859, 0, 350940, 
562897, 779890, 992805, 1226505, 1443294, 1677288, 1901534, 2104827, 
2307256, 2428800, 0, 292714, 479090, 666377, 867457, 1058315, 
1268516, 1486690, 1687120, 1890182, 2081588, 2202168, 0, 401946, 
654525, 908625, 1190691, 1465206, 1720245, 1980199, 2223449, 
2458126, 2675610, 2837221, 0, 296190, 484898, 661245, 856829, 
1059521, 1247215, 1437624, 1605198, 1769373, 1913244, 2020148, 
0, 178039, 379180, 585716, 791106, 1012789, 1205306, 1397312, 
1568109, 1720954, 1870954, 2020954, 0, 312658, 509462, 722547, 
936139, 1168228, 1382537, 1602535, 1829260, 2040071, 2215819, 
2332590, 0, 293677, 470132, 659438, 867073, 1082114, 1291002, 
1499493, 1707018, 1905361, 2083071, 2197764, 0, 325562, 524535, 
726101, 944271, 1156014, 1371216, 1589487, 1815813, 2025560, 
2216443, 2336825, 0, 293748, 478908, 694471, 903671, 1112836, 
1316200, 1507003, 1714187, 1902766, 2069871, 2187924, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 356989, 559966, 783894, 999938, 
1221808, 1424401, 1611264, 1779218, 1921666, 2050627, 2143030, 
0, 153397, 327480, 533139, 751339, 967301, 1173340, 1356815, 
1528857, 1681645, 1844064, 1844064, 0, 277899, 471614, 685994, 
931414, 1170915, 1408527, 1648295, 1890483, 2115120, 2320897, 
2477707, 0, 286848, 475638, 677115, 889567, 1093680, 1302750, 
1498135, 1707756, 1902284, 2056873, 2203041, 0, 293132, 484911, 
692956, 910137, 1125735, 1321297, 1570114, 1823508, 2083550, 
2328746, 2557551, 0, 251641, 449150, 646659, 855304, 1075814, 
1306749, 1520861, 1733646, 1942672, 2139001, 2308905, 0, 290147, 
478702, 669796, 888708, 1115979, 1324921, 1553339, 1760894, 1970290, 
2155050, 2313919, 0, 0, 473206, 659936, 852017, 852017, 2091329, 
2257681, 2423115, 2564532, 2703990, 2824381, 0, 242353, 535636, 
812677, 1085746, 1321196, 1515369, 1696831, 1858096, 2009150, 
2165269, 2165269, 0, 269580, 444846, 649443, 851755, 1069161, 
1281917, 1496394, 1706670, 1899022, 2067432, 2195206, 0, 255739, 
422021, 602141, 774780, 954868, 1129565, 1307203, 1475507, 1649559, 
1785976, 1894091, 0, 277274, 455302, 651753, 839349, 1028250, 
1242225, 1501278, 1768379, 2046656, 2286616, 2488195, 0, 249985, 
413904, 589518, 775096, 951728, 1127046, 1312713, 1486103, 1668493, 
1826932, 1945829, 0, 316538, 523487, 758068, 1008297, 1260871, 
1506309, 1756571, 1981208, 2205873, 2401959, 2572401, 0, 296051.8364, 
473206, 659936, 852017, 1045664.5, 1239312, 1405664, 1571098, 
1712515, 1851973, 1972364, 0, 168157, 347277, 557413, 779536, 
999457, 1199247, 1390077, 1572696, 1731900, 1892243, 2052586, 
0, 277887, 452524, 643973, 860794, 1079867, 1294875, 1518751, 
1726708, 1937370, 2112473, 2271351, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 396456, 572474, 780224, 1001572, 1224677, 1439215, 
1645148, 1854331, 2046215, 2211839, 2362043, 0, 292315, 488035, 
690859, 923659, 1147883, 1374140, 1594284, 1821473, 2035683, 
2223045, 2375520, 0, 307470, 507533, 728256, 959485, 1200713, 
1423500, 1640529, 1850180, 2046532, 2221283, 2382291.91, 0, 285560, 
486728, 701417, 943416, 1185898, 1395710, 1604240, 1793172, 1962999, 
2113823, 2232717, 0, 158881, 326370, 494727, 676647, 850244, 
1021835, 1167815, 1302997, 1421208, 1542626, 1542626, 0, 241132, 
407313, 584655, 780747, 983805, 1185358, 1374598, 1569981, 1751983, 
1918465, 2040527, 0, 266487, 422864, 593228, 771899, 951790, 
1129720, 1293674, 1464121, 1624755, 1777559, 1895461, 0, 275884, 
456859, 652361, 860750, 1063475, 1256408, 1449684, 1656636, 1838144.25984455, 
2003701, 2139876, 0, 259078, 419011, 588710, 766509, 940493, 
1109202, 1283608, 1467065, 1631987, 1773653, 1892431, 0, 304141, 
476994, 668898, 802665, 875109, 1094131, 1471062, 1733966, 1985945, 
2225504, 2450605, 0, 449053, 697820, 952431, 1210898, 1464442, 
1707353, 1946528, 2146538, 2333322, 2515627, 2674651, 0, 191057, 
398300, 621097, 859606, 1102018, 1338608, 1547151, 1735105, 1909331, 
2076719, 2076719, 0, 249006, 413848, 589790, 776803, 971293, 
1154215, 1341453, 1532779, 1704763, 1845413, 1975962, 0, 242039, 
395044, 555791, 710396, 891304, 1055436, 1217639, 1380435, 1536080, 
1665876, 1781080, 0, 288190, 464692, 652759, 853812, 1067162, 
1259819, 1451037, 1652630, 1842384, 2013646, 2143242)), row.names = c(NA, 
-528L), class = c("tbl_df", "tbl", "data.frame"))

And here is the output of the glimpse() function.

Rows: 156
Columns: 5
$ Date        <date> 2022-04-01, 2022-04-01, 2022-04-01, 2022-04-01, 2022-04-01, 2022-04-01, 2022-04-01, 2022-04-01…
$ Day_of_Week <chr> "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Frid…
$ Hour        <chr> "10am", "11am", "12pm", "1pm", "2pm", "3pm", "4pm", "5pm", "6pm", "7pm", "8pm", "9pm", "10am", …
$ Date_Time   <dttm> 2022-04-01 10:00:00, 2022-04-01 11:00:00, 2022-04-01 12:00:00, 2022-04-01 13:00:00, 2022-04-01…
$ Sales       <dbl> 0, 307470, 507533, 728256, 959485, 1200713, 1423500, 1640529, 1850180, 2046532, 2221283, 238229…

I'm trying to figure out the average orders that come through for each day and each hour grouped together, but only up to and not including the current day.

Here is the code that I have so far. It creates an output that shows the average orders that come in on a Friday at 10am, and the average orders that come in on Friday at 11am, etc.

dataframe3 %>%
# some kind of filter on the date column? 
  group_by(Day_of_Week, Hour) %>%
  summarize(Average_Orders_Date_and_Hour = mean(Sales)) %>%
  mutate(Percent_of_Total = Average_Orders_Date_and_Hour / max(Average_Orders_Date_and_Hour))

What I'm really looking for, though, is a way of versioning. The output I'm hoping for is a much longer dataframe that would show the result of this calculation up to a given day, as though I had ran the code on updated data every single day and saved the output.

The code that I created compiles OK. It creates a dataframe that has 84 rows. But I need a dataframe that has 84 rows for each date in the dataframe. For the date 2020-06-01, it would group the day of the week and the hour, but also group the date, and then calculate the average sales for each day of the week and hour combination, for every previous date (not including the current date).

CodePudding user response：

Perhaps this? For each hour of each day of the week, it will calculate the average sales for prior observations (ie "up to and not including the current day"). It's undefined for the first rows since there's no prior observation yet.

library(dplyr)                                    
dataframe3 %>%  # from shorter first version of data
  group_by(Day_of_Week, Hour) %>%
  arrange(Date) %>% # in case not sorted yet
  mutate(Sales_avg = lag(cummean(Sales))) %>%
  ungroup()

Looks like the sample includes under two weeks of data, so the 2nd week just shows the 1st week's sales.

... %>%
arrange(Day_of_Week, Hour)


# A tibble: 156 × 6
   Date       Day_of_Week Hour  Date_Time            Sales Sales_avg
   <date>     <chr>       <chr> <dttm>               <dbl>     <dbl>
 1 2022-04-01 Friday      10am  2022-04-01 10:00:00      0        NA
 2 2022-04-08 Friday      10am  2022-04-08 10:00:00      0         0
 3 2022-04-01 Friday      11am  2022-04-01 11:00:00 307470        NA
 4 2022-04-08 Friday      11am  2022-04-08 11:00:00 304141    307470
 5 2022-04-01 Friday      12pm  2022-04-01 12:00:00 507533        NA
 6 2022-04-08 Friday      12pm  2022-04-08 12:00:00 476994    507533
 7 2022-04-01 Friday      1pm   2022-04-01 13:00:00 728256        NA
 8 2022-04-08 Friday      1pm   2022-04-08 13:00:00 668898    728256
 9 2022-04-01 Friday      2pm   2022-04-01 14:00:00 959485        NA
10 2022-04-08 Friday      2pm   2022-04-08 14:00:00 802665    959485
# … with 146 more rows

Edit - here's a visual check of it using the larger data just added. Looks like it's working as I'd expect.

library(tidyverse)
dataframe3 %>%  # from larger data added in revision
  group_by(Day_of_Week, Hour) %>%
  arrange(Date) %>% # in case not sorted yet
  mutate(Sales_avg = lag(cummean(Sales))) %>%
  arrange(Day_of_Week, Hour) %>%
  
  ggplot(aes(Date))  
  geom_point(aes(y = Sales))  
  geom_line(aes(y = Sales_avg))   
  facet_grid(Day_of_Week~Hour)