Remove DF Rows Based on a CSV File (Pandas,Python3)

Question

Right now, I am trying to create a function that removes rows based on certain criteria that are outlined within an excel file. This excel file (bad words2) contains word pairs that should be removed from within the DF and looks like this:

header
the man
is a

The second part of my code is function I am trying to apply

import pandas as pd
data = ({'words':['the man','is a','good guy']})
df = pd.DataFrame(data)

xl = pd.ExcelFile('C:/Users/j/Desktop/bad words2.xlsx')
badwords = xl.parse()
badwords = badwords['header']

def removewords(x):
    for w in x:
        pattern = '^'+''.join('(?=.*{})'.format(word) for word in w.split())
        df[df['words'].str.contains(pattern)==False]
        df.dropna()


 print(removewords(badwords))

So ideally, at the end of applying this function, I should end up with a DF that contains only:

 words
 good guy

However, right now, all that this function returns is 'None'. What am I doing wrong?

Sorry are you just wanting to filter out words that are in the pattern or filter out words that are not in the pattern? anyway unclear what you are trying to do but this line does nothing without an assignment: df[df['words'].str.contains(pattern)==False] should be df = df[df['words'].str.contains(pattern)==False] — EdChum
– EdChum, Commented Sep 30, 2014 at 20:04

mechanical_meat · Accepted Answer · 2014-09-30 19:54:45Z

1

Some thoughts:

The final two operations return a new DataFrame. I.e. they do not modify the DataFrame in-place. You need to assign the result of those operations to something, e.g. df.
And then if you do the above, the variable df is not available for assignment within the scope of the function. You can pass it in as an argument. (Please note: This is not a problem with your code, but with the proposed solution.) Alternatively you could instantiate a new DataFrame within the function.
You are not returning the DataFrame at the end of your function.

Try instead:

def removewords(df,x):
    for w in x:
        pattern = '^'+''.join('(?=.*{})'.format(word) for word in w.split())
        df = df[df['words'].str.contains(pattern)==False]
        df = df.dropna()
    return df

print(removewords(df,badwords))

edited Sep 30, 2014 at 19:54

answered Sep 30, 2014 at 18:43

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3682157 Over a year ago

Hi Bernie thanks for the input. However, when I try to assign any type of variable to df (i.e. df = ), I get the error UnboundLocalError: local variable 'df' referenced before assignment

user3682157 Over a year ago

So problem solved, second problem springs up -- the changes being made don't "stack" within the for loop. So using the code above, the return output is "is a / good guy". I was hoping to remove all word pairs that appeared in my excel file so that my final return output was JUST 'Good Guy'

mechanical_meat Over a year ago

I made a mistake in the indentation of the return statement. Please see edited code which now does what you want.

Collectives™ on Stack Overflow

Remove DF Rows Based on a CSV File (Pandas,Python3)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related