Inconsistent Date format in python

Question

I am new to Python, I have a file having date column as below formats:

date = pd.Series(['10-21-2012 ', '7-18-2019 ', '02-2-2008', 
                  '2010/21/4 ', '11-8-2019 '])

I used the following code to get the month but I get an error:

ValueError: month must be in 1..12

Code:

pd.to_datetime(date).dt.month

The output should be

Please can someone help me with this?

Have a look at this question and answer: https://stackoverflow.com/questions/62233084/how-can-i-clean-date-ranges-in-multiple-formats-using-python-pandas/62233880#62233880 — Roy2012, Jul 16 '20 at 06:16

score 1 · Answer 1 · answered Jul 16 '20 at 06:16

1

Ignoring the values that have /

In [13]: date = pd.Series(['10-21-2012 ', '7-18-2019 ', '02-2-2008',
    ...:                 '2010/21/4 ', '11-8-2019 '])

In [14]: pd.to_datetime(date, errors="coerce").dt.month
Out[14]:
0    10.0
1     7.0
2     2.0
3     NaN
4    11.0
dtype: float64

answered Jul 16 '20 at 06:16

bigbounty

16,526
5
37
65

: I already did this, but ignoring values that have '/' will lead to significant loss of data in my dataset. – sak Jul 17 '20 at 06:22
You could follow the accepted answer and apply this answer – bigbounty Jul 17 '20 at 06:23

score 1 · Accepted Answer · answered Jul 16 '20 at 06:35

welcome!
You could "normalize" the date list before passing it the Pandas Series object.
Create a function that can do it and you could also use it somewhere else in your code should you require it.
From your series it seems like you have two main formats that the dates are arranged by:
- mm-dd-yyyy
- yyyy/dd/mm

def get_months(date_list):  
    month_list = []  
    m = ''
    for dt_string in date_list:
        if "-" in dt_string:
            numbers = [int(x) for x in dt_string.split("-")]
            m = f'{numbers[0]}-{numbers[1]}-{numbers[2]}'
            month_list.append(m)
        elif "/" in dt_string:
            numbers = [int(x) for x in dt_string.split("/")]
            m = f'{numbers[2]}-{numbers[1]}-{numbers[0]}'
            month_list.append(m)
    return month_list

dates = ['10-21-2012', '7-18-2019', '02-2-2008', '2010/21/4', '11-8-2019']

months = get_months(dates)

[print(x) for x in months]

This would create a list that look like:

['10-21-2012','7-18-2019','2-2-2008','4-21-2010','11-8-2019']

Let me know if you have special requirements that would not make this work.

Inconsistent Date format in python

2 Answers2