技术咨询：使用Python分析每个ID对应的多条记录

Got it, let's walk through how to analyze each ID's records in your pandas DataFrame and generate meaningful descriptive stats. First, we'll set up the sample data, then break down different analysis approaches step by step.

Step 1: Set up the sample DataFrame #

First, let's replicate your data so we can work with it directly:

import pandas as pd

data = {
    'ID': [1,2,2,3,3,3,4],
    'Date': ['09/12', '09/13', '09/13', '09/13', '09/13', '09/13', '09/12'],
    'Name': ['Ann', 'Pete', 'Pete', 'Ann', 'Ann', 'Ann', 'Pete'],
    'ColA': ['String']*7,
    'ColB': ['String']*7,
    'ColC': ['String']*7,
    'ColD': ['String']*7,
    'Column_Interest': ['OneThing', 'OneThing', 'AnotherThing', 'OneThing', 'AnotherThing', 'ThirdThing', 'OneThing']
}

df = pd.DataFrame(data)

Step 2: Basic record counts per ID #

Start with the simplest stat: how many records each ID has. This helps identify IDs with multiple entries (like ID 2 and 3 in your data):

# Count records per ID
record_counts = df.groupby('ID').size().reset_index(name='Record_Count')
print(record_counts)

Output:

ID  Record_Count
0   1             1
1   2             2
2   3             3
3   4             1

Step 3: Analyze Column_Interest per ID #

Since this column has multiple values for some IDs, we can get both the unique interests and their occurrence counts:

# 1. Count how many times each interest appears per ID
interest_frequency = df.groupby(['ID', 'Column_Interest']).size().reset_index(name='Interest_Count')
print(interest_frequency)

# 2. Get a list of unique interests per ID
unique_interests = df.groupby('ID')['Column_Interest'].agg(lambda x: list(x.unique())).reset_index(name='Unique_Interests')
print(unique_interests)

Sample output for unique_interests:

ID               Unique_Interests
0   1                  [OneThing]
1   2        [OneThing, AnotherThing]
2   3  [OneThing, AnotherThing, ThirdThing]
3   4                  [OneThing]

Step 4: Date-related stats per ID #

If you want to understand date patterns for each ID (like first/last occurrence, number of unique dates):

date_summary = df.groupby('ID')['Date'].agg(
    First_Date='min',
    Last_Date='max',
    Unique_Dates='nunique'
).reset_index()
print(date_summary)

In your sample, all records for an ID share the same date, so Unique_Dates will be 1 for all.

Step 5: Combine all stats into a single summary #

For a holistic view, aggregate multiple stats into one DataFrame:

full_summary = df.groupby('ID').agg(
    Record_Count=('ID', 'size'),
    Unique_Name=('Name', 'nunique'),  # Check if name is consistent per ID
    Associated_Name=('Name', 'first'),  # Get the name linked to the ID
    First_Date=('Date', 'min'),
    Last_Date=('Date', 'max'),
    Unique_Dates=('Date', 'nunique'),
    Number_of_Unique_Interests=('Column_Interest', 'nunique'),
    Interest_List=('Column_Interest', lambda x: list(x.unique()))
).reset_index()

print(full_summary)

This gives you a compact overview of every key detail per ID in one place.

Bonus: Filter IDs with multiple records #

If you only care about IDs that have more than one entry (like IDs 2 and 3), use filter():

multi_record_ids = df.groupby('ID').filter(lambda x: len(x) > 1)
print(multi_record_ids)

Note: If ColA-ColD were numeric instead of strings, you could add stats like mean, median, or std to the aggregation to analyze their distribution per ID.

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