5 Best Ways to Convert Python Dict to XLS

πŸ’‘ Problem Formulation: Python developers often need to convert data stored in dictionaries into an XLS file for data analysis, reporting, or sharing information with non-technical stakeholders. This article will illustrate how to transform a Python dictionary, which may look like {'Name': ['Alice', 'Bob'], 'Age': [24, 28]}, into a neatly formatted Excel spreadsheet.

Method 1: Using pandas DataFrame

Pandas is a powerful data manipulation library in Python that can easily turn dictionaries into various file formats, including Excel spreadsheets. To use this method, you must have pandas installed and understand DataFrame creation and the to_excel() function.

Here’s an example:

import pandas as pd

data_dict = {'Name': ['Alice', 'Bob'], 'Age': [24, 28]}
df = pd.DataFrame(data_dict)
df.to_excel('output.xlsx', index=False)

The output is an Excel file named ‘output.xlsx’ with the names and ages properly formatted.

This snippet converts the data dictionary into a pandas DataFrame and then writes the DataFrame to an Excel file using the to_excel() method. The index=False parameter is used to prevent pandas from writing row indices into the spreadsheet.

Method 2: Using xlwt Library

The xlwt library is an older library specifically designed for writing data to older Microsoft Excel files (.xls format). It is straightforward to use for small datasets and does not require pandas.

Here’s an example:

import xlwt
from collections import OrderedDict

data_dict = OrderedDict([('Name', ['Alice', 'Bob']), 'Age', [24, 28]])
wb = xlwt.Workbook()
sh = wb.add_sheet('My sheet name')

for col_index, (key, values) in enumerate(data_dict.items()):
    sh.write(0, col_index, key)
    for row_index, value in enumerate(values, 1):
        sh.write(row_index, col_index, value)

wb.save('output.xls')

The output will be an XLS file named ‘output.xls’ containing the data.

This code creates a new Excel workbook and sheet, iterates over the dictionary items, and writes the keys and values to the corresponding cells. The xlwt library handles older XLS formats which may be a limitation or a requirement depending on the use case.

Method 3: Using openpyxl Library

Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. It is more modern than xlwt and can handle new Excel formats.

Here’s an example:

from openpyxl import Workbook

data_dict = {'Name': ['Alice', 'Bob'], 'Age': [24, 28]}
wb = Workbook()
ws = wb.active

for col_index, (key, values) in enumerate(data_dict.items()):
    ws.cell(row=1, column=col_index + 1, value=key)
    for row_index, value in enumerate(values):
        ws.cell(row=row_index + 2, column=col_index + 1, value=value)

wb.save('output.xlsx')

The output will be an XLSX file named ‘output.xlsx’ with the data arranged in cells.

This code opens a new workbook, creates a worksheet, and populates it with the keys and values from the dictionary. openpyxl provides ease of handling modern Excel file formats and stylistic features for the spreadsheet.

Method 4: Using csv and xlrd/xlwt Libraries

This method involves a two-step process: first writing the dictionary to a CSV file using Python’s built-in csv module, then converting the CSV file to an XLS file using xlrd and xlwt libraries.

Here’s an example:

import csv
from xlrd import open_workbook
from xlwt import easyxf, Workbook

data_dict = {'Name': ['Alice', 'Bob'], 'Age': [24, 28]}

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

# Read CSV and write to XLS
rb = open_workbook('output.csv')
r_sheet = rb.sheet_by_index(0)
wb = Workbook()
w_sheet = wb.add_sheet('Sheet 1')

for row_index in range(0, r_sheet.nrows):
    for col_index in range(0, r_sheet.ncols):
        w_sheet.write(row_index, col_index, r_sheet.cell(row_index, col_index).value)

wb.save('output.xls')

The output will be two files, a CSV and an XLS file, both containing the same data.

This method is more convoluted than others as it first requires the dictionary to be written in CSV format and then the CSV to be read and written to an XLS file. It can be used when more control over the intermediate CSV format is needed.

Bonus One-Liner Method 5: Using pandas One-Liner

For those who love brevity, pandas can also convert a dictionary directly to an XLS file in one line of code.

Here’s an example:

pd.DataFrame({'Name': ['Alice', 'Bob'], 'Age': [24, 28]}).to_excel('output.xlsx', index=False)

The output is an XLSX file just like the one obtained from Method 1.

This one-liner takes advantage of chaining methods in pandas. It is essentially the Method 1, but compressed into a single line for quick tasks.

Summary/Discussion

  • Method 1: Pandas DataFrame. Best for large data sets. Requires pandas. Can be memory-intensive for very large datasets.
  • Method 2: xlwt Library. Good for exporting .xls files specifically. No pandas required. Not suitable for .xlsx formats or large datasets.
  • Method 3: openpyxl Library. Ideal for modern Excel files. No pandas required. Some learning curve to handle stylistic features.
  • Method 4: CSV to XLS. Offers control over CSV format. Requires multiple libraries. Cumbersome for simple tasks.
  • Method 5: Pandas One-Liner. Quick and efficient for small tasks. Requires understanding of pandas method chaining.