Human resources recruiters often deal with tasks such as processing numerous resumes, scheduling interviews, and evaluating candidates. Python provides powerful tools to automate these processes and increase work efficiency. Below are examples of how Python scripts can assist with recruitment tasks.
Sample Data (candidates.xlsx)
First, we create a sample Excel file, candidates.xlsx, and populate it with some example data.
import pandas as pd
# Create sample data
data = {
'Name': ['Zhang San', 'Li Si', 'Wang Wu', 'Zhao Liu', 'Sun Qi'],
'Contact': ['13800000000', '13900000000', '13700000000', '13600000000', '13500000000'],
'Email': ['zhangsan@example.com', 'lisi@example.com', 'wangwu@example.com', 'zhaoliu@example.com', 'sunqi@example.com'],
'Job Position': ['Software Engineer', 'Product Manager', 'UI Designer', 'Test Engineer', 'Project Manager'],
'Experience (Years)': [3, 5, 2, 4, 6],
'Education': ['Bachelor', 'Master', 'Bachelor', 'Bachelor', 'Master'],
'Skills': ['Python, Java, SQL', 'Project Management, Product Design', 'Photoshop, Sketch', 'Selenium, JUnit', 'Agile Development, Scrum']
}
# Save to Excel file
df = pd.DataFrame(data)
df.to_excel('candidates.xlsx', index=False)Run the above code to generate a sample data file, candidates.xlsx.
1. Reading an Excel File
Scenario: Load candidate data from an Excel file.
Explanation: Use pandas.read_excel() to read the Excel file.
import pandas as pd
# Read the Excel file
df = pd.read_excel('candidates.xlsx')
print(df.head())2. Writing to an Excel File
Scenario: Save processed candidate data to a new Excel file.
Explanation: Use DataFrame.to_excel() to write the Excel file.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Write to a new Excel file
df.to_excel('processed_candidates.xlsx', index=False)3. Updating a Specific Cell
Scenario: Modify specific information for a candidate.
Explanation: Use indexing to locate the cell and assign a new value.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Update the contact of the first candidate
df.at[0, 'Contact'] = '13811111111'
# Save the updated data
df.to_excel('updated_candidates.xlsx', index=False)4. Adding a New Worksheet
Scenario: Add a new worksheet to an existing Excel file.
Explanation: Use the openpyxl library to manipulate Excel files.
from openpyxl import load_workbook
# Load existing workbook
wb = load_workbook('candidates.xlsx')
# Create a new worksheet
ws = wb.create_sheet(title="New Sheet")
# Save workbook
wb.save('candidates_with_new_sheet.xlsx')5. Deleting a Worksheet
Scenario: Delete a specified worksheet from an Excel file.
Explanation: Use the openpyxl library to delete a worksheet.
from openpyxl import load_workbook
# Load existing workbook
wb = load_workbook('candidates.xlsx')
# Delete the specified worksheet
if 'New Sheet' in wb.sheetnames:
del wb['New Sheet']
# Save workbook
wb.save('candidates_deleted_sheet.xlsx')6. Copying a Worksheet
Scenario: Copy a specified worksheet from an Excel file.
Explanation: Use the openpyxl library to copy a worksheet.
from openpyxl import load_workbook
# Load existing workbook
wb = load_workbook('candidates.xlsx')
# Copy the specified worksheet
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Copied Sheet"
# Save workbook
wb.save('candidates_copied_sheet.xlsx')7. Renaming a Worksheet
Scenario: Rename a specified worksheet in an Excel file.
Explanation: Use the openpyxl library to rename a worksheet.
from openpyxl import load_workbook
# Load existing workbook
wb = load_workbook('candidates.xlsx')
# Rename the specified worksheet
sheet = wb['Sheet1']
sheet.title = "Renamed Sheet"
# Save workbook
wb.save('candidates_renamed_sheet.xlsx')8. Finding Specific Values
Scenario: Search for specific values in an Excel file.
Explanation: Use the pandas library to locate specific values.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Find a specific value
result = df[df['Name'] == 'Zhang San']
print(result)9. Filtering Data
Scenario: Filter data based on specific criteria.
Explanation: Use the pandas library to filter data.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Filter candidates with 3 or more years of experience
filtered_df = df[df['Experience (Years)'] >= 3]
# Print filtered results
print(filtered_df)10. Sorting Data
Scenario: Sort data based on a specific column.
Explanation: Use the pandas library to sort data.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Sort by experience in descending order
sorted_df = df.sort_values(by='Experience (Years)', ascending=False)
# Print sorted results
print(sorted_df)11. Grouping and Summarizing Data
Scenario: Group data by a specific column and compute summaries.
Explanation: Use the pandas library for grouping and aggregation.
import pandas as pd
# Read existing data
df = pd.read_excel('candidates.xlsx')
# Group by job position and count candidates
grouped_df = df.groupby('Job Position').size()
# Print grouped summary
print(grouped_df)12. Merging Cells
Use Case: Merging multiple cells in an Excel file.
Code Explanation: This example uses the openpyxl library to merge cells.
Example Code:
from openpyxl import load_workbook
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Merge cells from A1 to C1
ws.merge_cells('A1:C1')
# Save the workbook
wb.save('candidates_merged_cells.xlsx')13. Formatting Cells
Use Case: Setting the format of cells in an Excel file.
Code Explanation: This example uses the openpyxl library to format cells.
Example Code:
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Set font and alignment for cell A1
cell = ws['A1']
cell.font = Font(bold=True, color="FF0000")
cell.alignment = Alignment(horizontal='center', vertical='center')
# Save the workbook
wb.save('candidates_formatted_cell.xlsx')14. Inserting Charts
Use Case: Inserting a chart into an Excel file.
Code Explanation: This example uses the openpyxl library to insert a chart.
Example Code:
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
# Load existing data
df = pd.read_excel('candidates.xlsx')
# Save data to a temporary file
df.to_excel('temp_candidates.xlsx', index=False)
# Load the workbook
wb = load_workbook('temp_candidates.xlsx')
ws = wb.active
# Create a bar chart
chart = BarChart()
data = Reference(ws, min_col=5, min_row=1, max_row=len(df) + 1, max_col=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(df) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = "Work Experience Distribution"
ws.add_chart(chart, "F1")
# Save the workbook
wb.save('candidates_with_chart.xlsx')15. Calculating Sum and Average
Use Case: Calculating sum and average of data.
Code Explanation: This example uses the pandas library to calculate summary statistics.
Example Code:
import pandas as pd
# Load existing data
df = pd.read_excel('candidates.xlsx')
# Calculate total and average work experience
total_experience = df['Work Experience (years)'].sum()
average_experience = df['Work Experience (years)'].mean()
# Print results
print(f"Total Work Experience: {total_experience} years")
print(f"Average Work Experience: {average_experience:.2f} years")16. Applying Conditional Formatting
Use Case: Applying formatting based on conditions.
Code Explanation: This example uses the openpyxl library to apply conditional formatting.
Example Code:
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Define a conditional formatting rule
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
rule = CellIsRule(operator='lessThan', formula=['3'], fill=red_fill)
# Apply conditional formatting to the range
ws.conditional_formatting.add('E2:E6', rule)
# Save the workbook
wb.save('candidates_conditional_format.xlsx')17. Unmerging Cells
Use Case: Unmerging previously merged cells in an Excel file.
Code Explanation: This example uses the openpyxl library to unmerge cells.
Example Code:
from openpyxl import load_workbook
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Unmerge cells from A1 to C1
ws.unmerge_cells('A1:C1')
# Save the workbook
wb.save('candidates_unmerged_cells.xlsx')18. Clearing Cell Content and Formatting
Use Case: Clearing the content or formatting of cells.
Code Explanation: This example uses the openpyxl library to clear cell contents or formatting.
Example Code:
from openpyxl import load_workbook
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Clear the content of cell A1
ws['A1'].value = None
# Clear formatting from cell A1
ws['A1'].font = None
ws['A1'].fill = None
ws['A1'].border = None
ws['A1'].alignment = None
ws['A1'].number_format = None
ws['A1'].protection = None
# Save the workbook
wb.save('candidates_cleared_content_and_style.xlsx')19. Auto-Adjusting Column Width
Use Case: Automatically adjusting the width of columns in an Excel file.
Code Explanation: This example uses the openpyxl library to auto-adjust column width.
Example Code:
from openpyxl import load_workbook
# Load an existing workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
# Auto-adjust the width of all columns
for col in ws.columns:
max_length = 0
column = col[0].column_letter # Get column letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2)
ws.column_dimensions[column].width = adjusted_width
# Save the workbook
wb.save('candidates_auto_adjusted_columns.xlsx')20. Sending Email Notifications
Use Case: Sending an email notification to a candidate.
Code Explanation: This example uses the smtplib library to send an email.
Example Code:
import smtplib
from email.mime.text import MIMEText
from email.header import Header
# Email configuration
sender = 'your_email@example.com'
receivers = ['candidate_email@example.com']
smtp_server = 'smtp.example.com'
smtp_port = 587
username = 'your_email@example.com'
password = 'your_password'
# Email content
message = MIMEText('Hello, your interview has been scheduled. Please attend on time.', 'plain', 'utf-8')
message['From'] = Header("Recruitment Team", 'utf-8')
message['To'] = Header("Candidate", 'utf-8')
message['Subject'] = Header('Interview Notification', 'utf-8')
try:
# Connect to SMTP server
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(username, password)
# Send the email
server.sendmail(sender, receivers, message.as_string())
print("Email sent successfully")
except Exception as e:
print(f"Failed to send email: {e}")
finally:
server.quit()Summary
These Python code examples cover a variety of tools that HR personnel may need during recruitment processes. Each script includes the necessary import statements and can be run directly. You can adjust these scripts based on your specific requirements. We hope these tools improve your efficiency!
