Tripura University Students: Handling Missing and Messy Data in Your First Analytics Project

Handle missing and messy data by deciding, column by column, whether a gap is ignorable, fixable from other information, fillable with a defensible estimate, or a reason to drop the row or column, and then writing down why. Your mentor will care less about which method you picked than about whether you can justify it. This guide gives you a decision tree, a cleaning checklist with before-and-after examples, and a note template for explaining each choice.

Why messy data decides the quality of a first analytics project

Real datasets arrive with blank cells, “NA” typed three different ways, dates in mixed formats, city names spelled four ways and numbers stored as text. Beginners often rush this stage because modelling and charts feel more impressive. But every cleaning choice changes the answer. Filling blanks with the average can shrink variation. Dropping every incomplete row can quietly remove one whole group of people. Merging “Agartala” and “agartala ” is harmless; merging “0” and “missing” is not.

In a short project, such as a 14-day internship, you will not clean perfectly. You can, however, clean transparently. A reviewer can accept a rough choice that is documented, and will distrust a polished result with no explanation.

Three kinds of missing data and what each means for your choices

Before touching a cell, ask why it is empty. Statisticians use three labels; you only need the practical meaning.

• Missing completely at random: the gap has no pattern, such as a survey form smudged at random. Dropping or filling usually causes little bias.
• Missing at random (given other columns): the gap depends on something you can see. For example, older respondents skip an “app usage” question more often. Filling within groups (by age band) is more defensible than a global average.
• Missing not at random: the gap depends on the missing value itself. High earners leave income blank; failed sensors stop recording at extreme temperatures. Simple filling is risky here. Report the limitation and consider a “missing” category instead.

You usually cannot prove which type you have. You can look for clues: compare the rows with a blank against the rows without on other columns, and see whether the blanks cluster by date, region, source file or category.

Decision tree: what to do with a missing value

Work through this for each column that has gaps. Write the outcome in your notes.

1. Is the “blank” really a blank? Check for placeholders such as 0, -1, 999, “N/A”, “-“, “unknown” or a space. If a placeholder is hiding there, convert it to a true missing value first. If a blank actually means “none” (e.g. no coupon used), recode it to 0 or “None” and stop.
2. Can it be recovered from the data itself or its source? Examples: a missing state derivable from a PIN code, a missing total from price times quantity, a missing value present in another file. If yes, recover it and note the rule.
3. How much is missing in this column?
   • Under about 5%: the choice rarely matters much; simple methods are usually acceptable.
   • About 5% to 30%: choose carefully and test whether results change.
   • Above about 30-40%: question whether the column is usable at all. These are rules of thumb, not laws.
4. Is the column essential to your question? If it is your target or key measure, dropping rows with a blank there is often right, because inventing the thing you are trying to measure is a bad idea. If it is a minor feature, you have more freedom.
5. Is the gap patterned? If blanks cluster in one group, do not drop silently. Fill within groups, add a “missing” category, or flag the limitation.
6. Choose the method by column type:
   • Numeric, roughly symmetric: median (safer than mean when outliers exist).
   • Numeric, skewed (income, prices): median or group median.
   • Categorical: mode, or a separate “Unknown” category (usually better when the gap is meaningful).
   • Time series: carry forward or interpolate, only for short gaps.
7. Add a flag column (such as age_was_missing) when you fill, so the filled values stay traceable.
8. Run a sensitivity check: compute your key result with the rows dropped and with the values filled. If the conclusion changes, say so.

Cleaning-steps checklist for a first project

Use this order. Cleaning in a consistent sequence keeps results reproducible, and always keep an untouched copy of the raw file.

Before-and-after examples for the most common messes

All the data below is invented for illustration, not taken from any real dataset.

Placeholders hiding as numbers

If you had averaged the column with 999 still in it, the “average age” would be absurdly high. Always convert placeholders before computing anything.

Inconsistent text categories

Mixed date formats

Ambiguous dates like 05/11 are a classic trap. Ask whoever supplied the data rather than guessing.

Worked illustration: a campus survey with gaps in a key column

This scenario is illustrative; no code was run and the numbers are made up.

Imagine a survey of 200 students on weekly study hours and exam stress. Profiling shows:

• study_hours: 12 blanks (6%)
• stress_score: 10 blanks (5%)
• family_income: 74 blanks (37%)
• 14 duplicate submissions (same name, same timestamp)

Decisions using the tree:

1. Remove the 14 duplicates first, leaving 186 rows. Record the rule: same name and timestamp.
2. stress_score is the outcome being studied, so drop the rows where it is blank rather than invent it.
3. study_hours is a small gap and roughly skewed, so fill with the median and add study_hours_was_missing.
4. family_income is missing for over a third, and people with higher or lower incomes may skip it deliberately (possibly missing not at random). Do not fill. Either exclude the column from the main analysis or convert it to a band with an “Not stated” category, and state the limitation.
5. Sensitivity check: compare average stress with the study_hours rows dropped and with them filled. If the gap is tiny, say the choice did not matter. If large, report both.

Notice that the final dataset is smaller, the income variable is handled honestly, and each action can be justified in one sentence.

Starter code pattern for flagging, then filling (illustrative)

# Illustrative pattern in pandas - adapt to your own data
df["study_hours_was_missing"] = df["study_hours"].isna()
median_val = df["study_hours"].median()
df["study_hours"] = df["study_hours"].fillna(median_val)

The flag is created before the fill, otherwise you lose the record of which values were estimated. For group-wise filling, compute the median within each group instead of across the whole column.

Note template: explaining each cleaning choice to a mentor

Keep one short entry per decision, in a text file or a sheet tab named “cleaning log”. Copy this structure:

• Column / issue: what you found (e.g. “study_hours, 12 of 186 blank, 6%”).
• Evidence: how you checked the pattern (e.g. “blanks spread across all branches; no clustering”).
• Options considered: drop rows, median fill, group median.
• Decision and reason: “Median fill because the column is skewed and the gap is small.”
• Effect on results: “Average changed from X to Y” or “No visible change in the key chart.”
• Risk or limitation: “If students with fewer hours skipped the question, the average is slightly overstated.”
• Question for mentor (optional): something you want a second opinion on.

A filled-in example: “family_income: 74 of 186 blank (40%). Blanks appear in every branch, but respondents may avoid the question for privacy. Considered median fill and dropping rows; both distort the sample. Decision: excluded from the main model, kept as banded ‘Not stated’ for descriptive tables. Limitation: income effects on stress cannot be concluded.”

Planning the cleaning work inside a 14-day internship

If you are doing the free two-week online internship from CareerSwami, cleaning should not eat the whole fortnight. A workable split for a data track:

Offer letter and completion certificate are provided free on successful completion; what makes the project useful to you is the reasoning you can show.

Tripura University students: fitting a data-cleaning project into UG internship rules

Tripura University is headed in its own regulations as “A Central University” at Suryamaninagar, and the same document sets NEP-2020 undergraduate rules for its affiliated colleges from the 2023-24 session. Those regulations treat project work, dissertation, internship and entrepreneurship as a course category and describe internship as an integral part of the curriculum, but the text gives no duration, credit count or required documents. So a self-run analytics project should not be assumed to carry credit.

The same document says internal assessment component C2 may be based on internship, industrial practicum or project work, with continuous assessment (C1+C2) at 40% and the semester-end exam at 60%. A well-documented cleaning log is exactly the kind of evidence a department could look at if it decides to accept your work for C2, but that decision belongs to your department. The regulations also propose using summer vacation for internships and field studies, which suggests where a short project may fit in the calendar. Note that the SWAYAM provision (up to 40% of a semester’s credits with college permission) concerns online courses, not online internships. No statement on online internships was verified, so check the current version of the regulations and your department notices.

Verification steps for Tripura University students before you rely on this project

1. Download the current UG regulations from the university site and read the sections on internship, project work and C2 assessment.
2. Email your department coordinator asking in writing whether an online, non-credit internship project can be used for C2 or only as extra-curricular evidence.
3. Ask what documents are required (certificate, report, supervisor letter) and in what format.
4. Ask which semester or vacation window your department expects internships to fall in.
5. Check the university’s yearly Academic Calendar for exam weeks so your 14 days do not collide with them.
6. Keep the reply email with your project files so you can show what was approved.

Quality checks to run before you call the data clean

• Row count before and after matches your log (every dropped row accounted for).
• No column has leftover placeholders such as 999 or “NA” strings.
• Categorical columns have no near-duplicate labels.
• Date columns sort chronologically, not alphabetically.
• Filled values have flag columns.
• Key results are tested with and without your fills.
• Someone else can rerun your steps from the raw file and get the same output.

Cleaning habits that quietly damage a first project

• Filling before looking. Applying the mean to every numeric column without checking patterns or skew.
• Dropping all incomplete rows. This can shrink the sample sharply and remove a specific group.
• Overwriting the raw file. Without it you cannot undo a mistake.
• Treating outliers as errors by default. A very high value can be real; verify first.
• Cleaning in the test or evaluation stage differently. If you build a model, calculate fill values from training data only, then apply them to test data.
• Leaving no trail. A decision with no note becomes impossible to defend a week later.

Frequently Asked Questions

Should I always fill missing values instead of dropping rows?

No. Drop rows when the blank is in the variable you are trying to measure, or when very few rows are affected and there is no pattern. Fill when the column is useful, the gap is moderate and you can justify the method. Either way, record the choice.

Is the mean or the median better for filling a numeric column?

The median is usually safer for skewed data or data with outliers, such as income or prices. The mean can be acceptable for roughly symmetric data. Comparing results under both is a quick sensitivity check.

What percentage of missing data is too much?

There is no universal cutoff. Many analysts become cautious above 5% and seriously question a column above 30-40%, but importance of the column and the pattern of the gaps matter more than the number alone.

How do I tell whether 0 means zero or missing?

Check the data dictionary or ask the data owner. Look at the distribution: a spike of zeros in a column where zero is implausible (age, height) suggests a placeholder. If you cannot confirm, state your assumption in the cleaning log.

Why add a flag column when I fill values?

It keeps estimated values distinguishable from observed ones. You can later test whether filled rows behave differently, and a reviewer can see exactly what you changed.

How detailed should my cleaning log be for a mentor?

One short entry per decision: the issue, evidence, options, choice, effect and limitation. If a mentor can read it in a few minutes and understand what you did and why, it is detailed enough.

Can a cleaning project like this count for internal assessment at Tripura University?

The UG regulations say component C2 may be based on internship or project work, but they do not specify duration, credits or documents for it. Whether your project qualifies is for your department to decide, so ask in writing and keep the reply.

Do I need Python, or can I clean data in Excel or Google Sheets?

Both work for a first project. Spreadsheets are fine for small files if you keep a raw copy and log your steps. Python is easier to rerun and audit as data grows. Pick the one you can document clearly.

Related Data Analytics guides

• Shobhit Institute Students: Automating Repetitive Reports with Excel and Python in an Internship
• Jaipur National University Students: Writing a One-Page Data Analytics Project Proposal
• Bhattadev University Students: Collecting and Cleaning Your Own Data for an Analytics Internship
• All Data Analytics internship guides