CODEWITHSUNDEEP
rdataframe
MC09
MC09

Reputation: 95

Add multiple new columns to dataframe based on condition in R

Suppose I have a data set which looks like:

library(tidyverse)

df_raw <- data.frame(id = paste0('id', sample(c(1:13), replace = TRUE)), startTime = as.Date(rbeta(13, 0.7, 10) * 100, origin = "2016-01-01"), Channel = paste0('c', sample(c(1:3), 13, replace = TRUE, prob = c(0.2, 0.12, 0.3))) ) %>%
  group_by(id) %>%
  mutate(totals_transactions = sample(c(0, 1), n(), prob = c(0.9, 0.1), replace = TRUE)) %>%
  ungroup() %>%
  arrange(id, startTime)

Now I would like to summarize the same id's together and add columns to this new dataframe which indicates whether or not a certain channel is used by that id. I have done it like this:

seq_summaries <- df_raw %>%
  group_by(id) %>%
  summarize(
    c1_touches = max(ifelse(Channel == "c1",1,0)),
    c2_touches = max(ifelse(Channel == "c2",1,0)),
    c3_touches = max(ifelse(Channel == "c3",1,0)),
    conversions = sum(totals_transactions)
  ) %>% ungroup()

However, I'm searching for a way in which I don't have to manually create columns for every channel, as the number of channels could be much more than three which results in a lot of work.

Upvotes: 4

Views: 85

Answers (1)

www
www

Reputation: 39174

Here is one idea. Notice that you have no any c2 in your data frame. To use the complete function, you still need to provide a complete list of c (c1 to c3).

library(tidyverse)

df2 <- df_raw %>%
  group_by(id, Channel) %>%
  summarize(
    touches = 1L,
    conversions = as.integer(sum(totals_transactions))
  ) %>% 
  ungroup() %>%
  complete(Channel = paste0("c", 1:3)) %>%
  spread(Channel, touches, fill = 0L) %>%
  drop_na(id) %>%
  select(id, paste0("c", 1:3), conversions)
df2
# # A tibble: 8 x 5
#   id       c1    c2    c3 conversions
#   <fct> <int> <int> <int>       <int>
# 1 id10      1     0     0           0
# 2 id11      0     0     1           0
# 3 id12      0     0     1           1
# 4 id2       0     0     1           0
# 5 id3       0     0     1           0
# 6 id6       1     0     0           0
# 7 id8       1     0     0           1
# 8 id9       0     0     1           0

Upvotes: 2

Related Questions