5 Best Ways to Convert Python Dictionary to Excel Table

by

💡 Problem Formulation: Imagine you have a Python dictionary containing employee data, keyed by employee ID, with each value being another dictionary of details such as name and role. You want to transform this data into an organized Excel table for reporting and analysis. The goal is straightforward: convert a Python dictionary into a structured Excel table while preserving the data’s integrity.

Method 1: Using pandas.DataFrame

With Pandas, conversion of a dictionary to an Excel table is streamlined. The DataFrame object is capable of interpreting dictionary structures and can then be easily exported to an Excel file using the to_excel() method. This method supports complex data types and nested dictionaries, providing extensive functionality for data manipulation before exporting.

Here’s an example:

import pandas as pd

# Given dictionary
data_dict = {
    '101': {'Name': 'Alice', 'Role': 'Developer'},
    '102': {'Name': 'Bob', 'Role': 'Manager'},
    '103': {'Name': 'Charlie', 'Role': 'Analyst'}
}

# Convert dictionary to DataFrame
df = pd.DataFrame.from_dict(data_dict, orient='index')

# Save to Excel
df.to_excel('employees.xlsx', index_label='Employee ID')

The output is an Excel file named ’employees.xlsx’ with the employee data organized in tabular format, including an ‘Employee ID’ column.

This code snippet creates a Pandas DataFrame from the Python dictionary. By setting orient='index', we ensure that dictionary keys (employee IDs) are used as row indices. Then the DataFrame is exported to an Excel file, where the index_label parameter adds a header for the index column in the output file.

Method 2: Using xlsxwriter Library

The xlsxwriter library is a powerful tool for creating complex Excel files, including formatting and charts. It provides greater control over the Excel file creation process but requires a bit more code to handle the dictionary to Excel conversion.

Here’s an example:

import xlsxwriter

# Given dictionary
data_dict = {
    '101': {'Name': 'Alice', 'Role': 'Developer'},
    '102': {'Name': 'Bob', 'Role': 'Manager'},
    '103': {'Name': 'Charlie', 'Role': 'Analyst'}
}

# Create a new Excel file and add a worksheet
workbook = xlsxwriter.Workbook('employees.xlsx')
worksheet = workbook.add_worksheet()

# Write header
worksheet.write('A1', 'Employee ID')
worksheet.write('B1', 'Name')
worksheet.write('C1', 'Role')

# Write data to Excel
for row_num, (emp_id, info) in enumerate(data_dict.items(), start=1):
    worksheet.write(row_num, 0, emp_id)
    worksheet.write(row_num, 1, info['Name'])
    worksheet.write(row_num, 2, info['Role'])

workbook.close()

The output is an ’employees.xlsx’ file containing the employee data, similar to Method 1 but possibly with better control over formatting.

This snippet initiates a new workbook, adds a worksheet, and populates it with headers and data from the dictionary. We manually iterate over the dictionary items and write them to the appropriate cells. Once all data is written, the workbook is closed, finalizing the Excel file.

Method 3: Using openpyxl Library

Openpyxl is a library designed to read/write Excel 2010 xlsx/xlsm/xltx/xltm files. It’s useful in cases where you want to utilize Excel’s features like formulas, charts, and more through Python. It is less memory-efficient than other methods but offers a rich set of features.

Here’s an example:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

# Given dictionary
data_dict = {
    '101': {'Name': 'Alice', 'Role': 'Developer'},
    '102': {'Name': 'Bob', 'Role': 'Manager'},
    '103': {'Name': 'Charlie', 'Role': 'Analyst'}
}

# Create a new Workbook and select the active worksheet
wb = Workbook()
ws = wb.active

# Write header
headers = ['Employee ID', 'Name', 'Role']
ws.append(headers)

# Write data to Excel
for emp_id, info in data_dict.items():
    ws.append([emp_id] + list(info.values()))

# Save the workbook
wb.save('employees.xlsx')

The output is the ’employees.xlsx’ file with the employee data organized.

The openpyxl module creates a new Excel workbook and appends rows directly from the dictionary. This approach is quite straightforward, and the append() method makes adding rows easy, handling the looping internally.

Method 4: Using csv Module and Conversion Tool

If you don’t require Excel-specific features, you can use Python’s built-in csv module to write the data to a CSV file, which Excel can open naturally. This method is especially useful when you only need to display data without additional Excel functions.

Here’s an example:

import csv

# Given dictionary
data_dict = {
    '101': {'Name': 'Alice', 'Role': 'Developer'},
    '102': {'Name': 'Bob', 'Role': 'Manager'},
    '103': {'Name': 'Charlie', 'Role': 'Analyst'}
}

# Write to CSV file
with open('employees.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    # Write header
    writer.writerow(['Employee ID', 'Name', 'Role'])
    # Write data rows
    for emp_id, info in data_dict.items():
        writer.writerow([emp_id, info['Name'], info['Role']])

The output is ’employees.csv’, which can be opened with Excel to display data in table format.

This code snippet opens a new CSV file and writes the header and each data row. The CSV file is a plain text file and can be read by Excel, making it a simple and lightweight solution for data presentation.

Bonus One-Liner Method 5: Using pandas with json_normalize

For nested dictionaries where you would like to flatten the data structure without much boilerplate code, use Pandas’ json_normalize method to prepare the data before writing to Excel.

Here’s an example:

import pandas as pd
from pandas import json_normalize

# Nested dictionary
data_dict = {
    '101': {'Name': 'Alice', 'Details': {'Role': 'Developer', 'Department': 'Engineering'}},
    '102': {'Name': 'Bob', 'Details': {'Role': 'Manager', 'Department': 'Sales'}},
    '103': {'Name': 'Charlie', 'Details': {'Role': 'Analyst', 'Department': 'Marketing'}}
}

# Normalize and convert to Excel
json_normalize(data_dict).T.to_excel('detailed_employees.xlsx')

The output is ‘detailed_employees.xlsx’, with the nested data flattened and stored in Excel.

This one-liner takes the nested dictionary, normalizes it to create a flat table structure, transposes it since we want the keys as rows, and then writes it directly to an Excel file. This method is powerful for handling complex nested dictionaries.

Summary/Discussion

  • Method 1: Pandas DataFrame. Strengths: Simple, powerful, handles various data types. Weaknesses: Requires Pandas, not as memory-efficient for large datasets.
  • Method 2: xlsxwriter Library. Strengths: Great control over formatting, good for complex files. Weaknesses: More verbose code, dealing with indexing manually.
  • Method 3: openpyxl Library. Strengths: Access to advanced Excel features. Weaknesses: Slower with very large datasets, less memory-efficient.
  • Method 4: CSV Module. Strengths: Simple, no third-party libraries needed. Weaknesses: Lacks native Excel features, CSVs are less versatile.
  • Method 5: Pandas with json_normalize. Strengths: Handles nested dictionaries efficiently in a one-liner. Weaknesses: Requires knowledge of Pandas, less intuitive for deeply nested structures.