1 Monthly Submissions (Revision Number = 0)

This section reads Manuscript Time in Review Kamran - September.xlsx, filters to records with Revision_Number == 0, aggregates monthly counts, excludes the last (most recent) month, and plots a line chart.

# Load required libraries
library(readxl)
library(dplyr)
library(lubridate)
library(ggplot2)
library(knitr)
# File path (adjust if needed)
setwd("~/Academia/GT/Service/Editorial/Technometrics/Monthly Reports")
file_path <- "Manuscript Time in Review Kamran - September.xlsx"

# Read the data (header is on the 2nd row after skipping two rows)
df <- read_excel(file_path, sheet = "Sep 2, 2025", skip = 2)

# Rename columns for convenience
colnames(df) <- c("Manuscript_ID", "Submission_Date", "Revision_Number", "Manuscript_Type",
                  "Manuscript_Status", "Decision", "Decision_Date", "Reviewers_Assigned",
                  "Revisions", "Days_Orig_Decision", "Days_Final_Decision", "Editor_Last_Name")

# Filter where Revision Number = 0
df_filtered <- df %>%
  filter(Revision_Number == 0)

# Convert submission date to Date
df_filtered <- df_filtered %>%
  mutate(Submission_Date = as.Date(Submission_Date))

# Group by month and count submissions
monthly_counts <- df_filtered %>%
  mutate(Month = floor_date(Submission_Date, "month")) %>%
  group_by(Month) %>%
  summarise(Num_Submissions = n()) %>%
  ungroup()

# Exclude the last (most recent) month
last_month <- max(monthly_counts$Month, na.rm = TRUE)
monthly_counts <- monthly_counts %>%
  filter(Month < last_month)

# Show the monthly table
kable(monthly_counts, caption = "Monthly Submissions (excluding last month)")
Monthly Submissions (excluding last month)
Month Num_Submissions
2025-01-01 22
2025-02-01 27
2025-03-01 43
2025-04-01 38
2025-05-01 30
2025-06-01 47
2025-07-01 42
2025-08-01 58 
# Line chart of submissions by month (excluding last month)
ggplot(monthly_counts, aes(x = Month, y = Num_Submissions)) +
  geom_line(size = 1) +
  geom_point(size = 2) +
  labs(
    title = "Number of Submissions by Month (Revision Number = 0, excluding last month)",
    x = "Month",
    y = "Number of Submissions"
  ) +
  theme_minimal()

2 Yearly Submissions (Revision Number = 0, Excluding 2011)

This section reads Allpapers0925.xlsx, filters to records with Revision.Number == 0, computes yearly counts, excludes 2011, prints the table, and draws a line chart.

# Load required libraries (reaffirm)
library(readxl)
library(dplyr)
library(lubridate)
library(ggplot2)
library(knitr)

# File path (adjust to your local directory if needed)
file_path <- "Allpapers0925.xlsx"

# Read the first sheet; skip = 2 to align with header row
df_y <- read_excel(file_path, sheet = 1, skip = 2)

# Ensure consistent column naming
colnames(df_y) <- make.names(colnames(df_y))

# Filter only Revision Number = 0
df_y_filtered <- df_y %>%
  filter(Revision.Number == 0)

# Convert Submission Date to proper Date
df_y_filtered <- df_y_filtered %>%
  mutate(Submission.Date = as.Date(Submission.Date))

# Extract Year
df_y_filtered <- df_y_filtered %>%
  mutate(Year = year(Submission.Date))

# Count submissions by year
yearly_counts <- df_y_filtered %>%
  group_by(Year) %>%
  summarise(Num_Submissions = n(), .groups = "drop")

# Exclude 2011
yearly_counts <- yearly_counts %>%
  filter(Year != 2011)

# Show the yearly table
kable(yearly_counts, caption = "Yearly Submissions (excluding 2011)")
Yearly Submissions (excluding 2011)
Year Num_Submissions
2012 235
2013 225
2014 203
2015 214
2016 195
2017 215
2018 182
2019 211
2020 203
2021 237
2022 195
2023 202
2024 252
2025 310 
# Line plot of submissions by year (excluding 2011)
ggplot(yearly_counts, aes(x = Year, y = Num_Submissions)) +
  geom_line(size = 1) +
  geom_point(size = 2) +
  labs(
    title = "Number of Submissions by Year (Revision Number = 0; 2012 and later)",
    x = "Year",
    y = "Number of Submissions"
  ) +
  theme_minimal()