如何使用Python遍历日期匹配对应列，填充temperature列？

Hey there! Let's work through this problem together—transforming that wide temperature table into a slimmer, focused dataset makes total sense, and pandas is perfect for this job. Here's a step-by-step approach that's flexible enough to match your column naming rules:

Step 1: Load and prep your data #

First, make sure you've got pandas imported, then read in your file (adjust the file path/type to match your actual data, like .csv or .xlsx):

import pandas as pd

# Load your dataset
df = pd.read_csv("your_temperature_data.csv")  # Use pd.read_excel if it's an Excel file

Next, convert your DateFin column to a proper datetime format—this lets us easily extract day-of-year or month/day info later:

df["DateFin"] = pd.to_datetime(df["DateFin"])

Step 2: Match DateFin to the right temperature column #

Your column naming rule (e.g., b1_T_M01 for Jan 1) suggests each column corresponds to a day of the year. Let's generate the exact column name for each row's DateFin:

If your columns use day-of-year numbering (e.g., b1_T_M001 = Jan 1, b1_T_M365 = Dec 31):

Use pandas' dt.dayofyear to get the numeric day of the year, then format it to match your column's padding (adjust str.zfill(3) to str.zfill(2) if your columns use two digits like M01 instead of M001):

# Create a helper column with the exact temperature column name for each row
df["temp_col"] = "b1_T_M" + df["DateFin"].dt.dayofyear.astype(str).str.zfill(3)

# Pull the temperature value from the matching column
df["temperature"] = df.apply(lambda row: row[row["temp_col"]], axis=1)

If your columns use month-day numbering (e.g., b1_T_M0101 = Jan 1, b1_T_M1231 = Dec 31):

If your columns follow a MMDD pattern instead, use dt.strftime to format the date into that string:

df["temp_col"] = "b1_T_M" + df["DateFin"].dt.strftime("%m%d")
df["temperature"] = df.apply(lambda row: row[row["temp_col"]], axis=1)

Step 3: Trim down to your desired columns #

Now that you've got the temperature column filled, you can drop all the extra daily temperature columns and keep only what you need:

# Keep just DateFin and the new temperature column
df_final = df[["DateFin", "temperature"]].copy()

Bonus: Handle edge cases #

If your DateFin includes leap days (Feb 29) but your dataset only has 365 columns (for a non-leap year), add a check to avoid errors—this will fill those rows with pd.NA (a nullable missing value):

df["temperature"] = df.apply(
    lambda row: row.get(row["temp_col"], pd.NA),  # Use .get() to safely handle missing columns
    axis=1
)

That's it! This approach iterates through each row, matches the right temperature column, and gives you a clean, slimmed-down dataset.

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