How can I conditionally count in each row the number of instances where theres a value in the previous date then decrease to 0 in the next date?
df:
Jan Feb Mar Apr
A 1 2 3 0
B 0 0 0 0
C 1 0 2 0
D 0 0 0 1
want to get: df_to_zero_count
Count
A 1
B 0
C 2
D 0
I've tried some combination of apply and iterating through columns but can't seem to make it work.
((df.diff(axis=1) < 0) & (df == 0)).sum(axis=1)
You can check where the differential over the rows is smaller than 0 (that indicates a decrease) and the value of the cells is 0 too.
output:
((df.diff(axis=1) < 0) & (df == 0)).sum(axis=1)
0 1
1 0
2 2
3 0
dtype: int64
You can use:
df_to_zero_count = (df.T.eq(0) & df.T.shift(fill_value=0).ne(0)).sum().to_frame(name='Count')
Result:
print(df_to_zero_count)
Count
A 1
B 0
C 2
D 0
Try:
x = df.shift(axis=1).fillna(0)
df["count"] = ((df == 0) & (x != 0)).sum(axis=1)
print(df)
Prints:
Jan Feb Mar Apr count
A 1 2 3 0 1
B 0 0 0 0 0
C 1 0 2 0 2
D 0 0 0 1 0