Storing Dataframe with Array Entries

Question

I have a Pandas DataFrame with the following structure, which contains both numbers and numpy arrays of fixed shape:

import pandas as pd
import numpy as np

df = pd.DataFrame({"num":(23, 42), "list":(np.arange(3), np.arange(1,4))

Assuming I have large (more than 1 GB) amounts of this data that I would like to store and retrieve quickly, how should I go about storing it? If I use HDF5, the Numpy array gets pickled which will affect the ability to retrieve the data quickly. Is there some way to tell HDF5 how to store Numpy arrays? Alternatively, should I not be using HDF5 at all?

The following GitHub thread seems to suggest the following:

Create a function that gets the desired Numpy array, which is stored in some other format [1]
Create a class to inform HDF5 [2]

Both of these solutions seem oddly specific for how common I imagine this problem to be. Are there more general approaches? Am I just using the wrong tool?

But that results in the redundant storage of the `num` field. Is it silly to try to avoid that? — Seanny123, Oct 01 '16 at 02:45
It isn't silly to avoid that if it was a problem. The way this is arranged, you won't have extra rows of `num` because the arrays expand within the same row. @HYRY is right, expand the arrays, then store. Collapsing them back after retrieval. — piRSquared, Oct 01 '16 at 07:38

score 3 · Accepted Answer · edited Oct 01 '16 at 07:48

3

I mean something like this:

df_x = pd.concat([df.num, pd.DataFrame(np.vstack(df.list))], 
                 keys=["key", "arr"], axis=1)

the dataframe:

  key arr      
  num   0  1  2
0  23   0  1  2
1  42   1  2  3

convert back with:

pd.concat([df_x.key, pd.Series(tuple(df_x.arr.values), name='list')], axis=1)

   num       list
0   23  [0, 1, 2]
1   42  [1, 2, 3]

edited Oct 01 '16 at 07:48

piRSquared

285,575
57
475
624

answered Oct 01 '16 at 02:50

HYRY

94,853
25
187
187

Storing Dataframe with Array Entries

1 Answers1

Linked