I am using Python to save the data of date and datetime columns in specific format while storing it in a csv file.
我正在使用Python以特定格式保存Date和DateTime列的数据,同时将其存储在CSV文件中。
However, whenever I open the file in Microsoft Excel the format changes to the default format of Excel.
但是,每当我在Microsoft Excel中打开该文件时,格式都会更改为默认的Excel格式。
Is there a way to resolve this while keeping csv as format?
有没有办法在保持CSV格式的同时解决这个问题?
Thanks
谢谢
import pandas as pd
import os
from datetime import datetime
import csv
def convert_datetime(cell):
try:
if pd.notnull(cell):
return f"{datetime.strptime(f'{cell}', '%m/%d/%Y %I:%M:%S %p').strftime('%Y-%m-%d %H:%M:%S')}"
else:
return None
except ValueError:
return cell
def convert_date(cell):
try:
if pd.notnull(cell):
return f"{datetime.strptime(f'{cell}', '%m/%d/%Y').strftime('%Y-%m-%d')}"
else:
return None
except ValueError:
return cell
file_name = input("Enter the file name with extension (xlsx or csv): ")
if os.path.exists(file_name):
file_extension = file_name.split('.')[-1]
if file_extension == 'xlsx':
df = pd.read_excel(file_name, engine='openpyxl')
elif file_extension == 'csv':
df = pd.read_csv(file_name)
datetime_columns = ['created', 'created_date', 'modify_date']
date_columns = ['offers_expiry']
for col in date_columns:
if col in df.columns:
df[col] = df[col].fillna(pd.Timestamp.now())
df[col] = df[col].apply(convert_date)
for col in datetime_columns:
if col in df.columns:
df[col] = df[col].fillna(pd.Timestamp.now())
df[col] = df[col].apply(convert_datetime)
df[col] = df[col].apply(lambda x: x.replace(microsecond=0))
output_file_name = "modified_" + os.path.splitext(file_name)[0] + '.csv'
df.to_csv(output_file_name, index=False, header=False)
print(f"Modified file saved as {output_file_name}")
else:
print("File not found.")
更多回答
优秀答案推荐
Is there a way to resolve this while keeping csv as format?
Unfortunately not. If you want to control the output format, you have to export your data as excel to set the format with ExcelWriter
. Something like:
不幸的是没有。如果您想控制输出格式,您必须将数据导出为EXCEL,以便使用ExcelWriter设置格式。类似于:
# Read your data
df = pd.DataFrame({'created': ['09/10/2023 07:34:23 AM'],
'offers_expiry': ['09/10/2023']})
# For datetime columns (your function should return DatetimeIndex)
df['created'] = pd.to_datetime(df['created'])
# For date columns (your function should return a Series with date objects)
df['offers_expiry'] = pd.to_datetime(df['offers_expiry']).dt.date
# Export your data
with pd.ExcelWriter('output.xlsx',
date_format='DD MM YYYY',
datetime_format='DD/MM/YYYY HH:MM:SS',
engine='xlsxwriter'
) as writer:
df.to_excel(writer, index=False)
The output respects the date and datetime formats:
输出遵循日期和日期时间格式:
更多回答
我是一名优秀的程序员,十分优秀!