Date conversion in pandas dataframe

Question

My data frame looks like -

date        cnt
2020-05-09  301
2020-07-09  402
2020-08-09  403
2020-09-09  402
2020-09-14  512
2020-10-09  403
2020-11-09  403

I want my data frame looks like -

date        cnt
2020-09-05  301
2020-09-07  402
2020-09-08  403
2020-09-09  402
2020-09-14  512
2020-09-10  403
2020-09-11  403

On 14th september i.e 2020-09-14 is correct but rest of the date I need to convert in '%Y-%m-%d'.How to do in python?My code is given below -

df['date'] = pd.df(df['date'].astype(str), format='%Y-%m-%d')

Does this answer your question? [How to change the datetime format in pandas](https://stackoverflow.com/questions/38067704/how-to-change-the-datetime-format-in-pandas) — Riccardo Bucco, Oct 03 '20 at 08:16
Please post a sample of the raw data, as it comes from the file. — Trenton McKinney, Oct 03 '20 at 08:39

David · Accepted Answer · 2020-10-03T08:14:44.153

0

You can do the following:

from datetime import datetime, timedelta
from pandas import DataFrame
import pandas as pd
df = DataFrame.from_dict(
    {'Alfa': [1, 2, 3],
     'Bravo': [4, 5, 6],
     'Date': [datetime.strftime(datetime.now()-timedelta(days=_), "%m/%d/%Y") for _ in range(3)]},
    orient='index',
    columns=['A', 'B', 'C']).T
print(df)
#   Alfa Bravo        Date
#  A    1     4  10/03/2020
#  B    2     5  10/02/2020
#  C    3     6  10/01/2020

df['Date'] = pd.to_datetime(df.Date).dt.strftime('%Y-%m-%d')
print(df)
#   Alfa Bravo        Date
#  A    1     4  2020-10-03
#  B    2     5  2020-10-02
#  C    3     6  2020-10-01

edited Oct 03 '20 at 08:14

answered Oct 03 '20 at 08:03

David

8,113
2
17
36

It's not working...no change in my data frame – John Davis Oct 03 '20 at 08:10
@JohnDavis you didnt supply your data, so I can't seem to understand the result that you get. Maybe you can add it. The solution I provided solve the issue in my specific example. – David Oct 03 '20 at 08:15
@JohnDavis Did you try my solution? What kind of errors did you encounter if any? – David Oct 04 '20 at 04:16
@JohnDavis thanks for the accept. I would also appreciate an upvote if possible – David Oct 04 '20 at 05:25

Mike · Answer 2 · 2020-10-03T09:24:20.097

You didn't provide enough details in your question, but if your date column is already a date value rather than a string value, I'm guessing your problem was during the initial load of the dates.

If you loaded your data via pandas pd.read_csv() then there are a lot of options for loading dates, including options that will try to detect the date format automatically. Several (but not all rows) in your sample data would confuse this automatic detection (it can't tell which part is the month or day).

If the date column is already a date value, then

df['date'] = pd.to_datetime(df['date'].astype(str), format='%Y-%m-%d')

will not do anything useful.

If you did use pd.read_csv() and the dates are YYYY-MM-DD on disk try using this instead:

import pandas as pd
import numpy as np
myDateLoader = lambda d: np.datetime64('NaT') if d == '' or d == 'NULL' or d.startswith('9999-12-31') else np.datetime64(datetime.strptime(d[:10], '%Y-%m-%d'))
df = pd.read_csv('file.csv', converters={'date': myDateLoader})

If they are not YYYY-MM-DD on disk, then adjust the above format as needed.

Date conversion in pandas dataframe

2 Answers2