1955-2023年每日.xls文件合并与GROUPED区域年度聚合求助

Prerequisites #

• Install pandas if you haven't already: pip install pandas openpyxl (openpyxl is required for reading/writing .xlsx files)

Step 1: Merge Daily MEAN Columns #

This code will iterate through all your daily Excel files, extract the required columns, and merge them into a single dataframe with GROUPED as the row index.

import pandas as pd
import os

# Replace with the actual path to your folder containing the .xls files
data_directory = "/path/to/your/excel/files"

daily_data_frames = []

# Loop through all files in the directory
for filename in os.listdir(data_directory):
    if filename.startswith("day_") and filename.endswith(".tif.xls"):
        # Parse year and day of year from the filename
        file_parts = filename.split("_")
        year = file_parts[1]
        day_of_year = file_parts[2].split(".")[0]
        new_col_name = f"{year}_{day_of_year}"
        
        # Read only the required columns from the Excel file
        df = pd.read_excel(
            os.path.join(data_directory, filename),
            usecols=["GROUPED", "MEAN"]
        )
        
        # Set GROUPED as index and rename MEAN column to year_day format
        df = df.set_index("GROUPED").rename(columns={"MEAN": new_col_name})
        daily_data_frames.append(df)

# Combine all daily dataframes into one (columns are added side-by-side)
merged_daily_df = pd.concat(daily_data_frames, axis=1)

# Save the merged data to a new Excel file
merged_daily_df.to_excel("merged_daily_data.xlsx")

Step 2: Annual Aggregation #

After merging the daily data, this code will aggregate all columns from the same year into a single annual value (using mean by default—adjust the aggregation function as needed).

# Group columns by their year
year_column_groups = {}
for column in merged_daily_df.columns:
    year = column.split("_")[0]
    if year not in year_column_groups:
        year_column_groups[year] = []
    year_column_groups[year].append(column)

# Calculate annual aggregated values (mean used here; replace with sum/median/etc. if needed)
annual_aggregated_df = pd.DataFrame()
for year, columns in year_column_groups.items():
    annual_aggregated_df[f"{year}_annual_mean"] = merged_daily_df[columns].mean(axis=1)

# Keep GROUPED as the index
annual_aggregated_df.index = merged_daily_df.index

# Save the aggregated data
annual_aggregated_df.to_excel("annual_aggregated_data.xlsx")

Key Notes #

• Missing Files: If some daily files are missing, the code will skip them without throwing an error. You can add a check to log missing dates if needed.
• Column Validation: Add a check (e.g., if "GROUPED" in df.columns and "MEAN" in df.columns) to avoid errors if any file lacks the required columns.
• Aggregation Function: Replace .mean(axis=1) with .sum(axis=1), .median(axis=1), or other pandas aggregation methods based on your needs.

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