Why pandas dataframe to_dict("records") performance is bad compared to another naive implementation?

user_5860262 0
Viewed 1121

Pandas to_dict("records") seems to have a much inferior performance compared to a naive implementation. Below is the code snippet of my implementation:

def fast_to_dict_records(df):
    data = df.values.tolist()
    columns = df.columns.tolist() 
    return [
        dict(zip(columns, datum))
        for datum in data
    ]

To compare the performance, try the below code snippet:

import pandas as pd
import numpy as np

df_test = pd.DataFrame(
    np.random.normal(size=(10000, 300)),
    columns=range(300)
)

%timeit df_test.to_dict('records')
%timeit fast_to_dict_records(df_test)

And the outputs are:

2.21 s ± 71.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
293 ms ± 15.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Namely my implementation is ~7.5 faster than pandas native implementation. Also, it should be easy to verify that the two methods provide the same result. I have also tested the performance against different sizes of dataframes and seems like my implementation is consistently outperform its counterpart (although the magnitude might differ).

I am curious am I missing anything here? I am just not convinced that pandas native implementation performance, which under my impression was quite competitive, can be beaten that much by a not-so-complicated alternative...

pandas dataframe python performance
1 Answers

TL;DR: Pandas is mostly written in pure Python like your implementation although it often use vectorized Numpy calls internally to speed the computation up. Unfortunately, this is not case here. As a result, the Pandas implementation is inefficient. Your implementation is faster, but it requires more memory.

In-depth study:

You can find the implementation of to_list here. It iterate over data using itertuples internally (see here for its code). The resulting (slightly simplified) Pandas code at the date of the 12th march 2021 is the following:

def maybe_box_native(value: Scalar) -> Scalar:
    if is_datetime_or_timedelta_dtype(value): # branch never taken here
        value = maybe_box_datetimelike(value)
    elif is_float(value):                     # branch always taken here
        value = float(value)                  # slow manual conversion for EACH values!
    elif is_integer(value):
        value = int(value)
    elif is_bool(value):
        value = bool(value)
    return value

def pandas_to_list(df):
    # From itertuples:
    fields = list(df.columns)
    arrays = [df.iloc[:, k] for k in range(len(df.columns))]
    tmpRes = zip(*arrays)

    # From to_list:
    columns = df.columns.tolist()
    rows = (dict(zip(columns, row)) for row in tmpRes)
    return [dict((k, maybe_box_native(v)) for k, v in row.items()) for row in rows]

Your implementation generates a big temporary list in memory using to_list while Pandas works with Python generators internally. This list should not be a problem in practice in most simple cases since the dict should eventually be much bigger.

However, to_list (in your implementation) also converts the Numpy types efficiently using vectorized Numpy calls internally while Pandas use a very slow approach. Indeed, Pandas checks and converts all the value one by one using the maybe_box_native pure Python function and slow if/else... It is thus not surprising that the Pandas implementation is slower. That being said, note that your code might behave differently with dates.

The current Pandas implementation is inefficient and it can clearly be improved in the future (possibly without requiring much more memory).

user_129395570
Related