基于第二个Dataframe在轴1上的qcuts的Dataframe的和值

Question

我想把数据框A/axis=1在axis=1上计算的数据框B的qcut箱上的掩码值求和。

A和B具有相同的索引和列。q的值可能与示例中给出的值不同。数据帧B可以有NaNs

例子

import pandas as pd
import numpy as np
A = pd.DataFrame(
{'a': [4, 8, -2, -6, 7, -3, 4],
'b': [-8, 8, 6, 6, 5, -3, -2],
'e': [1, 34, -13, 5, 0, 8, -76],
'c': [1, 2, 6, 3, 8, -4, -20],
'd': [4, 0, 3, -4, 8, 4, 2]})
B = pd.DataFrame(
{'a': [34, 78, -2, -96, np.nan, -34, 44],
'b': [-82, 28, 96, 46, np.nan, -3, -20],
'e': [12, 324, -123, 56, np.nan, 8, -876],
'c': [np.nan, 28, 96, np.nan, 8, -34, -20],
'd': [42, -40, 23, -40, -50, 10, 97]})
q = [0, 0.33, 0.66, 1]

out = 
0    1     2
0  -8.0  1.0   8.0
1  10.0  0.0  42.0
2 -15.0  3.0  12.0
3  -6.0 -4.0  11.0
4   8.0  0.0   8.0
5  -7.0 -3.0  12.0
6 -98.0  0.0   6.0

方法似乎起作用的一种可能的方法是，

bins = B.apply(lambda x: pd.qcut(x, q, labels=False), axis=1)
out = pd.concat(
[A.mask(bins!=x, np.nan).sum(axis=1) for x in range(len(q)-1)],
axis=1)

然而，这是非常慢的。大部分时间都用于应用qcuts。

时机我的数据帧大约是3000行乘500列，应用qcuts需要2.7秒，加起来需要130毫秒。

import numpy as np
import pandas as pd
D = 3000
C = 500
dt_index = pd.date_range('2000-1-1', periods=D)
A = pd.DataFrame(np.random.rand(D,C), index=dt_index)
B = pd.DataFrame(np.random.rand(D,C), index=dt_index)
q = [0, 0.33, 0.66, 1]

%timeit bins = B.apply(lambda x: pd.qcut(x, q, labels=False), axis=1)
2.74 s ± 147 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit out = pd.concat([A.mask(bins!=x, np.nan).sum(axis=1) for x in range(len(q)-1)], axis=1)
131 ms ± 1.35 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

是否有一个显着更快的方法来实现相同的结果?

Answer 1

概述和结果

有趣的问题。pd.qcut()是一个很好的函数。如果您正在寻找更快的东西，您可能想看看numpy实现。不幸的是，numpy似乎没有完全相同的功能。np.nanpercentile()和np.digitize()的组合可以工作。但是它处理nan和bin的方式和Pandas有点不同。因此，您将需要一些额外的(丑陋的)处理来确保您拥有与原始实现完全相同的结果。我测试了3个实现:

• 您的实现(变量bins): 1460 ms
• 使用numpy循环(变量bins2)实现:590 ms
• 使用numpy列表推导(变量bins3)实现:325 ms

要确保最终结果相同，您可以将结果数据帧解析为可空Int64类型并使用df.equals()函数。

Numpy循环:

def makeBins2(df, q):
# Use nanpercentile as there are NaNs in the original dataframe
numpy_quantiles = np.nanpercentile(df, np.array(q)*100, axis=1).T
df_nparray = df.to_numpy()

myList = []
for i in range(0,len(df_nparray)):
row = df_nparray[i]
np_cut = numpy_quantiles[i]
# Correct for 0-start vs 1-start
result = np.digitize(row, np_cut, right=True) -1

# Correct for NaNs and left side
result = np.where(result>(len(q)-2), np.nan, result)
result = np.where(result<0, 0, result)
myList.append(result)


df_out = pd.DataFrame(myList, columns=list(df))
df_out.index = df.index
return df_out

Numpy list comprehension:

def row_function(row, q):
data = row[:-len(q)]
np_cut = row[-len(q):]
result = np.digitize(data, np_cut, right=True) -1

# Correct for NaNs and left side borders
result = np.where(result>(len(q)-2), np.nan, result)
result = np.where(result<0, 0, result)
return result


def makeBins3(df, q):
# Use nanpercentile as there are NaNs in the original dataframe
numpy_quantiles = np.nanpercentile(df, np.array(q)*100, axis=1).T
npArray = df.to_numpy()

# Make a single np matrix for row comprehension   
npAll = np.concatenate((npArray, numpy_quantiles),axis=1)

# List comprehension
myList = np.array(([row_function(x,q) for x in npAll]))

df_out = pd.DataFrame(myList, columns=list(df))
df_out.index = df.index
return df_out

检查结果:

import numpy as np
import pandas as pd 
D = 3000
C = 500
dt_index = pd.date_range('2000-1-1', periods=D)
A = pd.DataFrame(np.random.rand(D,C), index=dt_index)
B = pd.DataFrame(np.random.rand(D,C), index=dt_index)
q = [0, 0.33, 0.66, 1]
bins = B.apply(lambda x: pd.qcut(x, q, labels=False), axis=1)
bins2 = makeBins2(B,q)
bins3 = makeBins3(B,q)
print(bins.astype(pd.Int64Dtype()).equals(bins2.astype(pd.Int64Dtype())))
print(bins.astype(pd.Int64Dtype()).equals(bins3.astype(pd.Int64Dtype())))