Writing Data: to_csv & to_json

After cleaning the IPL data or calculating the Orange Cap table, you need to save the result — to share with a teammate, load into Excel, or send from a backend API. Every read_* function has a matching to_* method on the DataFrame. The two you'll use most are to_csv (for files and spreadsheets) and to_json (for web APIs and JavaScript frontends).

Definition

DataFrame.to_csv() and DataFrame.to_json() are export methods that convert a DataFrame into CSV or JSON text. Given a path (or file-like object) they write that text to it and return None; called without a path they return the text as a string.

• Export method — called on the DataFrame itself: df.to_csv(...), not pd.to_csv(...).
• Path or file-like object — a filename like "orange_cap.csv", or an io.StringIO buffer (which is how we demonstrate writing in the browser).
• Without a path — handy for quickly printing what the file would contain.

Syntax

df.to_csv(path_or_buf=None, sep=",", index=True, header=True, columns=None,
          na_rep="", float_format=None, mode="w", encoding="utf-8")

df.to_json(path_or_buf=None, orient=None, lines=False, indent=None, date_format=None)

Example — to_csv and the index=False rule

import pandas as pd
import io

df = pd.DataFrame({
    "player": ["Virat Kohli", "Travis Head", "Sunil Narine"],
    "team": ["RCB", "SRH", "KKR"],
    "runs": [741, 567, 488],
    "strike_rate": [154.7, 191.55, 180.74],
})
print(df.to_csv())             # default: index written too
print(df.to_csv(index=False))  # usually what you want

,player,team,runs,strike_rate
0,Virat Kohli,RCB,741,154.7
1,Travis Head,SRH,567,191.55
2,Sunil Narine,KKR,488,180.74

player,team,runs,strike_rate
Virat Kohli,RCB,741,154.7
Travis Head,SRH,567,191.55
Sunil Narine,KKR,488,180.74

How it works: by default the row index (0, 1, 2) is written as an unnamed first column — notice the line starts with an empty name: ,player,.... When you read that file back, the index returns as a junk column:

back = pd.read_csv(io.StringIO(df.to_csv()))
print(back)

   Unnamed: 0        player team  runs  strike_rate
0           0   Virat Kohli  RCB   741       154.70
1           1   Travis Head  SRH   567       191.55
2           2  Sunil Narine  KKR   488       180.74

That Unnamed: 0 column is the most famous pandas annoyance. Rule: use index=False unless your index carries real information (like player names after set_index). If you receive such a file, pd.read_csv(path, index_col=0) puts the column back as the index.

Example — writing into a file-like object

On your computer you would write df.to_csv("orange_cap.csv", index=False) and a file appears next to your notebook. In the browser we write into an io.StringIO "file" and then read its contents:

buffer = io.StringIO()
df.to_csv(buffer, index=False)
print(buffer.getvalue())

player,team,runs,strike_rate
Virat Kohli,RCB,741,154.7
Travis Head,SRH,567,191.55
Sunil Narine,KKR,488,180.74

Example — more to_csv options

print(df.to_csv(index=False, sep="|", columns=["player", "runs"]))
print(df.to_csv(index=False, float_format="%.1f"))

missing = pd.DataFrame({"player": ["MS Dhoni", "Jasprit Bumrah"], "runs": [161, None]})
print(missing.to_csv(index=False, na_rep="DNB"))

player|runs
Virat Kohli|741
Travis Head|567
Sunil Narine|488

player,team,runs,strike_rate
Virat Kohli,RCB,741,154.7
Travis Head,SRH,567,191.6
Sunil Narine,KKR,488,180.7

player,runs
MS Dhoni,161.0
Jasprit Bumrah,DNB

How it works: columns picks (and orders) the columns, sep changes the separator, float_format="%.1f" rounds floats to one decimal only in the file (the DataFrame is unchanged), and na_rep writes a marker instead of an empty cell. mode="a" with header=False appends new rows to an existing file — useful for adding each new match result to a season log.

Example — to_json and its orients

The orient decides the JSON shape. The same two-row table, six ways:

df = pd.DataFrame({"player": ["Virat Kohli", "Travis Head"],
                   "team": ["RCB", "SRH"], "runs": [741, 567]})
for o in ["columns", "records", "index", "split", "values"]:
    print(o, "->", df.to_json(orient=o))

columns -> {"player":{"0":"Virat Kohli","1":"Travis Head"},"team":{"0":"RCB","1":"SRH"},"runs":{"0":741,"1":567}}
records -> [{"player":"Virat Kohli","team":"RCB","runs":741},{"player":"Travis Head","team":"SRH","runs":567}]
index -> {"0":{"player":"Virat Kohli","team":"RCB","runs":741},"1":{"player":"Travis Head","team":"SRH","runs":567}}
split -> {"columns":["player","team","runs"],"index":[0,1],"data":[["Virat Kohli","RCB",741],["Travis Head","SRH",567]]}
values -> [["Virat Kohli","RCB",741],["Travis Head","SRH",567]]

Add indent=2 for pretty, human-readable JSON, and orient="records", lines=True to write JSON Lines. When the DataFrame has dates, pass date_format="iso" — the default writes dates as epoch milliseconds (e.g. 1716681600000), and pandas 3 warns that this default is deprecated.

Common mistakes

• ❌ df.to_csv("out.csv") and then an Unnamed: 0 column on re-reading. ✅ df.to_csv("out.csv", index=False).
• ❌ pd.to_csv(df, ...). ✅ It is a DataFrame method: df.to_csv(...).
• ❌ x = df.to_csv("out.csv") and expecting text in x. ✅ With a path it writes the file and returns None; call it without a path to get a string.
• ❌ Sending orient="columns" JSON to a frontend that expects a list of rows. ✅ Use orient="records".
• ❌ Dates in JSON look like 1716681600000. ✅ Pass date_format="iso".

Interview tip

Q: Why do you often see an Unnamed: 0 column after reading a CSV? A: The file was written with to_csv() using the default index=True, so the old index was saved as an unnamed first column. Either write with index=False, or read with index_col=0 to turn that column back into the index.