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

By

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.

Key terms

Related