如何在R中生成含汇总列的rpivotTable/dcast表（仿Excel格式）

Absolutely! You can absolutely recreate that Excel-style pivot table with row/column summary totals in R—let’s walk through two reliable methods that should solve your problem: using data.table’s dcast with summary margins, and leveraging rpivotTable’s built-in total features.

Method 1: data.table + DT (Full Control Over Formatting) #

This approach gives you precise control over the table structure and lets you render it with a clean, Excel-like interface using the DT package.

Step 1: Load Required Packages #

library(data.table)
library(DT)

Step 2: Prepare Your Data #

First, convert your dataset to a data.table (if it isn’t already):

# Assuming your dataset is named 'inventory'
setDT(inventory)

Step 3: Generate Pivot Table with Summaries #

We’ll first create the base pivot table, then add row and column totals using add_margins:

# Create base pivot table (count of Late_Days entries per Buyer/Month)
pivot_base <- dcast(inventory, 
                    Buyer ~ year_month, 
                    fun.aggregate = length, 
                    value.var = "Late_Days")

# Add row (Buyer total) and column (month total) summaries
pivot_with_totals <- add_margins(pivot_base, 
                                 margin = c(1, 2),  # 1 = rows, 2 = columns
                                 FUN = sum, 
                                 quiet = TRUE)

# Rename the auto-generated "Sum" labels to "Total" (matches Excel)
setnames(pivot_with_totals, "Sum", "Total")
pivot_with_totals[Buyer == "Sum", Buyer := "Total"]

Step 4: Render as Excel-Style Table #

Use DT::datatable to get a filterable, clean table that mirrors Excel’s look:

datatable(pivot_with_totals,
          filter = 'top',  # Keep your desired top filter
          rownames = FALSE,
          options = list(
            dom = 't',  # Hide extra elements (keep just the table)
            pageLength = nrow(pivot_with_totals),  # Show all rows at once
            columnDefs = list(list(className = 'dt-center', targets = "_all"))  # Center-align all cells
          ))

Method 2: rpivotTable (Interactive, Built-In Totals) #

If you prefer an interactive pivot table (like Excel’s pivot table interface), rpivotTable supports row/column totals directly via parameters—you might have missed these in your initial attempts!

Step 1: Load the Package #

library(rpivotTable)

Step 2: Generate Pivot Table with Totals #

Use the rowTotals and colTotals arguments to enable summary columns/rows:

rpivotTable(inventory,
            rows = "Buyer",
            cols = "year_month",
            vals = "Late_Days",
            aggregatorName = "Count",  # Matches Excel's count of entries
            rendererName = "Table",
            rowTotals = TRUE,  # Enable row-wise Buyer totals
            colTotals = TRUE)  # Enable column-wise month totals

This will render an interactive table where you can drag-and-drop fields (just like Excel) and the totals will update automatically.

Notes for Your Dataset #

Since your Late_Days column is categorical (e.g., "<=20", "<=10"), the length (for data.table) or "Count" (for rpivotTable) aggregator will correctly count the number of entries per Buyer/Month. If you wanted to summarize by specific Late_Days categories, you could adjust the pivot rows/cols to include Late_Days as well.

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