CODEWITHSUNDEEP
r
Lime
Lime

Reputation: 756

How to join two columns then assign missing values

I want to join two dataframes by the column OBS_DT, however, one of these datasets are missing values from the other, so only the values found are connected and the rest are removed, when I use this code:

data_bird <- inner_join(df1, df2, by = "OBS_DT")

I want to join these two dataframes but when one of the dataframes do not have the values assigned to the others column, then instead of removing those values, it will create a character vector, assigned the letter X.

An example of both the dataframes being used:

df1<-structure(list(year = c(2010, 2010, 2010, 2010, 2011, 2011, 2011, 
2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 
2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 
2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 
2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 
2011, 2011, 2011, 2011, 2011, 2012, 2012, 2012, 2012, 2012, 2012, 
2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 
2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 
2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 
2012, 2012, 2012, 2012, 2012, 2012), week = c(49, 50, 51, 52, 
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 
35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 
51, 52, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 
17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 
33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45), OBS_DT = structure(c(14948, 
14955, 14962, 14969, 14976, 14983, 14990, 14997, 15004, 15011, 
15018, 15025, 15032, 15039, 15046, 15053, 15060, 15067, 15074, 
15081, 15088, 15095, 15102, 15109, 15116, 15123, 15130, 15137, 
15144, 15151, 15158, 15165, 15172, 15179, 15186, 15193, 15200, 
15207, 15214, 15221, 15228, 15235, 15242, 15249, 15256, 15263, 
15270, 15277, 15284, 15291, 15298, 15305, 15312, 15319, 15326, 
15333, 15340, 15347, 15354, 15361, 15368, 15375, 15382, 15389, 
15396, 15403, 15410, 15417, 15424, 15431, 15438, 15445, 15452, 
15459, 15466, 15473, 15480, 15487, 15494, 15501, 15508, 15515, 
15522, 15529, 15536, 15543, 15550, 15557, 15564, 15571, 15578, 
15585, 15592, 15599, 15606, 15613, 15620, 15627, 15634, 15641, 
15648), class = "Date"), `IP20 0HR` = c(0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `IP20 9LR` = c(0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `IP20 9PJ` = c(0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 69, 68, 51, 60, 33, 36, 48, 50, 44, 34, 35, 
30, 34, 42, 41, 37, 65, 43, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
    `IP20 9QE` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `IP21 4NT` = c(0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0), `IP21 4NZ` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `IP21 4PT` = c(0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0)), row.names = 50:150, class = "data.frame")

and

df2<-structure(list(OBS_DT = structure(c(17230, 17239, 14498, 17653, 
17850, 14361, 16101, 16222, 17590, 17538, 13116, 15953, 13141, 
13999, 16746, 17532, 17991, 12246, 17852, 12362, 14937, 15715, 
17085, 16546, 17832, 9131, 9131, 9131, 9131, 17883, 9131, 9131, 
9131, 12035, 12856, 12208, 13450, 14608, 14618, 15368, 16512, 
17181, 17839, 14684, 16455, 16461, 18358, 13422, 16155, 16156, 
16231, 17532, 16843, 18192, 14855, 15746, 12270, 13179, 13805, 
11600, 14457, 15512, 15459, 15814, 17855, 17485, 18079, 17342, 
11956, 17775, 17776, 12529, 9131, 15547, 9131, 9131, 9131, 12043, 
13274, 13973, 17905, 9131, 9132, 9131, 9131, 9131, 9131, 14610, 
15340, 14696, 15438, 15475, 17211, 17534, 17266, 17808, 18103, 
15181, 15305, 15312, 16418), .Names = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_), class = "Date"), 
    GARDEN_SIZE = c("S", "M", "L", "L", "L", "S", "M", "M", "M", 
    "S", "L", "L", "M", "S", "M", "M", "L", "M", "M", "M", "M", 
    "M", "M", "L", "L", "M", "L", "L", "L", "L", "M", "M", NA, 
    "S", "M", "L", "M", "M", "M", "S", "M", "S", "M", "M", "S", 
    "L", "M", "L", "M", "M", "S", "S", "L", "M", "M", "M", "S", 
    "L", "L", "M", "S", "M", "L", "L", "M", "M", "M", "M", "S", 
    "S", "S", "M", NA, "M", "M", "L", "S", "M", "M", "M", "M", 
    "L", "L", "M", "L", "L", "M", "L", "L", "L", "L", "M", "S", 
    "S", "L", "L", "L", "M", "L", "M", "M"), GARDEN_AGE = c("E", 
    "E", "D", "E", "D", "B", "D", "D", "A", "E", "E", NA, "D", 
    "D", "D", "D", "D", "D", "D", "D", "D", "D", "B", "E", "D", 
    "D", "D", "D", "E", "D", "D", "D", "C", "D", "E", "C", "D", 
    "D", "D", "C", "E", "E", "D", "E", "E", "E", "A", "E", "E", 
    "E", "C", "E", "E", "E", "D", "D", "D", "D", "D", "D", "D", 
    "A", "D", "E", "D", "D", "D", "D", "B", "A", "A", "D", "E", 
    "E", "E", "D", "D", "D", "D", "C", "D", "B", "E", "D", "E", 
    "D", "C", "D", "D", "E", "E", "E", "E", "D", "E", "D", "D", 
    "E", "E", "E", "E"), SMALL_DECID_TREES = c("E", "A", "A", 
    "E", "C", "A", "C", "C", "B", "C", "E", "E", "D", "B", "E", 
    "E", "E", "B", "B", "E", "C", "A", "D", "E", "D", "D", "E", 
    "E", "E", "D", "D", "D", "E", "C", "C", "E", "D", "E", "C", 
    "B", "C", "D", "C", "D", "A", "E", "D", "A", "E", "E", "A", 
    "C", "C", "D", "A", "B", "C", "C", "E", "D", "A", "C", "C", 
    "C", "A", "C", "C", "D", "C", "A", "A", "D", "C", "D", "C", 
    "D", "C", "D", "D", "E", "B", "E", "E", "C", "E", "D", "D", 
    "D", "C", "E", "E", "C", "B", "C", "C", "D", "D", "B", "E", 
    "C", "C"), LARGE_DECID_TREES = c("A", "C", "E", "E", "A", 
    "A", "A", "A", "A", "A", "E", "E", "A", "A", "C", "B", "E", 
    "B", "C", "B", "D", "A", NA, "E", "D", NA, "C", "A", "E", 
    "E", "C", "B", "A", "C", "B", "E", "A", "A", "A", "B", "B", 
    "D", "B", "C", "C", "E", "A", "B", "B", "B", "A", "A", "C", 
    "B", "A", "C", NA, "D", "B", "A", "A", "A", "A", "A", "C", 
    "A", "A", "D", "A", "A", "A", "B", "B", "B", "C", NA, NA, 
    "E", "E", "C", "C", "E", "C", "C", "E", "D", "C", "C", "A", 
    "E", "E", "A", "A", "A", "E", "D", "D", "C", "C", "D", "C"
    ), SMALL_CONIF_TREES = c("A", "A", "B", "E", "D", "A", "A", 
    "A", "A", "B", "A", "A", "B", "A", "B", "B", "E", "A", "B", 
    "C", "A", "A", "B", "C", "C", "C", "D", "E", "E", "D", "C", 
    "C", "C", "C", "D", "E", "C", "E", "C", "A", "A", "A", "B", 
    "C", "B", "D", "D", "A", "C", "C", "A", "C", "C", "A", "B", 
    "C", NA, "E", "E", "D", "A", "A", "A", "A", "A", "C", "C", 
    "C", "C", "A", "A", "C", NA, "C", "E", NA, "C", "C", "C", 
    "C", "B", "E", "B", "A", "C", "E", "B", "B", "B", "D", "A", 
    "B", "B", "A", "A", "C", "C", "A", "E", "D", "A"), LARGE_CONIF_TREES = c("A", 
    "A", "C", "E", "A", "A", "A", "A", "A", "A", "E", "E", "A", 
    "B", "A", "A", "E", "A", "A", "A", "B", "A", NA, "A", "C", 
    NA, "E", "A", "E", "B", "A", "C", "A", "A", NA, "E", "A", 
    "A", "E", "A", "A", "E", "A", "A", "A", "E", "A", "A", "B", 
    "B", "A", "A", "A", "A", "D", "A", NA, "D", "C", "A", "A", 
    "A", "A", "B", "A", "A", "A", "A", "A", "A", "A", "A", NA, 
    "A", "B", NA, NA, "A", "A", "A", "B", "C", "E", "A", "D", 
    "E", "C", "A", "A", "E", "C", "A", "A", "A", "D", "B", "B", 
    "C", "C", "C", "A"), LAWN = c("B", "C", "B", "C", "D", "C", 
    "A", "C", "C", "C", "D", "C", "E", "D", "D", "D", "B", "D", 
    "D", "D", "D", "D", "D", "D", "C", "A", "C", "B", "B", "D", 
    "D", "B", "D", "B", "D", "C", "D", "C", "C", "C", "D", "D", 
    "E", "B", "E", "B", "B", "C", "C", "C", "D", "D", "C", "C", 
    "B", "D", "E", "C", "C", "D", "D", "E", "D", "D", "C", "C", 
    "C", "C", "B", "A", "A", "C", "B", "B", "C", "C", "C", "B", 
    "B", "B", "B", "C", "D", "D", "E", "C", "D", "B", "B", "C", 
    "B", "B", "C", "B", "C", "C", "C", "C", "B", "D", "D"), FLOWERBEDS = c("C", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "A", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", NA, "B", 
    "B", "C", "B", "B", "B", "B", "B", "A", "B", "D", "C", "B", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", "B", "C", "B", "B", "B", "B", "A", "A", NA, "C", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "C", "B", "B", "B", "B", "C", "B", "B", "C", "B", "B", 
    "B", "B", "B", "B"), SHRUBBERIES = c("B", "B", "B", "B", 
    "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", "B", "B", "B", "A", "B", "B", "C", NA, "B", "B", 
    "C", "B", "B", "C", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", "B", "A", "B", "B", "C", "A", "B", "B", "B", "B", 
    "B", "B", "B", "A", "A", "B", "B", "B", "B", "A", "A", "B", 
    "B", "B", "B", "B", "B", "A", "A", "C", "B", "B", "B", "B", 
    "B", "A", "A", "B", "B", "B", "B", "C", "B", "B", "B", "B", 
    "B", "B", "B", "B", "A", "C", "B", "B", "B", "A", "B", "B", 
    "A"), VEGETABLES = c("B", "A", "B", "B", "A", "A", "B", "A", 
    "B", "A", "B", "B", "A", "A", "A", "A", "B", "A", "B", "B", 
    "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", NA, NA, "A", "A", "A", "A", "A", "A", "A", "B", 
    "A", "B", "B", "A", "A", "A", "A", "A", "A", "A", "A", "A", 
    "A", "B", "B", "A", "A", "A", "B", "B", "A", "B", "B", "A", 
    "A", "A", "A", "B", "B", "B", "C", "A", NA, "B", "B", "A", 
    "B", "B", "B", "B", NA, "A", NA, "A", "A", "B", "B", "A", 
    "B", "A", "B", "A", "B", "B", "B", "A", "A")), row.names = c(NA, 
-101L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 1

Views: 47

Answers (1)

akrun
akrun

Reputation: 887881

We can use left_join and then replace the NAs later with 'X'

library(dplyr)
library(tidyr)
left_join(df1, df2, by = 'OBS_DT') %>%
     mutate(across(setdiff(names(df2), 'OBS_DT'), ~ replace_na(., 'X')))

Upvotes: 3

Related Questions