Exporting and Reporting Results
Analysis is only useful once it reaches someone or something that can act on it, and pandas provides a family of to_* methods on DataFrames that mirror its read_* input functions. These writers cover flat files (to_csv, to_json), spreadsheet formats (to_excel), columnar formats optimized for downstream analytics (to_parquet, to_feather), and even direct database writes (to_sql). Choosing the right export format is a design decision: CSV is universally readable but loses dtype information and can be slow for large files, Parquet preserves schema and compresses well but requires a library like pyarrow, and Excel is often the only format a non-technical stakeholder will actually open.
Cricket analogy: Publishing a season's scorecards as to_csv works for any spreadsheet app but drops the fact that 'runs' was an int, to_parquet keeps that schema and compresses well but needs pyarrow installed, and to_excel is what the club secretary will actually open.
Writing CSV, Excel, and JSON
to_csv() is the most common export and takes index=False in most reporting contexts to avoid writing pandas' auto-generated row numbers as an extra, meaningless column. to_excel() requires an engine such as openpyxl and supports writing multiple DataFrames to different sheets of the same workbook via pd.ExcelWriter used as a context manager. to_json() supports several orient values — 'records' (a list of row-dicts, the most common for APIs), 'columns', 'index', and 'split' — each producing a structurally different JSON layout, so the orient must match what the downstream consumer expects.
Cricket analogy: to_csv(index=False) drops the meaningless row numbers from a scorecard export; to_excel with pd.ExcelWriter puts batting and bowling stats on separate sheets in one workbook; and to_json(orient='records') turns each innings into a row-dict, the format a scoring API expects.
import pandas as pd
report = pd.DataFrame({
'region': ['North', 'South', 'East', 'West'],
'q1_revenue': [42000, 38500, 51200, 29800],
'q2_revenue': [45300, 40100, 49800, 33200]
})
report['growth_pct'] = ((report['q2_revenue'] - report['q1_revenue']) / report['q1_revenue'] * 100).round(2)
# Plain CSV without the pandas row-index column
report.to_csv('regional_report.csv', index=False)
# Multi-sheet Excel workbook
with pd.ExcelWriter('regional_report.xlsx', engine='openpyxl') as writer:
report.to_excel(writer, sheet_name='Summary', index=False)
report[report['growth_pct'] > 0].to_excel(writer, sheet_name='Positive Growth', index=False)
# JSON as a list of row records, the typical API-friendly shape
# [{'region': 'North', 'q1_revenue': 42000, ...}, ...]
report.to_json('regional_report.json', orient='records', indent=2)Formatting for Human-Readable Reports
When output is destined for a human reader rather than another program, presentation matters as much as content. DataFrame.style provides a fluent API for HTML-based conditional formatting — highlighting negative values, adding percentage or currency formatting via .format(), and drawing in-cell bar charts with .bar() — which renders nicely in Jupyter and can be exported to a standalone HTML file with .to_html(). For plain-text summaries, to_markdown() (requires the tabulate package) produces GitHub-flavored Markdown tables that drop cleanly into README files or Slack messages, while to_string() gives a full, non-truncated console-friendly rendering useful for quick terminal reports.
Cricket analogy: DataFrame.style.bar() draws in-cell bars showing each batter's strike rate directly in a Jupyter notebook for the coach; to_markdown() drops a clean scorecard table into a team Slack channel, and to_string() prints the full, untruncated table in a terminal report.
Parquet is worth learning even if your stakeholders only ever see Excel: because it stores column dtypes, compression, and partitioning metadata, df.to_parquet('data.parquet') followed by pd.read_parquet('data.parquet') round-trips a DataFrame exactly, whereas a CSV round-trip can silently turn integers into floats or dates into strings.
Forgetting index=False on to_csv() or to_excel() is one of the most common reporting mistakes — it silently adds an unlabeled first column of row numbers that confuses anyone who opens the file expecting only your named columns, and can shift every subsequent column reference by one in downstream scripts.
Automating Recurring Reports
For reports that run on a schedule, it is good practice to parameterize the output path with the run date (e.g. f'report_{pd.Timestamp.today():%Y-%m-%d}.csv'), write to a temporary file and rename on success to avoid partial files if the job crashes mid-write, and validate row counts or key aggregates before overwriting a previous report. Combining to_csv() or to_sql() with a workflow scheduler (cron, Airflow, or a simple if __name__ == '__main__': script triggered by a task runner) turns a one-off notebook export into a dependable, repeatable pipeline step.
Cricket analogy: A weekly scorecard report named f'scorecard_{date}.csv' avoids overwriting last week's file, gets written to a temp file first and renamed only on success so a crash mid-write doesn't corrupt Sunday's report, and cron triggers the whole pipeline every Monday morning automatically.
- Pandas'
to_*writer methods mirror itsread_*readers: CSV, Excel, JSON, Parquet, SQL, and more. - Always pass
index=Falsewhen the auto-generated row index isn't meaningful to the consumer. pd.ExcelWriteras a context manager lets you write multiple sheets into one workbook.- JSON's
orientparameter changes the output structure —'records'is the most API-friendly. - Parquet preserves dtypes and compresses well, making it superior to CSV for pipeline hand-offs.
DataFrame.styleandto_markdown()are the go-to tools for human-facing, formatted reports.
Practice what you learned
1. What is the effect of omitting `index=False` when calling `df.to_csv('out.csv')`?
2. Which `to_json()` orient value produces a list of row-level dictionaries, the shape most commonly expected by web APIs?
3. What is a key advantage of Parquet over CSV for exporting DataFrames?
4. How do you write two different DataFrames to two separate sheets in the same Excel workbook?
5. Which method is best suited for producing a GitHub-flavored Markdown table from a DataFrame?
Was this page helpful?
You May Also Like
Reading and Writing Data
Learn pandas' file I/O functions — read_csv, read_excel, read_json, to_csv, and to_parquet — plus the key parameters that control parsing, dtypes, and output format.
Plotting with Pandas
Learn how pandas wraps Matplotlib to give Series and DataFrames a fast, built-in `.plot()` interface for quick exploratory visualizations without leaving your analysis workflow.
Pivot Tables
How pandas' pivot_table reshapes long-format data into a summarized cross-tabulation, aggregating values across row and column groupings similar to a spreadsheet pivot table.
GroupBy Basics
Understand pandas' split-apply-combine model via groupby(), including grouping keys, selecting columns, and iterating over groups.