基于日期熊猫计算

Long_NgV

我有这个数据框：

a = [1, 2, 3, 4, 5]
b = ['2019-08-01', '2019-09-01', '2019-10-23', '2019-11-12', '2019-11-30']
c = [12, 0, 0, 0, 0]
d = [0, 23, 0, 0, 0]
e = [12, 24, 35, 0, 0]
f = [0, 0, 44, 56, 82]
g = [21, 22, 17, 75, 63]

df = pd.DataFrame({'ID': a, 'Date': b, 'Unit_sold_8': c, 
                  'Unit_sold_9': d, 'Unit_sold_10': e, 'Unit_sold_11': f, 
                   'Unit_sold_12': g})
df['Date'] = pd.to_datetime(df['Date'])

我想计算基于日期的每个 ID 的平均销售额。例如，如果 ID 的开放日期是 9 月，那么这个 ID 的平均销售额将从 9 月开始。我尝试过，np.select但我意识到这种方法会使我的代码超长。

col = df.columns

mask1 = (df['Date'] >= "08/01/2019") & (df['Date'] < "09/01/2019")
mask2 = (df['Date'] >= "09/01/2019") & (df['Date'] < "10/01/2019")
mask3 = (df['Date'] >= "10/01/2019") & (df['Date'] < "11/01/2019")
mask4 = (df['Date'] >= "11/01/2019") & (df['Date'] < "12/01/2019")
mask5 = (df['Date'] >= "12/01/2019")

condition2 = [mask1, mask2, mask3, mask4, mask5]
result2 = [df[col[2:]].mean(skipna = True, axis = 1),
          df[col[3:]].mean(skipna = True, axis = 1),
          df[col[4:]].mean(skipna = True, axis = 1),
          df[col[5:]].mean(skipna = True, axis = 1),
          df[col[6:]].mean(skipna = True, axis = 1)]
df.loc[:, 'Mean'] = np.select(condition2, result2, default = np.nan)

有没有更快的方法来解决这个问题？特别是当时间范围扩大时（12 个月、24 个月等）

海洋保护区

它对你有帮助吗？

from datetime import datetime
import numpy as np
from dateutil import relativedelta


check_date = datetime.today()
df['n_months'] = df['Date'].apply(lambda x: relativedelta.relativedelta( check_date,x).months)
df['total'] = df.iloc[:,range(2,df.shape[1]-1)].sum(axis=1)
df['avg']  = df['total'] / df['n_months']

print(df)

   ID       Date  Unit_sold_8  ...  n_months  total    avg
0   1 2019-08-01           12  ...         5     45   9.00
1   2 2019-09-01            0  ...         4     69  17.25
2   3 2019-10-23            0  ...         3     96  32.00
3   4 2019-11-12            0  ...         2    131  65.50
4   5 2019-11-30            0  ...         2    145  72.50

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。