Quick way to find previous instance of a value in a pandas Dataframe or numpy array?

user_8447436 0
Viewed 381

I have an large data set (number of rows in millions) which I read into a pandas DataFrame called datafile.

Each row has an Order ID number - this is non-unique. So my datafile looks something like this

Price   Qty           OrderId

26690  3000  1213772

26700  3000  1215673

26705  6000  1216656

26700  3000  1213772

26710  3000  1215673

Now, what I want is, for each row - get the OrderID, find the previous occurrence of that OrderID in the DataFrame and get the corresponding price, and populate it in a new column "Prev_Price". If no previous occurrence is found, keep the value as 0. So my output should look like this

Price   Qty           OrderId  Prev_Price

26690  3000  1213772 0

26700  3000  1215673 0

26705  6000  1216656 0

26700  3000  1213772 26690

26710  3000  1215673 26700

I tried using numpy and wrote this function 

def getPrevPrice_np(x):
    try:
        return list(datanp[np.where(datanp[0:x,2]==datanp[x,2])][:,0])[-1]
    except:
        return 0

which I'm applying like this 

datanp = datafile.values
datafile['Prev_Price'] = pd.Series(datafile.index).apply(getPrevPrice_np)

But it is still quite slow for my requirement - what would be the fastest way to implement this?

pandas numpy python
1 Answers

This is faster:

datafile['Prev_Price'] = datafile.groupby('OrderId')['Price'].shift(fill_value=0)

It returns:

   Price   Qty  OrderId  Prev_Price
0  26690  3000  1213772           0
1  26700  3000  1215673           0
2  26705  6000  1216656           0
3  26700  3000  1213772       26690
4  26710  3000  1215673       26700

Now, on a short dataframe like the one you posted this method is actually slower.
But I did a couple of tests with bigger dataframes:

  • on a dataframe of 100000 (one hundred thousands) rows it is faster by a factor of 3, roughly.
  • on a dataframe of 1000000 (one million) rows it still takes ~1.5 seconds on my machine, I didn't measure the execution time of your method (takes too long and I killed the process).

Note: fill_value is a valid argument of pandas.DataFrame.shift since pandas 0.24.0. For older version, don't pass the argument and replace NaN values later using datafile.fillna(0).

user_104260370
Related