1

I am trying to convert a datetime string (German) that comes from MS Project Excel Export.

02 Februar 2022 17:00

I read it from a Excel-Export of MS Project in to a pandas dataframe.

When converting it with 

to_datetime(df["Anfang"], format= '%d %B %Y %H:%M').dt.date

but get the error

ValueError: time data '07 Januar 2019 07:00' does not match format '%d %B %Y %H:%M' (match)

from https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior

%B   Month as locale’s full name.   September

What I am doing wrong here? Do I have to check some local settings? I am using German(Swiss)

import locale
locale.getdefaultlocale()
('de_CH', 'cp1252')

df in:

0       10 April 2019 08:00
1      07 Januar 2019 07:00
2      07 Januar 2019 07:00
3      07 Januar 2019 07:00
4     09 Oktober 2019 17:00
5    04 Dezember 2020 17:00
Name: Anfang, dtype: object

df out (wanted):

0       10-04-2019
1       07-01-2019
.
.

EDIT: I changed my locale to ('de_DE', 'cp1252'), but I get the same error.

SOLVED: By using matJ's answer, I got the error that "Die 15.06.21" was not matching the format, which led me to investigate the data. There I found two different date formats (Thanks, Microsoft!). After cleaning, the above code worked well!!! So the error message of to_datetime wasn't precise as datetime.strptime.

Thanks for helping.

Johannes

nanuuq
  • 169
  • 9
  • Possible duplicate of [How do I strftime a date object in a different locale?](https://stackoverflow.com/questions/18593661/how-do-i-strftime-a-date-object-in-a-different-locale) – LinPy Jun 13 '19 at 06:25
  • @LinSel: I checked that, but I don't want to change the locale. I could not find information about if the `de_CH` locale is recognized by `strftime`. If it isn't, I will use dateparser. – nanuuq Jun 13 '19 at 07:51

2 Answers2

2

One possible solution is use dateparser module:

import dateparser

df['Anfang'] = df['Anfang'].apply(dateparser.parse)
print (df)
               Anfang
0 2019-04-10 08:00:00
1 2019-01-07 07:00:00
2 2019-01-07 07:00:00
3 2019-01-07 07:00:00
4 2019-10-09 17:00:00
5 2020-12-04 17:00:00

import dateparser

df['Anfang'] = df['Anfang'].apply(dateparser.parse).dt.date
print (df)
       Anfang
0  2019-04-10
1  2019-01-07
2  2019-01-07
3  2019-01-07
4  2019-10-09
5  2020-12-04
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

I'd change the locale in a different way. Then your code should work.

The following works for me:

import locale
from datetime import datetime 

locale.setlocale(locale.LC_ALL, 'de_DE')  # changing locale to german 
datetime.strptime('07 Januar 2019 07:00', '%d %B %Y %H:%M')  # returns a datetime obj which you can format as you like

Let me know if that works for you as well.

turnman
  • 154
  • 10
  • Your aproach led me to the solution, actualy the problem. When using it with `df_tp_cm["Anfang"].apply(lambda x: datetime.strptime(x,'%d %B %Y %H:%M'))` I got the error about a value, that was'nt matching (weird MS Project export, two different formats for date...). When cleaning this, my initial aproach worked well also! But there I got the wrong error value that didn't match (Die 15.06.21) . Thanks a lot! – nanuuq Jun 13 '19 at 16:23