比loc更高效的DataFrame清洗方法？附现有实现代码

Hey there! Let's make your DataFrame cleaning workflow faster and more scalable than using multiple loc assignments. Your current code works, but it's not the most efficient—especially if you have more country names to standardize later. Here are some optimized approaches:

1. Batch Replace Country Names with replace() #

Instead of calling loc for each country, use a dictionary mapping to handle all replacements in a single vectorized operation (way faster than repeated loc calls):

import pandas as pd

# Read and slice data in one go (skip extra copy if you don't need the original df)
c_df = pd.read_excel("Energy Indicators.xls", header=None, footer=None).iloc[18:245, 2:]

# Rename columns
c_df = c_df.rename(columns={2: 'Country', 3: 'Energy Supply', 4:'Energy Supply per Capita', 5:'% Renewable'})

# Batch country name mappings
country_mapping = {
    'Korea, Rep.': 'South Korea',
    'United States of America20': 'United States',
    # Add any other country name fixes here
}
c_df['Country'] = c_df['Country'].replace(country_mapping)

2. Ditch apply() for Vectorized Operations #

Your apply call for Energy Supply can be replaced with a simple vectorized multiplication, which avoids slow loops under the hood:

# Vectorized multiplication (far faster than apply)
c_df['Energy Supply'] *= 1000000

3. Batch Clean Trailing Suffixes with Regex #

If you have country names with trailing numbers (like United States of America20), use regex to strip all trailing digits in one step—no need to list each case individually:

# Remove trailing digits from all country names
c_df['Country'] = c_df['Country'].str.replace(r'\d+$', '', regex=True)

4. Chain Operations to Cut Down on Intermediate Copies #

You can chain most operations to minimize unnecessary DataFrame copies, saving memory and speeding up execution:

# Full optimized workflow with chaining
country_mapping = {
    'Korea, Rep.': 'South Korea',
    # Add other mappings here
}

c_df = (pd.read_excel("Energy Indicators.xls", header=None, footer=None)
        .iloc[18:245, 2:]
        .rename(columns={2: 'Country', 3: 'Energy Supply', 4:'Energy Supply per Capita', 5:'% Renewable'})
        .assign(
            Energy_Supply=lambda x: x['Energy Supply'] * 1000000,
            Country=lambda x: x['Country'].replace(country_mapping).str.replace(r'\d+$', '', regex=True)
        ))

Why This Works Better: #

• Vectorized operations: Pandas is built for these, so they run significantly faster than loops or repeated loc assignments.
• Scalability: Adding more country fixes just requires updating the dictionary, not writing new loc lines.
• Cleaner code: Easier to read and maintain, especially as your dataset grows.

Hope these tweaks make your data cleaning process smoother and faster!

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