python - Pandas:添加满足条件的元素的渐进计数列

Question

给定以下数据框df:

df = pd.DataFrame({'A':['Tony', 'Mike', 'Jen', 'Anna'], 'B': ['no', 'yes', 'no', 'yes']})

    A    B
0   Tony no 
1   Mike yes
2   Jen  no
3   Anna yes

我想添加另一列，逐步计算具有 df['B']='yes' 的元素:

    A    B   C
0   Tony no  0
1   Mike yes 1
2   Jen  no  0
3   Anna yes 2

我该怎么做？

Answer 1

您可以使用 numpy.where与 cumsum bool 掩码:

m = df['B']=='yes'
df['C'] = np.where(m, m.cumsum(), 0)

另一种解决方案是通过过滤创建count bool 掩码，然后通过reindex添加0值:

m = df['B']=='yes'
df['C'] = m[m].cumsum().reindex(df.index, fill_value=0)
print (df)
      A    B  C
0  Tony   no  0
1  Mike  yes  1
2   Jen   no  0
3  Anna  yes  2

性能(实际数据应该不同，最好先检查一下):

np.random.seed(123)
N = 10000
L = ['yes','no']
df = pd.DataFrame({'B': np.random.choice(L, N)})
print (df)

In [150]: %%timeit
     ...: m = df['B']=='yes'
     ...: df['C'] = np.where(m, m.cumsum(), 0)
     ...: 
1.57 ms ± 34.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [151]: %%timeit
     ...: m = df['B']=='yes'
     ...: df['C'] = m[m].cumsum().reindex(df.index, fill_value=0)
     ...: 
2.53 ms ± 54.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [152]: %%timeit
     ...: df['C'] = df.groupby('B').cumcount() + 1
     ...: df['C'].where(df['B'] == 'yes', 0, inplace=True)

4.49 ms ± 27.6 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)