How do i find a the difference of the max and min of a category and add that difference to a column-CodePudding

So I have a data frame with different basketball teams and leagues and I want to find out how to get the max rating of each team in the league and subtract it from the rating of the team itself. For example, the max rating for the Celtics is 100 and it is in the league NBA and the raptors have a rating of 85, in the new column, the row for the raptors should have a difference rating of 15 and the Celtics should have a rating of 0(since it's the maximum of that league). I should be able to do this for every league and every team. There is a column with different team names and a column with different league names associated with that team, in addition, there is a column with the rating of that team. I tried to find the maximum and minimum of each team, however, I couldn't add the difference into another column. Thank you in advance :)

CodePudding user response：

I hope this is what you are looking for. For this type of operations, the library data.table is very useful and fast because it's implemented in C.

library(data.table)

data.dt <- data.table()
data.dt[, team := sample(9, 50, replace = TRUE)][
        , league := letters[sample(6, 50, replace = TRUE)]][
        , rating := round(runif(50), 1)*100]

data.dt <- data.dt[, .(max_rating = max(rating)), by = c('team', 'league')]
data.dt <- data.dt[, inv_max_rating := 100 - max_rating]

You coerce your data to data.table like this.

data.dt <- data.table(your_dataframe)

CodePudding user response：

Give me the data and i give you the code.