在R中去重并处理剩余行：统计董事董事会席位与关联公司

Hey there! Let's work through your R data processing task—this is a classic aggregation problem, and we can handle it cleanly with tidyverse tools.

Step 1: Setup & Assumptions #

First, let's assume your dataset (let's call it director_data) has at least these columns:

• director_name: The name of the board member
• company_name: The company they serve on the board of
• (For the extra requirement) A column indicating their role, like position_title or role_type (e.g., "executive" vs "outsider")

If you don't have the tidyverse installed yet, run this first:

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

Step 2: Basic Requirements (Deduplicate, Count Seats, List Companies) #

We'll use dplyr to group by each director, then aggregate the data to get what we need:

# Aggregate to get one row per director
cleaned_data <- director_data %>%
  # Group by director (use director_id instead if you have unique IDs to avoid name collisions!)
  group_by(director_name) %>%
  summarize(
    board_seats = n(),  # Number of board seats = total rows per director
    all_companies = str_c(company_name, collapse = ", ")  # Combine all company names into one string
  ) %>%
  ungroup()

This will give you a dataset where each row is a unique director, with their total seat count and a comma-separated list of all companies they serve.

Step 3: Extra Requirement - Identify Affiliated Company (Executive Role) #

If your data has a role indicator, we can add a column to pull out the company where they serve as an executive. Let's cover two scenarios:

Scenario A: You already have a role_type column (values like "executive"/"outsider")

cleaned_data_with_affiliation <- director_data %>%
  group_by(director_name) %>%
  summarize(
    board_seats = n(),
    all_companies = str_c(company_name, collapse = ", "),
    # Pull all companies where the director is an executive
    affiliated_company = str_c(company_name[role_type == "executive"], collapse = ", ")
  ) %>%
  ungroup() %>%
  # Replace empty strings with NA for directors who have no executive roles
  mutate(affiliated_company = ifelse(affiliated_company == "", NA, affiliated_company))

Scenario B: You only have a position_title column (e.g., "CEO", "Independent Director")

First, we'll categorize roles into "executive" or "outsider" using regex, then proceed:

# First, create a role_type column from position titles
director_data <- director_data %>%
  mutate(
    role_type = case_when(
      # Match executive roles (adjust regex to fit your data's titles!)
      str_detect(position_title, regex("CEO|CFO|COO|President|Executive Director", ignore_case = TRUE)) ~ "executive",
      # Match outsider/independent roles
      str_detect(position_title, regex("Independent Director|Outside Director", ignore_case = TRUE)) ~ "outsider",
      # Mark uncategorized roles as unknown
      TRUE ~ "unknown"
    )
  )

# Now run the same aggregation as Scenario A
cleaned_data_with_affiliation <- director_data %>%
  group_by(director_name) %>%
  summarize(
    board_seats = n(),
    all_companies = str_c(company_name, collapse = ", "),
    affiliated_company = str_c(company_name[role_type == "executive"], collapse = ", ")
  ) %>%
  ungroup() %>%
  mutate(affiliated_company = ifelse(affiliated_company == "", NA, affiliated_company))

Quick Notes #

• Name Collisions: If multiple directors share the same name, use a unique identifier (like director_id) in the group_by() instead of director_name to avoid merging unrelated people.
• Custom Separators: If you don't want commas for the company list, change the collapse argument in str_c() (e.g., collapse = "; ").

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