如何在R中按城市计算连续0段的Sales中位数及对应Count值

First, let's fix the small typo in your dataset: the first column should be named City (since your code references x$City), not Group. Assuming your data is loaded into a data frame df with columns City, Sales, flag, Count, here's how to solve your problem:

Key Issues with Your Original Code #

Your current code groups by city and consecutive flag segments, but it replaces the Sales value with the median and returns the first row of each segment. This doesn't link the median to its corresponding Count value—especially problematic when the segment has an even number of observations (since the median is an average of two values, not present in the data itself).

Solution Approach #

We need to:

1. Split each city's data into consecutive segments where flag is the same.
2. Focus only on segments where flag = 0.
3. For each segment:
   • Calculate the median of Sales.
   • Identify the row(s) corresponding to the middle value(s) of the sorted Sales (one row for odd counts, two rows for even counts).
   • Attach the median value to these rows (even if it's an average of two values).

Option 1: Using dplyr (Readable & Intuitive) #

This approach is easier to follow and handles both odd/even segment sizes clearly:

library(dplyr)

# Process the data
result <- df %>%
  # Group by city to create segment IDs for consecutive flags
  group_by(City) %>%
  mutate(segment = cumsum(c(1, diff(flag) != 0))) %>%
  ungroup() %>%
  # Keep only segments where flag is 0
  filter(flag == 0) %>%
  # Group by city and segment to analyze each 0-segment
  group_by(City, segment) %>%
  mutate(
    num_obs = n(),
    sorted_sales = sort(Sales),
    median_sales = median(Sales),
    # Mark rows that are the middle values in the segment
    is_middle = case_when(
      num_obs %% 2 == 1 ~ Sales == sorted_sales[(num_obs + 1) %/% 2],
      num_obs %% 2 == 0 ~ Sales %in% sorted_sales[c(num_obs %/% 2, num_obs %/% 2 + 1)]
    )
  ) %>%
  # Keep only the middle rows
  filter(is_middle) %>%
  # Clean up unnecessary columns
  select(-sorted_sales, -num_obs) %>%
  ungroup()

# View the result
print(result)

Option 2: Base R (No External Libraries) #

If you prefer base R, here's a modified version of your original code that correctly links the median to its Count value:

# Create segment IDs for consecutive flags per city
df$segment <- with(df, ave(flag, City, FUN = function(x) cumsum(c(1, diff(x) != 0))))

# Function to process each 0-segment
process_segment <- function(seg) {
  if (seg$flag[1] != 0) return(NULL)
  
  n <- nrow(seg)
  sorted_sales <- sort(seg$Sales)
  
  if (n %% 2 == 1) {
    # Odd number of observations: pick the middle value
    med_val <- sorted_sales[(n + 1) %/% 2]
    seg_result <- seg[seg$Sales == med_val, ]
    seg_result$median_sales <- med_val
  } else {
    # Even number: pick both middle values, compute average median
    med_vals <- sorted_sales[c(n %/% 2, n %/% 2 + 1)]
    seg_result <- seg[seg$Sales %in% med_vals, ]
    seg_result$median_sales <- mean(med_vals)
    seg_result$median_type <- "even_middle_pair"
  }
  
  return(seg_result)
}

# Apply function to each (City, segment) group
result_list <- by(df, list(df$City, df$segment), process_segment)
result_list <- Filter(Negate(is.null), result_list)
final_result <- do.call(rbind, result_list)
rownames(final_result) <- NULL

# View the result
print(final_result)

Handling Even Segment Sizes #

When a segment has an even number of observations, the median is the average of the two middle Sales values. Both solutions return both rows corresponding to these middle values, along with the computed median. For example, in the New York segment with 6 observations, you'll get rows for Sales = 6542 (Count=11) and Sales=6616 (Count=27), with median_sales = 6579 (the average of the two).

If you only want one of the two rows for even segments, you can adjust the filter (e.g., take the first middle row with slice(1) in dplyr, or seg_result[1, ] in base R).

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