I have a pandas dataframe containing columns in timedelta64[ns] format. For this project I cannot use df.to_excel() and I need to import the dataframe via xlwings so that it prints into existing Excel Workbook and keeps its format.

When I try the usual:

workbook_sablona_dochazka.sheets[zamestnanec].range('A1').options(index=False).value = individual_dochazka_zamestnanec

I receive error: TypeError: must be real number, not Timedelta

Is there a way to format my timedelta64[ns] so that xlwings would be able to import the dataframe in? I need to preserve my time values so that it becomes 12:30:00 in Excel again after xlwings import and maybe some after-formatting inside the Excel itself.

I tried:

individual_dochazka_zamestnanec['Příchod do práce'] = individual_dochazka_zamestnanec['Příchod do práce'].values.astype(float)

This worked around the error but imported columns had totally out of sense numbers.

Any idea how to work around this?

Thank you very much in advance!

CodePudding user response：

If the error says it's a "TimeDelta", then you have to ask delta relative to what? Usually a TimeDelta indicates something like "three hours" or "minus two days". You say you'd like the output to be "12:30:00" is that an actual time or does it mean 12 hours 30 minutes and no seconds?

You could try making the TimeDelta relative to "the beginning of time" so that it's a date which can be imported into xlwings but is formatted like a time as suggested here.

CodePudding user response：

Just managed to figure it out.

The trick was to first format timedelta64[ns] columns as string and then trim it a bit with .map(lambda x:str(x)[7:])) so that I would get that nice time only stamp.

individual_dochazka_zamestnanec['Počet odpracovaných hodin celkem'] = ((individual_dochazka_zamestnanec['Počet odpracovaných hodin celkem']).astype(str)).map(lambda x: str(x)[7:])

To my surprise, Excel accepted this without issue which is exactly what I needed.

Hope this helps someone, sometime.

Cheers!