如何在Pandas中统计唯一Lead ID对应的银行账户ID及账户总数

Hey there! Let's work through your problem using Pandas, step by step. First, we'll load your dataset correctly, then get the grouped results you need (each Lead ID's associated bank accounts and total count), and also cover general methods for counting unique rows and their occurrences in Pandas.

1. 加载数据集 #

First, let's structure your sample data into a Pandas DataFrame:

import pandas as pd

# 构造你提供的数据集
data = {
    'Lead ID': [308148.0, 310443.0, 310443.0, 312745.0, 312745.0, 312745.0, 312745.0, 312745.0, 313082.0, 314036.0, 314559.0, 314559.0, 316728.0, 316728.0, 316728.0, 316728.0, 316728.0, 316728.0, 318465.0, 318465.0, 318465.0, 318465.0, 318465.0, 318465.0, 318465.0, 321146.0, 321146.0, 321218.0, 321356.0, 321356.0, 321356.0],
    'bank_account_id': [12460.0, 12654.0, 12655.0, 12835.0, 12836.0, 12837.0, 12838.0, 12839.0, 13233.0, 13226.0, 13271.0, 13273.0, 13228.0, 13230.0, 13232.0, 13234.0, 13235.0, 13272.0, 13419.0, 13420.0, 13421.0, 13422.0, 13423.0, 13424.0, 13425.0, 13970.0, 13971.0, 14779.0, 15142.0, 15144.0, 15146.0],
    'NO.of account': [1]*31
}

df = pd.DataFrame(data)

2. 获取每个Lead ID对应的账户列表及总数 #

To get each unique Lead ID's associated bank accounts and the total count of accounts per Lead ID, we'll use groupby() with custom aggregations:

# 分组并聚合得到账户列表和总数
grouped_result = df.groupby('Lead ID').agg(
    bank_account_ids=('bank_account_id', list),
    total_accounts=('bank_account_id', 'count')
).reset_index()

# 查看结果
print(grouped_result)

输出结果示例： #

Lead ID                                  bank_account_ids  total_accounts
0   308148.0                                      [12460.0]               1
1   310443.0                            [12654.0, 12655.0]               2
2   312745.0  [12835.0, 12836.0, 12837.0, 12838.0, 12839.0]               5
3   313082.0                                      [13233.0]               1
4   314036.0                                      [13226.0]               1
5   314559.0                            [13271.0, 13273.0]               2
6   316728.0  [13228.0, 13230.0, 13232.0, 13234.0, 13235...               6
7   318465.0  [13419.0, 13420.0, 13421.0, 13422.0, 13423...               7
8   321146.0                            [13970.0, 13971.0]               2
9   321218.0                                      [14779.0]               1
10  321356.0                            [15142.0, 15144.0, 15146.0]               3

3. Pandas中统计唯一行及其出现次数的通用方法 #

Here are the most common ways to handle unique rows and their counts:

方法1：使用value_counts() #

This is the quickest way to count occurrences of unique row combinations (e.g., Lead ID + bank_account_id):

# 统计Lead ID和bank_account_id组合的出现次数
unique_row_counts = df[['Lead ID', 'bank_account_id']].value_counts().reset_index(name='occurrences')

This returns results sorted by occurrence count in descending order.

方法2：使用groupby() + size() #

If you prefer more control over the grouping logic, use groupby() followed by size():

# 分组统计唯一组合的次数
unique_row_counts = df.groupby(['Lead ID', 'bank_account_id']).size().reset_index(name='occurrences')

获取唯一行 #

To get just the unique rows (removing duplicates), use drop_duplicates():

# 获取Lead ID和bank_account_id的唯一组合
unique_rows = df.drop_duplicates(subset=['Lead ID', 'bank_account_id'])

The subset parameter lets you specify which columns to use to determine duplicates; omit it to check all columns.

内容的提问来源于stack exchange，提问作者Sanjeev Aashu