Merging two dataframes with left_join produces NAs in 'right' columns

user_11831133 0
Viewed 533

When I use dplyr::left_join to combine 2 dataframes, all of the 'right' dataframe columns are filled with NA values.

I have checked multiple other answers on StackOverflow to try and eliminate the source of my mistake, including https://stackoverflow.com/questions/35016377/dplyrleft-join-produce-na-values-for-new-joined-columns]

However, the answers already available on Stack have not been able to fix my issue.

Here is my reproducible code

# Libraries
library('remotes')
library("tidytuesdayR")
library('ggplot2')
library("tidyverse")

# Load data
tuesdata <- tidytuesdayR::tt_load('2021-01-19')
gender <- tuesdata$gender
crops <-tuesdata$crops
households <- tuesdata$households

#rename crops column
colnames(crops)[1]<-"County"
# make County columns into characters
gender$County <- as.character(gender$County)
crops$County <- as.character(crops$County)
households$County <- as.character(households$County)
# Change "total" cell to "kenya"
gender[1, 1] <- "Kenya"
# All caps to Title case
crops$County<-str_to_title(crops$County)

# left_join households and crops column
df<- left_join(households, crops, by=c("County"="County"))

When I run this, every single 'crops' column is filled with NAs. My overall goal to to merge all three datasets (crops, households, and gender) by county name in Kenya.

I could use some assitance. Thanks.

dplyr tidyverse r
1 Answers

You need to trim the County variable in the households df - there are extra spaces so it is matching incorrectly with the crops df. E.g.:

"Kenya   "
"Mombasa   "

Adding this extra line before the left_join fixes it:

households$County <- stringr::str_trim(households$County)
df <- left_join(households, crops)
user_63230150
Related