数据集筛选提取与转换：处理十万行级.xlsx数据集

Alright, let's work through handling your large-scale Excel dataset (hundreds of thousands of rows) with filtering, extraction, and transformation tasks. Below are two practical, actionable approaches tailored to your needs:

Pandas is ideal here because it efficiently handles big data without the lag or crashes Excel might hit with hundreds of thousands of rows. Here's a step-by-step implementation:

Step 1: Install Required Libraries #

First, make sure you have Pandas and the Excel reader openpyxl installed:

pip install pandas openpyxl

Step 2: Load and Clean the Data #

Your dataset uses commas as decimal separators (e.g., 135,3) and has dates in DD/MM/YYYY format—we'll fix these during loading:

import pandas as pd

# Convert comma-separated strings to numeric values
def clean_numeric(value):
    if isinstance(value, str):
        return float(value.replace(',', '.'))
    return value

# Load the Excel file, parse dates, and clean numeric columns
df = pd.read_excel(
    "your_dataset.xlsx",
    parse_dates=["date"],  # Automatically convert 'date' column to datetime objects
    converters={
        "open": clean_numeric,
        "high": clean_numeric,
        "low": clean_numeric,
        "close": clean_numeric,
        "close_ratio": clean_numeric,
        "spread": clean_numeric
    }
)

Step 3: Filter and Extract Data #

Let's say you want to filter records from 2018 and extract specific columns (e.g., date, close price, volume):

# Filter rows where the date is in 2018
filtered_data = df[df["date"].dt.year == 2018]

# Extract only the columns you need
extracted_data = filtered_data[["date", "close", "volume", "market"]]

Step 4: Transform the Data #

Add custom calculations (like daily return) and fix scientific notation in the market column:

# Calculate daily percentage return
extracted_data["daily_return"] = extracted_data["close"].pct_change()

# Convert scientific notation in 'market' to plain integers
extracted_data["market"] = extracted_data["market"].apply(lambda x: f"{x:.0f}" if pd.notna(x) else x)

Step 5: Save the Processed Data #

Export your cleaned, filtered, and transformed data to a new Excel file:

extracted_data.to_excel("processed_dataset.xlsx", index=False)

For Extremely Large Files (100k+ Rows)

Use chunking to process the data in batches and avoid memory issues:

chunk_size = 10000  # Adjust based on your system's memory
processed_chunks = []

for chunk in pd.read_excel(
    "your_dataset.xlsx",
    chunksize=chunk_size,
    parse_dates=["date"],
    converters={
        "open": clean_numeric,
        "high": clean_numeric,
        "low": clean_numeric,
        "close": clean_numeric,
        "close_ratio": clean_numeric,
        "spread": clean_numeric
    }
):
    # Apply the same filtering/transformation to each chunk
    chunk_filtered = chunk[chunk["date"].dt.year == 2018]
    chunk_filtered["daily_return"] = chunk_filtered["close"].pct_change()
    chunk_filtered["market"] = chunk_filtered["market"].apply(lambda x: f"{x:.0f}" if pd.notna(x) else x)
    processed_chunks.append(chunk_filtered)

# Combine all chunks into one dataframe
final_data = pd.concat(processed_chunks)
final_data.to_excel("processed_large_dataset.xlsx", index=False)

If you prefer not to code, Excel can work for smaller chunks of your data (though it may lag with 100k+ rows):

• Clean Numeric Columns: Select columns like open, high, etc. Use Find & Replace (Ctrl+H) to swap commas with periods, then set the cell format to Number.
• Fix Date Format: Select the date column, go to Data > Text to Columns, choose Delimited, then skip delimiters and set the column data format to Date (DMY).
• Filter Rows: Click Data > Filter, then use the dropdown in the date column to select only 2018 dates.
• Extract & Transform: Copy the filtered rows to a new sheet, then use formulas like =(C2-B2)/B2 to calculate daily returns (adjust cell references to match your columns).

Key Notes #

• Always back up your original dataset before making any changes.
• Pandas is far more reliable for large datasets—Excel may freeze or crash with 100k+ rows.
• Double-check date formats and numeric cleaning to avoid calculation errors.

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