Get Count of Keyword and Min and MAX Date and Time in RStudio

Question

Have a server log file and I want to do some analysis like the number of hits on a method and time range for those hits.

So I am using RStudio to achieve that:

DateTime,Method
5/8/2020  0:00:00, Login
5/8/2020  2:00:00, Logout
5/8/2020  4:00:00, Login
5/8/2020  6:00:00, Login
5/8/2020  8:00:00, Login
5/8/2020  1:00:00, Logout
5/8/2020  9:00:00, Login
5/8/2020  5:00:00, Login
5/8/2020  10:00:00, Login
5/8/2020  5:00:00, Logout
5/8/2020  6:00:00, Logout

So I need output like this

     MinDateTime MaxDateTime,Hits
Login  5/8/2020  0:00:00,5/8/2020  10:00:00,7
Logout 5/8/2020  1:00:00,5/8/2020  10:00:00,4

Here is the code that generates the df.

df <- data.frame(
  DateTime = c(
    "5/8/2020 0:00:00", "5/8/2020 2:00:00", "5/8/2020 4:00:00",
    "5/8/2020 6:00:00", "5/8/2020 8:00:00", "5/8/2020 1:00:00",
    "5/8/2020 9:00:00", "5/8/2020 5:00:00", "5/8/2020 10:00:00",
    "5/8/2020 5:00:00", "5/8/2020 6:00:00"),
  Method = c(
    "Login", "Logout", "Login",
    "Login", "Login", "Logout",
    "Login", "Login", "Login",
    "Logout", "Logout")
)

Thanks

Image

Can you add data using `dput` ? `dput(head(df))` ? – Ronak Shah May 21 '20 at 00:03 — Ronak Shah, May 21 '20 at 00:03

MacOS · Accepted Answer · 2020-05-20T17:05:32.707

0

You can use aggregate for this task. Something as

aggregate(DateTime ~ Method, df, min)
aggregate(DateTime ~ Method, df, max)
aggregate(df$Method, by=list(agg.method=df$Method), length)

The full code looks the following way.

library(fasttime)


df <- data.frame(
  DateTime = c(
    "5/8/2020 0:00:00", "5/8/2020 2:00:00", "5/8/2020 4:00:00",
    "5/8/2020 6:00:00", "5/8/2020 8:00:00", "5/8/2020 1:00:00",
    "5/8/2020 9:00:00", "5/8/2020 5:00:00", "5/8/2020 10:00:00",
    "5/8/2020 5:00:00", "5/8/2020 6:00:00"),
  Method = c(
    "Login", "Logout", "Login",
    "Login", "Login", "Logout",
    "Login", "Login", "Login",
    "Logout", "Logout")
)

df$DateTime <- fastPOSIXct(df$DateTime)

agg.date.min <- aggregate(DateTime ~ Method, df, min)
agg.date.max <- aggregate(DateTime ~ Method, df, max)
agg.method.count <- aggregate(df$Method, by=list(Method=df$Method), length)

df.agg <- cbind(agg.date.min, agg.date.max$DateTime, agg.method.count$x)
colnames(df.agg) <- c("Method", "MinDateTimae", "MaxDateTime", "Hits")
df.agg

Please look here and here if you want to know more about converting to Date.

edited May 20 '20 at 17:05

answered May 20 '20 at 10:16

MacOS

1,149
1
7
14

No am struggling with the Date conversion. I end up with this: – Murli May 20 '20 at 16:32
@MurliKrishna This does not help me. I do not know what you did to get this. Please look [here](https://stackoverflow.com/questions/30023578/r-date-format-cast-string-as-date) and [here](https://stackoverflow.com/questions/29140416/r-data-table-fread-read-column-as-date) if you want to know more about converting to `Date`. – MacOS May 20 '20 at 16:48
I have added [link]https://i.stack.imgur.com/cQuF8.png so that you can see what I have done. In the above example, I have given a single column of data and time but they are separate columns. – Murli May 21 '20 at 09:31
Can you please try `df$DateTime <- fastPOSIXct(df$DateTime)` from `library(fasttime)`? Because this worked on my computer. Also, please notice that my answer is edited, i.e. it has new content. – MacOS May 21 '20 at 09:34

Get Count of Keyword and Min and MAX Date and Time in RStudio

1 Answers1