title: Weighted Average calculation based on conditions in pandas/python
body: <p>I have 2 <code>dataframes</code> for <code>Promotion Data</code>  and <code>Sales Data</code> as below.</p>
  <pre><code>+-----------+----------+
  | Promotion |   Date   |
  +-----------+----------+
  |     A     | 5-Jan-21 |
  +-----------+----------+
  |     B     | 8-Jan-21 |
  +-----------+----------+
  |     C     | 8-Jan-21 |
  +-----------+----------+
  
  df_prom = pd.DataFrame({
      'Promotion':['A','B','C'],
      'Date':['5-Jan-21','8-Jan-21','8-Jan-21']  })
  
  +-----------+-------+
  |    Date   | Sales |
  +-----------+-------+
  |  1-Jan-21 | 1,140 |
  +-----------+-------+
  |  2-Jan-21 | 3,046 |
  +-----------+-------+
  |  3-Jan-21 | 2,981 |
  +-----------+-------+
  |  4-Jan-21 | 2,262 |
  +-----------+-------+
  |  5-Jan-21 | 3,266 |
  +-----------+-------+
  |  6-Jan-21 | 3,231 |
  +-----------+-------+
  |  7-Jan-21 | 2,979 |
  +-----------+-------+
  |  8-Jan-21 | 1,687 |
  +-----------+-------+
  |  9-Jan-21 | 2,728 |
  +-----------+-------+
  | 10-Jan-21 | 1,136 |
  +-----------+-------+
  | 11-Jan-21 | 3,159 |
  +-----------+-------+
  | 12-Jan-21 | 1,799 |
  +-----------+-------+
  
      df_sales = pd.DataFrame({
          'Date':['1-Jan-21', '2-Jan-21', '3-Jan-21', '4-Jan-21', '5-Jan-21', '6-Jan-21', '7-Jan-21',
   '8-Jan-21', '9-Jan-21', '10-Jan-21', '11-Jan-21', '12-Jan-21'],
          'Sales':[1140, 3046, 2981, 2262, 3266, 3231, 2979, 1687, 2728, 1136, 3159, 1799]
      })
  </code></pre>
  <p>My task is to calculate a <code>weighted average</code> <code>Prior 3 days</code> &amp; <code>Post 3 days sales</code> considering all 3 promotions.</p>
  <p>Meaning, All 3 promotions has different dates. I need to bring it to a common <code>Prior 3 days</code> &amp; common <code>Post 3 days</code>.</p>
  <p>Step 1:</p>
  <p>Eg <code>Promotion A</code> is on <code>5-Jan-21</code>, so <code>Prior 3 days</code> would be from <code>2-Jan-21 to 4-Jan-21</code>.</p>
  <p>so the average would be <code>2763</code> (average of 3046, 2981, 2262)
  that of <code>Post 3 Days</code> would be <code>6-Jan-21 to 8-Jan-21</code>.so the average would be <code>2632</code> (average of 3231, 2979, 1687)</p>
  <p>in the case of <code>Promotion B</code> is on <code>8-Jan-21</code>, <code>Prior 3 days</code> would be <code>5-Jan-21 to 7-Jan-21</code> averages is equal to <code>3159</code> (average of 3266, 3231, 2979).</p>
  <p>The <code>Post 3 days</code> would be <code>9-Jan-21 to 11-Jan-21</code> average is <code>2341</code> (average of 2728, 1136, 3159).</p>
  <p>For <code>C</code> it is same as <code>B</code>. since, the dates are same.</p>
  <p>Step 2:</p>
  <p>Once after calculating the <code>Prior 3</code> of <code>A, B and C</code> individually. I must averages this together. i.e average would be <code>3027</code> (Average of 2763 of <code>A</code>, 3159 of <code>B</code> and  3159 of <code>C</code>).
  same applied to <code>Post 3 days</code> average. which is equal to <code>2438</code> (Average of 2632 of <code>A</code>,  2341 of <code>B</code>  and 2341 of <code>C</code>).</p>
  <p>so my final answer should look like</p>
  <pre><code>+--------------+---------+
  | Type         | Average |
  +--------------+---------+
  | Prior 3 days | 3,027   |
  +--------------+---------+
  | Post 3 days  | 2,438   |
  +--------------+---------+
  </code></pre>
  <p>please guide me on how should I approach to solve this.</p>