如何筛选变量总和达标Top/Bottom行及分时段占比达标企业

以下基于你提供的数据集，使用R语言的dplyr包解决两个数据筛选需求：

数据集导入 #

首先将你提供的数据导入R环境：

df <- structure(list(year = c(1998, 1998, 1998, 1998, 1998, 1998, 1998, 
1998, 1998, 1998, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 2000, 2000, 2000, 
2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2001, 
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
2001, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 
2002, 2002, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 
2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 
2003, 2003, 2003, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 
2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 
2004, 2004, 2004, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 
2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 
2005, 2005, 2005, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 
2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 
2006, 2006, 2006, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 
2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 
2007, 2007, 2007, 2007), id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 1, 2, 
3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 
21, 22, 23, 24, 25, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 
14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 1, 2, 3, 5, 6, 7, 
8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 
25, 1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 
20, 21, 22, 23, 24, 25, 1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 
14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 1, 2, 3, 5, 6, 7, 8, 
9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 1, 
2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 
21, 22, 23, 24, 1, 2, 3, 5, 6, 8, 9, 10, 11, 12, 13, 14, 16, 
17, 19, 20, 21, 22, 23, 24, 26, 27, 28), variable = c(2708, 4747, 
2605, 3614, 3531, 1830, 1043, 1964, 1268, 2279, 2170, 4675, 2910, 
2243, 2320, 1111, 4261, 1093, 3940, 4611, 3024, 3736, 2119, 1688, 
2688, 1989, 1270, 1437, 2431, 1676, 4837, 1351, 2395, 3094, 4726, 
4228, 1621, 2914, 3435, 3922, 4432, 4900, 2286, 2203, 4711, 2254, 
1869, 1655, 3617, 2056, 3984, 1009, 4204, 4240, 2478, 3832, 2776, 
4309, 1459, 3753, 3126, 3103, 3571, 1220, 1537, 3817, 4759, 3518, 
1934, 1425, 4038, 3027, 2357, 4243, 3735, 4198, 1042, 3252, 3357, 
1253, 3105, 1208, 2420, 1824, 1329, 4831, 4741, 3356, 3157, 3176, 
1763, 1775, 1202, 2594, 4705, 4376, 1492, 2594, 3520, 2351, 1245, 
1712, 3218, 2564, 1189, 4889, 1480, 4314, 4684, 3312, 3404, 2925, 
1411, 2642, 3415, 2681, 2101, 2160, 1555, 1181, 2111, 4142, 1461, 
3427, 1506, 4501, 3281, 4734, 3053, 3504, 1619, 1171, 3739, 3160, 
3739, 4453, 1744, 4743, 3584, 1072, 1096, 3425, 4479, 4971, 4199, 
1118, 4258, 2969, 3908, 2920, 2163, 2252, 1606, 3588, 3689, 3929, 
4751, 2911, 3170, 3238, 2523, 2288, 2778, 4714, 1851, 3496, 3255, 
3705, 4168, 4403, 1775, 2435, 2228, 1444, 1040, 4989, 2655, 3232, 
2671, 1314, 1515, 4322, 3553, 4386, 4396, 4602, 3007, 1651, 1524, 
1360, 3756, 1490, 4356, 1671, 4163, 4344, 4290, 1737, 1870, 3753, 
3766, 4184, 3309, 3734, 4715, 1630, 2394, 1106, 2759)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -209L))

library(dplyr)

问题1：选择n个顶部/底部行，使指定变量总和超过x #

思路 #

1. 按目标变量从大到小（顶部）或从小到大（底部）排序
2. 计算累计求和
3. 筛选累计和首次超过指定值x的所有行（包括刚好超过的那一行）

代码示例 #

选择顶部行，使variable总和超过50000

x <- 50000

top_rows <- df %>%
  arrange(desc(variable)) %>%  # 按variable降序排列
  mutate(cum_sum = cumsum(variable)) %>%  # 计算累计和
  filter(cum_sum <= x | row_number() == which(cum_sum > x)[1])  # 保留到累计和首次超过x的行

选择底部行，使variable总和超过10000

x <- 10000

bottom_rows <- df %>%
  arrange(variable) %>%  # 按variable升序排列
  mutate(cum_sum = cumsum(variable)) %>%
  filter(cum_sum <= x | row_number() == which(cum_sum > x)[1])

问题2：分时段筛选占比达指定比例的企业 #

思路 #

1. 给数据添加时段分组：1998-2004（含2004）、2005-2010（因数据仅到2007，实际为2005-2007）
2. 按时段和企业id分组，计算每个企业在对应时段的variable总和
3. 按时段分组，计算该时段的总variable总和，进而得到每个企业的占比
4. 筛选占比超过指定比例（如80%）的企业；若需筛选累计占比达80%的头部企业，可额外排序后计算累计占比

代码示例 #

第一步：添加时段分组

df_with_period <- df %>%
  mutate(period = case_when(
    year >= 1998 & year <= 2004 ~ "1998-2004",
    year >= 2005 & year <= 2010 ~ "2005-2010"
  ))

第二步：筛选单个企业占比达80%的结果

target_ratio <- 0.8

high_ratio_firms <- df_with_period %>%
  group_by(period, id) %>%
  summarise(firm_total = sum(variable), .groups = "drop_last") %>%
  mutate(period_total = sum(firm_total),
         ratio = firm_total / period_total) %>%
  filter(ratio >= target_ratio) %>%
  ungroup()

第三步：筛选累计占比达80%的头部企业（帕累托分析）

cumulative_high_ratio_firms <- df_with_period %>%
  group_by(period, id) %>%
  summarise(firm_total = sum(variable), .groups = "drop_last") %>%
  arrange(desc(firm_total)) %>%
  mutate(period