R语言：如何在dcast中添加汇总字段并实现年度订单统计？

Hey there! Let's fix up your code to generate that user-by-month order count report with an annual summary row, just like your Excel example. I'll walk through each step clearly:

Step 1: Fix Data Preprocessing First #

First, let's correct the initial data handling—your example data uses Order_date, not Month_Due, so we'll align that. We'll also properly parse the dates with lubridate and extract the year-month string:

library(data.table)
library(lubridate)
library(DT) # Don't forget this for the interactive datatable display!

# Load your sample data (recreated here for clarity)
df <- data.table(
  User = c("a", "b", "a", "c", "a", "s", "c", "s", "b", "c", "a", "s", "c", "s", "b", "b"),
  Order_date = c("02-01-2017", "02-02-2017", "02-08-2017", "02-05-2017", "02-08-2017", "02-06-2017", "02-03-2017", "02-04-2017", "02-06-2017", "02-11-2017", "02-11-2017", "02-11-2017", "02-01-2018", "02-01-2018", "02-02-2018", "02-10-2018")
)

# Parse dates correctly (your format is month-day-year)
df$Order_date <- mdy(df$Order_date)
# Extract year-month as a formatted string for grouping
df$start_year_month <- format(df$Order_date, "%Y-%m")

Step 2: Generate the Aggregated Wide Table #

Next, we'll use dcast to pivot the data into the user-by-month format. Here are the key fixes:

• Specify fun.aggregate = length to count orders per user per month
• Use drop = FALSE to keep all months even if a user has no orders that month

# Pivot to wide format: User rows, Year-Month columns, count of orders
wide_df <- dcast(df, User ~ start_year_month, fun.aggregate = length, drop = FALSE)
# Replace NA values with 0 (since NA means no orders that month)
wide_df[is.na(wide_df)] <- 0

Step 3: Add the Annual Summary Row #

Now let's create the summary row that totals each month's orders across all users, then bind it to our table:

# Create summary row: sum each month column, label User as "年度汇总"
summary_row <- data.table(
  User = "年度汇总",
  t(colSums(wide_df[, -"User"])) # Sum all columns except the User column
)

# Bind the summary row to the original wide table
final_df <- rbind(wide_df, summary_row)

Step 4: Display with Interactive Datatable #

Finally, use DT::datatable to show the table with top filters as you originally intended:

datatable(final_df, filter = 'top', 
          options = list(pageLength = 10))

What was wrong with your original code? #

• You referenced df$Month_Due which doesn't exist in your sample data—should be Order_date
• You didn't specify fun.aggregate in dcast (we need length to count orders)
• You missed adding the summary row entirely, which is why you didn't see those totals
• You forgot to load the DT package for the interactive table display

This should give you exactly the table you're looking for, with each user's monthly order counts plus a bottom row summing up each month's total orders.

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