Trying to tally number of failed attempts per login id in R

Question

I am trying to tabulate from an audit log the number of failed attempts by login id. Suppose the audit log is as follows:

User ID   | LoginStatus  | LoginAttempt
----------+--------------+-----------------
2ABQM4    | F            | 1
2ABQM4    | F            | 2
1BIDU8    | F            | 1
1YUM8T    | F            | 1
2MP32A    | F            | 1
2MP32A    | F            | 2
2ABQM4    | F            | 3
1BIDU8    | F            | 2

I would like to create a table where the max login attempts is shown by User ID as follows:

User ID   | MaxLoginAttempt
----------+-----------------
2ABQM4    | 3
1BIDU8    | 2
1YUM8T    | 1
2MP32A    | 2

I am unsure of how to do it in R. Thanking gurus and seniors in advance for kind assistance.

Arifin

score 0 · Answer 1 · answered Oct 06 '20 at 07:04

You can do this with aggregate :

aggregate(LoginStatus~User.ID,  df, function(x) sum(x == 'F'))

#  User.ID LoginStatus
#1  1BIDU8           2
#2  1YUM8T           1
#3  2ABQM4           3
#4  2MP32A           2

Using dplyr :

library(dplyr)
df %>% group_by(User.ID) %>% summarise(Failed_Attempt = sum(LoginStatus == 'F'))

and data.table :

library(data.table)
setDT(df)[, sum(LoginStatus == 'F'), User.ID]

In the example, that you have shared all the LoginStatus is always 'F' in which case you can count number of rows in each group as well.

Trying to tally number of failed attempts per login id in R

1 Answers1