I am trying to create a function which returns the z scores for multiple, floating-point fields of data grouped by categorical str/object data in the same 2D Array (originally from a parquet file via pandas.read_parquet).
I am looking for the equivalent functionality to the following operation using pandas:
def z_score_series(series: pd.Series) -> np.ndarray:
values = series.to_numpy()
return (values - values.mean()) / values.std()
df = pd.read_parquet(<filepath>, columns=<my_columns>, read_dictionary=['x', 'y', 'z'])
df.groupby(['x', 'y', 'z'])['a','b','c'].transform(z_score_series)
This operation, using my data of ~270,000 rows takes 43 seconds to complete, taking much longer for each additional field I wish to use to group my data by. I am looking to simply increase the speed of this operation.
I reached for numpy + numba (latest versions) to attempt to increase performance. I know that the fields that I group by will always be categorical data, and the fields I wish to calculate the z scores for numeric (float32).
from numba import prange
@numba.njit(fastmath=True)
def z_score(x):
return (x - x.mean())/x.std()
@numba.njit(parallel=True, fastmath=True)
def transform(unq_idx, values):
out = np.zeros(values.shape)
for x in prange(np.max(unq_idx)):
mask = np.where(unq_idx == x)
for field in prange(values.shape[0]):
out[field][mask] = z_score(values[field][mask])
return out
def get_z_score(df, groupby_cols, calc_cols):
coded_arr = np.array([df[x].values.codes for x in groupby_cols])
_, unq_idx = np.unique(coded_arr, axis=1, return_inverse=True)
calc_arr = np.array([df[x].to_numpy() for x in calc_cols])
out = transform(unq_idx, calc_arr)
return out
df = pd.read_parquet(<filepath>, columns=<my_columns>, read_dictionary=['x', 'y', 'z'])
out = get_z_score(df, ['x', 'y', 'z'], ['a', 'b', 'c'])
The variables in the ```get_z_score``` function just for reference.
coded_arr:
array([[ 0, 0, 0, ..., 168, 168, 168],
[ 0, 0, 0, ..., 0, 0, 0],
[ 0, 1, 2, ..., 4, 0, 0]], dtype=int16)
unq_idx:
array([ 0, 1, 2, ..., 30488, 30484, 30484], dtype=int64)
calc_arr:
array([[ 2.8231514e+07, 6.4926816e+07, 1.3972318e+07, ...,
1.8851726e+08, 2.0333277e+08, 2.5717517e+08],
[ 2.9859206e+07, 4.7409168e+07, 1.2298259e+07, ...,
2.8027318e+08, 1.9047000e+08, 2.3501864e+08],
[-6.1378721e-02, 7.5533399e-03, 2.3085000e-02, ...,
nan, nan, nan]], dtype=float32)
Using the code above I can reduce the equivalent operation from a time of 43 seconds to ~3.5 seconds with drastically improved performance when increasing the number of grouping fields. However I am not convinced that this implementation is the most efficient and that greater efficiencies could be achieved using numpy functions/techniques I've not thought of (I am relatively new to numpy).
Profiling the code suggests that is is the calculation of the index used to create the 1D vectors fed into the z-score function which take up 90% of the code run time, the np.where(unq_idx == x), and so this is where I thought the most improvement could be made.
I tried replacing this with a simple boolean mask: mask = unq_idx == x, but this is again a much slower operation (I'm assuming due to having to scan the whole boolean vector looking for Trues instead of just using the corresponding indices from np.where directly).
So this question could boil down to how do I loop through, create and use indexing arrays faster from a set of unique rows of a 2D numpy array. Using the parallel functionality of prange and numba helps, but I imagine there'd be a vectorised or better operation for this that could be coupled with using more of your threads. If you think this could be achieved a completely different way (different module?) or my code could be improved from where it is then please let me know.
Thanks
Edit: Here is some broadly similar random data that takes roughly similar periods of time to perform the operation on. I'm using an 8 logical core, Intel i7-4770 (3.4GHz)
np.random.seed(0)
df = pd.DataFrame(
{'x': map(chr, np.random.randint(1,122, 270000)),
'y': map(chr, np.random.randint(97,97+26, 270000)),
'z': map(chr, np.random.randint(97,97+10, 270000)),
'a': np.random.uniform(-1e+8, 1e+8, size=270000),
'b':np.random.uniform(-1e+8, 1e+8, size=270000),
'c':np.random.uniform(-1, 1, size=270000)}
)
df[['x','y','z']] = df[['x','y','z']].astype('category')
df[['a','b','c']] = df[['a','b','c']].astype(np.float32)
%timeit get_z_score(df, ['x', 'y', 'z'], ['a', 'b', 'c'])
3.3 s ± 42 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
%timeit df.groupby(['x', 'y', 'z'])[['a', 'b', 'c']].transform(z_score_series)
42.9 s ± 411 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)