0

How to change format date from 12-Mar-2022 to , format='%d/%m/%Y' in python

so the problem is I read data from the google sheet where in the data contain multiple format, some of them is 12/03/2022 and some of them 12-Mar-2022.

I tried using this got error of couse because doesn't match for 12-Mar-2022 defectData_x['date'] = pd.to_datetime(defectData_x['date'], format='%d/%m/%Y')

Appreciate your help

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
zoezanis
  • 1
  • 1
    Did you just try `pd.to_datetime(defectData_x['date'])` without `format` to let Pandas guess the right format? – Corralien Mar 13 '22 at 07:49
  • Can you update your import process to pull the date unformatted from your google sheet? That would solve any formatting issues. See similar example https://stackoverflow.com/questions/42216491/how-to-read-value-of-fetched-cell-data-as-date-google-sheets-api – Stephan Mar 13 '22 at 08:31
  • Please provide enough code so others can better understand or reproduce the problem. – Community Mar 13 '22 at 10:37

1 Answers1

0
defectData_x['date1'] = defectData_x['date'].dt.strftime('%d/%m/%Y')

don forget date1's dtype is not datetime but object

so it is better using date column and date1 column both before make final result

after final result, you can drop date column

add my example:

import pandas as pd
df = pd.DataFrame(["12/03/2022", "12-Mar-2022"], columns=["date"])
df["date1"] = pd.to_datetime(df["date"])
df['date2'] = df['date1'].dt.strftime('%d/%m/%Y')
Khai Kim
  • 66
  • 3
  • still error, because they still found format 8 Mar 2022, so its not match with this format '%d/%m/%Y' error says: ValueError: time data '8-Mar-2022' does not match format '%d/%m/%Y' (match) – zoezanis Mar 13 '22 at 08:25
  • aha. now im understanding your question. 8-Mar-2022 is "%d-%b%-%Y " format. sorry for my poor english. – Khai Kim Mar 13 '22 at 11:01
  • now add my example for you. my english is poor, but my mind for you is not poor. if still im misunderstanding problem, please tell me problem 1 more. – Khai Kim Mar 13 '22 at 11:20
  • How if in the same dataframe contain multiple format? – zoezanis Mar 13 '22 at 13:07
  • i add my example for you. example have multiple format. – Khai Kim Mar 13 '22 at 13:33