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.
