Pandas filter rows based on separate dataframe row values

Viewed 44

I have two column-wise identical dataframes ocrmatches and fpmatches

ocrmatches.shape output:

(30325, 7)

fpmatches.shape output:

(10268, 7)

ocrmatches.dtypes output:

match_id                      int64
case_id                       int64
match_status                 object
last_updated_date    datetime64[ns]
entity_id                     int64
entity_score                float64
entity_version                int64
dtype: object

fpmatches.dtypes output:

match_id                      int64
case_id                       int64
match_status                 object
last_updated_date    datetime64[ns]
entity_id                     int64
entity_score                float64
entity_version                int64
dtype: object

case_id is a base value for my analysis. case_id can have multiple match_id assigned and match_id can have one or more entity_id assigned while entity_id can have one entity_version, but entity_version may vary. entity_version is an int that contains first 8 digits for the date in YYYYMMDDformat and either 14 or 16 digits for the version itself - the higher the number, the newer the version. entity_version may have coincidental duplicate values for different entity_id. I'm trying to check if:

  • fpmatches df contains case_id values that are present in ocrmatches df,
  • if yes, does that case_id have at least those entity_id values (could be others as well, but not relevant) assigned to it,
  • if yes, does that entity_id has the same or newer entity_version assigned

If case_id in ocrmatches df has correspondent value in fpmatches and that value meets all the above conditions, I can mark that case_id in ocrmatches as True (in a separate column, e.g.).

csv files for the mentioned dataframes can be found here.

I got as far as using pd.groupby(ocrmatches['case_id']), but other than that - utterly stuck. Any ideas on how to proceed would be very much appreciated.

0 Answers
Related