总计3个月和6个月

How to compute the average billing of each customer in the last 3 and 6 months. When there is not enough data in the 3 or 6 month window, the service registers the entry as NaN (or null).

结果需要像这样:

+--------+-------+--------------------------+--------------------------+
|customer|account|avg_invoices_last_3_months|avg_invoices_last_6_months|
+--------+-------+--------------------------+--------------------------+
|C1000   |A1100  |56,333                    |41,333                    |
|C1000   |A1200  |41,333                    |NaN                       |

样本数据:

df = pd.DataFrame({' account': {0: ' A1100',
  1: ' A1100',
  2: ' A1100',
  3: ' A1100',
  4: ' A1100',
  5: ' A1100',
  6: ' A1200',
  7: ' A1200',
  8: ' A1100',
  9: ' A1300',
  10: ' A1300',
  11: ' A1300',
  12: ' A2100',
  13: ' A2100',
  14: ' A2100',
  15: ' A2100',
  16: ' A2100',
  17: ' A2100',
  18: ' A2100'},
 ' invoice': {0: 80000,
  1: 1000,
  2: 78000,
  3: 34000,
  4: 55000,
  5: 80000,
  6: 90000,
  7: 55000,
  8: 80000,
  9: 10000,
  10: 10000,
  11: 20000,
  12: 1000,
  13: 78000,
  14: 3000,
  15: 45000,
  16: 78000,
  17: 55000,
  18: 80000},
 ' month': {0: ' 2019-05-01',
  1: ' 2019-06-01',
  2: ' 2019-07-01',
  3: ' 2019-10-01',
  4: ' 2019-11-01',
  5: ' 2019-12-01',
  6: ' 2019-10-01',
  7: ' 2019-11-01',
  8: ' 2019-12-01',
  9: ' 2019-10-01',
  10: ' 2019-11-01',
  11: ' 2019-12-01',
  12: ' 2019-06-01',
  13: ' 2019-07-01',
  14: ' 2019-08-01',
  15: ' 2019-09-01',
  16: ' 2019-10-01',
  17: ' 2019-11-01',
  18: ' 2019-12-01'},
 'customer': {0: 'C1000',
  1: 'C1000',
  2: 'C1000',
  3: 'C1000',
  4: 'C1000',
  5: 'C1000',
  6: 'C1000',
  7: 'C1000',
  8: 'C1000',
  9: 'C1000',
  10: 'C1000',
  11: 'C1000',
  12: 'C2000',
  13: 'C2000',
  14: 'C2000',
  15: 'C2000',
  16: 'C2000',
  17: 'C2000',
  18: 'C2000'}})

I'm trying to make it with rolling function of pandas, but it doesn't work.

评论