Obtengo 2 comportamientos diferentes al aplicar rolling ("1D"). Max () en 2 conjuntos de datos en el cuaderno Jupyter.

Necesito calcular el máximo rodante para cada día.

Sample:
df = pd.DataFrame({'B': [0, 4, 3, 3, 4, 2, 1, 2, 3, 4]},
                  index = [pd.Timestamp('20130101 09:00:00'),
                           pd.Timestamp('20130101 09:02:02'),
                           pd.Timestamp('20130101 09:03:03'),
                           pd.Timestamp('20130101 09:04:05'),
                           pd.Timestamp('20130101 09:15:06'),                          
                           pd.Timestamp('20130102 09:16:06'),
                           pd.Timestamp('20130102 09:17:06'),
                           pd.Timestamp('20130102 09:35:06'),
                           pd.Timestamp('20130102 09:36:06'),
                           pd.Timestamp('20130102 09:37:06')])

df.rolling("1D").max() #gives desired output

                        B
2013-01-01 09:00:00     0.0
2013-01-01 09:02:02     4.0
2013-01-01 09:03:03     4.0
2013-01-01 09:04:05     4.0
2013-01-01 09:15:06     4.0
2013-01-02 09:16:06     2.0 # <- 2 is the highest value for new day
2013-01-02 09:17:06     2.0
2013-01-02 09:35:06     2.0
2013-01-02 09:36:06     3.0
2013-01-02 09:37:06     4.0

Cuando intento aplicar a datos reales, obtengo

# Sample data
data = '{"High":{"1611221400000":0.99615,"1611222300000":0.9751,"1611223200000":1.035,"1611224100000":0.9894,"1611225000000":1.385,"1611225900000":1.345,"1611226800000":1.235,"1611227700000":1.245,"1611228600000":1.315,"1611229500000":1.295,"1611230400000":1.28,"1611231300000":1.295,"1611232200000":1.415,"1611233100000":1.415,"1611234000000":1.355,"1611234900000":1.385,"1611235800000":1.335,"1611236700000":1.325,"1611237600000":1.365,"1611238500000":1.445,"1611239400000":1.515,"1611240300000":1.475,"1611241200000":1.405,"1611242100000":1.375,"1611243000000":1.255,"1611243900000":1.225,"1611307800000":1.375,"1611308700000":1.415,"1611309600000":1.495}}'
df2 = pd.read_json(data)

df2.rolling("1D").max()
# keeps rolling from previous day

    High
Date    
2021-01-21 09:30:00     0.99615
2021-01-21 09:45:00     0.99615
2021-01-21 10:00:00     1.03500
2021-01-21 10:15:00     1.03500
2021-01-21 10:30:00     1.38500
2021-01-21 10:45:00     1.38500
2021-01-21 11:00:00     1.38500
2021-01-21 11:15:00     1.38500
2021-01-21 11:30:00     1.38500
2021-01-21 11:45:00     1.38500
2021-01-21 12:00:00     1.38500
2021-01-21 12:15:00     1.38500
2021-01-21 12:30:00     1.41500
2021-01-21 12:45:00     1.41500
2021-01-21 13:00:00     1.41500
2021-01-21 13:15:00     1.41500
2021-01-21 13:30:00     1.41500
2021-01-21 13:45:00     1.41500
2021-01-21 14:00:00     1.41500
2021-01-21 14:15:00     1.44500
2021-01-21 14:30:00     1.51500
2021-01-21 14:45:00     1.51500
2021-01-21 15:00:00     1.51500
2021-01-21 15:15:00     1.51500
2021-01-21 15:30:00     1.51500
2021-01-21 15:45:00     1.51500
2021-01-22 09:30:00     1.51500 # <- value got rolled from previous day
2021-01-22 09:45:00     1.51500
2021-01-22 10:00:00     1.51500

Versión de Pandas = 0.25.1

Ambos DF tienen DatetimeIndex, dtype = 'datetime64 [ns]', freq = None

¿Alguna idea de por qué está sucediendo esto?

1
Biarys 22 ene. 2021 a las 18:34

2 respuestas

La mejor respuesta

En ambos casos, la ventana móvil abre un filtro de un día (equivale a 24 horas).

Cambié un poco su primer ejemplo, vea el resultado:

df = pd.DataFrame({'B': [0, 4, 3, 3, 4, 2, 1, 2, 3, 4]},
                  index = [pd.Timestamp('20130101 09:00:00'),
                           pd.Timestamp('20130101 09:02:02'),
                           pd.Timestamp('20130101 09:03:03'),
                           pd.Timestamp('20130101 09:04:05'),
                           pd.Timestamp('20130101 09:15:06'),                          
                           pd.Timestamp('20130102 09:13:06'), # <-- minus 3 minutes
                           pd.Timestamp('20130102 09:17:06'),
                           pd.Timestamp('20130102 09:35:06'),
                           pd.Timestamp('20130102 09:36:06'),
                           pd.Timestamp('20130102 09:37:06')])
df.rolling("1D").max()
>>> 
                       B
2013-01-01 09:00:00  0.0
2013-01-01 09:02:02  4.0
2013-01-01 09:03:03  4.0
2013-01-01 09:04:05  4.0
2013-01-01 09:15:06  4.0
2013-01-02 09:13:06  4.0 # <-- overlap of days
2013-01-02 09:17:06  2.0
2013-01-02 09:35:06  2.0
2013-01-02 09:36:06  3.0
2013-01-02 09:37:06  4.0

Esto significa que en ambos casos el rolling está haciendo lo mismo.

Si desea obtener el máximo rodante por día, tal vez desee hacer algo como esto:

df = df.groupby(df.index.day).rolling('1D').max()

Y

df2 = df2.groupby(df2.index.day).rolling('1D').max()

Que le devolverá un DataFrame con MultiIndex.

El MultiIndex se puede reducir en el siguiente paso usando

df.index = df.index.droplevel(0) 

Y

df2.index = df2.index.droplevel(0) 
1
mosc9575 22 ene. 2021 a las 16:28

La ventana móvil de un día ('1D') no es de medianoche a medianoche, pero cubre un lapso de 24 horas, independientemente de los cambios de fecha. Puedes ver esto cuando lo haces:

def fun(x):
    print(x.index[0], x.index[-1])
    return len(x)
df2.rolling("1d").apply(fun)

Entonces, lo que necesitas es df2.set_index(df2.index.normalize()).rolling("1d").max():

               High
2021-01-21  0.99615
2021-01-21  0.99615
2021-01-21  1.03500
2021-01-21  1.03500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.38500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.41500
2021-01-21  1.44500
2021-01-21  1.51500
2021-01-21  1.51500
2021-01-21  1.51500
2021-01-21  1.51500
2021-01-21  1.51500
2021-01-21  1.51500
2021-01-22  1.37500
2021-01-22  1.41500
2021-01-22  1.49500
1
Stef 22 ene. 2021 a las 16:18
65848107