How to filter rows containing a similar pattern for a column but different values for another column

user_12914638 0
Viewed 270

i have this dataframe in pandas:

df = pd.DataFrame(
             {"bond": ["XSD070623A17","XSD090222A10","XSD100221A18", "XSD070623T15",
                        "XSD070623T23","XSD090222T32","XSD100221T11"],

             "issue_date":["01.01.2020", "03.05.2020", "05.02.2020", "10.11.2019",
                            "01.01.2020", "03.05.2020", "12.10.2020"]
             }
             )
df


    bond            issue_date
0   XSD070623A17    01.01.2020
1   XSD090222A10    03.05.2020
2   XSD100221A18    05.02.2020
3   XSD070623T15    10.11.2019
4   XSD070623T23    01.01.2020
5   XSD090222T32    03.05.2020
6   XSD100221T11    12.10.2020

As you see, the first 9 characters of the some values are same in the "bond" column. i will get a subset of this dataframe that for every value whose 10th character is equal "A", i wanna get the value which has same the first 9 characters and 10th character is equal "T". After that if issue date of "A" and "T" bonds are different then i wanna filter both of these bonds.

e.g. bond value of 0th index has same pattern with values of 3rd and 4th indexes but issue date of 0th and 4th indexes are same, so i wanna filter 0th and 3rd rows. On the other hand bond value of 1st index has same pattern with 5th index, but their issue dates are same so i wanna filter none of them.

After all, i wanna get the following dataframe:

    bond            issue_date
0   XSD070623A17    01.01.2020
2   XSD100221A18    05.02.2020
3   XSD070623T15    10.11.2019
6   XSD100221T11    12.10.2020

Any help will be appreciated.

pandas python
2 Answers

Hi I had tried the same problem like this before. We can use a regex in df['bond'].str.contains() and here as you want only rows with XSD070623 or XSD100221 and also wants to drop rows which have values of same issue-date. Here's what I did:

#your df:
>>> df
           bond  issue_date
0  XSD070623A17  01.01.2020
1  XSD090222A10  03.05.2020
2  XSD100221A18  05.02.2020
3  XSD070623T15  10.11.2019
4  XSD070623T23  01.01.2020
5  XSD090222T32  03.05.2020
6  XSD100221T11  12.10.2020

Now applying the regex to satisfy your first condition for the bond column and also dropping duplicates of issue-date column and considering the first one, I did:

>>> df[df['bond'].str.contains('XSD070623|XSD100221')].drop_duplicates(subset='issue_date', keep="first")
           bond  issue_date
0  XSD070623A17  01.01.2020
2  XSD100221A18  05.02.2020
3  XSD070623T15  10.11.2019
6  XSD100221T11  12.10.2020

Above 'XSD070623|XSD100221' will be considered as a regex and we will drop duplicates in issue-date column and keeping the first one (in our case).

user_120568210

Here is an answer that does not require any hard coding.

First step: grouping by the first 9 characters that are supposed to be equal, as well as by issue_date. Actually, you don't need to consider the cases where A or T is the 10th character.

The following assumptions have been made:

  • the index does not matter
  • when bond have same first 9 characters: keep the first occurence only
df_grouped = (df.groupby([df.bond.str[:9], df.issue_date])
                .agg({'bond': ['first', 'nunique']}))

                                  bond
                     first        nunique
bond      issue_date  
XSD070623 01.01.2020 XSD070623A17 2
XSD070623 10.11.2019 XSD070623T15 1
XSD090222 03.05.2020 XSD090222A10 2
XSD100221 05.02.2020 XSD100221A18 1
XSD100221 12.10.2020 XSD100221T11 1

Second step: stacking the grouped dataframe

df_grouped = df_grouped.unstack()

Third step: filtering out rows with only one issue date for bonds sharing first 9 characters. Then, stack the result, and reset the index

df_grouped[df_grouped['nunique'].count(axis=1) > 1].stack().reset_index()

  bond      issue_date first        nunique
0 XSD070623 01.01.2020 XSD070623A17 2.0
1 XSD070623 10.11.2019 XSD070623T15 1.0
2 XSD100221 05.02.2020 XSD100221A18 1.0
3 XSD100221 12.10.2020 XSD100221T11 1.0

Final step: keeping useful columns

df_grouped[['first', 'issue_date']].rename(columns={'first': 'bond'})
  first issue_date
0 XSD070623A17 01.01.2020
1 XSD070623T15 10.11.2019
2 XSD100221A18 05.02.2020
3 XSD100221T11 12.10.2020

Please try this on a larger dataset, and let me know if there is any improvement needed :)

user_137650850
Related