-1

Two columns are there one with veh_No and other one with date_time. How to get the maximum count of trips vehicle has done with respect to date

The input is given as :

Veh_No Date_time
AP3029 2022-10-18 13:11:20
AP3029 2022-10-18 05:51:40
MP5029 2022-10-18 14:59:46
UP3040 2022-09-18 20:51:40
AP3029 2022-10-18 23:34:44
UP3040 2022-10-18 23:51:40

The output should be as :

Veh_No Date_time count
AP3029 2022-10-18 13:11:20 3
AP3029 2022-10-18 05:51:40 3
MP5029 2022-10-18 14:59:46 1
UP3040 2022-09-18 20:51:40 1
AP3029 2022-10-18 23:34:44 3
UP3040 2022-10-18 23:51:40 1
Adriaan
  • 17,741
  • 7
  • 42
  • 75
Tulips
  • 13
  • 4
  • 1
    Please provide a [R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and the code you tried. – Benson_YoureFired Nov 08 '22 at 10:19
  • Exanple is already mentioned in question only along with the expected output. I am not sure about what I have tried as I am getting an error. – Tulips Nov 08 '22 at 10:24
  • Read the link @Benson_YoureFired sent. You did gave an example, but not in a way we can easily run code with. Also, it good to post your code, even if you're getting an error, so we can have a starting point. – Ricardo Semião e Castro Nov 08 '22 at 10:36
  • 1
    @RicardoSemiãoeCastro it's not ideal but you can c&p the table by copying it and then using ``clipr::read_clip_tbl`` – user438383 Nov 08 '22 at 10:45
  • 1
    Please don't make more work for other people by vandalizing your posts. By posting on the Stack Exchange network, you've granted a non-revocable right, under the [CC BY-SA 4.0 license](https://creativecommons.org/licenses/by-sa/4.0/), for Stack Exchange to distribute that content (i.e. regardless of your future choices). By Stack Exchange policy, the non-vandalized version of the post is the one which is distributed. Thus, any vandalism will be reverted. If you want to know more about deleting a post please see: [How does deleting work?](https://meta.stackexchange.com/q/5221) – Adriaan Nov 08 '22 at 13:13

1 Answers1

1

One possibe solution

library(dplyr)

df %>%
  group_by(Veh_No, Date=as.Date(Date_time)) %>%
  mutate(count = n()) %>%
  ungroup() %>%
  select(-Date)

# A tibble: 6 x 3
  Veh_No Date_time           count
  <chr>  <chr>               <int>
1 AP3029 2022-10-18 13:11:20     3
2 AP3029 2022-10-18 05:51:40     3
3 MP5029 2022-10-18 14:59:46     1
4 UP3040 2022-09-18 20:51:40     1
5 AP3029 2022-10-18 23:34:44     3
6 UP3040 2022-10-18 23:51:40     1

Or

library(data.table)

setDT(df)[, count := .N, by=.(Veh_No, Date=as.Date(Date_time))]

   Veh_No           Date_time count
   <char>              <char> <int>
1: AP3029 2022-10-18 13:11:20     3
2: AP3029 2022-10-18 05:51:40     3
3: MP5029 2022-10-18 14:59:46     1
4: UP3040 2022-09-18 20:51:40     1
5: AP3029 2022-10-18 23:34:44     3
6: UP3040 2022-10-18 23:51:40     1
B. Christian Kamgang
  • 5,098
  • 5
  • 10