Pandas Dataframe: to_dict() poor performance

Question

I work with apis that return large pandas dataframes. I'm not aware of a fast way to iterate through the dataframe directly so I cast to a dictionary with to_dict().

After my data is in dictionary form, the performance is fine. However, the to_dict() operation tends to be a performance bottleneck.

I often group columns of the dataframe together to form multi-index and use the 'index' orientation for to_dict(). Not sure if the large multi-index drives the poor performance.

Is there a faster way to cast a pandas dataframe? Maybe there is a better way to iterate directly over the dataframe without any cast? Not sure if there is a way I could apply vectorization.

Below I give sample code which mimics the issue with timings:

import pandas as pd
import random as rd
import time

#Given a dataframe from api (model as random numbers)
df_columns = ['A','B','C','D','F','G','H','I']
dict_origin = {col:[rd.randint(0,10) for x in range(0,1000)] for col in df_columns}
dict_origin = pd.DataFrame(dict_origin)

#Transform to pivot table
t0 = time.time()
df_pivot = pd.pivot_table(dict_origin,values=df_columns[-3:],index=df_columns[:-3])
t1 = time.time()
print('Pivot Construction takes: ' + str(t1-t0))

#Iterate over all elements in pivot table
t0 = time.time()
for column in df_pivot.columns:
    for row in df_pivot[column].index:
        test = df_pivot[column].loc[row]
t1 = time.time()
print('Dataframe iteration takes: ' + str(t1-t0))


#Iteration over dataframe too slow. Cast to dictionary (bottleneck)
t0 = time.time()
df_pivot = df_pivot.to_dict('index')
t1 = time.time()
print('Cast to dictionary takes: ' + str(t1-t0))

#Iteration over dictionary is much faster
t0 = time.time()
for row in df_pivot.keys():
    for column in df_pivot[row]:
        test = df_pivot[row][column]
t1 = time.time()
print('Iteration over dictionary takes: ' + str(t1-t0))

Thank you!

Answer 1

The common guidance is don't iterate, use functions on all rows columns, or grouped rows/columns. Below, in the third code block shows how to iterate over the numpy array whhich is the .values attribute. The results are:

Pivot Construction takes: 0.012315988540649414

Dataframe iteration takes: 0.32346272468566895

Iteration over values takes: 0.004369020462036133

Cast to dictionary takes: 0.023524761199951172

Iteration over dictionary takes: 0.0010480880737304688

import pandas as pd
from io import StringIO 

# Test data
import pandas as pd
import random as rd
import time

#Given a dataframe from api (model as random numbers)
df_columns = ['A','B','C','D','F','G','H','I']
dict_origin = {col:[rd.randint(0,10) for x in range(0,1000)] for col in df_columns}
dict_origin = pd.DataFrame(dict_origin)

#Transform to pivot table
t0 = time.time()
df_pivot = pd.pivot_table(dict_origin,values=df_columns[-3:],index=df_columns[:-3])
t1 = time.time()
print('Pivot Construction takes: ' + str(t1-t0))

#Iterate over all elements in pivot table
t0 = time.time()
for column in df_pivot.columns:
    for row in df_pivot[column].index:
        test = df_pivot[column].loc[row]
t1 = time.time()
print('Dataframe iteration takes: ' + str(t1-t0))

#Iterate over all values in pivot table
t0 = time.time()
v = df_pivot.values
for row in range(df_pivot.shape[0]):
    for column in range(df_pivot.shape[1]):
        test = v[row, column]
t1 = time.time()
print('Iteration over values takes: ' + str(t1-t0))


#Iteration over dataframe too slow. Cast to dictionary (bottleneck)
t0 = time.time()
df_pivot = df_pivot.to_dict('index')
t1 = time.time()
print('Cast to dictionary takes: ' + str(t1-t0))

#Iteration over dictionary is much faster
t0 = time.time()
for row in df_pivot.keys():
    for column in df_pivot[row]:
        test = df_pivot[row][column]
t1 = time.time()
print('Iteration over dictionary takes: ' + str(t1-t0))