Writing Data Out
Overview
Saving a result, and the index that gets written with it.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Write a table to CSV.
- Say what
to_csvwrites that you did not ask for. - Decide when the index is worth keeping.
- Write only the columns a reader needs.
Why This Matters
The file you write is the one someone else reads, including you next month. An unnamed index column read back as data is the most common way a saved result quietly grows a junk column every time it goes round the loop.
Step 1: to_csv writes the index too
By default the index becomes an unnamed first column. Reading that file back gives you a column called Unnamed: 0.
The extra column
Nothing is wrong with the data; there is simply a column of row numbers in the file that was never part of your table.
import pandas as pd
df = pd.DataFrame({"name": ["Ada"], "score": [92]})
df.to_csv("out.csv")
print(open("out.csv").read())Round-tripping makes it worse
Read it back, write it again, and you have two junk columns. This is how a file ends up with Unnamed: 0, Unnamed: 0.1 and so on.
import pandas as pd
pd.DataFrame({"name": ["Ada"]}).to_csv("out.csv")
back = pd.read_csv("out.csv")
print(list(back.columns))
back.to_csv("out2.csv")
print(list(pd.read_csv("out2.csv").columns))Mini Practice #1: See the extra column
Try It YourselfWrite this table with the default settings, read it back, and print the columns you got.
Step 2: Write what you meant to write
index=False keeps the row numbers out, and columns= picks what goes.
Without the index
When the index is just 0, 1, 2 it carries nothing and should not be written.
import pandas as pd
df = pd.DataFrame({"name": ["Ada"], "score": [92]})
df.to_csv("out.csv", index=False)
print(open("out.csv").read())When the index should be written
If the index is the label for the row — a date, a name, an id — then dropping it loses information. Keep it, and give it a name so the column has a header.
import pandas as pd
df = pd.DataFrame({"score": [92, 88]}, index=["Ada", "Grace"])
df.index.name = "name"
df.to_csv("out.csv")
print(open("out.csv").read())Only the columns that matter
A result for a reader is usually narrower than the table you worked in.
import pandas as pd
df = pd.DataFrame({"name": ["Ada"], "score": [92], "scratch": [0]})
df.to_csv("out.csv", index=False, columns=["name", "score"])
print(open("out.csv").read())Mini Practice #2: A clean round trip
Try It YourselfWrite it again without the index, read it back, and confirm the columns match what you started with.
Step 3: When to Use It
Saving a cleaned table
to_csv with index=False writes exactly the columns a reader expects.
df.to_csv("clean.csv", index=False)Handing results to someone else
A file they can open in a spreadsheet, with named columns and no internal index.
When Not to Use It
Do not write the default index for a range index. Read back in, it becomes an “Unnamed: 0” column, and one more on every round trip.
End-of-Lesson Exercises
Exercise 1: Save without the index
Write save_scores(df, path) that writes the table to path as CSV with no index column, then returns the first line of the file.
index=False is the argument that keeps the row numbers out. Reading the first line back is what proves it worked.
Exercise 2: A round trip that survives
Write round_trip(df, path) that writes the table out, reads it straight back, and returns the column names of what came back. Written properly, they are the names you started with.
to_csv(path, index=False), then pd.read_csv(path), then list(back.columns). Leaving index=False off adds an Unnamed column, which the second check is looking for.