Pytables vs. CSV for files that are not very large

csv, pytables, python

Solution

Have you considered Numpy arrays?

PyTables are wonderful when your data is too large to fit in memory, but a 200x2000 matrix of 8 byte floats only requires about 3MB of memory. So I think PyTables may be overkill.

You can save numpy arrays to files using `np.savetxt` or `np.savez` (for compression), and can read them from files with `np.loadtxt` or `np.load`.

If you have many such arrays to store on disk, then I'd suggest using a database instead of numpy `.npz` files. By the way, to store a 200x2000 matrix in a database, you only need 3 table columns: row, col, value:

import sqlite3
import numpy as np

db = sqlite3.connect(':memory:')
cursor = db.cursor()
cursor.execute('''CREATE TABLE foo
                  (row INTEGER,
                   col INTEGER,
                   value FLOAT,
                   PRIMARY KEY (row,col))''')
ROWS=4
COLUMNS=6
matrix = np.random.random((ROWS,COLUMNS))
print(matrix)
# [[ 0.87050721  0.22395398  0.19473001  0.14597821  0.02363803  0.20299432]
#  [ 0.11744885  0.61332597  0.19860043  0.91995295  0.84857095  0.53863863]
#  [ 0.80123759  0.52689885  0.05861043  0.71784406  0.20222138  0.63094807]
#  [ 0.01309897  0.45391578  0.04950273  0.93040381  0.41150517  0.66263562]]

# Store matrix in table foo
cursor.executemany('INSERT INTO foo(row, col, value) VALUES (?,?,?) ',
                   ((r,c,value) for r,row in enumerate(matrix) 
                                for c,value in enumerate(row)))

# Retrieve matrix from table foo
cursor.execute('SELECT value FROM foo ORDER BY row,col')
data=zip(*cursor.fetchall())[0]
matrix2 = np.fromiter(data,dtype=np.float).reshape((ROWS,COLUMNS))
print(matrix2)
# [[ 0.87050721  0.22395398  0.19473001  0.14597821  0.02363803  0.20299432]
#  [ 0.11744885  0.61332597  0.19860043  0.91995295  0.84857095  0.53863863]
#  [ 0.80123759  0.52689885  0.05861043  0.71784406  0.20222138  0.63094807]
#  [ 0.01309897  0.45391578  0.04950273  0.93040381  0.41150517  0.66263562]]

If you have many such 200x2000 matrices, you just need one more table column to specify which matrix.

Problem

I recently came across Pytables and find it to be very cool. It is clear that they are superior to a csv format for very large data sets. I am running some simulations using python. The output is not so large, say 200 columns and 2000 rows. If someone has experience with both, can you suggest which format would be more convenient in the long run for such data sets that are not very large. Pytables has data manipulation capabilities and browsing of the data with Vitables, but the browser does not have as much functionality as, say Excel, which can be used for CSV. Similarly, do you find one better than the other for importing and exporting data, if working mainly in python? Is one more convenient in terms of file organization? Any comments on issues such as these would be helpful. Thanks.

Original source

Related problems