修改R语言create_bar_plot函数以处理含'-'的Excel缺失数据

我编写了R语言的create_bar_plot函数，用于读取Excel文件并生成堆叠条形图，在无缺失值的文件上运行正常，但遇到用'-'填充缺失数据的Excel文件时无法生成图表。需要修改函数，使其自动排除无数据的州，同时兼容无缺失值的文件。

原函数代码 #

create_bar_plot <- function(file_path) {
  # Read the Excel file
  excel_data <- read_excel(file_path)
# Extract required columns
  states <- excel_data$State
  fifth_percentile <- excel_data$`5th Percentile`
  mean <- excel_data$Mean
  ninety_fifth_percentile <- excel_data$`95th Percentile`
  threeColData <- data.frame(fifth_percentile, mean, ninety_fifth_percentile) 
#ValueData code
ValueData <- c(rbind(threeColData$fifth_percentile,threeColData$mean,threeColData$ninety_fifth_percentile))
# Create plot_data dataframe
plot_data <- data.frame(State = rep(states, each = 3),
    Metric = factor(rep(c("5th Percentile", "Mean", "95th Percentile"), times = 13),
    levels = c("95th Percentile", "Mean", "5th Percentile")),
    Value = ValueData
  )
  plot_data <- plot_data |>
  arrange(desc(Metric)) |>
  mutate(value_bar = Value - lag(Value, default = 0), .by = State) |>
  arrange(State)
options(repr.plot.width=10, repr.plot.height=6)

allStatePlot = ggplot(plot_data, aes(x = State, y = value_bar, fill = Metric)) +
    geom_col(color = "white", linewidth = .25, width = .75 ) +
    geom_text(aes(label = Value), position = position_stack(vjust = 0.5)) +
    scale_fill_manual( values = c( "5th Percentile" = "forestgreen", "Mean" = "pink",
        "95th Percentile" = "orange") ) +
    scale_y_continuous( expand = c(0, 0, .05, 0), limits = \(x) {
        c(0, range(scales::breaks_extended(only.loose = TRUE)(x))[2])
      }) +
    labs(x = " ",  y = " ", fill = " ") + theme_bw() +
    theme(panel.grid = element_blank(),
      axis.text.x = element_text(angle = 45, hjust = 1, size = 14, face = "bold"),
      axis.text.y = element_text(size = 12, face = "bold"),
      legend.position = "top",
      legend.text = element_text(size = 15, face = "bold")
    )
    print(allStatePlot)

}

含缺失值的Excel数据示例 #

State   Sample Size Mean    S.D.    Range   5th Percentile  95th Percentile
All India   3423    218 46  93-419  142 294
Arunachal Pradesh   -   -   -   -   -   -
Gujarat 75  227 43  148-299 157 297
Jammu & Kashmir 485 239 43  138-375 167 310
Madhya Pradesh  825 227 43  105-419 156 298
Maharashtra 1249    202 45  93-391  128 276
Meghalaya   -   -   -   -   -   -
Mizoram -   -   -   -   -   -
Orissa  171 236 44  131-335 155 298
Punjab  -   -   -   -   -   -
Uttar Pradesh   -   -   -   -   -   -
Tamil Nadu  618 219 46  116-375 143 295
West Bengal -   -   -   -   -   -

数据结构 #

str(PULL_STRENGTH_BOTH_HANDS_STANDING)
tibble [13 × 7] (S3: tbl_df/tbl/data.frame)
 $ State          : chr [1:13] "All India" "Arunachal Pradesh" "Gujarat" "Jammu & Kashmir" ...
 $ Sample Size    : chr [1:13] "3423" "-" "75" "485" ...
 $ Mean           : chr [1:13] "218" "-" "227" "239" ...
 $ S.D.           : chr [1:13] "46" "-" "43" "43" ...
 $ Range          : chr [1:13] "93-419" "-" "148-299" "138-375" ...
 $ 5th Percentile : chr [1:13] "142" "-" "157" "167" ...
 $ 95th Percentile: chr [1:13] "294" "-" "297" "310" ...

修改后的函数代码 #

create_bar_plot <- function(file_path) {
  # 读取Excel文件并处理缺失值
  excel_data <- read_excel(file_path) %>%
    # 将'-'替换为NA，同时转换数值列为数值型
    mutate(across(c(`5th Percentile`, Mean, `95th Percentile`), ~{
      x <- na_if(., "-")
      as.numeric(x)
    })) %>%
    # 过滤掉三个关键指标全为NA的州
    filter(if_all(c(`5th Percentile`, Mean, `95th Percentile`), ~!is.na(.)))
  
  # 提取所需列
  states <- excel_data$State
  fifth_percentile <- excel_data$`5th Percentile`
  mean_val <- excel_data$Mean
  ninety_fifth_percentile <- excel_data$`95th Percentile`
  
  threeColData <- data.frame(fifth_percentile, mean_val, ninety_fifth_percentile) 
  
  # 整理ValueData
  ValueData <- c(rbind(threeColData$fifth_percentile, threeColData$mean_val, threeColData$ninety_fifth_percentile))
  
  # 创建plot_data数据框，动态设置重复次数
  plot_data <- data.frame(
    State = rep(states, each = 3),
    Metric = factor(rep(c("5th Percentile", "Mean", "95th Percentile"), times = nrow(excel_data)),
                    levels = c("95th Percentile", "Mean", "5th Percentile")),
    Value = ValueData
  )
  
  plot_data <- plot_data %>%
    arrange(desc(Metric)) %>%
    mutate(value_bar = Value - lag(Value, default = 0), .by = State) %>%
    arrange(State)
  
  options(repr.plot.width=10, repr.plot.height=6)
  
  allStatePlot = ggplot(plot_data, aes(x = State, y = value_bar, fill = Metric)) +
    geom_col(color = "white", linewidth = .25, width = .75 ) +
    geom_text(aes(label = Value), position = position_stack(vjust = 0.5)) +
    scale_fill_manual( values = c( "5th Percentile" = "forestgreen", "Mean" = "pink",
                                   "95th Percentile" = "orange") ) +
    scale_y_continuous( expand = c(0, 0, .05, 0), limits = \(x) {
      c(0, range(scales::breaks_extended(only.loose = TRUE)(x))[2])
    }) +
    labs(x = " ",  y = " ", fill = " ") + theme_bw() +
    theme(panel.grid = element_blank(),
          axis.text.x = element_text(angle = 45, hjust = 1, size = 14, face = "bold"),
          axis.text.y = element_text(size = 12, face = "bold"),
          legend.position = "top",
          legend.text = element_text(size = 15, face = "bold")
    )
  print(allStatePlot)
}

核心修改说明 #

• 缺失值处理与类型转换：用na_if将所有'-'替换为NA，同时把关键指标列转为数值型，解决原函数中字符型数据无法进行数值计算的问题。
• 自动过滤无效州：通过filter(if_all(...))筛选出三个关键指标都不为NA的州，自动剔除没有有效数据的行。
• 动态适配数据行数：把原代码中固定的times = 13改为times = nrow(excel_data)，确保过滤后的数据能正确生成对应数量的Metric标签，兼容不同规模的有效数据。
• 避免变量名冲突：将原函数中的mean变量重命名为mean_val，防止和R内置函数mean()冲突引发潜在问题。

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