使用Pandas筛选定期月度薪资支付重复记录

Alright, let's work through this problem where you need to pull out the regular monthly salary records from your dataset. The goal is to keep only those rows where the same Beneficient, Payer, and Amount show up across multiple months—those are the consistent recurring payments we're targeting.

Step 1: Break Down the Data #

First, let's lay out your original data clearly for reference:

Your desired result focuses on the repeating combinations (X-A-3000 and Y-B-7000) that occur each month:

Step 2: SQL Solutions to Get the Job Done #

Here are two straightforward methods to achieve this, depending on your database preferences:

Approach 1: Window Functions (Clean and Efficient)

This method calculates how many distinct months each (Beneficient, Payer, Amount) combination appears in, then filters to keep only those with 2+ months of entries:

WITH monthly_recurrence AS (
    SELECT 
        *,
        COUNT(DISTINCT DATE_TRUNC('month', Date)) OVER (PARTITION BY Beneficient, Payer, Amount) AS month_count
    FROM your_table_name
)
SELECT Date, Beneficient, Payer, Amount
FROM monthly_recurrence
WHERE month_count >= 2
ORDER BY Beneficient, Payer, Date;

Approach 2: Subquery to Identify Valid Combinations

First, we find all the (Beneficient, Payer, Amount) groups that repeat across months, then join back to the original table to get the full records:

SELECT t.Date, t.Beneficient, t.Payer, t.Amount
FROM your_table_name t
JOIN (
    SELECT Beneficient, Payer, Amount
    FROM your_table_name
    GROUP BY Beneficient, Payer, Amount
    HAVING COUNT(DISTINCT DATE_TRUNC('month', Date)) >= 2
) valid_payments ON t.Beneficient = valid_payments.Beneficient 
                 AND t.Payer = valid_payments.Payer 
                 AND t.Amount = valid_payments.Amount
ORDER BY t.Beneficient, t.Payer, t.Date;

Quick Notes for Adjustments: #

• Replace your_table_name with your actual table name.
• If you're using MySQL instead of PostgreSQL, swap DATE_TRUNC('month', Date) with DATE_FORMAT(Date, '%Y-%m') to extract the month-year part.
• The >= 2 condition ensures we only keep payments that show up in at least two different months—adjust this if you need a stricter recurrence (like 3+ months).

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