Pandas Data Cleaning and Analysis: Step-by-Step

This guide shows you how to use the Python pandas library to clean messy data and perform basic analysis. You’ll learn to load data, handle missing values, remove duplicates, filter rows, and compute summary statistics—turning raw CSV or Excel files into a clean, analysis-ready DataFrame.

What You’ll Need

• Python installed (3.8 or newer)
• pandas installed (pip install pandas)
• A data file (CSV, Excel, or similar) to practice on
• Optional: Jupyter Notebook or any Python IDE

Step-by-Step

1. Import pandas – Start your script with import pandas as pd. This gives you access to all the functions you’ll need.
2. Load your data – Use df = pd.read_csv('yourfile.csv') for CSV files, or pd.read_excel('yourfile.xlsx') for Excel. If your file has a different delimiter (like tabs or semicolons), add sep='\t' or sep=';'.
3. Inspect the data – Run df.head() to see the first 5 rows, df.info() to see column types and non-null counts, and df.describe() for quick summary statistics of numeric columns.
4. Handle missing values – Check for missing data with df.isnull().sum(). To drop rows with any missing values, use df.dropna(). To fill them with a value (e.g., 0 or the column mean), use df.fillna(value) or df['col'].fillna(df['col'].mean()).
5. Remove duplicates – Find duplicates with df.duplicated().sum(). Remove them with df.drop_duplicates(). If you want to keep the first occurrence, use keep='first' (default).
6. Fix data types – Check types with df.dtypes. Convert a column to numeric with pd.to_numeric(df['col'], errors='coerce') (coerce turns invalid values into NaN). Convert to datetime with pd.to_datetime(df['date'], errors='coerce').
7. Clean text data – Strip whitespace with df['col'].str.strip(), convert to lowercase with .str.lower(), and replace specific values with .replace(). For example, df['gender'] = df['gender'].replace({'M':'Male', 'F':'Female'}).
8. Filter rows – Use boolean indexing: df[df['age'] > 30] keeps rows where age is over 30. Combine conditions with & (and) and | (or), e.g., df[(df['age'] > 30) & (df['city'] == 'NYC')].
9. Sort and reorder – Sort by a column with df.sort_values('column', ascending=False). Reset the index after filtering or sorting with df.reset_index(drop=True).
10. Compute summary statistics – Use df['col'].mean(), .median(), .sum(), .min(), .max(), and .std(). For group-wise stats, use df.groupby('category')['value'].mean().
11. Rename columns – Use df.rename(columns={'old_name':'new_name'}, inplace=True) or assign directly: df.columns = ['a','b','c'].
12. Export clean data – Save your cleaned DataFrame with df.to_csv('clean_data.csv', index=False) or df.to_excel('clean_data.xlsx', index=False).

Common Mistakes

• Forgetting to assign the result – Many pandas operations return a new DataFrame. If you don’t write df = df.dropna(), the original stays unchanged.
• Using inplace=True inconsistently – It works for some methods (like rename) but not all. It’s safer to always assign the result.
• Mixing up dropna() and fillna() – Dropping removes rows; filling replaces missing values. Choose based on whether you can afford to lose data.
• Ignoring data types – If a column of numbers is stored as text, calculations will fail or give wrong results. Always check dtypes.
• Not using errors='coerce' when converting – Without it, a single bad value will raise an error and stop your script.
• Overlooking the index parameter when saving – If you don’t set index=False, pandas writes the row numbers as an extra column.

Tips

• Always make a copy of your original DataFrame before heavy cleaning: df_clean = df.copy().
• Use df.sample(10) to see random rows, which helps spot issues that head() might miss.
• Chain operations with .pipe() or parentheses to keep code readable, e.g., df.dropna().drop_duplicates().reset_index(drop=True).
• Use pd.set_option('display.max_columns', None) to see all columns when printing.
• For large datasets, use df.memory_usage(deep=True) to check memory and consider downcasting numeric types.
• Document your cleaning steps in comments so you can reproduce them later.

Troubleshooting

• File not found – Check the file path. Use import os; print(os.getcwd()) to see your current working directory, and adjust the path accordingly.
• UnicodeDecodeError when reading CSV – Specify the encoding: pd.read_csv('file.csv', encoding='utf-8') or try 'latin1' if your file has special characters.
• Date column not parsing – Pass parse_dates=['date_column'] to read_csv to convert it automatically.
• Operation fails because of mixed types – Convert the column to numeric first: df['col'] = pd.to_numeric(df['col'], errors='coerce').
• Groupby result looks wrong – Check that the grouping column has consistent values (e.g., no extra spaces). Use df['col'].str.strip() to clean it.
• MemoryError on large files – Read in chunks with pd.read_csv('file.csv', chunksize=10000) and process each chunk separately.

Frequently Asked Questions

1. What is the difference between dropna() and fillna()? – dropna() removes rows (or columns) that contain missing values; fillna() replaces missing values with a specified value or method (like forward fill).
2. How do I remove duplicate rows based on one column? – Use df.drop_duplicates(subset='column_name') to keep only the first occurrence for each unique value in that column.
3. Why does my numeric column show as object? – It likely contains non-numeric values (like commas or ‘N/A’). Convert with pd.to_numeric(df['col'], errors='coerce') after cleaning those strings.
4. How do I handle missing dates? – You can drop rows with missing dates using df.dropna(subset=['date_col']) or fill them with a placeholder like pd.NaT (which is the default for datetime).
5. Can I use pandas for Excel files with multiple sheets? – Yes, use pd.read_excel('file.xlsx', sheet_name='Sheet2') or sheet_name=None to get a dict of all sheets.
6. How do I save a DataFrame without the index column? – Use df.to_csv('file.csv', index=False) or df.to_excel('file.xlsx', index=False).

Related

• Palworld Tops Steam Concurrent Charts?
• Palworld Best Ancient Relic Farm Locations
• Security First Development: Beyond DevSecOps