基于两个数据透视表绘制用户月度达标率折线图的R技术问询

Alright, let's get that line chart sorted out for you! You've already nailed the pivot tables with rpivotTable, so we just need to wrangle that data into a plot-ready format and then build the visualization with ggplot2—here's how to do it step by step:

Step 1: Extract Structured Data from Your Pivot Tables #

First, we need to convert the interactive rpivotTable outputs into regular R data frames so we can manipulate them. Assuming your two pivot tables are stored in objects named PivotTable_1 (for "0 or early" counts) and PivotTable_2 (for total counts):

# Convert pivot tables to data frames
early_counts <- as.data.frame(PivotTable_1)
total_counts <- as.data.frame(PivotTable_2)

# Rename columns for clarity (adjust if your pivot output has different names)
colnames(early_counts) <- c("Buyer", "year_month", "early_count")
colnames(total_counts) <- c("Buyer", "year_month", "total_count")

Step 2: Combine and Clean the Data #

Next, merge the two data frames so we have both counts for each Buyer/year_month pair, then handle any missing values (like if a Buyer has no "0 or early" entries in a month):

# Merge data on Buyer and year_month
combined_data <- merge(early_counts, total_counts, 
                       by = c("Buyer", "year_month"), 
                       all.x = TRUE, all.y = TRUE)

# Replace NA values with 0 (since NA means no counts for that group)
combined_data$early_count <- ifelse(is.na(combined_data$early_count), 0, combined_data$early_count)
combined_data$total_count <- ifelse(is.na(combined_data$total_count), 0, combined_data$total_count)

# Calculate on-time percentage (avoid division by 0 by setting to NA if total is 0)
combined_data$on_time_pct <- ifelse(combined_data$total_count == 0, NA,
                                    (combined_data$early_count / combined_data$total_count) * 100)

Pro tip: If your year_month column is a character string (like "2023-01"), convert it to a date type to ensure proper sorting on the x-axis:

combined_data$year_month <- as.Date(paste0(combined_data$year_month, "-01"), format = "%Y-%m-%d")

Step 3: Build the Line Chart with ggplot2 #

Now we can create the line chart, with each Buyer represented by a unique colored line:

library(ggplot2)

ggplot(combined_data, aes(x = year_month, y = on_time_pct, color = Buyer, group = Buyer)) +
  geom_line(linewidth = 1.2) + # Thicker lines for better visibility
  geom_point(size = 2.5) + # Add points to highlight monthly values
  labs(
    title = "On-Time Delivery Percentage by Buyer and Month",
    x = "Year-Month",
    y = "On-Time Percentage (%)",
    color = "Buyer"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1), # Rotate x-axis labels to prevent overlap
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold")
  )

This will give you a clean, visualization-ready line chart that shows the on-time percentage trend for each Buyer across months. If you're embedding this in a Shiny dashboard, wrap the ggplot code in renderPlot() to display it directly in your app.

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