I have pandas data frame with int64 , object , and datetime64[ns] data types. How to preserve those data types when exporting pandas DataFrame.to_Excel option?
I want exported Excel file columns looks like this:
int64 Number format in Excel
object Text format in Excel
datetime64[ns] Date format in Excel
Right now all of my Excel column format shows as General
You can convert a Pandas dataframe to an Excel file with column formats using Pandas and XlsxWriter.
Here is an example of what you can do: Pandas Excel output with column formatting
For more possibilities, have a look at this page, especially the Format methods and Format properties section: The Format Class
I have pandas data frame with int64 , object , and datetime64[ns] data types. How to preserve those data types when exporting pandas DataFrame.to_Excel option?
The short answer is that you can't.
Excel does't have as many datatypes as Python and far fewer than Pandas. For example the only numeric type it has is a IEEE 754 64bit double. Therefore you won't be able be able to store a int64 without losing information (unless the integer values are <= ~15 digits). Dates and times are are also stored in the same double format and only with millisecond resolution. So you won't be able to store datetime64[ns].
You could store them in string format but you won't be able to use them for calculations and Excel will complain about "Numbers stored as strings".
