How can i filter pandas endswith mail column?

Question

I want to print the ones that match the last indexes of the mails I read from the Excel file, @gmail.com. I tried a code like this.

import pandas as pd



excelRead = pd.read_excel('mailing.xlsx')
excelRead.dropna(inplace= True)
excelTest = (excelRead['mails'][:-11] == "@gmail.com")
print(excelTest)

The output I get is True or False. I tried a code like this because it says in the sources I researched that I need to import it into a DataFrame.

import pandas as pd



excelRead = pd.read_excel('mailing.xlsx')
excelRead.dropna(inplace= True)
excelTest = excelRead[(excelRead['mails'][:-11] == "@gmail.com")]
print(excelTest)

But this is the output I got

pandas.core.indexing.IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match).

Where am I making mistakes in the executed codes?

You can also use `excelRead[excelRead['mails'].str.endswith("@gmail.com")]` — Achille G, Sep 07 '22 at 09:47

score 0 · Accepted Answer · answered Sep 07 '22 at 09:43

If use excelRead['mails'][:-11] get last 11 elements of column mails, compare with "@gmail.com" - get boolean Series with 11 values and then if compare with original DataFrame it raise your error - index of 11 elements is different like index of excelRead DataFrame.

Correct solution is use str for last 9 elements:

excelTest = excelRead[excelRead['mails'].str[:-9] == "@gmail.com"]

Or:

excelTest = excelRead[excelRead['mails'].str.endswith("@gmail.com")]

How can i filter pandas endswith mail column?

1 Answers1