Python Pandas: detecting frequency of time series

Question

Assume I have loaded time series data from SQL or CSV (not created in Python), the index would be:

DatetimeIndex(['2015-03-02 00:00:00', '2015-03-02 01:00:00',
               '2015-03-02 02:00:00', '2015-03-02 03:00:00',
               '2015-03-02 04:00:00', '2015-03-02 05:00:00',
               '2015-03-02 06:00:00', '2015-03-02 07:00:00',
               '2015-03-02 08:00:00', '2015-03-02 09:00:00', 
               ...
               '2015-07-19 14:00:00', '2015-07-19 15:00:00',
               '2015-07-19 16:00:00', '2015-07-19 17:00:00',
               '2015-07-19 18:00:00', '2015-07-19 19:00:00',
               '2015-07-19 20:00:00', '2015-07-19 21:00:00',
               '2015-07-19 22:00:00', '2015-07-19 23:00:00'],
              dtype='datetime64[ns]', name=u'hour', length=3360, freq=None, tz=None)

As you can see, the freq is None. I am wondering how can I detect the frequency of this series and set the freq as its frequency. If possible, I would like this to work in the case of data which isn't continuous (there are plenty of breaks in the series).

I was trying to find the mode of all the differences between two timestamps, but I am not sure how to transfer it into a format that is readable by Series

If there are gaps, is freq set by the smallest difference two timestamps? — mdurant, Jul 20 '15 at 13:39
@mdurant yes, the differences of most two timestamps are same as smallest differences — Jim, Jul 20 '15 at 13:42

score 15 · Answer 1 · answered May 14 '17 at 12:31

15

It is worth mentioning that if data is continuous, you can use pandas.DateTimeIndex.inferred_freq property:

dt_ix = pd.date_range('2015-03-02 00:00:00', '2015-07-19 23:00:00', freq='H')
dt_ix._set_freq(None)
dt_ix.inferred_freq
Out[2]: 'H'

or pandas.infer_freq method:

pd.infer_freq(dt_ix)
Out[3]: 'H'

If not continuous pandas.infer_freq will return None. Similarly to what has been proposed yet, another alternative is using pandas.Series.diff method:

split_ix = dt_ix.drop(pd.date_range('2015-05-01 00:00:00','2015-05-30 00:00:00', freq='1H'))
split_ix.to_series().diff().min()
Out[4]: Timedelta('0 days 01:00:00')

answered May 14 '17 at 12:31

Delforge

792
7
17

can you return the frequency in integer instead of a string? For example 60 instead of 'H' – k.ko3n Jan 03 '20 at 20:05
You can have look to https://stackoverflow.com/questions/31469811/convert-pandas-freq-string-to-timedelta . Then you can check how to retrieve the proper integer depending on the time unit. – Delforge Jan 03 '20 at 22:55
Rather than finding the minimum diff value, it seems to me more accurate to find the ***most frequent*** diff value: **`split_ix.to_series().diff().value_counts().idxmax()`**. – Daniel Goldfarb Apr 07 '21 at 00:42

Jianxun Li · Accepted Answer · 2015-07-20T13:58:09.857

Maybe try taking difference of the timeindex and use the mode (or smallest difference) as the freq.

import pandas as pd
import numpy as np

# simulate some data
# ===================================
np.random.seed(0)
dt_rng = pd.date_range('2015-03-02 00:00:00', '2015-07-19 23:00:00', freq='H')
dt_idx = pd.DatetimeIndex(np.random.choice(dt_rng, size=2000, replace=False))
df = pd.DataFrame(np.random.randn(2000), index=dt_idx, columns=['col']).sort_index()
df

                        col
2015-03-02 01:00:00  2.0261
2015-03-02 04:00:00  1.3325
2015-03-02 05:00:00 -0.9867
2015-03-02 06:00:00 -0.0671
2015-03-02 08:00:00 -1.1131
2015-03-02 09:00:00  0.0494
2015-03-02 10:00:00 -0.8130
2015-03-02 11:00:00  1.8453
...                     ...
2015-07-19 13:00:00 -0.4228
2015-07-19 14:00:00  1.1962
2015-07-19 15:00:00  1.1430
2015-07-19 16:00:00 -1.0080
2015-07-19 18:00:00  0.4009
2015-07-19 19:00:00 -1.8434
2015-07-19 20:00:00  0.5049
2015-07-19 23:00:00 -0.5349

[2000 rows x 1 columns]

# processing
# ==================================
# the gap distribution
res = (pd.Series(df.index[1:]) - pd.Series(df.index[:-1])).value_counts()

01:00:00    1181
02:00:00     499
03:00:00     180
04:00:00      93
05:00:00      24
06:00:00      10
07:00:00       9
08:00:00       3
dtype: int64

# the mode can be considered as frequency
res.index[0]  # output: Timedelta('0 days 01:00:00')
# or maybe the smallest difference
res.index.min()  # output: Timedelta('0 days 01:00:00')




# get full datetime rng
full_rng = pd.date_range(df.index[0], df.index[-1], freq=res.index[0])
full_rng

DatetimeIndex(['2015-03-02 01:00:00', '2015-03-02 02:00:00',
               '2015-03-02 03:00:00', '2015-03-02 04:00:00',
               '2015-03-02 05:00:00', '2015-03-02 06:00:00',
               '2015-03-02 07:00:00', '2015-03-02 08:00:00',
               '2015-03-02 09:00:00', '2015-03-02 10:00:00', 
               ...
               '2015-07-19 14:00:00', '2015-07-19 15:00:00',
               '2015-07-19 16:00:00', '2015-07-19 17:00:00',
               '2015-07-19 18:00:00', '2015-07-19 19:00:00',
               '2015-07-19 20:00:00', '2015-07-19 21:00:00',
               '2015-07-19 22:00:00', '2015-07-19 23:00:00'],
              dtype='datetime64[ns]', length=3359, freq='H', tz=None)

score 8 · Answer 3 · answered Jul 20 '15 at 14:39

8

The minimum time difference is found with

np.diff(data.index.values).min()

which is normally in units of ns. To get a frequency, assuming ns:

freq = 1e9 / np.diff(df.index.values).min().astype(int)

answered Jul 20 '15 at 14:39

mdurant

27,272
5
45
74

Python Pandas: detecting frequency of time series

3 Answers3

Linked