5 Best Ways to Write a List of Tuples to Excel in Python

💡 Problem Formulation: You have a list of tuples containing data in Python, and you need to write this data to an Excel file. For example, you might have data like [('Alice', 30), ('Bob', 25), ('Charlie', 35)] and you want to store this in a spreadsheet where each tuple corresponds to a row and each element of the tuple corresponds to a column.

Method 1: Using pandas DataFrame and to_excel

Creating an Excel file from a list of tuples can be efficiently performed using the pandas library. The DataFrame data structure in pandas is ideal for table-like data, and the to_excel() method provides a simple way to write the data to an Excel file.

Here’s an example:

import pandas as pd

# Sample data
data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]

# Convert the list of tuples to a DataFrame
df = pd.DataFrame(data, columns=['Name', 'Age'])

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

Output: An Excel file named ‘people.xlsx’ with the data organized in rows and columns are produced.

The code snippet above takes the list of tuples and converts it into a pandas DataFrame, using custom column headers. The to_excel() method is then used to write the DataFrame to an Excel file, without the index column.

Method 2: Using openpyxl and manual cell assignment

The openpyxl library allows for more control when writing to Excel files. If you need to customize the way data is written, such as formatting or location within the worksheet, openpyxl is your go-to choice.

Here’s an example:

from openpyxl import Workbook

# Sample data
data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]

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

# Write data to the worksheet
for row_index, (name, age) in enumerate(data, start=1):
    ws.append([name, age])

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

Output: An Excel file named ‘people.xlsx’ with the custom assigned data is created.

This example iterates over the list of tuples, appending each tuple as a new row in the active worksheet. The Workbook is then saved with the specified file name using the save() method.

Method 3: Using xlsxwriter and customization options

With xlsxwriter, you can not only write data to Excel but also apply a range of formatting options. This library is useful if you want a high degree of control over the visual presentation of the spreadsheet.

Here’s an example:

import xlsxwriter

# Sample data
data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]

# Create a workbook and worksheet
workbook = xlsxwriter.Workbook('people.xlsx')
worksheet = workbook.add_worksheet()

# Write data to worksheet
for row_idx, tuple in enumerate(data):
    worksheet.write_row(row_idx, 0, tuple)

# Close the workbook
workbook.close()

Output: An Excel file named ‘people.xlsx’ that contains the tuples as rows is generated.

In the provided code, we create a new workbook and worksheet with xlsxwriter. Then, the data is written row by row using write_row(). The workbook is finally closed, which is necessary when using xlsxwriter to finalize and save the file.

Method 4: Using xlwt for older XLS Excel files

If you’re dealing with legacy systems that require the older XLS file format, xlwt is a suitable library. It’s specifically designed to write data to the older Excel format (Excel 97-2003).

Here’s an example:

import xlwt

# Sample data
data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]

# Create a workbook and add a worksheet
wb = xlwt.Workbook()
ws = wb.add_sheet('People')

# Write data to the sheet
for row_index, (name, age) in enumerate(data):
    ws.write(row_index, 0, name)
    ws.write(row_index, 1, age)

# Save the workbook
wb.save('people.xls')

Output: An XLS file named ‘people.xls’ is created with each tuple written to a row in the first worksheet titled ‘People’.

This script uses the xlwt library to create a new workbook and worksheet. Then, it loops through the list of tuples, using the write() method to write each item to the corresponding cell. The save() method finalizes and creates the Excel file in the .xls format.

Bonus One-Liner Method 5: Using pandas with a one-liner

For maximum efficiency, you can use pandas to write a list of tuples directly to Excel in a single line of code using the DataFrame constructor and to_excel() method chained together.

Here’s an example:

import pandas as pd

# One-liner to write data to Excel
pd.DataFrame([('Alice', 30), ('Bob', 25), ('Charlie', 35)]).to_excel('people.xlsx', header=False, index=False)

Output: An Excel file ‘people.xlsx’ is created with no headers and no index.

The concise code uses a one-liner to create a DataFrame from the list of tuples and immediately writes it to an Excel file, specifying that there should be no headers or index in the output.

Summary/Discussion

• Method 1: pandas DataFrame and to_excel. Strengths: Simple and efficient for most use cases. Weaknesses: Dependency on pandas library and less control over formatting.
• Method 2: openpyxl and manual cell assignment. Strengths: Greater control over how data is written, including cell formatting. Weaknesses: Can be more verbose and requires more code for large datasets.
• Method 3: xlsxwriter with customization. Strengths: High degree of control over formatting and presentation. Weaknesses: The interface can be intricate for simple tasks, and closing the workbook is a necessary step.
• Method 4: xlwt for older XLS files. Strengths: Compatibility with older versions of Excel. Weaknesses: Does not support newer XLSX file formats and has less functionality compared to other libraries.
• Bonus Method 5: pandas one-liner. Strengths: Extremely concise. Weaknesses: Very limited control over output formatting and assumes default column names.