如何用R自动将数据框列值填充至新数据框并导出至Excel？

Absolutely, you can restructure your data in R and export it to Excel using the writexl package—no VBA required! Let's walk through this step by step.

First, let's start with your original data frame:

# Original data
df <- data.frame(
  player_no = c(1,2,3,4,5,6), 
  player = c("Ram", "Shyam", "Jordan", "Chris", "Salim", "Salman"), 
  sold_to = c("Team_1", "Team_2", "Team_3", "Team_2", "Team_3", "Team_1")
)

Step 1: Restructure the Data into Team Columns #

We'll use dplyr and tidyr (part of the tidyverse) to reshape the data. The key is to assign a row ID to each player within their team, then pivot the data wide to create team-specific columns.

First, install and load the tidyverse if you haven't already:

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

Now restructure the data:

# Add row IDs for each team's players, then pivot wide
final_df <- df %>%
  # Assign row numbers per team (so each team's players get 1, 2, ...)
  group_by(sold_to) %>%
  mutate(row_id = row_number()) %>%
  ungroup() %>%
  # Pivot to wide format, creating team-specific columns
  pivot_wider(
    names_from = sold_to, 
    values_from = c(player_no, player), 
    names_sep = "_"
  ) %>%
  # Remove the temporary row_id column
  select(-row_id) %>%
  # Reorder columns to group each team's player_no and player together
  select(
    player_no_Team_1, player_Team_1,
    player_no_Team_2, player_Team_2,
    player_no_Team_3, player_Team_3
  )

The resulting final_df will look exactly like your expected structured table:

player_no_Team_1 player_Team_1 player_no_Team_2 player_Team_2 player_no_Team_3 player_Team_3
1                1           Ram                2         Shyam                3         Jordan
2                6        Salman                4         Chris                5          Salim

Step 2: Export to Excel with writexl #

Now let's export this structured data to an Excel file. First install and load the writexl package:

install.packages("writexl")
library(writexl)

Then export the data frame to Excel:

# Export to Excel (you can change the file name as needed)
write_xlsx(final_df, "team_players.xlsx")

This will create an Excel file in your working directory with the team columns neatly organized, just like you wanted. The best part? This method works even if teams have different numbers of players—any missing entries will automatically be filled with NA in the Excel file.

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