使用Python Pandas标记跨行交易：生成满足特定条件的zflag列

Got it, let's break down how to implement this requirement clearly. The goal is to mark transactions where an employee has both car rental-related expenses and car mileage expenses on the same day, with unique numbers for each qualifying (employee + date) group.

Step-by-Step Implementation #

First, let's assume your dataset has at least these columns: employee_id (unique identifier for each employee), transaction_date (date of the transaction), and expense_type (the type of expense).

1. Prepare Helper Columns

We'll first create two boolean columns to flag rows that fall into our target expense categories:

import pandas as pd

# Sample data (replace with your actual dataset)
data = {
    'employee_id': ['a', 'a', 'a', 'b', 'b', 'c', 'c'],
    'transaction_date': ['2024-01-01', '2024-01-01', '2024-01-02', '2024-01-01', '2024-01-01', '2024-01-01', '2024-01-01'],
    'expense_type': ['Car Rental', 'Car Mileage', 'Car Rental', 'Car Rental', 'Car Rental - Gas', 'Car Rental - Gas', 'Car Mileage']
}
df = pd.DataFrame(data)

# Convert date column to datetime (critical for accurate grouping)
df['transaction_date'] = pd.to_datetime(df['transaction_date'])

# Flag rental-related and mileage expenses
df['is_rental'] = df['expense_type'].isin(['Car Rental', 'Car Rental - Gas'])
df['is_mileage'] = df['expense_type'] == 'Car Mileage'

2. Identify Qualifying (Employee + Date) Groups

Next, we group by employee_id and transaction_date to check if a group has both rental and mileage expenses:

# Check each group for presence of both expense types
group_summary = df.groupby(['employee_id', 'transaction_date']).agg(
    has_rental=('is_rental', 'any'),
    has_mileage=('is_mileage', 'any')
).reset_index()

# Filter to only groups that meet the condition
valid_groups = group_summary[group_summary['has_rental'] & group_summary['has_mileage']]

# Assign unique zflag numbers to each valid group
valid_groups['zflag'] = range(1, len(valid_groups) + 1)

3. Merge zflag Back to Original Data

Finally, we merge the zflag values back into the original dataframe so every row in a qualifying group gets the same zflag:

# Merge zflag with original data (keep non-qualifying rows as NaN)
df = df.merge(
    valid_groups[['employee_id', 'transaction_date', 'zflag']],
    on=['employee_id', 'transaction_date'],
    how='left'
)

# Optional: Replace NaN with 0 or another placeholder if needed
# df['zflag'] = df['zflag'].fillna(0).astype(int)

# Clean up helper columns if desired
df = df.drop(['is_rental', 'is_mileage'], axis=1)

Example Output #

Running the code on the sample data will produce this result:

As you can see:

• Employee a's 2024-01-01 transactions get zflag 1 (meets the condition)
• Employee c's 2024-01-01 transactions get zflag 2 (meets the condition)
• Employee b and a's 2024-01-02 transactions stay as NaN (don't meet the condition)

Key Notes #

• Ensure transaction_date is a datetime type (not string) to avoid grouping issues with different date formats.
• The zflag numbers are assigned in the order of the valid groups (sorted by employee and date in this case). If you need a specific ordering, you can sort valid_groups before assigning zflag.
• If you prefer non-null values for non-qualifying rows, uncomment the fillna line to replace NaNs with 0 (or another value).

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