How to filter column by a specific language

Mansa_TCC

Hi,

I’m currently working on a dataset that contains a “Page Title” column with rows in both English and other languages. I want to set up a filter to exclude all non-English titles. I use the following REGEX formula:

CASE
WHEN Page Title REGEXP '^[A-Za-z0-9 .,!?-]*$' THEN 'English'
ELSE 'Other'
END

This formula helps remove all non-Latin alphabets and special characters. However, a few Portuguese and Spanish titles are still present.

Is there a way to exclude all non-English titles using a SQL query or a more advanced beast mode formula than what I’m currently using?

Thanks!

ArborRose

If you are trying to capture other titles that include accented characters, the following might work.

CASE

WHEN Page Title REGEXP '^[A-Za-zÀ-ÿ0-9 .,!?-]*$' THEN 'English'

ELSE 'Other'

END

This would cover most accented Latin characters used in European countries.

Basic Latin alphabet characters (A-Za-z)
Accented characters used in European languages (À-ÿ)
Numbers (0-9)
Common punctuation (.,!?-)

Manasi_Panov

Hello @Mansa_TCC,

If the title in Portuguese or Spanish contains only standard English letters, this might be why you can't filter them.

This can be done with Domo Jupyter if you have access to this function.

Here is the script:

pip install langdetect
from langdetect import detect

#Function to detect language
def detect_language(title):
try:
return detect(title)
except LangDetectException:
return 'Unknown'

#Apply the function to create the 'Language' column

sample['Language'] = sample['title'].apply(detect_language)

#Display the DataFrame with the new column
sample

Here is the result:

You can then write to the dataset with the new column.

I can't think of any other solution. Unfortunately, I don't see 'langdetect' as part of the Python in ETL and you can't do it from there. If you need, I can help you with the rest of the steps.

ArborRose

If you are trying to capture other titles that include accented characters, the following might work.

CASE

WHEN Page Title REGEXP '^[A-Za-zÀ-ÿ0-9 .,!?-]*$' THEN 'English'

ELSE 'Other'

END

This would cover most accented Latin characters used in European countries.

Basic Latin alphabet characters (A-Za-z)
Accented characters used in European languages (À-ÿ)
Numbers (0-9)
Common punctuation (.,!?-)

Manasi_Panov

Hello @Mansa_TCC,

If the title in Portuguese or Spanish contains only standard English letters, this might be why you can't filter them.

This can be done with Domo Jupyter if you have access to this function.

Here is the script:

pip install langdetect
from langdetect import detect

#Function to detect language
def detect_language(title):
try:
return detect(title)
except LangDetectException:
return 'Unknown'

#Apply the function to create the 'Language' column

sample['Language'] = sample['title'].apply(detect_language)

#Display the DataFrame with the new column
sample

Here is the result:

You can then write to the dataset with the new column.

I can't think of any other solution. Unfortunately, I don't see 'langdetect' as part of the Python in ETL and you can't do it from there. If you need, I can help you with the rest of the steps.