如何在R中汇总2021年艾姆斯每日酒类销售核心指标

已完成的前置操作 #

数据导入与经纬度提取代码 #

# 导入数据
if (!file.exists("ames-liquor.rds")) { 
  url <- "https://github.com/ds202-at-ISU/materials/blob/master/03_tidyverse/data/ames-liquor.rds?raw=TRUE" 
  download.file(url, "ames-liquor.rds", mode="wb") 
} 
data <- readRDS("ames-liquor.rds")

# 提取经纬度
data <- data %>% separate(remove= FALSE, col = 'Store Location' , sep=" ", into=c("toss-it", "Latitude", "Longitude")) 
data <- data %>% mutate( Latitude = parse_number(Latitude), Longitude = parse_number(Longitude) )

数据结构（dput(head(data))输出） #

structure(list(`Invoice/Item Number` = c("INV-31574300001", "INV-28409200221", "INV-20965600005", "INV-20840300018", "INV-20413100006", "INV-28435900006"), 
               Date = c("11/02/2020", "07/01/2020", "07/31/2019", "07/25/2019", "07/05/2019", "07/02/2020"), 
               `Store Number` = structure(c(1L, 1L, 1L, 1L, 1L, 1L), levels = "Store Number", class = "factor"), 
               `Store Name` = c("Hy-Vee  #2 / Ames", "Hy-Vee Food Store #1 / Ames", "Hy-Vee Food Store #1 / Ames", "Kum & Go #1215 / Ames", "Kum & Go #1215 / Ames", "Cyclone Liquors"), 
               Address = c("640 Lincolnway", "3800 W Lincoln Way", "3800 W Lincoln Way", "4506 Lincoln Way", "4506 Lincoln Way", "626 Lincoln Way"), 
               City = c("Ames", "Ames", "Ames", "Ames", "Ames", "Ames"), 
               `Zip Code` = structure(c(1L, 1L, 1L, 1L, 1L, 1L), levels = "Zip Code", class = "factor"), 
               `County Number` = structure(c(1L, 1L, 1L, 1L, 1L, 1L), levels = "County Number", class = "factor"), 
               County = c("STORY", "STORY", "STORY", "STORY", "STORY", "STORY"), 
               Category = structure(c(1L, 1L, 1L, 1L, 1L, 1L), levels = "Category", class = "factor"), 
               `Category Name` = c("Cocktails /RTD", "Mixto Tequila", "Canadian Whiskies", "Canadian Whiskies", "Canadian Whiskies", "Imported Vodkas"), 
               `Vendor Number` = c("626", "395", "260", "260", "260", "260"), 
               `Vendor Name` = c("JDSO INC / Red Boot Distillery", "PROXIMO", "DIAGEO AMERICAS", "DIAGEO AMERICAS", "DIAGEO AMERICAS", "DIAGEO AMERICAS"), 
               `Item Number` = structure(c(1L, 1L, 1L, 1L, 1L, 1L), levels = "Item Number", class = "factor"), 
               `Item Description` = c("Oxtails Rum Punch", "Jose Cuervo Especial Silver", "Crown Royal Regal Apple Mini", "Crown Royal Regal Apple", "Crown Royal Regal Apple", "Ketel One"), 
               Pack = c(6, 6, 10, 24, 24, 6), 
               `Bottle Volume (ml)` = c(1750, 1750, 300, 375, 375, 1750), 
               `State Bottle Cost` = c(5.97, 21, 7.35, 8, 8, 22), 
               `State Bottle Retail` = c(8.96, 31.5, 11.03, 12, 12, 33), 
               `Bottles Sold` = c(6, 2, 6, 4, 4, 6), 
               `Sale (Dollars)` = c(53.76, 63, 66.18, 48, 48, 198), 
               `Volume Sold (Liters)` = c(10.5, 3.5, 1.8, 1.5, 1.5, 10.5), 
               `Volume Sold (Gallons)` = c(2.77, 0.92, 0.47, 0.39, 0.39, 2.77), 
               `Store Location` = c("POINT (-93.619455 42.022848)", "POINT (-93.669896 42.02160500000001)", "POINT (-93.669896 42.02160500000001)", NA, NA, "POINT (-93.618911 42.022854)"), 
               `toss-it` = c("POINT", "POINT", "POINT", NA, NA, "POINT"), 
               Latitude = c(-93.619455, -93.669896, -93.669896, NA, NA, -93.618911), 
               Longitude = c(42.022848, 42.021605, 42.021605, NA, NA, 42.022854)), 
          row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

需求 #

统计2021年艾姆斯的每日酒类销售，需汇总以下指标：

• 销售笔数
• 售酒体积（以升为单位）
• 销售金额

实现代码 #

library(tidyverse)
library(lubridate)

# 处理日期并按日汇总2021年艾姆斯销售数据
daily_sales_2021 <- data %>%
  # 将字符串日期转为标准日期格式（原格式为MM/DD/YYYY）
  mutate(Date = mdy(Date)) %>%
  # 筛选艾姆斯地区2021年的数据
  filter(City == "Ames", year(Date) == 2021) %>%
  # 按日期分组
  group_by(Date) %>%
  # 计算每日汇总指标
  summarize(
    销售笔数 = n(),
    总售酒体积_升 = sum(`Volume Sold (Liters)`, na.rm = TRUE),
    总销售金额_美元 = sum(`Sale (Dollars)`, na.rm = TRUE)
  ) %>%
  # 取消分组
  ungroup()

# 查看结果示例
head(daily_sales_2021)

代码说明 #

1. 日期转换：用lubridate包的mdy()函数将原字符串格式的日期转为可操作的日期类型，便于按年份筛选。
2. 数据筛选：精准保留艾姆斯地区2021年的销售记录。
3. 分组汇总：按日期分组后，用n()统计每日销售笔数，用sum()计算售酒体积和销售金额的总和，na.rm = TRUE用于忽略缺失值对计算的影响。

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