组内自动递增 [英] auto increment inside group

问题描述

我有一个 dataframe :

df = pd.DataFrame.from_dict({
    'product': ('a', 'a', 'a', 'a', 'c', 'b', 'b', 'b'),
    'sales': ('-', '-', 'hot_price', 'hot_price', '-', 'min_price', 'min_price', 'min_price'),
    'price': (100, 100, 50, 50, 90, 70, 70, 70),
    'dt': ('2020-01-01 00:00:00', '2020-01-01 00:05:00', '2020-01-01 00:07:00', '2020-01-01 00:10:00', '2020-01-01 00:13:00', '2020-01-01 00:15:00', '2020-01-01 00:19:00', '2020-01-01 00:21:00')
})

  product      sales  price                   dt
0       a          -    100  2020-01-01 00:00:00
1       a          -    100  2020-01-01 00:05:00
2       a  hot_price     50  2020-01-01 00:07:00
3       a  hot_price     50  2020-01-01 00:10:00
4       c          -     90  2020-01-01 00:13:00
5       b  min_price     70  2020-01-01 00:15:00
6       b  min_price     70  2020-01-01 00:19:00
7       b  min_price     70  2020-01-01 00:21:00

我需要下一个输出:

  product      sales  price                   dt  unique_group
0       a          -    100  2020-01-01 00:00:00             0
1       a          -    100  2020-01-01 00:05:00             0
2       a  hot_price     50  2020-01-01 00:07:00             1
3       a  hot_price     50  2020-01-01 00:10:00             1
4       c          -     90  2020-01-01 00:13:00             2
5       b  min_price     70  2020-01-01 00:15:00             3
6       b  min_price     70  2020-01-01 00:19:00             3
7       b  min_price     70  2020-01-01 00:21:00             3

我该怎么做:

unique_group = 0
df['unique_group'] = unique_group
for i in range(1, len(df)):
    current, prev = df.loc[i], df.loc[i - 1]
    if not all([
        current['product'] == prev['product'],
        current['sales'] == prev['sales'],
        current['price'] == prev['price'],
    ]):
        unique_group += 1
    df.loc[i, 'unique_group'] = unique_group

是否可以不进行迭代?我尝试使用 cumsum()， shift()， ngroup()， drop_duplicates()，但未成功.

Is it possible to do it without iteration? I tried using cumsum(), shift(), ngroup(), drop_duplicates() but unsuccessfully.

推荐答案

IIUC，

IIUC, GroupBy.ngroup:

df['unique_group'] = df.groupby(['product', 'sales', 'price'],sort=False).ngroup()
print(df)

  product      sales  price                   dt  unique_group
0       a          -    100  2020-01-01 00:00:00             0
1       a          -    100  2020-01-01 00:05:00             0
2       a  hot_price     50  2020-01-01 00:07:00             1
3       a  hot_price     50  2020-01-01 00:10:00             1
4       c          -     90  2020-01-01 00:13:00             2
5       b  min_price     70  2020-01-01 00:15:00             3
6       b  min_price     70  2020-01-01 00:19:00             3
7       b  min_price     70  2020-01-01 00:21:00             3

这两种方法都可以工作，即使数据帧没有排序

this works either way, even if the data frame is not ordered

另一种方法

这适用于有序数据框

cols = ['product','sales','price']
df['unique_group'] = df[cols].ne(df[cols].shift()).any(axis=1).cumsum().sub(1)

这篇关于组内自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！