自定义函数转换DataFrame格式及特定DataFrame结构调整方法

Got it, let's tackle both of your questions clearly. First, I'll walk you through how to build a custom function for this DataFrame transformation, then apply it directly to your specific example.

1. Writing a Custom Function for DataFrame Format Conversion #

When creating a custom function for this kind of transformation, we need to account for a few key steps: filtering out rows with missing row_number values, splitting comma-separated values into individual entries, expanding those entries into separate rows, and cleaning up the final output.

Here's a breakdown of the core logic we'll implement:

• Protect original data: Always make a copy of the input DataFrame to avoid modifying the source data accidentally.
• Filter empty entries: Remove rows where row_number is blank (since we don't need those in the final output).
• Clean and split values: Split comma-separated row_number strings into lists, and strip any accidental whitespace from individual entries.
• Expand lists to rows: Use pandas' explode() method to turn each item in the list into its own row.
• Final cleanup: Convert row_number to a numeric type (optional but useful for future operations) and keep only the columns we need.

2. Solution for Your Specific DataFrame #

Let's start by replicating your original DataFrame, then apply our custom function to get the desired output.

Step 1: Import pandas and create the original DataFrame #

import pandas as pd

# Original DataFrame matching your structure
original_df = pd.DataFrame({
    'col_name': ['ST_NUM', 'ST_NAME', 'OWN_OCCUPIED', 'NUM_BEDROOMS'],
    'No. Missing': [2, 0, 3, 2],
    'row_number': ['2,4', '', '1,3,10', '1,4']
})

Step 2: Define the custom transformation function #

def transform_missing_rows(original_df):
    # Make a copy to avoid altering the original DataFrame
    df = original_df.copy()
    
    # Replace empty strings in row_number with NaN, then drop those rows
    df['row_number'] = df['row_number'].replace('', pd.NA)
    df = df.dropna(subset=['row_number'])
    
    # Split comma-separated strings into lists, stripping any whitespace
    df['row_number'] = df['row_number'].str.split(',').apply(lambda x: [num.strip() for num in x])
    
    # Explode the list values into separate rows
    df = df.explode('row_number', ignore_index=True)
    
    # Convert row_number to integer type (optional but recommended for numeric operations)
    df['row_number'] = df['row_number'].astype(int)
    
    # Keep only the required columns
    df = df[['col_name', 'row_number']]
    
    return df

Step 3: Run the function and view the result #

result_df = transform_missing_rows(original_df)
print(result_df)

Output: #

col_name  row_number
0        ST_NUM           2
1        ST_NUM           4
2  OWN_OCCUPIED           1
3  OWN_OCCUPIED           3
4  OWN_OCCUPIED          10
5   NUM_BEDROOMS           1
6   NUM_BEDROOMS           4

This matches exactly the structure you're looking for! The function is reusable too—you can apply it to any DataFrame with a similar structure.

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