CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Rohit Lamba
Rohit Lamba

Reputation: 186

Add the Values if appears consecutive to last row otherwise keep same value

I have a dataframe with two column (Time, Value) which have time data in the format HH:MM:SS and some Values against each time value. Sample data is shown below for reference:

Time        Value
09:15:00    0
09:15:30    0
09:15:31    0
09:15:32    85
09:15:33    168
09:15:34    50
09:15:34    100
09:15:35    0
09:15:35    0
09:15:36    0
09:15:37    0
09:15:37    0
09:15:38    40
09:15:39    0
09:15:39    0
09:15:39    0
09:15:40    51
09:15:40    0
09:15:41    250
09:15:42    110
09:15:43    42
09:15:44    0
09:15:45    0

I want to add the Values if appears consecutive (non zero values) to last row and need to keep same Value if Values are isolated.

Sample output as below:

Time        Value   Result
09:15:00    0       0
09:15:30    0       0
09:15:31    0       0
09:15:32    85      0
09:15:33    168     0
09:15:34    50      0
09:15:34    100     403
09:15:35    0       0
09:15:35    0       0
09:15:36    0       0
09:15:37    0       0
09:15:37    0       0
09:15:38    40      40
09:15:39    0       0
09:15:39    0       0
09:15:39    0       0
09:15:40    51      51
09:15:40    0       0
09:15:41    250     0
09:15:42    110     0
09:15:43    42      402
09:15:44    0       0
09:15:45    0       0

Upvotes: 2

Views: 57

Answers (1)

ALollz
ALollz

Reputation: 59579

Form consecutive groups taking the cumsum of a Boolean mask. Then take the cumsum within each group. Use where to mask everything but the last row within a group (Since cumsum(0) == 0 this works for all 0 rows too) and then we fill the values we masked with 0.

s = df['Value'].eq(0).cumsum()
df['Result'] = (df['Value'].groupby(s).cumsum()
                    .where(~s.duplicated(keep='last'))
                    .fillna(0, downcast='infer'))

        Time  Value  Result
0   09:15:00      0       0
1   09:15:30      0       0
2   09:15:31      0       0
3   09:15:32     85       0
4   09:15:33    168       0
5   09:15:34     50       0
6   09:15:34    100     403
7   09:15:35      0       0
8   09:15:35      0       0
9   09:15:36      0       0
10  09:15:37      0       0
11  09:15:37      0       0
12  09:15:38     40      40
13  09:15:39      0       0
14  09:15:39      0       0
15  09:15:39      0       0
16  09:15:40     51      51
17  09:15:40      0       0
18  09:15:41    250       0
19  09:15:42    110       0
20  09:15:43     42     402
21  09:15:44      0       0
22  09:15:45      0       0

Upvotes: 2

Related Questions