如何为DataFrame添加基于现有行计算的新行（dplyr/Base R方案）

Hey there! Let's work through how to add those Profit and Tax rows for each quarterly column in your DataFrame df. I'll start with the dplyr approach (since you asked for it first) and follow up with a Base R solution, plus show what the final output should look like.

First, let's set up a sample version of your original DataFrame to make the examples concrete:

# Sample original DataFrame
df <- data.frame(
  Category = c("Income", "Cost"),
  Dec.17 = c(1000, 600),
  Sep.17 = c(900, 550),
  Jun.17 = c(800, 500),
  Mar.17 = c(700, 450),
  Dec.16 = c(600, 400)
)

dplyr Implementation #

We'll use dplyr alongside tidyr to reshape the data, compute our new metrics, then reshape back to the original wide format:

library(dplyr)
library(tidyr)

df_processed <- df %>%
  # Convert to long format to group by quarter
  pivot_longer(cols = -Category, names_to = "Quarter", values_to = "Value") %>%
  # Spread back to wide to have Income and Cost as columns per quarter
  pivot_wider(names_from = Category, values_from = Value) %>%
  # Calculate Profit and Tax for each quarter
  mutate(
    Profit = Income - Cost,
    Tax = 0.2 * Profit
  ) %>%
  # Convert back to long format to include all categories
  pivot_longer(cols = c(Income, Cost, Profit, Tax), names_to = "Category", values_to = "Value") %>%
  # Convert back to wide format matching original structure
  pivot_wider(names_from = Quarter, values_from = Value) %>%
  # Reorder rows to keep Income → Cost → Profit → Tax order
  arrange(match(Category, c("Income", "Cost", "Profit", "Tax")))

Base R Implementation #

If you prefer not to use tidyverse packages, here's a Base R approach that directly computes the new rows and combines them with the original data:

# Extract Income and Cost values for each quarter
income_vals <- df[df$Category == "Income", -1]
cost_vals <- df[df$Category == "Cost", -1]

# Calculate Profit and Tax
profit_vals <- income_vals - cost_vals
tax_vals <- 0.2 * profit_vals

# Create new rows for Profit and Tax
profit_row <- data.frame(Category = "Profit", profit_vals)
tax_row <- data.frame(Category = "Tax", tax_vals)

# Combine original data with new rows and reorder
df_processed_base <- rbind(df, profit_row, tax_row)
df_processed_base <- df_processed_base[match(c("Income", "Cost", "Profit", "Tax"), df_processed_base$Category), ]

Expected Output #

Both methods will produce the same final DataFrame, which looks like this:

> df_processed
# A tibble: 4 × 6
  Category Dec.17 Sep.17 Jun.17 Mar.17 Dec.16
  <chr>     <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 Income     1000    900    800    700    600
2 Cost        600    550    500    450    400
3 Profit      400    350    300    250    200
4 Tax          80     70     60     50     40

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