Turning a list of key/value pairs into a pandas dataframe stored in a HDFStore
<p>There are questions similar to this, but none of them handle the case where my dataframe is inside an HDFStore.</p>

<p>I need to turn a list of timestamp/key/value items into dataframes and store it as several dataframes each indexed on the timestamp, and then save it in an HDFStore.</p>

<p>Example code:</p>

<pre><code>from pandas import HDFStore
from pandas import DataFrame
store = HDFStore('xxx', driver="H5FD_CORE")
for i, k, v in ((0, 'x', 5), (1, 'y', 6)):
    if k not in store:
        store[k] = DataFrame()
    store[k].set_value(i, 'value', v)
</code></pre>

<p>After this code runs, <code>store['x']</code> remains empty.</p>

<pre><code>&gt;&gt;&gt; store['x']
Empty DataFrame
Columns: []
Index: []
</code></pre>

<p>So there is obviously some reason why that is not persisting, and it is also certainly the case that I just don't know how this stuff is supposed to work. I can certainly figure out the logic if I just understand how you append to tables/dataframes inside an HDFStore.</p>

<p>I could also just keep the dataframes in memory, in some kind of dictionary, and just assign them to to the HDFStore right at the end. I somehow had this misguided idea that doing it this way will save memory, perhaps I am wrong about that too.</p>

<p>I'd comment to get some clarification, but I don't have the rep yet. Without some more context, it's hard for me to say whether your approach is wise, but I'd inclined to say no in almost all cases. Correct me if I'm wrong, but what you're trying to do is:</p>

<ul>
<li>Given a list of iterables: <code>[(timeA, key1, value1), (timeB, key1, value2), (timeC, key2, value1)]</code></li>
<li>You would want two df's in the HDFStore, where:

<ul>
<li><code>store[key1] = DataFrame([value1, value2], index=[timeA, timeB])</code></li>
<li><code>store[key2] = DataFrame([value1], index=[timeC])</code></li>
</ul></li>
</ul>

<p>Correct?</p>

<p>If so, what I would recommend is some kind of "filtering" on your store key, creating dataframes, and then writing a whole dataframe to the store, like so:</p>

<pre><code>dataTuples = [(0, 'x', 5), (1, 'y', 6), ...]

# initializing the dict of lists, which will become a dict of df's
sortedByStoreKey = {storeKey: [] for idx, storeKey, val in dataTuples}

for idx, storeKey, val in dataTuples:
    sortedByStoreKey[storeKey].append([idx, storeKey]) # appending a 2-list to a list

# this can all be done with dict comprehensions but this is more legible imo
for storeKey, dfContents in sortedByStoreKey.items():
    df = pd.DataFrame(dfContents, columns=['time', 'value'])
    df['time'] = pd.to_datetime(df['time']) # make sure this is read as a pd.DatetimeIndex (as you said you wanted)
    df.set_index('time', inplace=True)
    sortedByStoreKey[storeKey] = df

# now we write full dataframes to HDFStore
with pd.HDFStore('xxx') as store:
    for storeKey, df in sortedByStoreKey.values():
         store[storeKey] = df
</code></pre>

<p>I'm quite confident there's a more efficient way to do this, both number-of-lines-wise and resources-wise, but this is what strikes me as the most pythonic. If the <code>dataTuples</code> object is HUGE (like >= RAM), then my answer may have to change. </p>

<p>Generally speaking, the idea here is to create each of the dataframes in full before writing to the store. As I'm finishing up here, I'm realizing that you can do what you've chosen as well, and the piece that you are missing is the need to specify the store with a <a href="http://pandas.pydata.org/pandas-docs/stable/io.html#table-format" rel="nofollow">table format</a>, which enables appending. Granted, appending one row at a time is probably not a good idea. </p>

python pandas hdf