CODEWITHSUNDEEP
r
Philippe
Philippe

Reputation: 857

R match NA in a selection of columns based on the column name

I have a dataset containing per month revenue per client : Underneath is a working minimal sample. (the real dataset runs over multiple years, all months and multple clients, but you get the picture.)

client <-c("name1","name2","name3","name4","name5","name6")
Feb2018 <- c(10,11,NA,21,22,NA)
Jan2018 <- c(20,NA,NA,NA,58,NA)
Dec2017 <- c(30,23,33,NA,NA,NA)
Nov2017 <- c(40,22,75,NA,NA,11)
df <- data.frame(client,Feb2018,Jan2018,Dec2017,Nov2017)

My objective is to have our revenue split up between 'new','recurrent'&'lost', by adding an extra column.

That is :

  • new : clients having some revenue in 2018 but none in 2017. (name4 & name5)
  • recurrent : clients having some revenue in 2017 & 2018. (name1 & name2)
  • lost : clients having some revenue in 2017 but none in 2018. (name3 & name6)

I know how to use grep to select the column names, 

df[,c('client',colnames(df[grep('2018$',colnames(df))]))]

I also know how to use is.na. but I'm really stuck in making the combination of having a selection on both the column name & the existance of NA in the selected column.

Seen I'm thinking in circles now for some hours now, I would appreciate some help. Thanks for reading.

Upvotes: 1

Views: 93

Answers (1)

akrun
akrun

Reputation: 887118

We can gather into 'long' format and then apply the conditions and later do a join

library(dplyr)
library(tidyr)
df %>%
  gather(key, val,  -client, na.rm = TRUE) %>% 
  group_by(client) %>% 
  mutate(newcol = case_when(any(grepl('2018', key)) & all(!grepl('2017', key))~ 'new', 
                           any(grepl('2018', key)) & any(grepl('2017', key)) ~ 'recurrent',
                           any(grepl('2017', key)) & all(!grepl('2018', key)) ~ 'lost')) %>%
  distinct(client, newcol) %>%
  right_join(df)
# A tibble: 6 x 6
# Groups: client [?]
#   client newcol    Feb2018 Jan2018 Dec2017 Nov2017
#  <fctr> <chr>       <dbl>   <dbl>   <dbl>   <dbl>
#1 name1  recurrent    10.0    20.0    30.0    40.0
#2 name2  recurrent    11.0    NA      23.0    22.0
#3 name3  lost         NA      NA      33.0    75.0
#4 name4  new          21.0    NA      NA      NA  
#5 name5  new          22.0    58.0    NA      NA  
#6 name6  lost         NA      NA      NA      11.0

Upvotes: 1

Related Questions