Estou tentando calcular o saldo (o nível) de um inventário ao longo do tempo e ter quantidades de entrada e saída como entrada (e uma categoria para cada tipo de inventário). Normalmente eu calcularia incoming - outgoinge transportaria para o próximo período (soma cumulativa), mas neste caso uma dificuldade adicional é que o saldo pode ser substituído em vários pontos no tempo, o que "redefine" o saldo para esses valores (e entradas/saídas precisam ser adicionadas a essas substituições deste ponto no tempo em diante).
Eu descobri uma maneira de calcular isso compensando o saldo calculado (=cumsum(entrada-saída)) quando há um saldo de substituição (pelo cumsum calculado negativo; ou seja, definindo o inventário como 0 quando há um saldo de substituição), mas isso não funciona quando há várias substituições em momentos diferentes.
Esta é minha abordagem atual que funciona bem para o dataframe fornecido (=apenas uma substituição ( bal) por categoria ( cat)).
>>> df = pd.DataFrame({
... 'cat': ['a', 'a', 'b', 'b', 'a', 'a', 'a', 'a', 'a', 'b'],
... 'time': [1, 2, 1, 2, 4, 5, 6, 7, 8, 9],
... 'in': [None, 10, None, None, None, 20, 11, 9, 10, None],
... 'out': [10, None, None, 20, 10, 5, None, 30, None, None],
... 'bal': [None, None, None, None, 50, None, None, None, None, None]
^ at this time, the balance should be set to 50, irrespective of prior `in` and `out`.
... })
>>>
>>> # cumsum goes by row, so order matters
>>> df = df.sort_values(by=['time'])
>>> df
cat time in out bal
0 a 1 NaN 10.0 NaN
2 b 1 NaN NaN NaN
1 a 2 10.0 NaN NaN
3 b 2 NaN 20.0 NaN
4 a 4 NaN 10.0 50.0
5 a 5 20.0 5.0 NaN
6 a 6 11.0 NaN NaN
7 a 7 9.0 30.0 NaN
8 a 8 10.0 NaN NaN
9 b 9 NaN NaN NaN
>>>
>>>
>>> # Calculate the balance as if 'bal' (the override) wasn't there (cumsum(in - out))
>>> df['inout'] = df['in'].fillna(0) - df['out'].fillna(0)
>>> df['cumsum'] = df[['cat', 'inout']].groupby(['cat']).cumsum()
>>> df
cat time in out bal inout cumsum
0 a 1 NaN 10.0 NaN -10.0 -10.0
2 b 1 NaN NaN NaN 0.0 0.0
1 a 2 10.0 NaN NaN 10.0 0.0
3 b 2 NaN 20.0 NaN -20.0 -20.0
4 a 4 NaN 10.0 50.0 -10.0 -10.0 <-- we want to override this with the value from 'bal' (50) and continue the calculation
5 a 5 20.0 5.0 NaN 15.0 5.0
6 a 6 11.0 NaN NaN 11.0 16.0
7 a 7 9.0 30.0 NaN -21.0 -5.0
8 a 8 10.0 NaN NaN 10.0 5.0
9 b 9 NaN NaN NaN 0.0 -20.0
>>>
>>> # Find the positions where a balance would override the calculated balance
>>> df['correction'] = -df.loc[pd.notnull(df['bal']), 'cumsum']
>>> df
cat time in out bal inout cumsum correction
0 a 1 NaN 10.0 NaN -10.0 -10.0 NaN
2 b 1 NaN NaN NaN 0.0 0.0 NaN
1 a 2 10.0 NaN NaN 10.0 0.0 NaN
3 b 2 NaN 20.0 NaN -20.0 -20.0 NaN
4 a 4 NaN 10.0 50.0 -10.0 -10.0 10.0
5 a 5 20.0 5.0 NaN 15.0 5.0 NaN
6 a 6 11.0 NaN NaN 11.0 16.0 NaN
7 a 7 9.0 30.0 NaN -21.0 -5.0 NaN
8 a 8 10.0 NaN NaN 10.0 5.0 NaN
9 b 9 NaN NaN NaN 0.0 -20.0 NaN
>>>
>>>
>>> # Calculate with the corrected balance
>>> df['inout2'] = df['in'].fillna(0) - df['out'].fillna(0) + df['bal'].fillna(0) + df['correction'].fillna(0)
>>> df['cumsum2'] = df[['cat', 'inout2']].groupby(['cat']).cumsum()
>>> df
cat time in out bal inout cumsum correction inout2 cumsum2
0 a 1 NaN 10.0 NaN -10.0 -10.0 NaN -10.0 -10.0
2 b 1 NaN NaN NaN 0.0 0.0 NaN 0.0 0.0
1 a 2 10.0 NaN NaN 10.0 0.0 NaN 10.0 0.0
3 b 2 NaN 20.0 NaN -20.0 -20.0 NaN -20.0 -20.0
4 a 4 NaN 10.0 50.0 -10.0 -10.0 10.0 50.0 50.0 (override from 'bal')
5 a 5 20.0 5.0 NaN 15.0 5.0 NaN 15.0 65.0 <--- 50 (override) +15 (in-out)
6 a 6 11.0 NaN NaN 11.0 16.0 NaN 11.0 76.0
7 a 7 9.0 30.0 NaN -21.0 -5.0 NaN -21.0 55.0
8 a 8 10.0 NaN NaN 10.0 5.0 NaN 10.0 65.0
9 b 9 NaN NaN NaN 0.0 -20.0 NaN 0.0 -20.0
>>>
>>>
>>> df[df['cat'] == 'a']
cat time in out bal inout cumsum correction inout2 cumsum2
0 a 1 NaN 10.0 NaN -10.0 -10.0 NaN -10.0 -10.0
1 a 2 10.0 NaN NaN 10.0 0.0 NaN 10.0 0.0
4 a 4 NaN 10.0 50.0 -10.0 -10.0 10.0 50.0 50.0
5 a 5 20.0 5.0 NaN 15.0 5.0 NaN 15.0 65.0
6 a 6 11.0 NaN NaN 11.0 16.0 NaN 11.0 76.0
7 a 7 9.0 30.0 NaN -21.0 -5.0 NaN -21.0 55.0
8 a 8 10.0 NaN NaN 10.0 5.0 NaN 10.0 65.0
Isso parece bom. No índice 4, o cálculo de saldo simples é substituído (era -10, agora é 50, como esperado) e os fluxos de entrada e saída do período subsequente são adicionados, como esperado.
Entretanto, quando introduzo outra substituição, o algoritmo acima quebra.
df = pd.DataFrame({
'cat': ['a', 'a', 'b', 'b', 'a', 'a', 'a', 'a', 'a', 'b'],
'time': [1, 2, 1, 2, 4, 5, 6, 7, 8, 9],
'in': [None, 10, None, None, None, 20, 11, 9, 10, None],
'out': [10, None, None, 20, 10, 5, None, 30, None, None],
'bal': [None, None, None, None, 50, None, None, 30, None, None]
# ^
})
... same pipeline as before
>>> df
cat time in out bal inout cumsum correction inout2 cumsum2
0 a 1 NaN 10.0 NaN -10.0 -10.0 NaN -10.0 -10.0
2 b 1 NaN NaN NaN 0.0 0.0 NaN 0.0 0.0
1 a 2 10.0 NaN NaN 10.0 0.0 NaN 10.0 0.0
3 b 2 NaN 20.0 NaN -20.0 -20.0 NaN -20.0 -20.0
4 a 4 NaN 10.0 50.0 -10.0 -10.0 10.0 50.0 50.0 # still ok
5 a 5 20.0 5.0 NaN 15.0 5.0 NaN 15.0 65.0
6 a 6 11.0 NaN NaN 11.0 16.0 NaN 11.0 76.0
7 a 7 9.0 30.0 30.0 -21.0 -5.0 5.0 14.0 90.0 # expect 30
8 a 8 10.0 NaN NaN 10.0 5.0 NaN 10.0 100.0 # expect 30 + 10 = 40
9 b 9 NaN NaN NaN 0.0 -20.0 NaN 0.0 -20.0
Gostaria de modificar o algoritmo para manter a simplicidade de uso cumsum(funcional), mas não consigo descobrir como proceder. É quase como se eu precisasse de um cumsum condicional que substitua os valores intermediários quando uma condição for atendida (nesse caso, um valor em bal). No entanto, eu preferiria calcular mais uma coluna de correção (ou consertar a existente) e adicioná-la (mas bati em uma parede, pois provavelmente olhei para ela por muito tempo). Qualquer ajuda é muito apreciada.
Código
df(seu primeiro exemplo)df(seu segundo exemplo)