Efficient conditional validation of large dataset in python

user_3257191 0
Viewed 239

I have a simple/flat dataset that looks like...

columnA columnB columnC
value1a value1b value1c
value2a value2b value2c
...
valueNa valueNb valueNc

Although the structure is simple, it's tens of millions of rows deep and I have 50+ columns.

I need to validate that each value in a row conforms with certain format requirements. Some checks are simple (e.g. isDecimal, isEmpty, isAllowedValue etc) but some involve references to other columns (e.g. does columnC = columnA / columnB) and some involve conditional validations (e.g. if columnC = x, does columnB contain y).

I started off thinking that the most efficient way to validate this data was by applying lambda functions to my dataframe...

df.apply(lambda x: validateCol(x), axis=1)

But it seems like this can't support the full range of conditional validations I need to perform (where specific cell validations need to refer to other cells in other columns).

Is the most efficient way to do this to simply loop through all rows one-by-one and check each cell one-by-one? At the moment, I'm resorting to this but it's taking several minutes to get through the list...

df.columns = ['columnA','columnB','columnC']
myList = df.T.to_dict().values() #much faster to iterate over list
for row in myList:
    #validate(row['columnA'], row['columnB'], row['columnC'])

Thanks for any thoughts on the most efficient way to do this. At the moment, my solution works, but it feels ugly and slow!

validation python performance
2 Answers

Iterating over rows is very inefficient. You should work on columns directly using vectorized functions or Pandas functions. Indeed, Pandas store data column-major in memory. Thus, row by row accesses require data from many different locations to be fetched and aggregated from memory. Vectorization in not possible or hard to achieve efficiently (since most hardware themselves cannot vectorize this). Moreover, Python loops are generally very slow (if you use the mainstream CPython interpreter).

You can work on columns using Pandas or Numpy directly. Note that Numpy operation are much faster if you work on well-defined native types (float, small integers and bounded strings, as opposed to Python objects like unbounded strings and large integers). Note also that Pandas already store data using Numpy internally.

Here is an example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'columnA':[1, 4, 5, 7, 8], 'columnB':[4, 7, 3, 6, 9], 'columnC':[5, 8, 6, 1, 2]})

a = df['columnA'].to_numpy()
b = df['columnB'].to_numpy()
c = df['columnC'].to_numpy()

# You can use np.isclose for a row-by-row result stored into an array
print(np.allclose(a/b, c))
user_129395570

Another approach is to split your dataset into smaller pieces and parallelize computation. For validations part , type, or schema i suggest to use a library like voluptuous or others, i found a schema very maintainable approach. Anyway, as told by @jerome vectorial approach can save you a lot of computational time

user_31946180
Related