5 Best Ways to Convert Python Dictionaries to Excel

by

💡 Problem Formulation: Transferring data from a Python dictionary to an Excel file is a common requirement for developers dealing with data analysis and reporting. Suppose you have a dictionary containing data structured as key-value pairs, and you need to export this data into a structured Excel spreadsheet. The target output is an Excel file with headers as dictionary keys and rows as corresponding values.

Method 1: Using Pandas to Excel Function

Pandas is a powerful Python library for data manipulation and analysis, which provides a simple to_excel method to convert a DataFrame to an Excel file. By converting a dictionary to a DataFrame first, you can then use this method to export your data to Excel format effectively.

Here’s an example:

import pandas as pd

# Sample Python dictionary
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35],
             'City': ['New York', 'Los Angeles', 'Chicago']}

# Converting to a DataFrame
df = pd.DataFrame(data_dict)

# Writing the DataFrame to an Excel file
df.to_excel('output.xlsx', index=False)

Output: An Excel file named ‘output.xlsx’ with the data from ‘data_dict’.

This code snippet creates a pandas DataFrame from a dictionary, where the keys become column headers and the values become rows in the DataFrame. The to_excel() method then exports this DataFrame to an Excel file, with index=False ensuring that the DataFrame’s indices are not included as an extra column in the Excel file.

Method 2: Using Openpyxl Library

The Openpyxl library allows you to read and write Excel 2010+ xlsx/xlsm/xltx/xltm files specifically. You can define a workbook and append dictionary items as rows directly into the Excel spreadsheet.

Here’s an example:

from openpyxl import Workbook

# Sample Python dictionary
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35],
             'City': ['New York', 'Los Angeles', 'Chicago']}

# Initialize an Excel Workbook
wb = Workbook()
ws = wb.active

# Adding the headers
ws.append(list(data_dict.keys()))

# Adding the rows
for row in zip(*data_dict.values()):
    ws.append(row)

# Saving the workbook
wb.save('output.xlsx')

Output: An Excel file named ‘output.xlsx’ with dictionary data added as rows.

The code sets up an Excel workbook using Openpyxl and then appends the dictionary keys as the header row. It adds each set of values from the dictionary as a new row to the Excel sheet before saving the workbook as ‘output.xlsx’. The zip(*data_dict.values()) expression transposes the values from the dictionary so that they align with the corresponding headers.

Method 3: Using XlsxWriter Module

XlsxWriter is another Python module for creating Excel files. This method can give you more control over the Excel file formatting and structure.

Here’s an example:

import xlsxwriter

# Sample Python dictionary
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35],
             'City': ['New York', 'Los Angeles', 'Chicago']}

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

# Start from the first cell
row, col = 0, 0

# Iterate over the dictionary keys and values
for key in data_dict.keys():
    worksheet.write(row, col, key)
    col += 1

for i, key in enumerate(data_dict.keys()):
    for j, item in enumerate(data_dict[key]):
        worksheet.write(j+1, i, item)

# Close the Excel file
workbook.close()

Output: An Excel file named ‘output.xlsx’ with the provided dictionary written across columns and rows.

With the XlsxWriter module, you start by creating a new Excel workbook and adding a worksheet. You then iterate over the dictionary, writing the keys as headers and the associated items in subsequent rows and columns. Finally, you close the workbook which writes the content to the Excel file ‘output.xlsx’.

Method 4: Using csv with Excel Dialect

For simplicity, you can use Python’s built-in csv module with an Excel dialect to write a CSV file that can be opened by Excel with proper formatting.

Here’s an example:

import csv

# Sample Python dictionary
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35],
             'City': ['New York', 'Los Angeles', 'Chicago']}

# Writing to a CSV file with the Excel dialect
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=data_dict.keys(), dialect='excel')
    writer.writeheader()
    writer.writerows([{'Name': name, 'Age': age, 'City': city} for name, age, city in zip(data_dict['Name'], data_dict['Age'], data_dict['City'])])

Output: A CSV file named ‘output.csv’ which can be opened with Excel to show the data in a tabular format.

This code leverages Python’s CSV module to write the dictionary’s keys and values to a CSV file. The DictWriter class uses the Excel dialect for compatibility with Excel’s default formatting. Headers are written first, followed by the rows of dictionary values, ensuring that cells align correctly under their corresponding headers.

Bonus One-Liner Method 5: Using Pandas with a Simple Function Call

For the ultimate one-liner, you can combine dictionary to DataFrame conversion and DataFrame to Excel export in a single command with pandas.

Here’s an example:

import pandas as pd

# Sample Python dictionary
data_dict = {'Name': ['Alice', 'Bob', 'Charlie'],
             'Age': [25, 30, 35],
             'City': ['New York', 'Los Angeles', 'Chicago']}

# Convert and save in one line
pd.DataFrame(data_dict).to_excel('output.xlsx', index=False)

Output: An Excel file named ‘output.xlsx’ with the python dictionary data.

This compact code example takes a dictionary, converts it to a pandas DataFrame, and then immediately writes it to an Excel file, all in one chain of method calls. This method is concise and leverages pandas’ powerful DataFrame manipulation capabilities.

Summary/Discussion

  • Method 1: Pandas to_excel Function. Strengths: Easy one-liner available, part of a powerful data manipulation library. Weaknesses: Requires installing pandas, which can be heavy for lightweight scripts.
  • Method 2: Openpyxl Library. Strengths: Offers granular control and capability to work with more complex Excel features. Weaknesses: Syntax can be more verbose and is Excel-specific.
  • Method 3: XlsxWriter Module. Strengths: Highly customizable and performant for large datasets. Weaknesses: Similar to Openpyxl, it might be too heavy for simple tasks.
  • Method 4: CSV with Excel Dialect. Strengths: Utilizes built-in modules, no extra dependencies required. Weaknesses: Produces a CSV file, not a native Excel file, and might require further formatting in Excel.
  • Bonus Method 5: Pandas One-Liner. Strengths: Extremely concise and easy to use for simple data transformations. Weaknesses: Still requires pandas, and lacks the control of other dedicated Excel libraries.