I have the following dataframe:
df = pd.DataFrame(
[{'price': 22, 'weight': 1, 'product': 'banana', },
{'price': 20, 'weight': 2, 'product': 'apple', },
{'price': 18, 'weight': 2, 'product': 'car', },
{'price': 100, 'weight': 1, 'product': 'toy', },
{'price': 27, 'weight': 1, 'product': 'computer', },
{'price': 200, 'weight': 1, 'product': 'book', },
{'price': 200.5, 'weight': 3, 'product': 'mouse', },
{'price': 202, 'weight': 3, 'product': 'door', },]
)
我需要做的是将价格连续的分组,其中它们之间的差异小于一个阈值(比如2.0),或者不小于这个阈值。然后,我必须仅在“小于阈值”的组上应用以下聚合,否则该组不应进行聚合:
price
应该是price
和weight
的加权平均值weight
应该是最大值product
应该是字符串连接
- 按价格升序对数据框进行排序(以获取连续值)
df.sort_values(by=['price'], inplace=True)
price weight product
2 18.0 2 car
1 20.0 2 apple
0 22.0 1 banana
4 27.0 1 computer
3 100.0 1 toy
5 200.0 1 book
6 200.5 3 mouse
7 202.0 3 door
2. 获取价格升序和降序之间的差异,以检测连续的价格。
df['asc_diff'] = df['price'].diff(periods=1)
df['desc_diff'] = df['price'].diff(periods=-1).abs()
price weight product asc_diff desc_diff
2 18.0 2 car NaN 2.0
1 20.0 2 apple 2.0 2.0
0 22.0 1 banana 2.0 5.0
4 27.0 1 computer 5.0 73.0
3 100.0 1 toy 73.0 100.0
5 200.0 1 book 100.0 0.5
6 200.5 3 mouse 0.5 1.5
7 202.0 3 door 1.5 NaN
- 将
asc_diff
和desc_diff
列组合起来,去除NaN
并创建连续区域。
df['asc_diff'] = df['asc_diff'].combine_first(df['desc_diff'])
df['asc_diff'] = df[['asc_diff', 'desc_diff']].min(axis=1).abs()
df['asc_diff'] = df['asc_diff'] <= 2.0
df = df.drop(columns=['desc_diff'])
price weight product asc_diff
2 18.0 2 car True
1 20.0 2 apple True
0 22.0 1 banana True
4 27.0 1 computer False
3 100.0 1 toy False
5 200.0 1 book True
6 200.5 3 mouse True
7 202.0 3 door True
- 创建了分组
g = df.groupby((df['asc_diff'].shift() != df['asc_diff']).cumsum())
for k, v in g:
print(f'[group {k}]')
print(v)
[group 1]
price weight product asc_diff
2 18.0 2 car True
1 20.0 2 apple True
0 22.0 1 banana True
[group 2]
price weight product asc_diff
4 27.0 1 computer False
3 100.0 1 toy False
[group 3]
price weight product asc_diff
5 200.0 1 book True
6 200.5 3 mouse True
7 202.0 3 door True
到目前为止一切都很好,但当我需要聚合时就会遇到问题:
def product_join(x):
return ' '.join(x)
g.agg({'weight': 'max', 'product': product_join})
weight product
asc_diff
1 2 car apple banana
2 1 computer toy
3 3 book mouse door
问题:
- 只有组1和3应该被聚合(但在代码中它适用于所有组)
- 即使使用自定义函数(例如 product_join),我也无法访问其他列的值,以便例如获取加权平均价格。
- 仅聚合1和3组(其中 asc_diff 为true)并保持2组不变
- 在 price 聚合函数中需要一个函数来访问两个列(即 price 和 weight)以获取加权平均值