5 Best Ways to Convert Python Dictionaries to Excel Files

by
💡 Problem Formulation:

As a developer or data analyst, you often need to transfer data between different formats. Specifically, converting a Python dictionary (dict) into an Excel spreadsheet (xlsx) requires methods that maintain the structure and readability of the data. Imagine you have a Python dictionary with multiple key-value pairs and you want to transfer this data into an organized Excel file where keys become column headers and values fill the rows underneath.

Method 1: Using pandas DataFrame

Pandas is a powerful data manipulation library in Python. To convert a Python dictionary to an Excel file, you can utilize Pandas DataFrame structure, and then use the to_excel() function to save the DataFrame as an Excel file. This method is efficient and allows for further data manipulation before saving if necessary.

Here’s an example:

import pandas as pd

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

df = pd.DataFrame(data_dict)
df.to_excel('output.xlsx', index=False)

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

This code snippet imports the pandas library and creates a DataFrame from the data_dict. The pandas method to_excel() is then used to convert the DataFrame to an Excel file, which will be saved in the current directory under the name ‘output.xlsx’. The index=False parameter is used to prevent pandas from writing row indices into the Excel file.

Method 2: Using xlsxwriter Library

The xlsxwriter module allows you to create an Excel file and add new data to it using Python code. This library provides greater control over the Excel file’s formatting and appearance, making it ideal for customized spreadsheet creation.

Here’s an example:

import xlsxwriter

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

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

row, col = 0, 0
for key in data_dict.keys():
    worksheet.write(row, col, key)
    col += 1

for i, key in enumerate(data_dict.keys()):
    for value in data_dict[key]:
        worksheet.write(row+1, i, value)
        row += 1
    row = 0

workbook.close()

Output: A customized Excel file ‘output.xlsx’ containing the dictionary’s data.

The code creates an Excel file named ‘output.xlsx’ and writes the keys and values from data_dict to the file. It uses the xlsxwriter module to write the keys as the headers and the corresponding values underneath them in separate columns. This method allows for more advanced customization of the Excel file compared to pandas.

Method 3: Using OpenPyXL

OpenPyXL is another library for reading and writing Excel 2010 xlsx/xlsm files. It is flexible and provides the user with more control over cell styles, formatting, and charting capabilities.

Here’s an example:

from openpyxl import Workbook

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

wb = Workbook()
ws = wb.active

ws.append(list(data_dict.keys()))
for i in zip(*data_dict.values()):
    ws.append(i)

wb.save('output.xlsx')

Output: Excel file ‘output.xlsx’ with the data mapped from the dictionary.

This code imports the Workbook class from OpenPyXL, creates a new workbook and a worksheet, appends the dictionary keys as headers, and then iterates through values. It uses zip() to transpose the list of values and then appends them row by row to the worksheet. The workbook is saved with the filename ‘output.xlsx’.

Method 4: Using csv Module and Microsoft Excel

The CSV file format is natively supported by Excel, so you can first write the dictionary to a CSV file and then open it with Excel. This is a simple method that leverages Python’s built-in csv module.

Here’s an example:

import csv

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

with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(data_dict.keys())
    writer.writerows(zip(*data_dict.values()))

# Manually open and save 'output.csv' in Excel to convert to 'output.xlsx'

Output: A CSV file ‘output.csv’, which you can open and save as ‘output.xlsx’ in Excel.

The snippet creates a CSV writer and uses it to write the dictionary keys and then the values. The values are transposed into rows using the zip() function before writing. You’ll need to open the resulting CSV file in Excel and save it as an Excel workbook manually.

Bonus One-Liner Method 5: Using Excel’s Power Query

If you already have the dictionary formatted as a JSON string, Excel’s own Power Query feature can be used to import it directly into a spreadsheet. This method is suitable for users who prefer to work within Excel rather than script in Python.

Here’s an example:

# Consider the dictionary is stored as JSON in "data.json".
# In Excel, use the "Get Data" -> "From File" -> "From JSON" option,
# select the file, and Excel will convert the JSON to an xlsx format.

Output: Excel file with content from “data.json” which was structured as a Python dictionary.

This simple method does not require Python scripting. Simply save your Python dictionary as a JSON file (often done using the json.dump() method from the json module in Python), then use Excel’s Power Query to import and convert it directly into the workbook.

Summary/Discussion

  • Method 1: pandas DataFrame. Strengths: Simple, efficient, allows further data manipulation. Weaknesses: Requires pandas installation, less control over Excel-specific features.
  • Method 2: xlsxwriter Library. Strengths: Great control over formatting, good for custom Excel sheets. Weaknesses: Can be verbose, requires xlsxwriter installation.
  • Method 3: OpenPyXL. Strengths: Offers detailed control and extended Excel features. Weaknesses: Might have a steeper learning curve, requires OpenPyXL installation.
  • Method 4: csv Module and Microsoft Excel. Strengths: Utilizes Python’s built-in module, easy to understand and use. Weaknesses: Additional manual step to convert CSV to XLSX in Excel.
  • Bonus Method 5: Excel’s Power Query. Strengths: Useful for non-programmers, all operations are done within Excel. Weaknesses: Limited to the features offered by Excel, indirect use of Python.