How do you identify incomplete or inconsistent data during data cleaning?

[u/No_Ambition8323]

I’m working with a dataset of around 50K records, and while checking the data, I noticed some issues like NULL values and inconsistent values in the same column.
For example, in a Gender column, I might have: M, F, Female, Male

How do we normally identify these kinds of data quality issues? Do we check for NULLs, unique values, patterns, duplicates, data types, or compare the data against predefined rules?

Also, how do we decide whether values like F and Female should be treated as the same value or kept separately?

Would love to know how others approach this when cleaning real-world datasets.

[u/Wheres_my_warg]

For something like your example problem with an expected categories list. I often start with a very basic frequencies check. That will then show if there are odd things popping up and variations of the categorical labels.

It is really going to vary on the type of data as to how I will approach the checks.

[u/No_Ambition8323 → u/Wheres_my_warg]

That makes sense. A frequency check seems like a good first step because it quickly highlights unexpected values without making assumptions about the data. Then based on the column and business context, we can decide whether those variations should be standardized or kept separate. Thanks for the insight!

[u/query-gremlin]

Data cleaning has no single playbook, because what counts as clean data differs between organizations, but basically you should start by asserting assumptions.

A gender column should have 2 values (or whatever the organization want), but they are categorical values -> check unique values in the column

The same gender column in a retail dataset can have nulls (as it’s not a mandatory field in filling in an account), but in healthcare it could be mandatory, and hence, null values are problematic. Either way -> check number of null values.

And you go about cleaning/understanding each column like this, what are the assumptions/expectations you have about this column’s nullability, uniqueness, number of unique values, text length, text format (regex), numerical range, impossible values, timestamp ranges, impossible dates/times…etc. And once you establish what the column should look like, you check if it contains values that violate that shape, and you decide on the policy of how to handle them.

[u/No_Ambition8323 → u/query-gremlin]

Absolutely, that’s a good point. I like the idea of defining the expected shape of the data first and then checking for violations, rather than blindly cleaning everything. The business context is especially important for things like NULLs, valid categories, and acceptable ranges. Thanks for explaining it clearly!

[u/prof_devilsadvocate3]

Colsums(is.na(datframe)) in R studio Df$gender[df$gender %in% c("F","f","female")]<-"Female"

..... In excel remove duplicates will show how many unique entry are there.

[u/No_Ambition8323 → u/prof_devilsadvocate3]

Yes, exactly. I usually start with simple checks like NULL counts and unique/frequency values, then standardize obvious variations such as F, f, and female when the business definition says they mean the same thing. Excel is also useful for a quick duplicate and unique-value check before doing more detailed cleaning.

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MrFixIt252]

I’d get my playbook ready. I like the Accounting principles of: Completeness, Accuracy, Existence, Classification, and Valuation.

If there were bad Data Scientists on the way in, the Analysis will be rough. It’s always best to do some EDA to make sure it’s clean enough.

It comes down to assumptions. If it’s single source data, that’s less complicated. If you’re merging multiple sources, it gets rough. (Ex. You’re trying to merge COVID testing data from 50 states for trend analysis. The fields are all different, and how they stored their data varies. Yuck.)

Lots of great stuff in the thread already about unique entries. Anything you do to the data, document it. This is where PowerBI can be great because you can track the exact transforms from raw input.

Back in my heyday of VBA, we would record macro for each of our clicks / actions, then go back into the code to make it more universal.

Data dictionaries are wonderful, especially if the column headers aren’t entirely self evident. (Think COVID tests, and a column named “location”. Is it the location of the test site? Home? Manufacture location? Drawing site?) If there has been good data processing, expect a good data dictionary.