Sorting Data in R by multiple Parameters

Question

I have the following Problem: I have a dataset including 3 different columns (there are more, but for the analysis they are not relevant). Here is an example Dataset (the origin dataset has much more observations):

Date               Company             Return
March              A                   0.03
March              A                   0.02
March              B                   0.01
April              B                   0.02       
April              A                   0.01
May                C                   0.02
June               B                   0.03

Now I want to find the biggest return for every month, but each company should show 3 times at max in the final output. I tried some for loops to create subsets for each month, but then I have no idea how I can extract the biggest return for each month while still have the restriction of each company showing up less than 3 times. Important is that when a company was returned three times in the output, the company with the next highest return has to be picked for the certain month. Every month hast to be returned one time.

Answer 1

If you want to maximise the overall return, then you would need to move to an optimisation approach, with the formulation:

• Objective function to maximise: Sum of returns
• Monthly restriction: Each month must appear once in the solution
• Company restriction: Each company can appear maximum 3 times in the solution

This could be done with library lpSolveAPI:

library(lpSolveAPI)

# Create data.table
dt <- data.frame(Date = c("March", "March", "March", "April", "April", "May", "June"),
                 Company = c("A", "A", "B", "B", "A", "C", "B"),
                 Return = c(0.03, 0.02, 0.01, 0.02, 0.01, 0.02, 0.03))

# Objective
obj <- -dt$Return

# Constraints
constraints <- list()

# Each month must appear once in the solution
for (month in unique(dt$Date)){
  constraints[[paste0('month', month)]] <- list(xt = as.numeric(dt$Date == month),
                                                type = "=",
                                                rhs = 1)
}

# Each company can appear maximum 3 times in the solution
for (com in unique(dt$Company)){
  constraints[[paste0('company', com)]] <- list(xt = as.numeric(dt$Company == com),
                                                type = "<=",
                                                rhs = 3)
}

# Build model
lprec <- make.lp(0, ncol = nrow(dt))
set.type(lprec, columns = seq(1,nrow(dt)), type = "binary")

set.objfn(lprec, obj = obj)

for (constraint in constraints){
  add.constraint(lprec, xt = constraint$xt, type = constraint$type, rhs = constraint$rhs)
}

# Compute Solution
solve(lprec)

# Visualize solution
solution <- dt[get.variables(lprec)==1,]

solution

#    Date Company Return
# 1 March       A   0.03
# 4 April       B   0.02
# 6   May       C   0.02
# 7  June       B   0.03

Answer 2

like this?

> library(tidyverse)
> 
> dataset<-data.frame(Date=c("March","March","March","April","April","May","June"),Company=c("A","A","B","B","A","C","B"),Return=c(0.03,0.02,0.01,0.02,0.01,0.02,0.03))

> dataset %>% group_by(Date,Company) %>% summarise(max_ret=max(Return))
# A tibble: 6 x 3
# Groups:   Date [?]
Date  Company max_ret
<fct> <fct>     <dbl>
1 April A          0.01
2 April B          0.02
3 June  B          0.03
4 March A          0.03
5 March B          0.01
6 May   C          0.02

Answer 3

First let's create a data set with more than 3 cases of any company, just so we can test it:

df1<-structure(list(Date = c("March", "March", "March", "April", "April", 
     "May", "June", "July"), Company = c("A", "A", "B", "B", "A", 
     "C", "B", "B"), Return = c(0.03, 0.02, 0.01, 0.02, 0.01, 0.02, 
     0.03, 0.02)), .Names = c("Date", "Company", "Return"), row.names = c(NA, 
     -8L), class = "data.frame")

Now, using dplyr:

df1 %>% group_by(Company) %>% 
  arrange(desc(Return)) %>% 
  filter(row_number() %in% 1:3) %>% 
  group_by(Date) %>% 
  filter(Return==max(Return))

First we keep only the top three results for each company, thus assuring that no matter what, our final output will not have more than three instances for each company. Then we group by Date and just keep the top Return for each month.

# A tibble: 5 x 3
# Groups:   Date [5]
  Date  Company Return
  <chr> <chr>    <dbl>
1 March A         0.03
2 June  B         0.03
3 April B         0.02
4 May   C         0.02
5 July  B         0.02