Pandas DataFrame to CSV: to_csv() Without Index, Append and More

Saving a pandas DataFrame to CSV takes one call: df.to_csv("sales.csv", index=False). The index=False part is the one people forget, and it’s the reason so many CSV files grow a mystery first column:

import pandas as pd

sales = pd.DataFrame({"state": ["California", "Texas"], "orders": [1200, 950]})
sales.to_csv("sales.csv", index=False)

I’ll also cover overwriting versus appending, number and date formats, Excel encoding and a Windows quirk that adds blank rows. The output comes from Python 3.14.7 and pandas 3.0.6.

Save a pandas DataFrame to CSV with to_csv()

Here’s the plain call with no options, and what you get when you read the file back:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
sales.to_csv("sales.csv")

print(open("sales.csv").read())
print("read back:", pd.read_csv("sales.csv").columns.tolist())

Output:

,state,sales_usd,orders
0,California,125000.5,1200
1,Texas,98000.25,950
2,New York,110500.0,1010

read back: ['Unnamed: 0', 'state', 'sales_usd', 'orders']

By default pandas writes the row index as the first column, with an empty header. The file looks fine until you load it again and find a column called Unnamed: 0.

I’ve lost count of the CSV files I’ve been sent with that column in them. It’s almost always a to_csv() call that was missing one argument.

The file lands in the current working directory, since "sales.csv" is a relative path. If you can’t find it, that’s the first thing to check.

There’s a whole article on getting the current directory in Python if a CSV keeps landing somewhere unexpected.

Write a pandas DataFrame to CSV without the index

Pass index=False and the extra column disappears:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
sales.to_csv("sales.csv", index=False)

print(open("sales.csv").read())
print("read back:", pd.read_csv("sales.csv").columns.tolist())

Output:

state,sales_usd,orders
California,125000.5,1200
Texas,98000.25,950
New York,110500.0,1010

read back: ['state', 'sales_usd', 'orders']

Now the header starts with state and the file reads back with exactly the columns you wrote. For a normal numbered DataFrame, this is almost always what you want.

Keep the index when it actually holds data, like dates or IDs you set with set_index(). Then read the file back with index_col=0 and pandas restores it instead of creating Unnamed: 0.

Reading CSV files has its own set of options, covered in reading a CSV file with pandas.

Does pandas to_csv overwrite the file?

Yes, and without any warning. Writing to the same name twice replaces the file:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
sales.to_csv("orders.csv", index=False)
sales.head(1).to_csv("orders.csv", index=False)     # same file name again
print("rows now in orders.csv:", len(pd.read_csv("orders.csv")))
print()

try:
    sales.to_csv("orders.csv", index=False, mode="x")
except FileExistsError as err:
    print("mode='x' ->", type(err).__name__, err)

Output:

rows now in orders.csv: 1

mode='x' -> FileExistsError [Errno 17] File exists: 'orders.csv'

The second call wiped the three rows and left one. That’s the default mode="w", the same as opening a file for writing in plain Python.

If overwriting would be a disaster, pass mode="x". It creates the file only if it doesn’t already exist, and raises FileExistsError otherwise.

You can also check first, which is covered in checking if a file exists in Python.

Append a DataFrame to an existing CSV file

mode="a" adds rows to the end instead of replacing the file. There’s a catch, though:

import os
import pandas as pd

september = pd.DataFrame({"date": ["2026-09-28", "2026-09-29"], "orders": [410, 385]})
october = pd.DataFrame({"date": ["2026-10-01"], "orders": [402]})

september.to_csv("daily.csv", index=False)
october.to_csv("daily.csv", mode="a", index=False)
print("mode='a' on its own:")
print(open("daily.csv").read())

september.to_csv("daily.csv", index=False)
path = "daily.csv"
october.to_csv(path, mode="a", index=False, header=not os.path.exists(path))
print("with header=not os.path.exists(path):")
print(open(path).read())

Output:

mode='a' on its own:
date,orders
2026-09-28,410
2026-09-29,385
date,orders
2026-10-01,402

with header=not os.path.exists(path):
date,orders
2026-09-28,410
2026-09-29,385
2026-10-01,402

Appending writes the column names again, so date,orders shows up halfway down the file. Anything reading it later will treat that line as data.

The fix is header=not os.path.exists(path). The header is written only when the file is new, which makes the same line safe for the first run and every run after it.

Make sure the appended DataFrame has the same columns in the same order. to_csv() doesn’t check against what’s already in the file.

Choose the columns, header and delimiter in to_csv()

A few options control what goes into the file. Here are four files written with different settings, each one read back:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
sales.to_csv("some_columns.csv", index=False, columns=["state", "orders"])
sales.to_csv("no_header.csv", index=False, header=False)
sales.to_csv("renamed.csv", index=False, header=["State", "Sales (USD)", "Orders"])
sales.to_csv("sales.tsv", index=False, sep="\t")

for name in ("some_columns.csv", "no_header.csv", "renamed.csv", "sales.tsv"):
    print(f"--- {name}")
    print(open(name).read())

Output:

--- some_columns.csv
state,orders
California,1200
Texas,950
New York,1010

--- no_header.csv
California,125000.5,1200
Texas,98000.25,950
New York,110500.0,1010

--- renamed.csv
State,Sales (USD),Orders
California,125000.5,1200
Texas,98000.25,950
New York,110500.0,1010

--- sales.tsv
state	sales_usd	orders
California	125000.5	1200
Texas	98000.25	950
New York	110500.0	1010

columns= picks and orders the columns. header=False drops the header row, and a list of names renames the columns in the file only, without touching the DataFrame.

sep="\t" writes a tab-separated file, which is handy when your data contains commas. Name it .tsv so people know what they’re opening.

Format numbers, dates and missing values in the CSV

Without formatting options, the CSV gets whatever pandas has in memory:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.5, 98000.25, None],
    "updated": pd.to_datetime(["2026-09-01", "2026-09-15", "2026-09-29"]),
})

sales.to_csv("plain.csv", index=False)
sales.to_csv("formatted.csv", index=False, float_format="%.2f", na_rep="N/A", date_format="%m/%d/%Y")

print("--- plain.csv")
print(open("plain.csv").read())
print("--- formatted.csv")
print(open("formatted.csv").read())

Output:

--- plain.csv
state,sales_usd,updated
California,125000.5,2026-09-01
Texas,98000.25,2026-09-15
New York,,2026-09-29

--- formatted.csv
state,sales_usd,updated
California,125000.50,09/01/2026
Texas,98000.25,09/15/2026
New York,N/A,09/29/2026

The first version has 125000.5 with one decimal, an empty field for the missing value and ISO dates. That’s accurate, but not always what the person opening the file expects.

float_format="%.2f" gives every number two decimal places, which suits dollar amounts. na_rep="N/A" makes missing values visible instead of blank.

date_format="%m/%d/%Y" writes US-style dates. Only the text in the file changes; the DataFrame keeps its real numbers and dates.

Read the CSV back without losing the date types

A CSV file is only text, so types don’t survive the trip on their own. Dates are the ones that bite:

import pandas as pd

sales = pd.DataFrame({
    "updated": pd.to_datetime(["2026-09-01", "2026-09-15"]),
    "orders": [1200, 950],
})
sales.to_csv("sales.csv", index=False)

back = pd.read_csv("sales.csv")
print("read_csv as-is:     ", back["updated"].dtype)

back = pd.read_csv("sales.csv", parse_dates=["updated"])
print("with parse_dates:   ", back["updated"].dtype)
print("same values again?  ", back.equals(sales))

Output:

read_csv as-is:      str
with parse_dates:    datetime64[us]
same values again?   True

Read back plainly, the date column comes back as text, a string dtype in pandas 3. Sorting and date math quietly stop working, and nothing warns you.

parse_dates=["updated"] turns it back into real dates, and the round trip then matches the original exactly. I add it to every read_csv() call that touches a date column.

If types really matter and only Python will read the file, CSV may be the wrong format. Parquet keeps the types, but that’s a topic of its own.

CSV encoding: UTF-8 or utf-8-sig for Excel?

pandas writes UTF-8 by default. The difference with utf-8-sig is three bytes at the start of the file:

import pandas as pd

cities = pd.DataFrame({"city": ["San Jos" + chr(233), "Austin"]})   # chr(233) is an accented e

cities.to_csv("cities.csv", index=False)
cities.to_csv("cities_excel.csv", index=False, encoding="utf-8-sig")

print("utf-8:    ", open(&q