Transforming date object into datetime (year month day) python

Question

I need to transform a date that is format obj in datetime to make a calculation.

The column is in this format:

01JUN2020:00:00:01  
01OUT2020:00:00:02
01MAI2020:00:00:04

I tried the datetime function, but I couldn’t.

df['data_nova'] = pd.to_datetime(df['data'],format='%d%b%Y').strptime 

But returns the error ValueError: unconverted data remains: :00:00:00

Just turning it into datetime is also not possible

df['ArrivalDate'] = pd.to_datetime(df['data'])
Unknown string format: 01MAI2020:00:00:00

The ultimate goal is to create a new field by subtracting the current date with the date field

Answer 1

I’ll make a modification to the reply from Paulo Marques because I agree that the fact that the months are shortened in capital is a problem to get the solution.

But operating systems provide an internationalization database where you only install in the operating system a package of language and cultural specifications. The programming languages provide access to that database and with Python is no different.
The internationalization mechanism allows programmers to deal with certain cultural issues in an application, without requiring the programmer to know all the specifics of each country where the software runs.

Within the Python language the module is available locale opening access to the local database.

Initially use setlocale() to modify your location settings with the parameters:

• locale.LC_TIME : Specifier for time and date locations.
• locale.normalize('pt_BR.utf8') : Returns a normalized locale code for given locale.

Thus allowing a user anywhere in the world to use their software with data in native language.

>>> import locale
>>> import datetime as dt
>>> import pandas as pd

>>> try:
...    locale.setlocale(locale.LC_TIME, locale.normalize('pt_BR.utf8'))
... except locale.Error:
...    print('Instale o módulo de linguagem adequado no seu Sistema Operacional.')

>>> df = pd.DataFrame({"data": ["01JUN2020:00:00:01", "01OUT2020:00:00:02", "01MAI2020:00:00:04"]})

>>> df['nova_data'] = pd.to_datetime(df['data'].str.title(),format='%d%b%Y:%H:%M:%S')

>>> df

Result on Windows: Result on Linux:

Answer 2

Two things in the example you posted:

1. The months are abbreviated in capital letters
2. The months are in Portuguese

For the first case, use the title resolves.

For the second case you have to use a function to replace

>>> df = pd.DataFrame({"data": ["01JUN2020:00:00:01", "01AUG2020:00:00:04"]})

>>> df
                 data
0  01JUN2020:00:00:01
1  01AUG2020:00:00:04

>>> df['nova_data'] = pd.to_datetime(df['data'].str.title(),format='%d%b%Y:%H:%M:%S')

>>> df
                 data           nova_data
0  01JUN2020:00:00:01 2020-06-01 00:00:01
1  01AUG2020:00:00:04 2020-08-01 00:00:04

dataframe info

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column     Non-Null Count  Dtype
---  ------     --------------  -----
 0   data       2 non-null      object
 1   nova_data  2 non-null      datetime64[ns]   <<<<<------
dtypes: datetime64[ns](1), object(1)
memory usage: 160.0+ bytes

For the second case

>>> df = pd.DataFrame({"data": ["01JUN2020:00:00:01", "01MAI2020:00:00:04"]})

>>> df['nova_data'] = pd.to_datetime(df['data'].str.replace('MAI', 'May'),format='%d%b%Y:%H:%M:%S')

This dataframe

>>> print(df)
                 data           nova_data
0  01JUN2020:00:00:01 2020-06-01 00:00:01
1  01MAI2020:00:00:04 2020-05-01 00:00:04
>>>

Update based on the comment

>>> df = pd.DataFrame({"data": ["01FEV2020:00:00:01", "01MAI2020:00:00:04"]})

>>> df
                 data
0  01FEV2020:00:00:01
1  01MAI2020:00:00:04

>>> df['nova_data'] = pd.to_datetime(df['data'].str.replace('MAI', 'May').str.replace('FEV', 'Feb'),format='%d%b%Y:%H:%M:%S')

>>> df
                 data           nova_data
0  01FEV2020:00:00:01 2020-02-01 00:00:01
1  01MAI2020:00:00:04 2020-05-01 00:00:04