如何在Pandas中基于多条件快速创建列？大文件处理需提速

Hey there! I totally get the frustration of slow apply() calls when working with large datasets—those 100MB TXT files can really grind things to a halt. The issue with your current approach is that apply() operates row-by-row, which is inherently slow for big data. Let's switch to vectorized operations instead—these are optimized under the hood in pandas/numpy and will drastically speed up your workflow.

Top 2 Efficient Solutions #

1. Use pd.cut() (Best for Interval Binning) #

Pandas has a built-in function pd.cut() specifically designed for binning numeric values into intervals. It's fully vectorized, so it processes the entire Age column at once instead of looping through each row.

Here's how to replicate your logic with pd.cut():

import pandas as pd

# Define your bin edges and corresponding labels
bins = [14, 19, 24, 29, 34, 39, 44, 49, 54, 59, float('inf')]
labels = [
    '15-19', '20-24', '25-29', '30-34', '35-39',
    '40-44', '45-49', '50-54', '55-59', '60 and more'
]

# Generate the AGE-GROUP column in one vectorized step
DF['AGE-GROUP'] = pd.cut(
    DF['Age'],
    bins=bins,
    labels=labels,
    include_lowest=True  # Ensures ages <=19 fall into '15-19' (since our first bin starts at 14)
)

This is not only faster but also cleaner code—no need for a custom row-wise function!

2. Use numpy.select() (For More Flexible Conditions) #

If you ever need to handle non-continuous or more complex conditions, numpy.select() is another great vectorized option. It works by matching rows to the first true condition in a list:

import numpy as np

# Define your conditions (ordered from most specific to least)
conditions = [
    DF['Age'] <= 19,
    DF['Age'] <= 24,
    DF['Age'] <= 29,
    DF['Age'] <= 34,
    DF['Age'] <= 39,
    DF['Age'] <= 44,
    DF['Age'] <= 49,
    DF['Age'] <= 54,
    DF['Age'] <= 59,
    DF['Age'] > 59
]

# Corresponding labels for each condition
choices = [
    '15-19', '20-24', '25-29', '30-34', '35-39',
    '40-44', '45-49', '50-54', '55-59', '60 and more'
]

# Generate the column
DF['AGE-GROUP'] = np.select(conditions, choices, default='60 and more')

Note: Make sure conditions are ordered correctly (just like your original if/elif chain) so the first matching condition is applied.

Bonus: Optimize File Reading for Large TXT Files #

Since you're working with 100MB+ files, optimizing how you load the data can also save time and memory:

• Specify data types: Avoid pandas inferring dtypes (which can use more memory). For example, set Age to int instead of a float or object:

  DF = pd.read_csv('your_data.txt', dtype={'Age': int})
  

• Process in chunks: If files are too large to fit in memory, use chunksize to process data in batches:

  chunk_size = 100000  # Adjust based on your available memory
  output_path = 'processed_data.txt'
  
  # Initialize output file with header
  pd.read_csv('your_data.txt', nrows=0).to_csv(output_path, index=False)
  
  for chunk in pd.read_csv('your_data.txt', chunksize=chunk_size, dtype={'Age': int}):
      # Apply binning to the chunk
      chunk['AGE-GROUP'] = pd.cut(chunk['Age'], bins=bins, labels=labels, include_lowest=True)
      # Append to output file
      chunk.to_csv(output_path, mode='a', header=False, index=False)
  

Why These Work Better #

Vectorized operations leverage optimized C code under the hood, avoiding the Python-level loop overhead of apply(). You should see a 10-100x speedup depending on your dataset size!

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