pandas how to compare similar rows and then drop by conditions

user_7984318 0
Viewed 53

I have a dataframe:

dfs = """
    contract  RB  BeginDate  ValIssueDate   EndDate   Valindex0
1  A00118  46   19000100      19880901  19841231          50
2  A00118  46   19850100      19880901  99999999          50
3  A00118  47   19000100      19880901  19831231          47
4  A00118  47   19840100      19880901  19841299          47
5  A00118  47   19850100      19880901  99999999          50
6  A00253  48   19000100      19820101  19811231          47
7  A00253  48   19820100      19820101  19841299          47
8  A00253  48   19850100      19820101  99999999          50
9  A00253  50   19000100      19820101  19781231          47
10 A00253  50   19790100      19820101  19841299          47
11 A00253  50   19850100      19820101  99999999          50
12 A00253  4L   20170101      19880901  99999999          39


"""
df = pd.read_csv(StringIO(dfs.strip()), sep='\s+', 
                  dtype={"RB": str, "BeginDate": int, "EndDate": int,'ValIssueDate':int,'Valindex0':int})

df:

   contract RB  BeginDate   ValIssueDate    EndDate Valindex0
1   A00118  46  19000100    19880901    19841231    50
2   A00118  46  19850100    19880901    99999999    50
3   A00118  47  19000100    19880901    19831231    47
4   A00118  47  19840100    19880901    19841299    47
5   A00118  47  19850100    19880901    99999999    50
6   A00253  48  19000100    19820101    19811231    47
7   A00253  48  19820100    19820101    19841299    47
8   A00253  48  19850100    19820101    99999999    50
9   A00253  50  19000100    19820101    19781231    47
10  A00253  50  19790100    19820101    19841299    47
11  A00253  50  19850100    19820101    99999999    50
12 A00253  4L   20170101      19880901  99999999    39

I want to drop rows by this condition:

if this row has the same 'contract' and 'RB' with other row, but its 'ValIssueDate' is not between 'BeginDate' and 'EndDate',then drop this row.

Notice the last row,it has unique RB so it should not be dropped.

index_names = df[ (df['ValIssueDate'] <= df['EndDate'] ) | (df['ValIssueDate'] >= df['BeginDate'])].index
# drop these given row
# indexes from dataFrame
df.drop(index_names, inplace = True)

This method only compares within 1 row, but how to compare different rows with my conditions ?

The output should be:

    contract  RB  BeginDate  ValIssueDate   EndDate   Valindex0
2  A00118  46   19850100      19880901  99999999          50
5  A00118  47   19850100      19880901  99999999          50
7  A00253  48   19820100      19820101  19841299          47
10 A00253  50   19790100      19820101  19841299          47
12 A00253  4L   20170101      19880901  99999999          39
pandas dataframe numpy python
1 Answers

Instead of dropping the rows you don't need, keep the rows you need. The boolean indexing you did comes very close to what you actually need:

  1. For rows where the contract and RB are duplicated, use the condition on ValIssueDate
  2. For rows with unique contract and RB, keep all rows.
df = df[((df.duplicated(subset = ["contract", "RB"], keep=False)) & 
         (df['ValIssueDate'] <= df['EndDate'] ) & 
         (df['ValIssueDate'] >= df['BeginDate'])) | 
        ~df.duplicated(subset = ["contract", "RB"], keep=False)]

>>> df
   contract  RB  BeginDate  ValIssueDate   EndDate  Valindex0
2    A00118  46   19850100      19880901  99999999         50
5    A00118  47   19850100      19880901  99999999         50
7    A00253  48   19820100      19820101  19841299         47
10   A00253  50   19790100      19820101  19841299         47
12   A00253  4L   20170101      19880901  99999999         39
user_98576310
Related