Maximum consecutive row for an id using Pandas?

Question

I am very new to pandas. I have two dataframes related to two player Game

DF1:matches # match information 

match_num   winner_id   loser_id    
270      201504         201595
271      201514         201426
272      201697         211901
273      201620         211539
274      214981         203564

DF2: players #information about the players

playerid  First Name    Last Name   Country
200001     Martina      Hingis        SUI
200002     Mirjana      Lucic         CRO
200003     Justine      Henin         BEL

full data: https://drive.google.com/file/d/1QHglO3DkZPDFvqHH-jJYDbYh3jylo2-C/view?usp=sharing

I need to find out Which player(s) won the highest number of matches in a row?

Can this be solved using sql functions?

Thanks in advance!!

If you have a specific issue while solving this yourself you can ask here with your code. — Michael Butscher, Oct 06 '21 at 19:28
I am not sure whether this can be solved using pandas itself — jebaseelan ravi, Oct 06 '21 at 19:29
looks like homework. try searching how to use window functions — barker, Oct 06 '21 at 19:41
@barker - This helps can you please provide some examples. I am very new to it. I would like learn it — jebaseelan ravi, Oct 06 '21 at 19:59
sure, looks like you need to join the tables using some type of left or inner join on player id, then sort by match_num asc, then you need to do either a rolling count or sum over a partition, as explained here https://mode.com/sql-tutorial/sql-window-functions/ — barker, Oct 07 '21 at 20:15

mozway · Answer 1 · 2021-10-06T20:31:20.130

The example that you provided is not really useful as there is no more than one match per id. Here is a better one:

   match_num  winner_id  loser_id
0        270          3         2
1        271          2         3
2        272          3         4
3        273          4         1
4        274          2         4

My proposed pandas solution is a bit lengthy. We first reshape the dataframe to a long format using melt, and sort by match. Then we compute a group corresponding to consecutive matches of the same type (won/lost). Finally, we keep only the wins, count the group size (i.e. the number of consecutive matches won) and sort.

(df.melt(id_vars='match_num',
         var_name='wl',
         value_name='ID')
   .sort_values(by=['ID', 'match_num'])
   .assign(group=lambda d: d.groupby('ID')['wl'].apply(lambda s: s.ne(s.shift()).cumsum()))
   .query('wl == "winner_id"')
   .groupby(['ID', 'group']).size()
   .sort_values(ascending=False)
   .droplevel('group')
   .rename('consecutive_win')
   .to_frame()
 )

Output:

    consecutive_win
ID                 
2                 2
3                 1
3                 1
4                 1

You can find the full list of content https://drive.google.com/file/d/1QHglO3DkZPDFvqHH-jJYDbYh3jylo2-C/view?usp=sharing — jebaseelan ravi, Oct 07 '21 at 08:25

score 0 · Answer 2 · answered Oct 06 '21 at 20:00

0

You can use Python only to solve this problem with groupby:

from itertools import groupby

wid, nwin = max([(name, len(list(grp))) for name, grp in groupby(df1['winner_id'])],
                key=lambda x: x[1])

Now you can lookup to your second dataframe:

df2.loc[df2['playerid'].eq(wid), ['First Name', 'Last Name']].apply(' '.join, axis=1)

answered Oct 06 '21 at 20:00

Corralien

109,409
8
28
52

Don't you need to reshape to long format first? What happens if the player loses a game? – mozway Oct 06 '21 at 20:12
Responding to your question on "consecutive": Yes it does mean *in a row*. So if a player wins game 1 and 3 but loses 2, the max consecutive is 1, your solution uses only 'winner_id`, it cannot be correct. – mozway Oct 06 '21 at 20:25
Ok Ok. I understood :) – Corralien Oct 06 '21 at 20:26
Sorry typing on phone, I have a bit of lag :p – mozway Oct 06 '21 at 20:26

score -1 · Answer 3 · answered Oct 06 '21 at 19:50

-1

Something like this would work where the variable b_id is the player with the most wins in a row.

import numpy as np

b_id = None
_max = float('-inf')
for g,g_hold in df.groupby('winner_id'):
    diff = np.diff(g_hold.index)
    if len(np.where(diff>1)[0]) > 0:
        count = len(diff[:np.where(diff>1)[0][0]])
    elif len(diff) == 0:
        count = len(g_hold)
    else:
        count = 0
    if count > _max:
        b_id = g
        _max = count

answered Oct 06 '21 at 19:50

bellerb

137
8

This helps, Can it be solved using SQL way? – jebaseelan ravi Oct 06 '21 at 19:58
1

what do you mean by SQL way? This uses the dataframe but utilizes numpy to vectorize the index difference calculation used to find wins in a row. – bellerb Oct 06 '21 at 20:02

Maximum consecutive row for an id using Pandas?

3 Answers3