I have this following dataframe:
| Track | Surface | FGrating | HorseId | Last FGrating at Sha Tin Grass | Last FGrating at Sha Tin Dirt | Last FGrating at Happy Valley Grass |
|---|---|---|---|---|---|---|
| Happy Valley | grass | 97 | 22609 | |||
| Happy Valley | grass | 106 | 22609 | 97 | ||
| Happy Valley | grass | 104 | 22609 | 106 | ||
| Happy Valley | grass | 102 | 22609 | 104 | ||
| Happy Valley | grass | 95 | 22609 | 102 | ||
| Sha Tin | grass | 108 | 22609 | |||
| Sha Tin | grass | 104 | 22609 | 108 | ||
| Sha Tin | grass | 114 | 22609 | 104 | ||
| Sha Tin | dirt | 104 | 22609 | |||
| Sha Tin | grass | 101 | 22609 | 114 | ||
| Sha Tin | dirt | 92 | 22609 | 104 | ||
| Happy Valley | grass | 107 | 22609 | 95 | ||
| Sha Tin | grass | 102 | 22609 | 101 | ||
| Happy Valley | grass | 108 | 22609 | 107 | ||
| Happy Valley | grass | 105 | 22609 | 108 | ||
| Happy Valley | grass | 104 | 22609 | 105 |
This Dataframe can be obtained like this:
{'Track': {10153: 'Happy Valley', 10154: 'Happy Valley', 10155: 'Happy Valley', 10156: 'Happy Valley', 10157: 'Happy Valley', 10158: 'Sha Tin', 10159: 'Sha Tin', 10160: 'Sha Tin', 10161: 'Sha Tin', 10162: 'Sha Tin', 10163: 'Sha Tin', 10164: 'Happy Valley', 10165: 'Sha Tin', 10166: 'Happy Valley', 10167: 'Happy Valley', 10168: 'Happy Valley'}, 'Surface': {10153: 'Gress', 10154: 'Gress', 10155: 'Gress', 10156: 'Gress', 10157: 'Gress', 10158: 'Gress', 10159: 'Gress', 10160: 'Gress', 10161: 'Dirt', 10162: 'Gress', 10163: 'Dirt', 10164: 'Gress', 10165: 'Gress', 10166: 'Gress', 10167: 'Gress', 10168: 'Gress'}, 'FGrating': {10153: 97, 10154: 106, 10155: 104, 10156: 102, 10157: 95, 10158: 108, 10159: 104, 10160: 114, 10161: 104, 10162: 101, 10163: 92, 10164: 107, 10165: 102, 10166: 108, 10167: 105, 10168: 104}, 'HorseId': {10153: 22609, 10154: 22609, 10155: 22609, 10156: 22609, 10157: 22609, 10158: 22609, 10159: 22609, 10160: 22609, 10161: 22609, 10162: 22609, 10163: 22609, 10164: 22609, 10165: 22609, 10166: 22609, 10167: 22609, 10168: 22609}, 'Last FGrating at Sha-Tin Grass': {10153: nan, 10154: nan, 10155: nan, 10156: nan, 10157: nan, 10158: nan, 10159: 108.0, 10160: 104.0, 10161: nan, 10162: 114.0, 10163: nan, 10164: nan, 10165: 101.0, 10166: nan, 10167: nan, 10168: nan}, 'Last FGrating at Sha-Tin Dirt': {10153: nan, 10154: nan, 10155: nan, 10156: nan, 10157: nan, 10158: nan, 10159: nan, 10160: nan, 10161: nan, 10162: nan, 10163: 104.0, 10164: nan, 10165: nan, 10166: nan, 10167: nan, 10168: nan}, 'Last FGrating at Happy Valley Grass': {10153: nan, 10154: 97.0, 10155: 106.0, 10156: 104.0, 10157: 102.0, 10158: nan, 10159: nan, 10160: nan, 10161: nan, 10162: nan, 10163: nan, 10164: 95.0, 10165: nan, 10166: 107.0, 10167: 108.0, 10168: 105.0}}
I am trying to achieve two interlinked goals:
The first goal is to complete the empty cells according to the following two rules:
- If a horse never raced on a particular track before, then its 'Last FGrating at ' is 0;
- If a horse previously raced on a particular track, then he races on another, the 'Last FGrating at ' gets the last value on that particular track, until he either comes back on it or the dataset ends.
What I am looking for is this:
| Track | Surface | FGrating | HorseId | Last FGrating at Sha Tin Grass | Last FGrating at Sha Tin Dirt | Last FGrating at Happy Valley Grass |
|---|---|---|---|---|---|---|
| Happy Valley | grass | 97 | 22609 | 0 (rule 1) | 0 (rule 1) | 0 (rule 1) |
| Happy Valley | grass | 106 | 22609 | 0 (rule 1) | 0 (rule 1) | 97 |
| Happy Valley | grass | 104 | 22609 | 0 (rule 1) | 0 (rule 1) | 106 |
| Happy Valley | grass | 102 | 22609 | 0 (rule 1) | 0 (rule 1) | 104 |
| Happy Valley | grass | 95 | 22609 | 0 (rule 1) | 0 (rule 1) | 102 |
| Sha Tin | grass | 108 | 22609 | 0 (rule 1) | 0 (rule 1) | 95 (rule 2) |
| Sha Tin | grass | 104 | 22609 | 108 | 0 (rule 1) | 95 (rule 2) |
| Sha Tin | grass | 114 | 22609 | 104 | 0 (rule 1) | 95 (rule 2) |
| Sha Tin | dirt | 104 | 22609 | 104 (rule 2) | 0 (rule 1) | 95 (rule 2) |
| Sha Tin | grass | 101 | 22609 | 114 | 0 (rule 1) | 95 (rule 2) |
| Sha Tin | dirt | 92 | 22609 | 114(rule 2) | 104 | 95 (rule 2) |
| Happy Valley | grass | 107 | 22609 | 114 (rule 2) | 104 (rule 2) | 95 |
| Sha Tin | grass | 102 | 22609 | 101 | 104 (rule 2) | 95 (rule 2) |
| Happy Valley | grass | 108 | 22609 | 101(rule 2) | 104 (rule 2) | 107 |
| Happy Valley | grass | 105 | 22609 | 101(rule 2) | 104 (rule 2) | 108 |
| Happy Valley | grass | 104 | 22609 | 101(rule 2) | 104 (rule 2) | 105 |
I want to do this for every HorseId so a groupby is in order.
The second goal is to generalized the way the first goal is achieved. What do I mean by that? First, no matter the track the horse is racing on, the process is the same. Second, this process can also be applied to other statistics (let's say last position on a certain track).
The code that I have so far is this:
def return_mask_and_text_from_tracks(data, track_no, metric):
if track_no == 0: # Sha Tin - grass
mask = (data.Track == 'Sha Tin') & (data.Surface == 'Gress')
text = str(metric) + ' at Sha-Tin Grass'
if track_no == 1: # Sha Tin - dirt
mask = (data.Track == 'Sha Tin') & (data.Surface == 'Dirt')
text = str(metric) + ' at Sha-Tin Dirt'
if track_no == 2: # Happy Valley - grass
mask = (data.Track == 'Happy Valley') & (data.Surface == 'Gress')
text = str(metric) + ' at Happy Valley Grass'
return mask, text
def compute_last_fgrating(data, mask=''):
if len(mask) == 0:
return data.groupby('HorseId')['FGrating'].apply(lambda x: x.shift(1))
else:
return data.loc[mask][['HorseId', 'FGrating']].groupby('HorseId')['FGrating'].apply(
lambda x: x.shift(1))
for i in range(3):
mask, text = return_mask_and_text_from_tracks(featured_data, i, 'Last FGrating')
featured_data[text] = compute_last_fgrating(featured_data, mask=mask)
idx = [ser.index[0] for _, ser in featured_data.groupby('HorseId')[text] if pd.isna(ser.iat[0])]
featured_data.loc[idx, text] = 0
featured_data[text] = featured_data[text].fillna(method='bfill')
I would be very nice if I could do this.
Can you help?