CODEWITHSUNDEEP
pythonpandaspandas-groupby
Niazipan
Niazipan

Reputation: 1037

Pandas - retrieving previous result / row from each user

I'm new to Pandas.

I have a data frame which has looks like this (only much bigger):

    Horses        RaceDate Position
1   RedHorse      1/2/00   2
2   BlueHorse     1/2/00   6
3   YellowHorse   1/2/00   7
4   RedHorse      15/1/00  3

I want to add column for previous results. So that my data frame might end up looking like:

    Horses        RaceDate Position   PrevPosition
1   RedHorse      1/2/00   2          3
2   BlueHorse     1/2/00   6          -
3   YellowHorse   1/2/00   7          -
4   RedHorse      15/1/00  3          -

I had tried the following:

def prevRuns(horseName, raceDate):
    horseDf = df.loc[df['Horse'] == horseName]
    currentRace = horseDf.index[horseDf['RaceDate'] == raceDate]

    if len(horseDf.index) >= currentRace:
        return horseDf.at[currentRace+1,'Position']
    else:
        return 0


df['prevRun'] = df['Horse'].apply(prevRuns, raceDate = df['RaceDate'])

But it doesn't work. 

ValueError: Can only compare identically-labeled Series objects

Why doesn't it work?

Is there a much more elegant way to achieve what I'm trying to do?

Upvotes: 3

Views: 52

Answers (1)

jpp
jpp

Reputation: 164733

You can use groupby + shift:

# convert dates to datetime and sort descending
df['RaceDate'] = pd.to_datetime(df['RaceDate'], dayfirst=True)
df = df.sort_values('RaceDate', ascending=False)

# groupby and shift for previous position
df['PrevPosition'] = df.groupby('Horses')['Position'].shift(-1)

print(df)

        Horses   RaceDate  Position  PrevPosition
1     RedHorse 2000-02-01         2           3.0
2    BlueHorse 2000-02-01         6           NaN
3  YellowHorse 2000-02-01         7           NaN
4     RedHorse 2000-01-15         3           NaN

Upvotes: 2

Related Questions