How to get max value from column in data.frame and get ALL records
I have a data.frame and I want to get a row that contains the maximum value for a given column Total
Txn_date Cust_no Acct_no cust_type Credit Debit Total
09DEC2013 17382 601298644 I 1500 0 1500
16DEC2013 17382 601298644 I 500 0 500
17DEC2013 17382 601298644 I 0 60 60
18DEC2013 17382 601298644 I 0 200 200
19DEC2013 17382 601298644 I 1500 0 1500
20DEC2013 17382 601298644 I 0 60 60
20DEC2013 17382 601298644 I 0 103 103
30DEC2013 17382 601298644 I 500 0 500
So, I wrote a simple SQL query that needs to be parsed with the sqldf()
following:
s1<-paste("SELECT Txn_date, Cust_no,Credit,Debit,Total,max(Total) as 'MaxTxnAmt' FROM sample GROUP BY Cust_no")
sample_t1<-sqldf(s1)
And it gives me
Txn_date Cust_no Acct_no cust_type Credit Debit Total
09DEC2013 17382 601298644 I 1500 0 1500
I get the exact output as shown above if I use the functions base - R
:
sample_t1<-do.call(rbind,
lapply(split(sample,sample$Cust_no),
function(data) data[which.max(data$Total),]))
I want to know how to get ALL rows from a table sample
that have the maximum value for a column Total
.
Desired output:
Txn_date Cust_no Acct_no cust_type Credit Debit Total
09DEC2013 17382 601298644 I 1500 0 1500
19DEC2013 17382 601298644 I 1500 0 1500
Sample data:
sample <- structure(list(Txn_date = c("09DEC2013", "16DEC2013", "17DEC2013",
"18DEC2013", "19DEC2013", "20DEC2013", "20DEC2013", "30DEC2013"
), Cust_no = c(17382L, 17382L, 17382L, 17382L, 17382L, 17382L,
17382L, 17382L), Acct_no = c("601298644", "601298644", "601298644",
"601298644", "601298644", "601298644", "601298644", "601298644"
), cust_type = c("I", "I", "I", "I", "I", "I", "I", "I"), Credit = c(1500,
500, 0, 0, 1500, 0, 0, 500), Debit = c(0, 0, 60, 200, 0, 60,
103, 0), Total = c(1500, 500, 60, 200, 1500, 60, 103, 500)), .Names = c("Txn_date",
"Cust_no", "Acct_no", "cust_type", "Credit", "Debit", "Total"
), row.names = c(16303L, 29153L, 31174L, 33179L, 35388L, 38750L,
38751L, 53052L), class = "data.frame")
source to share
Try
library(dplyr)
sample %>%
group_by(Cust_no) %>%
filter( Total==max(Total))
# Txn_date Cust_no Acct_no cust_type Credit Debit Total
#1 09DEC2013 17382 601298644 I 1500 0 1500
#2 19DEC2013 17382 601298644 I 1500 0 1500
or
library(data.table)
setDT(sample)[, .SD[Total==max(Total)] ,Cust_no]
# Cust_no Txn_date Acct_no cust_type Credit Debit Total
#1: 17382 09DEC2013 601298644 I 1500 0 1500
#2: 17382 19DEC2013 601298644 I 1500 0 1500
or
setkey(setDT(sample), Total)[J(max(Total)), .SD,Cust_no]
# Cust_no Txn_date Acct_no cust_type Credit Debit Total
#1: 17382 09DEC2013 601298644 I 1500 0 1500
#2: 17382 19DEC2013 601298644 I 1500 0 1500
Or using base R
sample[with(sample, ave(Total, Cust_no, FUN=max)==Total),]
# Txn_date Cust_no Acct_no cust_type Credit Debit Total
#1 09DEC2013 17382 601298644 I 1500 0 1500
#5 19DEC2013 17382 601298644 I 1500 0 1500
source to share
1) Correlated subquery Try this:
sqldf("select *
from sample a
where Total = (select max(Total)
from sample b
where b.Cust_no = a.Cust_no)")
giving:
Txn_date Cust_no Acct_no cust_type Credit Debit Total
1 09DEC2013 17382 601298644 I 1500 0 1500
2 19DEC2013 17382 601298644 I 1500 0 1500
2) Join a subquery or this:
sqldf("select *
from sample
join (select Cust_no, max(Total) as 'MaxTxnAmt'
from sample
group by Cust_no)
using(Cust_no)
where Total = MaxTxnAmt")
giving:
Txn_date Cust_no Acct_no cust_type Credit Debit Total MaxTxnAmt
1 09DEC2013 17382 601298644 I 1500 0 1500 1500
2 19DEC2013 17382 601298644 I 1500 0 1500 1500
source to share