条件转置Data Frame：将长格式tibble转换为指定宽格式

Here's how you can transform your data into the desired wide format using tidyverse tools:

First, make sure you have the tidyverse package installed and loaded:

install.packages("tidyverse")
library(tidyverse)

Then, apply this code to your tibble (assuming your data is stored in a variable named df):

df_wide <- df %>%
  # Fill missing X1 values with the previous non-NA value (propagates 22 to all rows)
  fill(X1, .direction = "down") %>%
  # Remove the row with all NA metrics (the initial UTILITIES header row)
  filter(!is.na(revenue)) %>%
  # Pivot to wide format, combining X2 labels with metric names
  pivot_wider(
    id_cols = X1,
    names_from = X2,
    values_from = c(revenue, concentration, payroll, empl),
    names_glue = "{X2}.{.value}"
  )

Breakdown of the steps: #

1. fill(X1, .direction = "down"): This takes the X1 value from the first row (22) and fills it into all subsequent rows where X1 is NA. This ensures every subgroup (A-E) is linked to its parent category's X1 identifier.
2. filter(!is.na(revenue)): Removes the first row (where all metrics are NA) since we've already propagated the X1 value to the relevant rows.
3. pivot_wider(): Converts the long format to wide:
   • id_cols = X1: Groups the data by X1, so each unique X1 gets its own row.
   • names_from = X2: Uses the X2 values (A-E) as part of the new column names.
   • values_from = c(revenue, concentration, payroll, empl): Specifies which columns to pivot into the wide format.
   • names_glue = "{X2}.{.value}": Creates column names like A.revenue, B.concentration by combining the X2 label with the metric name.

The resulting df_wide will have one row per X1 value, with columns for each subgroup's metrics exactly as you requested.

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