Find and return column with max value from a list of vectors

user_18528618 0
Viewed 46

I'm trying to calculate favorite brand per productcategory per customer.

1. Start and what I want to achieve

I start with a dataframe (mydata) with purchase orders, build up like this:

customer ordernumber productcategory brand amount
ABC 123456 H11_plumbing Nitrofill 6
ABC 123457 H11_plumbing Antileak 2
DEF 123458 H11_plumbing Nitrofill 1
DEF 123459 H11_plumbing Antileak 5
ABC 123460 H12_electric Shock 10
ABC 123461 H12_electric Lightning 5
DEF 123462 H12_electric Shock 4
DEF 123463 H12_electric Lightning 8

What I want to achieve is list per customer the favorite brand per productcategory.

customer H11_plumbing_favorite_brand H12_electric_favorite_brand
ABC Nitrofill Shock
DEF Antileak Lightning

For customer ABC Nitrofill (amount = 6) and Shock (amount = 10) are favorites

2. What I am doing now

What I now did was create a list of vectors for each productcategory and change the shape from long to wide using lapply to use data.table::dcast

df_list <- split(mydata, as.factor(mydata$productcategory)) # create list of vectors

library(data.table)
df_list_2 <- lapply(df_list,function(x) x <- data.table::dcast(setDT(x), customer ~ brand, sum, value.var = c("amount"))) # change shape from long to wide

3. Where I get stuck is finding and returning the column with the favorite brand

This is where I get stuck. I have been able to do this for a data.frame (vector) rather than a list of vectors by using this code:

mydata_t <- mydata[mydata$productcategory=="H12_electric",]
mydata_overview<- data.table::dcast(setDT(mydata_t), customer ~ brand, sum, value.var = c("amount"))
rm(mydata_t)
mydata_overview$favorite_brand <- apply(mydata_overview[,-c(1)],1,function(x) which(x==max(x)))

However, if I try to use this code on the list of vectors (df_list) then it doesn't work.

df_list_3 <- lapply(df_list,function(x) x$favorite_brand<- apply(x[,-c(1)],1,function(y) which(y==max(y))))
rm(df_list_t)

Any suggestions?

dcast lapply apply r function
2 Answers

Here is one approach. Create a favorite column that includes the brand where you have the maximum amount (for each customer and productcategory combination). Then, use dcast to put into wide form, using the new favorite category as your value.

library(data.table)

setDT(df)

df[, .(favorite = brand[which.max(amount)]), by = .(customer, productcategory)][
  , dcast(.SD, 
          customer ~ productcategory, 
          value.var = "favorite")
]

Output

   customer H11_plumbing H12_electric
1:      ABC    Nitrofill        Shock
2:      DEF     Antileak    Lightning

Data

df <- structure(list(customer = c("ABC", "ABC", "DEF", "DEF", "ABC", 
"ABC", "DEF", "DEF"), ordernumber = 123456:123463, productcategory = c("H11_plumbing", 
"H11_plumbing", "H11_plumbing", "H11_plumbing", "H12_electric", 
"H12_electric", "H12_electric", "H12_electric"), brand = c("Nitrofill", 
"Antileak", "Nitrofill", "Antileak", "Shock", "Lightning", "Shock", 
"Lightning"), amount = c(6L, 2L, 1L, 5L, 10L, 5L, 4L, 8L)), class = "data.frame", row.names = c(NA, 
-8L))
user_34606700

Here is a solution using data.table:

> library(data.table)
> setDT(dat)
> dcast(dat[ , .(favourite=brand[which.max(amount)]), by=.(customer, productcategory) ], customer ~ productcategory)

   customer H11_plumbing H12_electric
1:      ABC    Nitrofill        Shock
2:      DEF     Antileak    Lightning

To break it down:

We can use data.table to find the brand corresponding to the maximum amount for each combination of customer and category as follows:

> dat[ , .(favourite=brand[which.max(amount)]), by=.(customer, productcategory) ]
   customer productcategory favourite
1:      ABC    H11_plumbing Nitrofill
2:      DEF    H11_plumbing  Antileak
3:      ABC    H12_electric     Shock
4:      DEF    H12_electric Lightning

Then use dcast to reshape this table with customer as the column and productcategory along the rows.

user_121762800
Related