I have an expression matrix with gene probes in rows and GEO samples in columns, on the other hand I have an annotation file of these probes with their symbols. Some symbols are duplicated, that means some symbols may have more than one probe, I want to consider all of them in one group and average them all, moreover some symbols are combined symbols and separated by slashes, but there may be symbols on their own on other lines, e.g. CARD16///CASP1, in one row is CARD16///CASP1, in another row CARD16 and in another CASP1, I want to consider them all in one group and calculate their average. Another issue is that some symbols are combined symbols and separated by a slash like the previous issue I said, but may differ by just one symbol like this: LOC101930400///AKR1C2 and LOC101930400///AKR1C2///AKR1C1, I also want to consider these two as a group and calculate the average, some of them are the same as before but they may have extra symbols e.g. LOC101930343///CATSPER2P1///CATSPER2 and LOC101930343///STRC///CATSPER2 or (DUX4L24///DBET///LOC100291626///DUX4///LOC100288289///DUX4L2///DUX4L3///DUX4L5///DUX4L6///DUX4L7///LOC652301///DUX4L4///DUX4L8///DUX4L1) and (DUX4L24///DBET///LOC100291626///DUX4///LOC100288289///LOC100287823///LOC100133400///DUX4L2///DUX4L3///DUX4L5///DUX4L6///DUX4L7///LOC652301///DUX4L4///DUX4L8///DUX3///DUX4L1), I also want to consider these in a group and calculate the average.

 # Function to create a lookup table mapping each base symbol to its most comprehensive form
create_lookup_table <- function(all_symbols) {
  lookup_table <- list()
  for (symbol in all_symbols) {
    # Split the symbol into parts
    parts <- unlist(strsplit(symbol, "///"))
    # Iterate over each part to update the lookup table
    for (part in parts) {
      # If the part is not already in the lookup table, or if the current symbol is more comprehensive, update the lookup table
      if (!part %in% names(lookup_table) || nchar(symbol) > nchar(lookup_table[[part]])) {
        lookup_table[[part]] <- symbol
      }
    }
  }
  return(lookup_table)
}


# Create a vector of all unique gene symbols from the annotation file
all_symbols <- unique(probe_symbols$Gene.symbol)

# Create the lookup table
lookup_table <- create_lookup_table(all_symbols)

# Map each symbol to the most comprehensive symbol using the lookup table
probe_symbols$Group.Symbol <- sapply(probe_symbols$Gene.symbol, function(symbol) {
  base_symbol <- unlist(strsplit(symbol, "///"))[1]
  return(lookup_table[[base_symbol]])
})

# Merge the expression matrix with the annotation to get group symbols for each probe
merged_data <- merge(probe_symbols, data_no_batch, by.x="ID", by.y="row.names", all.y=TRUE)

# Group by 'Group.Symbol' and calculate the mean expression for each group
library(dplyr)
mean_expression <- merged_data %>%
  group_by(Group.Symbol) %>%
  summarise(across(starts_with("GSM"), ~mean(.x, na.rm = TRUE)))

mean_expression <- data.frame(mean_expression)
rownames(mean_expression) <- mean_expression$Group.Symbol
mean_expression2 <- mean_expression[,-1]
#write.table(mean_expression2, file = "D:/GEO/mean-expression.txt", quote = F, sep = "t")
pe here

I ran the above code, it worked correctly in some ways but it did not work well for example it did not consider ACTG1P4///AMY2B///AMY2A///AMY1C///AMY1B///AMY1A
and ACTG1P4///RNPC3///AMY2B or GH1///CSHL1///CSH2///CSH1 and GH2///GH1///CSHL1///CSH1 as one group then computes the mean. How can I solve this and revise my code?

    # Function to create a lookup table mapping each base symbol to its most comprehensive form
create_lookup_table <- function(all_symbols) {
  lookup_table <- list()
  for (symbol in all_symbols) {
    # Split the symbol into parts
    parts <- unlist(strsplit(symbol, "///"))
    # Iterate over each part to update the lookup table
    for (part in parts) {
      # If the part is not already in the lookup table, or if the current symbol is more comprehensive, update the lookup table
      if (!part %in% names(lookup_table) || nchar(symbol) > nchar(lookup_table[[part]])) {
        lookup_table[[part]] <- symbol
      }
    }
  }
  return(lookup_table)
}


# Create a vector of all unique gene symbols from the annotation file
all_symbols <- unique(probe_symbols$Gene.symbol)

# Create the lookup table
lookup_table <- create_lookup_table(all_symbols)

# Map each symbol to the most comprehensive symbol using the lookup table
probe_symbols$Group.Symbol <- sapply(probe_symbols$Gene.symbol, function(symbol) {
  base_symbol <- unlist(strsplit(symbol, "///"))[1]
  return(lookup_table[[base_symbol]])
})

# Merge the expression matrix with the annotation to get group symbols for each probe
merged_data <- merge(probe_symbols, data_no_batch, by.x="ID", by.y="row.names", all.y=TRUE)

# Group by 'Group.Symbol' and calculate the mean expression for each group
library(dplyr)
mean_expression <- merged_data %>%
  group_by(Group.Symbol) %>%
  summarise(across(starts_with("GSM"), ~mean(.x, na.rm = TRUE)))

mean_expression <- data.frame(mean_expression)
rownames(mean_expression) <- mean_expression$Group.Symbol
mean_expression2 <- mean_expression[,-1]
#write.table(mean_expression2, file = "D:/GEO/mean-expression.txt", quote = F, sep = "t")
pe here

I ran the above code, it worked correctly in some ways but it did not work well for example it did not consider ACTG1P4///AMY2B///AMY2A///AMY1C///AMY1B///AMY1A
and ACTG1P4///RNPC3///AMY2B or GH1///CSHL1///CSH2///CSH1 and GH2///GH1///CSHL1///CSH1 as one group then computes the mean. How can I solve this and revise my code?