<p>Just to conclude on the solution to this ancestral question. HDF5 is very useful, but there are better solutions for this specific question if it is focused on pandas dataframes. If you've lots of data the pythonic way to do this is pandas and therefore storing as a pandas dataframe makes sense.</p>
<p>For long term storage, which was the original question, the storage of choice for pandas is <code>parquet</code>. Described <a href="https://pandas.pydata.org/pandas-docs/version/0.21/io.html#io-parquet" rel="nofollow noreferrer">here</a></p>
<blockquote>
<p>Apache Parquet provides a partitioned binary columnar serialization
for data frames. It is designed to make reading and writing data
frames efficient, and to make sharing data across data analysis
languages easy. Parquet can use a variety of compression techniques to
shrink the file size as much as possible while still maintaining good
read performance.</p>
</blockquote>
<p>In the following example, two methods <code>parquet</code> methods are used for a pandas dataframe proteinDF which is first written and then imported.</p>
<pre><code>from fastparquet import write
write('outfile.parq', proteinDF)
</code></pre>
<p>and</p>
<pre><code>import pandas as pd
pd.read_parquet('proteindata.parquet', engine='fastparquet')
</code></pre>
<p>The above approach appears to have received most current developmental attention. However,</p>
<pre><code>import pyarrow as pa
import pyarrow.parquet as pq
arrow_table = pa.Table.from_pandas(proteinDF)
pq.write_table(arrow_table, 'myoutpath', use_dictionary=False,
                   compression='SNAPPY')
</code></pre>
<p>and</p>
<pre><code>import pandas as pd
pd.read_parquet('example_pa.parquet', engine='pyarrow')
</code></pre>
<p>The above approach is compatible with <code>numba</code>. 'Multithreading' is further described <a href="https://wesmckinney.com/blog/python-parquet-multithreading/" rel="nofollow noreferrer">here</a>.</p>
<p>The differences between the methods appear slight. Parallelisation is a big deal and depends on the problem.</p>
<p>A general discussion on the methods is <a href="https://blog.datasyndrome.com/python-and-parquet-performance-e71da65269ce" rel="nofollow noreferrer">here</a></p>
<p><strong>HDF5 vs parquet</strong>
The backdrop for <code>parquet</code> is the method is used for batch I/O not record by record writing to disk and that is this question and for that is pretty fast (at a guess bit better than <code>pickle</code>, but a lot more compression).  <code>HDF5</code> is perfect for rapid record by record writing and that is what it is designed for, so 'benchmarks' comparing I/O speed are not realistic unless they compare batch vs. record by record timings, where <code>HDF5</code> excels. <code>parquet</code> does achieve significantly better data compression and is considered a long term storage solution. In my hands <code>HDF5</code> for fasta writing results in around half the size of the flat file (not much).</p>

CC BY-SA 4.0
2022-06-24T23:36:53.427Z
2022-06-26T13:32:16.943Z
2022-06-26T13:32:16.943Z
M__
<p>Just to conclude, @user172818 and @AlexReynolds are collectively saying either learn SQL or stick with CSV. </p>

<hr>

<p>Update,
I can provide an answer. It's HDF5 with pandas dataframes (Python).</p>

<p>Simply</p>

<pre><code>df.to_hdf('path/file.h5', 'key', format='table')
</code></pre>

<p>This isn't SQL but it works:</p>

<p>What it gives,</p>

<ul>
<li>it's fast, that's it's purpose (not my main reason though)</li>
<li>it perfectly preserves all pandas dataframe information, this is  important</li>
<li>The 'key' ( object) allows you to store multiple dataframes into the same file, you just provide different key names</li>
</ul>

<p>The last point is very useful, because you can pull out a dataframe of choice and the SQL commands are mimicked within pandas</p>

<p>I ran into trouble using CSV to store/recover complex pandas dataframe formats. </p>

<p>So it's good enough and much faster than SQL. I could have problems with format changes in time (unlike SQL), but for the here and now it works well. </p>

<hr>

<p>Just to add a caveat, for dictionaries the json dump works well, but it is just shunting dictionaries around, so isn't relevant.</p>

CC BY-SA 4.0
2019-06-04T02:37:52.207Z
Hashes are memory intensive. Perhaps keep an eye on your memory usage, if you go this route. This is why indexing and sorting approaches are used.
Alex Reynolds
CC BY-SA 4.0
2019-06-04T13:10:44.897Z
Thanks, I appreciate critical feedback. In defence of Perl v5.20 now has postfix dereferencing ->@* syntax, which a step forward for hashes. The data loads involved are less than a single higher eukaryotic genome. Obviously not doing dataframe style calculations in a hash ... its doable done (higher order programming), but possibly surpassed.
M__
CC BY-SA 4.0
2019-06-03T14:11:16.600Z
2019-09-28T22:20:52.947Z
2019-09-28T22:20:52.947Z
M__
<p>I'm looking at better database/storage solutions for NCBI virus data, with all attributes particularly year and country of isolation, together with structural data, possible antibody data, T-cell data and bioinformatics data such as %GC etc... The amount of data is comparatively small, with a maximum size of <code>3 x ([6000 &lt; no. viruses &lt; 20000] for 10 genes)</code> and each entry will contain nucleotide, amino acid and one or more pieces of alignment data (i.e. with indels). </p>

<p>The data will be used for numerous different calculations from structural biology, phylogenetics, and deep learning. Some of the calculations will require parallel processing. A typical calculation would be to identify a clade/similarity/difference then screen the database to look at the isolates information.</p>

<p>Previously I've used lots of flat files which are loaded at initiation and are held within a data pipeline either as an object (Python) or especially a multi-dimensional hash (Perl, preferred).</p>

<p>The two solutions that I think look suitable are: </p>

<ol>
<li><a href="https://biosql.org" rel="noreferrer">BioSQL</a> - The official Genbank solution, however my SQL commands are very basic and the documentation appears minimal (for BioSQL)</li>
<li><a href="https://www.hdfgroup.org/solutions/hdf5/" rel="noreferrer">HDF5</a> (hierarchical data format) - this looks good because it uses complex hashes (dictionaries), and allows layering of multiple information onto a single Genbank sequence code (key). It has strong documentation. However, I understand this isn't a "database" (long term storage), but thats how I intend to use it.</li>
</ol>

<p><strong>JSON/Pickling</strong> I had a look at JSON, which to me looks okay, because I'm fond of complex hashes / dictionaries, but it appears to be more of a Web solution. Finally, "pickling" (Python) is cool, very cool, but I understand this not a database solution, because it risk compatibility issues, e.g. with future Python releases. </p>

<p>I am interested in knowing what archival systems board members are using, and a critique whether my assessment is ok for moving forward.</p>

CC BY-SA 4.0
2019-05-30T23:04:29.367Z
If you are querying repeatedly and have multiple features on which you are applying search criteria, use a proper database with indexed features/columns/fields.
Alex Reynolds
<python><perl>
HDF5 and BioSQL solutions
# HDF5 and BioSQL solutions

(Asked by: username_0 on 2019-05-30)

I'm looking at better database/storage solutions for NCBI virus data, with all attributes particularly year and country of isolation, together with structural data, possible antibody data, T-cell data and bioinformatics data such as %GC etc... The amount of data is comparatively small, with a maximum size of `3 x ([6000 < no. viruses < 20000] for 10 genes)` and each entry will contain nucleotide, amino acid and one or more pieces of alignment data (i.e. with indels).

The data will be used for numerous different calculations from structural biology, phylogenetics, and deep learning. Some of the calculations will require parallel processing. A typical calculation would be to identify a clade/similarity/difference then screen the database to look at the isolates information.

Previously I've used lots of flat files which are loaded at initiation and are held within a data pipeline either as an object (Python) or especially a multi-dimensional hash (Perl, preferred).

The two solutions that I think look suitable are:

1. [BioSQL](https://biosql.org) - The official Genbank solution, however my SQL commands are very basic and the documentation appears minimal (for BioSQL)
2. [HDF5](https://www.hdfgroup.org/solutions/hdf5/) (hierarchical data format) - this looks good because it uses complex hashes (dictionaries), and allows layering of multiple information onto a single Genbank sequence code (key). It has strong documentation. However, I understand this isn't a "database" (long term storage), but thats how I intend to use it.

**JSON/Pickling** I had a look at JSON, which to me looks okay, because I'm fond of complex hashes / dictionaries, but it appears to be more of a Web solution. Finally, "pickling" (Python) is cool, very cool, but I understand this not a database solution, because it risk compatibility issues, e.g. with future Python releases.

I am interested in knowing what archival systems board members are using, and a critique whether my assessment is ok for moving forward.

**Tags:** python, perl
> **Comments:**
> **username_1**: If you are querying repeatedly and have multiple features on which you are applying search criteria, use a proper database with indexed features/columns/fields.  

---

## 2 answers

### Answer by username_0 on 2022-06-24. Score: 1 | Accepted answer

Just to conclude on the solution to this ancestral question. HDF5 is very useful, but there are better solutions for this specific question if it is focused on pandas dataframes. If you've lots of data the pythonic way to do this is pandas and therefore storing as a pandas dataframe makes sense.
For long term storage, which was the original question, the storage of choice for pandas is parquet. Described here

Apache Parquet provides a partitioned binary columnar serialization
for data frames. It is designed to make reading and writing data
frames efficient, and to make sharing data across data analysis
languages easy. Parquet can use a variety of compression techniques to
shrink the file size as much as possible while still maintaining good
read performance.

In the following example, two methods parquet methods are used for a pandas dataframe proteinDF which is first written and then imported.
from fastparquet import write
write('outfile.parq', proteinDF)

and
import pandas as pd
pd.read_parquet('proteindata.parquet', engine='fastparquet')

The above approach appears to have received most current developmental attention. However,
import pyarrow as pa
import pyarrow.parquet as pq
arrow_table = pa.Table.from_pandas(proteinDF)
pq.write_table(arrow_table, 'myoutpath', use_dictionary=False,
                   compression='SNAPPY')

and
import pandas as pd
pd.read_parquet('example_pa.parquet', engine='pyarrow')

The above approach is compatible with numba. 'Multithreading' is further described here.
The differences between the methods appear slight. Parallelisation is a big deal and depends on the problem.
A general discussion on the methods is here
HDF5 vs parquet
The backdrop for parquet is the method is used for batch I/O not record by record writing to disk and that is this question and for that is pretty fast (at a guess bit better than pickle, but a lot more compression).  HDF5 is perfect for rapid record by record writing and that is what it is designed for, so 'benchmarks' comparing I/O speed are not realistic unless they compare batch vs. record by record timings, where HDF5 excels. parquet does achieve significantly better data compression and is considered a long term storage solution. In my hands HDF5 for fasta writing results in around half the size of the flat file (not much).


---

### Answer by username_0 on 2019-06-03. Score: 1

Just to conclude, @user172818 and @AlexReynolds are collectively saying either learn SQL or stick with CSV. 

Update,
I can provide an answer. It's HDF5 with pandas dataframes (Python).
Simply
df.to_hdf('path/file.h5', 'key', format='table')

This isn't SQL but it works:
What it gives,

it's fast, that's it's purpose (not my main reason though)
it perfectly preserves all pandas dataframe information, this is  important
The 'key' ( object) allows you to store multiple dataframes into the same file, you just provide different key names

The last point is very useful, because you can pull out a dataframe of choice and the SQL commands are mimicked within pandas
I ran into trouble using CSV to store/recover complex pandas dataframe formats. 
So it's good enough and much faster than SQL. I could have problems with format changes in time (unlike SQL), but for the here and now it works well. 

Just to add a caveat, for dictionaries the json dump works well, but it is just shunting dictionaries around, so isn't relevant.

> **Comments:**
> **username_1**: Hashes are memory intensive. Perhaps keep an eye on your memory usage, if you go this route. This is why indexing and sorting approaches are used.  
> **username_0**: Thanks, I appreciate critical feedback. In defence of Perl v5.20 now has postfix dereferencing ->@* syntax, which a step forward for hashes. The data loads involved are less than a single higher eukaryotic genome. Obviously not doing dataframe style calculations in a hash ... its doable done (higher order programming), but possibly surpassed.  

---

