how to merge and summerise rows based on 2 columns of dataframe in r

Question

I have below data frame as df1:

    Date        id  Age  B   R       S
1   00/01/16    223 55  7.9  5.65   138
2   00/01/16    223 55  NA   NA      NA
3   00/01/16    223 55  NA   NA      NA
4   00/01/17    223 55  NA   NA      NA
5   00/01/17    223 55  9.6  5.71   135
6   00/01/17    223 55  NA   NA      NA
7   00/01/18    223 55  NA   NA      NA
8   00/01/18    223 55  NA   NA      NA
9   00/01/18    223 55 11.5  6.11   135
10  00/01/17    223 55  NA   NA      NA
11  00/01/05    102 60  NA   NA     135
12  00/01/05    102 60  19.7 5.5     NA
13  00/01/05    102 60  NA   NA      NA
14  00/01/05    102 60  NA   NA      NA
15  00/01/06    102 60  18.5 5.34   144
16  00/01/06    102 60  NA   NA      NA
17  00/01/06    102 60  NA   NA      NA

First I need to merge rows based on "id" and then merge rows based on "Date".My problem is not omited raws with NA.for example, in raws No. 11 and 12, I have to select between 135 and 143 for "S" column. Finally, my out put should be as below data frame (df2):

      Date       id  Age     B     R     S
1   00/01/16    223  55     7.9   5.65  138
2   00/01/17    223  55     9.6   5.71  135
3   00/01/18    223  55     11.5  6.11  135
4   00/01/05    102  60     19.7  5.5   135
5   00/01/06    102  60     18.5  5.34  144

I wrote below code:

df2 <- df1 %>% 
  group_by(Date,id) %>% 
  summarise_all(funs(na.omit))

but I got the below error:

Error: Problem with `summarise()` column `S`.
i `S = na.omit(S)`.
x `S` must be size 6 or 1, not 0.
i An earlier column had size 6.
i The error occurred in group 1: Request_Date = "00/01/05", Patient.Code = 223

I appreciate it if anybody shares his/her comment with me.

Bests Regards

How do you choose which one to keep on rows 11 and 12, (135 vs 143)? — zx8754, May 30 '22 at 10:15
depends on the value of the "B" column. between rows No. 11 and 12, I select one for "S" that has value for the "B" column. here row No. 12 has a value for "B" then I selected 143 for "S". — Mohammad, May 30 '22 at 10:20
yes, but we have to complete another column of chosen rows by the value of the other rows that have a similar id. — Mohammad, May 30 '22 at 10:29
Of course you can, it is your question - https://stackoverflow.com/posts/72431220/edit — zx8754, May 30 '22 at 10:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245173/discussion-between-mohammad-and-zx8754). — Mohammad, May 30 '22 at 10:38

Darmist · Accepted Answer · 2022-05-30T11:16:03.447

2

Turning data into a long format, and then back to wide should do something similar, I think. Try this:

library(tidyr)

df2 = df %>% 
  pivot_longer(cols = c(B, R, S)) %>% 
  filter(is.na(value) == FALSE) %>% 
  pivot_wider(names_from = name, values_from = value)

edited May 30 '22 at 11:16

answered May 30 '22 at 11:00

Darmist

50
9

score 0 · Answer 2 · answered May 30 '22 at 08:27

0

Seems that you're only deleting rows with NAs:

df1 |> complete.cases()

answered May 30 '22 at 08:27

flopeko

149
8

Dear @flopeko, My problem is not just omitted raws with NA. For example, in raws No. 11 and 12, I have to select between 135 and 143 for "S" column. I appreciate it if you guide me. – Mohammad May 30 '22 at 09:18

how to merge and summerise rows based on 2 columns of dataframe in r

2 Answers2