保留正文与补采附件。源页面当前未重新核验；具体限制见交付清单。

Revisit consent button We value your privacy We use cookies to enhance your browsing experience, serve personalised ads or content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies. Customise Reject All Accept All Customise Consent Preferences We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below. The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... Show more NecessaryAlways Active Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

Functional Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

Analytics Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

Performance Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

Advertisement Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

Uncategorised Other uncategorised cookies are those that are being analysed and have not been classified into a category as yet. No cookies to display. Reject All Save My Preferences Accept All Toggle SearchToggle Mobile Navigation Publish AI, ML & data-science insights to a global community of data professionals. Sign inSubmit an Article LatestEditor's PicksNewsletterShipAI

Write For TDS LinkedInXToggle Search Data Science

I Cleaned a Messy CSV File Using Pandas . Here’s the Exact Process I Follow Every Time.

Stop guessing at data cleaning. Use this repeatable 5-step Python workflow to diagnose and fix the most common data flaws. Ibrahim Salami November 26, 202515 min read Image by Author Often, data isn't perfect. You’re going to encounter a lot of data inconsistencies. Nulls, negative values, string inconsistencies, etc. If these aren’t handled early in your data analysis workflow, querying and analysing your data would be a pain later on. Now, I’ve done data cleaning before using SQL and Excel, not really with Python. So, to learn about Pandas (one of Python’s data analysis libraries), I’ll be dabbling in some data cleaning. In this article, I’ll be sharing with you a repeatable, beginner-friendly data cleaning workflow. By the end of this article, you should be pretty confident in using Python for data cleaning and analysis.

The Dataset we’ll be working with

I’ll be working with a synthetic, messy HR dataset containing typical real-world errors (inconsistent dates, mixed data types, compound columns). This dataset is from Kaggle, and it is designed for practising data cleaning, transformation, exploratory analysis, and preprocessing for data visualisation and machine learning. Learn this step by step with the interactive AI and Data Scientist roadmap. The dataset contains over 1,000 rows and 13 columns, including employee information such as names, department-region combinations, contact details, status, salaries, and performance scores. It includes examples of:

It contains columns like:

You can access the dataset here and play around with it The dataset is fully synthetic. It does not contain any real individuals’ data and is safe to use for public, academic, or commercial projects. This dataset is in the public domain under the CC0 1.0 Universal license. You are free to use, modify, and distribute it without restriction.

Overview of the Cleaning Workflow

The data cleaning workflow I’ll be working with consists of 5 simple stages.

  1. Load
  2. Inspect
  3. Clean
  4. Review
  5. Export

Let’s dive deeper into each of these stages.

Step 1 — Load the CSV (And Handle the First Hidden Issues)

There are some things to keep in mind before loading your dataset. However, this is an optional step, and we probably wouldn’t encounter most of these issues in our dataset. But it doesn’t hurt to know these things. Here are some key things to consider while loading.

Encoding issues (utf-8, latin-1)

Encoding defines how characters are stored as bytes in the file. Python and Pandas usually default to UTF-8, which handles most modern text and special characters globally. However, if the file was created in an older system or a non-English environment, it might use a different encoding, most commonly Latin-1 So if you try to read a Latin-1 file with UTF-8, Pandas will encounter bytes it doesn’t recognise as valid UTF-8 sequences. You’ll typically see a UnicodeDecodeError when you try to read a CSV with encoding issues. If perhaps the default load fails, you could try to specify a different encoding: python

# First attempt (the default)
try:
df = pd.read_csv(‘messy_data.csv’)
except UnicodeDecodeError:
# Second attempt with a common alternative
df = pd.read_csv(‘messy_data.csv’, encoding=’latin-1')

Wrong delimiters

CSV stands for “Comma Separated Values,” but in reality, many files use other characters as separators, like semicolons (common in Europe), tabs, or even pipes (|). Pandas typically defaults to the comma (,). So, if your file uses a semicolon (;) but you load it with the default comma delimiter, Pandas will treat the entire row as a single column. The result would be a DataFrame with a single column containing entire lines of data, making it impossible to work with. The fix is pretty simple. You can try checking the raw file (opening it in a text editor like VS Code or Notepad++ is best) to see what character separates the values. Then, pass that character to the sep argument like so python

# If the file uses semicolons
df = pd.read_csv('messy_data.csv', sep=';')

# If the file uses tabs (TSV)
df = pd.read_csv('messy_data.csv', sep='\t')

Columns that import incorrectly

Sometimes, Pandas guesses the data type for a column based on the first few rows, but later rows contain unexpected data (e.g., text mixed into a column that started with numbers). For instance, Pandas may correctly identify 0.1, 0.2, 0.3 as floats, but if row 100 contains the value N/A, Pandas might force the entire column into an object (string) type to accommodate the mixed values. This sucks because you lose the ability to perform fast, vectorised numeric operations on that column until you clean up the bad values. To fix this, I use the dtype argument to tell Pandas what data type a column should be explicitly. This prevents silent type casting. python

df = pd.read_csv(‘messy_data.csv’, dtype={‘price’: float, ‘quantity’: ‘Int64’})

Reading the first few rows

You could save time by checking the first few rows directly during the loading process using the nrows parameter. This is great, especially when you’re working with large datasets, as it allows you to test encoding and delimiters without loading the entire 10 GB file. python

# Load only the first 50 rows to confirm encoding and delimiter
temp_df = pd.read_csv('large_messy_data.csv', nrows=50)
print(temp_df.head())

Once you’ve confirmed the arguments are correct, you can load the full file. Let’s load the Employee dataset. I don’t expect to see any issues here. python

import pandas as pd
df = pd.read_csv(‘Messy_Employee_dataset.csv’)
df

Output : markup

1020 rows × 12 columns

Now we can move on to Step 2 : Inspection

Step 2 — Inspect the Dataset

I treat this phase like a forensic audit. I’m looking for evidence of chaos hidden beneath the surface. If I rush this step, I guarantee myself a world of pain and analytical errors down the line. I always run these four crucial checks before writing any transformation code. The following methods give me the full health report on my 1,020 employee records:

1. df.head() and df.tail(): Understanding the Boundaries

I always start with a visual check. I use df.head() and df.tail() to see the first and last five rows. This is my quick sanity check to see if all columns look aligned and if the data visually makes sense. My Finding: When I ran df.head(), I noticed my Employee ID was sitting in a column, and the DataFrame was using the default numerical index (0, 1, 2, ...) instead. While I know I could set Employee ID as the index, for now, I’ll leave it. The bigger immediate visual risk I’m looking for here is data misaligned in the wrong column or obvious leading/trailing spaces on names that will cause trouble later.

2. df.info(): Spotting Datatype Problems and Missingness

This is the most critical method. It tells me the column names, the data types (Dtype), and the exact number of non-null values. My Findings on 1,020 Rows:

3. Data Integrity Check: Duplicates and Unique Counts

After checking dtypes, I need to know if I have duplicate records and how consistent my categorical data is.

4. df.describe(): Catching Odd and Impossible Values

I use df.describe() to get a statistical summary of all my numerical columns. This is the place where truly impossible values—the "red flags"—show up instantly. I mostly focus on the min and max rows. My Findings: I immediately noticed a problem in what I expected to be the Phone Number column, which Pandas mistakenly converted to a numerical type. markup

Mean-4.942253 * 10⁹Min-9.994973 * 10⁹Max-3.896086 * 10⁶25%-7.341992e * 10⁹50%4.943997 * 10⁹75%-2.520391e * 10⁹

It turns out all the phone number values were massive negative numbers! This confirms two things: Pandas incorrectly inferred this column as a number, even though phone numbers are strings. There must be characters in the text that Pandas cannot interpret (for example, parentheses, dashes, or country codes). I need to convert this to an object type and clean it up completely.

5. df.isnull().sum(): Quantifying Missing Data

While df.info() gives me non-null counts, df.isnull().sum() gives me the total count of nulls, which is a cleaner way to quantify my next steps. My Findings:

This inspection process is my safety net. If I had missed the negative phone numbers , any analytical step that involved numerical data would have failed or, worse, produced skewed results without warning. By identifying the need to treat Phone Number as a string and the significant missing values in Age now , I have a concrete cleaning list. This prevents runtime errors and, critically, ensures that my final analysis is based on plausible, non-corrupted data.

Step 3 — Standardise Column Names, Correct Dtypes, and Handle Missing Values

With my list of flaws in hand (missing Age, missing Salary, the terrible negative Phone Numbers, and the messy categorical data), I move into the heavy lifting. I treat this step in three sub-phases: ensuring consistency, fixing corruption, and filling gaps.

1. Standardising Column Names and Setting the Index (The Consistency Rule)

Before I do any serious data manipulation, I enforce strict consistency on column names. Why? Because typing df['Employee ID '] accidentally instead of df['employee_id'] is a silent, frustrating error. Once the names are clean, I set the index. My golden rule is snake_case and lowercase everywhere, and ID columns should be the index. I use a simple command to strip whitespace, replace spaces with underscores, and convert everything to lowercase. python

# The Standardization Command
df.columns = df.columns.str.lower().str.replace(' ', '_').str.strip()
# Before: ['Employee_ID', 'First_Name', 'Phone']
# After: ['employee_id', 'first_name', 'phone']

Now that our columns are standardised. I can move on to set employee_id as an index. python

# Set the Employee ID as the DataFrame Index
# This is crucial for efficient lookups and clean merges later.
df.set_index('employee_id', inplace=True)

# Let’s review it real quick
print(df.index)

Output: markup

Index(['EMP1000', 'EMP1001', 'EMP1002', 'EMP1003', 'EMP1004', 'EMP1005','EMP1006', 'EMP1007', 'EMP1008', 'EMP1009',...'EMP2010', 'EMP2011', 'EMP2012', 'EMP2013', 'EMP2014', 'EMP2015','EMP2016', 'EMP2017', 'EMP2018', 'EMP2019'],dtype='object', name='employee_id', length=1020)

Perfect, everything is in place.

2. Fixing Data Types and Corruption (Tackling the Negative Phone Numbers)

My df.describe() check revealed the most urgent structural flaw: the Phone column, which was imported as a garbage numerical type. Since phone numbers are identifiers (not quantities), they must be strings.

In this phase, I'll convert the entire column to a string type, which will turn all those negative scientific notation numbers into human-readable text (though still full of non-digit characters). I will leave the actual text cleaning (removing parentheses, dashes, etc.) for a dedicated standardisation step (Step 4). python

# Fix the Phone dtype immediately
# Note: The column name is now 'phone' due to standardization in 3.1
df['phone'] = df['phone'].astype(str)

3. Handling Missing Values (The Age & Salary Gaps)

Finally, I address the gaps revealed by df.info(): the 211 missing Age values and the 24 missing Salary values (out of 1,020 total rows). My strategy depends entirely on the column's role and the magnitude of the missing data:

I execute this strategy with two separate commands: python

# 1. Removal: Drop rows missing the critical 'salary' data
df.dropna(subset=['salary'], inplace=True)

# 2. Imputation: Fill missing 'age' with the median
median_age = df['age'].median()
df['age'].fillna(median_age, inplace=True)

After these commands, I would run df.info() or isnull().sum() again just to confirm that the non-null counts for salary and age now reflect a clean dataset. python

# Rechecking the null counts for salary and age
df[‘salary’].isnull().sum())
df[‘age’].isnull().sum())

Output: markup

np.int64(0)

So far so good! By addressing the structural and missing data issues here, the subsequent steps can focus entirely on value standardisation, such as the messy 36 unique values in**department_region**—which we tackle in the next phase.

Step 4 — Value Standardization: Making Data Consistent

My DataFrame now has the right structure, but the values inside are still dirty. This step is about consistency. If “IT,” “i.t,” and “Info. Tech” all mean the same department, I need to force them into a single, clean value (“IT”). This prevents errors in grouping, filtering, and any statistical analysis based on categories.

1. Cleaning Corrupted String Data (The Phone Number Fix)

Remember the corrupted phone column from Step 2? It's currently a mess of negative scientific notation numbers that we converted to strings in Step 3. Now, it's time to extract the actual digits. So, I’ll be removing every non-digit character (dashes, parentheses, dots, etc.) and converting the result into a clean, unified format. Regular expressions (.str.replace()) are perfect for this. I use \D to match any non-digit character and replace it with an empty string. python

# The phone column is currently a string like '-9.994973e+09'
# We use regex to remove everything that isn't a digit
df['phone'] = df['phone'].str.replace(r'\D', '', regex=True)

# We can also truncate or format the resulting string if needed
# For example, keeping only the last 10 digits:
df['phone'] = df['phone'].str.slice(-10)
print(df['phone'])

Output: markup

employee_idEMP1000 1651623197EMP1001 1898471390EMP1002 5596363211EMP1003 3476490784EMP1004 1586734256...EMP2014 2470739200EMP2016 2508261122EMP2017 1261632487EMP2018 8995729892EMP2019 7629745492Name: phone, Length: 996, dtype: object

Looks much better now. This is always a good practice to clean identifiers that contain noise (like IDs with leading characters or zip codes with extensions).

2. Separating and Standardizing Categorical Data (Fixing the 36 Regions)

My df.nunique() check revealed 36 unique values in the department_region column. When I reviewed all the unique values in the column, the output revealed that they are all neatly structured as department-region (e.g., devops-california, finance-texas, cloud tech-new york). I guess one way to solve this is to split this single column into two dedicated columns. I'll split the column at the hyphen (-) and assign the parts to new columns: department and region. python

# 1. Split the combined column into two new, clean columns
df[['department', 'region']] = df['department_region'].str.split('-', expand=True)
Next, I’ll drop the department_region column since it’s pretty much useless now
# 2. Drop the redundant combined column
df.drop('department_region', axis=1, inplace=True)
Let’s review our new columns
print(df[[‘department’, ‘region’]])

Output: markup

department regionemployee_idEMP1000 devops californiaEMP1001 finance texasEMP1002 admin nevadaEMP1003 admin nevadaEMP1004 cloud tech florida... ... ...EMP2014 finance nevadaEMP2016 cloud tech texasEMP2017 finance new yorkEMP2018 hr floridaEMP2019 devops illinois[996 rows x 2 columns]

After splitting, the new department column has only 6 unique values (e.g., 'devops', 'finance', 'admin', etc.). This is great news. The values are already standardised and ready for analysis! I guess we could always map all similar departments to one single category. But I’m gonna skip that. I don’t want to get too advanced in this article.

3. Converting Date Columns (The Join_Date Fix)

The Join_Date column is usually read in as a string (object) type, which makes time-series analysis impossible. This means we have to convert it to a proper Pandas datetime object. pd.to_datetime() is the core function. I often use errors='coerce' as a safety net; if Pandas can't parse a date, it converts that value to NaT (Not a Time), which is a clean null value, preventing the whole operation from crashing. python

# Convert the join_date column to datetime objects
df['join_date'] = pd.to_datetime(df['join_date'], errors='coerce')

The conversion of dates enables powerful time-series analysis, like calculating average employee tenure or identifying turnover rates by year. After this step, every value in the dataset is clean, uniform, and correctly formatted. The categorical columns (like department and region) are ready for grouping and visualisation, and the numerical columns (like salary and age) are ready for statistical modeling. The dataset is officially ready for analysis.

Step 5 — Final Quality Check and Export

Before closing the notebook, I always perform one last audit to ensure everything is perfect, and then I export the data so I can perform analysis on it later.

The Final Data Quality Check

This is quick. I re-run the two most critical inspection methods to confirm that all my cleaning commands actually worked:

If these checks pass, I know the data is reliable.

Exporting the Clean Dataset

The final step is to save this cleaned version of the data. I usually save it as a new CSV file to keep the original messy file intact for reference. I use index=False here if I don't want the employee_id (which is now the index) to be saved as a separate column, or index=True if I want to save the index as the first column in the new CSV. python

# Exporting the clean DataFrame to a new CSV file
# We use index=True to keep our primary key (employee_id) in the exported file
df.to_csv('cleaned_employee_data.csv', index=True)

By exporting with a clear, new filename (e.g., _clean.csv), you officially mark the end of the cleaning phase and provide a clean slate for the next phase of the project.

Conclusion

Honestly, I used to feel overwhelmed by a messy dataset. The missing values, the weird data types, the cryptic columns — it felt like facing the blank page syndrome. But this structured, repeatable workflow changed everything. By focusing on Load, Inspect, Clean, Review, and Export , we established order instantly: standardizing column names, making the employee_id the index, and using smart strategies for imputation and splitting messy columns. Now, I can jump straight into the fun analysis part without constantly second-guessing my results. If you struggle with the initial data cleaning step, try out this workflow. I’d love to hear how it goes. If you want to play around with the dataset, you can download it here. Wanna connect? Feel free to say hi on these platforms LinkedIn Twitter YouTube Medium

Register Now to Continue Reading

Unlock Towards Data Science with a Free Account

All articles on Towards Data Science are available to registered users. Create a free account today to access this article and thousands more. Register for freeSign in to your account

Written By

Ibrahim Salami

See all from Ibrahim Salami Deep DivesData AnalysisData CleaningData VisualizationPandas Share This Article

Towards Data Science is a community publication. Submit your insights to reach our global audience and earn through the TDS Author Payment Program. Write for TDS

Related Articles

3 Methods to Reduce Overfitting of Machine Learning Models

Artificial Intelligence An overfit model is full of surprises but not the good ones. I have come... Soner Yıldırım June 2, 20215 min read

Why you should know Big Data

Data Science Defining Big Data and its potential threats Niklas Lang February 5, 20229 min read

Explore Pandas DataFrame with DataTile

Data Science Get quick insights into your data with just a few lines of code Edwin Tan January 27, 20222 min read

Insurance 'Redlining' Investigation

Data Science Analyzing Minority Access to Private Insurance in 1970s Chicago Alex Yelverton January 13, 20216 min read

The Best Resource For Learning Data Science

Artificial Intelligence Make your learning journey more efficient. Soner Yıldırım August 13, 20213 min read

Smartphone for Activity Recognition

Data Visualization Your phone knows what you're doing, even without a mic or camera Albers Uzila May 30, 202119 min read

Backpropagation Explained for Beginners (Part 1): Building the Intuition

Deep Learning Let's discover how neural networks learn, step by step Nikhil Dasari July 19, 20269 min read

Data Domains - Where do I start?

Practical guidance from the field Piethein Strengholt December 15, 202115 min read

How Lucky is a Bowl of Lucky Charms?

Data Science The first bowls of the box somehow seem more magical than the latter bowls. G. Jay Kerns March 16, 202417 min read

Your home for data science and AI. The world's leading publication for data science, data analytics, data engineering, machine learning, and artificial intelligence professionals. Subscribe to Our NewsletterWrite For TDS•About•Advertise•Privacy Policy•Terms of Use © Insight Media Group, LLC 2026•The New Stack•roadmap.sh•Creator Network

Recommended Articles

Close