Jaypee University of Engineering & Technology: Using Python Basics for HR Data Cleaning in an Internship

Python basics are enough to clean a messy HR spreadsheet: load the file, standardise names and dates, remove duplicates and write out a tidy copy along with a log of what changed. This guide walks through a notebook-style exercise on an invented employee list, then supplies a reusable script outline and a checklist of the errors HR data most often contains. The code is illustrative and is not presented as tested output.

Why data cleaning is a core HR analytics skill

HR data comes from many places: application forms, attendance systems, payroll files and manual spreadsheets. The same employee may appear twice with slightly different spellings, dates may be typed in three formats, and a required field may be blank. Any headcount, attrition rate or average tenure computed from such data inherits those errors. Cleaning is therefore the first stage of analysis, not a side chore.

For an intern, cleaning is also a safe entry point. The task is well-defined, the output is checkable, and the discipline it builds, documenting every change, is what makes analysis trustworthy.

The fake dataset used in this exercise

Every row below is invented. Do not use real employee data in practice work, and when you work with real data at an employer, follow their rules on access and storage.

Even six rows hide many problems. Spot them before reading on: extra spaces, inconsistent capitalisation, a duplicated employee, three date styles, a blank date, an impossible date, a malformed email and inconsistent department labels.

Step one: load the data and look before changing anything

Start by reading the file and inspecting it. Never edit the original; keep the raw file untouched so you can always return to it.

import pandas as pd

raw = pd.read_csv("employees_raw.csv", dtype=str)
df = raw.copy()

print(df.shape)
print(df.head())
print(df.isna().sum())
print(df.duplicated(subset=["emp_id"]).sum())

Reading every column as text with dtype=str prevents Python from silently converting IDs or dates in surprising ways. The prints give a first snapshot: how many rows, which columns have blanks and how many repeated IDs exist.

Step two: clean names, labels and emails

df["full_name"] = (
    df["full_name"]
      .str.strip()
      .str.replace(r"\s+", " ", regex=True)
      .str.title()
)

df["department"] = df["department"].str.strip().str.title()
df["department"] = df["department"].replace({"Hr": "HR"})

df["email"] = df["email"].str.strip().str.lower()

Three ideas matter here. Trimming and collapsing spaces fixes invisible differences that break matching. Title case makes names consistent, though you should know its limits: it can mishandle names such as “McDonald” or “D’Souza”, so review the output and keep a manual-exceptions list. Mapping department variants to one agreed label needs the HR team’s official list; do not invent categories.

Step three: parse dates safely instead of guessing

Dates are the most error-prone column. A value like 05/06/2022 means 5 June in India but 6 May in some other regions, so the format must be agreed with whoever supplied the file.

def parse_date(value):
    if pd.isna(value) or str(value).strip() == "":
        return pd.NaT
    value = str(value).strip()
    for fmt in ("%Y-%m-%d", "%d/%m/%Y", "%d-%b-%Y"):
        try:
            return pd.to_datetime(value, format=fmt)
        except ValueError:
            continue
    return pd.NaT

df["join_date_clean"] = df["join_date"].apply(parse_date)
df["date_problem"] = df["join_date_clean"].isna()

This function tries each known format in turn and returns a missing value if none works. The impossible date 31/02/2020 fails every format, so it is flagged rather than silently converted. The blank join date for Rohan Das is flagged too. A flagged row is a question for the data owner, not a gap to fill with a guess.

Step four: handle duplicates deliberately

dupe_mask = df.duplicated(subset=["emp_id"], keep=False)
dupes = df[dupe_mask].sort_values("emp_id")
print(dupes)

df = df.drop_duplicates(subset=["emp_id"], keep="first")

Print duplicates before dropping them. In the sample, the two rows for employee 101 match after cleaning, so dropping one is safe. In real data two rows with the same ID but different details might show a transfer, a rehire or a data entry error, which need a decision from HR, not a script. Also remember that duplicates can hide behind different IDs, so check name plus email combinations as a second pass.

Step five: validate emails and required fields

email_ok = df["email"].str.match(r"^[^@\s]+@[^@\s]+\.[^@\s]+$", na=False)
df["email_problem"] = ~email_ok

required = ["emp_id", "full_name", "department"]
df["missing_required"] = df[required].isna().any(axis=1)

The pattern is a light sanity check, not proof that a mailbox exists. In the sample, rohan.das@example fails because it lacks a domain extension. Flag it and ask for a corrected address.

Step six: export a clean file and a change log

problems = df[df[["date_problem", "email_problem", "missing_required"]].any(axis=1)]

df.to_csv("employees_clean.csv", index=False)
problems.to_csv("employees_needs_review.csv", index=False)

with open("cleaning_log.txt", "w") as f:
    f.write(f"Rows in raw file: {len(raw)}\n")
    f.write(f"Rows after de-duplication: {len(df)}\n")
    f.write(f"Rows flagged for review: {len(problems)}\n")

A separate review file is the professional habit that separates cleaning from guessing. The clean file feeds analysis; the review file goes back to the data owner with specific questions.

Reusable script outline for any HR data cleaning task

1. Configuration block: input path, output paths, expected columns, agreed date formats, department list.
2. Load: read everything as text, keep a copy of the raw frame.
3. Structure check: confirm expected columns exist, report extras and missing ones.
4. Standardise text: trim, collapse spaces, set case, apply the department map.
5. Parse dates and numbers: use explicit formats; flag failures.
6. Validate: emails, required fields, plausible ranges such as join dates not in the future.
7. De-duplicate: print first, then drop only under an agreed rule.
8. Export: clean file, review file, short log.
9. Summarise: counts of each problem type in plain words for your supervisor.

Wrap steps four to seven in small functions so you can test each on a few rows, and keep configuration at the top so a colleague can adapt it without reading the whole script.

Checklist of common HR data errors and the fix for each

Sanity checks after cleaning: simple questions the data must still answer

Cleaning can introduce errors as easily as it removes them. After running the script, test the output with simple questions whose answers you can verify by eye on a small sample.

Spot-check by choosing five rows at random from the raw file and following each one through to the clean file, confirming that every change was intended. Where you can, ask a colleague to repeat the check on a different five rows. This habit costs ten minutes and prevents most embarrassing corrections later.

Ethical handling of employee data while practising

• Work only on data you are authorised to use, and store it only in approved locations.
• Do not email raw employee files to personal accounts or upload them to online tools.
• Remove or mask direct identifiers such as names, phone numbers and email addresses before sharing samples with anyone.
• Do not include sensitive fields such as pay, health or disciplinary details in practice files at all.
• Delete working copies when the task is complete, according to the retention instructions you were given.

Reporting what you did so the supervisor can trust the numbers

Write three or four sentences in your summary: how many rows came in, how many changes were automatic, how many rows are waiting for a decision and which rule you used for duplicates. Attach the review file. This turns cleaning from a black box into an auditable process. Never describe data as “clean” if flagged rows remain; call it “cleaned with open items”.

If you want a guided place to practise this kind of task with a mentor-style structure, the free two-week online internship from CareerSwami is available, and it provides a free offer letter and a free completion certificate after successful completion.

JUET Guna students: how the Training & Placement page connects to HR data skills

Jaypee University of Engineering & Technology is located in Guna, Madhya Pradesh. Its Training and Placement Cell page states that all B.Tech students must complete a summer internship after the sixth semester, in line with government guidelines. For a computer science student, an HR data task like this one is a legitimate way to show Python skills in a business setting, but the page does not say whether an online or HR-themed internship satisfies that requirement, so that is a question for the university.

The same page describes a committee led by a Senior Training & Placement Officer, a placement season that typically starts in July, and in-house training covering aptitude, communication, emotional intelligence and ethical and professional conduct. It also notes computer labs capable of hosting online exams for 400 systems at a time. Ethical and professional conduct is especially relevant to this topic, because HR datasets contain personal information that must be handled carefully.

Because the placement season starts in July, work done after the sixth semester will be fresh when recruiters visit; having a small, well-documented cleaning notebook to describe in an interview is a practical advantage. Treat the page as a snapshot and check it for updates.

Five steps for JUET students to confirm how an HR data internship fits the summer-internship requirement

1. Email or visit the Training & Placement Cell and ask for the written internship guidelines applicable to your batch and branch.
2. Ask whether the summer internship after sixth semester must be offline, may be online, and whether it must be with a registered organisation.
3. Ask your department which documents are required, such as an offer letter, a report or a completion certificate, and which format and signatures they accept.
4. Ask whether an HR-analytics project is acceptable for a computer science or related branch, or whether the work must match a technical domain.
5. Get the answers in writing and store them with your internship papers.

Frequently Asked Questions

Do I need to know advanced Python to clean HR data?

No. Loading a file, string methods, date parsing, duplicate detection and exporting are enough for most cleaning tasks, and they use a small set of pandas functions.

Why read all columns as text at the start?

It stops Python from converting IDs, phone numbers or ambiguous dates automatically. You then convert each column deliberately, with rules you can explain.

How should I treat the date 05/06/2022?

Do not guess. Ask the data owner whether the file uses day-month-year or month-day-year, set that format explicitly in your script and document the assumption.

Is it safe to drop duplicate employee rows automatically?

Only for exact matches after cleaning. Rows that share an ID but differ in other fields should go to a review file for an HR decision.

Can I practise with real employee data from my college or internship?

Use invented or anonymised data for practice and portfolios. Work with real data only under your employer’s access rules and keep it inside approved systems.

What should a cleaning log contain?

Input and output row counts, the rules applied, the number of rows flagged for each problem type and any assumptions, such as the date format used.

Can a short online internship project on HR data cleaning be used as evidence of Python skills?

It can, if you keep the notebook, the review file and a short summary of your method, using only invented data in anything you share publicly. Check with your department how internships are recorded for any formal requirement.

Related Human Resources guides

• Maharishi Dayanand Saraswati University: Learning Employee Records and Filing Systems in an HR Internship
• The Neotia University: Reference Checks and Background Verification Tasks for HR Interns
• Jagran Lakecity University: Media and Communications Hiring, an HR Internship With Portfolio Screening
• All Human Resources internship guides