Data cleaning: Common issues need to consider
Data mean any information that proves valuable to an organization. Simple…. 😊😊
and, the source of data for an organization can be applications such as online portals 📈, machines like production equipment ⚙ / sensors, or manual collection methods such as surveys 📋.
As we know Data plays a key role in decision-making and strategic planning. Therefore, it must be complete, correct, accurate and validated. The principle of ‘garbage in, garbage out’ highlights that poor-quality data can leads to wrong decisions.
In simple terms, before data is presented to decision-makers, it must be thoroughly validated and free from errors to ensure its reliability and usefulness and this important task with data are done by Data Engineers.
Let’s discuss common issues which need to be checked before loading data to permanent storage for further use by different departments of organization for analysis.
Important Note: It’s important to note that undefined, wrong, redundant values can impact the quality of our analysis, so it’s best to handle them appropriately. Always assess the situation based on the context of data and discuss with domain experts as well.
1- Missing Data:
This issue pertains to the presence of missing or non-standard entries — such as empty cells, NULLs, None, NaT, NA, or NaNs — within a column or counter, which can complicate data processing and analysis. If the count of empty cells is significantly high, it may be necessary to recollect the data to ensure its integrity.
However, if the emptiness is manageable, we can consider delete the empty rows or filling in some substitute values.
Solutions:
Deletion: Remove rows/columns with excessive missing data .
Imputation: Fill in missing values using:
- constant / literal
- Mean/median/mode (for numerical data)
- Most frequent value (categorical)
- Interpolation (time series)
- Predictive modeling (e.g., regression or KNN)
import pandas as pd
import numpy as np
from sklearn.impute import KNNImputer
# Sample data frame
data = {
'age': [25, np.nan, 30, 22, np.nan],
'salary': [50000, 54000, np.nan, 58000, 62000],
'dept': ['Sales', 'Engineering', np.nan, 'Engineering', 'Sales'],
'date': pd.to_datetime(['2025-01-01', '2025-01-02', None, '2025-01-04', '2025-01-05']),
}
df = pd.DataFrame(data)
df_filled_constant = df.fillna({
'age': 0,
'salary': 0, # Fill numeric with 0
'dept': 'Unknown', # strings with 'Unknown'
'date': pd.Timestamp('2025-01-03') # datetime with a specific date
})
## or mean / median / mode
# Mean
df['age_mean_filled'] = df['age'].fillna(df['age'].mean())
# Median
df['salary_median_filled'] = df['salary'].fillna(df['salary'].median())
# Mode (for multiple modes, take first)
df['dept_mode_filled'] = df['dept'].fillna(df['dept'].mode()[0])
# Apply to all categorical columns
df['dept_frequent_filled'] = df['dept'].fillna(df['dept'].value_counts().idxmax())
# Interpolate numeric fields
df['salary_interpolated'] = df['salary'].interpolate(method='linear')
# Interpolate datetime (if time-based index)
df_time = df.set_index('date')
df_time['age_interpolated'] = df_time['age'].interpolate(method='time')
# Prepare numeric data
numeric_df = df[['age', 'salary']]
# Apply KNN imputation (k=2)
imputer = KNNImputer(n_neighbors=2)
numeric_filled = imputer.fit_transform(numeric_df)
# Add back to original df
df[['age_knn', 'salary_knn']] = pd.DataFrame(numeric_filled, columns=['age_knn', 'salary_knn'])
2- Duplicate Data:
We use one column or combination of columns to identify uniqueness of row/data and this help us to detect which records appear multiple times within a dataset.
While duplicates may not significantly impact average aggregation, however they can greatly distort counts, sums, and other aggregate calculations. Therefore, it’s essential to remove duplicate records to ensure the accuracy and reliability of your data analysis. Doing so helps prevent inconsistencies and ensures your insights are based on clean, trustworthy data. Remember, maintaining accurate and well-structured data is crucial for effective analysis and sound decision-making.
Solutions:
- Drop duplicate: drop_duplicates() in pandas or equivalent.
import pandas as pd
# Sample DataFrame
df = pd.DataFrame({
'name': ['Aakash', 'Boby', 'Tina', 'David'],
'age': [25, 30, 25, 40]
})
# Drop duplicate rows
df.drop_duplicates(inplace=True, keep='last')
print(df)3- Inconsistent Formatting:
One common challenge in cleaning and validation is inconsistent formatting.
This includes issues such as inconsistent capitalization, varying date formats, and differing measurement units (e.g., centimeters vs. inches). To ensure clarity and uniformity across datasets, it’s important to establish and enforce consistent formatting standards.
Dates should follow a single format, text should adhere to consistent capitalization rules, and numerical data should use uniform decimal or comma conventions.
Solution:
- Standardize case (e.g., .lower() or .title()).
- Convert dates to ISO format (YYYY-MM-DD).
- Normalize units (e.g., convert cm to inches).
import pandas as pd
# Sample data
data = {
'date': ['2025-01-01', '01/02/2025', 'March 3, 2025', '2025.04.04']
}
df = pd.DataFrame(data)
# Convert to datetime
df['date'] = pd.to_datetime(df['date'], errors='coerce')
# Format all dates to a consistent string format: YYYY-MM-DD
df['date'] = df['date'].dt.strftime('%Y-%m-%d')
print(df)4- Inconsistent Data Types:
Inconsistent data types create major obstacles in effective data management and analysis.
For example, numerical values might be improperly stored as text strings, or dates may appear as unstructured objects — leading to confusion, processing errors, and unreliable outcomes. Such discrepancies not only undermine data accuracy but also complicate integration and analysis across systems. Additionally, the presence of undefined values or mixed data types can obscure insights and delay decision-making. Ensuring consistency in data types across datasets is critical for maintaining data integrity, enhancing operational efficiency, and enabling more confident, data-driven decisions.
Solutions:
- Check and validate datatype of columns before loading to permanent storage.
import pandas as pd
# Sample DataFrame with inconsistent data types
df = pd.DataFrame({
'age': ['25', '30', 'unknown', 45, None, '38']
})
# Approach: Use `pd.to_numeric` with `errors='coerce'` before `astype()`
df['age'] = pd.to_numeric(df['age'], errors='coerce').astype('Int64')
print(df)5- Encoding Data Issues:
For data professionals, analysts, and developers alike, understanding and managing character encoding is critical. Whether we’re cleaning data for analysis, importing files across systems, or integrating content from multiple sources, ensuring consistent UTF-8 encoding is a best practice that safeguards data quality and interoperability.
Proactively detecting and resolving encoding issues during the data wrangling phase can prevent downstream errors in processing, visualization, or reporting. Techniques such as automated encoding detection, normalization, and validation should be part of every robust data pipeline.
Get Sanjeev Tyagi’s stories in your inbox
Join Medium for free to get updates from this writer.
Solutions:
- Ensure proper encoding when reading files (encoding=’utf-8').
import pandas as pd
# Example DataFrame with corrupted strings
df = pd.DataFrame({
'corrupted_text': ['François', 'café', 'naïve']
})
# Fix the encoding: encode as 'latin1' (bytes) then decode as 'utf-8'
df['fixed_text'] = df['corrupted_text'].apply(lambda x: x.encode('latin1').decode('utf-8'))
print(df)6- Data Leakage:
It occurs when information from outside the training dataset is inadvertently used to create the model, leading to overly optimistic performance estimates during testing.
This can result in a model that performs well in theory but fails in practical applications, as it has been exposed to data it should not have had access to during training.
This involves ensuring that the dataset used for training is strictly separated from any data that will be used for validation or testing. Additionally, careful attention should be paid to the steps taken during the data cleaning process, as certain actions — such as imputing missing values or scaling features — can inadvertently introduce information from the validation or test sets into the training dataset.
Solutions:
- Always split your dataset into training and test sets before performing any cleaning operations.
- Apply data imputation, scaling, or encoding only on the training data, and apply the learned parameters to the test set.
- In time-dependent datasets, preserve temporal order to avoid future data bleeding into the past.
- Validate with domain experts or SMEs.
7- Irrelevant Data:
Columns or rows that contain irrelevant, redundant, or non-informative data and therefore do not add value to the analytical objectives should be identified and removed to improve data quality and model performance.
This process begins with applying domain expertise to interpret the context and purpose of each feature, enabling the identification of data points that do not serve the analytical goals.
Correlation analysis plays a complementary role by revealing redundant or weakly related variables, helping to prioritize features that drive the most value.
Incorporating advanced feature selection techniques — such as mutual information, recursive feature elimination, or model-based selection — can further streamline the dataset, enhancing both performance and interpretability.
It’s important to recognize that the definition of “irrelevant” data is inherently domain-specific and goal-dependent, requiring a thoughtful and iterative approach. By rigorously addressing irrelevant data during cleaning, you lay a strong foundation for efficient, focused, and reliable analysis.
8- Noise and Redundancy:
To ensure high-quality and accurate data analysis, it is essential to eliminate irrelevant variations and redundancies that can obscure meaningful insights.
During the data cleaning process, applying smoothing techniques — such as moving averages or exponential smoothing — can effectively reduce random noise and emphasize underlying trends. This not only improves data interpretability but also stabilizes volatile data points. Furthermore, identifying and removing highly correlated features is a crucial step in minimizing redundancy. Such features can distort modeling outcomes, inflate dimensionality, and complicate downstream analysis.
By systematically addressing undefined fluctuations and multicollinearity, you can enhance the clarity, consistency, and analytical value of your dataset — laying a solid foundation for reliable and insightful decision-making.
import pandas as pd
# Sample time series data
data = {'date': pd.date_range(start='2025-01-01', periods=15, freq='D'),
'value': [10, 12, 11, 15, 14, 13, 16, 18, 13, 20, 13, 15, 14, 13, 16 ]}
df = pd.DataFrame(data)
# Set 'date' as index
df.set_index('date', inplace=True)
# Calculate 3-day moving average
df['moving_avg_3'] = df['value'].rolling(window=3).mean()
# Calculate exponential weighted moving average with a span of 3
df['ewm_span_3'] = df['value'].ewm(span=3, adjust=False).mean()
print(df)9- Outliers:
Outliers are data points that markedly diverge from the overall pattern of a dataset and can be a signal of data quality issues. Identifying and appropriately managing these anomalies is a essential step in ensuring clean, reliable data.
Left unaddressed, outliers can distort statistical summaries, degrade the accuracy of machine learning models, and ultimately lead to erroneous conclusions.
Effective outlier treatment begins with understanding their origins — whether they result from data entry errors, measurement faults, or legitimate but rare occurrences. Depending on this context, the appropriate course of action may involve correcting, transforming, or removing outliers.
A thoughtful approach is essential, as the decision to keep or discard outliers can significantly alter analytical outcomes. Therefore, outlier handling should align with the analytical goals, the nature of the dataset, and the broader context of the problem being addressed.
How to Identify Outliers:
- Use statistical methods (e.g., Z-score, IQR).
- Visualize with boxplots or scatterplots (most commonly used to identify outliers).
How to handle Outliers:
- remove, cap (winsorize), or investigate.
10- Typographical Errors:
Typographical errors, particularly misspellings and inconsistent labeling- can significantly undermine the integrity of a dataset. For instance, variations such as “USA” versus “U.S.A” can lead to confusion and inaccuracies in data analysis. To ensure clarity and consistency, it is crucial to standardize these entries.
This process of data cleaning not only enhances the quality of our datasets but also improves the overall reliability of the information we work with. By addressing these typographical discrepancies, we can eliminate undefined variations that might skew our results or lead to misinterpretations.
11- Data Acquisition and Completeness Verification:
With my experience for data engineering, process need to collect all relevant data and validate it to ensure completeness. Use techniques such as file counts, row counts, end-of-file markers, checksums, log analysis, or any other indicators provided by the source to verify that the data is complete and not partial.
Conclusion:
Effectively overcoming common data cleaning challenges greatly enhances the accuracy, consistency, and reliability of your data. This creates a solid foundation for credible analysis and empowers more strategic, data-driven decision-making. By adopting these best practices, you ensure your data remains well-defined, structured, and insightful — enabling confident and impactful outcomes.
