Extracting text from column and making it lowercase and exists in array

user_8797830 0
Viewed 42

I want to extract text from a column in my df where the df has column text like:

df:

    text
    @bobby
    @mike
    why @mike
    huh, @brad
    @brad
    cmon @Sunny_Bat
    @Sunny_Bat
    @But_ten
    @g2
    @Mikey/fj
    @4242343

I have code that gives me the extracted text or word after @ like below:

df['text'].str.extract(r"@([A-Za-z_]+([0-9]+)*)")[0]

This gives me the output I want by extracting the word if it exists but is not a number as the expected output below:

Desired Output:

text        | new column
@bobby          bobby
@mike           mike
why @mike       mike
huh, @brad      brad
@brad           brad 
cmon @Sunny_bat Sunny_bat
@Sunny_Bat      Sunny_bat
@But_ten        But_ten
@g2             g2
@Mikey/fj       Mikey
@4242343        NaN

But what I am missing is I want my new column to have values that exist in another column in the same df or an array of unique values of that column called unique_values like below:

isin(unique_values))

I am trying to add via something like:

where(lambda x: x[0].isin(name_list))

but not sure how to add to my above code

Also,

Where I can add the isin to the end of the code above to output only values or words that exist in this array, also I want the outputted words to all be lowercase only since the words in unique values are lowercase but in the text column that is being extracted are not lower case.

Thanks!

pandas python-3.x python regex
1 Answers

You can change regex for remove second pair () and * for optional integers, add expand=False for Series, convert to lowercase for match list in Series.isin chain | for bitwise Or for test missing values with boolean indexing:

df['new'] = df['text'].str.extract(r"@([A-Za-z_]+[0-9]*)", expand=False).str.lower()
print (df)
                   text        new
0                @bobby      bobby
1                 @mike       mike
2             why @mike       mike
3            huh, @brad       brad
4                 @brad       brad
5       cmon @Sunny_Bat  sunny_bat
6            @Sunny_Bat  sunny_bat
7              @But_ten    but_ten
8                   @g2         g2
9             @Mikey/fj      mikey
10             @4242343        NaN

unique_values = ['sunny_bat','brad','bobby']

df = df[df['new'].isin(unique_values) | df['new'].isna()]
print (df)
                   text        new
0                @bobby      bobby
3            huh, @brad       brad
4                 @brad       brad
5       cmon @Sunny_Bat  sunny_bat
6            @Sunny_Bat  sunny_bat
10             @4242343        NaN
user_29010020
Related