3
$\begingroup$

I have a table of features and labels where each row has a time stamp. Labels are categorical. They go in a batch where one label repeats several times. Batches with the same label do not have a specific order. The number of repetitions of the same label in one batch is always the same. In the example below, every three rows has the same label.

enter image description here

I would like to get a new table where Var 1 and Var 2 are averaged across repetitions of labels. In this example, I would like to average every three rows as follows:

enter image description here

Is this possible to do with some functions in pandas or other Pyhton libraries?

$\endgroup$
1
$\begingroup$

Solution:

In [24]: res = (df.groupby((df.Label != df.Label.shift()).cumsum())
                  .mean()
                  .reset_index(drop=True))

Result:

In [25]: res
Out[25]:
        Var1       Var2  Label
0  22.413333  18.733333      2
1  39.390000  20.270000      3
2  38.450000  20.196667      1
3  21.173333  17.860000      3
4  36.453333  19.246667      2

Source DF (I had to use an OCR program in order to parse the data from your picture - please post your dataset in text/CSV form next time):

In [23]: df
Out[23]:
    Timestamp   Var1   Var2  Label
0  2015-01-01  23.56  18.85      2
1  2015-02-01  21.23  18.61      2
2  2015-03-01  22.45  18.74      2
3  2015-04-01  35.32  19.94      3
4  2015-05-01  40.50  20.36      3
5  2015-06-01  42.35  20.51      3
6  2015-07-01  41.33  20.43      1
7  2015-08-01  38.35  20.19      1
8  2015-09-01  35.67  19.97      1
9  2015-10-01  22.20  17.97      3
10 2015-11-01  20.11  17.75      3
11 2015-12-01  21.21  17.86      3
12 2015-01-13  32.79  18.95      2
13 2015-01-14  37.45  19.33      2
14 2015-01-15  39.12  19.46      2

Explanation: if we want to group DF by consecutive labels of the same value, then we need to create a series with a unique value for each group. This can be done using the following trick:

In [32]: (df.Label != df.Label.shift()).cumsum()
Out[32]:
0     1
1     1
2     1
3     2
4     2
5     2
6     3
7     3
8     3
9     4
10    4
11    4
12    5
13    5
14    5
Name: Label, dtype: int32

In [33]: df.Label != df.Label.shift()
Out[33]:
0      True
1     False
2     False
3      True
4     False
5     False
6      True
7     False
8     False
9      True
10    False
11    False
12     True
13    False
14    False
Name: Label, dtype: bool

NOTE: False == 0 and True == 1 in Python.

$\endgroup$
  • $\begingroup$ Thanks! This works even when batches has different sizes! And thanks for the comment on posting text instead of a picture. May I ask you to explain how this part works, please: df.groupby((df.Label != df.Label.shift()).cumsum())? I know what groupby, shift and cumsum do, but cannot figure out why here the summation happens only inside the batches. $\endgroup$ – Mirit Jan 14 at 13:11
  • $\begingroup$ @Mirit, i've added a short explanation $\endgroup$ – MaxU Jan 14 at 13:18
  • $\begingroup$ thank you very much! Now everything is working and everything is clear. $\endgroup$ – Mirit Jan 14 at 13:33
  • $\begingroup$ @Mirit, glad I could help :) $\endgroup$ – MaxU Jan 14 at 13:33
1
$\begingroup$

You can try like below with shifting the frame,

df['shift1'] = df.var1.shift(1)
df['shift2'] = df.var1.shift(2)
df['avg'] = df[['var1','shift1','shift2']].mean(axis=1)
df.iloc[range(2,n,3)]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.