R中列标题转换问题：替换动物列为M/B并避免重复列名编号

Got it, let's tackle this problem step by step. First off, manually editing the CSV to replace animal names with 'M'/'B' is causing R to auto-number duplicate columns because R doesn't allow duplicate column names by default—and honestly, that's a headache you don't need. Instead, let's use your existing df1 and dfanimal data frames to do this cleanly in R, which is way more reproducible and avoids the column naming mess.

First, let's assume your data structures look like this (adjust if yours differ slightly):

# Sample df1 (wide format with animal columns)
df1 <- data.frame(
  Location_Type = c("Urban", "Rural", "Suburban"),
  Cat = c(10, 5, 8),
  Mouse = c(20, 30, 15),
  Dog = c(15, 12, 18),
  Chicken = c(5, 20, 10),
  Turkey = c(3, 15, 7),
  Horse = c(2, 8, 4)
)

# Sample dfanimal (animal-to-type mapping)
dfanimal <- data.frame(
  Animal = c("Cat", "Mouse", "Dog", "Chicken", "Turkey", "Horse"),
  Type = c("M", "M", "M", "B", "B", "M")
)

Method 1: Convert to Long Format (RECOMMENDED for Analysis) #

This is the best approach because it aligns with tidy data principles, making it way easier to analyze the distribution of mammals vs. birds across location types. We'll use the tidyverse package to reshape the data and map the animal types in one go.

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

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

2. Reshape df1 from wide to long format, then join with dfanimal to get the mammal/bird labels:

# Reshape wide to long, then add animal type
df_long <- df1 %>%
  pivot_longer(
    cols = -Location_Type,  # Keep Location_Type as is, reshape all other columns
    names_to = "Animal",    # Name of the new column for animal names
    values_to = "Count"     # Name of the new column for animal counts
  ) %>%
  left_join(dfanimal, by = "Animal")  # Join with type mapping

# Now analyze the distribution
summary_by_location_type <- df_long %>%
  group_by(Location_Type, Type) %>%
  summarise(Total_Count = sum(Count), .groups = "drop")

print(summary_by_location_type)

This gives you a clean, analyzable dataset where each row represents a single animal count with its type and location. You can easily visualize this with ggplot2 too, if needed.

Method 2: Replace Column Names Directly (NOT RECOMMENDED) #

If you absolutely need to keep the wide format with 'M'/'B' column names, you can map the column labels directly—but be warned: duplicate column names in R are messy to work with (you'll have trouble indexing them later).

Here's how to do it:

# Get the corresponding type for each animal column in df1
column_types <- dfanimal$Type[match(colnames(df1)[-1], dfanimal$Animal)]

# Replace the column names
colnames(df1)[-1] <- column_types

# If you're reading from a CSV and want to avoid auto-numbering, use check.names=FALSE
# df1 <- read.csv("your_file.csv", check.names = FALSE)

But again, this will leave you with duplicate column names (e.g., multiple 'M' columns), which R will handle awkwardly. For example, df1$M will only return the first 'M' column—accessing others requires clunky syntax like df1[, which(colnames(df1) == "M")]. Stick with the long format for any actual analysis work.

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