基于其他列多条件计算新值（附过滤后数据集）

Got it, let's walk through how to calculate a new value based on multiple conditions from other columns using your provided dataset. First, let's make the dataset easier to read with a formatted table:

Let's use a practical example: we'll create a new column Session_Status based on these rules:

• Mark as Valid if End_Session isn't missing AND Start_Session is earlier than End_Session
• Mark as High Volume if the same Session_Number + Location group has Lines greater than 3
• All other cases get Invalid

Python (Pandas) Implementation #

Pandas makes this straightforward with np.select for multi-condition logic, plus group transforms for grouped checks:

import pandas as pd
import numpy as np

# Load your data (matching the table above)
df = pd.DataFrame({
    "ID": [1,2,3,4,5,6,7,93],
    "Date": ["2017-02-02"]*7 + ["2017-03-26"],
    "Location": ["FSZ5"]*3 + ["FSZ6"]*4 + ["FSZ1"],
    "Method": ["Trolling"]*3 + ["Bottom"]*5,
    "Lines": [2,2,2,5,5,5,5,3],
    "Session_Number": [1]*3 + [2]*4 + [3],
    "Start_Session": ["07:11"]*3 + ["08:05"]*4 + ["18:28"],
    "End_Session": [pd.NA, pd.NA, "07:49", "07:49", "07:49", "07:49", "07:49", "18:23"]
})

# Convert time columns to time objects for comparison
df["Start_Session"] = pd.to_datetime(df["Start_Session"], format="%H:%M").dt.time
df["End_Session"] = pd.to_datetime(df["End_Session"], format="%H:%M", errors="coerce").dt.time

# Define conditions and their corresponding values
conditions = [
    # Condition 1: Valid session time
    (df["End_Session"].notna()) & (df["Start_Session"] < df["End_Session"]),
    # Condition 2: High volume in the session group
    df.groupby(["Session_Number", "Location"])["Lines"].transform("max") > 3
]
status_choices = ["Valid", "High Volume"]

# Apply conditions to create the new column
df["Session_Status"] = np.select(conditions, status_choices, default="Invalid")

# Check the result
print(df[["ID", "Session_Status"]])

Output: #

ID Session_Status
0    1        Invalid
1    2        Invalid
2    3          Valid
3    4    High Volume
4    5    High Volume
5    6    High Volume
6    7    High Volume
7   93        Invalid

R (dplyr) Implementation #

For R users, dplyr::case_when is ideal for readable multi-condition logic, and we can use .by for grouped calculations:

library(dplyr)
library(lubridate)

# Build the dataset
df <- tibble(
  ID = c(1,2,3,4,5,6,7,93),
  Date = c(rep("2017-02-02",7), "2017-03-26"),
  Location = c(rep("FSZ5",3), rep("FSZ6",4), "FSZ1"),
  Method = c(rep("Trolling",3), rep("Bottom",5)),
  Lines = c(2,2,2,5,5,5,5,3),
  Session_Number = c(rep(1,3), rep(2,4), 3),
  Start_Session = c(rep("07:11",3), rep("08:05",4), "18:28"),
  End_Session = c(NA, NA, "07:49", rep("07:49",4), "18:23")
)

# Calculate the new status column
df <- df %>%
  # Convert times to period objects for comparison
  mutate(
    Start_Session = hm(Start_Session),
    End_Session = hm(End_Session)
  ) %>%
  # Check if the session group has high volume
  mutate(
    is_high_volume = max(Lines) > 3,
    .by = c(Session_Number, Location)
  ) %>%
  # Apply condition logic
  mutate(
    Session_Status = case_when(
      !is.na(End_Session) & Start_Session < End_Session ~ "Valid",
      is_high_volume ~ "High Volume",
      TRUE ~ "Invalid"
    ),
    # Clean up the helper column
    is_high_volume = NULL
  )

# View the result
print(df %>% select(ID, Session_Status))

Output: #

# A tibble: 8 × 2
     ID Session_Status
  <dbl> <chr>         
1     1 Invalid       
2     2 Invalid       
3     3 Valid         
4     4 High Volume   
5     5 High Volume   
6     6 High Volume   
7     7 High Volume   
8    93 Invalid       

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