Create a new Dataframe based on conditions from another Dataframe for loop

Question

Working with 2017 NFL Quarterback Data, looking to put the top 10 qbs each week in a dataframe (along with the rest of the data).

qb = {'week': [1, 1, 1, 2, 2, 2], 'qb': ['Rodgers', 'Brady', 'Wilson', 'Rodgers', 'Brady', 'Wilson'], 'pts': [30, 24, 20, 31, 20, 26]}

qb_df = pd.DataFrame(data=qb)

week    qb        pts
1       Rodgers   30
1       Brady     24
1       Wilson    20
2       Rodgers   31
2       Brady     20
3       Wilson    26

For this sake looking to return the top 2 from each week into a new dataframe.

week    qb        pts
1       Rodgers   30
1       Brady     24
2       Rodgers   31
2       Wilson    26

I tried a for loop that works as far as getting the data, but can't figure out to put it in a dataframe

top10_17 = pd.DataFrame()
for i in range(1, 18):
    i_17 = qb_2017.loc[qb_2017['Week'] == i].sort_values('FantasyPoints', ascending=False)[:10]
    top10_17 = pd.concat(i_17)

Used range(1,18) for the 17 weeks in an NFL season

BENY · Accepted Answer · 2018-08-01 01:53:58Z

2

IIUC sort_values with groupby + head

df.sort_values('pts',ascending=False).groupby('week').head(2).sort_values('week')
Out[49]: 
   pts       qb  week
0   30  Rodgers     1
1   24    Brady     1
3   31  Rodgers     2
5   26   Wilson     2

answered Aug 1, 2018 at 1:53

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rushabh Mehta · Accepted Answer · 2018-08-01 01:53:46Z

0

grouped = qb_df.groupby('week')
print(grouped.head(2))

This assumes your list is sorted, which can be done with pandas.sort_values()

answered Aug 1, 2018 at 1:53

Rushabh Mehta

1,5691 gold badge16 silver badges31 bronze badges

Comments

sacuL · Accepted Answer · 2018-08-01 01:58:23Z

0

You could also do this:

qb_df.set_index('qb').groupby('week')['pts'].nlargest(2)

week  qb     
1     Rodgers    30
      Brady      24
2     Rodgers    31
      Wilson     26
Name: pts, dtype: int64

If format is really important to stay constant:

qb_df.set_index('qb').groupby('week')['pts'].nlargest(2).reset_index()

week       qb  pts
0     1  Rodgers   30
1     1    Brady   24
2     2  Rodgers   31
3     2   Wilson   26

answered Aug 1, 2018 at 1:58

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Collectives™ on Stack Overflow

Create a new Dataframe based on conditions from another Dataframe for loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related