python 2.7 - Save additional attributes in Pandas Dataframe

Question

I recall from my MatLab days using structured arrays wherein you could store different data as an attribute of the main structure. Something like:

a = {}
a.A = magic(10)
a.B = magic(50); etc.

where a.A and a.B are completely separate from each other allowing you to store different types within a and operate on them as desired. Pandas allows us to do something similar, but not quite the same.

I am using Pandas and want to store attributes of a dataframe without actually putting it within a dataframe. This can be done via:

import pandas as pd

a = pd.DataFrame(data=pd.np.random.randint(0,100,(10,5)),columns=list('ABCED')

# now store an attribute of <a>
a.local_tz = 'US/Eastern'

Now, the local timezone is stored in a, but I cannot save this attribute when I save the dataframe (i.e. after re-loading a there is no a.local_tz). Is there a way to save these attributes?

Currently, I am just making new columns in the dataframe to hold information like timezone, latitude, longituded, etc., but this seems to be a bit of a waste. Further, when I do analysis on the data I run into problems of having to exclude these other columns.

################## BEGIN EDIT ##################

Using unutbu's advice, I now store the data in h5 format. As mentioned, loading metadata back in as attributes of the dataframe is risky. However, since I am the creator of these files (and the processing algorithms) I can choose what is stored as metadata and what is not. When processing the data that will go into the h5 files, I choose to store the metadata in a dictionary that is initialized as an attribute of my classes. I made a simple IO class to import the h5 data, and made the metadata as class attributes. Now I can work on my dataframes without risk of losing the metadata.

class IO():
    def __init__(self):
        self.dtfrmt = 'dummy_str'

    def h5load(self,filename,update=False):
        '''h5load loads the stored HDF5 file.  Both the dataframe (actual data) and 
        the associated metadata are stored in the H5file

        NOTE: This does not load "any" H5 
        file, it loads H5 files specifically created to hold dataframe data and 
        metadata.

        When multi-indexed dataframes are stored in the H5 format the date 
        values (previously initialized with timezone information) lose their
        timezone localization.  Therefore, <h5load> re-localizes the 'DATE' 
        index as UTC.

        Parameters
        ----------
        filename : string/path
            path and filename of H5 file to be loaded.  H5 file must have been 
            created using <h5store> below.

        udatedf : boolean True/False
            default: False
            If the selected dataframe is to be updated then it is imported 
            slightly different.  If update==True, the <metadata> attribute is
            returned as a dictionary and <data> is returned as a dataframe 
            (i.e., as a stand-alone dictionary with no attributes, and NOT an 
            instance of the IO() class).  Otherwise, if False, <metadata> is 
            returned as an attribute of the class instance.

        Output
        ------
        data : Pandas dataframe with attributes
            The dataframe contains only the data as collected by the instrument.  
            Any metadata (e.g. timezone, scaling factor, basically anything that
            is constant throughout the file) is stored as an attribute (e.g. lat 
            is stored as <data.lat>).'''

        with pd.HDFStore(filename,'r') as store:
            self.data = store['mydata']
            self.metadata = store.get_storer('mydata').attrs.metadata    # metadata gets stored as attributes, so no need to make <metadata> an attribute of <self>

            # put metadata into <data> dataframe as attributes
            for r in self.metadata:
                setattr(self,r,self.metadata[r])

        # unscale data
        self.data, self.metadata = unscale(self.data,self.metadata,stringcols=['routine','date'])

        # when pandas stores multi-index dataframes as H5 files the timezone
        # initialization is lost.  Remake index with timezone initialized: only
        # for multi-indexed dataframes
        if isinstance(self.data.index,pd.core.index.MultiIndex):
            # list index-level names, and identify 'DATE' level
            namen = self.data.index.names
            date_lev = namen.index('DATE')

            # extract index as list and remake tuples with timezone initialized
            new_index = pd.MultiIndex.tolist(self.data.index)
            for r in xrange( len(new_index) ):
                tmp = list( new_index[r] )
                tmp[date_lev] = utc.localize( tmp[date_lev] )

                new_index[r] = tuple(tmp)

            # reset multi-index
            self.data.index = pd.MultiIndex.from_tuples( new_index, names=namen )


        if update:
            return self.metadata, self.data
        else:
            return self





    def h5store(self,data, filename, **kwargs):
        '''h5store stores the dataframe as an HDF5 file.  Both the dataframe 
        (actual data) and the associated metadata are stored in the H5file

        Parameters
        ----------
        data : Pandas dataframe NOT a class instance
            Must be a dataframe, not a class instance (i.e. cannot be an instance 
            named <data> that has an attribute named <data> (e.g. the Pandas 
            data frame is stored in data.data)).  If the dataframe is under
            data.data then the input variable must be data.data.

        filename : string/path
            path and filename of H5 file to be loaded.  H5 file must have been 
            created using <h5store> below.

        **kwargs : dictionary
            dictionary containing metadata information.


        Output
        ------
        None: only saves data to file'''

        with pd.HDFStore(filename,'w') as store:
            store.put('mydata',data)
            store.get_storer('mydata').attrs.metadata = kwargs

H5 files are then loaded via data = IO().h5load('filename.h5') the dataframe is stored under data.data I retain the metadata dictionary under data.metadata and have created individual metadata attributes (e.g. data.lat created from data.metadata['lat']).

My index time stamps are localized to pytz.utc(). However, when a multi-indexed dataframe is stored to h5 the timezone localization is lost (using Pandas 15.2), so I correct for this in IO().h5load.

Answer 1

There is an open issue regarding the storage of custom metadata in NDFrames. But due to the multitudinous ways pandas functions may return DataFrames, the _metadata attribute is not (yet) preserved in all situations.

For the time being, you'll just have to store the metadata in an auxilliary variable.

There are multiple options for storing DataFrames + metadata to files, depending on what format you wish to use -- pickle, JSON, HDF5 are all possibilities.

Here is how you could store and load a DataFrame with metadata using HDF5. The recipe for storing the metadata comes from the Pandas Cookbook.

import numpy as np
import pandas as pd

def h5store(filename, df, **kwargs):
    store = pd.HDFStore(filename)
    store.put('mydata', df)
    store.get_storer('mydata').attrs.metadata = kwargs
    store.close()

def h5load(store):
    data = store['mydata']
    metadata = store.get_storer('mydata').attrs.metadata
    return data, metadata

a = pd.DataFrame(
    data=pd.np.random.randint(0, 100, (10, 5)), columns=list('ABCED'))

filename = '/tmp/data.h5'
metadata = dict(local_tz='US/Eastern')
h5store(filename, a, **metadata)
with pd.HDFStore(filename) as store:
    data, metadata = h5load(store)

print(data)
#     A   B   C   E   D
# 0   9  20  92  43  25
# 1   2  64  54   0  63
# 2  22  42   3  83  81
# 3   3  71  17  64  53
# 4  52  10  41  22  43
# 5  48  85  96  72  88
# 6  10  47   2  10  78
# 7  30  80   3  59  16
# 8  13  52  98  79  65
# 9   6  93  55  40   3

---

print(metadata)

yields

{'local_tz': 'US/Eastern'}