CODEWITHSUNDEEP
pythonpandas
nilsinelabore
nilsinelabore

Reputation: 5135

Extract range-start and range-end records from a dataframe

I'd like to calculate the time periods for which Value is in range (41 - 46) and remain at the same value for df below. Value should only update when there is a change, otherwise remains constant.

            Id          Timestamp               Value
34213951    34214809    2012-05-01 08:33:47.127 41.5    
34214252    34215110    2012-05-01 08:39:06.270 41.5    
34214423    34215281    2012-05-01 08:41:56.240 40.5
34214602    34215460    2012-05-01 08:44:55.777 39.5
34214873    34215731    2012-05-01 08:49:25.600 38.5
34215071    34215929    2012-05-01 08:53:04.593 37.5
34215342    34216200    2012-05-01 08:56:47.257 36.5
34216007    34216865    2012-05-01 09:07:24.370 34.5
34216443    34217301    2012-05-01 09:14:46.120 33.5
34216884    34217742    2012-05-01 09:22:51.907 32.5
34217190    34218048    2012-05-01 09:29:00.023 31.5
34217803    34218661    2012-05-01 09:40:08.483 30.5
34218381    34219239    2012-05-01 09:50:20.440 30.5
34218382    34219240    2012-05-01 09:50:22.317 32.5
34218388    34219246    2012-05-01 09:50:26.067 37.5
34218389    34219247    2012-05-01 09:50:27.940 39.0
34218392    34219250    2012-05-01 09:50:29.817 39.5
34218393    34219251    2012-05-01 09:50:31.690 40.5
34218396    34219254    2012-05-01 09:50:35.440 41.0
34218789    34219647    2012-05-01 09:56:55.327 41.0
34218990    34219848    2012-05-01 10:00:07.847 40.0

with:

def samevalue(df):

    df = df.reset_index(drop=True)
    dataframe = []
    flag = 0   
    start_time = []
    start_value = []
    end_time = []
    end_value = []
    
    for i in range(len(df.index)):
        if flag == 0:   
            if ((df.loc[i, 'Value']>=41) and
                (df.loc[i, 'Value']<=46)):

                start_time = df.loc[i, 'Timestamp']
                start_value = df.loc[i, 'Value']  
                flag = 1

        elif flag == 1:
            if (df.loc[i, 'Data'] != start_temp):
                end_time = df.loc[i, 'Timestamp']
                end_value = df.loc[i, 'Value']
                flag = 0

            dataframe.append([start_time, end_time, start_value, end_value])        

    data1 = pd.DataFrame(dataframe, columns= ["StartTime", "EndTime", "StartValue", "EndValue"])
    return data1

samevalue(df)

Actual Output:

    StartTime               EndTime                     StartValue  EndValue
0   2012-05-01 08:33:47.127 []                          41.5        []
1   2012-05-01 08:33:47.127 2012-05-01 08:41:56.240000  41.5        40.5
2   2012-05-01 09:50:35.440 2012-05-01 08:41:56.240000  41.0        40.5
3   2012-05-01 09:50:35.440 2012-05-01 10:00:07.847000  41.0        40

Expected Output:

    StartTime               EndTime                     StartValue  EndValue

0   2012-05-01 08:33:47.127 2012-05-01 08:41:56.240     41.5        40.5
1   2012-05-01 09:50:35.440 2012-05-01 10:00:07.847     41.0        40.0

I would have expected that the EndTime is always after the StartTime but it's not the case. Have I missed out something?

Upvotes: 2

Views: 263

Answers (1)

Roy2012
Roy2012

Reputation: 12523

Here's a vectorized way of doing that. Mostly using shift to compare adjacent rows.

df["in_range"] = (df.Value >= 41) & (df.Value <= 46)
df["end_of_range"] = df.in_range.shift() & ~df.in_range
df["start_of_range"] = ~df.in_range.shift(1).fillna(False) & df.in_range

At this point, the dataframe is (I removed the index and the Id fro better visibility):

                  Timestamp  Value  in_range  end_of_range  start_of_range
0   2012-05-01 08:33:47.127   41.5      True         False            True
1   2012-05-01 08:39:06.270   41.5      True         False           False
2   2012-05-01 08:41:56.240   40.5     False          True           False
3   2012-05-01 08:44:55.777   39.5     False         False           False
...

I now create two dataframes - one for all the 'range start' records, and another one for all the 'range end' records:

starts = df[df.start_of_range][["Timestamp", "Value"]]
ends = df[df.end_of_range][["Timestamp", "Value"]]

# reset the index of these two dataframe, so I can easility concat them later. 
starts.index = range(len(starts))
ends.index = range(len(starts))

The value of 'starts' and 'ends' is now:

                 Timestamp  Value
0  2012-05-01 08:33:47.127   41.5
1  2012-05-01 09:50:35.440   41.0
                 Timestamp  Value
0  2012-05-01 08:41:56.240   40.5
1  2012-05-01 10:00:07.847   40.0

All that is left now is concat the two newly created dataframes, so that each start record is aligned with its corresponding end record.

res = pd.concat([starts, ends], axis=1)
res.columns = ["StartTime", "EndTime", "StartValue", "EndValue"]

The result is:

                 StartTime  EndTime               StartValue  EndValue
0  2012-05-01 08:33:47.127     41.5  2012-05-01 08:41:56.240      40.5
1  2012-05-01 09:50:35.440     41.0  2012-05-01 10:00:07.847      40.0

Upvotes: 1

Related Questions