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("cities.csv", "rb").read())
print("utf-8-sig:", open("cities_excel.csv", "rb").read())

Output:

utf-8:     b'city\r\nSan Jos\xc3\xa9\r\nAustin\r\n'
utf-8-sig: b'\xef\xbb\xbfcity\r\nSan Jos\xc3\xa9\r\nAustin\r\n'

The accented e is stored as two bytes, \xc3\xa9, in both files. utf-8-sig adds a byte-order mark, \xef\xbb\xbf, before the first character.

That mark is how Excel on Windows usually recognizes a UTF-8 file. Without it, a program that assumes the older Windows code page reads those two bytes as two separate symbols, and San Jose comes out garbled.

So if the CSV is going to someone who’ll double-click it into Excel, use encoding="utf-8-sig". For files read by code, plain UTF-8 is the better default.

Save the CSV to a folder path

pandas won’t create folders for you, which surprises people the first time:

import shutil
from pathlib import Path
import pandas as pd

shutil.rmtree("exports", ignore_errors=True)   # start without the folder

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
try:
    sales.to_csv("exports/sales.csv", index=False)
except OSError as err:
    print("OSError:", err)

out = Path("exports") / "2026-09" / "sales.csv"
out.parent.mkdir(parents=True, exist_ok=True)
sales.to_csv(out, index=False)
print("saved:", out, "| exists:", out.exists())

Output:

OSError: Cannot save file into a non-existent directory: 'exports'
saved: exports\2026-09\sales.csv | exists: True

The error message is clear once you’ve seen it: Cannot save file into a non-existent directory. Create the folder with pathlib, then save.

parents=True builds every missing level, and exist_ok=True makes the line safe to run again. pandas accepts the Path object directly.

On Windows, write paths with forward slashes or a raw string. A plain "C:\new\sales.csv" turns \n into a line break.

Why does my CSV have blank rows on Windows?

This one only happens on Windows, and only when you pass pandas a file you opened yourself:

import csv
import pandas as pd

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

with open("bad.csv", "w") as f:              # no newline=""
    sales.to_csv(f, index=False)
with open("good.csv", "w", newline="") as f:
    sales.to_csv(f, index=False)

for name in ("bad.csv", "good.csv"):
    print(name, open(name, "rb").read()[:22])
    with open(name, newline="") as f:
        print("   csv.reader ->", list(csv.reader(f)))

Output:

bad.csv b'state,orders\r\r\nCalifor'
   csv.reader -> [['state', 'orders'], [], ['California', '1200'], [], ['Texas', '950'], []]
good.csv b'state,orders\r\nCaliforn'
   csv.reader -> [['state', 'orders'], ['California', '1200'], ['Texas', '950']]

pandas already writes \r\n line endings on Windows. When you hand it a file opened in text mode, Python translates the \n into \r\n again, and every line ends in \r\r\n.

Python’s csv module reads that as an empty row between every real one, and spreadsheet programs often do the same. Opening the file with newline="" stops the second translation.

Simpler still, pass pandas the file name and let it open the file. The Python new line guide explains the same doubling with os.linesep.

On macOS and Linux, pandas writes \n and nothing gets translated, so this bug doesn’t appear there. It shows up when a Windows teammate runs the same script.

Compress, quote and other to_csv options

A handful of smaller options round things out:

import pandas as pd

sales = pd.DataFrame({
    "state": ["California", "Texas", "New York"],
    "sales_usd": [125000.50, 98000.25, 110500.00],
    "orders": [1200, 950, 1010],
})
text = sales.to_csv(index=False)                    # no path: returns the CSV text
print(type(text).__name__, repr(text[:40]))
print(repr(sales.to_csv(index=False, lineterminator="\n")[:40]))
print()

sales.to_csv("sales.csv.gz", index=False)          # compression from the extension
print(".gz file starts with", open("sales.csv.gz", "rb").read(2), "| read back:", pd.read_csv("sales.csv.gz").shape)
print()

names = pd.DataFrame({"name": ["Smith, John", 'Ava "AJ" Lee'], "city": ["Austin", "Denver"]})
names.to_csv("names.csv", index=False)
print(open("names.csv").read())

try:
    sales.to_csv("x.csv", line_terminator="\n")
except TypeError as err:
    print("TypeError:", err)

Output:

str 'state,sales_usd,orders\r\nCalifornia,12500'
'state,sales_usd,orders\nCalifornia,125000'

.gz file starts with b'\x1f\x8b' | read back: (3, 3)

name,city
"Smith, John",Austin
"Ava ""AJ"" Lee",Denver

TypeError: NDFrame.to_csv() got an unexpected keyword argument 'line_terminator'. Did you mean 'lineterminator'?

With no path, to_csv() returns the CSV as a string, which is useful for APIs and tests. On Windows that string uses \r\n line endings too, which surprised me; pass lineterminator="\n" if you need plain \n.

A .gz or .zip extension compresses the file automatically, and read_csv() reads it back the same way.

Values containing a comma get wrapped in quotes, and a quote inside a value is doubled. That’s standard CSV, and every decent reader handles it.

The last line is for anyone following an older tutorial. The line_terminator argument was renamed to lineterminator, and current pandas rejects the old name with a helpful hint.

If you’re working with CSV files from other sources, these are worth a look:

• Write a list to CSV in Python
• Write a dictionary to CSV
• Read large CSV files in Python
• Add a row to a pandas DataFrame

Frequently asked questions