python - 对 pandas 数据框中的相等索引重复操作

Question

我有一个故意带有非唯一索引的数据框。我想对键相等的行进行操作，如下所示。对于每个唯一的键，我想将第一个“其他数字”与每个“数字”列相加。这是否可以在不分割数据帧或其他耗时操作的情况下实现？

import pandas as pd


d = {'key':['a', 'a', 'b','b'],
    'numbers':[10,20,30,40],
     'other_numbers':[1,2,3,4]
     }

df = pd.DataFrame(data=d)
df = df.set_index('key')

print df

##      numbers  other_numbers    new
##  key  
##  a       10             1      11
##  a       20             2      21
##  b       30             3      33
##  b       40             4      43

Answer 1

您可以使用duplicated对于重复索引的第一个值，用于按 mask 过滤 other_numbers 列对于被 ffill 替换的 NaN(fillna 与 method='ffill'):

df['new'] = df['numbers'] + df['other_numbers'].mask(df.index.duplicated()).ffill().astype(int)
print (df)
     numbers  other_numbers new
key                              
a         10              1  11
a         20              2  21
b         30              3  33
b         40              4  43

时间:

np.random.seed(123)

N = 1000000

df = pd.DataFrame({'numbers': np.random.randint(20,size=N),
                   'other_numbers': np.random.randint(10,size=N)},
                    index=np.random.randint(20000,size=N)).sort_index()
df.index.name = 'key'
print (df)

In [83]: %timeit df['new'] = df['numbers'] + df['other_numbers'].mask(df.index.duplicated()).ffill().astype(int)
10 loops, best of 3: 34.8 ms per loop

In [84]: %timeit df.assign(new1=df.groupby('key')['other_numbers'].transform('first')+df['numbers'])
10 loops, best of 3: 64.7 ms per loop