python - Pandas Xlsxwriter 时间格式

Question

我正在尝试使用 xlsxwriter 写出我的 pandas 表。我有两列:

Date       | Time  
10/10/2015  8:57
11/10/2015  10:23

但是当我使用 xlsxwriter 时，输出是:

Date       | Time
10/10/2015  0.63575435
11/10/2015  0.33256774

我尝试使用 datetime_format = 'hh:mm:ss' 但这并没有改变它。我还能如何在不影响日期列的情况下正确设置日期格式？

Answer 1

以下代码适用于我，但有一些注意事项。自定义格式是否有效取决于您打开它时使用的 Windows/Excel 版本。 Excel 自定义格式取决于 Windows 操作系统的语言设置。

Excel custom formatting

Windows date/time settings

是的，这不是最好的解决方案...但我们的想法是更改每一列的格式，而不是更改如何解释正在创建的整个 Excel 文件的数据类型。

import pandas as pd
from datetime import datetime, date

# Create a Pandas dataframe from some datetime data.
df = pd.DataFrame({'Date and time': [date(2015, 1, 1),
                                     date(2015, 1, 2),
                                     date(2015, 1, 3),
                                     date(2015, 1, 4),
                                     date(2015, 1, 5)],
                   'Time only':     ["11:30:55",
                                     "1:20:33",
                                    "11:10:00",
                                     "16:45:35",
                                    "12:10:15"],
                   })


df['Time only'] = df['Time only'].apply(pd.to_timedelta)
#df['Date and time'] = df['Date and time'].apply(pd.to_datetime)


# Create a Pandas Excel writer using XlsxWriter as the engine.
# Also set the default datetime and date formats.
writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects in order to set the column
# widths, to make the dates clearer.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

#PLAY AROUND WITH THE NUM_FORMAT, IT DEPENDS ON YOUR WINDOWS AND EXCEL DATE/TIME SETTINGS WHAT WILL WORK
# Add some cell formats.
format1 = workbook.add_format({'num_format': 'd-mmm-yy'})
format2 = workbook.add_format({'num_format': "h:mm:ss"})

# Set the format

worksheet.set_column('B:B', None, format1)

worksheet.set_column('C:C', None, format2)

worksheet.set_column('B:C', 20)

# Close the Pandas Excel writer and output the Excel file.
writer.save()