# Saving the Clean Data: to_csv and to_excel

A clean table lives only as long as the notebook's kernel, so to_csv with index=False saves it as a file the next notebook can load, to_excel saves a workbook for people who use Excel, and keeping data/raw and data/clean folders, with a save function that refuses the raw folder, means the raw file is never written over.

Apprentice · Python · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Save a DataFrame with to_csv(index=False) and to_excel, and read the file back to check it
- Keep raw and clean data in separate folders, and never write over the raw file

## Key takeaways

- to_csv() writes a DataFrame to a CSV file. Without index=False it also writes the row numbers, which come back as an extra column named Unnamed 0.
- to_excel() writes an Excel workbook (pandas uses the openpyxl package for it), with sheet_name for the sheet's name and index=False again.
- Read a saved file back and check its rows and columns, so you know what the next person will load.
- Keep the raw file in data/raw and never write to it. Clean files go in data/clean, so a cleaning mistake can always be fixed by running the cleaning again from the raw data.

A clean table in a notebook lasts until the kernel restarts. To answer questions with it tomorrow, hand it to a teammate, or send it to someone who lives in Excel, it has to become a file. Saving is the last step of every cleaning job, and where you save matters as much as how.

The example is the Ridgeview School weather station: its raw file is `data/raw/readings.csv`, a reading every 30 minutes for a school year, and the clean table comes from the cleaning in *A Cleaning Function, Step by Step*. Here it's done in one cell, so the lesson stands on its own:

```python
import numpy as np
import pandas as pd

raw = pd.read_csv("data/raw/readings.csv")
readings = raw.replace(-999, np.nan).drop_duplicates(subset=["timestamp"])
temp, humidity = readings["temp_f"], readings["humidity_pct"]
readings["suspect"] = (temp > 120) | (humidity > 100)
readings["temp_f"] = temp.mask(temp > 120)
readings["humidity_pct"] = humidity.mask(humidity > 100)
print("clean rows:", len(readings))
```

```text
clean rows: 13296
```

## to_csv, and index=False

**`to_csv(path)`** writes the table to a CSV file, plain text that any tool can open. Save it, then read it back the way the next notebook will:

```python
readings.to_csv("readings_clean.csv")
back = pd.read_csv("readings_clean.csv")
list(back.columns)
```

```text
['Unnamed: 0', 'timestamp', 'temp_f', 'humidity_pct', 'rain_in', 'wind_mph', 'battery_v', 'suspect']
```

An extra first column, `Unnamed: 0`. By default `to_csv` also writes the index, the row labels on the left of every table pandas prints, and reading the file back turns them into a column with no name. **`index=False`** leaves them out:

```python
readings.to_csv("readings_clean.csv", index=False)
back = pd.read_csv("readings_clean.csv")
print(list(back.columns))
print("rows:", len(back))
```

```text
['timestamp', 'temp_f', 'humidity_pct', 'rain_in', 'wind_mph', 'battery_v', 'suspect']
rows: 13296
```

Same columns, same rows. The missing readings are empty cells in the file, and `read_csv` reads them as `NaN` again. Reading back is the check that the file says what you meant.

## to_excel for people who use Excel

The science teacher wants to open the readings in Excel. **`to_excel(path)`** writes an `.xlsx` workbook, using the openpyxl package, the same one `read_excel` uses (*Setup: pandas and Notebook Support* installs it). `sheet_name=` names the sheet, and `index=False` works the same way:

```python
readings.to_excel("readings_clean.xlsx", sheet_name="readings", index=False)
book = pd.read_excel("readings_clean.xlsx", sheet_name="readings")
print("rows:", len(book))
print("suspect rows:", book["suspect"].sum())
```

```text
rows: 13296
suspect rows: 71
```

Keep the CSV as the file your code loads: it's plain text, quick to read, and every tool understands it. The workbook is a copy for people.

## A raw folder and a clean folder

A photographer never edits the only copy of a photo: the original stays as it came off the camera, and every edit is saved as a new file. Data works the same way. The **raw data**, the file exactly as the station sent it, is your evidence. If a cleaning step turns out to be wrong next month, you fix the step and run the cleaning again from the raw file. Write over it, and the -999s and the sun-on-the-sensor readings are gone for good, with nothing to check the clean file against.

So each project keeps two folders:

```text
data/
  raw/
    readings.csv            as the station sent it, never written to
  clean/
    readings_clean.csv      made by the cleaning, safe to make again
```

`pathlib`'s `Path` builds the paths (`/` joins a folder and a file name), and `mkdir(parents=True, exist_ok=True)` makes the folder when it isn't there yet. A small save function also refuses the raw folder. `raise ValueError("...")` stops a function with an error and your message, the way pandas' own errors stop your code:

```python
from pathlib import Path

def save_clean(df, folder):
    folder = Path(folder)
    if folder.name == "raw":
        raise ValueError("save_clean won't write into a raw folder")
    folder.mkdir(parents=True, exist_ok=True)
    path = folder / "readings_clean.csv"
    df.to_csv(path, index=False)
    return path

saved = save_clean(readings, "data/clean")
print("saved:", saved.name, saved.exists())
```

```text
saved: readings_clean.csv True
```

And the mistake it's there for:

```python
save_clean(readings, "data/raw")
```

```text
ValueError: save_clean won't write into a raw folder
```

The raw file is untouched, and the clean one can be made again any time from it.

## Check yourself

2 questions to try on the lesson page.

## So what

Save clean data with `to_csv(index=False)`, read it back to check it, give Excel users a workbook copy, and keep the raw file in its own folder, never written to, so any cleaning can be run again from the evidence.

## Key terms

- [to_csv()](https://www.ask-codey.com/glossary/to-csv)
- [to_excel()](https://www.ask-codey.com/glossary/to-excel)
- [CSV File](https://www.ask-codey.com/glossary/csv-file)
- [Raw Data](https://www.ask-codey.com/glossary/raw-data)

## Related

- [A Cleaning Function, Step by Step](https://www.ask-codey.com/lessons/pandas-cleaning-function)
- [Loading a CSV File](https://www.ask-codey.com/lessons/pandas-read-csv)
- [Loading an Excel Workbook](https://www.ask-codey.com/lessons/pandas-read-excel)

Source: https://www.ask-codey.com/lessons/pandas-saving-data
