title: How do I write an efficient function to calculate the average closing balance for different accounts given a time period
body: <p>I work in a financial organization. In our transactions table, we keep track of customers' balances only when they transact. For example, if a customer opened an account on the 1st of October with $200 and then withdraws $50 on the 8th of October, he will have just two entries in the transactions table, one for the 2020/10/01 and the other for the 2020/10/8. Now, the focus of this question is on the <em>closing balances</em>. Going by this analogy, if we use today as the cut-off date, you would agree that the customer would have had a closing balance of $200 for 7 days (2020/10/8 - 2020/10/1) and $150 for the remaining 29 days.</p>
  <p>Now, I am not sure how to write this function. I have been running into errors and I would appreciate if anyone can help me out with the python code and corresponding comments so that this becomes a valid learning experience for me.</p>
  <p>This is a sample of the dataset that I have:</p>
  <pre><code>sample_df = pd.DataFrame({'ID': [15, 16, 15, 15, 16, 17, 17, 16],
                           'Calendar_Date': ['2020-10-10', '2020-10-12', '2020-10-12', '2020-10-22', '2020-10-28', '2020-10-30', '2020-11-03', '2020-11-04'] ,
                           'Closing_Balance': [10000, 3000, 6000, 5100, 14500, 25000, 13000, 9000]}) 
  </code></pre>
  <p>and this is the result that I expect:</p>
  <pre><code>result_df = pd.DataFrame({'ID':[15, 16, 17],
                           'Total_Days': [26, 24, 6],
                           'Average_Account_Balance': [5823.08, 6375.00, 19000]})
  </code></pre>
  <p>For clarity: This is how I arrived at the result_df:</p>
  <blockquote>
  <p>When ID = 15, <strong>Total_Days</strong> = (2+10+15) = 27;
  <strong>Average_Account_Balance</strong> = ((10000 * 2) + (6000 * 10) + (5100 * 15))/27 = 156500/27 = 5796.3</p>
  <p>when ID = 16, <strong>Total_Days</strong> = (16+7+2) = 25;
  <strong>Average_Account_Balance</strong> = ((3000 * 16) + (14500 * 7) + (9000 * 2))/25 = 167500/25 = 6700.00</p>
  <p>when ID = 17, <strong>Total_Days</strong> = (4+3) = 7;<br />
  <strong>Average_Account_Balance</strong> = ((25000 * 4) + (13000 * 3))/7 = 139000/7 = 19857.14</p>
  </blockquote>
  <p>I need the solution to be computationally efficient because you can guess how many transactions we have in our DB. Please feel free to ask further questions if you are not clear on anything stated or implied here. Thank you!</p>