r rearrange rows by Id

Question

Suppose if this is my dataset

  Id    Name     Score     Fear     Satisfaction
  1     A        10        NA       NA
  1     B        11        NA       NA
  1     A        NA        23.2     NA
  1     B        NA        34.1     NA
  1     A        NA        NA       42.11
  1     B        NA        NA       31.19

How can I cleanup this dataset such that all rows are aligned correctly like this below and there aren't too many unnecessary NAs like this below

 Id    Name     Score     Fear     Satisfaction
  1     A        10        23.2     42.11
  1     B        11        34.1     31.19

I apologize if I was unable to explain this more accurately but I assume the example should help. Thanks in advance.

Using `dplyr`, `df %>% group_by(Id, Name) %>% summarise_all(na.omit) ` — Ronak Shah, Oct 01 '19 at 05:15

Siddharth Jain · Answer 1 · 2019-10-01T06:00:00.183

1

If you have a limited number of columns, then I believe this is the best way:

SELECT * 
FROM <table_name> 
WHERE 
       Score IS NOT NULL
   AND Fear IS NOT NULL 
   AND Satisfaction IS NOT NULL

edited Oct 01 '19 at 06:00

answered Oct 01 '19 at 05:27

Siddharth Jain

118
1
14

Not good at SQL: What does `Score IS NOT` do? I also think this might be inefficient if you have so many conditions. – NelsonGon Oct 01 '19 at 05:58

score 0 · Accepted Answer · answered Oct 01 '19 at 05:20

0

Assuming you have equal number of non-NA values per group and per column as shown in the example, we cangroup_by Id and Name and remove NA values

library(dplyr)

df %>%  group_by(Id, Name) %>%  summarise_all(na.omit)

#     Id Name  Score  Fear Satisfaction
#  <int> <fct> <int> <dbl>        <dbl>
#1     1 A        10  23.2         42.1
#2     1 B        11  34.1         31.2

answered Oct 01 '19 at 05:20

Ronak Shah

377,200
20
156
213

that was perfect , thanks. – Emily Fassbender Oct 01 '19 at 14:55

r rearrange rows by Id

2 Answers2