0

I have a timezone aware TimeSeries in pandas 0.10.1. I want to export to Excel, but the timezone prevents the date from being recognized as a date in Excel.

In [40]: resultado
Out[40]: 
fecha_hora
2013-04-11 13:00:00+02:00    31475.568
2013-04-11 14:00:00+02:00    37263.072
2013-04-11 15:00:00+02:00    35979.434
2013-04-11 16:00:00+02:00    35132.890
2013-04-11 17:00:00+02:00    36356.584

If I strip the tzinfo with .tz_convert(None), the date gets converted to UTC:

In [41]: resultado.tz_convert(None)
Out[41]: 
fecha_hora
2013-04-11 11:00:00    31475.568
2013-04-11 12:00:00    37263.072
2013-04-11 13:00:00    35979.434
2013-04-11 14:00:00    35132.890
2013-04-11 15:00:00    36356.584

Is there a TimeSeries method to apply .replace(tzinfo=None) to each date in the index?

Alternativelly, is there a way to properly export time-aware TimeSeries to Excel?

rapto
  • 405
  • 3
  • 15

1 Answers1

2

You can simply create a copy without timezone.

import pandas as pa

time = pa.Timestamp('2013-04-16 10:08', tz='Europe/Berlin')
time_wo_tz = pa.datetime(year=time.year, month=time.month, day=time.day, 
                         hour=time.hour, minute=time.minute, second=time.second,
                         microsecond=time.microsecond)

When you want to convert the whole index of the timeseries, use a list comprehension.

ts.index = [pa.datetime(year=x.year, month=x.month, day=x.day, 
                        hour=x.hour, minute=x.minute, second=x.second, 
                        microsecond=x.microsecond) 
            for x in ts.index]
Tim Tröndle
  • 465
  • 5
  • 12
  • Actually, I didn't want to loop over the index. I believe .replace is clearer than using each component to recreate the date. – rapto Apr 17 '13 at 10:24
  • Ok. But since datetimes are immutable (and should be immutable), you probably won't find such a function doing this without creating a copy. What could help is some sort of converting function while writing to the csv. While this seem to exist for `write_csv`, it does not for `to_csv` and `to_excel`. – Tim Tröndle Apr 17 '13 at 12:28