having trouble with the shift method on data frames

[dnessett]

I am having a problem with the shift operator on dataframes. The following code works fine:

import vaex
import numpy as np
data = np.array([0, 1, 2, 3, 4, 5, 6,-3,-2,-1])
osc_data = vaex.from_arrays(x=data)
osc_data['sx'] = osc_data['x']
osc_data.shift(1,column = 'sx',fill_value=0,inplace=True)
osc_data['zc'] = (osc_data['sx'] >= 0) & (osc_data['x'] < 0)
osc_data['id'] = vaex.vrange(0, len(osc_data))
osc_data['id'] = osc_data.id.astype('int32')
osc_data_zc = osc_data[osc_data.zc == True]
osc_data_zc['sx_prev'] = osc_data_zc['sx']
osc_data_zc['x_prev'] = osc_data_zc['x']
osc_data_zc['id_prev'] = osc_data_zc['id']
#osc_data_zc.shift(1,column = 'sx_prev',fill_value=0,inplace=True)
#osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
#osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)
print(osc_data_zc[['sx', 'x', 'zc', 'id', 'sx_prev', 'x_prev', 'id_prev']])

The result is:

 #    sx    x  zc      id    sx_prev    x_prev    id_prev
  0     6   -3  True     7          6        -3          7

However, if I enable the first commented out shift statement at the end of the snippet, i.e.,

import vaex
import numpy as np
data = np.array([0, 1, 2, 3, 4, 5, 6,-3,-2,-1])
osc_data = vaex.from_arrays(x=data)
osc_data['sx'] = osc_data['x']
osc_data.shift(1,column = 'sx',fill_value=0,inplace=True)
osc_data['zc'] = (osc_data['sx'] >= 0) & (osc_data['x'] < 0)
osc_data['id'] = vaex.vrange(0, len(osc_data))
osc_data['id'] = osc_data.id.astype('int32')
osc_data_zc = osc_data[osc_data.zc == True]
osc_data_zc['sx_prev'] = osc_data_zc['sx']
osc_data_zc['x_prev'] = osc_data_zc['x']
osc_data_zc['id_prev'] = osc_data_zc['id']
osc_data_zc.shift(1,column = 'sx_prev',fill_value=0,inplace=True)    <----- enabled
#osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
#osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)
print(osc_data_zc[['sx', 'x', 'zc', 'id', 'sx_prev', 'x_prev', 'id_prev']])

vaex throws an error I don't understand:

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
/var/folders/l5/wjz73b69427bh_9x0954j0dr0000gn/T/ipykernel_2284/1395200688.py in <module>
     15 #osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
     16 #osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)
---> 17 print(osc_data_zc[['sx', 'x', 'zc', 'id', 'sx_prev', 'x_prev', 'id_prev']])

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in __getitem__(self, item)
   5077                 if expression not in self.column_names:
   5078                     self.validate_expression(expression)
-> 5079             df = self.copy(column_names=item)
   5080             return df
   5081         elif isinstance(item, slice):

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in copy(self, column_names, treeshake)
   5676 
   5677             for name in column_names:
-> 5678                 track(name)
   5679 
   5680             # track all selection dependencies, this includes the filters

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in track(name)
   5673                     required.update(deps)
   5674                     for name in missing:
-> 5675                         track(name)
   5676 
   5677             for name in column_names:

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in track(name)
   5665                         # this might be an expression, create a valid name
   5666                         expression_columns.add(name)
-> 5667                         expr = self[name]
   5668                     # we expand it ourselves
   5669                     deps = expr.variables(ourself=True, expand_virtual=False)

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in __getitem__(self, item)
   5057             #     return self._virtual_expressions[item]
   5058             if item not in self.column_names:
-> 5059                 self.validate_expression(item)
   5060             item = vaex.utils.valid_expression(self.get_column_names(), item)
   5061             return Expression(self, item)  # TODO we'd like to return the same expression if possible

/usr/local/lib/python3.9/site-packages/vaex/dataframe.py in validate_expression(self, expression)
   3227             return vaex.expresso.validate_expression(expression, vars, funcs)
   3228         except NameError as e:
-> 3229             raise NameError(str(e)) from None
   3230 
   3231     def _block_scope(self, i1, i2):

NameError: Column or variable '__sx_shifted' does not exist. Did you mean: '__x_shifted' or '__sx_unshifted' or '__x_unshifted'

Since the shift call at the beginning of the snippet works, I am at a loss to understand why the shift call at the end of the snippet doesn't. This snippet abstracts code that I want to use on a dataframe with many rows. I particular, I want to copy certain columns down one place so I can do some calculations that involve the values on both rows. Can anyone give me a hand?

[JovanVeljanoski]

Yeah, this is a known issue. Currently you can't shift a column that has already been shifted before. You may also get the same (or similar) issue if you try to shift a virtual column that is several "generations deep" (i.e. a virtual column created from a virtual column created from a virtual column create.. ).

We are working on this, but timeline is uncertain. Maybe @maartenbreddels can comment on it more.

[dnessett]

Thanks for the reply. Are there any workarounds? For example, is it possible to create a virtual column and set its value to some column in a previous row? Or, is it possible to reference the value in a column that comes from the same column in a previous row?

[dnessett]

Thinking out-loud, how about:

osc_data_zc[['x', 'sx','id']].export('osc_data_zc_cols.hdf5')
osc_datat_zc_cols = vaex.open('osc_data_zc_cols.hdf5')
osc_datat_zc_cols.rename('sx', 'sx_prev')
osc_datat_zc_cols.rename('x', 'x_prev')
osc_datat_zc_cols.rename('id', 'id_prev')
osc_data_zc = osc_datat_zc.join(osc_datat_zc_cols)
osc_data_zc.shift(1,column = 'sx_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)

[dnessett]

I tried the code in the last message. It failed with:

ValueError: Merging datasets with unequal row counts (8388608 != 419432)

It appears that osc_data_zc is actually osc_data internally defined using a slice of osc_data

[dnessett]

I finally got the following to work. I am posting it here, in case someone else wants to do something similar. Also, I have a few questions after I document the solution:

import vaex
import numpy as np
data = np.array([-3,-2,-1,0, 1, 2, 3, 4, 5, 6,-4,-3,-2,-1,0,1,2,3,4,5,-4,-3,-2,-1,0,1,2,3,4])
osc_data = vaex.from_arrays(x=data)
print(osc_data)
osc_data['sx'] = osc_data['x']
osc_data.shift(1,column = 'sx',fill_value=0,inplace=True)
osc_data['zc'] = (osc_data['sx'] >= 0) & (osc_data['x'] < 0)
osc_data['id'] = vaex.vrange(0, len(osc_data))
osc_data['id'] = osc_data.id.astype('int32')
osc_data[['x','sx','id']].export('/Users/dnessett/Desktop/osc_data_cols.hdf5')
osc_data_cols = vaex.open('/Users/dnessett/Desktop/osc_data_cols.hdf5')
osc_data_cols.rename('sx', 'sx_prev')
osc_data_cols.rename('x', 'x_prev')
osc_data_cols.rename('id', 'id_prev')
osc_data = osc_data.join(osc_data_cols)
osc_data_zc = osc_data[osc_data.zc == True]
osc_data_zc.shift(1,column = 'sx_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)
print(osc_data_zc[['sx', 'x', 'zc', 'id', 'sx_prev', 'x_prev', 'id_prev']])

This solution is a workaround, but it leaves a "temporary file" lying around (i.e, osc_data_cols.hdf5). I would like to delete this file in the Python code, but I could not find a delete dataframe method. Am I missing something? If such a method does not exist, I wonder if the following would work? Put the code that creates the "temporary file" in a function. Return the name of the temporary file from the function. Delete the temporary file in the calling code. This should work if when exiting the function, the dataframe is deleted because it goes out of scope. Is this true?

[maartenbreddels]

This is a nasty issue, that will take me a while to fix (just requires a deep dive/thinking)

but I could not find a delete dataframe method

This does not exist, it is simply a file on disk, so you can delete that using a regular Python API.

This should work if when exiting the function, the dataframe is deleted because it goes out of scope. Is this true?

Not sure if I totally understand your description, but this also seems to work:

import vaex
import numpy as np
data = np.array([-3,-2,-1,0, 1, 2, 3, 4, 5, 6,-4,-3,-2,-1,0,1,2,3,4,5,-4,-3,-2,-1,0,1,2,3,4])
osc_data = vaex.from_arrays(x=data)
print(osc_data)
osc_data['sx'] = osc_data['x']
osc_data.shift(1,column = 'sx',fill_value=0,inplace=True)
osc_data['zc'] = (osc_data['sx'] >= 0) & (osc_data['x'] < 0)
osc_data['id'] = vaex.vrange(0, len(osc_data))
osc_data['id'] = osc_data.id.astype('int32')
osc_data_cols = osc_data[['x','sx','id']]
osc_data_cols.rename('sx', 'sx_prev')
osc_data_cols.rename('x', 'x_prev')
osc_data_cols.rename('id', 'id_prev')
osc_data = osc_data.join(osc_data_cols)
osc_data_zc = osc_data[osc_data.zc == True]
osc_data_zc.shift(1,column = 'sx_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'x_prev',fill_value=0,inplace=True)
osc_data_zc.shift(1,column = 'id_prev',fill_value=0,inplace=True)
print(osc_data_zc[['sx', 'x', 'zc', 'id', 'sx_prev', 'x_prev', 'id_prev']])

[dnessett]

Thanks for the reply. Following up on the delete dataframe question, I assume there is some state in local memory that holds the expressions associated with a dataframe for its virtual columns and that is what the dataframe variable holds (or points to). If I delete the underlying file, can I then set this variable to another value (say, 0) without throwing an error?

[maartenbreddels]

yeah, it's in df.virtual_columns, you don't have to do anything with it, just do not reference it and it will garbage collect.

[dnessett]

Thanks.