Menu
I process a lot of text/data that I exchange between Python, R, and sometimes Matlab.
My go-to is the flat text file, but also use SQLite occasionally to store the data and access from each program (not Matlab yet though). I don't use GROUPBY, AVG, etc. in SQL as much as I do these operations in R, so I don't necessarily require the database operations.
For such applications that requires exchanging data among programs to make use of available libraries in each language, is there a good rule of thumb on which data exchange format/method to use (even XML or NetCDF or HDF5)?
I know between Python -> R there is rpy or rpy2 but I was wondering about this question in a more general sense - I use many computers which all don't have rpy2 and also use a few other pieces of scientific analysis software that require access to the data at various times (the stages of processing and analysis are also separated).
659
A database in SQLite is a single disk file¹. Furthermore, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture.
The primary advantage of a database vs a text file or an single data file (aka a flat file) is that a database allows you to capture relationships across single data files.
In fact, SQLite has done benchmarking to show they are 35% faster than file systems and reading and writing this type of data through fread() or fwrite()1.
One of the main drawbacks of the SQLite system is its lack of multi-user capabilities which can be found in full-fledged RDBMS systems like MySQL and PostgreSQL. This translates to a lack of granular access control, a friendly user management system, and security capabilities beyond encrypting the database file itself.
If all the languages support SQLite - use it. The power of SQL might not be useful to you right now, but it probably will be at some point, and it saves you having to rewrite things later when you decide you want to be able to query your data in more complicated ways.
SQLite will also probably be substantially faster if you only want to access certain bits of data in your datastore - since doing that with a flat-text file is challenging without reading the whole file in (though it's not impossible).
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
