python - 熊猫:在多列上分组
问题描述
我在学习pandas
,有很强的SQL背景,所以我需要重新思考很多习惯和心态。虽然我认为我理解该groupby()
方法,但我只是不知道如何将它应用于多个列。
假设我们在数据库中有这张表:
+----+--------------+-----------+--------------+-------+
| id | product_name | category | subcategory | price |
+----+--------------+-----------+--------------+-------+
| 1 | product1 | category1 | subcategory1 | 8.41 |
| 2 | product2 | category1 | subcategory1 | 62.74 |
| 3 | product3 | category1 | subcategory2 | 85.84 |
| 4 | product4 | category2 | subcategory2 | 32.71 |
| 5 | product5 | category2 | subcategory1 | 39.62 |
| 6 | product6 | category2 | subcategory1 | 37.43 |
| 7 | product7 | category3 | subcategory2 | 55.01 |
| 8 | product8 | category3 | subcategory1 | 26.91 |
| 9 | product9 | category3 | subcategory3 | 77.13 |
| 10 | product10 | category3 | subcategory3 | 40.79 |
+---+--------------+-----------+--------------+-------+
在多个列上进行聚合非常容易:
select category, subcategory, avg(price) as avg_price from my_table group by category, subcategory
它返回这个:
+-----------+--------------+-----------+
| category | subcategory | avg_price |
+-----------+--------------+-----------+
| category1 | subcategory1 | 35.575 |
| category1 | subcategory2 | 85.84 |
| category2 | subcategory1 | 38.525 |
| category2 | subcategory2 | 32.71 |
| category3 | subcategory1 | 26.91 |
| category3 | subcategory2 | 55.01 |
| category3 | subcategory3 | 58.96 |
+-----------+--------------+-----------+
所以,在我明显不正确的理解中,这在熊猫中也会做同样的事情:
df['price'].groupby(df[['category', 'subcategory']]).mean()
返回ValueError: Grouper for '<class 'pandas.core.frame.DataFrame'>' not 1-dimensional
,而:
df['price'].groupby(df['category']).mean()
按预期工作。
有人可以帮助我吗?
解决方案
我认为你需要做 -
df.groupby(['category', 'subcategory'])['price'].mean()