在Pandas中筛选DataFrame的20-500范围值并迁移至另一DataFrame

First, let's lay out the sample CSV data we're working with:

date,mean,min,max,std
2018-03-15,3.9999999999999964,inf,0.0,100.0
2018-03-16,0.46403712296984756,90.0,0.0,inf
2018-03-17,2.32452732452731,,0.0,143.2191767899579
2018-03-18,2.8571428571428523,inf,0.0,100.0
2018-03-20,0.6928406466512793,100.0,0.0,inf
2018-03-22,2.8675703858185635,,0.0,119.05383697172658

Your goal is to import this CSV into a Pandas DataFrame, filter out values that fall between 20 and 500 across all numeric columns, and preserve the corresponding date values in a new DataFrame (while excluding inf, empty values, and numbers outside the target range).

Step 1: Import Pandas & Load the CSV #

First, we'll read the CSV and clean up invalid values right away—inf and empty strings don't fit our 20-500 range, so we'll convert them to NaN upfront to simplify filtering:

import pandas as pd

# Load CSV, convert inf and empty strings to NaN
df = pd.read_csv('your_file.csv', na_values=['inf', ''])

Step 2: Filter Values & Build the New DataFrame #

There are two straightforward approaches to achieve this—pick whichever fits your workflow better:

Option 1: Process Columns One by One

This method is easy to follow if you want explicit control over each column's handling:

# Start with a copy of the date column to preserve all dates
new_df = df[['date']].copy()

# Loop through each numeric column (exclude the date column)
for col in df.columns.drop('date'):
    # Keep only values between 20-500, set all others to NaN
    new_df[col] = df[col].where((df[col] >= 20) & (df[col] <= 500))

# Print the final result
print(new_df)

Option 2: Batch Process with apply

If you prefer a more concise style, use apply to handle all numeric columns in one go:

# Filter all numeric columns simultaneously
filtered_numeric = df.drop('date', axis=1).apply(lambda x: x.where((x >= 20) & (x <= 500)))

# Merge the original date column back with the filtered values
new_df = pd.concat([df['date'], filtered_numeric], axis=1)

Final Result Preview #

After running either method, your new_df will look like this:

date  mean   min  max         std
0  2018-03-15   NaN   NaN  NaN  100.000000
1  2018-03-16   NaN  90.0  NaN         NaN
2  2018-03-17   NaN   NaN  NaN  143.219177
3  2018-03-18   NaN   NaN  NaN  100.000000
4  2018-03-20   NaN  100.0 NaN         NaN
5  2018-03-22   NaN   NaN  NaN  119.053837

Only values within the 20-500 range are retained, and every row keeps its original date value.

Extra Tip #

If you want to remove rows where all numeric columns are NaN (no valid values in that row), add this line:

new_df = new_df.dropna(how='all', subset=df.columns.drop('date'))

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