Para el siguiente marco de datos, quiero volver a calcular value si predicted es igual a 1, se calculará en función de pct de la fecha actual y value de la anterior fecha.

   city district     date  value       pct  predicted
0     a        c  2019-09   9.48  0.004237          0
1     a        c  2019-10   9.35 -0.013713          0
2     a        c  2019-11   9.05 -0.032086          0
3     a        c  2019-12   9.04 -0.001105          1    --> need to recalculate values based on  pct and previous values
4     a        c  2020-01   8.80 -0.020000          1    --> need to recalculate values based on  pct and previous values
5     a        c  2020-02   8.91  0.012500          1    --> need to recalculate values based on  pct and previous values
6     b        d  2019-09   9.48  0.004237          0
7     b        d  2019-10   9.35 -0.013713          0
8     b        d  2019-11   9.05 -0.032086          0
9     b        d  2019-12   9.04 -0.001105          1    --> need to recalculate values based on  pct and previous values
10    b        d  2020-01   8.80 -0.020000          1   --> need to recalculate values based on  pct and previous values
11    b        d  2020-02   8.91  0.012500          1   --> need to recalculate values based on  pct and previous values

Intento con el siguiente código, pero el resultado parece diferente de lo que calculo con la fórmula de Excel:

df.loc[df["predicted"]==1, "value"] = np.nan
df['value'] = df['value'].ffill().mul(df['pct']).add(df['value'].ffill(), fill_value=0)
print(df)

Salida:

   district     date     value       pct  predicted
0         c  2019-09  9.520169  0.004237          0
1         c  2019-10  9.221783 -0.013713          0
2         c  2019-11  8.759626 -0.032086          0
3         c  2019-12  9.040000 -0.001105          1
4         c  2020-01  8.869000 -0.020000          1
5         c  2020-02  9.163125  0.012500          1
6         d  2019-09  9.520169  0.004237          0
7         d  2019-10  9.221783 -0.013713          0
8         d  2019-11  8.759626 -0.032086          0
9         d  2019-12  9.040000 -0.001105          1
10        d  2020-01  8.869000 -0.020000          1
11        d  2020-02  9.163125  0.012500          1

La fórmula que uso para el cálculo value en 2019-12: value en 2019-12 = (1 + pct en 2019-12) * {{X6} } en 2019-11, la misma lógica para otros meses.

   district     date    value       pct  predicted
0         c  2019-09  9.48000  0.004237          0
1         c  2019-10  9.35000 -0.013713          0
2         c  2019-11  9.05000 -0.032086          0
3         c  2019-12  9.04000 -0.001105          1
4         c  2020-01  8.85920 -0.020000          1
5         c  2020-02  8.96994  0.012500          1
6         d  2019-09  9.48000  0.004237          0
7         d  2019-10  9.35000 -0.013713          0
8         d  2019-11  9.05000 -0.032086          0
9         d  2019-12  9.04000 -0.001105          1
10        d  2020-01  8.85920 -0.020000          1
11        d  2020-02  8.96994  0.012500          1

¿Cómo puedo corregir mi código? Gracias.

Actualizado:

Df:

   city district     date    value       pct  predicted
0     a        c  2018-12  10.1700       NaN          0
1     a        c  2019-01   9.9900 -0.017699          0
2     a        c  2019-02  10.6600  0.067067          0
3     a        c  2019-03  10.5600 -0.009381          0
4     a        c  2019-04  10.0600 -0.047348          0
5     a        c  2019-05  10.6900  0.062624          0
6     a        c  2019-06  10.7700  0.007484          0
7     a        c  2019-07  10.6700 -0.009285          0
8     a        c  2019-08  10.5100 -0.014995          0
9     a        c  2019-09  10.2800 -0.021884          0
10    a        c  2019-10  10.0500 -0.022374          0
11    a        c  2019-11   9.7200 -0.032836          0
12    a        c  2019-12   9.8400  0.012346          1
13    a        c  2020-01  10.0368  0.020000          1
14    a        c  2020-02  10.3500 -0.004808          1
15    a        c  2020-03  10.1430 -0.020000          1
16    a        c  2020-04   9.8882 -0.020000          1
17    a        c  2020-05   9.5256 -0.020000          1
18    a        c  2020-06   8.9572 -0.020000          1
19    a        c  2020-07   9.0882  0.020000          1
20    a        c  2020-08   9.3024  0.020000          1
21    a        c  2020-09   9.9042  0.020000          1
22    a        c  2020-10  10.1000 -0.001976          1
23    a        c  2020-11   9.8980 -0.020000          1
24    b        d  2018-12   6.3200       NaN          0
25    b        d  2019-01   6.3200  0.000000          0
26    b        d  2019-02   6.3200  0.000000          0
27    b        d  2019-03   6.3200  0.000000          0
28    b        d  2019-04   6.3200  0.000000          0
29    b        d  2019-05   6.3200  0.000000          0
30    b        d  2019-06   6.0000 -0.050633          0
31    b        d  2019-07   6.0000  0.000000          0
32    b        d  2019-08   6.0000  0.000000          0
33    b        d  2019-09   6.0000  0.000000          0
34    b        d  2019-10   6.0000  0.000000          0
35    b        d  2019-11   6.0000  0.000000          0
36    b        d  2019-12   5.7800 -0.020000          1
37    b        d  2020-01   5.8956  0.020000          1
38    b        d  2020-02   5.7820 -0.020000          1
39    b        d  2020-03   5.7936  0.020000          1
40    b        d  2020-04   5.7428 -0.020000          1
41    b        d  2020-05   5.7222  0.020000          1
42    b        d  2020-06   5.7428 -0.020000          1
43    b        d  2020-07   5.5386  0.020000          1
44    b        d  2020-08   5.7820 -0.020000          1
45    b        d  2020-09   5.3142  0.020000          1
46    b        d  2020-10   5.8898 -0.020000          1
47    b        d  2020-11   5.0490  0.020000          1

Después de ejecutar el siguiente código:

m = df["predicted"]==1
s = df[m].groupby('district')['value'].shift()
df['value'] = (1 + df['pct']).mul(s).fillna(df['value'])

df['new_pct'] = df.groupby('city')['value'].apply(lambda x: x.pct_change())
print(df)

Normalmente las columnas pct y new_pct deben tener los mismos valores, pero puede ver que en algunas filas son diferentes.

   city district     date      value       pct  predicted   new_pct
0     a        c  2018-12  10.170000       NaN          0       NaN
1     a        c  2019-01   9.990000 -0.017699          0 -0.017699
2     a        c  2019-02  10.660000  0.067067          0  0.067067
3     a        c  2019-03  10.560000 -0.009381          0 -0.009381
4     a        c  2019-04  10.060000 -0.047348          0 -0.047348
5     a        c  2019-05  10.690000  0.062624          0  0.062624
6     a        c  2019-06  10.770000  0.007484          0  0.007484
7     a        c  2019-07  10.670000 -0.009285          0 -0.009285
8     a        c  2019-08  10.510000 -0.014995          0 -0.014995
9     a        c  2019-09  10.280000 -0.021884          0 -0.021884
10    a        c  2019-10  10.050000 -0.022374          0 -0.022374
11    a        c  2019-11   9.720000 -0.032836          0 -0.032836
12    a        c  2019-12   9.840000  0.012346          1  0.012346
13    a        c  2020-01  10.036800  0.020000          1  0.020000
14    a        c  2020-02   9.988546 -0.004808          1 -0.004808
15    a        c  2020-03  10.143000 -0.020000          1  0.015463
16    a        c  2020-04   9.940140 -0.020000          1 -0.020000
17    a        c  2020-05   9.690436 -0.020000          1 -0.025121
18    a        c  2020-06   9.335088 -0.020000          1 -0.036670
19    a        c  2020-07   9.136344  0.020000          1 -0.021290
20    a        c  2020-08   9.269964  0.020000          1  0.014625
21    a        c  2020-09   9.488448  0.020000          1  0.023569
22    a        c  2020-10   9.884626 -0.001976          1  0.041754
23    a        c  2020-11   9.898000 -0.020000          1  0.001353
24    b        d  2018-12   6.320000       NaN          0       NaN
25    b        d  2019-01   6.320000  0.000000          0  0.000000
26    b        d  2019-02   6.320000  0.000000          0  0.000000
27    b        d  2019-03   6.320000  0.000000          0  0.000000
28    b        d  2019-04   6.320000  0.000000          0  0.000000
29    b        d  2019-05   6.320000  0.000000          0  0.000000
30    b        d  2019-06   6.000000 -0.050633          0 -0.050633
31    b        d  2019-07   6.000000  0.000000          0  0.000000
32    b        d  2019-08   6.000000  0.000000          0  0.000000
33    b        d  2019-09   6.000000  0.000000          0  0.000000
34    b        d  2019-10   6.000000  0.000000          0  0.000000
35    b        d  2019-11   6.000000  0.000000          0  0.000000
36    b        d  2019-12   5.780000 -0.020000          1 -0.036667
37    b        d  2020-01   5.895600  0.020000          1  0.020000
38    b        d  2020-02   5.777688 -0.020000          1 -0.020000
39    b        d  2020-03   5.897640  0.020000          1  0.020761
40    b        d  2020-04   5.677728 -0.020000          1 -0.037288
41    b        d  2020-05   5.857656  0.020000          1  0.031690
42    b        d  2020-06   5.607756 -0.020000          1 -0.042662
43    b        d  2020-07   5.857656  0.020000          1  0.044563
44    b        d  2020-08   5.427828 -0.020000          1 -0.073379
45    b        d  2020-09   5.897640  0.020000          1  0.086556
46    b        d  2020-10   5.207916 -0.020000          1 -0.116949
47    b        d  2020-11   6.007596  0.020000          1  0.153551

Link de referencia: Calcule los valores actuales basados en pct_change y anteriores valores en pandas

1
ahbon 24 dic. 2019 a las 10:29

2 respuestas

Esto parece resuelto el problema:

df.loc[df["predicted"]==1, "value"] = np.nan
while len(df.loc[df['value'].isin(['', np.nan])]) > 0:
    df['value'] = (1 + df['pct']).mul(df.groupby('district')['value'].shift()).fillna(df['value'])
df['new_pct'] = df.groupby('district')['value'].apply(lambda x: x.pct_change())

print(df)

Salida:

   city district     date      value       pct  predicted   new_pct
0     a        c  2018-12  10.170000       NaN          0       NaN
1     a        c  2019-01   9.990001 -0.017699          0 -0.017699
2     a        c  2019-02  10.660001  0.067067          0  0.067067
3     a        c  2019-03  10.559999 -0.009381          0 -0.009381
4     a        c  2019-04  10.060004 -0.047348          0 -0.047348
5     a        c  2019-05  10.690002  0.062624          0  0.062624
6     a        c  2019-06  10.770006  0.007484          0  0.007484
7     a        c  2019-07  10.670006 -0.009285          0 -0.009285
8     a        c  2019-08  10.510010 -0.014995          0 -0.014995
9     a        c  2019-09  10.280009 -0.021884          0 -0.021884
10    a        c  2019-10  10.050004 -0.022374          0 -0.022374
11    a        c  2019-11   9.720002 -0.032836          0 -0.032836
12    a        c  2019-12   9.840005  0.012346          1  0.012346
13    a        c  2020-01  10.036804  0.020000          1  0.020000
14    a        c  2020-02   9.988548 -0.004808          1 -0.004808
15    a        c  2020-03   9.788778 -0.020000          1 -0.020000
16    a        c  2020-04   9.592998 -0.020000          1 -0.020000
17    a        c  2020-05   9.401140 -0.020000          1 -0.020000
18    a        c  2020-06   9.213114 -0.020000          1 -0.020000
19    a        c  2020-07   9.397375  0.020000          1  0.020000
20    a        c  2020-08   9.585320  0.020000          1  0.020000
21    a        c  2020-09   9.777027  0.020000          1  0.020000
22    a        c  2020-10   9.757712 -0.001976          1 -0.001976
23    a        c  2020-11   9.562560 -0.020000          1 -0.020000
24    b        d  2018-12   6.320000       NaN          0       NaN
25    b        d  2019-01   6.320000  0.000000          0  0.000000
26    b        d  2019-02   6.320000  0.000000          0  0.000000
27    b        d  2019-03   6.320000  0.000000          0  0.000000
28    b        d  2019-04   6.320000  0.000000          0  0.000000
29    b        d  2019-05   6.320000  0.000000          0  0.000000
30    b        d  2019-06   5.999999 -0.050633          0 -0.050633
31    b        d  2019-07   5.999999  0.000000          0  0.000000
32    b        d  2019-08   5.999999  0.000000          0  0.000000
33    b        d  2019-09   5.999999  0.000000          0  0.000000
34    b        d  2019-10   5.999999  0.000000          0  0.000000
35    b        d  2019-11   5.999999  0.000000          0  0.000000
36    b        d  2019-12   5.879999 -0.020000          1 -0.020000
37    b        d  2020-01   5.997599  0.020000          1  0.020000
38    b        d  2020-02   5.877647 -0.020000          1 -0.020000
39    b        d  2020-03   5.995200  0.020000          1  0.020000
40    b        d  2020-04   5.875296 -0.020000          1 -0.020000
41    b        d  2020-05   5.992802  0.020000          1  0.020000
42    b        d  2020-06   5.872947 -0.020000          1 -0.020000
43    b        d  2020-07   5.990406  0.020000          1  0.020000
44    b        d  2020-08   5.870598 -0.020000          1 -0.020000
45    b        d  2020-09   5.988010  0.020000          1  0.020000
46    b        d  2020-10   5.868249 -0.020000          1 -0.020000
47    b        d  2020-11   5.985614  0.020000          1  0.020000
0
ahbon 25 dic. 2019 a las 02:49

Creo que puedes usar:

df['value'] = (1 + df['pct']).mul(df.groupby('district')['value'].shift()).fillna(df['value'])
print(df)
   city district     date     value       pct  predicted
0     a        c  2019-09  9.480000  0.004237          0
1     a        c  2019-10  9.350001 -0.013713          0
2     a        c  2019-11  9.049996 -0.032086          0
3     a        c  2019-12  9.040000 -0.001105          1
4     a        c  2020-01  8.859200 -0.020000          1
5     a        c  2020-02  8.910000  0.012500          1
6     b        d  2019-09  9.480000  0.004237          0
7     b        d  2019-10  9.350001 -0.013713          0
8     b        d  2019-11  9.049996 -0.032086          0
9     b        d  2019-12  9.040000 -0.001105          1
10    b        d  2020-01  8.859200 -0.020000          1
11    b        d  2020-02  8.910000  0.012500          1

Cómo funciona:

Puede cambiar los valores por grupos DataFrameGroupBy.shift para fechas anteriores y múltiples agregando 1 a pct, el último reemplazo del primer valor de los grupos por el original por fillna:

df = df.assign(add = (1 + df['pct']),
               shifted=df.groupby('district')['value'].shift(),
               mult = (1 + df['pct']).mul(df.groupby('district')['value'].shift()),
               fin = (1 + df['pct']).mul(df.groupby('district')['value'].shift()).fillna(df['value']))
print(df)
   city district     date  value       pct  predicted       add  shifted  \
0     a        c  2019-09   9.48  0.004237          0  1.004237      NaN   
1     a        c  2019-10   9.35 -0.013713          0  0.986287     9.48   
2     a        c  2019-11   9.05 -0.032086          0  0.967914     9.35   
3     a        c  2019-12   9.04 -0.001105          1  0.998895     9.05   
4     a        c  2020-01   8.80 -0.020000          1  0.980000     9.04   
5     a        c  2020-02   8.91  0.012500          1  1.012500     8.80   
6     b        d  2019-09   9.48  0.004237          0  1.004237      NaN   
7     b        d  2019-10   9.35 -0.013713          0  0.986287     9.48   
8     b        d  2019-11   9.05 -0.032086          0  0.967914     9.35   
9     b        d  2019-12   9.04 -0.001105          1  0.998895     9.05   
10    b        d  2020-01   8.80 -0.020000          1  0.980000     9.04   
11    b        d  2020-02   8.91  0.012500          1  1.012500     8.80   

        mult       fin  
0        NaN  9.480000  
1   9.350001  9.350001  
2   9.049996  9.049996  
3   9.040000  9.040000  
4   8.859200  8.859200  
5   8.910000  8.910000  
6        NaN  9.480000  
7   9.350001  9.350001  
8   9.049996  9.049996  
9   9.040000  9.040000  
10  8.859200  8.859200  
11  8.910000  8.910000  

Si las hormigas procesan filas solo por condición:

m = df["predicted"]==1
s = df[m].groupby('district')['value'].shift()
df['value'] = (1 + df['pct']).mul(s).fillna(df['value'])
print(df)
   city district     date   value       pct  predicted
0     a        c  2019-09  9.4800  0.004237          0
1     a        c  2019-10  9.3500 -0.013713          0
2     a        c  2019-11  9.0500 -0.032086          0
3     a        c  2019-12  9.0400 -0.001105          1
4     a        c  2020-01  8.8592 -0.020000          1
5     a        c  2020-02  8.9100  0.012500          1
6     b        d  2019-09  9.4800  0.004237          0
7     b        d  2019-10  9.3500 -0.013713          0
8     b        d  2019-11  9.0500 -0.032086          0
9     b        d  2019-12  9.0400 -0.001105          1
10    b        d  2020-01  8.8592 -0.020000          1
11    b        d  2020-02  8.9100  0.012500          1
1
jezrael 24 dic. 2019 a las 08:33