Reputation: 597

pandas: filtering groupby and/or pivot?

I'm trying to figure how to filter a greater/lesser than condition within group context with pandas.

In the sample df, there are 7 groups(a,b,c,d,e,f,g). And each groups have between 1 to 6 players. Is is possible to filter out the groups which player 1 score between 9-20? And the groups whose player 1 scored between 9-20 will be shown (as seen in output)?

ps. the original df is much larger with groups having over 10 players and other columns with variable values.

sample df:

╔═══════╦════════╦═══════╗
║ Group ║ player ║ score ║
╠═══════╬════════╬═══════╣
║ a     ║      1 ║    10 ║
║ a     ║      2 ║    20 ║
║ a     ║      3 ║    29 ║
║ a     ║      4 ║    22 ║
║ a     ║      5 ║    14 ║
║ b     ║      1 ║    16 ║
║ b     ║      2 ║    16 ║
║ b     ║      3 ║    17 ║
║ c     ║      1 ║    22 ║
║ c     ║      2 ║    23 ║
║ c     ║      3 ║    22 ║
║ d     ║      1 ║    13 ║
║ d     ║      2 ║    13 ║
║ d     ║      3 ║    23 ║
║ d     ║      4 ║    13 ║
║ d     ║      5 ║    34 ║
║ e     ║      1 ║    32 ║
║ e     ║      2 ║    29 ║
║ e     ║      3 ║    28 ║
║ e     ║      4 ║    19 ║
║ e     ║      5 ║    19 ║
║ e     ║      6 ║    27 ║
║ f     ║      1 ║    47 ║
║ f     ║      2 ║    17 ║
║ f     ║      3 ║    14 ║
║ f     ║      4 ║    25 ║
║ g     ║      1 ║    67 ║
║ g     ║      2 ║    21 ║
║ g     ║      3 ║    27 ║
║ g     ║      4 ║    16 ║
║ g     ║      5 ║    14 ║
║ g     ║      6 ║    25 ║
╚═══════╩════════╩═══════╝

Output required:

╔═══════╦════════╦═══════╗
║ Group ║ player ║ score ║
╠═══════╬════════╬═══════╣
║ a     ║      1 ║    10 ║
║ a     ║      2 ║    20 ║
║ a     ║      3 ║    29 ║
║ a     ║      4 ║    22 ║
║ a     ║      5 ║    14 ║
║ b     ║      1 ║    16 ║
║ b     ║      2 ║    16 ║
║ b     ║      3 ║    17 ║
║ d     ║      1 ║    13 ║
║ d     ║      2 ║    13 ║
║ d     ║      3 ║    23 ║
║ d     ║      4 ║    13 ║
║ d     ║      5 ║    34 ║
╚═══════╩════════╩═══════╝

df code as follow:

data = {'Group':['a','a','a','a','a','b','b','b','c','c','c','d','d','d','d','d',
'e','e','e','e','e','e','f','f','f','f','g','g','g','g','g','g'],
'players':[1,2,3,4,5,1,2,3,1,2,3,1,2,3,4,5,1,2,3,4,5,6,1,2,3,4,1,2,3,4,5,6],
'score':[10,20,29,22,14,16,16,17,22,23,22,13,13,23,13,34,32,29,28,19,19,27,47,17,14,25,67,21,27,16,14,25,]}

Many thanks Regards

Upvotes: 3

Answers (3)

Nathan Furnal

Reputation: 2410

More extravagant answer using slices :

idx = pd.IndexSlice
df_reid = df.set_index(["Group", "players"])
mask = df_reid[idx["score"]].between(9, 20)
groups = df_reid.loc[idx[mask,1],:].index.get_level_values("Group") # 1 means players == 1

df.loc[df["Group"].isin(groups)]

Upvotes: 1

Alexander

Reputation: 109626

Find the groups where the condition is satisfied, and then use isin to filter for the data contained within those groups.

df = pd.DataFrame(data)

groups_filter = (
    df[df['players'].eq(1) 
       & df['score'].ge(9) 
       & df['score'].le(20)
      ]['Group'].unique()
)
>>> df[df['Group'].isin(groups_filter)]
   Group  players  score
0      a        1     10
1      a        2     20
2      a        3     29
3      a        4     22
4      a        5     14
5      b        1     16
6      b        2     16
7      b        3     17
11     d        1     13
12     d        2     13
13     d        3     23
14     d        4     13
15     d        5     34

Upvotes: 3

anky

Reputation: 75100

IIUC, you can use series.eq with series.between with df.groupby and transform with any:

df[(df['players'].eq(1)&df['score'].between(9,20)).groupby(df['Group']).transform('any')]

   Group  players  score
0      a        1     10
1      a        2     20
2      a        3     29
3      a        4     22
4      a        5     14
5      b        1     16
6      b        2     16
7      b        3     17
11     d        1     13
12     d        2     13
13     d        3     23
14     d        4     13
15     d        5     34

Upvotes: 3

pandas: filtering groupby and/or pivot?

Answers (3)

Related Questions