title: Pandas Filtering and convert to Date to datetime64ns
body: <p>I am trying to figure out a problem but so far I could not find any solution I hope you might can help. 
  I have a DataFrame and I would like to convert <code>str</code> to <code>datatime</code> but there are some invalid rows which I would like to filter out. Here are two examples:</p>
  
  <pre><code>Out[6]:
    #  name    date
    0  aa      2012-11-30T14:00:00+01:00
    1  bb      2012-12-01T08:16:00+01:00
    2  cc      2012-12-01T10:14:00+01:00
    3  ee      2012-12-01T11:05:00+01:00
    4  gg      2012-12-01T11:05:00+01:00
  
  In [7]: df2
  Out[7]:
    #  name    date
    0  aa      2012-11-30T14:00:00+01:00
    1  bb      2012-12-01T08:16:00+01:00
    2  cc      2012-12-01T10:14:00+01:00
    3  ee      2012-12-01T11:05:00+01:00
    4  ff      fsadfi2 2ih3ro
    5  gg      2012-12-01T11:05:00+01:00
  </code></pre>
  
  <pre><code>In [11]: df.dtypes
  Out[11]:
  name    &lt;class 'str'&gt;
  date    &lt;class 'str'&gt;
  dtype: object
  
  In [12]: df2.dtypes
  Out[12]:
  name    &lt;class 'str'&gt;
  date    &lt;class 'str'&gt;
  dtype: object
  </code></pre>
  
  <p><code>df</code> I fine, it only has valid dates in the <code>date</code> column. But <code>df2</code> has some invalid lines.
  Let's see <code>df</code> first with the following line I can convert to <code>datetime</code>:</p>
  
  <pre><code>df['pdate']=df.date.values.astype('datetime64[ns]')
  </code></pre>
  
  <p>Works well:</p>
  
  <pre><code>
  In [16]: df
  Out[16]:
    #  name    date                       pdate
    0  aa      2012-11-30T14:00:00+01:00  2012-11-30 13:00:00.000000000
    1  bb      2012-12-01T08:16:00+01:00  2012-12-01 07:16:00.000000000
    2  cc      2012-12-01T10:14:00+01:00  2012-12-01 09:14:00.000000000
    3  ee      2012-12-01T11:05:00+01:00  2012-12-01 10:05:00.000000000
    4  gg      2012-12-01T11:05:00+01:00  2012-12-01 10:05:00.000000000
  
  In [17]: df.dtypes
  Out[17]:
  name      &lt;class 'str'&gt;
  date      &lt;class 'str'&gt;
  pdate    datetime64[ns]
  dtype: object
  </code></pre>
  
  <p>Now I try to filter out with a very simple <code>str.contains</code>::</p>
  
  <pre><code>In [18]: df2_filtered=df2[df2['date'].str.contains(':00')]
  
  In [19]: df2_filtered
  Out[19]:
    #  name    date
    0  aa      2012-11-30T14:00:00+01:00
    1  bb      2012-12-01T08:16:00+01:00
    2  cc      2012-12-01T10:14:00+01:00
    3  ee      2012-12-01T11:05:00+01:00
    4  gg      2012-12-01T11:05:00+01:00
  
  In [20]: df2_filtered.dtypes
  Out[20]:
  name    &lt;class 'str'&gt;
  date    &lt;class 'str'&gt;
  dtype: object
  </code></pre>
  
  <p>It has only <code>5 Rows</code>. 
  Now I try to convert and I get a nice error message:</p>
  
  <pre><code>In [21]: df2_filtered['pdate']=df2_filtered.date.values.astype('datetime64[ns]')
      ...:
  /usr/local/bin/ipython:1: DeprecationWarning: parsing timezone aware datetimes is deprecated; this will raise an error in the future
    #!/opt/local/Library/Frameworks/Python.framework/Versions/3.7/bin/python3.7
  ---------------------------------------------------------------------------
  ValueError                                Traceback (most recent call last)
  &lt;ipython-input-21-563087d6f949&gt; in &lt;module&gt;
  ----&gt; 1 df2_filtered['pdate']=df2_filtered.date.values.astype('datetime64[ns]')
  
  /opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py in __setitem__(self, name, value)
     4370         if isinstance(name, six.string_types):
     4371             if isinstance(value, (np.ndarray, Column)):
  -&gt; 4372                 self.add_column(name, value)
     4373             else:
     4374                 self.add_virtual_column(name, value)
  
  /opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py in add_column(self, name, data, dtype)
     5743         #     self._length_original = len(data)
     5744         #     self._index_end = self._length_unfiltered
  -&gt; 5745         super(DataFrameArrays, self).add_column(name, data, dtype=dtype)
     5746         self._length_unfiltered = int(round(self._length_original * self._active_fraction))
     5747         # self.set_active_fraction(self._active_fraction)
  
  /opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py in add_column(self, name, f_or_array, dtype)
     2872                     # give a better warning to avoid confusion
     2873                     if len(self) == len(ar):
  -&gt; 2874                         raise ValueError("Array is of length %s, while the length of the DataFrame is %s due to the filtering, the (unfiltered) length is %s." % (len(ar), len(self), self.length_unfiltered()))
     2875                 raise ValueError("array is of length %s, while the length of the DataFrame is %s" % (len(ar), self.length_original()))
     2876             # assert self.length_unfiltered() == len(data), "columns should be of equal length, length should be %d, while it is %d" % ( self.length_unfiltered(), len(data))
  
  ValueError: Array is of length 5, while the length of the DataFrame is 5 due to the filtering, the (unfiltered) length is 6.
  </code></pre>
  
  <p>Saying: <strong>ValueError: Array is of length 5, while the length of the DataFrame is 5 due to the filtering, the (unfiltered) length is 6.</strong></p>
  
  <p>But as far as I understand in the <code>df2_filtered</code> I have only 5 rows. I do not know why does it matter how many rows are in the <code>df2</code>.</p>
  
  <p><strong>Basically my question is how can I filter out the unnecessary data and convert the column to a Datetime?</strong></p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Based on <code>Maarten Breddels</code> I have tried to use:</p>
  
  <pre><code>df2_filtered['pdate']=df2_filtered.date.astype('datetime64[ns]')
  </code></pre>
  
  <p>Which seems working but when I try to use <code>df2_filtered</code> I get the following.</p>
  
  <pre><code>In [57]: df2_filtered
  Out[57]: ERROR:MainThread:vaex:error evaluating: pdate at rows 0-5
  Traceback (most recent call last):
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 94, in evaluate
      result = self[expression]
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 141, in __getitem__
      raise KeyError("Unknown variables or column: %r" % (variable,))
  KeyError: 'Unknown variables or column: "astype(date, \'datetime64[ns]\')"'
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 3467, in table_part
      values[name] = df.evaluate(name)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 5038, in evaluate
      dtype = dtypes[expression] = self.dtype(expression, internal=False)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 2005, in dtype
      data = self.evaluate(expression, 0, 1, filtered=False, internal=True, parallel=False)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 5143, in evaluate
      value = scope.evaluate(expression)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 94, in evaluate
      result = self[expression]
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 136, in __getitem__
      self.values[variable] = self.evaluate(expression)  # , out=self.buffers[variable])
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 100, in evaluate
      result = eval(expression, expression_namespace, self)
    File "&lt;string&gt;", line 1, in &lt;module&gt;
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/functions.py", line 2106, in _astype
      return x.astype(dtype)
  AttributeError: 'ColumnStringArrow' object has no attribute 'astype'
  ERROR:MainThread:vaex:error evaluating: pdate at rows 0-5
  Traceback (most recent call last):
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 94, in evaluate
      result = self[expression]
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 141, in __getitem__
      raise KeyError("Unknown variables or column: %r" % (variable,))
  KeyError: 'Unknown variables or column: "astype(date, \'datetime64[ns]\')"'
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 3467, in table_part
      values[name] = df.evaluate(name)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 5038, in evaluate
      dtype = dtypes[expression] = self.dtype(expression, internal=False)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 2005, in dtype
      data = self.evaluate(expression, 0, 1, filtered=False, internal=True, parallel=False)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/dataframe.py", line 5143, in evaluate
      value = scope.evaluate(expression)
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 94, in evaluate
      result = self[expression]
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 136, in __getitem__
      self.values[variable] = self.evaluate(expression)  # , out=self.buffers[variable])
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/scopes.py", line 100, in evaluate
      result = eval(expression, expression_namespace, self)
    File "&lt;string&gt;", line 1, in &lt;module&gt;
    File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/vaex/functions.py", line 2106, in _astype
      return x.astype(dtype)
  AttributeError: 'ColumnStringArrow' object has no attribute 'astype'
  
    #  name    date                       pdate
    0  aa      2012-11-30T14:00:00+01:00  error
    1  bb      2012-12-01T08:16:00+01:00  error
    2  cc      2012-12-01T10:14:00+01:00  error
    3  ee      2012-12-01T11:05:00+01:00  error
    4  gg      2012-12-01T11:05:00+01:00  error
  </code></pre>