Adding prefixes like “Mr.” or “Mrs.” to names in Excel can be a useful task, especially when dealing with large datasets. In this guide, we’ll show you how to use the Python library gender-guesser to automatically add prefixes to names based on gender. This approach can save time and effort compared to manually adding prefixes, especially when working with a large number of names.

Example:

Let’s say you have an Excel file (names.xlsx) with a column of names and you want to add a prefix column with “Mr.” for male names and “Mrs.” for female names. Here’s how you can do it using Python and the gender-guesser library:

1. Install the gender-guesser library if you haven’t already:

pip install gender-guesser

Created Excel with names:

Now let us go for the code to add a column known as prefix in the same sheet as above which is names.xlsx which has Mr & Mrs written against names after identifying whether it is male or female.

Code:

import pandas as pd
import gender_guesser.detector as gender
from openpyxl import load_workbook

# Load the Excel file
df = pd.read_excel('names.xlsx')

# Create an instance of the gender detector
d = gender.Detector()

# Function to add prefix based on gender
def add_prefix(row):
    name = row['Name']
    gender = d.get_gender(name.split()[0])
    if gender in ['male', 'mostly_male']:
        return 'Mr. ' + name
    elif gender in ['female', 'mostly_female']:
        return 'Mrs. ' + name

# Apply the function to the 'Name' column and create a new column 'Prefix'
df['Prefix'] = df.apply(add_prefix, axis=1)

# Load the Excel file into openpyxl
wb = load_workbook('names.xlsx')
ws = wb.active

# Iterate over the rows and update the 'Prefix' column with the prefixed names
for idx, value in enumerate(df['Prefix'], start=2):  # Start from row 2 to skip header
    ws.cell(row=idx, column=2, value=value)  # Assuming 'Prefix' column is the second column

# Save the modified Excel file
wb.save('names.xlsx')

Explanation of the above code:

1. import pandas as pd: Imports the pandas library and assigns it the alias pd, which is a commonly used convention.
2. import gender_guesser.detector as gender: Imports the Detector class from the gender_guesser.detector module and assigns it the alias gender.
3. from openpyxl import load_workbook: Imports the load_workbook function from the openpyxl library, which is used to load an existing Excel file.
4. df = pd.read_excel('names.xlsx'): Reads the Excel file 'names.xlsx' into a pandas DataFrame named df.
5. d = gender.Detector(): Creates an instance of the Detector class from the gender_guesser library, which is used to guess the gender of names.
6. def add_prefix(row):: Defines a function add_prefix that takes a row of data from the DataFrame as input.
7. name = row['Name']: Retrieves the value in the 'Name' column from the input row.
8. gender = d.get_gender(name.split()[0]): Uses the Detector instance d to guess the gender of the first word in the name.
9. if gender in ['male', 'mostly_male']:: Checks if the guessed gender is male or mostly male.
10. return 'Mr. ' + name: If the gender is male or mostly male, returns the name prefixed with 'Mr. '.
11. elif gender in ['female', 'mostly_female']:: Checks if the guessed gender is female or mostly female.
12. return 'Mrs. ' + name: If the gender is female or mostly female, returns the name prefixed with 'Mrs. '.
13. df['Prefix'] = df.apply(add_prefix, axis=1): Applies the add_prefix function to each row of the DataFrame df along axis 1 (column-wise) and creates a new column 'Prefix' with the prefixed names.
14. wb = load_workbook('names.xlsx'): Loads the Excel file 'names.xlsx' into an openpyxl Workbook object named wb.
15. ws = wb.active: Gets the active worksheet from the Workbook object wb and assigns it to the variable ws.
16. for idx, value in enumerate(df['Prefix'], start=2):: Iterates over the prefixed names in the 'Prefix' column of the DataFrame df, starting from row 2 (to skip the header).
17. ws.cell(row=idx, column=2, value=value): Updates the cell in the 'Prefix' column (column=2) of the worksheet ws at the current row idx with the value value (the prefixed name).
18. wb.save('names.xlsx'): Saves the modified Excel file back to 'names.xlsx'.

This code reads an Excel file containing names, guesses the gender of each name, adds a prefix (‘Mr.’ or ‘Mrs.’) based on the gender, and then saves the modified names back to the same Excel file with an additional ‘Prefix’ column.

Output:

Conclusion:

In this guide, we’ve demonstrated how to use the Python library gender-guesser to automatically add prefixes like “Mr.” or “Mrs.” to names in an Excel file. By leveraging the gender-guesser library, we were able to guess the gender of each name based on the first name and add the appropriate prefix accordingly. This approach can be particularly useful when working with large datasets, as it saves time and ensures consistency in adding prefixes to names.

What else you can do with the gender-guesser library:

1. Gender Analysis: The gender-guesser library can be used to analyze the gender distribution in a dataset based on names. This can be helpful in demographic studies or marketing research.
2. Data Cleaning: You can use the gender-guesser library to clean up datasets by standardizing gender-related fields, such as adding prefixes or correcting gender-specific errors.
3. Personalization: In applications where personalization is important, such as email marketing or customer communication, you can use the gender-guesser library to address individuals with the appropriate prefix based on their name.
4. Educational Purposes: The gender-guesser library can be used in educational settings to teach students about data analysis and gender prediction algorithms.

Overall, the gender-guesser library provides a simple yet effective way to guess the gender of names and perform gender-related analysis and data manipulation tasks in Python.