Pandas - Compute a value for each row, using information from previous rows?

Question

So I have a Pandas dataframe, df, which looks like this (with other columns):

timestamp	player	event	location_x	location_y	location_z	dist
2021-07-14 22:54:28.001000	Bob	'PlayerMoveEvent'	10	10	10	?
2021-07-14 22:54:28.001600	Alice	'PlayerJoinEvent'	NaN	NaN	NaN	?
2021-07-14 22:54:28.001600	Alice	'PlayerMoveEvent'	20	20	20	?
2021-07-14 22:54:28.001670	Bob	'PlayerMoveEvent'	11	10	10	?
2021-07-14 22:54:28.001740	Eve	'PlayerMoveEvent'	5	15	9	?
2021-07-14 22:54:28.001670	Eve	'PlayerQuitEvent'	NaN	NaN	NaN	?
2021-07-14 22:54:28.001820	Alice	'PlayerMoveEvent'	18	20	19	?

Every time a player moves, an event is triggered and their position is logged.

Now I want to figure out the distance √((x2-x1)^2 + (y2-y1)^2 + (z2-z1)^2) between two given players, in this case Alice and Bob, and add it to the end of the dataframe as a new column.

For a row with another player, or a non-'PlayerMoveEvent', it would just repeat the same value from the previous row, because it'd be using the older locations (I tried doing this by storing the locations as separate lists, shown below).

def player_distance(df, player_0, player_1):
    player_0_location = [None, None, None]
    player_1_location = [None, None, None]

I've tried many things (before I was using .apply with a lambda function "dist"), but now I'm trying to do it all in one function. I know that iterrows() isn't doing what I think it does below, because none of the below worked as an IF statement, even if they worked when tested manually outside of the function:

if((df.loc[i]['player'][0]) == player_0) & (df.loc[i]['event'][0]) == 'PlayerMoveEvent')):

if((df.loc[i]['player'].item()) == player_0) & (df.loc[i]['event'].item()) == 'PlayerMoveEvent')):

both of these neither work when type-casted to string

if((df.loc[i]['player'].item() == player_0) & (df.loc[i]['event'].item() == 'PlayerMoveEvent')):

if((j['player'] == player_0) and (j['event'] == 'PlayerMoveEvent')):

if((j['player'].eq(player_0)) & (j['event'].eq('PlayerMoveEvent'))):

def player_distance(df, player_0, player_1):
    player_0_location = [None, None, None]
    player_1_location = [None, None, None]
    for i, j in df.iterrows():
        # PROBLEM LINE
        if((df.loc[i]['player'][0]) == player_0) & (df.loc[i]['event'][0]) == 'PlayerMoveEvent')):
        # this line always gives a "ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()." error
            player_0_location[0] = df.loc[i, 'location_x'].values[0]
            player_0_location[1] = df.loc[i, 'location_y'].values[0]
            player_0_location[2] = df.loc[i, 'location_z'].values[0]
        # PROBLEM LINE
        if((df.loc[i]['player'][0]) == player_1) & (df.loc[i]['event'][0]) == 'PlayerMoveEvent')):
            player_1_location[0] = df.loc[i, 'location_x'].values[0]
            player_1_location[1] = df.loc[i, 'location_y'].values[0]
            player_1_location[2] = df.loc[i, 'location_z'].values[0]
        if ((None not in player_0_location) and (None not in player_1_location)):
            df.loc[i]['dist'] = (((player_0_location[0] - player_1_location[0]) ** 2) + ((player_0_location[1] - player_1_location[1]) ** 2) + ((player_0_location[2] - player_1_location[2]) ** 2)) ** 0.5

user_3282436 · Answer

Your current attempt feels very imperative, but I find that it often helps to treat Pandas more like a declarative language.

Here's how I would approach the problem statement (though note that I haven't tested this code):

# filter down to move events for just these two players
df_important_events = df.loc[(df['player'].isin([player_0, player_1])) & (df['event'] == 'PlayerMoveEvent')]

# make sure events are timestamp-ordered for the next step
df_important_events = df_important_events.sort_values(['timestamp'])

# filter down to the latest move event per player
df_latest_move_events = df_important_events.groupby('player').last().reset_index(drop=True)

if len(df_latest_move_events) != 2:
    # handle the case where the user passed in an invalid player, or one of the players has not moved yet

# filter down to just location columns
df_locations = df_latest_move_events[['location_x', 'location_y', 'location_z']]

# convert to numpy array of (x, y, z) tuples
locations_array = df_locations.to_records()

# return euclidean distance between the two locations
return numpy.linalg.norm(locations_array[1] - locations_array[0])

Pandas - Compute a value for each row, using information from previous rows?

1 Answers