Is there a way to set pandas to default YYY-MM-DD instead of YYY-MM-DD 00:00:00?

Question

Can I set pandas to default YYYY-MM-DD, am getting YYYY-MM-DD 00:00:00 at the end? Is there a way to make sure by default that the zeros don't appear when I export to excel/csv?

Updated per comment request:

I have a function that looks like this:

x1 = my_funct('Unemployment', '2004-01-04 2009-01-04', 'DK', 'Unemployment (Denmark)')

Then I create a df out of it:

df1 = pd.DataFrame(x1)

along with others:

# this concats the df horizontally
df_merged1 = pd.concat([df1, df0, df2, df0, df3, df0, df4], axis=1)
df_merged1.reset_index(inplace=True)

Then I export that to excel:

writer = pd.ExcelWriter('Test1.xlsx', engine='xlsxwriter')

df_merged1.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']


# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})


# Write the column headers with the defined format.
for col_num, value in enumerate(df_merged1.columns.values):
    worksheet.write(0, col_num, value, header_format)


format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')
worksheet.set_column(0, 11, 30, format)
writer.save()
writer.close()

The exported excel file has multiple date columns each one showing the extra 00:00:00 at the end. Is it possible to have it only as YYYY-MM-DD?

Thanks

convert the date to string with `.dt.strftime('%Y-%m-%d')` before exporting? — Quang Hoang, Mar 04 '21 at 18:37
If you need more details, take a look [here](https://stackoverflow.com/questions/38067704/how-to-change-the-datetime-format-in-pandas) — Paulo Marques, Mar 04 '21 at 18:47
@QuangHoang I have kind of a unique situation, essentially I have a function that takes three parameters: x1 = my_funct('Unemployment', '2004-01-04 2009-01-04', 'DK', 'Unemployment (Denmark)')... I have x1, x2, x3, x4 that are different dates, then I create separate df's and merged them: df_merged1 = pd.concat([df1, df0, df2, df0, df3, df0, df4], axis=1)... and then run a to_excel function, the excel shows up as YYY-MM-DD 00:00:00 not sure why. — JohnReese23487, Mar 04 '21 at 18:51
@PauloMarques I have a unique situation (shown in the comment above). How to convert that kind of code to just YYYY-MM-DD? — JohnReese23487, Mar 04 '21 at 18:52
It is better to update your post with the code. However, if you exported to Excel and the issue is there, why not formatting the date in the Excel spreadsheet? — Paulo Marques, Mar 04 '21 at 19:02
@PauloMarques updated! It's a pretty large file, I'd rather have it send off to the user without them having to do any extra work. — JohnReese23487, Mar 04 '21 at 19:15

score 3 · Accepted Answer · answered Mar 05 '21 at 00:16

The solution is to create a writer

Creating a tiny dataframe for test

import pandas as pd
from datetime import datetime


df = pd.DataFrame([datetime(2021, 3, 4, 20, 48, 5)])

This is the dataframe so far:

                    0
0 2021-03-04 20:48:05

Creating the Writer

writer = pd.ExcelWriter("exemple.xlsx",  datetime_format='hh:mm:ss')

df.to_excel(writer, "Sheet1")

writer.close()

Note: I used hh:mm:ss but it could be any format.

If you need more details, see at ExcelWriter

The result would be:

THANK YOU!! This is solved my problem exactly! Much appreciated! — JohnReese23487, Mar 05 '21 at 17:50

Is there a way to set pandas to default YYY-MM-DD instead of YYY-MM-DD 00:00:00?

1 Answers1

Creating a tiny dataframe for test

Creating the Writer