How to make my groupby and transpose operations efficient?

Question

I have a DataFrame of size 3,745,802 rows and 30 columns. I would like to perform certain groupby and transpose operations which will finally end up with more (unimaginable) columns/features.

I also tried parallel processing,Dask,Modin and the pandarallel package, but couldn't do this because all these packages don't support unstack operation yet. The sample dataframe is the same as my real dataframe but the record count is small (as I cannot share the real data).

Please find the sample dataframe (subset of real dataframe)

df = pd.DataFrame({
'subject_id':[1,1,1,1,2,2,2,2,3,3,4,4,4,4,4],
'readings' : ['READ_1','READ_2','READ_1','READ_3','READ_1','READ_5','READ_6','READ_8','READ_10','READ_12','READ_11','READ_14','READ_09','READ_08','READ_07'],
'val' :[5,6,7,11,5,7,16,12,13,56,32,13,45,43,46],
})


N=2  # dividing into two dataframes for parallel processing.
dfs = [x for _,x in df.groupby(pd.factorize(df['subject_id'])[0] // N)]

Please find dummy huge dataframe for testing (only the column names match but the data inside is random)

df_size = int(3e7)
N = 30000000
s_arr = pd.util.testing.rands_array(10, N)
df = pd.DataFrame(dict(subject_id=np.random.randint(1, 1000, df_size),
                   readings = s_arr,
                   val=np.random.rand(df_size)
                   ))

Code (with the help of SO users)

import multiprocessing as mp

def transpose_ope(df):                      #this function does the transformation like I want
    df_op = (df.groupby(['subject_id','readings'])['val']
           .describe()
           .unstack()
           .swaplevel(0,1,axis=1)
           .reindex(df['readings'].unique(), axis=1, level=0))
    df_op.columns = df_op.columns.map('_'.join)
    df_op = df_op.reset_index()
    return df_op


def main():

    with mp.Pool(mp.cpu_count()) as pool:
       res = pool.map(transpose_ope, [df for df in dfs])

if __name__=='__main__':
    main()

What transpose_ope does?

It works like this. A subject can have n number of readings. Instead of having his n readings as row, I would like to create the summary statistics of each unique reading as a column. So, subject_id = 1 has 4 readings in total (but 3 unique readings). So, instead of having 4 rows for 1 subject, I would like create features/columns for the readings. But again, instead of readings, I would like to have summary statistics of the readings (MIN,MAX,STDDEV,COUNT,MEAN) etc. So each reading will have 5 columns. So subject_id = 1 will have 15 column in total

Though this code works fine on the sample dataframe, in real life my data has more than four million rows and 30 columns. Moreover, when I apply the transformation of my interest as shown in the code, the column count can go up to 955,500. I'm basically trying to reduce the row count (but increasing the column count).

Can you help?

Answer 1

A few months after this question was posted, Modin added support for .unstack(). So that's the simplest technical approach to solving this.

https://modin.readthedocs.io/en/stable/supported_apis/series_supported.html