Faster alternatives to Pandas pivot_table

Question

I'm using Pandas pivot_table function on a large dataset (10 million rows, 6 columns). As execution time is paramount, I try to speed up the process. Currently it takes around 8 secs to process the whole dataset which is way to slow and I hope to find alternatives to improve speed/performance.

My current Pandas pivot_table:

df_pivot = df_original.pivot_table(index="industry", columns = "months",
                    values = ["orders", "client_name"],
                    aggfunc ={"orders": np.sum, "client_name": pd.Series.nunique})

df_original includes all the data (10m rows, imported from a csv). Industry is the client's industry, months are the order months (Jan to Dec), orders are the number of orders. All data was converted to categorical data, except number of orders (int datatype). Originally industry, months and client_name were strings.

I tried using pandas.DataFrame.unstack - which was even slower. Also I experimented with Dask. The dask pivot_table yielded some improvement (6 sec execution time - so 2 sec less). However, it is still pretty slow. Are there any faster alternatives (for large datasets)? Maybe recreation of the pivot table with groupy, crosstab, ... Unfortunately, I did not get the alternatives to work at all and I am still quite new to Python and Pandas... Looking forward to your suggestions. Thanks in advance!

Update:

I figured out the groupby way with:

df_new = df_original.groupby(["months", "industry"]).agg({"orders": np.sum, "client_name": pd.Series.nunique}).unstack(level="months").fillna(0)

This is much faster now with about 2-3 secs. Are there still some options to improve speed further?

Answer 1

Convert the columns months and industry to categorical columns: https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html This way you avoid a lot of string comparisons.

Answer 2

You can use Sparse Matrices. They are fast to implement, a little bit restricted though. For example: You can't do indexing on a COO_matrix

I recently needed to train a recommmender system(lightFM) and it accepted sparse matrices as input, which made my job a lot easier. See it in action:

row  = np.array([0, 3, 1, 0])
col = np.array([0, 3, 1, 2])
data = np.array([4, 5, 7, 9])
mat = sparse.coo_matrix((data, (row, col)), shape=(4, 4))

>>> print(mat)
  (0, 0)    4
  (3, 3)    5
  (1, 1)    7
  (0, 2)    9
>>> print(mat.toarray())
[[4 0 9 0]
 [0 7 0 0]
 [0 0 0 0]
 [0 0 0 5]]

As you can see, it automatically creates a pivot table for you using the columns and rows of the data you have and fills the rest with zeros. You can convert the sparse matrix into array and dataframe as well (df = pd.DataFrame.sparse.from_spmatrix(mat, index=..., columns=...))

Answer 3

When you read the csv file into a df, you could pass a convert function (via the read_csv parameter converters), to transform client_name into a hash and downcast orders to an appropriate int type, in particular, an unsigned one.

This function lists the types and their ranges:

import numpy as np

def list_np_types():
    for k, v in np.sctypes.items():
        for i, d in enumerate(v):
            if np.dtype(d).kind in 'iu':
                # only int and uint have a definite range
                fmt = '{:>7}, {:>2}: {:>26}  From: {:>20}\tTo: {}'
                print(fmt.format(k, i, str(d),
                                 str(np.iinfo(d).min),
                                 str(np.iinfo(d).max)))

            else:
                print('{:>7}, {:>2}: {:>26}'.format(k, i, str(d)))


list_np_types()

Output:

    int,  0:       <class 'numpy.int8'>  From:                 -128 To: 127
    int,  1:      <class 'numpy.int16'>  From:               -32768 To: 32767
    int,  2:      <class 'numpy.int32'>  From:          -2147483648 To: 2147483647
    int,  3:      <class 'numpy.int64'>  From: -9223372036854775808 To: 9223372036854775807
   uint,  0:      <class 'numpy.uint8'>  From:                    0 To: 255
   uint,  1:     <class 'numpy.uint16'>  From:                    0 To: 65535
   uint,  2:     <class 'numpy.uint32'>  From:                    0 To: 4294967295
   uint,  3:     <class 'numpy.uint64'>  From:                    0 To: 18446744073709551615
  float,  0:    <class 'numpy.float16'>
  float,  1:    <class 'numpy.float32'>
  float,  2:    <class 'numpy.float64'>
complex,  0:  <class 'numpy.complex64'>
complex,  1: <class 'numpy.complex128'>
 others,  0:             <class 'bool'>
 others,  1:           <class 'object'>
 others,  2:            <class 'bytes'>
 others,  3:              <class 'str'>
 others,  4:       <class 'numpy.void'>