Most efficient method to select/delete entries in Pandas Dataframe

user_15905006 0
Viewed 78

I got a data frame with millions of rows and approximately thousand columns. I have a large process in selecting which rows that meets all correct criterias for different columns. Approximately 100 000 rows will be left when the process is over.

I'm currently using following code for each of every column to delete rows not meeting the criteriums but it's very time consuming and take several minutes and I think there must be a more efficient method to do this. I've read some threads but not find anything that relevant.

df = df.drop(df[(df['Column167'] < Column167_min) | (df['Column167'] > Column167_max)].index)

Thankful for suggestions.

pandas python
1 Answers

IIUC, create 2 tables the length of your number of columns for the lower and upper bounds then check if each column is between this range and finally, keep rows where all conditions are True.

df = pd.DataFrame(np.random.randint(1, 100, (9, 5)), columns=list('ABCDE'))
df = df.append(pd.Series([12, 22, 32, 42, 52], index=df.columns), ignore_index=True)

lb = [10, 20, 30, 40, 50]
ub = [15, 25, 35, 45, 55]

out = df[np.all((lb <= df) & (df <= ub), axis=1)]

Output:

>>> out
    A   B   C   D   E
9  12  22  32  42  52

Performance for 4,000,000 rows and 1,000 columns

M = int(4e6)  # rows
N = int(1e3)  # cols

lb = np.random.randint(0, 2, N)
ub = np.random.randint(98, 100, N)

df = pd.DataFrame(np.random.randint(1, 100, (M, N), dtype=np.int8))
out = df[np.all((lb <= df) & (df <= ub), axis=1)]

>>> df.shape
(4000000, 1000)

>>> out.shape
(25159,, 1000)

>>> %timeit -n 1 df[np.all((lb <= df) & (df <= ub), axis=1)]
16.3 s ± 2.96 s per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_152399510
Related