Delete the rows with repeated characters in the dataframe

Question

I have a large dataset from csv file to clean with the patterns I've identified but I can't upload the file here so I've just hardcoded a small sample to give an overview of what I'm looking for. The identified patterns are the repeated characters in the values. However, if you look at the dataframe below, there are actually repeated 'single characters' like ssssss, fffff, aaaaa, etc and then the repeated 'double characters' like dgdg, bvbvbv, tutu, etc. There are also repeated 'triple characters' such as yutyut and fdgfdg.

Despite of this, would it be also possible to delete the rows with ANY repeated 'single/double/triple characters' so that I can apply them to the large dataset? For example, the dataframe here only shows the patterns I identified above, however, there could be repeated characters of ANY letters like 'uuuu', 'zzzz', 'eded, 'rsrsrs', 'xyzxyz', etc in the large dataset.

        Address1        Address2            Address3        Address4
0    High Street     Park Avenue     St. John’s Road       The Grove
1        wssssss    The Crescent             tyutyut       Mill Road
2      qfdgfdgdg        dddfffff  qdffgfdgfggfbvbvbv  sefsdfdyuytutu
3     Green Lane  Highfield Road    Springfield Road     School Lane
4       Kingsway    Stanley Road       George Street     Albert Road
5  Church Street      New Street           Queensway        Broadway
6       qaaaaass          mjkhjk           chfghfghh         fghfhfh

Here's the code:

import pandas as pd
import numpy as np

data = {'Address1': ['High Street', 'wssssss', 'qfdgfdgdg', 'Green Lane', 'Kingsway', 'Church Street', 'qaaaaass'],
        'Address2': ['Park Avenue', 'The Crescent', 'dddfffff', 'Highfield Road', 'Stanley Road', 'New Street', 'mjkhjk'],
        'Address3': ['St. John’s Road', 'tyutyut', 'qdffgfdgfggfbvbvbv', 'Springfield Road', 'George Street', 'Queensway', 'chfghfghh'],
        'Address4': ['The Grove', 'Mill Road', 'sefsdfdyuytutu', 'School Lane', 'Albert Road', 'Broadway', 'fghfhfh']}


address_details = pd.DataFrame(data)

#Code to delete the data for the identified patterns




print(address_details)

The output I expect is:

       Address1         Address2            Address3        Address4
0    High Street     Park Avenue     St. John’s Road       The Grove
1     Green Lane  Highfield Road    Springfield Road     School Lane
2       Kingsway    Stanley Road       George Street     Albert Road
3  Church Street      New Street           Queensway        Broadway

Please advise, thank you!

user_8708364 · Answer

Try with str.contains and loc with agg:

print(address_details.loc[~address_details.agg(lambda x: x.str.contains(r"(.)\1+\b"), axis=1).any(1)])

Output:

        Address1        Address2          Address3     Address4
0    High Street     Park Avenue   St. John’s Road    The Grove
3     Green Lane  Highfield Road  Springfield Road  School Lane
4       Kingsway    Stanley Road     George Street  Albert Road
5  Church Street      New Street         Queensway     Broadway

Or if you care about index:

print(address_details.loc[~address_details.agg(lambda x: x.str.contains(r"(.)\1+\b"), axis=1).any(1)].reset_index(drop=True))

Output:

        Address1        Address2          Address3     Address4
0    High Street     Park Avenue   St. John’s Road    The Grove
1     Green Lane  Highfield Road  Springfield Road  School Lane
2       Kingsway    Stanley Road     George Street  Albert Road
3  Church Street      New Street         Queensway     Broadway

Edit:

For only lowercase letters, try:

print(address_details.loc[~address_details.agg(lambda x: x.str.contains(r"([a-z]+)\1{1,}\b"), axis=1).any(1)].reset_index(drop=True))

Delete the rows with repeated characters in the dataframe

1 Answers