如何基于多列条件在Pandas中创建新列？万级DataFrame场景实现

Hey there! Let's break down how to solve this problem—since you've got a large daily 15-minute interval dataset (over 1 year, ~10k rows) with columns Date (A), Time (B), and Value (C), we can use pandas to efficiently extract values for fixed times per date and build your df_2.

Step 1: Ensure your data types are correct #

First, make sure your Date and Time columns are properly formatted as date/time types (not strings) to avoid bugs:

import pandas as pd

# Convert Date to date type, Time to time type
df['Date'] = pd.to_datetime(df['Date']).dt.date
df['Time'] = pd.to_datetime(df['Time']).dt.time

Method 1: Extract a single fixed time (e.g., 09:00:00) #

If you want df_2 to contain each date paired with the Value from a specific time (say, 9 AM), use boolean filtering first, then clean up the result:

# Define your target time (adjust this to your fixed time)
target_time = pd.to_datetime('09:00:00').time()

# Filter rows matching the target time, then keep only Date and Value
df_2 = df[df['Time'] == target_time][['Date', 'Value']]
# Rename the Value column to make it clear what time it's for
df_2 = df_2.rename(columns={'Value': f'Value_at_{target_time}'}).reset_index(drop=True)

If you also want to add this fixed-time value as a new column to your original DataFrame (so every row for a date has the 9 AM value), use a merge:

df_with_new_col = pd.merge(df, df_2, on='Date', how='left')

Method 2: Handle multiple fixed times at once #

If you need values from several fixed times (e.g., 09:00 and 15:00), use a pivot table to reshape your data:

# Pivot the data: Date as rows, Time as columns, Value as the cell values
pivot_df = df.pivot(index='Date', columns='Time', values='Value').reset_index()

# Define your list of target times
target_times = [
    pd.to_datetime('09:00:00').time(),
    pd.to_datetime('15:00:00').time()
]

# Build df_2 with just the Date and your target time columns
df_2 = pivot_df[['Date'] + target_times]
# Rename columns for clarity
df_2.columns = ['Date'] + [f'Value_at_{t}' for t in target_times]

Edge case: Missing data for a date + fixed time #

If some dates don't have a value for your target time, the code above will leave those entries as NaN. You can handle this by filling with a default value, interpolating, or forward-filling from the previous day:

# Example: Fill NaNs with the previous day's value
df_2 = df_2.fillna(method='ffill')

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