Follow and star to learn new Python skills every day

Due to changes in the public account’s push rules, please click “View” and add “Star” to get exciting technical shares at the first time

Source from the internet, please delete if infringing

As a Python user, I use Excel files to load/store data because business personnel prefer to share data in Excel or CSV format. Unfortunately, Python is particularly slow when handling Excel files.

In this article, I will share five methods for loading data in Python. In the end, we will achieve a speedup of three orders of magnitude. It will be lightning fast.

Experimental Setup

Assuming we want to load 10 Excel files with 20,000 rows and 25 columns each (approximately 70MB in total). This is a typical case where you want to load transaction data from ERP (SAP) into Python for some analysis.

Let’s populate this virtual data and import the necessary libraries (we will discuss pickle and joblib later in this article).

import pandas as pd
import numpy as np
from joblib import Parallel, delayed
import time
for file_number in range(10):
    values = np.random.uniform(size=(20000,25))
    pd.DataFrame(values).to_csv(f"Dummy {file_number}.csv")
    pd.DataFrame(values).to_excel(f"Dummy {file_number}.xlsx")
    pd.DataFrame(values).to_pickle(f"Dummy {file_number}.pickle")

import pandas as pd
import numpy as np
from joblib import Parallel, delayed
import time
for file_number in range(10):
    values = np.random.uniform(size=(20000,25))
    pd.DataFrame(values).to_csv(f"Dummy {file_number}.csv")
    pd.DataFrame(values).to_excel(f"Dummy {file_number}.xlsx")
    pd.DataFrame(values).to_pickle(f"Dummy {file_number}.pickle")

5 Methods to Load Data in Python

1. Loading Excel Files in Python

Let’s start with a simple method to load these files. We will create the first Pandas DataFrame and then append each Excel file to it.

start = time.time()
df = pd.read_excel("Dummy 0.xlsx")
for file_number in range(1,10):
    df.append(pd.read_excel(f"Dummy {file_number}.xlsx"))
end = time.time()
print("Excel:", end - start)  # Excel: 53.4

start = time.time()
df = pd.read_excel("Dummy 0.xlsx")
for file_number in range(1,10):
    df.append(pd.read_excel(f"Dummy {file_number}.xlsx"))
end = time.time()
print("Excel:", end - start)

It takes about 50 seconds to run, which is slow.

2. Loading CSV in Python

Now, let’s assume we save these files from ERP/system/SAP as .csv (instead of .xlsx).

start = time.time()
df = pd.read_csv("Dummy 0.csv")
for file_number in range(1,10):
    df.append(pd.read_csv(f"Dummy {file_number}.csv"))
end = time.time()
print("CSV:", end - start)  # CSV: 0.632

start = time.time()
df = pd.read_csv("Dummy 0.csv")
for file_number in range(1,10):
    df.append(pd.read_csv(f"Dummy {file_number}.csv"))
end = time.time()
print("CSV:", end - start)

We can now load these files in 0.63 seconds, which is almost 10 times faster!

Loading CSV files in Python is 100 times faster than Excel files, use CSV.

Disadvantage:CSV files are almost always larger than .xlsx files. In this example, the .csv file is 9.5MB while the .xlsx is 6.4MB.

3. Creating Smarter Pandas DataFrames

We can speed up our process by changing the way we create Pandas DataFrames.

Instead of appending each file to the existing DataFrame,

1. We load each DataFrame independently into a list.

2. Then we concatenate the entire list into a single DataFrame.

start = time.time()
df = []
for file_number in range(10):
    temp = pd.read_csv(f"Dummy {file_number}.csv")
    df.append(temp)
df = pd.concat(df, ignore_index=True)
end = time.time()
print("CSV2:", end - start)  # CSV2: 0.619

start = time.time()
df = []
for file_number in range(10):
    temp = pd.read_csv(f"Dummy {file_number}.csv")
    df.append(temp)
df = pd.concat(df, ignore_index=True)
end = time.time()
print("CSV2:", end - start)

We reduced the time by a few percentage points. In my experience, this trick becomes useful when dealing with larger DataFrames (df >> 100MB).

4. Parallelizing CSV Import with Joblib

We want to load 10 files in Python. Instead of loading each file one by one, why not load them in parallel?

We can easily do this using joblib.

start = time.time()
def loop(file_number):
    return pd.read_csv(f"Dummy {file_number}.csv")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("CSV//:", end - start)  # CSV//: 0.386

start = time.time()
def loop(file_number):
    return pd.read_csv(f"Dummy {file_number}.csv")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("CSV//:", end - start)

This is almost twice as fast as the single-core version. However, as a general rule, do not expect to speed up your process by 8 times using 8 cores (here, I achieved a x2 speedup using 8 cores on a Mac Air with the new M1 chip).

Joblib

If you are not familiar with joblib, it is a simple Python library that allows you to run a function in parallel. In practice, joblib acts like list comprehension, except that each iteration is executed by different threads. Here’s an example.

def loop(file_number):
    return pd.read_csv(f"Dummy {file_number}.csv")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
# equivalent to
df = [loop(file_number) for file_number in range(10)]

def loop(file_number):
    return pd.read_csv(f"Dummy {file_number}.csv")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
# equivalent to
df = [loop(file_number) for file_number in range(10)]

5. Storing Pickle Files Faster

You can speed up the process by storing data in pickle files (a specific format used by Python) instead of .csv files.

Disadvantage:You will not be able to manually open a pickle file and view its contents.

start = time.time()
def loop(file_number):
    return pd.read_pickle(f"Dummy {file_number}.pickle")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("Pickle//:", end - start)  # Pickle//: 0.072

start = time.time()
def loop(file_number):
    return pd.read_pickle(f"Dummy {file_number}.pickle")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("Pickle//:", end - start)

We just reduced the runtime by 80%!

Generally, using pickle files is much faster than using CSV files. However, on the other hand, pickle files usually take up more space on your drive (not in this specific example).

In fact, you will not be able to extract data directly from a pickle file from the system.

I recommend using pickles in the following two cases:

You want to save data within a Python process (and you do not intend to open it in Excel) for later use in another process. Save your DataFrame as a pickle instead of .csv.

You need to reload the same file multiple times. The first time you open the file, save it as a pickle so that next time you can load the pickle version directly.

Example: Suppose you are using monthly trading data (loading a new month’s data each month). You can save all historical data as .pickle, and each time you receive a new file, you can load it as .csv once and then save it as .pickle for next time.

Bonus: Parallel Loading of Excel Files

Suppose you received Excel files and have no choice but to load them as they are. You can also use joblib to parallelize it. Compared to the pickle code above, we only need to update the loop function.

start = time.time()
def loop(file_number):
    return pd.read_excel(f"Dummy {file_number}.xlsx")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("Excel//:", end - start)  # Excel//: 13.45

start = time.time()
def loop(file_number):
    return pd.read_excel(f"Dummy {file_number}.xlsx")
df = Parallel(n_jobs=-1, verbose=10)(delayed(loop)(file_number) for file_number in range(10))
df = pd.concat(df, ignore_index=True)
end = time.time()
print("Excel//:", end - start)

We can reduce the loading time by 70% (from 50 seconds to 13 seconds).

You can also use this loop to dynamically create pickle files. Therefore, the next time you load these files, you will achieve lightning-fast loading speeds.

def loop(file_number):
    temp = pd.read_excel(f"Dummy {file_number}.xlsx")
    temp.to_pickle(f"Dummy {file_number}.pickle")
    return temp

Thank you for reading, I hope this is helpful.

Click to follow the public account below to get free Python public courses and hundreds of GB of learning materials organized by experts, including but not limited to Python e-books, tutorials, project orders, source code, etc.


▲ Click to follow - Get for free

Recommended Reading
Use these little-known (and mostly overlooked by developers) tips to speed up your Python code by 10 times
15 top Python libraries you must try!
12 killer Python code snippets for daily programming
How to read and write PDF files using Python?


Click to read the original text