0

I'm reading in a csv file into a new dataframe "df" using

df = pd.read_csv(r'C:\projects\tstr_results.csv',index_col=None)

The file has a column 'date' that is in a format of 4-Nov-2021. df.dtypes shows 'date' to be an object.

I used the following command to the column into a datetime stamp: df['date'] = pd.to_datetime(df['date'], format='%d-%b-%Y')

However, df['date'] shows the date to be 2021-11-04 and as a dtype of datetime64[ns].

Am I missing a parameter to get to the desired format of 04-Nov-2021?

buran
  • 13,682
  • 10
  • 36
  • 61
R DesGr
  • 1
  • 2
  • 4
    You converted your string to a timestamp object. Then you display it. By default its displays it as %Y-%m-%d. You can print it a you wish. See https://stackoverflow.com/a/38067805/2681662 – MSH Nov 07 '21 at 19:24
  • `format` in `to_datetime` is only to `parse` value from `string` to `datetime` - not to format when you display it. You would have to create new column with strings instead of `datetime` and then you can use `strftime` (`string format time`) to create strings with expected `format`. But when you will have strings then you can't make calculations on dates - you can't sort by date, substract date, etc. – furas Nov 07 '21 at 23:29
  • Thank you, MSH and fursa. The link to the previous posting on this helped. Unfortunately, since I'm creating a pivot table, I do need the ability act on the date as a date and not string. I've ended up generating my pivot table and then writing it to a CSV file to read into Excel. There I can format the date as needed for my report. I was hoping to do everything in python without using Excel. Thank you again for your help. – R DesGr Nov 12 '21 at 18:10

1 Answers1

0

You can set the right format directly while reading the csv with keyword arguments parse_dates and date_parser:

df = pd.read_csv(r'C:\projects\tstr_results.csv',index_col=None, parse_dates=['date'], date_parser=lambda d: pd.Timestamp(d).strftime("%d-%b-%Y"))
Tranbi
  • 11,407
  • 6
  • 16
  • 33
  • Thank you, Tranbi, for this. I modified my pd.read_csv and found it turn 'date' into a datetime64[ns] data type. But the format didn't take. It was still 2021-11-04. I then tried using style.format df.style.format({"date": lambda t: t.strftime("%d-%b-%Y")}) on the date as an object and as datetime 64. Either way, it still turned into year-month-day. To get my report done, I've exported my results to a csv and used Excel to pretty-up the date to my boss's needs. – R DesGr Nov 12 '21 at 18:15