3
$\begingroup$

enter image description here

Part of my dataset is shown in this image, I want to combine the columns GR_S01_w1_a, GR_S01_w1_b and GR_S01_w1_c into a single column - GR_S01_w1 - whose values are the sum of the three.

I know how to use mutate to add a new column which does this, but I also want to delete the other three, and do this about 100 more times for all the other samples I have. So essentially - I have three replicates of each sample in the form of a column of the format samplename_a, samplename_b and samplename_c, and I want to replace these with a single column, many times over.

I have tried using mutate like this -

Gregory <- Gregory %>% mutate(GR_S01_w1 = sum(GR_S01_w1_a, GR_S01_w1_b, GR_S01_w1_c))

but for all of the samples that I have this would of course take far too long. Is there a quick way for me to do this (other than manually on excel which is what I'm doing at the moment)?

$\endgroup$
2
  • $\begingroup$ The easiest way to automatically do this is probably by first using gather to convert the data to a long format. After that create a new column that you can use to make the groups (i.e. convert GR_S01_w1_c to GR_S01_w1_c) and use groupby, mutate, and sum to sum over that new column. Finally, if necessary, you can convert those group back over the columns using spread. $\endgroup$
    – Oxbowerce
    Commented Jun 4, 2021 at 9:06
  • $\begingroup$ A work around can be to use column numbers. Are your columns strictly in the manner of a,b,c then again a,b,c? $\endgroup$
    – Dayne
    Commented Jun 5, 2021 at 23:05

2 Answers 2

2
$\begingroup$

Answer

This can be done by following a number of steps:

  1. Use grep to get the groups of columns to sum over
  2. Use rowSums over each group of columns
base <- c("GR_S01_w1", "GR_S01_w2")
cols <- lapply(base, grep, names(Gregory), fixed = TRUE)
for (i in seq_along(base)) {
  Gregory[, base[i]] <- rowSums(Gregory[, cols[[i]]])
}

This automates the whole process without defining any names manually (apart from the group names), and without having to transform your dataset to long and then back to wide.


Finding the sample names automatically

If you also don't want to have to specify the samples by hand, then you can use grep and sub. Here, we make the assumption that your structure is always "sample underscore letter", e.g. sample_d or test_sample_b. We can do this by using grep:

relevant_columns <- grep(".*_[a-zA-Z]{1}$", names(Gregory), value = TRUE)
base <- unique(sub("(_[a-zA-Z]{1})$", "", relevant_columns))
base
# [1] "GR_S01_w1" "GR_S01_w2"

What the grep term means:

  • .*: Any number of any characters.
  • _: Presence of an underscore, followed by...
  • [a-zA-Z]: Any of the alphabetic letters (lowercase or upper case).
  • {1}: Only one of those.
  • $: This is the end of the word.

Next, we just use sub to remove that part, select the unique values, and we're done.

This does assume that:

  1. There are no other columns that end with _[a-zA-Z]; you can just avoid those columns by inputting names(Gregory)[-1] or whatever columns you DON'T want to consider.
  2. Names are only followed by ONE letter, not e.g. two or three.
$\endgroup$
12
  • $\begingroup$ Thanks for this slamballais! Seems like there's not really a short cut which allows me to skip the process of specifying the columns I want to group , but maybe I can try this code and substitute the values of base with column numbers instead of column names, to avoid having to type out a hundred detailed column names :) $\endgroup$
    – Aashi
    Commented Jun 6, 2021 at 23:54
  • $\begingroup$ I added some code that automatically finds all the unique names in your columns, so that you don't have to type them out. $\endgroup$ Commented Jun 7, 2021 at 5:58
  • $\begingroup$ Perfect!! Yes this is what I was hoping could be done! THANK YOU :D $\endgroup$
    – Aashi
    Commented Jun 8, 2021 at 4:32
  • $\begingroup$ Would you possibly be able to explain the code from the first two steps (grep and rowSums) in a bit more detail so I can troubleshoot my errors? (my R skills are very entry level). At the moment this is the error I'm getting : > cols <- lapply(base, grep, names(teleZ), fixed = TRUE) > for (i in seq_along(base)) {teleZ[, base[i]] <- rowSums(teleZ, cols[i])} Error in base::rowSums(x, na.rm = na.rm, dims = dims, ...) : invalid 'na.rm' argument. (Database name is now teleZ, not Gregory) $\endgroup$
    – Aashi
    Commented Jun 8, 2021 at 4:48
  • $\begingroup$ I tried using lapply for the last step and there were no errors but the dataset didn't change at all $\endgroup$
    – Aashi
    Commented Jun 8, 2021 at 5:13
1
$\begingroup$

In base R you can do this quite easily with:

# creates new variable as sum of the 3 existing ones
data$GR_S01_w1 <- data$GR_S01_w1_a + data$GR_S01_w1_b + data$GR_S01_w1_c

# remove the 3 existing ones
data$GR_S01_w1_a <- data$GR_S01_w1_b <- data$GR_S01_w1_c <- NULL
$\endgroup$
1
  • $\begingroup$ Thanks Robert! This helps for the one set of columns, but to replicate that step across another hundred samples (typing out each sample name) is rather too much effort.... Maybe it's just something that can't really be automated, ended up doing it in Excel :) $\endgroup$
    – Aashi
    Commented Jun 4, 2021 at 22:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.