title: Fastest way to compare row and previous row in pandas dataframe with millions of rows
body: <p>I'm looking for solutions to speed up a function I have written to loop through a pandas dataframe and compare column values between the current row and the previous row.</p>
  
  <p>As an example, this is a simplified version of my problem:</p>
  
  <pre><code>   User  Time                 Col1  newcol1  newcol2  newcol3  newcol4
  0     1     6     [cat, dog, goat]        0        0        0        0
  1     1     6         [cat, sheep]        0        0        0        0
  2     1    12        [sheep, goat]        0        0        0        0
  3     2     3          [cat, lion]        0        0        0        0
  4     2     5  [fish, goat, lemur]        0        0        0        0
  5     3     9           [cat, dog]        0        0        0        0
  6     4     4          [dog, goat]        0        0        0        0
  7     4    11                [cat]        0        0        0        0
  </code></pre>
  
  <p>At the moment I have a function which loops through and calculates values for '<code>newcol1</code>' and '<code>newcol2</code>' based on whether the '<code>User</code>' has changed since the previous row and also whether the difference in the '<code>Time</code>' values is greater than 1. It also looks at the first value in the arrays stored in '<code>Col1</code>' and '<code>Col2</code>' and updates '<code>newcol3</code>' and '<code>newcol4</code>' if these values have changed since the previous row.</p>
  
  <p>Here's the pseudo-code for what I'm doing currently (since I've simplified the problem I haven't tested this but it's pretty similar to what I'm actually doing in ipython notebook):</p>
  
  <pre><code> def myJFunc(df):
  ...     #initialize jnum counter
  ...     jnum = 0;
  ...     #loop through each row of dataframe (not including the first/zeroeth)
  ...     for i in range(1,len(df)):
  ...             #has user changed?
  ...             if df.User.loc[i] == df.User.loc[i-1]:
  ...                     #has time increased by more than 1 (hour)?
  ...                     if abs(df.Time.loc[i]-df.Time.loc[i-1])&gt;1:
  ...                             #update new columns
  ...                             df['newcol2'].loc[i-1] = 1;
  ...                             df['newcol1'].loc[i] = 1;
  ...                             #increase jnum
  ...                             jnum += 1;
  ...                     #has content changed?
  ...                     if df.Col1.loc[i][0] != df.Col1.loc[i-1][0]:
  ...                             #record this change
  ...                             df['newcol4'].loc[i-1] = [df.Col1.loc[i-1][0], df.Col2.loc[i][0]];
  ...             #different user?
  ...             elif df.User.loc[i] != df.User.loc[i-1]:
  ...                     #update new columns
  ...                     df['newcol1'].loc[i] = 1; 
  ...                     df['newcol2'].loc[i-1] = 1;
  ...                     #store jnum elsewhere (code not included here) and reset jnum
  ...                     jnum = 1;
  </code></pre>
  
  <p>I now need to apply this function to several million rows and it's impossibly slow so I'm trying to figure out the best way to speed it up. I've heard that Cython can increase the speed of functions but I have no experience with it (and I'm new to both pandas and python). Is it possible to pass two rows of a dataframe as arguments to the function and then use Cython to speed it up or would it be necessary to create new columns with "<code>diff</code>" values in them so that the function only reads from and writes to one row of the dataframe at a time, in order to benefit from using Cython?  Any other speed tricks would be greatly appreciated!</p>
  
  <p>(As regards using .loc, I compared .loc, .iloc and .ix and this one was marginally faster so that's the only reason I'm using that currently)</p>
  
  <p>(Also, my <code>User</code> column in reality is unicode not int, which could be problematic for speedy comparisons)</p>