在R中实现跨两个DataFrame的区间Sumifs求和需求

Hey there! Since you're new to R, let's break this down simply—you can absolutely replicate Excel's SUMIFS for interval matching, and I'll show you two straightforward ways to do it. First, let's start with some sample data so you can follow along (I'll assume your data looks something like this, but you can swap in your own):

Sample Data Setup #

First, let's create a sample interval DataFrame (with your from/to columns) and a dummy DataFrame (with dummy_time and dummy_var):

# Interval DataFrame: each row is a time range
intervals_df <- data.frame(
  group_id = c("Range1", "Range2", "Range3"), # Optional: helps track which range results belong to
  from = c(10, 20, 30),
  to = c(19, 29, 39)
)

# Dummy DataFrame: the values we want to sum based on time range
dummy_df <- data.frame(
  dummy_time = c(12, 15, 22, 28, 33, 40, 18),
  dummy_var = c(5, 3, 7, 2, 4, 1, 6)
)

Method 1: Using tidyverse + fuzzyjoin (Recommended for Readability) #

This approach uses popular R packages that make data manipulation intuitive for beginners. First, install and load the packages if you haven't already:

install.packages(c("dplyr", "fuzzyjoin"))
library(dplyr)
library(fuzzyjoin)

Now, we'll use a fuzzy join to match every row in dummy_df to the interval it falls into, then sum the dummy_var values per interval:

sumifs_result <- fuzzy_left_join(
  intervals_df,
  dummy_df,
  by = c("from" = "dummy_time", "to" = "dummy_time"),
  match_fun = list(`<=`, `>=`) # Match where dummy_time >= from AND dummy_time <= to
) %>%
  group_by(group_id, from, to) %>%
  summarise(total_dummy = sum(dummy_var, na.rm = TRUE)) %>% # Sum values, ignore NAs if no matches
  ungroup()

# View the result
print(sumifs_result)

Quick Notes:

• If you need to adjust the interval rules (e.g., left-open, right-closed), just tweak the match_fun symbols (swap <= for <, etc.)
• Add replace_na(total_dummy = 0) after summarise() if you want empty intervals to show 0 instead of NA

Method 2: Base R (No Extra Packages Needed) #

If you don't want to install new packages, you can use base R functions to loop through each interval and calculate the sum:

# Using dplyr's rowwise() (still beginner-friendly, no niche packages)
base_result <- intervals_df %>%
  rowwise() %>%
  mutate(total_dummy = sum(dummy_var[dummy_time >= from & dummy_time <= to], na.rm = TRUE)) %>%
  ungroup()

# Or pure base R with apply():
pure_base_result <- cbind(
  intervals_df,
  total_dummy = apply(intervals_df, 1, function(row) {
    sum(dummy_df$dummy_var[dummy_df$dummy_time >= as.numeric(row["from"]) & dummy_df$dummy_time <= as.numeric(row["to"])], na.rm = TRUE)
  })
)

# View either result
print(base_result)

Key Tips for Your Own Data: #

• If your DataFrames have grouping columns (e.g., different categories), add those to the by argument in fuzzy_join or the filter condition in base R to ensure you only sum matching groups
• Double-check your interval boundaries (inclusive/exclusive) to match exactly what you need from Excel's SUMIFS

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