如何用dplyr计算各城市关闭门店的占比

我有一份包含多个城市及对应营业、关闭门店的数据集，其中Store.Status字段中"A"代表营业（开放），"I"代表停业（关闭）。我希望使用dplyr计算每个城市的关闭门店占比。

我原本的代码如下：

df %>% 
  group_by(City,Store.Status) %>% 
  summarise(Num_stores=n(),
  Percent_closed = ??? / Num_stores) %>%   
  arrange(desc(Num_stores))

但这段代码的输出仅返回各城市的分状态门店数，无法得到关闭门店数/总门店数的占比。我期望的输出格式如下：

City      Num_store       Percent_closed
--------------------------------------------------------------
Ackley       3                (1/3)
Adair        2                (0/2)

其中Num_store是该城市的总门店数（含营业、停业），Percent_closed是该城市停业门店（Store.Status为"I"）占总门店数的比例。

数据集示例：

> dput(head(iowa, 20))
structure(list(Store = c(2656L, 2657L, 2674L, 2835L, 2954L, 3013L, 
3041L, 3045L, 3162L, 3354L, 3385L, 3886L, 3738L, 3740L, 3831L, 
3833L, 3838L, 3872L, 3873L, 3879L), Name = c("Hy-Vee Food Store / Corning", 
"Hy-Vee Food Store / Bedford", "Hy-Vee Food Store / Lamoni", 
"CVS Pharmacy #8538 / Cedar Falls", "Dahl's / Ingersoll", "Keith's Foods", 
"Shugar's Super Valu / Colfax", "Britt Food Center", "Nash Finch / Wholesale Food", 
"Sam's Club 8238 / Davenport", "Sam's Club 8162 / Cedar Rapids", 
"Wal-Mart 0646 / Anamosa", "Hartig Drug Company #4 / Dubuque", 
"Hometown Foods / Waterloo", "The Market Of Madrid", "Wal-Mart 3394 / Atlantic", 
"Schnucks / Bettendorf", "Target Store T-0086 / Dubuque", "Target Store T-1113 / Coralville", 
"Target Store T-1800 / Sioux City"), Store.Status = c("A", "A", 
"A", "A", "I", "A", "A", "A", "I", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "A"), Address = c("300  10th St", "1604 Bent", 
"720 East Main", "2302 West First St", "3425 Ingersoll", "207 E Locust St", 
"28 E Howard", "8 2nd St NW", "807 Grandview", "3845 Elmore Ave.", 
"2605 Blairs Ferry Rd NE", "101  115 St", "2225 Central Ave", 
"1010 E Mitchell Ave", "301 Annex Rd", "1905 East 7th St", "858 Middle Rd", 
"3500 Dodge St", "1441 Coral Ridge Ave", "5775 Sunnybrook Dr"
), City = c("Corning", "Bedford", "Lamoni", "Cedar Falls", "Des Moines", 
"Bloomfield", "Colfax", "Britt", "Muscatine", "Davenport", "Cedar Rapids", 
"Anamosa", "Dubuque", "Waterloo", "Madrid", "Atlantic", "Bettendorf", 
"Dubuque", "Coralville", "Sioux City"), State = c("IA", "IA", 
"IA", "IA", "IA", "IA", "IA", "IA", "IA", "IA", "IA", "IA", "IA", 
"IA", "IA", "IA", "IA", "IA", "IA", "IA"), Zip.Code = c("51632", 
"50833", "50140", "50613", "50300", "52537", "50054", "50423", 
"52761", "52807", "52402", "52205", "52001", "50702", "50156", 
"50022", "52722", "52003", "52241", "51106"), Store.Address = c("300 10th St\nCorning, IA 51632\n(40.991861, -94.731809)", 
"1604 Bent\nBedford, IA 50833\n(40.676171, -94.725578)", "720 East Main\nLamoni, IA 50140\n(40.623647, -93.924475)", 
"2302 West First St\nCedar Falls, IA 50613\n(42.539874, -92.472778)", 
"3425 Ingersoll\nDes Moines, IA 50300\n(41.586313, -93.663337)", 
"207 E Locust St\nBloomfield, IA 52537\n(40.752691, -92.412847)", 
"28 E Howard\nColfax, IA 50054\n(41.677932, -93.244443)", "8 2nd St NW\nBritt, IA 50423\n(43.098696, -93.801917)", 
"807 Grandview\nMuscatine, IA 52761\n(41.408437, -91.064113)", 
"3845 Elmore Ave.\nDavenport, IA 52807\n(41.559731, -90.527081)", 
"2605 Blairs Ferry Rd NE\nCedar Rapids, IA 52402\n(42.031819, -91.67969)", 
"101 115 St\nAnamosa, IA 52205", "2225 Central Ave\nDubuque, IA 52001\n(42.51384, -90.671531)", 
"1010 E Mitchell Ave\nWaterloo, IA 50702\n(42.476639, -92.343118)", 
"301 Annex Rd\nMadrid, IA 50156\n(41.87894, -93.815365)", "1905 East 7th St\nAtlantic, IA 50022\n(41.403853, -94.98571)", 
"858 Middle Rd\nBettendorf, IA 52722\n(41.539421, -90.520013)", 
"3500 Dodge St\nDubuque, IA 52003\n(42.491944, -90.720589)", 
"1441 Coral Ridge Ave\nCoralville, IA 52241\n(41.691276, -91.608399)", 
"5775 Sunnybrook Dr\nSioux City, IA 51106\n(42.448939, -96.332446)"
), Report.Date = c("10/01/2018", "10/01/2018", "10/01/2018", 
"10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", 
"10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", 
"10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", "10/01/2018", 
"10/01/2018", "10/01/2018"), Inactive = c(FALSE, FALSE, FALSE, 
FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)), row.names = c(NA, 
20L), class = "data.frame")

原代码的问题在于同时按City和Store.Status分组，导致每个城市的营业/停业门店被分开统计，无法直接计算总门店数和关闭占比。正确的做法是只按City分组，在分组内完成总门店数、关闭门店数的统计，再格式化占比为分数形式。

正确代码 #

library(dplyr)

df %>%
  group_by(City) %>%
  summarise(
    Num_store = n(),  # 统计每个城市的总门店数
    closed_count = sum(Store.Status == "I"),  # 统计关闭门店数
    Percent_closed = paste0("(", closed_count, "/", Num_store, ")")  # 格式化为期望的分数形式
  ) %>%
  arrange(desc(Num_store))  # 按总门店数降序排列

代码解释 #

• group_by(City)：仅按城市分组，确保每个分组对应一个城市的所有门店
• Num_store = n()：计算当前城市的总门店数量
• closed_count = sum(Store.Status == "I")：通过逻辑判断（Store.Status == "I"返回TRUE/FALSE，对应1/0）求和，得到关闭门店的数量
• Percent_closed = paste0(...)：将关闭数和总数拼接成(x/y)的格式
• arrange(desc(Num_store))：按总门店数从多到少排序

示例数据运行结果 #

用你提供的20行示例数据运行后，会得到如下结果（截取部分）：

# A tibble: 18 × 3
   City        Num_store closed_count Percent_closed
   <chr>           <int>        <int> <chr>         
 1 Dubuque             2            0 (0/2)         
 2 Coralville          1            0 (0/1)         
 3 Sioux City          1            0 (0/1)         
 4 Bettendorf          1            0 (0/1)         
 5 Atlantic            1            0 (0/1)         
 6 Madrid              1            0 (0/1)         
 7 Waterloo            1            0 (0/1)         
 8 Anamosa             1            0 (0/1)         
 9 Cedar Rapids        1            0 (0/1)         
10 Davenport           1            0 (0/1)         
11 Muscatine           1            1 (1/1)         
12 Britt               1            0 (0/1)         
13 Colfax              1            0 (0/1)         
14 Bloomfield          1            0 (0/1)         
15 Des Moines          1            1 (1/1)         
16 Cedar Falls         1            0 (0/1)         
17 Lamoni              1            0 (0/1)         
18 Bedford             1            0 (0/1)         

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