0
$\begingroup$

How to remove rows from a data frame that have special character (any character except alphabet and numbers) I have some unwanted labels which seems to be useless but I want to remove them, this are in column[0] (labels) of my dataframe. What is easiest way to remove the rows with special character in their label column (column[0]) (for instance: ab!, #, !d) from dataframe

For instance in 2d dataframe similar to below, I would like to delete the rows whose column= label contain some specific characters (such as blank, !, ", $, #NA, FG@)

example of dataframe

$\endgroup$
1
  • $\begingroup$ Instead of image can you paste actual data?? $\endgroup$ Dec 15, 2021 at 11:58

2 Answers 2

1
$\begingroup$

You can use the regular expression to clean your data. Below I have compiled an almost complete list of functions that one uses frequently when cleaning text data.

1.) Remove URL
def remove_URL(headline_text):
    url = re.compile(r'https?://\S+|www\.\S+')
    return url.sub(r'', headline_text)

train['headline_text'] = train['headline_text'].apply(remove_URL)

2.) Remove HTML tags (<..>)
def remove_html(headline_text):
    html=re.compile(r'<.*?>')
    return html.sub(r'',headline_text)

train['headline_text'] = train['headline_text'].apply(remove_html)

3.) Removing Pictures/Tags/Symbols/Emojis
def remove_emojis(data):
    emoj = re.compile("["
        u"\U0001F600-\U0001F64F"  # emoticons
        u"\U0001F300-\U0001F5FF"  # symbols & pictographs
        u"\U0001F680-\U0001F6FF"  # transport & map symbols
        u"\U0001F1E0-\U0001F1FF"  # flags (iOS)
        u"\U00002500-\U00002BEF"  # chinese char
        u"\U00002702-\U000027B0"
        u"\U00002702-\U000027B0"
        u"\U000024C2-\U0001F251"
        u"\U0001f926-\U0001f937"
        u"\U00010000-\U0010ffff"
        u"\u2640-\u2642" 
        u"\u2600-\u2B55"
        u"\u200d"
        u"\u23cf"
        u"\u23e9"
        u"\u231a"
        u"\ufe0f"  # dingbats
        u"\u3030"
                      "]+", re.UNICODE)
    return re.sub(emoj, '', data)
train['headline_text'] = train['headline_text'].apply(remove_emoji)

4.) Removing Punctuation
def remove_punct(headline_text):
    table=str.maketrans('','',string.punctuation)
    return headline_text.translate(table)
train['headline_text'] = train['headline_text'].apply(remove_punct)

5.) Remove everything except strings
corpus = []

for i in range(0 ,len(data1)):
    review = re.sub('[^a-zA-Z]', ' ', data1['features'][i])
    corpus.append(review)

EDIT BASED ON THE COMMENT

If you want to remove the rows with special characters then this might help:

# select and then merge rows
# with special characters
print(df[df.label.str.contains(r'[^0-9a-zA-Z]')])
 
# drop the rows
print(df.drop(df[df.label.str.contains(r'[^0-9a-zA-Z]')].index))

Another thing you can do besides dropping the rows would be to convert the non ASCII characters in ASCII. That way you do not loose information. I do not know if it will work in your case but give it a try and lemme know how it goes:

string_with_nonASCII = "àa string withé fuünny charactersß."
encoded_string = string_with_nonASCII.encode("ascii", "ignore")
decode_string = encoded_string.decode()
print(decode_string)

The above code will output a string with funny characters.

Cheers!

$\endgroup$
2
  • $\begingroup$ thanks spectre but I would like to remove the whole rows in a dataframe which column label contains specific character $\endgroup$ Dec 15, 2021 at 9:53
  • $\begingroup$ Edited my answer to reflect the changes $\endgroup$
    – spectre
    Dec 15, 2021 at 11:35
0
$\begingroup$
import re
regex = re.compile('[@_!#$%^&*()<>?/\|}{~:]')
index = []
#this list is used to store index of rows having characters except alphabets and numbers
for i in range(len(df['Tables label__1'])):
    try:
        if(regex.search(df['Tables label__1'][i]) != None):
            index.append(i)
    except:
        index.append(i)
df = df.drop(index)
#dropping all the indexes at once

I hope this code will help you with your task!!!

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.