return next row of dataframe based on matching condition

Question

Is this the most efficient way to get the next row of data based on a matching value in the prior row? It seems terribly cumbersome but the Int64Index type seems to not play well.

df_of_urls = {'ID': [100,101], 'URL': ['https://www.firsturl.com','https://www.secondurl.com']}
df_of_urls = pd.DataFrame.from_dict(df_of_urls)

prior_url = 'https://www.firsturl.com'

next_url = df_of_urls.iloc[df_of_urls[df_of_urls['URL']==prior_url ].index+1,1].values[0]

Depends, how many times are you performing this? You might consider `shift`, for example, to make a new column of values with each row offset by 1 index — roganjosh, Nov 08 '18 at 18:47
Can you add a small example `df_of_urls` dataframe to your question? (or even better, a full code example) — Xukrao, Nov 08 '18 at 18:56

score 1 · Accepted Answer · edited Nov 08 '18 at 20:43

Indexing a series is more efficient than indexing a dataframe.

# Index using iat accessor
next_url = df_of_urls['URL'].iat[np.where(df_of_urls['URL']==prior_url)[0][0] + 1]

# Index using NumPy array
next_url = df_of_urls['URL'].values[np.where(df_of_urls['URL']==prior_url)[0][0] + 1]

This algorithm is inefficient for certain cases. A full iteration is always required, even when the condition is satisfied near the beginning of the array. A manual loop can solve this problem by terminating immediately when the condition is satisfied.

See also: Efficiently return the index of the first value satisfying condition in array.

return next row of dataframe based on matching condition

1 Answers1