如何绘制以年份为X轴、占比为Y轴的双折线图？附分州占比表需求

我正在研究两种处方药（仿制药DIMETHYL F与品牌药TECFIDERA）的数据，包含state、year、quarter、product_name、number_of_prescriptions等变量。目前已写出R代码绘制出以yr_q（年月季度）为X轴、处方总数为Y轴的双折线图，但需要把Y轴改成对应时间段内的处方占比（单种药处方数/两种药总处方数）。同时还需要生成按state和year拆分的处方占比表格。尝试过创建哑变量、柱状图等方法但没解决问题，求技术帮助。

现有代码 #

alldrugs %>%
  filter(product_name == "DIMETHYL F" | product_name == "TECFIDERA ") %>%
  mutate(yr_q = yq(paste(year, quarter)), number_of_prescriptions = as.numeric(x = number_of_prescriptions)) %>%
  group_by(product_name, yr_q)%>%
  summarize(Prescription.count = sum(number_of_prescriptions)) %>%
  ggplot(aes(x = yr_q, y = Prescription.count)) + geom_line(aes(colour=product_name), size = 1.4) +
  xlab("2020-2022") + 
  ylab("Number of Prescriptions") + 
  labs(colour = "Generic vs Name Brand") +
  theme_bw()

数据子集 #

structure(list(state = c("AK", "CA", "OR", "MN", "NY", "UT",  "AK", "AK", "CA", "NJ", "AK", "NY", "AK", "AK", "AK", "AK", "AK",  "AK", "SC", "NC", "NM", "AK", "AK", "AK", "CA"), year = c("2020",  "2020", "2020", "2020", "2021", "2021", "2021", "2021", "2022",  "2022", "2022", "2022", "2021", "2020", "2021", "2021", "2020",  "2020", "2021", "2021", "2021", "2021", "2022", "2022", "2022" ), quarter = c("1", "2", "3", "4", "1", "2", "3", "4", "1", "2",  "3", "4", "1", "4", "1", "2", "3", "4", "1", "2", "3", "4", "1",  "2", "3"), product_name = c("GILENYA   ", "GILENYA   ", "GILENYA   ",  "GILENYA   ", "GILENYA   ", "GILENYA   ", "GILENYA   ", "GILENYA   ",  "GILENYA   ", "GILENYA   ", "GILENYA   ", "GILENYA   ", "DIMETHYL F",  "DIMETHYL F", "DIMETHYL F", "DIMETHYL F", "DIMETHYL F", "DIMETHYL F",  "DIMETHYL F", "DIMETHYL F", "DIMETHYL F", "DIMETHYL F", "DIMETHYL F",  "DIMETHYL F", "DIMETHYL F"), number_of_prescriptions = c("10",  "1", "4", "6", "1", "7", "2", "9", "3", "7", "6", "4", "3", "2",  "4", "9", "8", "2", "1", "10", "6", "9", "10", "3", "8")), row.names = c(NA,  25L), class = "data.frame")

解决方案 #

1. 修改折线图为处方占比 #

核心思路是先按yr_q计算两种药的总处方数，再计算每种药的占比，同时清理药物名称的多余空格避免过滤错误：

library(tidyverse)
library(lubridate)

alldrugs %>%
  # 清理药物名称空格，过滤目标药物
  mutate(product_name = str_trim(product_name)) %>%
  filter(product_name %in% c("DIMETHYL F", "TECFIDERA")) %>%
  mutate(
    yr_q = yq(paste(year, quarter)),
    number_of_prescriptions = as.numeric(number_of_prescriptions)
  ) %>%
  # 按季度分组计算总处方数
  group_by(yr_q) %>%
  mutate(total_prescriptions = sum(number_of_prescriptions)) %>%
  # 按药物+季度分组，计算单种药处方数与占比
  group_by(product_name, yr_q) %>%
  summarize(
    prescription_count = sum(number_of_prescriptions),
    prescription_share = prescription_count / first(total_prescriptions),
    .groups = "drop"
  ) %>%
  ggplot(aes(x = yr_q, y = prescription_share, color = product_name)) +
  geom_line(size = 1.4) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) + # Y轴显示为百分比
  xlab("2020-2022") +
  ylab("处方占比") +
  labs(colour = "仿制药 vs 品牌药") +
  theme_bw()

2. 生成按state和year拆分的处方占比表格 #

按state和year分组，计算每种药在对应分组内的处方数与占比，并整理为宽格式表格：

prescription_share_table <- alldrugs %>%
  mutate(product_name = str_trim(product_name)) %>%
  filter(product_name %in% c("DIMETHYL F", "TECFIDERA")) %>%
  mutate(number_of_prescriptions = as.numeric(number_of_prescriptions)) %>%
  # 按州+年份分组计算总处方数
  group_by(state, year) %>%
  mutate(total_prescriptions_state_year = sum(number_of_prescriptions)) %>%
  # 按州+年份+药物分组计算单种药数据与占比
  group_by(state, year, product_name) %>%
  summarize(
    drug_prescriptions = sum(number_of_prescriptions),
    prescription_share = drug_prescriptions / first(total_prescriptions_state_year),
    .groups = "drop"
  ) %>%
  # 转为宽格式，方便查看
  pivot_wider(
    names_from = product_name,
    values_from = c(drug_prescriptions, prescription_share),
    values_fill = 0 # 缺失值填充为0
  )

# 查看生成的表格
print(prescription_share_table)

# 可选：导出为CSV文件
# write.csv(prescription_share_table, "state_year_prescription_share.csv", row.names = FALSE)

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