Python Forum

I am trying to group the data-frame based on a few conditions.

Data frame:
Start Date End Date value
1971-07-01 1971-07-31 0.0
1971-08-01 1971-08-31 0.25
1971-09-01 1971-09-30 -0.62
1971-10-01 1971-10-31 0.0
1971-11-01 1971-11-30 -0.63
1971-12-01 1971-12-31 -1.0
1972-01-01 1972-01-31 0.0
1972-02-01 1972-02-29 0.0
1972-03-01 1972-03-31 2.0
1972-04-01 1972-04-30 0.0
.
.
1973-07-01 1973-07-31 2.0
1973-08-01 1973-08-31 0.5
1973-09-01 1973-09-30 -2.0
1973-10-01 1973-10-31 0.0
1973-11-01 1973-11-30 0.0
1973-12-01 1973-12-31 0.0
1974-01-01 1974-01-31 0.0
1974-02-01 1974-02-28 0.0
.
.
.
1974-11-01 1974-11-30 0.0
1974-12-01 1974-12-31 -1.25
1975-01-01 1975-01-31 -1.0
1975-02-01 1975-02-28 -1.0
1975-03-01 1975-03-31 -0.5
1975-04-01 1975-04-30 -0.25
1975-05-01 1975-05-31 0.0
1975-06-01 1975-06-30 1.25
1975-07-01 1975-07-31 0.0
1975-08-01 1975-08-31 0.0

Criteria for grouping

The group should always start with the negative value

The group continues as long as we have a negative value

The group ends if we reach either a positive value or three consecutive zeros.



An example that fulfills the criteria from the above data frame

1971-09-01 1971-09-30 -0.62
1971-10-01 1971-10-31 0.0
1971-11-01 1971-11-30 -0.63
1971-12-01 1971-12-31 -1.0
1972-01-01 1972-01-31 0.0
1972-02-01 1972-02-29 0.0

I tried for loop but I am not going anywhere with that.

for i in df.index:
    no = 0
    if df['Value'][i] < 0:
        df['groupno'] = no

Thanks for reading.