Module 27 · Extension

Import CSV and text with confidence

Choose explicit parsing rules, preserve identifiers, and verify imported records.

Before you start: You can inspect a DataFrame, identify missing values, and read a CSV file.

Python loads from this site on your first run.

Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.

M27C01 · Explore · predict · practice

Inspect a real CSV before analyzing it

The bundled Palmer Penguins CSV is a real public dataset, preserved from its authoritative upstream source. Read the field definitions in data/public/README.md before interpreting its values. The simplified table records 344 observations, eight fields, and source missing values written as NA.

ReadWhat does one row describe, according to the source documentation?

PredictWill the preview contain only complete observations?

ExplainWhy does a successful read_csv call not prove the data are ready for every analysis?

Read the example

path = DATA_DIR / "public" / "penguins.csv"
df = pd.read_csv(path, na_values=["NA"])
result = df.head(5)
print(result)
print("Shape:", df.shape)
print("Columns:", df.columns.tolist())
print("Missing values:")
print(df.isna().sum())

Verified example output · pandas 2.2.3

  species     island  bill_length_mm  bill_depth_mm  flipper_length_mm  body_mass_g     sex  year
0  Adelie  Torgersen            39.1           18.7              181.0       3750.0    male  2007
1  Adelie  Torgersen            39.5           17.4              186.0       3800.0  female  2007
2  Adelie  Torgersen            40.3           18.0              195.0       3250.0  female  2007
3  Adelie  Torgersen             NaN            NaN                NaN          NaN     NaN  2007
4  Adelie  Torgersen            36.7           19.3              193.0       3450.0  female  2007
Shape: (344, 8)
Columns: ['species', 'island', 'bill_length_mm', 'bill_depth_mm', 'flipper_length_mm', 'body_mass_g', 'sex', 'year']
Missing values:
species               0
island                0
bill_length_mm        2
bill_depth_mm         2
flipper_length_mm     2
body_mass_g           2
sex                  11
year                  0
dtype: int64

Watch for this. These are historical study observations from 2007–2009, not current measurements or a population census.

Your turn

Read only species, island, and body_mass_g using usecols. Store the full three-column table in result and print its shape and the number of known body masses.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

path = DATA_DIR / "public" / "penguins.csv"
df = pd.read_csv(path, na_values=["NA"])
result = df.head(5)
print(result)
print("Shape:", df.shape)
print("Columns:", df.columns.tolist())
print("Missing values:")
print(df.isna().sum())

Show a hint

Pass the three names as a list to usecols; count() counts known values.

Show one worked solution

path = DATA_DIR / "public" / "penguins.csv"
result = pd.read_csv(path, usecols=["species", "island", "body_mass_g"], na_values=["NA"])
print(result.head())
print("Shape:", result.shape)
print("Known body masses:", result["body_mass_g"].count())

The result has 344 rows and three columns. There are 342 known body masses; two missing measurements remain missing.

Compare your explanations

Read. One observed penguin in this simplified research dataset. The table does not provide an animal ID.
Predict. No. The fourth source record lacks its measurements and sex, so missing values are visible in the first five rows.
Explain. You still need to check field meaning, units, types, missingness, and which records the question requires.

One quick check

How many body masses support a summary of the available measurements?

What you should be able to explain. Inspect the parsed table and its documentation together.

Your own assessment, separate from code checks

M27C02 · Explore · predict · practice

The separator is part of the file contract

A text file does not need a .csv extension to hold a table. Its delimiter determines where fields begin and end. The TSV and TXT files here are documented format conversions of the same public observations, so you can compare the imports directly.

ReadWhy does the comma-based import have only one column?

PredictWhat shape should the correctly parsed TSV have?

ExplainDoes changing a filename extension change its internal delimiter?

Read the example

path = DATA_DIR / "public" / "penguins.tsv"
wrong = pd.read_csv(path, sep=",")
result = pd.read_csv(path, sep="\t", na_values=["NA"])
print("With a comma separator:", wrong.shape)
print("With a tab separator:", result.shape)
print(result.head(3))

Verified example output · pandas 2.2.3

With a comma separator: (344, 1)
With a tab separator: (344, 8)
  species     island  bill_length_mm  bill_depth_mm  flipper_length_mm  body_mass_g     sex  year
0  Adelie  Torgersen            39.1           18.7              181.0       3750.0    male  2007
1  Adelie  Torgersen            39.5           17.4              186.0       3800.0  female  2007
2  Adelie  Torgersen            40.3           18.0              195.0       3250.0  female  2007

Watch for this. Do not use skiprows to hide unexpected malformed records; first identify what those rows mean.

Your turn

Read penguins.txt into result using its semicolon separator and skip its two comment lines using comment="#". Read penguins.csv into reference and compare the two parsed tables.

For the code check, keep these result variable names: reference, result. Different valid approaches are welcome.

Your Python code

path = DATA_DIR / "public" / "penguins.tsv"
wrong = pd.read_csv(path, sep=",")
result = pd.read_csv(path, sep="\t", na_values=["NA"])
print("With a comma separator:", wrong.shape)
print("With a tab separator:", result.shape)
print(result.head(3))

Show a hint

Use pd.read_csv even though the extension is .txt.

Show one worked solution

folder = DATA_DIR / "public"
result = pd.read_csv(folder / "penguins.txt", sep=";", comment="#", encoding="utf-8", na_values=["NA"])
reference = pd.read_csv(folder / "penguins.csv", na_values=["NA"])
pd.testing.assert_frame_equal(result, reference)
print(result.head(3))
print("Same parsed values, columns, and row order as the source CSV.")

The comment lines are excluded from the table, and all 344 observations remain. No conversion to an Excel file is necessary to read this text format.

Compare your explanations

Read. The TSV uses tabs between fields; a comma separator does not split those fields.
Predict. 344 rows and eight columns, matching the original CSV.
Explain. No. Parsing must follow the actual file contents and documented format.

One quick check

Which setting separates a TSV into fields?

What you should be able to explain. Read a table according to its actual delimiter and header rules.

Your own assessment, separate from code checks

M27C03 · Explore · predict · practice

Preserve leading zeros and literal NA codes

This small import fixture is explicitly fictional and separate from the penguin research data. Its IDs contain leading zeros, NA is a literal site code, and MISSING marks one unavailable amount. Encoding, quoting, decimal symbols, and missing-value rules all affect what a reader produces.

ReadWhy is record_id read as text?

PredictDoes the NA site code become a missing value in this import?

ExplainWhy does the semicolon inside the second note not create an extra column?

Read the example

path = DATA_DIR / "public" / "import_practice_semicolon.csv"
result = pd.read_csv(path, sep=";", comment="#", encoding="utf-8-sig", dtype="string", keep_default_na=False)
print(result)
print("IDs:", result["record_id"].tolist())
print("Site codes:", result["site_code"].tolist())

Verified example output · pandas 2.2.3

  record_id site_code amount_eur               note
0       001        NA      12,50    café, east desk
1       010        EU    MISSING  quoted; semicolon
2       100        NA       0,00      recorded zero
IDs: ['001', '010', '100']
Site codes: ['NA', 'EU', 'NA']

Watch for this. The meanings NA=North Area and EU=East Unit apply only to this invented fixture. Do not impose those meanings on other sources.

Your turn

Read the fixture again with record_id and site_code as strings, comma decimal symbols for amount_eur, and only MISSING in amount_eur treated as missing. Keep the NA site code unchanged. Print the known amount count and sum.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

path = DATA_DIR / "public" / "import_practice_semicolon.csv"
result = pd.read_csv(path, sep=";", comment="#", encoding="utf-8-sig", dtype="string", keep_default_na=False)
print(result)
print("IDs:", result["record_id"].tolist())
print("Site codes:", result["site_code"].tolist())

Show a hint

Use decimal="," and a column-specific na_values dictionary while keeping keep_default_na=False.

Show one worked solution

path = DATA_DIR / "public" / "import_practice_semicolon.csv"
result = pd.read_csv(path, sep=";", comment="#", encoding="utf-8-sig", dtype={"record_id": "string", "site_code": "string"}, decimal=",", keep_default_na=False, na_values={"amount_eur": ["MISSING"]})
print(result)
print("Known amounts:", result["amount_eur"].count())
print("Known amount sum:", result["amount_eur"].sum())

The amounts are 12.5, missing, and 0.0 EUR. Two known amounts sum to 12.5; the missing value was not replaced with zero. Both NA site codes and all leading-zero IDs are preserved.

Compare your explanations

Read. It is an identifier, not a quantity. Converting 001 to an integer would lose its leading zeros.
Predict. No. keep_default_na=False preserves the literal code as text.
Explain. The field is quoted using CSV quoting rules, so the embedded separator belongs to the note.

One quick check

Which is a safe way to preserve leading-zero identifiers?

What you should be able to explain. Specify identifiers, missing tokens, and number formats deliberately.

Your own assessment, separate from code checks

M27C04 · Explore · predict · practice

Verify a CSV round trip with explicit types

A CSV stores text and does not carry a pandas dtype schema. A repeatable import can specify its expected text, integer, and measurement types. Exporting and reading back the table lets you check that values, missingness, and the chosen schema survive.

ReadWhy use nullable Int64 for body_mass_g?

PredictHow many columns should the exported CSV contain?

ExplainWhy specify the schema again when reading the exported CSV?

Read the example

schema = {"species": "string", "island": "string", "bill_length_mm": "Float64", "bill_depth_mm": "Float64", "flipper_length_mm": "Int64", "body_mass_g": "Int64", "sex": "string", "year": "Int64"}
df = pd.read_csv(DATA_DIR / "public" / "penguins.csv", dtype=schema, na_values=["NA"])
path = OUTPUT_DIR / "penguins_round_trip.csv"
df.to_csv(path, index=False, na_rep="NA")
result = pd.read_csv(path, dtype=schema, na_values=["NA"])
pd.testing.assert_frame_equal(result, df)
print("Rows and columns:", result.shape)
print(result.dtypes)
print("Round-trip values and declared types match.")

Verified example output · pandas 2.2.3

Rows and columns: (344, 8)
species              string[python]
island               string[python]
bill_length_mm              Float64
bill_depth_mm               Float64
flipper_length_mm             Int64
body_mass_g                   Int64
sex                  string[python]
year                          Int64
dtype: object
Round-trip values and declared types match.

Watch for this. An export should go to OUTPUT_DIR, leaving the downloaded source unchanged.

Your turn

Using the same explicit schema, store only Gentoo observations in gentoo with a reset index. Export gentoo to penguins_gentoo.csv, read the file back into result, and compare result with gentoo.

For the code check, keep these result variable names: gentoo, result. Different valid approaches are welcome.

Your Python code

schema = {"species": "string", "island": "string", "bill_length_mm": "Float64", "bill_depth_mm": "Float64", "flipper_length_mm": "Int64", "body_mass_g": "Int64", "sex": "string", "year": "Int64"}
df = pd.read_csv(DATA_DIR / "public" / "penguins.csv", dtype=schema, na_values=["NA"])
path = OUTPUT_DIR / "penguins_round_trip.csv"
df.to_csv(path, index=False, na_rep="NA")
result = pd.read_csv(path, dtype=schema, na_values=["NA"])
pd.testing.assert_frame_equal(result, df)
print("Rows and columns:", result.shape)
print(result.dtypes)
print("Round-trip values and declared types match.")

Show a hint

Reset the filtered index because the CSV intentionally does not store it.

Show one worked solution

schema = {"species": "string", "island": "string", "bill_length_mm": "Float64", "bill_depth_mm": "Float64", "flipper_length_mm": "Int64", "body_mass_g": "Int64", "sex": "string", "year": "Int64"}
df = pd.read_csv(DATA_DIR / "public" / "penguins.csv", dtype=schema, na_values=["NA"])
gentoo = df.loc[df["species"] == "Gentoo"].reset_index(drop=True)
path = OUTPUT_DIR / "penguins_gentoo.csv"
gentoo.to_csv(path, index=False, na_rep="NA")
result = pd.read_csv(path, dtype=schema, na_values=["NA"])
pd.testing.assert_frame_equal(result, gentoo)
print("Gentoo rows:", len(result))
print("Known body masses:", result["body_mass_g"].count())

The subset contains 124 Gentoo observations, with 123 known body masses. Its row labels are newly created positions, not animal identities.

Compare your explanations

Read. The source contains whole-number masses and missing entries; nullable Int64 represents both without inventing values.
Predict. Eight, because index=False does not add the DataFrame index as a ninth field.
Explain. CSV has no embedded pandas dtype contract, so the reader would otherwise infer types again.

One quick check

Does a CSV automatically preserve a pandas nullable-integer dtype?

What you should be able to explain. Treat an import/export schema as part of reproducibility.

Your own assessment, separate from code checks

Previous: End-to-end classroom project Next: Work across Excel and JSON formats

Further detail: official documentation for this topic.