Count and aggregate by date

Question

The dataset:

Date
2021-09-25T17:07:24.222Z    
2021-09-25T16:17:20.376Z
2021-09-24T09:30:53.013Z
2021-09-24T09:06:24.565Z

I would like to count the number of rows per day. For example, 2021-09-25 will be 2.

To solve said challenge I looked at the following post:

Count and Aggregate Date in R

The answer of Rorshach is the solution. However, I do not understand how I can format my rows in the Date column to 2021/09/24 instead of 2021-09-24T09:06:24.565Z.

Could someone explain to me how to format the entries in the Date column?

Have you tried `df$Date <- as.Date(df$Date)` ? `as.Date('2021-09-25T17:07:24.222Z')` works correctly. — Ronak Shah, Sep 26 '21 at 09:38
@RonakShah I have and it works. However, I am unable to cut per day as shown by Rorschach. How does this happen? — user16949460, Sep 26 '21 at 09:46

score 2 · Answer 1 · answered Sep 26 '21 at 10:01

2

After converting the date you may use table to count occurrence of each Date.

table(as.Date(df$Date))

#2021-09-24 2021-09-25 
#         2          2

answered Sep 26 '21 at 10:01

Ronak Shah

377,200
20
156
213

score 1 · Accepted Answer · edited Sep 26 '21 at 09:52

1

Parse the string into a datetime object and then extract the date (without the hours and minutes) to be able to count:

library(dplyr)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

tibble::tribble(
  ~Date,
  "2021-09-25T17:07:24.222Z",
  "2021-09-25T16:17:20.376Z",
  "2021-09-24T09:30:53.013Z",
  "2021-09-24T09:06:24.565Z"
) %>%
  mutate(
    day = Date %>% parse_datetime() %>% as.Date()
  ) %>%
  count(day)
#> # A tibble: 2 × 2
#>   day            n
#>   <date>     <int>
#> 1 2021-09-24     2
#> 2 2021-09-25     2

edited Sep 26 '21 at 09:52

NelsonGon

13,015
7
27
57

answered Sep 26 '21 at 09:42

danlooo

10,067
2
8
22

Hi! Thank you for your answer. I do not understand the solution. What is the purpose of 'day = Date'? – user16949460 Sep 26 '21 at 09:49
Your example mentioned the colum `Date` which is actually a datetime. The `day` column is added to the table representing only the date. This is different from the datetime thus I created another column to prevent confusion. The column `day` starts with `Date`, then the two functions were applied. – danlooo Sep 26 '21 at 09:53

score 1 · Answer 3 · answered Sep 26 '21 at 10:08

1

@RonakShah's answer is good, but to have the dataframe in better format, use the count function from the plyr library:

library(plyr)
count(as.Date(df$Date))

Output:

           x freq
1 2021-09-24    2
2 2021-09-25    2

answered Sep 26 '21 at 10:08

U13-Forward

69,221
14
89
114

Count and aggregate by date

3 Answers3