Add exception when passing invalid column name to select_as_multiple

[gravesee]

No exception is raised when requesting a column that doesn't exist in any of the key tables.

I think the desired behavior in this situation would be to check if the requested columns exist before performing retrieving the data. If any of the columns do not exist, raising an exception that lists the invalid columns would be very helpful.

When I finally finish my wrapper code, I will have a reverse dict that looks up the tables that the requested columns belong too (as suggest by @jreback on SO). I will add this safety check there as well, but it seems like a safe thing to add to select_as_multiple, too.

>>> pd.__version__
'0.11.0.dev-eb07c5a'

>>> tmp = store.select_as_multiple(['chunk0','chunk1'], columns=['SalesID','INVALID_COLUMN'])

>>> tmp
<class 'pandas.core.frame.DataFrame'>
Int64Index: 401125 entries, 0 to 1124
Data columns (total 1 columns):
SalesID    401125  non-null values
dtypes: int64(1)

[jreback]

that seems reasonable

and if u would post your code when u can would appreciate it

I think it makes sense to make a 'manager' object for muti tables
so would like to see how u r using it

it actually makes it interesting to serialize this object into the store as well

[gravesee]

I'm starting to work on the wrapper code mostly geared towards keeping memory use low. My first attempt tried to store each column using append, but that raised another exception (can a series not be stored as a table?). To get around this, I instead store the dataframe in groups of 2 columns.

The code below chunks through the csv file twice. The first pass is to calculate the datatypes. The second pass is to store the dataframes in the HDFStore.

# find the datatypes with chunksize
def get_dtypes(chunker):
    tmp = pd.DataFrame([chunk.dtypes for chunk in chunker])
    return tmp.max().to_dict()

chunker = pd.read_csv('Train.csv', header=0, chunksize=50000)
dtypes = get_dtypes(chunker)

# store columns in their own nodes
store.close()
store = pd.HDFStore('test.h5', 'w')
chunker = pd.read_csv('Train.csv', header=0, chunksize=50000, dtype=dtypes)

# break up columns into groups of at least 2
num_cols = len(dtypes)
rng = [x for x in xrange(0, len(chunk.columns) - 2 , 2)]
rng.append(num_cols)

for chunk in chunker:
    for id, tup in enumerate(zip(rng, rng[1:])):
        start, stop = tup
        # I will eventually add a function that calcs min_itemsize for each chunk
        store.append('chunk%s' % id, chunk.ix[:,start:stop], min_itemsize=64)

[gravesee]

Just tested that I can store a single column using append as long as it's a dataframe class:

df = pd.DataFrame({'a':range(1000000), 'b':range(1000000)})

# I'm using append as I will be building the data store iteratively with pd.read_csv
# It doesn't make sense to use append for this toy example

# this fails:
store.append('a', df['a'])
Exception: cannot properly create the storer for: ...

# while this succeeds.  Note the double brackets:
store.append('a', df[['a']])
store.append('b', df[['b']])


# interesting that this:
tmp = pd.concat([store['a'], store['b']])

# is much faster than this:
 tmp = store.select_as_multiple(['a','b'], columns=['a','b'])

I will test the performance of storing each column individually like this tonight. This is probably how I will end up using pandas and HDFStore.

[jreback]

select_as_multiple will be much faster if you are using a query

if first does the query then just selects those rows from all tables; your example is actually too simple to catch this, and the select_as_multiple is doing 3 queries to your 2

[wabu]

The behaviour is consistent with select, so it should either raise for both (which will be an api change) or just leave it as is.

In [4]: data = pd.DataFrame({'a': np.random.rand(10), 'b': np.random.rand(10)})
In [5]: store.append_to_multiple({'a': ['a'], 'b': ['b']}, data, 'a')

In [6]: store.select_as_multiple(['a','b'], columns=['c'])
Out[6]: Empty DataFrame
Columns: []
Index: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

In [7]: store.select('a', columns=['c'])
Out[7]: Empty DataFrame
Columns: []
Index: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

[jreback]

ok if u try an invalid query (eg use a column that does not exist or is not a data_column) it will raise
I am thinking that this should too though that limits flexibility a bit as u have to "know" a priori what columns are in the table

this change is a bit tricky - u have to put it in process_axes because columns can be passed as a kw or in an expression (eg "columns=['C']")

@wabu if u would like to take a stab to for it