CODEWITHSUNDEEP
rdataframefilteringna
Sai Ram
Sai Ram

Reputation: 53

Filter dataframe based on column values and include NA for those which dont contain the column value

I have a dataframe as below.

Hospital    State   Mortality   Rank
aaa          AK        9.7       1    
bbb          AK        10.5      2 
ccc          AK        11.3      3
ddd          AL         5.6      1
eee          AL         8.7      2 
fff          AL         9.1      3
ggg          AL         9.3      4 
hhh          AR         9.9      1
iii          AR         10.2     2
jjj          TX          6.5     1
kkk          TX          6.5     2
lll          TX          8.3     3
mmm          TX          8.4     4

for reproducability 

df <- data.frame(Hospital=c("aaa","bbb","ccc","ddd","eee","fff","ggg","hhh","iii","jjj","kkk","lll","mmm"),State=c("AK","AK","AK","AL","AL","AL","AL","AR","AR","AZ","AZ","AZ","AZ"), Mortality=c(9.7,10.5,11.3,5.6,8.7,9.1,9.3,9.9,10.2,6.5,6.5,8.3,8.4),Rank=c(1,2,3,1,2,3,4,1,2,1,2,3,4))

when I search for hospitals with rank 4 I want an outcome as below, returning NA for Hospitals under each state which don't have the passed rank

Hospital    State   
NA          AK            
ggg         AL        
NA          AR        
mmm         TX

currently I only get those rows which contains the rank with value 4. 

Hospital    State 
ggg         AL
mmm         TX

is there a faster way other than creating a df containing 4 rows for each of the state leaving NA under hospital for those state that dont have the expected rank value and then filter them.

Upvotes: 0

Views: 160

Answers (2)

www
www

Reputation: 39154

A solution from dplyr.

library(dplyr)

df2 <- df %>%
  group_by(State) %>%
  summarise(Rank = max(Rank)) %>%
  left_join(df, by = c("State", "Rank")) %>%
  mutate(Hospital = ifelse(Rank < 4, NA_character_, as.character(Hospital))) %>%
  select(Hospital, State)
df2
# A tibble: 4 x 2
  Hospital  State
     <chr> <fctr>
1     <NA>     AK
2      ggg     AL
3     <NA>     AR
4      mmm     AZ

Upvotes: 0

lmo
lmo

Reputation: 38500

You can get this result with merge and setting the all.y argument to TRUE:

merge(df[df$Rank == 4,], unique(df["State"]), all.y=TRUE)
  State Hospital Mortality Rank
1    AK     <NA>        NA   NA
2    AL      ggg       9.3    4
3    AR     <NA>        NA   NA
4    AZ      mmm       8.4    4

Here, the idea is to get a data.frame with a single variable of unique state names and merge it onto the data.frame that contains hospitals of rank 4. Since the data.frame with states is the second argument, keep.y=TRUE tells merge to keep all the states in the final data.frame.

To return just the two columns, you could further subset the first argument to merge like

merge(df[df$Rank == 4, c("State", "Hospital")], unique(df["State"]), all.y=TRUE)

Upvotes: 1

Related Questions