Comparing the Speed and Efficiency of Different File Formats for Storing and Reading Data with Pandas
If you work with data in Python, you know that there are many different file formats that you can use to store and read data. But which file format is the best for your specific needs? In this blog post, we will compare the speed and efficiency of five common file formats for storing and reading data with pandas, a popular data manipulation library in Python: CSV, feather, pickle, HDF5, and parquet. We will measure the time it takes to write and read data with each file format, and discuss the relative strengths and weaknesses of each option. By the end of this post, you should have a good understanding of which file format is the best fit for your data and use case.
!pip install tables
Collecting tables
Downloading tables-3.8.0-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (6.5 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 6.5/6.5 MB 131.3 MB/s eta 0:00:00
Collecting numexpr>=2.6.2
Downloading numexpr-2.8.4-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (380 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 380.7/380.7 KB 62.6 MB/s eta 0:00:00
Collecting cython>=0.29.21
Downloading Cython-0.29.33-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.manylinux_2_24_x86_64.whl (2.0 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 2.0/2.0 MB 141.3 MB/s eta 0:00:00
Collecting py-cpuinfo
Downloading py_cpuinfo-9.0.0-py3-none-any.whl (22 kB)
Requirement already satisfied: packaging in /shared-libs/python3.9/py-core/lib/python3.9/site-packages (from tables) (21.3)
Collecting blosc2~=2.0.0
Downloading blosc2-2.0.0-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.9 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 3.9/3.9 MB 143.3 MB/s eta 0:00:00
Requirement already satisfied: numpy>=1.19.0 in /shared-libs/python3.9/py/lib/python3.9/site-packages (from tables) (1.23.4)
Collecting msgpack
Downloading msgpack-1.0.4-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (322 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 322.4/322.4 KB 69.3 MB/s eta 0:00:00
Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /shared-libs/python3.9/py-core/lib/python3.9/site-packages (from packaging->tables) (3.0.9)
Installing collected packages: py-cpuinfo, msgpack, numexpr, cython, blosc2, tables
Successfully installed blosc2-2.0.0 cython-0.29.33 msgpack-1.0.4 numexpr-2.8.4 py-cpuinfo-9.0.0 tables-3.8.0
WARNING: You are using pip version 22.0.4; however, version 22.3.1 is available.
You should consider upgrading via the '/root/venv/bin/python -m pip install --upgrade pip' command.
import time
import pandas as pd
# Generate a sample dataframe
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})
# Write the dataframe to various file formats
start = time.time()
df.to_csv('df.csv')
csv_time = time.time() - start
start = time.time()
df.to_feather('df.feather')
feather_time = time.time() - start
start = time.time()
df.to_pickle('df.pickle')
pickle_time = time.time() - start
start = time.time()
df.to_hdf('df.h5', 'df')
hdf5_time = time.time() - start
start = time.time()
df.to_parquet('df.parquet')
parquet_time = time.time() - start
# Read the data from various file formats
start = time.time()
pd.read_csv('df.csv')
csv_read_time = time.time() - start
start = time.time()
pd.read_feather('df.feather')
feather_read_time = time.time() - start
start = time.time()
pd.read_pickle('df.pickle')
pickle_read_time = time.time() - start
start = time.time()
pd.read_hdf('df.h5', 'df')
hdf5_read_time = time.time() - start
start = time.time()
pd.read_parquet('df.parquet')
parquet_read_time = time.time() - start
# Print the results
print(f'Time to write CSV: {csv_time:.5f} seconds')
print(f'Time to write feather: {feather_time:.5f} seconds')
print(f'Time to write pickle: {pickle_time:.5f} seconds')
print(f'Time to write HDF5: {hdf5_time:.5f} seconds')
print(f'Time to write parquet: {parquet_time:.5f} seconds')
print(f'Time to read CSV: {csv_read_time:.5f} seconds')
print(f'Time to read feather: {feather_read_time:.5f} seconds')
print(f'Time to read pickle: {pickle_read_time:.5f} seconds')
print(f'Time to read HDF5: {hdf5_read_time:.5f} seconds')
print(f'Time to read parquet: {parquet_read_time:.5f} seconds')
Time to write CSV: 0.00554 seconds
Time to write feather: 0.16897 seconds
Time to write pickle: 0.00186 seconds
Time to write HDF5: 0.03703 seconds
Time to write parquet: 0.09065 seconds
Time to read CSV: 0.00331 seconds
Time to read feather: 0.00294 seconds
Time to read pickle: 0.00154 seconds
Time to read HDF5: 0.00417 seconds
Time to read parquet: 0.05417 seconds
with conclusion
import time
import pandas as pd
# Generate a sample dataframe
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})
# Write the dataframe to various file formats
start = time.time()
df.to_csv('df.csv')
csv_time = time.time() - start
start = time.time()
df.to_feather('df.feather')
feather_time = time.time() - start
start = time.time()
df.to_pickle('df.pickle')
pickle_time = time.time() - start
start = time.time()
df.to_hdf('df.h5', 'df')
hdf5_time = time.time() - start
start = time.time()
df.to_parquet('df.parquet')
parquet_time = time.time() - start
# Read the data from various file formats
start = time.time()
pd.read_csv('df.csv')
csv_read_time = time.time() - start
start = time.time()
pd.read_feather('df.feather')
feather_read_time = time.time() - start
start = time.time()
pd.read_pickle('df.pickle')
pickle_read_time = time.time() - start
start = time.time()
pd.read_hdf('df.h5', 'df')
hdf5_read_time = time.time() - start
start = time.time()
pd.read_parquet('df.parquet')
parquet_read_time = time.time() - start
# Print the results
print('Writing:')
print(f' Time to write CSV: {csv_time:.5f} seconds')
print(f' Time to write feather: {feather_time:.5f} seconds')
print(f' Time to write pickle: {pickle_time:.5f} seconds')
print(f' Time to write HDF5: {hdf5_time:.5f} seconds')
print(f' Time to write parquet: {parquet_time:.5f} seconds')
# Determine the fastest and slowest file formats for writing
writing_times = {
'CSV': csv_time,
'feather': feather_time,
'pickle': pickle_time,
'HDF5': hdf5_time,
'parquet': parquet_time
}
fastest_format_writing = min(writing_times, key=writing_times.get)
slowest_format_writing = max(writing_times, key=writing_times.get)
print(f'The fastest file format for writing is {fastest_format_writing}')
print(f'The slowest file format for writing is {slowest_format_writing}')
# Determine the fastest and slowest file formats for reading
reading_times = {
'CSV': csv_read_time,
'feather': feather_read_time,
'pickle': pickle_read_time,
'HDF5': hdf5_read_time,
'parquet': parquet_read_time
}
fastest_format_reading = min(reading_times, key=reading_times.get)
slowest_format_reading = max(reading_times, key=reading_times.get)
print(f'The fastest file format for reading is {fastest_format_reading}')
print(f'The slowest file format for reading is {slowest_format_reading}')
Writing:
Time to write CSV: 0.00237 seconds
Time to write feather: 0.00260 seconds
Time to write pickle: 0.00199 seconds
Time to write HDF5: 0.00703 seconds
Time to write parquet: 0.00284 seconds
The fastest file format for writing is pickle
The slowest file format for writing is HDF5
The fastest file format for reading is pickle
The slowest file format for reading is HDF5
import matplotlib.pyplot as plt
# Get the writing times as a list
writing_times_list = list(writing_times.values())
# Get the file format names as a list
format_names = list(writing_times.keys())
# Create the bar chart
plt.bar(format_names, writing_times_list)
# Add labels and title
plt.xlabel('File Format')
plt.ylabel('Writing Time (seconds)')
plt.title('Writing Times for Different File Formats')
# Show the plot
plt.show()
import matplotlib.pyplot as plt
# Get the reading times as a list
reading_times_list = list(reading_times.values())
# Get the file format names as a list
format_names = list(reading_times.keys())
# Create the bar chart
plt.bar(format_names, reading_times_list)
# Add labels and title
plt.xlabel('File Format')
plt.ylabel('Reading Time (seconds)')
plt.title('Reading Times for Different File Formats')
# Show the plot
plt.show()