python - 根据当前日期添加和计算行的非零值

Question

我有以下数据框:

 account_id contract_id 2020-12-01 00:00:00 2021-01-01 00:00:00 2021-02-01 00:00:00 2021-03-01 00:00:00 2021-04-01 00:00:00 2021-05-01 00:00:00 2021-06-01 00:00:00
0   1   A   200.0   0.0 0.0 0.0 0.0 0.0 0.0
1   1   B   300.0   300.0   0.0 0.0 0.0 0.0 0.0
2   1   C   0.0 0.0 0.0 400.0   400.0   400.0   400.0
3   2   K   100.0   100.0   100.0   0.0 0.0 0.0 0.0
4   2   F   0.0 0.0 50.0    50.0    50.0    50.0    50.0

我想要以下输出:

 account_id contract_id total   active_months   2020-12-01 00:00:00 2021-01-01 00:00:00 2021-02-01 00:00:00 2021-03-01 00:00:00 2021-04-01 00:00:00 2021-05-01 00:00:00 2021-06-01 00:00:00
 0  1   A   200 1   200.0   0.0 0.0 0.0 0.0 0.0 0.0
 1  1   B   600 2   300.0   300.0   0.0 0.0 0.0 0.0 0.0
 2  1   C   1200    3   0.0 0.0 0.0 400.0   400.0   400.0   400.0
 3  2   K   300 3   100.0   100.0   100.0   0.0 0.0 0.0 0.0
 4  2   F   200 4   0.0 0.0 50.0    50.0    50.0    50.0    50.0

在此示例中，当前日期设置为“2021 年 5 月 1 日”。我想添加和计数(仅非零)从第三列到当前日期列的值。 total 列应显示总和，而 active_months 应显示基于当前日期集的非零值的计数。
这是数据框的字典:

{'account_id': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2},
 'contract_id': {0: 'A', 1: 'B', 2: 'C', 3: 'K', 4: 'F'},
 datetime.datetime(2020, 12, 1, 0, 0): {0: 200.0,
  1: 300.0,
  2: 0.0,
  3: 100.0,
  4: 0.0},
 datetime.datetime(2021, 1, 1, 0, 0): {0: 0.0,
  1: 300.0,
  2: 0.0,
  3: 100.0,
  4: 0.0},
 datetime.datetime(2021, 2, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 100.0,
  4: 50.0},
 datetime.datetime(2021, 3, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 4, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 5, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 6, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0}}

这是输出的字典:

{'account_id': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2},
 'contract_id': {0: 'A', 1: 'B', 2: 'C', 3: 'K', 4: 'F'},
 'total': {0: 200, 1: 600, 2: 1200, 3: 300, 4: 200},
 'active_months': {0: 1, 1: 2, 2: 3, 3: 3, 4: 4},
 datetime.datetime(2020, 12, 1, 0, 0): {0: 200.0,
  1: 300.0,
  2: 0.0,
  3: 100.0,
  4: 0.0},
 datetime.datetime(2021, 1, 1, 0, 0): {0: 0.0,
  1: 300.0,
  2: 0.0,
  3: 100.0,
  4: 0.0},
 datetime.datetime(2021, 2, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 100.0,
  4: 50.0},
 datetime.datetime(2021, 3, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 4, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 5, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0},
 datetime.datetime(2021, 6, 1, 0, 0): {0: 0.0,
  1: 0.0,
  2: 400.0,
  3: 0.0,
  4: 50.0}}

Answer 1

我们可以使用 bool 索引过滤所需的列，然后计算并插入 total和 active_months df 中的列哪里total通过将 axis=1 上的值相加来计算和 active_months通过沿 axis=1 计算非零值来计算

m = pd.to_datetime(df.columns, errors='coerce') <= '1 May, 2021'
c = df.loc[:, m]

df.insert(2, 'total', c.sum(1))
df.insert(3, 'active_months', c.ne(0).sum(1))

>>> df

   account_id contract_id   total  active_months  2020-12-01 00:00:00  2021-01-01 00:00:00  2021-02-01 00:00:00  2021-03-01 00:00:00  2021-04-01 00:00:00  2021-05-01 00:00:00  2021-06-01 00:00:00
0           1           A   200.0              1                200.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
1           1           B   600.0              2                300.0                300.0                  0.0                  0.0                  0.0                  0.0                  0.0
2           1           C  1200.0              3                  0.0                  0.0                  0.0                400.0                400.0                400.0                400.0
3           2           K   300.0              3                100.0                100.0                100.0                  0.0                  0.0                  0.0                  0.0
4           2           F   200.0              4                  0.0                  0.0                 50.0                 50.0                 50.0                 50.0                 50.0