dplyr::mutate new columns based on a second (filtered) dataframe

user_5355800 0
Viewed 52

I have 2 data frames:

df1:

a
s1
s2
s3
s4

df2:

a    b
s1    w
s1    x
s4    y
s2    z
s4    x

I want to append to df1 as many columns as unique values are in df2$b (w, x, y, z), and for each df1$a and new column add a 1 if df2$a has a row associating them. This is cumbersome to explain, maybe showing the desired output is better:

a    w    x    y    z
s1    1    1    0    0
s2    0    0    0    1
s3    0    0    0    0
s4    0    1    1    0

I have tried

for (col_name in unique(df2$b)){
  df1 %<>%
    mutate(!!as.character(col_name) := ifelse(col_name %in% filter(df2,
                                                                   a == df1$a)$b,
                                          yes = 1,
                                          no = 0))
}

But this does not work, and I guess the problem lies in the

a == df1$a

bit, but I do not know which is the proper syntaxis for this. Any help is appreciated!

dplyr tidyverse filter r
3 Answers

We can use table in base R (assuming that we wanted the row for all the values from 'a' column in 'df1', convert the 'a' column of 'df2' to factor with levels specified as the unique values of 'a' from 'df1'

table(transform(df2, a = factor(a, levels = unique(df1$a))))

-output

# b
#a    w x y z
#  s1 1 1 0 0
#  s2 0 0 0 1
#  s3 0 0 0 0
#  s4 0 1 1 0

data

df1 <- structure(list(a = c("s1", "s2", "s3", "s4")), class = "data.frame",
row.names = c(NA, 
-4L))

df2 <- structure(list(a = c("s1", "s1", "s4", "s2", "s4"), b = c("w", 
"x", "y", "z", "x")), class = "data.frame", row.names = c(NA, 
-5L))
user_37322710

Another dplyr and tidyr solution.

library(dplyr)
library(tidyr)

df3 <- df2 %>%
  mutate(value = 1) %>%
  complete(a = df1$a, b) %>%
  spread(b, value, fill = 0)

df4 <- df1 %>%
  left_join(df3, by = "a")

DATA

df1 <- tibble(
  a = paste0("s", 1:4)
)

df2 <- read.table(text = "
a    b
s1    w
s1    x
s4    y
s2    z
s4    x",
                header = TRUE)
user_76698090

Try this:

library(dplyr)
library(tidyr)
#Code
newdf <- df1 %>% full_join(df2) %>%
  mutate(val=ifelse(!is.na(b),1,NA),
         b=ifelse(is.na(b),'Emp',b)) %>%
  pivot_wider(names_from = b,values_from=val,
              values_fill=0,names_sort = T) %>%
  select(-Emp)

Output:

# A tibble: 4 x 5
  a         w     x     y     z
  <chr> <dbl> <dbl> <dbl> <dbl>
1 s1        1     1     0     0
2 s2        0     0     0     1
3 s3        0     0     0     0
4 s4        0     1     1     0
user_20808480
Related