Python自动化删除指定列并转置数据集（跟进技术问询）

Got it, let's tackle this step by step. Your goal is to automate processing large CSV datasets to: remove columns with hyphens, restructure the data so all rule_ids sit in the first row, and have their corresponding comp values below them. Here's a streamlined, efficient fix that avoids manual work and handles big data without memory bottlenecks:

Step 1: Refine Single CSV Processing Logic #

First, let's adjust how we handle each individual CSV to get the exact structure you want:

import pandas as pd
import numpy as np

def process_single_csv(tx_id):
    # Read CSV efficiently (low_memory=False fixes mixed-type column warnings)
    df = pd.read_csv(f"{tx_id}.csv", low_memory=False)
    
    # Group rule_ids and count request_id occurrences, pivot to wide format
    df1 = df.groupby('rule_id')['request_id'].value_counts().unstack().fillna(0)
    
    # Calculate percentage comparison with the next column (your original logic)
    m = df1.eq(df1.shift(-1, axis=1))
    arr1 = np.select([df1 == 0, m], [np.nan, df1], df1 * 100)
    
    # Rename columns to comp1, comp2, etc.
    dft4 = pd.DataFrame(arr1, index=df1.index).rename(columns=lambda x: f'comp{x+1}')
    
    # Join original counts with comparison values
    dft5 = df1.join(dft4)
    
    # Drop any columns containing hyphens
    cols_to_drop = [c for c in dft5.columns if '-' in c]
    df_clean = dft5.drop(cols_to_drop, axis=1)
    
    # Restructure to put rule_ids in the first row:
    # 1. Reset index to turn rule_id into a regular column
    # 2. Transpose the DataFrame
    df_transposed = df_clean.reset_index().transpose()
    
    # 3. Set the first row (originally the rule_id column) as the header
    df_transposed.columns = df_transposed.iloc[0]
    # 4. Remove the redundant "rule_id" label row
    df_final = df_transposed.drop(df_transposed.index[0])
    
    # Optional: Add a tx_id column to track which original CSV this row comes from
    df_final['tx_id'] = tx_id
    
    return df_final

Step 2: Batch Process All CSVs #

Now apply this function to all your tx_ids and combine the results into one unified DataFrame:

# Get all unique tx_ids from your dframe2 dataset
tx_ids = dframe2['tx_id'].unique()

# Process all CSVs efficiently with a list comprehension
dfs = [process_single_csv(tx) for tx in tx_ids]

# Combine all processed data into a single DataFrame
final_combined_df = pd.concat(dfs, ignore_index=True)

Key Changes & Why They Work #

• Rule_id as first row: By resetting the index before transposing, we turn rule_id into a regular column. After transposing, this column becomes the first row, which we then set as the DataFrame header—exactly the structure you requested.
• Large dataset efficiency: Using low_memory=False prevents type-inference errors with mixed-type columns, and list comprehensions are faster than looping with append for big batches.
• Traceability: The optional tx_id column lets you map each row back to its original CSV (feel free to remove this if you don't need it).

What the Final Output Looks Like #

Your final_combined_df will have:

• All rule_ids as column headers (first row)
• Each subsequent row corresponds to a comp column from the original data, with your calculated values
• No columns containing hyphens
• All data from your large batch of CSVs merged into one easy-to-use DataFrame

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