如何将Pandas DataFrame导出为含空字段的指定格式文本文件

To export your 4-column DataFrame into a 10-field formatted text file (with empty spaces for missing fields), the key is to pre-format each row to match your desired format string exactly—since numeric format specs can't directly output empty spaces when using np.savetxt with numeric values. Here's a step-by-step approach:

Step 1: Define Your DataFrame and Format String #

First, set up your DataFrame and the target format string:

import pandas as pd

# Sample DataFrame
data = {'ID': [1, 2, 3], 'Field1': [1, 2, 4], 'Field2': [1, 3, 5], 'Field3': [1, 1, 8]}
df = pd.DataFrame(data)

# Your target format string
fmt = '%2s%10s%3s%1.0f%4s%1s%4.0f%02.0f%02.0f%4.0f'

Step 2: Parse the Format Specs #

Split the format string into individual specs and calculate the width of each field (to create empty spaces for missing columns):

import re

# Split into individual format specs
fmt_specs = ['%' + part for part in fmt.split('%')[1:]]

# Helper function to extract field width from a format spec
def get_field_width(spec):
    # Handle specs like '%2s' or '%02.0f'
    match = re.match(r'%0?(\d+)', spec)
    return int(match.group(1)) if match else 0

# Get width of each field
field_widths = [get_field_width(spec) for spec in fmt_specs]

Step 3: Pre-Format Each Row #

Loop through each row, format existing columns with their corresponding specs, and fill missing fields with empty spaces:

formatted_lines = []

for _, row in df.iterrows():
    # Format existing columns (adjust the mapping to match your needs!)
    field_0 = fmt_specs[0] % str(row['ID'])       # %2s for ID
    field_1 = fmt_specs[1] % str(row['Field1'])   # %10s for Field1
    field_2 = fmt_specs[2] % str(row['Field2'])   # %3s for Field2
    field_3 = fmt_specs[3] % row['Field3']        # %1.0f for Field3
    
    # Create empty spaces for missing fields
    field_4 = ' ' * field_widths[4]
    field_5 = ' ' * field_widths[5]
    field_6 = ' ' * field_widths[6]
    field_7 = ' ' * field_widths[7]
    field_8 = ' ' * field_widths[8]
    field_9 = ' ' * field_widths[9]
    
    # Combine all fields into a single line
    line = field_0 + field_1 + field_2 + field_3 + field_4 + field_5 + field_6 + field_7 + field_8 + field_9
    formatted_lines.append(line)

Step 4: Write to File #

Save the formatted lines to your target text file:

with open('df.txt', 'w') as f:
    f.write('\n'.join(formatted_lines) + '\n')

Key Notes #

• Adjust Column Mapping: If your existing columns correspond to different positions in the format string, simply reassign which fmt_specs index is used for each DataFrame column.
• Empty Numeric Fields: By pre-formatting, we ensure even numeric fields (like %4.0f) are replaced with the correct number of spaces instead of 0 or nan.
• Alignment: The format specs follow standard Python string formatting rules (e.g., %2s left-aligns strings, %1.0f right-aligns numbers).

content的提问来源于stack exchange，提问作者babz