title: Python - Counter in 2 million row table
body: <p>As an example, I have the following dataframe:</p>
  
  <pre><code>Date                     Balance
  2013-04-01 03:50:00         A
  2013-04-01 04:00:00         A
  2013-04-01 04:15:00         B
  2013-04-01 04:15:00         B
  2013-04-01 04:25:00         A
  2013-04-01 04:25:00         A
  2013-04-01 04:35:00         B
  2013-04-01 04:40:00         B
  2013-04-02 04:55:00         B
  2013-04-02 04:56:00         A
  2013-04-02 04:57:00         A
  2013-04-03 10:30:00         A
  2013-04-03 16:35:00         A
  2013-04-03 20:40:00         A
  </code></pre>
  
  <p>My goal is to add one column 'Counter' that basically shows a balance of the number of A's and B's. So, every time an A appears, the counter column increases one value. Every time B appears, the counter column decreases one value. If two A's appear at the same time (same Date) in two consecutive rows, the balance should increase by two on both of the rows (the same reasoning applies for consecutive B's or for A's and B's at the same time). Therefore, the dataframe would look like this in the end:</p>
  
  <pre><code> Date                     Balance        Counter
  2013-04-01 03:50:00         A               1
  2013-04-01 04:00:00         A               2
  2013-04-01 04:15:00         B               0
  2013-04-01 04:15:00         B               0
  2013-04-01 04:25:00         A               2
  2013-04-01 04:25:00         A               2
  2013-04-01 04:35:00         B               1
  2013-04-01 04:40:00         B               0
  2013-04-02 04:55:00         B              -1
  2013-04-02 04:56:00         A               0
  2013-04-02 04:57:00         A               1
  2013-04-03 10:30:00         A               2
  2013-04-03 16:35:00         A               3
  2013-04-03 20:40:00         A               4
  </code></pre>
  
  <p>The major problem is that the dataframe has more than 2 millions rows, therefore it is really time consuming to perform a loop. Is there any way to implement a vectorized approach to this problem?</p>
  
  <p>Edit (I was able to compile a solution that works well if the dates are not the same on consecutive rows). Anyone could help me to figure out the rest?</p>
  
  <pre><code>d = {'Date': ['2013-04-01 03:50:00', '2013-04-01 04:00:00','2013-04-01 
  04:15:00','2013-04-01 04:15:00','2013-04-01 04:25:00',
  '2013-04-01 04:25:00','2013-04-01 04:35:00','2013-04-01 04:40:00','2013-04- 
  02 04:55:00','2013-04-02 04:56:00',         
  '2013-04-02 04:57:00','2013-04-03 10:30:00','2013-04-03 16:35:00','2013-04- 
  03 20:40:00'], 'Balance': ['A','A','B','B','A','A','B','B','B',                                                                                                
  'A','A','A','A','A',]}
  
  df = pd.DataFrame(data=d)
  
  df['plus_minus'] = np.where(df.Balance == 'A', 1, -1)
  df['Counter'] = df['plus_minus'].cumsum()
  </code></pre>