英文:
How to group by counted value (python, pandas)
问题
你好，你可以尝试使用以下代码来实现你的需求：
import pandas as pd
# 创建DataFrame
data = {'SerialNr': [1, 2, 3, 4],
'Amount': [2, 4, 3, 2]}
df = pd.DataFrame(data)
# 使用groupby和count函数来计算数量
result = df.groupby('Amount')['SerialNr'].count().reset_index()
result.columns = ['Amount', 'Quantity of SerialNr']
# 打印结果
print(result)
这段代码会根据 "Amount" 列进行分组，然后计算每个分组中的 "SerialNr" 数量，最后得到你所需的结果表格。
英文:
Hello I have this Dataframe:
| SerialNr | Amount |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
And I want a table that shows me how many serial numbers in the right column have the same numbers.
Here for example it would look like this
| Amount | Quantity of SerialNr |
|---|---|
| 1 | 0 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
It should sum up the Serialnummer with the same amount
Tried to do it with groupby but did not work
答案1
得分: 1
根据您的期望输出需要Amount的数量，因此可以使用Series.value_counts结合Series.reindex，以1为起始点进行如下操作：
s = df['Amount'].value_counts()
df = (s.reindex(range(1, s.index.max()+1), fill_value=0)
.rename_axis('Amount')
.reset_index(name='Amount的数量'))
print (df)
Amount Amount的数量
0 1 0
1 2 2
2 3 1
3 4 1
或者使用Categorical：
cats = range(1, df.Amount.max()+1)
df = (pd.Categorical(df['Amount'], categories=cats)
.value_counts()
.rename_axis('Amount')
.reset_index(name='Amount的数量'))
print (df)
Amount Amount的数量
0 1 0
1 2 2
2 3 1
3 4 1
英文:
From your expected ouput need Quantity of Amount, so use Series.value_counts with Series.reindex by range starting by 1:
s = df['Amount'].value_counts()
df = (s.reindex(range(1, s.index.max()+1), fill_value=0)
.rename_axis('Amount')
.reset_index(name='Quantity of Amount'))
print (df)
Amount Quantity of SerialNr
0 1 0
1 2 2
2 3 1
3 4 1
Or use Categorical:
cats = range(1, df.Amount.max()+1)
df = (pd.Categorical(df['Amount'], categories=cats)
.value_counts()
.rename_axis('Amount')
.reset_index(name='Quantity of Amount'))
print (df)
Amount Quantity of Amount
0 1 0
1 2 2
2 3 1
3 4 1
答案2
得分: 1
另一个可能的解决方案：
(pd.concat([df.set_index('SerialNr'),
df['Amount'].value_counts().rename('Amount的数量')], axis=1)
.fillna(0, downcast='infer').reset_index()
.drop('Amount', axis=1).rename({'index': 'Amount'}, axis=1))
输出：
Amount count
0 1 0
1 2 2
2 3 1
3 4 1
英文:
Another possible solution:
(pd.concat([df.set_index('SerialNr'),
df['Amount'].value_counts().rename('Quantity of Amount')], axis=1)
.fillna(0, downcast='infer').reset_index()
.drop('Amount', axis=1).rename({'index': 'Amount'}, axis=1))
Output:
Amount count
0 1 0
1 2 2
2 3 1
3 4 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论