Pandas: Remove rows whose date does not follow specified format

Question

In my dataset, I have a date column in which the data is of mixed format. - Some rows only have `YYYY` (e.g. `2009`) - Others have `MMM-YY` (e.g. `Jan-08`) - Yet others have a full date `MM/DD/YYYY` (e.g. `01/15/2006`) How might I remove the entries that do not follow the format `MM/DD/YYYY`? I'm not quite sure where to start here. Below, I have provided the `.head()` of the data as a `dict`. ``` {'Collection Date': {0: '2001', 1: '2002', 2: '2006', 3: '2/19/2006', 4: '2/28/2006'}, 'Complete Genome': {0: 'No', 1: 'No', 2: 'No', 3: 'No', 4: 'No'}, 'Country': {0: 'Egypt', 1: 'Egypt', 2: 'Egypt', 3: 'Egypt', 4: 'Egypt'}, 'Flu Season': {0: '-N/A-', 1: '-N/A-', 2: '-N/A-', 3: '-N/A-', 4: '-N/A-'}, 'Host Species': {0: 'IRD:Human', 1: 'IRD:Human', 2: 'IRD:Bird/Avian', 3: 'IRD:Chicken/Avian', 4: 'IRD:Avian'}, 'Protein Name': {0: 'NA', 1: 'NA', 2: 'NA', 3: 'NA', 4: 'HA'}, 'Segment': {0: 6, 1: 6, 2: 6, 3: 6, 4: 4}, 'Segment Length': {0: 1428, 1: 1449, 2: 1441, 3: 1363, 4: 1707}, 'Sequence Acces…

Accepted Answer

Rather than applying Regular expression to match just MM/DD/YYYY, if your dates are only ever YYYY or MMM/YYYY or MM/DD/YYYY then you can exploit the fact that MM/DD/YYYY is a string of length 10:

```
In [8]:

import pandas as pd

pd.set_option('display.notebook_repr_html', False)
df = pd.DataFrame({'date':['01/03/1987', '2003', 'Jan-08', '31/01/2010']})
df
Out[8]:
         date
0  01/03/1987
1        2003
2      Jan-08
3  31/01/2010

[4 rows x 1 columns]
In [9]:

df.ix[df.date.str.len() !=10]
Out[9]:
     date
1    2003
2  Jan-08

[2 rows x 1 columns]
```

You can then just use `to_datetime`:

```
In [16]:

df1 = df.ix[df.date.str.len() !=10]
df1
Out[16]:
     date
1    2003
2  Jan-08

[2 rows x 1 columns]
In [17]:

df1.date = pd.to_datetime(df1.date)
df1
Out[17]:
                 date
1 2003-01-01 00:00:00
2 2014-01-08 00:00:00

[2 rows x 1 columns]
```

Just for completeness if you wanted to filter using regexp:

```
df.ix[~df.date.str.contains('(\d{2})[/](\d{2})[/](\d{4})')]
```

would work, note the `~` which is a negation

Pandas: Remove rows whose date does not follow specified format

Solution

Problem