Drop "faulty" lines of pandas dataframe based on their type and value
<p>I have a dataset that includes a column of date and time values and another column containing some measured values (float). However, during some measurements, an error occured, resulting in some weird entries - example below (these include a repeated part of the datetime object which is interpreted as string, incomplete datetime object, a completely random string, a missing value or a value for the other column which is way out of range (measured values are mostly between 10 and 50, but sometimes I get a zero or a value like 100).</p>

<p>extract from the large dataset (loaded as pandas dataframe):</p>

<pre><code>                                      t                          baaa
0                      13/11/2014 23:43                          17.6
1                      13/11/2014 23:44                          17.7
2   2014-11-13 23:452014-11-13 23:45:00                          17.7
3                      13/11/2014 23:46                          17.7
4                      14/11/2014 00:34                            16
5                      14/11/2014 00:35                          15.9
6                                   :00                          17.7
7                      14/11/2014 01:25                          14.9
8                      14/11/2014 01:26                          14.9
9                                     0                            80
10                     14/11/2014 02:16                          14.3
11                     14/11/2014 02:17                          14.3
12                                  NaN  AA550112209500080009002855AA
13                     14/11/2014 03:09                            13
14                      009000B002B55AA                           NaN
15                     14/11/2014 02:19                          14.3
16                     14/11/2014 03:59                          12.6
17                     14/11/2014 04:00                          12.6
18                     14/11/2014 05:41                          11.7
19                     14/11/2014 05:42                          11.7
20                                    0                           140
21                     14/11/2014 04:53                          12.2
</code></pre>

<p>examples of all types of faulty entries are here.
How can I get rid of the faulty lines?
My idea was to do an if loop, setting the condition that the 't' column should be a datetime object and the 'baaa' columns should be a float > 0 and &lt; 60. If the condition is not fulfilled, I would replace the value with <code>np.nan</code> and eventually use the <code>dropna</code> function. </p>

<pre><code>df['t'] = pd.to_datetime(df['t'], format = '%d/%m/%Y %H:%M', errors='coerce')
df.iloc[:,1] = pd.to_numeric(df.iloc[:,1], errors='coerce')    
for line in df.iloc[:,1]:  
    if (line &lt; 60) &amp; (line &gt; 0):
       line = line
   else:
       line = np.nan
    # not assigning this new value! :( 

    df = df.dropna(subset = df.columns.values, how='any', inplace=True)
</code></pre>

<p>This seems to have solved most of the problems except the condition that the line needs to be lower than 60.
I must have a wrong syntax? Or what is wrong here?
Thanks!</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a> for filtering, instead <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html" rel="nofollow"><code>dropna</code></a> you can add new (third) condition with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.notnull.html" rel="nofollow"><code>notnull</code></a> - get all not <code>NaN</code> values in column <code>t</code>. <code>NaN</code> values in first column are filtered by first and second condition:</p>

<pre><code>df['t'] = pd.to_datetime(df['t'], format = '%d/%m/%Y %H:%M', errors='coerce')
df.iloc[:,1] = pd.to_numeric(df.iloc[:,1], errors='coerce')  
df = df[(df.iloc[:,1] &lt; 60) &amp; (df.iloc[:,1] &gt; 0) &amp; (df['t'].notnull())]

print (df)
                     t  baaa
0  2014-11-13 23:43:00  17.6
1  2014-11-13 23:44:00  17.7
3  2014-11-13 23:46:00  17.7
4  2014-11-14 00:34:00  16.0
5  2014-11-14 00:35:00  15.9
7  2014-11-14 01:25:00  14.9
8  2014-11-14 01:26:00  14.9
10 2014-11-14 02:16:00  14.3
11 2014-11-14 02:17:00  14.3
13 2014-11-14 03:09:00  13.0
15 2014-11-14 02:19:00  14.3
16 2014-11-14 03:59:00  12.6
17 2014-11-14 04:00:00  12.6
18 2014-11-14 05:41:00  11.7
19 2014-11-14 05:42:00  11.7
21 2014-11-14 04:53:00  12.2
</code></pre>

python pandas