在R中基于CSV读取的DataFrame进行条件匹配统计的实现

Got it, let's break down how to solve this problem using pandas. I'll walk you through each step clearly, with code you can adapt to your actual data.

Step 1: Extract Relevant Columns from dos_log #

First, we'll pull out the psno and new_log_value columns we need. We'll also convert new_log_value to a datetime format so we can easily compare it to trans_month later:

import pandas as pd

# Extract the required columns and standardize the date format
dos_subset = dos_log[['psno', 'new_log_value']].copy()
# Adjust the format string to match your actual new_log_value format (e.g., '%Y%m' for 202305)
dos_subset['log_month'] = pd.to_datetime(dos_subset['new_log_value'], format='%Y-%m')

Step 2: Prepare net_pay for Comparison #

Next, we need to convert trans_month in net_pay to the same datetime format as our log month. This ensures we can reliably compare which transactions happened after the log date:

# Convert trans_month to datetime (update format to match your data's structure)
net_pay['trans_month_dt'] = pd.to_datetime(net_pay['trans_month'], format='%Y-%m')

Step 3: Merge DataFrames and Filter Relevant Records #

Now we'll merge the two datasets on psno to link each log entry to its corresponding transactions. Then we'll filter for transactions that happened after the log month and have a non-zero value:

# Merge the two DataFrames to connect psno entries across datasets
merged_data = pd.merge(dos_subset, net_pay, on='psno', how='left')

# Filter for transactions post-log-month with non-zero payment
# Replace 'payment_amount' with your actual column name for the transaction value
filtered_records = merged_data[
    (merged_data['trans_month_dt'] > merged_data['log_month']) &
    (merged_data['payment_amount'] != 0)
]

Step 4: Count Non-Zero Transactions and Build Result DataFrame #

Finally, we'll count how many non-zero transactions each psno has after their log month. We'll also ensure all psno entries from dos_log are included (even those with zero matching transactions) by filling missing counts with 0:

# Count non-zero transactions per psno and log value
counts = filtered_records.groupby(['psno', 'new_log_value'])['psno'].count().reset_index(name='non_zero_post_log_count')

# Merge back with the original dos_subset to retain all psno entries (fill 0 where no matches exist)
result_df = dos_subset[['psno', 'new_log_value']].merge(
    counts, on=['psno', 'new_log_value'], how='left'
).fillna(0).astype({'non_zero_post_log_count': int})

Quick Adaptation Tips #

• Date Format: If your new_log_value or trans_month use a different format (like '2023/05' or 202305), update the format parameter in pd.to_datetime() to match.
• Payment Column: Swap payment_amount with the actual column name in net_pay that holds the transaction value you're checking.
• Left Join: Using how='left' guarantees we don't lose any psno entries from dos_log, even if they have no matching transactions in net_pay.

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