基于dplyr优化百万级路径数据的最后非直接属性计算

Got it, dealing with million-row datasets using for loops is a total pain—30 minutes is way too long. Let's use dplyr (and optionally tidyr) to vectorize this operation, which will cut down your runtime drastically.

First, Recap the Requirements #

For each path:

1. Grab the last non-direct channel (direct includes direct, direct_web, direct_app)
2. If all channels are direct, take the last direct value
3. If there are no direct channels, take the last channel in the path

Step 1: Replicate Your Input Data #

First, let's set up your input dataframe for reference:

path = c("path1","path2","path3","path4","path5","path6","path7")
c1 = c("channel1","direct_app","direct","channel45","channel33","direct_web","direct_web")
c2 = c("channel2",NA,"channel23",NA,"channel11","channel5", "direct_app")
c3 = c("direct_app",NA,"direct_app",NA, NA,"direct_app",NA)
c4 = c(NA,NA,"direct_app",NA,NA,NA,NA)
c5 = c(NA,NA,"direct_web",NA,NA,NA,NA)
df_input <- data.frame(path,c1,c2,c3,c4,c5)

Step 2: Efficient Solution with dplyr + tidyr #

This approach uses long-format data and grouping, which is optimized for large datasets:

library(dplyr)
library(tidyr)

# Define all direct channel values
direct_channels <- c("direct", "direct_web", "direct_app")

df_output <- df_input %>%
  # Reshape to long format, dropping NA values
  pivot_longer(
    cols = starts_with("c"),
    names_to = "column_order",
    values_to = "channel",
    values_drop_na = TRUE
  ) %>%
  # Group by each path to process individually
  group_by(path) %>%
  mutate(
    # Flag if the channel is direct
    is_direct = channel %in% direct_channels,
    # Determine which row is our target:
    # - If there are non-direct channels, pick the last one
    # - If all are direct, pick the last channel in the path
    is_target = case_when(
      any(!is_direct) ~ !is_direct & row_number() == max(row_number()[!is_direct]),
      TRUE ~ row_number() == n()
    )
  ) %>%
  # Keep only the target row for each path
  filter(is_target) %>%
  ungroup() %>%
  # Join back to the original dataframe to keep all columns
  select(path, last_non_direct = channel) %>%
  right_join(df_input, by = "path") %>%
  # Reorder columns to match original + new column
  select(path, c1:c5, last_non_direct)

Alternative: Vectorized apply Method #

If you prefer a more compact approach without reshaping data, this apply method is also much faster than a raw for loop:

direct_channels <- c("direct", "direct_web", "direct_app")

# Define a helper function to process a single row
get_last_valid <- function(row) {
  # Remove NA values from the row
  cleaned_row <- na.omit(row)
  
  # Handle edge case where all values are NA (adjust if needed)
  if (length(cleaned_row) == 0) return(NA)
  
  # Find indices of non-direct channels
  non_direct_idx <- !cleaned_row %in% direct_channels
  
  # Return the correct value based on our rules
  if (any(non_direct_idx)) {
    tail(cleaned_row[non_direct_idx], 1)
  } else {
    tail(cleaned_row, 1)
  }
}

# Apply the function to each row of the channel columns
df_output <- df_input %>%
  mutate(last_non_direct = apply(select(., c1:c5), 1, get_last_valid))

Verify the Result #

Both methods will give you this output, which matches your requirements:

Why This Is Faster #

• These methods use vectorized operations instead of iterative for loops, which are optimized in R's C backend.
• The dplyr/tidyr approach leverages efficient grouping and reshaping that scales well for large datasets (even millions of rows).

You should see runtime drop from 30 minutes to just a few minutes (or less, depending on your hardware).

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