Pandas - 有效地从列表中搜索数据帧列

I am trying to figure out the most efficient way to search a data frame in Pandas with a list (dataframe) of other values without using brute force methods. Is there a way to vectorize it? I know I can for loop each element of the list (or dataframe) and extract the data using the loc method, but was hoping for something faster. I have a data frame with 1 million rows and I need to search within it to extract the index of 600,000 rows.

我试图找出使用其他值的列表(数据帧)搜索Pandas中的数据帧的最有效方法,而不使用强力方法。有没有办法对它进行矢量化?我知道我可以循环列表(或数据帧)的每个元素并使用loc方法提取数据,但希望更快的东西。我有一个包含100万行的数据框,我需要在其中搜索以提取600,000行的索引。

Example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'WholeList': np.round(1000000*(np.random.rand(1000000)),0)})
df2 = pd.DataFrame({'ThingsToFind': np.arange(50000)+50000})
df.loc[1:10,:]
#Edited, now that I think about it, the 'arange' method would have been better to populate the arrays.

I want the most efficient way to get the index of df2 in df, where it exists in df.

我想要最有效的方法来获得df中的df2索引,它存在于df中。

Thanks!

3 个解决方案

#1

Here's an approach with np.searchsorted as it seems the second dataframe has its elements sorted and unique -

这是一个使用np.searchsorted的方法,因为它似乎第二个数据框的元素已排序且唯一 -

def find_index(a,b, invalid_specifier = -1):
    idx = np.searchsorted(b,a)
    idx[idx==b.size] = 0
    idx[b[idx] != a] = invalid_specifier
    return idx

def process_dfs(df, df2):
    a = df.WholeList.values.ravel()
    b = df2.ThingsToFind.values.ravel()
    return find_index(a,b, invalid_specifier=-1)

Sample run on arrays -

在阵列上运行的示例 -

In [200]: a
Out[200]: array([ 3,  5,  8,  4,  3,  2,  5,  2, 12,  6,  3,  7])

In [201]: b
Out[201]: array([2, 3, 5, 6, 7, 8, 9])

In [202]: find_index(a,b, invalid_specifier=-1)
Out[202]: array([ 1,  2,  5, -1,  1,  0,  2,  0, -1,  3,  1,  4])

Sample run on dataframes -

样本在数据帧上运行 -

In [188]: df
Out[188]: 
    WholeList
0           3
1           5
2           8
3           4
4           3
5           2
6           5
7           2
8          12
9           6
10          3
11          7

In [189]: df2
Out[189]: 
   ThingsToFind
0             2
1             3
2             5
3             6
4             7
5             8
6             9

In [190]: process_dfs(df, df2)
Out[190]: array([ 1,  2,  5, -1,  1,  0,  2,  0, -1,  3,  1,  4])

#2

Pandas dataframes have an isin() method that works really well:

Pandas数据帧有一个非常好用的isin()方法:

df[df.WholeList.isin(df2.ThingsToFind)]

It seems reasonably performant on my MBP:

我的MBP看起来相当合理:

CPU times: user 3 µs, sys: 5 µs, total: 8 µs
Wall time: 11 µs

#3

I agree with @JDLong - IMO Pandas is pretty fast:

我同意@JDLong - IMO Pandas非常快:

In [49]: %timeit df[df.WholeList.isin(df2.ThingsToFind)]
1 loop, best of 3: 819 ms per loop

In [50]: %timeit df.loc[df.WholeList.isin(df2.ThingsToFind)]
1 loop, best of 3: 814 ms per loop

In [51]: %timeit df.query("WholeList in @df2.ThingsToFind")
1 loop, best of 3: 837 ms per loop

#1