Read CSV, Display Rows and Columns, Clean Data and Save Back

1. Big Picture

A CSV file is one of the most common ways to store data. CSV means Comma Separated Values. pandas can read CSV data into a DataFrame, clean it and save it again.

2. What is a CSV File?

A CSV file stores data in rows and columns. Each row is usually one record. Each column stores one type of information.

Example CSV file: students.csv

student_id,name,city,marks,attendance
1,Amit,Varanasi,35,55
2,Ravi,Lucknow,42,60
3,Sita,Varanasi,50,65
4,Meena,Delhi,,70
5,Kabir,Lucknow,70,78
5,Kabir,Lucknow,70,78
6,Anu,Delhi,80,85
7,Farhan,Varanasi,88,90
8,Pooja,Lucknow,96,95

Problems in this CSV

• One value is missing in the marks column.
• One row is duplicated.
• The data should be inspected before use.

3. Setup

We need pandas. If pandas is not installed, install it with:

pip install pandas

Import pandas

import pandas as pd

4. Reading Data from CSV

To read a CSV file, use pd.read_csv().

import pandas as pd

df = pd.read_csv("students.csv")

print(df)

Explanation

5. Displaying Rows and Columns

5.1 Display first rows

print(df.head())

head() displays the first 5 rows by default.

print(df.head(3))

This displays the first 3 rows.

5.2 Display last rows

print(df.tail())

tail() displays the last 5 rows by default.

print(df.tail(2))

This displays the last 2 rows.

5.3 Display number of rows and columns

print(df.shape)

shape returns a pair: number of rows and number of columns.

rows, columns = df.shape

print("Rows:", rows)
print("Columns:", columns)

5.4 Display full information

print(df.info())

info() shows column names, data types and missing value counts.

5.5 Display statistics

print(df.describe())

describe() shows count, mean, standard deviation, minimum, maximum and quartile values for numeric columns.

6. Working with Columns

6.1 Display column names

print(df.columns)

6.2 Display one column

print(df["name"])

6.3 Display multiple columns

print(df[["name", "city", "marks"]])

6.4 Rename columns

df = df.rename(columns={
    "student_id": "id",
    "name": "student_name"
})

print(df.columns)

6.5 Clean column names

Sometimes CSV files have column names like Student Name or Total Marks. We can clean them.

df.columns = df.columns.str.strip()
df.columns = df.columns.str.lower()
df.columns = df.columns.str.replace(" ", "_")

print(df.columns)

7. Working with Rows

7.1 Display one row by index

print(df.iloc[0])

iloc[0] displays the first row by position.

7.2 Display selected rows

print(df.iloc[0:3])

This displays rows from position 0 to position 2.

7.3 Filter rows by condition

high_marks = df[df["marks"] >= 70]

print(high_marks)

7.4 Filter rows with multiple conditions

good_students = df[
    (df["marks"] >= 70) &
    (df["attendance"] >= 80)
]

print(good_students)

7.5 Sort rows

sorted_df = df.sort_values("marks", ascending=False)

print(sorted_df)

8. Cleaning CSV Data

Data cleaning means fixing the problems in the dataset before analysis, graphing or training a model.

8.1 Check missing values

print(df.isnull().sum())

isnull() checks missing values. sum() counts missing values column by column.

8.2 Fill missing values

df["marks"] = df["marks"].fillna(df["marks"].mean())

print(df)

This replaces missing marks with the average marks.

8.3 Remove rows with missing values

df = df.dropna()

print(df)

dropna() removes rows that contain missing values.

8.4 Check duplicate rows

print(df.duplicated())

8.5 Count duplicate rows

print(df.duplicated().sum())

8.6 Remove duplicate rows

df = df.drop_duplicates()

print(df)

8.7 Convert column data type

df["marks"] = df["marks"].astype(int)
df["attendance"] = df["attendance"].astype(int)

print(df.dtypes)

astype() converts a column from one data type to another.

8.8 Remove invalid values

df = df[df["marks"] >= 0]
df = df[df["marks"] <= 100]

print(df)

This keeps only marks between 0 and 100.

8.9 Clean text data

df["name"] = df["name"].str.strip()
df["city"] = df["city"].str.strip()
df["city"] = df["city"].str.title()

print(df)

This removes extra spaces and converts city names to title case.

9. Writing Back to CSV

After cleaning the data, save it as a new CSV file using to_csv().

df.to_csv("clean_students.csv", index=False)

Explanation

10. Complete Mini Project

This program reads a CSV file, displays rows and columns, cleans the data and saves the clean result into a new CSV file.

import pandas as pd

# -------------------------------------------------
# 1. READ DATA FROM CSV
# -------------------------------------------------

df = pd.read_csv("students.csv")

print("Original DataFrame:")
print(df)

# -------------------------------------------------
# 2. DISPLAY BASIC INFORMATION
# -------------------------------------------------

print("\nFirst 5 rows:")
print(df.head())

print("\nLast 5 rows:")
print(df.tail())

print("\nRows and columns:")
print(df.shape)

print("\nColumn names:")
print(df.columns)

print("\nDataFrame information:")
print(df.info())

print("\nDataFrame statistics:")
print(df.describe())

# -------------------------------------------------
# 3. CLEAN COLUMN NAMES
# -------------------------------------------------

df.columns = df.columns.str.strip()
df.columns = df.columns.str.lower()
df.columns = df.columns.str.replace(" ", "_")

print("\nCleaned column names:")
print(df.columns)

# -------------------------------------------------
# 4. CHECK MISSING VALUES
# -------------------------------------------------

print("\nMissing values before cleaning:")
print(df.isnull().sum())

# -------------------------------------------------
# 5. FILL MISSING NUMERIC VALUES
# -------------------------------------------------

if "marks" in df.columns:
    df["marks"] = df["marks"].fillna(df["marks"].mean())

if "attendance" in df.columns:
    df["attendance"] = df["attendance"].fillna(df["attendance"].mean())

print("\nMissing values after filling:")
print(df.isnull().sum())

# -------------------------------------------------
# 6. REMOVE DUPLICATES
# -------------------------------------------------

print("\nDuplicate rows before cleaning:")
print(df.duplicated().sum())

df = df.drop_duplicates()

print("\nDuplicate rows after cleaning:")
print(df.duplicated().sum())

# -------------------------------------------------
# 7. CLEAN TEXT COLUMNS
# -------------------------------------------------

if "name" in df.columns:
    df["name"] = df["name"].astype(str).str.strip()

if "city" in df.columns:
    df["city"] = df["city"].astype(str).str.strip().str.title()

# -------------------------------------------------
# 8. FIX DATA TYPES
# -------------------------------------------------

if "marks" in df.columns:
    df["marks"] = df["marks"].astype(int)

if "attendance" in df.columns:
    df["attendance"] = df["attendance"].astype(int)

# -------------------------------------------------
# 9. REMOVE INVALID VALUES
# -------------------------------------------------

if "marks" in df.columns:
    df = df[df["marks"] >= 0]
    df = df[df["marks"] <= 100]

if "attendance" in df.columns:
    df = df[df["attendance"] >= 0]
    df = df[df["attendance"] <= 100]

# -------------------------------------------------
# 10. DISPLAY CLEAN DATA
# -------------------------------------------------

print("\nCleaned DataFrame:")
print(df)

print("\nFinal rows and columns:")
print(df.shape)

# -------------------------------------------------
# 11. WRITE CLEAN DATA BACK TO CSV
# -------------------------------------------------

df.to_csv("clean_students.csv", index=False)

print("\nClean data saved as clean_students.csv")

Sample CSV to create first

Create a file named students.csv and paste this data:

student_id,name,city,marks,attendance
1,Amit,Varanasi,35,55
2,Ravi,Lucknow,42,60
3,Sita,Varanasi,50,65
4,Meena,Delhi,,70
5,Kabir,Lucknow,70,78
5,Kabir,Lucknow,70,78
6,Anu,Delhi,80,85
7,Farhan,Varanasi,88,90
8,Pooja,Lucknow,96,95

11. pandas CSV Command Reference

12. Practice in Our Python Editor

Use the embedded Python editor below to practise reading, displaying, cleaning and saving CSV data.

13. Practice Tasks

1. Create a CSV file named products.csv.
2. Add columns: product_id, product_name, category, price, units_sold.
3. Read it using pd.read_csv().
4. Display first 5 rows.
5. Display last 5 rows.
6. Display all column names.
7. Display the number of rows and columns.
8. Check missing values.
9. Fill missing prices with average price.
10. Remove duplicate rows.
11. Clean product names using str.strip().
12. Save the final file as clean_products.csv.

Self Test

14. Final Summary

CSV file
   ↓
pd.read_csv()
   ↓
pandas DataFrame
   ↓
head(), tail(), shape, columns, info(), describe()
   ↓
isnull(), fillna(), dropna(), drop_duplicates()
   ↓
clean text and fix data types
   ↓
to_csv()
   ↓
clean CSV file