Pandas read_csv(): Import Files and Validate CSV Data
Use read_csv() to import CSV records into a DataFrame. Set heading, type and missing-value rules deliberately, then check the imported records before analysis.
Read the student CSV 🔝
read_csv() accepts a file path, URL or readable buffer. The examples use an embedded copy of the student sample. A relative filename is resolved from the current working directory. Use your own path for a local file; on Windows a raw string such as r"C:\data\student.csv" avoids accidental escape sequences.
import pandas as pd
import sqlite3
from io import StringIO, BytesIO
student_csv = 'id,name,class,mark,gender\n1,John Deo,Four,75,female\n2,Max Ruin,Three,85,male\n3,Arnold,Three,55,male\n4,Krish Star,Four,60,female\n5,John Mike,Four,60,female\n6,Alex John,Four,55,male\n7,My John Rob,Fifth,78,male\n8,Asruid,Five,85,male\n9,Tes Qry,Six,78,male\n10,Big John,Four,55,female\n11,Ronald,Six,89,female\n12,Recky,Six,94,female\n13,Kty,Seven,88,female\n14,Bigy,Seven,88,female\n15,Tade Row,Four,88,male\n16,Gimmy,Four,88,male\n17,Tumyu,Six,54,male\n18,Honny,Five,75,male\n19,Tinny,Nine,18,male\n20,Jackly,Nine,65,female\n21,Babby John,Four,69,female\n22,Reggid,Seven,55,female\n23,Herod,Eight,79,male\n24,Tiddy Now,Seven,78,male\n25,Giff Tow,Seven,88,male\n26,Crelea,Seven,79,male\n27,Big Nose,Three,81,female\n28,Rojj Base,Seven,86,female\n29,Tess Played,Seven,55,male\n30,Reppy Red,Six,79,female\n31,Marry Toeey,Four,88,male\n32,Binn Rott,Seven,90,female\n33,Kenn Rein,Six,96,female\n34,Gain Toe,Seven,69,male\n35,Rows Noump,Six,88,female\n'
df = pd.read_csv(StringIO(student_csv))
print(df.head())
print("Rows:", len(df))
assert df.columns.tolist() == ["id", "name", "class", "mark", "gender"]
# Local file: pd.read_csv(r"C:\data\student.csv")
# Hosted file: pd.read_csv("https://www.plus2net.com/python/download/student.csv")Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Star Four 60 female
4 5 John Mike Four 60 female
Rows: 35Choose the row index 🔝
index_col selects a field to use as row labels. Without it, Pandas creates a RangeIndex; that index is not a new data column. Keep an ID column as text when leading zeros matter.
indexed = pd.read_csv(StringIO(student_csv), index_col="id")
print(indexed.head())
assert indexed.index.name == "id" and "id" not in indexed.columnsExpected output
name class mark gender
id
1 John Deo Four 75 female
2 Max Ruin Three 85 male
3 Arnold Three 55 male
4 Krish Star Four 60 female
5 John Mike Four 60 femaleUnderstand header=None and skiprows 🔝
header=0 uses the first eligible line as column labels. header=None treats the heading line as data. To read only records from a file with one heading line, also use skiprows=1 and supply names when meaningful labels are needed.
as_data = pd.read_csv(StringIO(student_csv), header=None)
print(as_data.head())
records = pd.read_csv(StringIO(student_csv), header=None, skiprows=1)
print("Heading removed:")
print(records.head())
assert as_data.iloc[0, 0] == "id" and len(records) == len(df)Expected output
0 1 2 3 4
0 id name class mark gender
1 1 John Deo Four 75 female
2 2 Max Ruin Three 85 male
3 3 Arnold Three 55 male
4 4 Krish Star Four 60 female
Heading removed:
0 1 2 3 4
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Star Four 60 female
4 5 John Mike Four 60 femaleReplace column names deliberately 🔝
Passing names without header=0 treats an existing heading as data. Use header=0 to replace the heading, or header=None for a genuinely headerless file.
labels = ["my_id", "my_name", "my_class", "my_mark", "my_gender"]
included = pd.read_csv(StringIO(student_csv), names=labels)
renamed = pd.read_csv(StringIO(student_csv), names=labels, header=0)
print("Without header=0:", included.iloc[0].tolist())
print(renamed.head())
assert len(included) == len(df) + 1 and len(renamed) == len(df)Expected output
Without header=0: ['id', 'name', 'class', 'mark', 'gender']
my_id my_name my_class my_mark my_gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Star Four 60 female
4 5 John Mike Four 60 femaleSelect columns and limit rows 🔝
usecols accepts names or positions. Its list order does not set output order; select columns again to enforce a desired order. nrows limits data rows, excluding the heading.
selected = pd.read_csv(StringIO(student_csv), usecols=["mark", "name"], nrows=3)
selected = selected.loc[:, ["name", "mark"]]
print(selected)
positions = pd.read_csv(StringIO(student_csv), header=None, skiprows=1, usecols=[3, 4], nrows=5)
print(positions)
assert selected.shape == (3, 2)Expected output
name mark
0 John Deo 75
1 Max Ruin 85
2 Arnold 55
3 4
0 75 female
1 85 male
2 55 male
3 60 female
4 60 femaleSkip metadata or particular records 🔝
An integer skiprows removes initial physical lines. If you skip the heading too, the next line can become the heading unless you supply names/header=None. A list lets you skip specific lines while retaining line zero as the heading.
metadata_csv = "Report: students\nGenerated for practice\n" + student_csv
after_metadata = pd.read_csv(StringIO(metadata_csv), skiprows=2)
skip_first_student = pd.read_csv(StringIO(student_csv), skiprows=[1])
print(after_metadata.head(2))
print("First ID after skipping line 1:", skip_first_student.iloc[0]["id"])
assert after_metadata.equals(df) and len(skip_first_student) == len(df) - 1Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
First ID after skipping line 1: 2Set missing-value rules per field 🔝
Default missing markers include NA and empty fields. keep_default_na=False preserves literal NA text. Combine it with per-column na_values to define the exact markers your data uses. See missing-value checks.
text = "name,mark,region\nRavi,75,NA\n--,na,EU\nRaju,n/a,NA\n"
missing = pd.read_csv(StringIO(text), keep_default_na=False,
na_values={"name": ["--"], "mark": ["na", "n/a"]})
missing["status"] = missing["name"].isna()
print(missing)
assert missing["mark"].isna().sum() == 2
assert missing["region"].tolist() == ["NA", "EU", "NA"]Expected output
name mark region status
0 Ravi 75.0 NA False
1 NaN NaN EU True
2 Raju NaN NA FalsePreserve IDs and nullable integers 🔝
Declare schema fields that should not be guessed. string preserves leading zeros; Int64 supports missing integer values. dtype_backend="numpy_nullable" is another option for nullable inferred types. See dtypes and astype().
text = "order_id,quantity\n001,2\n002,\n"
typed = pd.read_csv(StringIO(text), dtype={"order_id": "string", "quantity": "Int64"})
print(typed)
print(typed.dtypes)
assert typed["order_id"].tolist() == ["001", "002"]
assert typed["quantity"].isna().sum() == 1Expected output
order_id quantity
0 001 2
1 002 <NA>
order_id string
quantity Int64
dtype: objectConvert percentages while importing 🔝
converters runs a function on field values and takes precedence over dtype for that field. This rule turns numeric marks or percentage text into fractions. Define how blanks and unexpected text should behave before using it on less controlled data.
def to_float(value):
return float(value.strip().rstrip("%")) / 100
converted = pd.read_csv(StringIO(student_csv), converters={"mark": to_float})
print(converted.head())
percentage = pd.read_csv(StringIO("name,mark\nRavi,75%\n"), converters={"mark": to_float})
print(percentage)
assert converted.iloc[0]["mark"] == 0.75Expected output
id name class mark gender
0 1 John Deo Four 0.75 female
1 2 Max Ruin Three 0.85 male
2 3 Arnold Three 0.55 male
3 4 Krish Star Four 0.60 female
4 5 John Mike Four 0.60 female
name mark
0 Ravi 0.75Handle separators, quoted fields and decimal conventions 🔝
Use sep for the actual delimiter. Quoted commas are part of a field, not extra columns. decimal and thousands handle numeric conventions when the delimiter does not conflict with those characters.
text = 'product;amount;note\nPen;1.234,50;"blue, fine"\nBook;20,00;plain\n'
regional = pd.read_csv(StringIO(text), sep=";", decimal=",", thousands=".")
print(regional)
assert regional["amount"].sum() == 1254.5
assert regional.iloc[0]["note"] == "blue, fine"Expected output
product amount note
0 Pen 1234.5 blue, fine
1 Book 20.0 plainParse known date formats and inspect failures 🔝
For a known valid format, use parse_dates with date_format. For mixed or invalid input, import text and use to_datetime() with errors="coerce", then retain failed rows for review.
text = "id,date\n1,2026-01-02\n2,2026-01-03\n"
dated = pd.read_csv(StringIO(text), parse_dates=["date"], date_format="%Y-%m-%d")
print(dated)
raw_dates = pd.read_csv(StringIO("id,date\n1,2026-01-02\n2,bad\n"))
parsed = pd.to_datetime(raw_dates["date"], format="%Y-%m-%d", errors="coerce")
print("Invalid date IDs:", raw_dates.loc[parsed.isna(), "id"].tolist())
assert raw_dates.loc[parsed.isna(), "id"].tolist() == [2]Expected output
id date
0 1 2026-01-02
1 2 2026-01-03
Invalid date IDs: [2]Read bytes with an explicit encoding 🔝
encoding is useful for files and binary buffers. Keep decoding errors visible so damaged text can be investigated. A StringIO buffer already contains decoded text. UTF-8 with a BOM can be read with utf-8-sig.
binary = BytesIO("name,mark\nJosé,75\n".encode("utf-8-sig"))
decoded = pd.read_csv(binary, encoding="utf-8-sig")
print(decoded)
assert decoded.iloc[0]["name"] == "José"Expected output
name mark
0 José 75Process large files in chunks 🔝
chunksize returns an iterator rather than a DataFrame. Accumulate totals across chunks instead of collecting every chunk into one list. This example uses three rows per chunk. Chunking reduces the portion read at once; it does not guarantee every later operation uses little memory.
row_count = 0
mark_total = 0
with pd.read_csv(StringIO(student_csv), chunksize=3, usecols=["id", "mark"]) as reader:
for chunk in reader:
row_count += len(chunk)
mark_total += chunk["mark"].sum()
print("Rows:", row_count, "Mark total:", mark_total)
assert row_count == len(df) and mark_total == df["mark"].sum()Expected output
Rows: 35 Mark total: 2613Distinguish malformed rows from invalid values 🔝
The default on_bad_lines="error" raises on lines with too many fields. warn or skip can discard records and should be a deliberate import policy. They do not validate values, and short rows may become missing fields. Keep the source for investigation.
malformed = "id,amount\n1,5\n2,8,extra\n3,9\n"
try:
pd.read_csv(StringIO(malformed))
except pd.errors.ParserError:
print("Extra field detected: import stopped for review.")
valid_structure = pd.read_csv(StringIO("id,amount\n1,5\n2,bad\n"))
amount = pd.to_numeric(valid_structure["amount"], errors="coerce")
print("Invalid amount IDs:", valid_structure.loc[amount.isna(), "id"].tolist())
assert valid_structure.loc[amount.isna(), "id"].tolist() == [2]Expected output
Extra field detected: import stopped for review.
Invalid amount IDs: [2]Practical import: accept valid sales and retain review rows 🔝
For this synthetic extract, require an ID, non-negative amount and a valid ISO date. Preserve raw values. A rejected row is not a zero-value sale. Write accepted/review tables separately with to_csv().
sales_csv = "order_id,date,amount\n001,2026-01-02,5.25\n002,2026-01-03,20\n003,bad,8\n004,2026-01-04,bad\n005,2026-01-05,-2\n"
raw = pd.read_csv(StringIO(sales_csv), dtype="string", keep_default_na=False)
clean = raw.copy()
clean["date"] = pd.to_datetime(raw["date"], format="%Y-%m-%d", errors="coerce")
clean["amount"] = pd.to_numeric(raw["amount"], errors="coerce")
valid = raw["order_id"].str.strip().ne("") & clean["date"].notna() & clean["amount"].ge(0).fillna(False)
accepted = clean.loc[valid].copy()
review = raw.loc[~valid].copy()
print(accepted)
print("Review IDs:", review["order_id"].tolist())
print("Accepted revenue:", accepted["amount"].sum())
assert accepted["order_id"].tolist() == ["001", "002"]
assert len(accepted) + len(review) == len(raw)
assert accepted["amount"].sum() == 25.25Expected output
order_id date amount
0 001 2026-01-02 5.25
1 002 2026-01-03 20.0
Review IDs: ['003', '004', '005']
Accepted revenue: 25.25Round-trip a database result through CSV 🔝
The runnable example uses an in-memory SQLite database. The same read_sql → to_csv → read_csv → to_sql pattern works with a configured SQLAlchemy MySQL engine. Appending twice duplicates rows unless your import process prevents it. See database reading, MySQL connections and to_sql().
with sqlite3.connect(":memory:") as connection:
df.to_sql("student", connection, index=False, if_exists="fail")
exported = pd.read_sql("SELECT * FROM student", connection).to_csv(index=False)
imported = pd.read_csv(StringIO(exported))
imported.to_sql("student3", connection, index=False, if_exists="fail")
checked = pd.read_sql("SELECT COUNT(*) AS total FROM student3", connection)
print(checked)
assert checked.iloc[0]["total"] == len(df)
# Optional configured MySQL engine:
# from sqlalchemy import create_engine
# engine = create_engine(your_connection_url)
# database_rows = pd.read_sql("SELECT * FROM student", engine)
# csv_text = database_rows.to_csv(index=False)
# imported = pd.read_csv(StringIO(csv_text))
# imported.to_sql("student3", engine, index=False, if_exists="append")Expected output
total
0 35Choose parameters for the actual file 🔝
Start with sep, header, names, usecols, dtype, nrows and the missing-value rules. Add date_format/parse_dates, encoding or chunksize when required. For trailing footer lines use skipfooter with engine="python". A .csv.gz path permits inferred compression. low_memory=False is not a substitute for an explicit schema. Consult the current reference for engine-specific restrictions; older parameters such as squeeze, error_bad_lines and date_parser are no longer part of the current API.
Common questions 🔝
Can I use a URL? Yes, if it returns accessible CSV data; optional cloud backends may require dependencies and credentials. Can I read several CSV files at once? Read each and combine compatible tables with pd.concat(), checking their schemas. How do I read categories? Use dtype={"class": "category"} when appropriate. Why is an ID missing its zeros? Declare a string dtype during import. Why did a header become a record? Check names and header together. For a real GA4 export, inspect metadata lines before choosing skiprows; see the GA4 example. For a desktop chunked import, see CSV to SQLite with progress.
Exercise: import selected student fields 🔝
Read only id and mark, retain students with marks at least 80 and count them. Read the CSV again rather than relying on a previously filtered table.
Exercise solution 🔝
Compare the count with the selected ID list.
scores = pd.read_csv(StringIO(student_csv), usecols=["id", "mark"])
high = scores.loc[scores["mark"].ge(80)]
print(high)
print("Count:", len(high))
assert len(high) == int(scores["mark"].ge(80).sum())Expected output
id mark
1 2 85
7 8 85
10 11 89
11 12 94
12 13 88
13 14 88
14 15 88
15 16 88
24 25 88
26 27 81
27 28 86
30 31 88
31 32 90
32 33 96
34 35 88
Count: 15Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change import options and sales values, then inspect parsed records and review rows. Save your own copy to keep edits. All sample tables are included in the notebook.
Reference: Pandas read_csv documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials