How can I generalize a conditional backfill?

Viewed 59

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:

  1. If a horse never raced on a particular track before, then its 'Last FGrating at ' is 0;
  2. 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?

0 Answers
Related