I have the following dataframe:
{'item': {0: 'A',
1: 'A',
2: 'A',
3: 'A',
4: 'A',
5: 'B',
6: 'B',
7: 'B',
8: 'B',
9: 'B',
10: 'B',
11: 'B',
12: 'B',
13: 'C',
14: 'C',
15: 'C',
16: 'C',
17: 'D',
18: 'D'},
'Date': {0: Timestamp('2021-05-02 00:00:00'),
1: Timestamp('2021-05-02 00:00:00'),
2: Timestamp('2021-05-02 00:00:00'),
3: Timestamp('2021-05-03 00:00:00'),
4: Timestamp('2021-06-13 00:00:00'),
5: Timestamp('2021-05-03 00:00:00'),
6: Timestamp('2021-05-04 00:00:00'),
7: Timestamp('2021-05-05 00:00:00'),
8: Timestamp('2021-05-06 00:00:00'),
9: Timestamp('2021-05-07 00:00:00'),
10: Timestamp('2021-05-08 00:00:00'),
11: Timestamp('2021-05-09 00:00:00'),
12: Timestamp('2021-05-10 00:00:00'),
13: Timestamp('2021-06-14 00:00:00'),
14: Timestamp('2021-06-15 00:00:00'),
15: Timestamp('2021-06-16 00:00:00'),
16: Timestamp('2021-07-23 00:00:00'),
17: Timestamp('2021-07-07 00:00:00'),
18: Timestamp('2021-07-08 00:00:00')},
'price': {0: 249,
1: 249,
2: 253,
3: 260,
4: 260,
5: 13,
6: 13,
7: 13,
8: 13,
9: 17,
10: 17,
11: 17,
12: 17,
13: 123,
14: 123,
15: 123,
16: 123,
17: 12,
18: 12}}
which looks like this:
item Date price
0 A 2021-05-02 249
1 A 2021-05-02 249
2 A 2021-05-02 253
3 A 2021-05-03 260
4 A 2021-06-13 260
5 B 2021-05-03 13
6 B 2021-05-04 13
7 B 2021-05-05 13
8 B 2021-05-06 13
9 B 2021-05-07 17
10 B 2021-05-08 17
11 B 2021-05-09 17
12 B 2021-05-10 17
13 C 2021-06-14 123
14 C 2021-06-15 123
15 C 2021-06-16 123
16 C 2021-07-23 123
17 D 2021-07-07 12
18 D 2021-07-08 12
As you might see, the price of an item changes over time. What I want to do is to have a column that indicates when a price changes for each item. Now, My first idea was that I could check if the price in the previous row is the same as in the current row (within) a group.
Now, I was convinced that I could do something like this:
df_changes['changed'] = df_changes.groupby(['item'])['price'].eq(df_changes['price'])
to compare row values within a group (returning a boolean) and then translating this to integers to get:
change_item_num diffsum Step
0 0 0 0
1 1 0 0
2 1 1 1
3 1 1 2
4 1 0 2
5 0 0 0
6 1 0 0
7 1 0 0
8 1 0 0
9 1 1 1
10 1 0 1
11 1 0 1
12 1 0 1
13 0 0 0
14 1 0 0
15 1 0 0
16 1 0 0
17 0 0 0
18 1 0 0
where the step column marks changes.
But I was wrong. Whatever I do, I get the error:
AttributeError: 'SeriesGroupBy' object has no attribute 'eq'
Instead, I found a workaround that I am very unhappy about:
j = df_changes.price
k = df_changes.item_num
df_changes['change_price'] = j.eq(j.shift()).astype(int)
df_changes['change_item_num'] = k.eq(k.shift()).astype(int)
df_changes['diffsum'] = abs(df_changes['change_price']-df_changes['change_item_num'])
df_changes['Step'] = df_changes.groupby('item')['diffsum'].cumsum()+1
which returns:
item Date price item_num change_price change_item_num diffsum \
0 A 2021-05-02 249 1 0 0 0
1 A 2021-05-02 249 1 1 1 0
2 A 2021-05-02 253 1 0 1 1
3 A 2021-05-03 260 1 0 1 1
4 A 2021-06-13 260 1 1 1 0
5 B 2021-05-03 13 2 0 0 0
6 B 2021-05-04 13 2 1 1 0
7 B 2021-05-05 13 2 1 1 0
8 B 2021-05-06 13 2 1 1 0
9 B 2021-05-07 17 2 0 1 1
10 B 2021-05-08 17 2 1 1 0
11 B 2021-05-09 17 2 1 1 0
12 B 2021-05-10 17 2 1 1 0
13 C 2021-06-14 123 3 0 0 0
14 C 2021-06-15 123 3 1 1 0
15 C 2021-06-16 123 3 1 1 0
16 C 2021-07-23 123 3 1 1 0
17 D 2021-07-07 12 4 0 0 0
18 D 2021-07-08 12 4 1 1 0
Step
0 1
1 1
2 2
3 3
4 3
5 1
6 1
7 1
8 1
9 2
10 2
11 2
12 2
13 1
14 1
15 1
16 1
17 1
18 1
Surely, there must be an easier way. If not, can anyone explain WHY I cannot use eq or any other logical comaprison within a groupby?
Thankful for any new knowledge!