to_dict, to_html & to_excel

Not every export is a file. Sometimes your Flask/FastAPI backend needs plain Python dicts to return as JSON, a web page needs an HTML table, and a team manager wants an Excel report. Munna's notebook lists the export family like this:

This topic covers to_dict, to_html and to_excel.

Definition

DataFrame.to_dict(orient) converts a DataFrame into native Python objects (dicts and lists) arranged according to orient. to_html() renders the DataFrame as an HTML <table> string, and to_excel() writes it to a sheet in an Excel workbook.

• Native Python objects — after to_dict you are out of pandas: plain dict/list you can loop over, json.dumps, or return from an API.
• orient — just like in to_json, it decides the nesting: by columns, by rows, as lists, and so on.
• HTML string — to_html() returns text containing <table>, <thead>, <tr>, <td> tags; with a path it saves a .html file you can open in a browser.

Syntax

df.to_dict(orient="dict")
df.to_html(buf=None, index=True, columns=None, classes=None, border=None)
df.to_excel(excel_writer, sheet_name="Sheet1", index=True)

Example — every to_dict orient

import pandas as pd

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

dict -> {'player': {0: 'Virat Kohli', 1: 'Travis Head'}, 'team': {0: 'RCB', 1: 'SRH'}, 'runs': {0: 741, 1: 567}}
list -> {'player': ['Virat Kohli', 'Travis Head'], 'team': ['RCB', 'SRH'], 'runs': [741, 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 -> {'index': [0, 1], 'columns': ['player', 'team', 'runs'], 'data': [['Virat Kohli', 'RCB', 741], ['Travis Head', 'SRH', 567]]}
tight -> {'index': [0, 1], 'columns': ['player', 'team', 'runs'], 'data': [['Virat Kohli', 'RCB', 741], ['Travis Head', 'SRH', 567]], 'index_names': [None], 'column_names': [None]}

How it works: notice that unlike JSON, the index keys stay real Python ints (0, 1), not strings. A very common trick is turning two columns into a lookup dict: df.set_index("player")["runs"].to_dict() gives {'Virat Kohli': 741, 'Travis Head': 567}.

Example — to_html

print(df.to_html(index=False))

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>player</th>
      <th>team</th>
      <th>runs</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Virat Kohli</td>
      <td>RCB</td>
      <td>741</td>
    </tr>
    <tr>
      <td>Travis Head</td>
      <td>SRH</td>
      <td>567</td>
    </tr>
  </tbody>
</table>

How it works: column names go into <th> cells inside <thead>, and each row becomes a <tr> with <td> cells. The table gets class="dataframe" so you can style it with CSS; add your own with classes="table table-striped" (Bootstrap). df.to_html("points_table.html") saves a file you can double-click to open in a browser. (Jupyter's pretty table display uses this same HTML behind the scenes.)

Example — to_excel (not runnable in the browser)

Writing .xlsx needs openpyxl, which the playground doesn't include. On your own computer:

path = r"C:\Users\Admin\Desktop\data\ipl_report.xlsx"
df.to_excel(path, index=False)                       # one sheet called "Sheet1"
df.to_excel(path, sheet_name="Orange Cap", index=False)

# several sheets in ONE workbook -> use ExcelWriter
with pd.ExcelWriter(path) as writer:
    batting.to_excel(writer, sheet_name="Batting", index=False)
    bowling.to_excel(writer, sheet_name="Bowling", index=False)

How it works: calling to_excel(path) twice on the same path overwrites the file each time — the second sheet replaces the first. To put several DataFrames into one workbook, open a pd.ExcelWriter in a with block and pass the writer to each to_excel call.

Common mistakes

• ❌ Looping for row in df.to_dict(): and getting column names. ✅ The default orient is by columns; use orient="records" to get one dict per row.
• ❌ Calling df.to_excel(path, sheet_name=...) twice expecting two sheets. ✅ Use one pd.ExcelWriter for multiple sheets.
• ❌ to_html() output shows the 0, 1, 2 index column on your web page. ✅ Pass index=False.
• ❌ ModuleNotFoundError: No module named 'openpyxl'. ✅ pip install openpyxl.

Interview tip

Q: How do you convert a DataFrame to a list of dictionaries, one per row? A: df.to_dict(orient="records"). This is the standard way to return DataFrame rows from a Python web API, because each dict maps directly to a JSON object.