CODEWITHSUNDEEP
pythonpython-3.xpandasdatetime
pyassign67
pyassign67

Reputation: 55

Change date format which include text using datetime

I have a pandas df with a column of dates where the dates are written as "Monday, March 19, 2001 at 3:30:00pm GMT", "Wednesday, April 11, 2001 at 9:00:00pm GMT", "Tuesday, April 3, 2001 at 2:00:00pm GMT", and so forth.

I wish to convert it to "19/03/2001", 11/04/2001, 03/04/2001, and so fourth. Is there a way of doing this using datetime?

Upvotes: 0

Views: 391

Answers (2)

zabop
zabop

Reputation: 7852

import pandas as pd

If you have a dataframe:

df = pd.DataFrame({'Dates':["Monday, March 19, 2001 at 3:30:00pm GMT",
                            "Wednesday, April 11, 2001 at 9:00:00pm GMT",
                            "Tuesday, April 3, 2001 at 2:00:00pm GMT"]})

Then can do:

import calendar
monthdict={v: k for k,v in enumerate(calendar.month_abbr)}

This will create monthdict, which will be:

{'': 0,
 'Apr': 4,
 'Aug': 8,
 'Dec': 12,
 'Feb': 2,
 'Jan': 1,
 'Jul': 7,
 'Jun': 6,
 'Mar': 3,
 'May': 5,
 'Nov': 11,
 'Oct': 10,
 'Sep': 9}

Then extract the month data from the strings in your df:

months=[monthdict[each] for each in df['Dates'].str.split(',').str[1].str.split(' ').str[1].str[:3]]

Extract the days:

days=df['Dates'].str.split(',').str[1].str.split(' ').str[2]

Years:

years=df['Dates'].str.split(',').str[2].str.split(' ').str[1]

Put it back together to a list of datetime.datetime objects:

dts=[datetime.datetime(int(year), (month), int(day)) for year, month, day in zip(years,  months, days)]

Transform it to a df:

newdf=pd.DataFrame({'NewDates':dts})

If want to convert these datetime.datetime objects to strings in some another format, can do:

newdf['FormattedDates']=newdf['NewDates'].apply(lambda row: repr(row.day)+'/'+repr(row.month)+'/'+repr(row.year))

You will end up with:

    NewDates    FormattedDates
0   2001-03-19  19/3/2001
1   2001-04-11  11/4/2001
2   2001-04-03  3/4/2001

(I recommend using datetime.datetimes rather than strings though.)

Upvotes: 1

EnderShadow8
EnderShadow8

Reputation: 1040

from datetime import datetime
def str_to_datetime(s):
    return datetime.strptime(" ".join(s.split()[1:4]), "%B %d, %Y")

Upvotes: 0

Related Questions