0

I am trying to get pandas.to_datetime to give me the correct time relevant to my timezone and I can't find anything about how to do that.

This is the code I made:

def _new_values_list(arg1=list()):
    import time 
    keys = arg1[1]
    sym = arg1[0]
    """
    ==========================================================================
    :param: values returned by the seperate_key_value() in the form of a
    variable. Probably something like candle_data, candleD, etc.
    ==========================================================================
    :returns: a sorted pandas DataFrame with columns for the open, high, low
    close, volume and datetime as index. It also, includes a row with the
    epoch time for easier time series calculations.
    ==========================================================================
    """

    o = []
    for vals in keys: o.append(vals['open'])

    h = []
    for vals in keys: h.append(vals['high'])

    l = []
    for vals in keys: l.append(vals['low'])

    c = []
    for vals in keys: c.append(vals['close'])

    etime = []
#     for vals in keys: etime.append((vals['datetime'])/10000)
    for vals in keys: etime.append(vals['datetime'])

    time = []
    for vals in keys:
        vals = pd.to_datetime(vals['datetime'], unit='ms')
        time.append(vals)

    vol = []
    for vals in keys: vol.append(vals['volume'])

    df = pd.DataFrame({'unix_time':etime, 'datetime': time, 'open': o, 'high': h, 'low': l, 'close': c,'volume': vol})

    return df

Then this is the output:

          unix_time            datetime    open      high       low    close  \
0     1637332200000 2021-11-19 14:30:00  342.71  343.0700  342.2000  342.810   
1     1637332260000 2021-11-19 14:31:00  342.73  343.2000  342.7100  342.710   
2     1637332320000 2021-11-19 14:32:00  342.76  342.8900  342.3101  342.875   
3     1637332380000 2021-11-19 14:33:00  342.80  342.9965  342.6800  342.810   
4     1637332440000 2021-11-19 14:34:00  342.86  343.1100  342.8600  343.080   
...             ...                 ...     ...       ...       ...      ...   
3815  1638564900000 2021-12-03 20:55:00  321.68  322.4000  321.6100  322.320   
3816  1638564960000 2021-12-03 20:56:00  322.30  322.7000  322.1600  322.450   
3817  1638565020000 2021-12-03 20:57:00  322.42  323.0800  322.1100  323.050   
3818  1638565080000 2021-12-03 20:58:00  323.05  323.0773  322.8500  322.950   
3819  1638565140000 2021-12-03 20:59:00  322.96  323.4500  322.7300  323.180   

       volume       std    volume_std  
0     1813225  0.242681  76462.813531  
1       77239  0.242681  76462.813531  
2       66437  0.242681  76462.813531  
3       42748  0.242681  76462.813531  
4       52295  0.242681  76462.813531

  • datetime should be showing up as 08:30:00 but it isn't.

  • Can I change that? and if so how?

I tried to do new_data['datetime'].tz_convert('US/Central') and I received this error message:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-93-1843a60046f9> in <module>
      3 data = _values_list(rdata)
      4 new_data = _new_values_list(rdata)
----> 5 new_data['datetime'].tz_convert('US/Central')
      6 new_data['std'] = new_data['close'] - new_data['open']
      7 new_data['std'] = new_data['std'].std()

~\anaconda3\lib\site-packages\pandas\core\generic.py in tz_convert(self, tz, axis, level, copy)
   9773             if level not in (None, 0, ax.name):
   9774                 raise ValueError(f"The level {level} is not valid")
-> 9775             ax = _tz_convert(ax, tz)
   9776 
   9777         result = self.copy(deep=copy)

~\anaconda3\lib\site-packages\pandas\core\generic.py in _tz_convert(ax, tz)
   9755                 if len(ax) > 0:
   9756                     ax_name = self._get_axis_name(axis)
-> 9757                     raise TypeError(
   9758                         f"{ax_name} is not a valid DatetimeIndex or PeriodIndex"
   9759                     )

TypeError: index is not a valid DatetimeIndex or PeriodIndex
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
CMWolfe
  • 47
  • 7

1 Answers1

0

There is a method for that:

df['datetime'].dt.tz_convert('US/Central')

To get your time zone use:

import time
time.tzname[time.localtime().tm_isdst]

You may need to first set the original timezone to convert from:

df['datetime'].dt.tz_localize('US/Central').dt.tz_convert('Israel')

You won't need to do that if your timestamps already belong to a timezone:

df = pd.DataFrame({'datetime': pd.to_datetime(['2020-1-1 19:43'], utc=True)})
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
rudolfovic
  • 3,163
  • 2
  • 14
  • 38
  • I just tried that and it didn't work, I will edit the question to show you what that returned. – CMWolfe Dec 05 '21 at 09:21
  • `df['datetime'].dt.tz_localize('US/Central')` is wrong if `df['datetime']` is naive datetime that was converted from Unix time. Note that pandas (in contrast to native Python) does not have local time as the default. Instead, it will be UTC by default, check e.g. conversion from Unix time with `utc=True` vs. `utc=False`. – FObersteiner Dec 05 '21 at 10:43
  • correct would be e.g. `pd.to_datetime([1637332200000], unit='ms', utc=True).dt.tz_convert(time.tzname[time.localtime().tm_isdst])` – FObersteiner Dec 05 '21 at 10:45
  • Yeah that's what I've got - I just broke it down so that each step is clear on its own, thanks for the `utc=True` suggestion :) – rudolfovic Dec 05 '21 at 10:52