rdk rdk -3 years ago 67
R Question

r: Add lowest value matching criteria and a corresponding observation to new columns

My data frame looks like this

ID score obs
1 0 a
1 11 b
1 12 c
2 1 d
2 11 e
2 2 f
3 0 g
3 0 h
3 0 i


I need to make sure each person has a score above 10. If a person has no score above 10, all rows the personID shows up need to be deleted. The lowest score above 10 needs to go to a new column and the corresponding obs needs to go into a new column too (*note: there is not a situation where a person has two of the same score). Result should look like this:

ID score obs nScore nObs
1 0 a 11 b
1 11 b 11 b
1 12 c 11 b
2 1 d 11 e
2 11 e 11 e
2 2 f 11 e


Ideally, every repeated personID should also be deleted so that the lowest score is kept in the score column. The result looks like this:

ID score obs nScore nObs
1 0 a 11 b
2 1 d 11 e


I would be happy if you could help me get to the first result. If you could help me get the second result, I don't require that you show me how to get to the first result before getting to the second result (like if you got to the second result all in one step).

Answer Source

You can try dplyr

#Q1 
DF1=DF%>%group_by(ID)%>%mutate(nScore=min(score[score>10]))%>%filter(!is.infinite(nScore))%>%mutate(nObs=obs[score==nScore])


# A tibble: 6 x 5
# Groups:   ID [2]
     ID score   obs nScore  nObs
  <int> <int> <chr>  <dbl> <chr>
1     1     0     a     11     b
2     1    11     b     11     b
3     1    12     c     11     b
4     2     1     d     11     e
5     2    11     e     11     e
6     2     2     f     11     e

#Q2
DF1%>%group_by(ID)%>%slice(which.min(score))
# A tibble: 2 x 5
# Groups:   ID [2]
     ID score   obs nScore  nObs
  <int> <int> <chr>  <dbl> <chr>
1     1     0     a     11     b
2     2     1     d     11     e
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download