如何高效将含vehicle、age、ways的DataFrame转换为指定计算规则的透视表

Hey there! Let's sort this out efficiently since you've got multiple DataFrames to handle—no more manual filtering and Excel imports needed. Here's a tidyverse-based workflow that automates the whole process:

Step 1: Load the required package #

First, make sure you have the tidyverse installed and loaded—it has all the tools we need for grouping, summarizing, and reshaping data:

library(tidyverse)

Step 2: Process a single DataFrame #

Let’s assume your original tibble is named df. This code will transform it exactly into the format you want, with the percentage calculation built in:

formatted_df <- df %>%
  # Calculate total ways for each age, attach to every row in that age group
  group_by(age) %>%
  mutate(age_total = sum(ways)) %>%
  ungroup() %>%
  # Sum ways for each vehicle-age combination, keep the age total
  group_by(vehicle, age) %>%
  summarize(
    vehicle_ways_sum = sum(ways),
    age_total = first(age_total), # All rows in the group share the same age total
    .groups = "drop"
  ) %>%
  # Compute the percentage using your formula
  mutate(percentage = (vehicle_ways_sum * 100) / age_total) %>%
  # Reshape to wide format: vehicle as rows, age as columns
  pivot_wider(
    names_from = age,
    values_from = percentage,
    values_fill = 0 # Fill empty cells with 0; use NA if you prefer blanks
  )

How this works:

• Group by age first: We calculate the total ways for each age and attach that value to every row in the age group—so every vehicle-age combination can access the total for its age.
• Group by vehicle + age: We sum up the ways for each unique vehicle-age pair, while retaining the precomputed age total.
• Calculate percentage: Directly applies your formula to get the proportional value.
• Reshape to wide format: pivot_wider turns the long-form data into the row-column structure you specified.

Step 3: Scale to multiple DataFrames #

If you have a list of DataFrames (e.g., df_list <- list(df1, df2, df3)), use purrr::map to apply the same logic to all of them in one go:

processed_dfs <- df_list %>%
  map(~ .x %>%
        group_by(age) %>%
        mutate(age_total = sum(ways)) %>%
        ungroup() %>%
        group_by(vehicle, age) %>%
        summarize(
          vehicle_ways_sum = sum(ways),
          age_total = first(age_total),
          .groups = "drop"
        ) %>%
        mutate(percentage = (vehicle_ways_sum * 100) / age_total) %>%
        pivot_wider(
          names_from = age,
          values_from = percentage,
          values_fill = 0
        )
  )

The result will be a list where each element is your formatted table for the corresponding original DataFrame.

Quick validation example #

For age=24, let’s verify:

• Total ways for age=24: 0.216 + 0.435 + 0.239 + 0.404 + 0.566 + 1.12 + 4.63 + 0.677 = 8.287
• Vehicle 1's sum for age=24: 0.216 + 0.404 = 0.62
• Percentage: (0.62 * 100)/8.287 ≈ 7.48%
  The code will compute this automatically and place it in the cell where vehicle=1 and age=24.

内容的提问来源于stack exchange，提问作者C. Toni