如何以最Pythonic、优雅高效的方式基于匹配长度的双列表创建指定结构的Pandas DataFrame并导出为XLS文件

Hey there! Since you're just getting started with Pandas, let's walk through a clean, Pythonic way to build your desired DataFrame, plus an alternative approach that doesn't require Pandas at all for generating the XLS file.

1. Pandas Approach (Elegant & Efficient) #

Instead of creating an empty DataFrame first and filling it in (which can get messy), we'll build our row data directly and then convert it to a DataFrame in one go. Here's how:

Step-by-Step Code #

import pandas as pd

# Your sample data (replace with your actual lists)
list1 = ['WordA', 'WordB', 'WordC', 'WordXYZ']
list2 = [['WordA1', 'WordA2'], ['WordB1'], ['WordC1', 'WordC2', 'WordC96'], ['WordXYZ1', 'WordXYZ2']]

# Calculate the total number of columns needed (1 for list1 items + longest sublist length in list2)
max_columns = max(len(sublist) for sublist in list2) + 1

# Build all rows for the DataFrame
rows = []
for word, sublist in zip(list1, list2):
    # First row: word + sublist elements, fill remaining columns with empty strings
    first_row = [word] + sublist + [''] * (max_columns - 1 - len(sublist))
    rows.append(first_row)
    # Second row: word + empty strings for all other columns
    second_row = [word] + [''] * (max_columns - 1)
    rows.append(second_row)

# Create the DataFrame
df = pd.DataFrame(rows)

# Save to XLS (no index or header, matching your example format)
df.to_excel('output_pandas.xls', index=False, header=False)

Why This Works Better: #

• Uses zip() to pair elements from list1 and list2 cleanly
• Builds rows directly, avoiding the headache of pre-filling an empty DataFrame
• Automatically handles variable-length sublists by padding with empty strings to match the longest column count

2. No Pandas? Use xlwt for XLS Generation #

If you don't want to use Pandas, the xlwt library lets you create XLS files directly by writing rows and columns manually. It's lightweight and perfect for simple formatting tasks like this.

Step-by-Step Code #

First, install the library:

pip install xlwt

Then write the code:

import xlwt

# Your sample data (replace with your actual lists)
list1 = ['WordA', 'WordB', 'WordC', 'WordXYZ']
list2 = [['WordA1', 'WordA2'], ['WordB1'], ['WordC1', 'WordC2', 'WordC96'], ['WordXYZ1', 'WordXYZ2']]

# Create a new workbook and sheet
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Data')

# Track the current row we're writing to
current_row = 0

for word, sublist in zip(list1, list2):
    # Write the first row: word + sublist elements
    sheet.write(current_row, 0, word)
    for col_idx, item in enumerate(sublist, start=1):
        sheet.write(current_row, col_idx, item)
    current_row += 1
    
    # Write the second row: only the word, rest empty
    sheet.write(current_row, 0, word)
    current_row += 1

# Save the workbook
workbook.save('output_no_pandas.xls')

Note for XLSX Format: #

xlwt only supports the older .xls format. If you need .xlsx, use openpyxl instead—its syntax is similar but optimized for the newer format.

内容的提问来源于stack exchange，提问作者Paul Alex