Pandas: How to apply operation on subdataframe splitted on a column value

user_12523234 0
Viewed 72

I have maintenance data (from csv file or other sources), the data are formatted as such:

Date        Equipment_id    sensor_reading  failure
2017-01-01  eq_1                 1.0        0
2017-01-03  eq_1                 0.5        0
2017-01-04  eq_1                 1.5        1
2017-01-01  eq_2                 Nan        Nan
2017-01-02  eq_2                 0.3        0
2017-01-03  eq_2                 1.0        0

I want to apply transformation like interpolation or rolling windows on those data, and I can do this with pandas. But if I use df.interpolate() for example it will interpolate even if the data are from different equipment_id. There is probably ways to avoid that, but it seems complicated and I can miss errors.

I consider using a for loop to split the dataframe like that:

data_dict = {}
for equipment_id in df[Equipment_id].unique():
    data_dict[equipment_id] = df.loc[df['Equipment_id']==equipment_id]

And then use operation like df.interpolate() or df.rolling(window_size).min() on each subdataframe before transforming then to numpy array and concatenate them to form my train set.

But I don't really know if what is happening behind the scene. So I was wondering if making those subdataframe to work on separately would cause memory issue or make longer calculations. I also don't know if there is a cleaner or more canonical way to do that.

pandas python
1 Answers

If I understand correctly, you want to apply a rolling window/function on each id individually. In that case, groupby() and apply() will help.

For example, this will apply a rolling window for each id that calculates the sum of a 3 row window:

df.groupby(equipment_id).rolling(3).sum()

if you would like to interpolate (note how you dont need to pass in an argument):

df.groupby(equipment_id).apply(pd.interpolate)

and you can also mix those:

df.groupby(equipment_id).rolling(3).apply(pd.interpolate)
user_139474650
Related