Why does specifying the index column in pandas significantly increases the read time of a csv?

user_8089695 0
Viewed 97

I am seeing a significantly increased read time for a CSV by pandas when I specify the index_col. I do not understand the reason behind it. Can you help me understand why that is happening and if that is actually the expected behaviour? Below is the code I am using:

import pandas as pd
#save the CSV to be used
pd.DataFrame({'id':np.arange(100000000),'b':np.random.choice(['a','b','c','d'],size=(100000000,),p=[0.25,0.25,0.25,0.25])}).to_csv('df_sp.csv',index=None)
dfpd = pd.read_csv('df_sp.csv')
#read time - 10.3 seconds
dfpd = pd.read_csv('df_sp.csv',index_col='id')
#read time - 1 minute 38.6 seconds

In fact, I am seeing significant improvement if I read the dataset without specifying index_col and then set the index by dfpd = dfpd.set_index('id'). This takes just 1.6 more seconds. Why does pandas not default to always reading the dataframe with index_col as a column and then setting it as the index internally with set_index(index_col) when index_col is specified?

I'm on linux

Adding performance images in 3 slightly different runtime configurations:

Ubuntu 20.04.3 LTS

config1

Ubuntu 20.04.3 LTS

config2

Ubuntu 18.04.5 LTS

config3

pandas csv python
1 Answers

I can't reproduce these differences. Using %timeit in Jupyter, I get:

import pandas as pd
#save the CSV to be used
pd.DataFrame({'id':np.arange(100000000),'b':np.random.choice(['a','b','c','d'],size=(100000000,),p=[0.25,0.25,0.25,0.25])}).to_csv('df_sp.csv',index=None)

%timeit dfpd1 = pd.read_csv('df_sp.csv')
%timeit dfpd2 = pd.read_csv('df_sp.csv',index_col='id')

Results:

10.9 s ± 74.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
11.6 s ± 10.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Edit: Python and pandas versions

>>> sys.version
'3.9.7 (default, Sep 16 2021, 08:50:36) \n[Clang 10.0.0 ]'
>>> pd.__version__
'1.3.3'

Running on MacOS 11.6 (amd64)

user_1894180
Related