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
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:
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))
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:
readings.to_csv("readings_clean.csv")
back = pd.read_csv("readings_clean.csv")
list(back.columns)
['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:
readings.to_csv("readings_clean.csv", index=False)
back = pd.read_csv("readings_clean.csv")
print(list(back.columns))
print("rows:", len(back))
['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:
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())
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:
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:
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())
saved: readings_clean.csv True
And the mistake it's there for:
save_clean(readings, "data/raw")
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.