[Python] In Python, is there a faster way to append tabular data to a data file than with...

I am trying to write a script that appends new rows of a table to an existing file of tabular data, so that not all progress is lost if an error is encountered. Here are five different ways to do this in Python:

• Write the data to a CSV file using writerows of the csv module.
• Write the data to a CSV file using polars.
• Structure the data as a pandas DataFrame and pickle it.
• Structure the data as a polars DataFrame and pickle it.
• Structure the data as a pandas DataFrame and save it as a Parquet file using fastparquet.

The following code measures the runtime of each of these options, excluding the time required to build the data, header, and any single-row DataFrames.

print('----------------------------------------------------------------------')
print('csv')

import numpy as np
from time import perf_counter as pc
import csv

file_name = 'data_file_0.csv'
header = list('ABCDE')
n_rows = 10**3
data = np.random.rand(n_rows, 5)

pc0 = pc()
with open(file_name, mode='w', newline='') as f:
csvwriter = csv.writer(f)
csvwriter.writerow(header)
for row in data:
csvwriter.writerow(row)
print(f'To write data to data file: {pc()-pc0} sec')

print('----------------------------------------------------------------------')
print('polars')

import polars as pl

file_name = 'data_file_1.csv'
df_header = pl.DataFrame(schema=header)
df = pl.DataFrame(data, schema=header)

pc0 = pc()
with open(file_name, mode='w', encoding='utf8') as f:
df_header.write_csv(f)
for k in range(n_rows):
df[k].write_csv(f, include_header=False)
print(f'To write data to data file: {pc()-pc0} sec')

print('----------------------------------------------------------------------')
print('pandas, pickle')

import pandas as pd
import pickle as pkl

file_name = 'data_file_2.pkl'
df = pd.DataFrame(data, columns=header)

pc0 = pc()
df_new = df.iloc[[0]]
df_new.to_pickle(file_name)
for k in range(1, n_rows):
df_new = pd.concat([df_new, df.iloc[[k]]], ignore_index=True)
df_new.to_pickle(file_name)
print(f'To write data to data file: {pc()-pc0} sec')

# df = pd.read_pickle(file_name)
# print(df)

print('----------------------------------------------------------------------')
print('polars, pickle')

file_name = 'data_file_3.pkl'
df = pl.DataFrame(data, schema=header)

pc0 = pc()
df_new = df[0]
df_ser = pkl.dumps(df_new)
with open(file_name, mode='wb') as f:
f.write(df_ser)
for k in range(1, n_rows):
df_new = df_new.vstack(df[k])
df_ser = pkl.dumps(df_new)
with open(file_name, mode='wb') as f:
f.write(df_ser)
print(f'To write data to data file: {pc()-pc0} sec')

# with open(file_name, mode='rb') as f:
# df_ser = f.read()
# df = pkl.loads(df_ser)
# print(df)

print('----------------------------------------------------------------------')
print('pandas, fastparquet')

import fastparquet as fp

file_name = 'data_file_4.parquet'
df = pd.DataFrame(data, columns=header)

pc0 = pc()
fp.write(file_name, df.iloc[[0]])
for k in range(1, n_rows):
fp.write(file_name, df.iloc[[k]], append=True)
print(f'To write data to data file: {pc()-pc0} sec')
print('----------------------------------------------------------------------')


On my machine, running this script yields the following times.

----------------------------------------------------------------------
csv
To write data to data file: 0.00842249999550404 sec
----------------------------------------------------------------------
polars
To write data to data file: 0.115502300002845 sec
----------------------------------------------------------------------
pandas, pickle
To write data to data file: 0.8650821000046562 sec
----------------------------------------------------------------------
polars, pickle
To write data to data file: 1.6329077999980655 sec
----------------------------------------------------------------------
pandas, fastparquet
To write data to data file: 25.658514900002046 sec
----------------------------------------------------------------------


So it appears that writerows is much faster than the other four alternatives for this specific task and test. But are there faster ways (in Python) of appending tabular data to an existing tabular file?

Continue reading...