CODEWITHSUNDEEP
rvariablesmode
Tade
Tade

Reputation: 13

Multiple Modes by variables

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

Upvotes: 1

Views: 488

Answers (4)

tmfmnk
tmfmnk

Reputation: 40171

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)

Upvotes: 1

Oriol
Oriol

Reputation: 23

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

Upvotes: 1

Ferroao
Ferroao

Reputation: 3056

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)

Upvotes: 1

C. Braun
C. Braun

Reputation: 5211

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)

Upvotes: 1

Related Questions