Exporting and Sharing Analysis Results: Writing CSV, Excel, and JSON Files from pandas

Introduction

You've done the hard work. You loaded a messy sales dataset, cleaned the missing values and fixed the data types, ran a groupby aggregation to summarize revenue by region, and filtered it down to the top performers. Now your manager is asking for the results, your colleague needs the file to feed into a dashboard tool, and the finance team wants it in a specific format.

This is the moment analysis becomes communication. Getting data out of pandas and into a file that other people — or other systems — can actually use is a critical skill that gets glossed over in most tutorials. It sounds simple ("just save it"), but the details matter: encoding problems that corrupt special characters, Excel files that open with a frozen header row and no formatting, JSON that a downstream API chokes on because the structure is wrong. These are real problems that happen on real projects.

By the end of this lesson, you'll know exactly how to export DataFrames to CSV, Excel, and JSON, how to control the format so your output looks professional, and how to avoid the common gotchas that trip people up. You'll be able to hand off your analysis with confidence.

What you'll learn:

• How to write a DataFrame to a CSV file with to_csv(), including controlling delimiters, encoding, and the index
• How to write single and multi-sheet Excel workbooks with to_excel() and ExcelWriter
• How to serialize data to JSON with to_json() and choose the right orientation for your use case
• How to export filtered subsets and aggregated summaries — not just raw DataFrames
• How to avoid the most common export mistakes (encoding errors, bloated files, broken JSON)

Prerequisites

You should be comfortable with the basics of pandas DataFrames — loading data, selecting columns, and running simple aggregations. If you're new to pandas, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data before working through this lesson. You should also have Python and a working environment set up; if not, see Setting Up Python for Data Analysis.

You'll need pandas installed (pip install pandas), and for Excel output you'll also need openpyxl (pip install openpyxl).

The Dataset We'll Use

Throughout this lesson, we'll work with a realistic sales dataset. Let's build it from scratch so you can follow along immediately without needing an external file:

import pandas as pd

data = {
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
    "region": ["Northeast", "Southeast", "Midwest", "West", "Northeast", "West", "Midwest", "Southeast"],
    "salesperson": ["Alice", "Bob", "Carol", "David", "Alice", "David", "Carol", "Bob"],
    "product": ["Widget A", "Widget B", "Widget A", "Widget C", "Widget C", "Widget A", "Widget B", "Widget C"],
    "units_sold": [120, 85, 200, 95, 140, 60, 175, 110],
    "unit_price": [29.99, 49.99, 29.99, 74.99, 74.99, 29.99, 49.99, 74.99],
    "order_date": pd.to_datetime([
        "2024-01-15", "2024-01-18", "2024-02-03",
        "2024-02-14", "2024-03-01", "2024-03-10",
        "2024-03-22", "2024-04-05"
    ])
}

df = pd.DataFrame(data)
df["revenue"] = df["units_sold"] * df["unit_price"]
print(df.head())

This gives you a clean DataFrame with eight orders, regional data, and calculated revenue — exactly the kind of summary table you'd generate in a real analysis.

Exporting to CSV with `to_csv()`

CSV (Comma-Separated Values) is the universal handshake format of data. Almost every tool on the planet can open a CSV: Excel, Google Sheets, SQL import wizards, BI tools, and APIs. When in doubt about format, CSV is usually the right choice.

The basic call is simple:

df.to_csv("sales_data.csv")

Run this and you'll find a file in your current working directory. Open it and you'll immediately notice something: there's a column on the left with 0, 1, 2, 3... That's the pandas index being written to the file. Unless your index contains meaningful data (like a custom row label), this is almost always unwanted noise.

Always suppress the index unless it means something:

df.to_csv("sales_data.csv", index=False)

This one parameter change makes your CSV cleaner and prevents confusion when someone else opens it and wonders what that mystery column is.

Controlling the Delimiter

"CSV" literally means comma-separated, but sometimes you need a different separator. European locales often use semicolons because commas are used as decimal separators. Some systems expect tab-separated files (TSV). Use the sep parameter:

# Semicolon-delimited (common in European Excel installs)
df.to_csv("sales_data_eu.csv", index=False, sep=";")

# Tab-separated
df.to_csv("sales_data.tsv", index=False, sep="\t")

Handling Encoding

Encoding is how text characters are stored as bytes. The default in pandas is utf-8, which handles the vast majority of characters correctly. But if your data contains special characters — accented letters, currency symbols, non-Latin scripts — and you're sending the file to someone using Excel on Windows, you may need utf-8-sig instead. That variant adds a Byte Order Mark (BOM) that tells Excel "this file is UTF-8" so it renders characters correctly:

df.to_csv("sales_data.csv", index=False, encoding="utf-8-sig")

Exporting a Filtered Subset

You rarely export an entire raw DataFrame. More often you're exporting a cleaned, filtered, or aggregated result. This works exactly the same way — you just export the derived DataFrame:

# Export only Northeast orders over $3,000 revenue
northeast_top = df[(df["region"] == "Northeast") & (df["revenue"] > 3000)]
northeast_top.to_csv("northeast_top_orders.csv", index=False)

# Export a regional summary
regional_summary = df.groupby("region")["revenue"].agg(
    total_revenue="sum",
    order_count="count",
    avg_revenue="mean"
).reset_index()

regional_summary.to_csv("regional_summary.csv", index=False)

Notice the reset_index() call before exporting the grouped result. After a groupby, the group keys become the index. Calling reset_index() promotes them back to regular columns, which is almost always what you want in an export. See Grouping and Aggregating in pandas for more on this pattern.

Other Useful `to_csv()` Parameters

Parameter	What it does	Example
columns	Export only specific columns	columns=["region", "revenue"]
float_format	Control decimal places	float_format="%.2f"
date_format	Format datetime columns	date_format="%Y-%m-%d"
na_rep	What to write for NaN values	na_rep="N/A"
chunksize	Write large files in chunks	chunksize=10000

# A polished export: specific columns, formatted numbers, explicit date format
df.to_csv(
    "sales_export.csv",
    index=False,
    columns=["order_date", "region", "salesperson", "product", "revenue"],
    float_format="%.2f",
    date_format="%Y-%m-%d",
    encoding="utf-8-sig"
)

Exporting to Excel with `to_excel()`

Excel files are often non-negotiable in business environments. Managers expect them, finance teams live in them, and some workflows simply require .xlsx. pandas makes this straightforward, though with a few more moving parts than CSV.

First, make sure openpyxl is installed — it's the engine pandas uses to write .xlsx files:

pip install openpyxl

The simplest export:

df.to_excel("sales_data.xlsx", index=False)

Just like with CSV, index=False is almost always what you want.

You can also name the sheet:

df.to_excel("sales_data.xlsx", index=False, sheet_name="Orders")

Writing Multiple Sheets with ExcelWriter

Here's where Excel export gets genuinely powerful. In a real reporting workflow, you often want one workbook with multiple sheets — a summary tab, a detail tab, maybe a filtered subset. You do this with pandas' ExcelWriter context manager:

# Build the sheets we want to export
regional_summary = df.groupby("region")["revenue"].agg(
    total_revenue="sum",
    order_count="count",
    avg_revenue="mean"
).reset_index()

salesperson_summary = df.groupby("salesperson")["revenue"].agg(
    total_revenue="sum",
    order_count="count"
).reset_index().sort_values("total_revenue", ascending=False)

product_summary = df.groupby("product")["revenue"].agg(
    total_revenue="sum",
    units_sold="sum"
).reset_index()

# Write all three to a single workbook
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="All Orders", index=False)
    regional_summary.to_excel(writer, sheet_name="By Region", index=False)
    salesperson_summary.to_excel(writer, sheet_name="By Salesperson", index=False)
    product_summary.to_excel(writer, sheet_name="By Product", index=False)

print("Workbook written successfully.")

The with statement is important here. It opens the file, lets you write as many sheets as you need, then closes and saves the file cleanly when the block ends. If you forget the with block and just call writer.save() manually, you risk writing a corrupted file if an error occurs partway through.

Controlling the Starting Row and Column

Sometimes you want to leave space for a title row above your data, or you're writing to a sheet that already has content in some cells. The startrow and startcol parameters let you control where the data lands:

with pd.ExcelWriter("sales_report_with_title.xlsx", engine="openpyxl") as writer:
    # Write the data starting at row 2 (0-indexed), leaving row 0 for a title
    df.to_excel(writer, sheet_name="Orders", index=False, startrow=1)
    
    # Add a title manually using openpyxl
    worksheet = writer.sheets["Orders"]
    worksheet.cell(row=1, column=1, value="Q1-Q2 2024 Sales Data")

For much more sophisticated formatting — column widths, color headers, conditional formatting — see Automating Excel Reports with pandas and openpyxl, which covers this in depth.

Exporting to JSON with `to_json()`

JSON (JavaScript Object Notation) is the lingua franca of web APIs and modern data pipelines. If you're sending data to a web service, saving configuration alongside results, or working with a system that consumes structured data programmatically, JSON is your format. It's less human-readable than CSV in a spreadsheet, but it's the native format of the internet.

The basic call:

df.to_json("sales_data.json")

But the output will probably surprise you. Open the file and you'll see something like this (truncated):

{"order_id":{"0":1001,"1":1002},"region":{"0":"Northeast","1":"Southeast"},...}

This is the default orient="columns" format — a dictionary of columns, each containing a dictionary of index-to-value pairs. It's compact and round-trips back to pandas perfectly, but it's not what most APIs or downstream tools expect.

Choosing the Right Orientation

The orient parameter is the most important choice you'll make when exporting JSON. Here are the options that matter most in practice:

orient="records" — the most useful for most purposes

df.to_json("sales_data.json", orient="records", indent=2)

Output:

[
  {
    "order_id": 1001,
    "region": "Northeast",
    "salesperson": "Alice",
    "product": "Widget A",
    "units_sold": 120,
    "unit_price": 29.99,
    "revenue": 3598.8
  },
  {
    "order_id": 1002,
    ...
  }
]

This is an array of objects — one object per row — which is exactly what REST APIs, JavaScript frontends, and most JSON consumers expect. Use this as your default unless you have a specific reason not to.

orient="index" — useful when row identity matters

df.set_index("order_id").to_json("sales_data_indexed.json", orient="index", indent=2)

Produces a dictionary keyed by the index value. Useful when you want to look up records by ID.

orient="split" — compact, round-trips cleanly

df.to_json("sales_data_split.json", orient="split", indent=2)

Produces a structure with separate "columns", "index", and "data" keys. Slightly more compact, and pd.read_json("file.json", orient="split") will reconstruct the DataFrame perfectly.

Handling Dates in JSON

JSON has no native date type. When pandas encounters a datetime column, it converts it to milliseconds since the Unix epoch by default — a large integer that means nothing to a human reader:

"order_date": 1705276800000

Fix this by using the date_format and date_unit parameters, or by converting the datetime column to a string before exporting:

# Option 1: Convert datetime to ISO string before exporting
df_export = df.copy()
df_export["order_date"] = df_export["order_date"].dt.strftime("%Y-%m-%d")
df_export.to_json("sales_data.json", orient="records", indent=2)

# Option 2: Use date_format parameter
df.to_json("sales_data.json", orient="records", indent=2, date_format="iso")

Option 1 gives you the most control over the string format. Option 2 is more concise but produces full ISO 8601 timestamps like "2024-01-15T00:00:00.000Z" rather than just the date portion.

Exporting Nested JSON

Sometimes an API expects a nested structure with metadata wrapping your records. You can build this using Python's built-in json module alongside pandas:

import json

records = df.copy()
records["order_date"] = records["order_date"].dt.strftime("%Y-%m-%d")

output = {
    "metadata": {
        "exported_at": "2024-04-10",
        "record_count": len(records),
        "source": "Q1-Q2 Sales Analysis"
    },
    "data": records.to_dict(orient="records")
}

with open("sales_data_wrapped.json", "w", encoding="utf-8") as f:
    json.dump(output, f, indent=2)

to_dict(orient="records") converts the DataFrame to a Python list of dictionaries, which json.dump then serializes. This pattern gives you full control over the JSON structure.

Hands-On Exercise

Work through this exercise to solidify what you've learned. Use the df DataFrame we built at the start of the lesson.

Part 1: CSV Export

1. Export the full DataFrame to all_orders.csv without the index and with revenue formatted to 2 decimal places.
2. Filter the DataFrame to orders where units_sold > 100 and export that subset to high_volume_orders.csv.
3. Create a salesperson summary using groupby (total revenue, total units sold, number of orders) and export it to salesperson_summary.csv, sorted by total revenue descending.

Part 2: Excel Export

4. Create a multi-sheet workbook called q1_q2_report.xlsx with three sheets:
   • "All Orders": the full DataFrame
   • "Regional Summary": revenue totals and order counts by region
   • "Top Performers": orders where revenue exceeds 5000

Part 3: JSON Export

5. Export the full DataFrame to sales_records.json using orient="records" with indent=2, with the order_date formatted as "YYYY-MM-DD" strings.
6. Export just the regional summary (from Part 1, step 3) to regional_summary.json.

Expected outcome for Part 1, Step 3:

After groupby and sorting, your CSV should have four columns — salesperson, total_revenue, total_units_sold, order_count — with Alice at the top (highest revenue).

Common Mistakes & Troubleshooting

"ModuleNotFoundError: No module named 'openpyxl'"

You need to install openpyxl separately: pip install openpyxl. pandas doesn't include Excel writing capability by default. For reading .xlsx files back in, you need the same library.

The exported CSV has a column called "Unnamed: 0"

This happens when you exported with the default index=True and then re-imported the file. The index got written as a column with no name, and pandas named it "Unnamed: 0" on import. Fix the export with index=False. If you're working with a file someone else created that has this problem, use pd.read_csv("file.csv", index_col=0) to tell pandas the first column is the index.

Special characters are garbled when opened in Excel

Use encoding="utf-8-sig" in your to_csv() call. The BOM marker tells Excel to interpret the file as UTF-8. This is specifically a Windows Excel issue — Excel on Mac and web typically handles UTF-8 without the BOM.

JSON export produces epoch milliseconds for dates

Convert datetime columns to strings before exporting, or use date_format="iso". See the datetime section above.

ExcelWriter creates an empty or corrupted file

Make sure you're using the with statement. If you create an ExcelWriter object without with and forget to call writer.close() (or the old writer.save()), the file won't be finalized properly.

The groupby summary doesn't export column names correctly

After a groupby with named aggregations, the group key becomes the index. Call .reset_index() before exporting so it appears as a regular column. This is easy to forget and produces a file where your category column (like "region") appears to be missing from the data.

File is saving in the wrong directory

When you call df.to_csv("file.csv"), pandas saves the file in your current working directory. In Jupyter, that's typically the folder where the notebook lives. In a script, it's wherever you ran the script from. Use an absolute path or os.path.join() to be explicit: df.to_csv("/Users/yourname/reports/file.csv").

Summary & Next Steps

You now have the full picture of getting data out of pandas and into the world. Here's what you've covered:

• CSV: Use to_csv() with index=False as your default. Control encoding with encoding="utf-8-sig" for Excel compatibility, format numbers with float_format, and choose your delimiter with sep.
• Excel: Use to_excel() for single sheets and ExcelWriter with a with block for multi-sheet workbooks. Name your sheets meaningfully. Install openpyxl first.
• JSON: Use to_json() with orient="records" and indent=2 for API-friendly output. Always handle datetime columns explicitly — convert them to strings before export.
• The meta-skill: You're rarely exporting raw data. You're exporting the result of filtering, aggregating, and reshaping. Those operations come first; the export is just the final step.

The natural next step from here is making your exports more polished. If your audience lives in Excel, Automating Excel Reports with pandas and openpyxl shows you how to add formatting, column widths, and conditional color to make workbooks that look genuinely professional — not just data dumps in a spreadsheet wrapper.

If your analysis involves dates and time-based trends, Working with Dates and Time Series in pandas covers the resampling and rolling window aggregations that produce time series outputs worth exporting. And if you're working at scale — exporting summaries from datasets with millions of rows — Handling Large Datasets in Python will keep your export pipeline from running out of memory.