Merge consecutive rows, if their numeric value have same operator sign using R

Question

I am having an R data frame with 4 columns. My goal is to merge the data frame based on df$symbol if they have the same value in a row. While doing this there are two conditions that are to be followed,

if same row-values have the same preceding operator (+/-) then find their mean, otherwise reject the rows.
if condition 1 is true for merging then merge the df$ID values with "|"

Here is my input df

df <- data.frame(
  symbol=c("A2M","A2M-AS1","A4GALT","AATBC","AATBC","ABCA17P","ABCA6","ABCA6","ABCA6"),
  logFC=c(-1.0912438,-0.8953089,-1.3640826,-2.2205471,1.3869134,2.3007386,-2.1360694,-1.1171276,-1.8703369),
  adj.P.Val=c(0.02647898,0.01443973,0.02797254,0.00654647,0.00874959,0.04703096,0.02161093,0.03133593,0.01409799),
  ID=c("217757_at","1564139_at","219488_at","205969_at","1563088_a_at","231164_at","0082_at","7504_at","4719_at"))

> df
   symbol      logFC  adj.P.Val           ID
1     A2M -1.0912438 0.02647898    217757_at
2 A2M-AS1 -0.8953089 0.01443973   1564139_at
3  A4GALT -1.3640826 0.02797254    219488_at
4   AATBC -2.2205471 0.00654647    205969_at
5   AATBC  1.3869134 0.00874959 1563088_a_at
6 ABCA17P  2.3007386 0.04703096    231164_at
7   ABCA6 -2.1360694 0.02161093      0082_at
8   ABCA6 -1.1171276 0.03133593      7504_at
9   ABCA6 -1.8703369 0.01409799      4719_at

I have used the following command to find means but wasn't able to implement my above-mentioned conditions with dplyr.

df %>%
  group_by(symbol) %>%
  summarise_at(c("logFC","adj.P.Val"), fun(mean))

The expected data frame should be like:

symbol     logFC     adj.P.Val      ID
A2M     -1.0912438  0.02647898  217757_at
A2M-AS1 -0.8953089  0.01443973  1564139_at
A4GALT  -1.3640826  0.02797254  219488_at
ABCA17P  2.3007386  0.04703096  231164_at
ABCA6   -1.7078446  0.02234828  0082_at | 7504_at | 4719_at

for (AATBC in symbol) logFc has (-2.2205471 & 1.3869134), therefore these two rows are removed because they have different operators
for (ABCA6 in symbol) all logFc has the same operator and therefore
we will find the mean (of logfc and adj p.value) and write ID in the same row as follows

user_3732271 · Answer

We can use summarise with across to apply functions on multiple blocks of columns after grouping by 'symbol' and filtering out the rows where the sign of 'logFC', 'adj.P.Val' is not the same

library(dplyr)
library(stringr)
df %>% 
  group_by(symbol) %>% 
  filter(across(c(logFC, adj.P.Val), ~ n_distinct(sign(.)) == 1)) %>%
  summarise(across(c(logFC, adj.P.Val), mean), ID = str_c(ID, collapse= " | "))
# A tibble: 5 x 4
#  symbol   logFC adj.P.Val ID                         
#                                  
#1 A2M     -1.09     0.0265 217757_at                  
#2 A2M-AS1 -0.895    0.0144 1564139_at                 
#3 A4GALT  -1.36     0.0280 219488_at                  
#4 ABCA17P  2.30     0.0470 231164_at                  
#5 ABCA6   -1.71     0.0223 0082_at | 7504_at | 4719_at

Merge consecutive rows, if their numeric value have same operator sign using R

1 Answers