CODEWITHSUNDEEP
rdataframebinding
french_fries
french_fries

Reputation: 1

How to make a specific binding of two tables

I have a single column dataframe with all possible IDs:

ID 
a1
a2
b1
b11
c1

I get dataframe from my database with same column "ID". But in that dataframe, not all IDs might be. Here is example of that table:

ID    value
a1     18
a2     10
b1     10

I want to bind those tw tables in that way, so IDs which were not in my table have value zero. So, how to bind these two tables to get this:


ID    value
a1     18
a2     10
b1     10
b11    0
c1     0

Upvotes: 0

Views: 40

Answers (2)

akrun
akrun

Reputation: 886948

We can also do

library(dplyr)
df1 %>%
     full_join(df2, by = 'ID') %>%
     mutate(value = case_when(is.na(value) ~ 0, TRUE ~ value))

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388817

join the two tables and replace NA value with 0.

Using dplyr :

library(dplyr)
df1 %>%
  full_join(df2, by = 'ID') %>%
  mutate(value = replace(value, is.na(value), 0))

#   ID value
#1  a1    18
#2  a2    10
#3  b1    10
#4 b11     0
#5  c1     0

In base R, you can do this as :

transform(merge(df1, df2, by = 'ID', all = TRUE), 
          value = replace(value, is.na(value), 0))

Upvotes: 0

Related Questions