Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 64 ยท Getting Started

Writing and Formatting Data

Export a pandas DataFrame with to_csv, to_json, to_dict, to_string and to_markdown, control formats, and check that data survives a round trip.

After you have cleaned and analyzed data, you usually need to hand it on: save a CSV for a colleague, send JSON to a web page, print a table in a report. pandas has a to_* method for each format, and they mirror the read_* functions you already know.

As in the previous lesson, we write to in-memory buffers and to plain strings instead of files, because the browser has no file system. When a method is called without a destination, it returns the result as a string, which is perfect for learning. On your own computer, pass a path instead: df.to_csv("out.csv", index=False).

flowchart LR
    DF["DataFrame"] --> A["to_csv<br/>text table"]
    DF --> B["to_json<br/>web data"]
    DF --> C["to_dict<br/>Python objects"]
    DF --> D["to_string / to_markdown<br/>reports"]

One DataFrame, many output formats. Each one is a method call.

to_csv

With no argument, to_csv() returns the CSV text. The most important option is index=False. Without it, the row index is written as an extra unnamed first column, and your next reader sees a mysterious column:

Python to_csv and index=False

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Other useful options include sep=";" for semicolon files, columns=[...] to write only some columns in a chosen order, float_format="%.2f" to fix decimals, na_rep="n/a" to choose how missing values appear, and header=False to omit the header.

Python More to_csv options

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the value hot, dry contains a comma, so pandas wrapped it in quotes automatically in the second output. You never have to escape anything yourself.

Writing to a buffer

When you want to behave like a file, write into an io.StringIO object. This is exactly what happens when you pass a real path, just without touching the disk:

Python Writing into a buffer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

We set lineterminator="\n" so the line ending is the same on every operating system; by default pandas uses the one of the machine it runs on.

to_json

JSON is the language of web APIs. How the table is laid out depends on the orient argument. The three you meet most are:

  • "records": a list with one object per row, the most common API shape.
  • "columns" (default): {column: {index: value}}.
  • "split": separate lists for columns, index and data, compact and lossless.
Python to_json orients

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

JSON has no date type. pandas writes dates as numbers (milliseconds since 1970) by default; pass date_format="iso" to get readable ISO 8601 strings. The next lesson, JSON and Nested Data, covers reading JSON in depth.

to_dict and to_numpy

If you want Python objects, not text, use to_dict. orient="records" gives a list of row dictionaries, ready to be passed to a template or an API client. to_numpy() gives the underlying array:

Python to_dict and to_numpy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last pattern in the middle (set_index("name")["age"].to_dict()) is a quick way to build a lookup dictionary from two columns.

to_string and to_markdown

For human-readable text, to_string() returns the table exactly as printed, with options to hide the index or header, set column widths and format numbers per column:

Python to_string with formatters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

to_markdown() renders a table in Markdown syntax for README files and chat messages. It needs the optional tabulate package, which is not installed in the browser environment, so this snippet is read-only here. On your computer, pip install tabulate makes it work:

Python
print(df.to_markdown(index=False))

If the package is missing you get an ImportError, which is easy to fix. The browser-friendly alternative is to build the text yourself, as the next example does with plain string joining.

Python A tiny Markdown table without tabulate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Round trips: does the data survive?

A round trip writes data out and reads it back. A good habit is to check that you get the same table. CSV is simple and universal but loses type information: dates, booleans and categories all come back as text or need options. Writing with index=False and reading with the same assumptions usually works for plain values:

Python CSV and JSON round trips

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first read leaves day as text, and parse_dates restores it. If dtypes matter in your pipeline, reapply them explicitly after reading.

Warning: when you write a CSV with the default index=True and read it back, you get an extra column named Unnamed: 0. Either write with index=False, or read with index_col=0.

Formats the browser cannot write

pandas also writes Excel (to_excel), Parquet (to_parquet), Feather, HDF5, SQL tables (to_sql) and more. They need extra packages (openpyxl, pyarrow, a database driver) and, usually, real files, so they are read-only in this course:

Python
df.to_excel("report.xlsx", sheet_name="Summary", index=False)   # needs openpyxl
df.to_parquet("data.parquet")                                   # needs pyarrow
df.to_sql("sales", connection, if_exists="replace", index=False)

Parquet deserves special mention: it is a compact binary format that stores dtypes, so it round-trips perfectly and loads far faster than CSV. We return to it in When Data Gets Big.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does df.to_csv() return when you give it no path?

    Without a destination, to_csv returns the text. With a path or buffer it writes there and returns None.
  2. 2. Why do people usually pass index=False to to_csv?

    By default the index is written as an unnamed column. Reading that file back creates an "Unnamed: 0" column unless you handle it.
  3. 3. Which to_json orient gives a list with one object per row?

    orient="records" produces [{...}, {...}], the shape most web APIs use. The default "columns" nests values under column names.
  4. 4. Why is df.to_markdown() read-only in the browser examples?

    to_markdown relies on tabulate. Without it you get an ImportError, so we show it as read-only and build the text by hand.
  5. 5. After a CSV round trip, a datetime column comes back as text. How do you restore it?

    CSV stores only text. Telling read_csv which columns are dates, or converting them yourself, restores the datetime dtype.

Summary

  • Every read_* function has a to_* partner: to_csv, to_json, to_dict, to_string, to_markdown, to_excel, to_parquet, to_sql.
  • With no destination, text-producing methods return a string; with a path or buffer they write there.
  • Use index=False with to_csv, and orient="records" for list-of-objects JSON.
  • to_dict(orient="records") and to_numpy() give Python objects and arrays.
  • to_string() supports per-column formatters; to_markdown() needs tabulate.
  • Check round trips: CSV loses types, so reapply dtypes and dates when reading back.
  • Excel, Parquet and SQL writers need extra packages and files, so they are read-only here.

Next: JSON and Nested Data, for data that is not a flat table.