Multiple Modes by variables

Question

I can't seem to find an answer for my problem.

Here is sample data

Credit Card Type  Bank   Year   Total Balance
MASTER CARD       BOFA   2017   $100
MASTER CARD       BOFA   2017   $100
MASTER CARD       BOFA   2017   $700
VISA              Wells  2018   $60 
VISA              Wells  2018   $50
VISA              Wells  2018   $60

etc

I am trying to figure how to get a mode by total Balance across all variables So it will end up like this

Desired output:

Credit Card Type  Bank   Year   Mode
MASTER CARD       BOFA   2017   $100
VISA              Wells  2018   $60

I guess `aggregate` with `Mode` from https://stackoverflow.com/q/2547402 might work. — Frank, Oct 01 '18 at 18:23

score 1 · Answer 1 · answered Oct 01 '18 at 18:27

Using Mode from stackoverflow.com/q/2547402 as Frank suggested, this is easy to do with dplyr.

library(dplyr)

df %>% 
    group_by(CreditCardType, Bank, Year) %>%
    summarise(mode = Mode(TotalBalance))

Where df is:

df <- read.table(text = 'CreditCardType  Bank   Year   TotalBalance
MASTERCARD       BOFA   2017   $100
MASTERCARD       BOFA   2017   $100
MASTERCARD       BOFA   2017   $700
VISA              Wells  2018   $60 
VISA              Wells  2018   $50
VISA              Wells  2018   $60', header = T, stringsAsFactors = F)

score 1 · Accepted Answer · answered Oct 01 '18 at 18:33

1

From this question obtaining 3 most common elements of groups, concatenating ties, and ignoring less common values

library(plyr)
getmode<- function(origtable,groupby,columnname) {
  data <- ddply (origtable, groupby, .fun = function(xx){
    c(m1 = paste(names(sort(table(xx[,columnname]),decreasing=TRUE)[1]))
    ) } ) 
  return(data)
}

getmode(df,c("CreditCardType","Bank","Year"),"TotalBalance")

df<-read.table(text="CreditCardType  Bank   Year   TotalBalance
MASTERCARD       BOFA   2017   $100
MASTERCARD       BOFA   2017   $100
MASTERCARD       BOFA   2017   $700
VISA              Wells  2018   $60 
VISA              Wells  2018   $50
VISA              Wells  2018   $60", header=T, stringsAsFactors=F)

answered Oct 01 '18 at 18:33

Ferroao

3,042
28
53

I tried to add min/max to it by this. Doesnt seem to like it. getmode(df,c("CreditCardType","Bank","Year"),"TotalBalance", which.min("TotalBalance"),which.max(TotalBalance")) – Tade Oct 01 '18 at 19:55
thanks for your help...what do you think about the comment above with min/max? – Tade Oct 01 '18 at 20:38
which.min(gsub("\\$","",getmode(df,c("CreditCardType","Bank","Year"),"TotalBalance")$m1) ) – Ferroao Oct 01 '18 at 21:52

tmfmnk · Answer 3 · 2018-10-01T20:09:32.690

A different dplyr solution:

df %>%
  add_count(Credit_Card_Type, Bank, Year, Total_Balance) %>%
  filter(n == max(n)) %>%
  distinct() %>%
  select(-n)

Considering ties and selecting the first mode value:

df %>%
  add_count(Credit_Card_Type, Bank, Year, Total_Balance) %>%
  filter(n == max(n)) %>%
  distinct() %>%
  select(-n) %>%
  group_by(Credit_Card_Type, Bank, Year) %>%
  summarise(Total_Balance = first(Total_Balance))

Data:

df <- read.table(text = "Credit_Card_Type Bank Year Total_Balance
           MASTER_CARD BOFA 2017 100
           MASTER_CARD BOFA 2017 100
           MASTER_CARD BOFA 2017 700
           VISA Wells 2018 60
           VISA Wells 2018 50
           VISA Wells 2018 60", header = TRUE)

Oriol · Answer 4 · 2018-10-01T19:19:36.003

1

I found a solution using the data.table and modeest packages.

library(data.table)
library(modeest)
dt <- data.table("Type"=c(rep("MASTERCARD",3),rep("VISA",3)),"Bank"=c(rep("BOFA",3),rep("Wells",3)),"Year"=c(rep(2017,3),rep(2018,3)),"TotalBalance"=c(100,100,700,60,50,60))
dt[,mfv(TotalBalance)[1],by=c("Type","Bank","Year")]

          Type  Bank Year  V1
 1: MASTERCARD  BOFA 2017 100
 2:       VISA Wells 2018  60

edited Oct 01 '18 at 19:19

answered Oct 01 '18 at 18:39

Oriol

23
5

1

You're right. Simpler. I'll edit the solution. Thanks! – Oriol Oct 01 '18 at 19:19

Multiple Modes by variables

4 Answers4