python - 根据条件计算 DataFrame 中的值——太慢

Question

给定一个看起来像这样的 pandas DF:

   key  value
0    0      A
1    0      A
2    1      A
3    1      B
4    2      C
5    2      B
6    2      B

我正在尝试获取每个键的每个值的计数，即:

   key      A    B    C
0    0      2    0    0
1    1      1    1    0
2    2      0    2    1

我正在使用这段代码，事实证明它对我的需求来说太慢了(实际数据大约有 20,000 个键、200 个唯一值，每个键大约有 100 个值)。为什么它很慢，我该怎么做才能让它更快？

import pandas as pd
from collections import defaultdict
import random

n_keys = 200
values_per_key = 200
n_unique_values = 200
total_rows = n_keys * values_per_key

keys = [i//values_per_key for i in range(total_rows)]
values = [random.randint(0, n_unique_values-1) for i in range(total_rows)]
data = {'key': keys, 'value': values}
df = pd.DataFrame(data)

#df = pd.DataFrame({'key': [0, 0, 1, 1, 2, 2, 2],
# 'value': ['A', 'A', 'A', 'B', 'C', 'B', 'B']})

counts = defaultdict(list)
values = df['value'].value_counts().index
keys = sorted(df['key'].value_counts().index)

for key in keys:
    for value in values:
        # the following line makes this super slow
        ind = (df['key'] == key) & (df['value'] == value)
        counts[value].append(ind.sum())

print(pd.DataFrame(counts, index=keys))

Answer 1

你可以试试crosstab和 reset_index :

df = pd.crosstab(df['key'], df['value']).reset_index()
df.columns.name=None

print df
   key  A  B  C
0    0  2  0  0
1    1  1  1  0
2    2  0  2  1