Let’s analyze the data of Brazilian soccer?
Brazil has a game called Cartola, this game evaluates the performance of players per round. Let’s get some information on this data!

Introduction

Today I will get some insights using the Cartola dataset (https://www.kaggle.com/lgmoneda/cartola-fc-brasil-scouts?select=jogadores.csv).

Cartola is a game that gets data of Brasileirao (Brazilian Soccer Championship) and the user can buy a player and score with them

When I saw my friends playing Cartola game and making decisions based on insights without statistics or data. So, I thought: “Can I make a decision in Cartola based on data?”

Another important thing is the movie Moneyball, where the manager buys players based on statistics reaching the championship final. Can we do something similar? Look, I have price, profit, and score… I will try it.

Before I start I will check some details, when you see ZAG in a graph it means defender, check the examples:

ATA = Attackers

ZAG = Defenders

TEC = Manager

LAT = Lateral

MEI = Mid

GOL = Goalkeeper

Players with the highest average per position

First, I explore the data and look at NaN values.

import pandas as pd cartolaDataframe = pd.read_csv(“/content/jogadores.csv”) cartolaDataframe = cartolaDataframe[(cartolaDataframe[“ano”] == 2019)] cartolaDataframe.rename(columns={“Nome”: “Name”}, inplace=True)

print(cartolaDataframe.shape) print(cartolaDataframe.describe()) print(cartolaDataframe.isna().sum())

[8 rows x 27 columns]

Nome 0

Clube 0

Preço 0

J 0

Média 0

Últ. Pont. 0

Variação 0

DS 8522

G 5045

A 5522

SG 5546

FS 1280

FF 2575

FD 3147

FT 7047

DD 7818

DP 8280

GC 8335

CV 7892

CA 3033

PP 8246

GS 7813

FC 1600

I 5428

PI 8522

rodada 0

ano 0

RB 1692

PE 992

A lot of data with NaN values. Well, I think that I can replace per 0, because, if the player did not score then the correct number is 0, not NaN

cartolaDataframe = cartolaDataframe.fillna(0)

Dataframe has a column called “Name” that we can extract the position of each player. For example, Marinho (ATA), we can to split and get your position (ATA)

playersPosition = [] splitedNames = cartolaDataframe[“Name”].str.split(“ “) for splitedName in splitedNames:

for namePosition in splitedName:

if “(“ in namePosition:

namePosition = namePosition.replace(“(“, “”)

namePosition = namePosition.replace(“)”, “”)

playersPosition.append(namePosition) cartolaDataframe[“posicao”] = playersPosition

cartolaDataframe

I think that is ok to generate the players with highest average

playerAndPosition = {}

for nome in cartolaDataframe[“Name”].unique():

lastMatchOfPlayer = cartolaDataframe[(cartolaDataframe[“Name”]==nome)].tail(1)

lastMean = lastMatchOfPlayer[“Média”].tolist()[0]

position = lastMatchOfPlayer[“posicao”].tolist()[0] if position not in playerAndPosition:

playerAndPosition[position] = {“name”: nome, “mean”: lastMean} if lastMean > playerAndPosition[position][“mean”]:

data = {“name”: nome, “mean”: lastMean}

playerAndPosition[position] = data



playerAndPosition

Result:

{‘ATA’: {‘media’: 10.73, ‘nome’: ‘Rodrygo (ATA)’},

‘GOL’: {‘media’: 10.0, ‘nome’: ‘Alexander (GOL)’},

‘LAT’: {‘media’: 8.4, ‘nome’: ‘Abner Felipe (LAT)’},

‘MEI’: {‘media’: 10.84, ‘nome’: ‘Arrascaeta (MEI)’},

‘TEC’: {‘media’: 6.4, ‘nome’: ‘Andrey Lopes (TEC)’},

‘ZAG’: {‘media’: 10.0, ‘nome’: ‘Rodrigues (ZAG)’}}

Team with the players with the highest average scores

So, We can get each position. Why not make a team? Go!!!!

First, I check which team has the highest average.

The code below gets the last punctuation of all players of the team and check the average of that team.

#The team with the highest average



highestAverageOfTeam = 0

teamNameWithHighestAverage = ""

for clubName in cartolaDataframe["Clube"].unique():

mean = cartolaDataframe[(cartolaDataframe["Clube"] == clubName)]["Últ. Pont."].mean()

if mean > highestAverageOfTeam:

highestAverageOfTeam = mean

teamNameWithHighestAverage = clubName





teamNameWithHighestAverage

print("club: ", teamNameWithHighestAverage, " highest average: ", teamNameWithHighestAverage)

club: Santos highest average: 4.206107055961076

The first step is generate a dataframe with necessary data for calc. Then, I do a extract process with code:

dataList = []

#filter the players with 20 matches or more

for name in cartolaDataframe["Name"].unique():

totalGames = cartolaDataframe[(cartolaDataframe["Name"] == name)].shape[0]

if totalGames > 20:

mean = cartolaDataframe[(cartolaDataframe["Name"] == name)]["Últ. Pont."].mean()

position = cartolaDataframe[(cartolaDataframe["Name"] == name)]["posicao"].to_list()[0]

data = {"Name": name, "Mean": mean, "Position": position}

dataList.append(data)

After, I define three functions to extract columns of a dataframe.

def generateTeam4_4_2(df, nomeDaColunaParaOrdernar):

attacker = df[(df["Position"] == "ATA")].sort_values(by=nomeDaColunaParaOrdernar).tail(2)

defenser = df[(df["Position"] == "ZAG")].sort_values(by=nomeDaColunaParaOrdernar).tail(2)

lateral = df[(df["Position"] == "LAT")].sort_values(by=nomeDaColunaParaOrdernar).tail(2)

mid = df[(df["Position"] == "MEI")].sort_values(by=nomeDaColunaParaOrdernar).tail(4)

manager = df[(df["Position"] == "TEC")].sort_values(by=nomeDaColunaParaOrdernar).tail(1)

goalkeeper = df[(df["Position"] == "GOL")].sort_values(by=nomeDaColunaParaOrdernar).tail(1) return attacker, defenser, lateral, mid, manager, goalkeeper def getColumnDataBasedOnThePlayerName(playerName, dfToGetData, columnToGetData):

columnData = dfToGetData[(dfToGetData["Name"] == playerName)][columnToGetData]

columnData = columnData.replace(0, columnData.mean()).to_list()

return columnData def generateDataFromColumnPerRound(columnToGetRoundData, dataframe):

data = {}

for player in dataframe["Name"].unique():

data[player] = getColumnDataBasedOnThePlayerName(player, dataframe, columnToGetRoundData) dfPerRound = pd.DataFrame.from_dict(data, orient='index').T fillMean = lambda col: col.fillna(col.mean()) return dfPerRound.apply(fillMean, axis=0)

Now, I will build other dataframe

# Generate the team with the players with the highest average

dataFrameHighestAverage = pd.DataFrame(lista,

columns=['Name', 'Mean', 'Position']) attackerHighestAverage, defenserHighestAverage, lateralHighestAverage, midHighestAverage, managerHighestAverage, goalkeeperHighestAverage = generateTeam4_4_2(dataFrameHighestAverage, "Mean") playersWithHighestAverage = pd.concat([attackerHighestAverage, defenserHighestAverage, lateralHighestAverage, midHighestAverage, managerHighestAverage, goalkeeperHighestAverage]) rowsWithPlayers = np.isin(cartolaDataframe, playersWithHighestAverage["Name"].to_list()) cartolaDataframeplayersWithHighestAverage = cartolaDataframe[rowsWithPlayers] pontuacaoPorRodadaParaCadaJogadorComMediaAlta = {} scorePerRoundTeamHighestAverage = generateDataFromColumnPerRound("Últ. Pont.", cartolaDataframeplayersWithHighestAverage).sum(axis=1) print(scorePerRoundTeamHighestAverage.mean())

The reason to fill with average was that players don’t play all games, then, the data frame has players with 30 matches and another with 23 matches. In this way, I preferred fill with the average.

The highest average of a team can have is:

82.10831839758166

But, what if we use the expected value to build a team? Would he be as good as the team with the highest average?

Now, we need to create a column with expected value!

cartolaDataframe.head()





cartolaDataframe["Lucro"] = cartolaDataframe["Últ. Pont."]/cartolaDataframe["Preço"]



dicitonaryList = []

#add expected value

for nome in cartolaDataframe["Name"].unique():

totalGames = cartolaDataframe[(cartolaDataframe["Name"] == nome)].shape[0]

if totalGames > 20:

totaThatPlayerCanGain = cartolaDataframe[(cartolaDataframe["Lucro"] >= 0) & (cartolaDataframe["Name"] == nome)]["Lucro"].mean()

totaThatPlayerCanLost = cartolaDataframe[(cartolaDataframe["Lucro"] <= 0) & (cartolaDataframe["Name"] == nome)]["Lucro"].mean()



probabilityOfWin = len(cartolaDataframe[(cartolaDataframe["Lucro"] >= 0) & (cartolaDataframe["Name"] == nome)])/totalGames

probabilityOfLose = len(cartolaDataframe[(cartolaDataframe["Lucro"] <= 0) & (cartolaDataframe["Name"] == nome)])/totalGames

probabilityOfDraw = len(cartolaDataframe[(cartolaDataframe["Lucro"] == 0) & (cartolaDataframe["Name"] == nome)])/totalGames



if probabilityOfDraw > 0:

probabilityOfWin = probabilityOfWin - probabilityOfDraw

probabilityOfLose = probabilityOfLose + probabilityOfDraw



if pd.isnull(totaThatPlayerCanGain) or totaThatPlayerCanGain == 0:

totaThatPlayerCanGain = 0

probabilityOfWin = 0



if pd.isnull(totaThatPlayerCanLost) or totaThatPlayerCanLost == 0:

totaThatPlayerCanLost = 0



ev = (totaThatPlayerCanGain * probabilityOfWin) - (totaThatPlayerCanLost * probabilityOfLose)

position = cartolaDataframe[(cartolaDataframe["Name"] == nome)]["posicao"].to_list()[0]

club = cartolaDataframe[(cartolaDataframe["Name"] == nome)]["Clube"].to_list()[0]

price = cartolaDataframe[(cartolaDataframe["Name"] == nome)]["Preço"].mean()

score = cartolaDataframe[(cartolaDataframe["Name"] == nome)]["Últ. Pont."].mean()

data = {"Name": nome, "EV": ev, "Position": position, "Club": club, "Price": price, "Score": score, "ProbabilityOfWin": probabilityOfWin}

dicitonaryList.append(data)

The first thing that I did was create a column with profit for each player. Then I created the EV calc. The EV calc use probability, suppose that the player average profit is 20 but he has 5% of chance to profit and the average loss is 8 and his chance loss is 95%, then the calc would look like this

-6.6 = 20*0,05–8 * 0.95

In the long run, it gives you loss.

Now I will repeat the steps that we used to generate the team with the highest average

attacker, defenser, lateral, mid, manager, goalkeeper = generateTeam4_4_2(df, "EV")



playersWithHighestEV = pd.concat([attacker, defenser, lateral, mid, manager, goalkeeper])



rowsWithPlayers = np.isin(cartolaDataframe, playersWithHighestEV["Name"].to_list())



cartolaDataframePlayersWithHighestEV = cartolaDataframe[rowsWithPlayers]



scorePerRoundOfTimeEV = generateDataFromColumnPerRound("Últ. Pont.", cartolaDataframePlayersWithHighestEV).sum(axis=1)

print(scorePerRoundOfTimeEV.mean())

The average result is: 31.115886491907343

Is a good difference between the team with the highest average and team EV, but, the team EV is really cheap.

We go to plot a graph with rows to check both performances?

We removed 1 because the EV team played 1 round less

x = total games

import matplotlib.pyplot as plt



#We removed 1 because the EV team played 1 round less

x = list(range(0, len(scorePerRoundTeamHighestAverage) - 1))



xev = list(range(0, len(scorePerRoundOfTimeEV)))

plt.plot(x, scorePerRoundTeamHighestAverage.head(35), label="Time com as Médias mais altas")

plt.plot(xev, scorePerRoundOfTimeEV, label="Time com os EVs mais altas")



plt.legend()

We already know the teams performances, however, and how is the price? Let’s go create this comparison?

Well, we know the performance, we can compare the price between them. Would the difference be really big too? How many points are we earning for the total paid?

# generate price per round TEAM HIGHEST AVERAGE

pricePerRoundTeamHighestAverage = generateDataFromColumnPerRound(“Preço”, cartolaDataframeplayersWithHighestAverage).sum(axis=1) # generate price per round TEAM EV

pricePerRoundTeamEV = generateDataFromColumnPerRound(“Preço”, cartolaDataframePlayersWithHighestEV).sum(axis=1) x = list(range(0, len(pricePerRoundTeamHighestAverage)-1)) xEv = list(range(0, len(pricePerRoundTeamEV)))

plt.figure(figsize=(15,8))

plt.title(“Comparando os preços”)

plt.bar(x, pricePerRoundTeamHighestAverage.head(35), label=”Time com as Médias mais altas”)

plt.bar(xEv, pricePerRoundTeamEV, label=”Time com os EVs mais altas”) plt.legend()

Apparently, the EV team spent 40% less! Go to check with some prints

print("AVERAGE PRICE TEAM EV: ", np.mean(pricePerRoundTeamEV), " MAX PRICE: ", np.max(pricePerRoundTeamEV), " MIN PRICE: ", np.min(pricePerRoundTeamEV), " MEDIAN: ", np.median(pricePerRoundTeamEV))

print("AVERAGE PRICE TEAM HIGHEST AVERAGE: ", np.mean(pricePerRoundTeamHighestAverage), " MAX PRICE: ", np.max(pricePerRoundTeamHighestAverage), " MIN PRICE: ", np.min(pricePerRoundTeamHighestAverage), " MEDIAN: ", np.median(pricePerRoundTeamHighestAverage))

AVERAGE PRICE TEAM EV: 47.76917267496996

MAX PRICE: 56.440000000000005

MIN PRICE: 29.7

MEDIAN: 49.85250898449377

AVERAGE PRICE TEAM HIGHEST AVERAGE: 172.13556812434123

MAX PRICE: 186.38

MIN PRICE: 140.55

MEDIAN: 176.39166219837134

The highest average team has 3.59 more expensive price than EV team but

does not score 3.59 more

What would be the best team using 100 cartoletas?

I think that I still get the best insights. We know that players have a price, so, let’s assume that we would always have 100 cartoletas (cartoletas is a money in the game) per round.

We will try to create some grouping and try to get insights.

First, we to check a graph with the probability the player make a positive score and your EV

#Obtain averages during the entire Brazilian championship

dataOfTheBestTeam= []

for name in df["Name"].unique():

price = np.mean(getColumnDataBasedOnThePlayerName(name, df, "Price"))

score = np.mean(getColumnDataBasedOnThePlayerName(name, df, "Score"))

ev = np.mean(getColumnDataBasedOnThePlayerName(name, df, "EV"))

probabilityOfWin = np.mean(getColumnDataBasedOnThePlayerName(name, df, "ProbabilityOfWin"))

position = df[(df["Name"] == name)]["Position"].to_list()[0]



data = {"Score": score, "Price": price, "Name": name, "EV": ev, "Position": position, "ProbabilityOfWin": probabilityOfWin}

dataOfTheBestTeam.append(data) import seaborn as sns





dataFrameTheBestTeam = pd.DataFrame(dados, columns=["Score", "Price", "Name", "EV", "Position", "ProbabilityOfWin"])



dataFrameTheBestTeam["ScorePerPrice"] = dataFrameTheBestTeam["Score"]/dataFrameTheBestTeam["Price"]



plt.figure(figsize=(15,8))

sns.scatterplot('EV', 'ProbabilityOfWin', data=dataFrameTheBestTeam, hue="Position")

But, it is too hard to check anything, however, we can check if the manager has a higher probability of scoring.

We can create a grid, look:

g = sns.FacetGrid(dataFrameTheBestTeam, col="Position", hue="Position", col_wrap=2)

g.map_dataframe(sns.scatterplot, x="EV", y="ProbabilityOfWin")

g.set_axis_labels("EV", "ProbabilityOfWin")

g.add_legend()

I could see that even with the probability of high gain, EVs are low. It should also be noted that the attackers have a great chance of scoring and in general, all above 50%.

Well, and the price per punctuation?

plt.figure(figsize=(15,8))

sns.scatterplot('EV', 'Score', data=dataFrameTheBestTeam, hue="Position")

plt.figure(figsize=(15,8))

sns.scatterplot('Price', 'Score', data=dataFrameTheBestTeam, hue="Position")

Here I can get some interesting insights… Expensive defenders, managers and goalkeeper usually score higher than attackers. But, this is just visualization, go to look a table.

dataFrameParaMelhorTime[(dataFrameParaMelhorTime["Preço"] > 13) & (dataFrameParaMelhorTime["Preço"] < 16)].groupby(by="Posicao").max()

Here I was able to observe that it is really worth investing in expensives lateral, mid and gol

Well, as I don’t know how to put an efficient curve like Markowitz for this occasion, so I will abuse computing and create code to generate 1 million teams at random.

dataFrameTheBestTeam

dataList = []

for combination in range(0, 1000000):

playersName = []



attacker = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="ATA")].sample(n=2)

attacker0 = attacker.iloc[0]

attacker1 = attacker.iloc[1]



manager = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="TEC")].sample(n=1).iloc[0]



goalkeeper = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="GOL")].sample(n=1).iloc[0]



mid = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="MEI")].sample(n=4)

mid0 = mid.iloc[0]

mid1 = mid.iloc[1]

mid2 = mid.iloc[2]

mid3 = mid.iloc[3]



defenser = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="ZAG")].sample(n=2)

defenser0 = defenser.iloc[0]

defenser1 = defenser.iloc[1]



lateral = dataFrameTheBestTeam[(dataFrameTheBestTeam['Position']=="LAT")].sample(n=2)

lateral0 = lateral.iloc[0]

lateral1 = lateral.iloc[1]





teamPrice = attacker["Price"].sum() + manager["Price"].sum() + goalkeeper["Price"].sum() + mid["Price"].sum() + defenser["Price"].sum() + lateral["Price"].sum()



if teamPrice >= 95 and teamPrice <= 100:

playersName.extend([attacker0["Name"], attacker1["Name"], manager["Name"], goalkeeper["Name"], mid0["Name"], mid1["Name"], mid2["Name"], mid3["Name"], defenser0["Name"], defenser1["Name"], lateral0["Name"], lateral1["Name"]])

teamScore = attacker["Score"].sum() + manager["Score"].sum() + goalkeeper["Score"].sum() + mid["Score"].sum() + defenser["Score"].sum() + lateral["Score"].sum()

print("COMBINATION: ", combination, " TEAM SCORE: ", teamScore, " PRICE: ", teamPrice)

data = {"TeamPrice": teamPrice, "TeamScore": teamScore, "Players": playersName}

dataList.append(data)





dataList dataFrame100Cartoletas = pd.DataFrame(dataList, columns=["TeamPrice", "TeamScore", "Players"])



print("SHAPE: ", dataFrame100Cartoletas.shape)



print("MAX SCORE: ", dataFrame100Cartoletas["TeamScore"].max(), " AVERAGE SCORE: ", dataFrame100Cartoletas["TeamScore"].mean())

print("PLAYERS: ", dataFrame100Cartoletas.max()["Players"], " STANDARD DEVIATION: ", dataFrame100Cartoletas["TeamScore"].std())

SHAPE: (103623, 3)

MAX SCORE: 50.077461423149884

AVERAGE SCORE: 38.21789270236684

PLAYERS: [‘Yony González (ATA)’, ‘Wellington Paulista (ATA)’, ‘Vanderlei Luxemburgo (TEC)’, ‘Éverson (GOL)’, ‘Fabinho (MEI)’, ‘Thiago Galhardo (MEI)’, ‘Patrick (MEI)’, ‘Felipe (MEI)’, ‘Quintero (ZAG)’, ‘Bruno Alves (ZAG)’, ‘Danilo Avelar (LAT)’, ‘Gilberto (LAT)’]

STANDARD DEVIATION: 2.9366541312757555

We had 100,000 samples, with an average of 38 points using 100 cards, with a standard deviation of 2.93. Well, apparently our EV team is below the average of teams with 100 cards, but it spends much less.

Conclusion

The EV team for this case, although more profitable in the long run, does not pay, because we want to score more even by risking more and losing cartouches. We also saw that putting together a team with a very high average is out of scope because it requires great value for cartoletas. We also found that scaling lateral, defenders and mids is better than investing in expensive attackers.

A very important note is that these algorithms do not have a team captain whose score is always 2x.
['Paulo Bernardo']
['Brazil', 'Tecnology', 'Programming', 'Data Science', 'Soccer']