CODEWITHSUNDEEP
pythonpandas
Divansh Chaudhary
Divansh Chaudhary

Reputation: 15

How to convert this whole column into date format?

I tried this to remove the unwanted characters but it is not working

Actual data

from datetime import datetime

dates = []

for i in data.Date:
    date_time_str = str(data.Date[i])
    date_time_str = date_time_str.replace("M ","")
    date1 = date_time_str.replace(".","")
    dates.append(date1)

print(dates)

Upvotes: 1

Views: 118

Answers (2)

Patrick Artner
Patrick Artner

Reputation: 51623

Use pd.to_datetime and give a correct format string for your data ('M %m.%Y'):

import pandas as pd

data = pd.DataFrame( {"Code":[114, 115, 116, 117], 
                      "Date":["M 02.2017", "M 03.2018", "M 04.2019", "M 05.2020",]})

print(data)

# simply use the correct format string for your data here
data["asDate"] = pd.to_datetime(data["Date"], format = "M %m.%Y") 
print(data)

Output:

   Code       Date
0   114  M 02.2017
1   115  M 03.2018
2   116  M 04.2019
3   117  M 05.2020

   Code       Date     asDate
0   114  M 02.2017 2017-02-01
1   115  M 03.2018 2018-03-01
2   116  M 04.2019 2019-04-01
3   117  M 05.2020 2020-05-01

To get just the dates as list use

dates_1 = list(data["asDate"]) # contains <class 'numpy.datetime64'> and
dates_2 = list(data["asDate"].values) # <class 'pandas._libs.tslibs.timestamps.Timestamp'>

Upvotes: 3

Always Right Never Left
Always Right Never Left

Reputation: 1481

Assuming your dates are always prefaced with 'M ' (meaning that the actual date starts at position 2 in the string):

df['date'] = df['date'].apply(lambda x: pd.to_datetime(x[2:], format = '%m.%Y'))

Upvotes: 1

Related Questions