I have a question about LpSolve in R. I have a panel with the following data: Football player ID (around 500 player), how many games each of them has already played, number of goals scored and cost of the player. I want to create a matrix from this data, but I do not know how this works with such a large amount of data (I have about 500 football players an therefore 500 rows).
The goal is to select the optimal number of players for a budget of 1,000,000. Each player can only be selected once, optimized by the number of scored goals.
In the end I want to have the optimal selection of players which scored the most goals, and the budget has to be (almost) used up.
Since I am relatively new with R I do not know how to solve this problem with LpSolve yet and I fail at the matrix production and the constraints.
I´m very grateful for your help !
My panel looks like this (example):
footballplayerID | gamesplayed | avggoals | costsperplayer
.
.
First I create a df like this: df <- read.table(text = "footballplayerID | gamesplayed | avggoals | costsperplayer 233276 | 120 | 80 | 50000 474823 | 200 | 140 | 34000 192834 | 150 | 90 | 14000 192833 | 30 | 50 | 90000 129834 | 204 | 129 | 70000 347594 | 123 | 19 | 10000 203845 | 129 | 57 | 43000 128747 | 98 | 124 | 1400001", header = TRUE, stringsAsFactors = FALSE, sep = "|" )
library(lpSolve)
The coefficients for objective function is avggoals:
obj_fun <- df$avggoals
The constraints is the sum of costperplayer that needs to be less than or equal to 100.000.000
constraints <- matrix(df$costsperplayer, nrow = 1)
c_dir <- "<="
c_rhs <- 1000000
You can then solve that lp problem with lp(). the argument all.bin = TRUE makes sure you choose a player once or not at all.
lp <- lp("max",
obj_fun,
constraints,
c_dir,
c_rhs,
all.bin = TRUE)
You can than have a look at the selected players:
df[lp$solution == 1, ]