Pandas GroupBy ["A"] Every Nth Row of ["B"] and Compute Difference of ["C"]

user_11228445 0
Viewed 45

I have a dataframe that represents several different machine ids, their job number, and the value they output, as follows:

     id  job  value
0     1    1     42
1     1    2     42
2     1    3     42
3     1    4     45
4     2    1     38
5     2    2     38
6     2    3     40
7     2    4     40
8     2    5     42
9     3    1     44
10    3    2     44
11    3    3     43
12    3    4     43

A machine gets each job done in 20 seconds. My goal is to know how many times the value changed per minute. For example, the intermediate step dataframe would be as follows:

     id  changes
0     1        0  # jobs 1-3=60 seconds. No changes in value
1     1        1  # job 4. Changed from previous value
2     2        1  # jobs 1-3. One change
3     2        1  # jobs 4-5. One change
4     3        1  # jobs 1-3. One change
5     3        0  # job 4. No changes

Then I can easily calculate the end result (change rate per minute) summing the changes column and divide by the number of entries.

     id  rate
0     1   0.5
1     2   1.0
2     3   0.5

I looked at other questions that partially answer mine, such as this one that uses df.groupby(df.index // 3) to do the bins, but in my case I want that grouping to be per id (groupby(df["id"] // 3)?). And df['changes'] = df.col1 - df.col1.shift()?

Edit to add more test cases:

# Values start at zero to indicate when changes happen
df = pd.DataFrame(
        {
            "id": [1, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3],
            "job": [1, 2, 3, 4, 5, 6, 1, 2, 3, 1, 2, 3, 4],
            "value": [0, 1, 2, 3, 4, 5, 0, 0, 0, 0, 1, 1, 2]
        }
    )

Expected result is

1    2.5 # 5 changes total over two minutes
2    0.0 # 0 changes total over one minute
3    1.0 # 2 changes total over a minute and 20 seconds (2 periods)
pandas dataframe python
2 Answers

You can use groupby().rolling() to group the rows, then compare the max and min to know whether the value has changed:

rolling = df.groupby('id')['value'].rolling(3)

intermediate = rolling.max().dropna() != rolling.min().dropna()

out = intermediate.groupby('id').mean()

Output (intermediate):

id    
1   2     False
    3      True
2   6      True
    7      True
    8      True
3   11     True
    12     True
Name: value, dtype: bool

Output:

id
1    0.5
2    1.0
3    1.0
Name: value, dtype: float64
user_42384080

Thanks to @quang hoang answer, I was able to reach my goal. Here's my code:

JOB_DURATION_SECONDS = 10
print(df)

# Compute the number of changes
df["num_of_changes"] = df.groupby("id").value.apply(
    lambda x: x.rolling(2).apply(lambda x: x.iloc[0] < x.iloc[1])
)
print(df)

# Group by id and calculate rate per minute
out = (
    df[["id", "num_of_changes"]]
    .groupby(["id"])
    .apply(lambda x: x["num_of_changes"].sum() / (len(x) * JOB_DURATION_SECONDS))
)
print(out)

Resulting in

# Input
         id  job            value
0         1    1                0
1         1    2                1
2         1    3                2
3         1    4                3
4         1    5                4
5         1    6                5
6         2    1                0
7         2    2                0
8         2    3                0
9         3    1                0
10        3    2                0
11        3    3                1
12        3    4                1
13        3    5                1
14        3    6                1
15        3    7                1

# Intermediate
         id  job            value  num_of_changes
0         1    1                0             NaN
1         1    2                1             1.0
2         1    3                2             1.0
3         1    4                3             1.0
4         1    5                4             1.0
5         1    6                5             1.0
6         2    1                0             NaN
7         2    2                0             0.0
8         2    3                0             0.0
9         3    1                0             NaN
10        3    2                0             0.0
11        3    3                1             1.0
12        3    4                1             0.0
13        3    5                1             0.0
14        3    6                1             0.0
15        3    7                1             0.0

# Out
id
1    5.000000
2    0.000000
3    0.857143
dtype: float64
user_112284450
Related