DataFrame with multi-index - which team has a larger number?-CodePudding

After using .groupby(['match_id', 'team']).sum() I'm left with this multi-index dataframe:

                      visionScore
match_id        team             
EUW1_5671848066 blue          212
                red           127
EUW1_5671858853 blue          146
                red           170
EUW1_5672206092 blue           82
...                           ...

How do I add a new boolean column that will tell whether blue or red team has larger visionScore? If there's a draw, consider both teams to be winning.

CodePudding user response：

This would work:

import pandas as pd

df = pd.DataFrame(
    {"visionScore": [212, 127, 146, 170, 82, 82]},
    index=pd.MultiIndex.from_product([["EUW1_5671848066", "EUW1_5671858853", "EUW1_5672206092"], ["blue", "red"]], names=["match_id", "team"])    
)

df["winner"] = df.groupby("match_id").transform(lambda x: [x[0] >= x[1], x[1] >= x[0]])

# df:
#                       visionScore  winner
# match_id        team                     
# EUW1_5671848066 blue          212    True
#                 red           127   False
# EUW1_5671858853 blue          146   False
#                 red           170    True
# EUW1_5672206092 blue           82    True
#                 red            82    True

though I can't help but think that there's a better way ,:)

CodePudding user response：

I did bit of a work around to get slightly different but still sufficient result by joining the dataframe on itself and getting visionScore on the same row:

df = df.reset_index(drop=True)
df = df.loc[df['team'] == 'blue'].merge(df.loc[df['team'] == 'red'], on=['match_id'], suffixes=('_blue', '_red'))
df['blueWins'] = df['visionScore_blue'] >= df['visionScore_red']

results in:

blueWins           visionScore_blue visionScore_red
False              156              189
False              83                90
True               142              102
True               185              161
True               147               94
...