5

I have date as string (example: 3/24/2020) that I would like to convert to datetime64[ns] format

df2['date'] = pd.to_datetime(df1["str_date"], format='%m/%d/%Y')

Use pandas to_datetime on vaex dataframe will result an error:

ValueError: time data 'str_date' does not match format '%m/%d/%Y' (match)

I have see maybe duplicate question.

df2['pdate']=df2.date.astype('datetime64[ns]')

However, the answer is type casting. My case required to a format ('%m/%d/%Y') parse string to datetime64[ns], not just type cast.

Solution: make custom function, then .apply

Haha TTpro
  • 5,137
  • 6
  • 45
  • 71

1 Answers1

6

vaex can use apply function for object operations, so you can use datetime and np.datetime64 convert each date string, then apply it.

import numpy as np
from datetime import datetime

def convert_to_datetime(date_string):
    return np.datetime64(datetime.strptime(str(date_string), "%Y%m%d%H%M%S"))

df['date']  = df.date.apply(convert_to_datetime)
Joey Gao
  • 850
  • 2
  • 7
  • 14
  • @ Joey Gao I tried using your solution on a similar problem where the string column has the format (%Y%m for example '201904') and it worked only when the column contains no blank cells. I was wondering how I could modify your code to solve my problem where there exist empty strings in the column. I'm also working with a Vaex dataset. – Omomaxi Feb 11 '22 at 23:03
  • So, I figured out a way around this but still building on top @Joey Gao solution. To solve it, I simply did the following on the function he created: def convert_to_datetime(date_string): for date in date_string: if date == '': np.nan else: return np.datetime64(datetime.strptime(date_string, "%Y%m$d%H%M%S")) – Omomaxi Feb 14 '22 at 15:09