How to update dataframe column using information from another dataframe-CodePudding

I have 2 dataframes:

df1 = data.frame(Bird_ID = c(1:6), Sex = c("Male","Female","Male","Male","Male","UNK"))
df2 = data.frame(Bird_ID = c(6), Seen_sex = c("Female"))

df1
    # Bird_ID Sex
    # 1 Male
    # 2 Female
    # 3 Male
    # 4 Male
    # 5 Male
    # 6 UNK
    
    df2
    # Bird_ID Seen_Sex
    # 6 Female

My first dataframe (df1) is my database where I have all my birds with known sex.
My second dataframe (df2) is the "updater"

How can I update the bird 6 in df1 using information from df2? So "UNK" in df1 should become "Female" now and all other birds stay the same.

CodePudding user response：

I personally prefer to left things on and the coalesce the columns like so

library(dplyr)
left_join(df1, df2, by= "Bird_ID") %>%
  mutate(
    Sex = coalesce(Seen_sex,  Sex)
  ) %>%
  select(-Seen_sex)

But you could just update the specific record by finding the row and overwriting it.

df1[df2$Bird_ID == df1$Bird_ID,] = df2

CodePudding user response：

With dplyr >= 0.5 version:

> merge(df1, setNames(df2, c('Bird_ID', 'Sex')), on='Bird_ID', all=T) %>% distinct(Bird_ID, .keep_all=T)
  Bird_ID    Sex
1       1   Male
2       2 Female
3       3   Male
4       4   Male
5       5   Male
6       6 Female
>

CodePudding user response：

Using dplyr:

library(dplyr)
df1 %>%
  left_join(., df2) %>%
  mutate(Sex = ifelse(!is.na(Seen_sex), Seen_sex, Sex)) %>%
  select(-Seen_sex)
Joining, by = "Bird_ID"
  Bird_ID    Sex
1       1   Male
2       2 Female
3       3   Male
4       4   Male
5       5   Male
6       6 Female

in base R:

df1 <- merge(df1, df2, by = "Bird_ID", all = TRUE)
df1$Sex[!is.na(df1$Seen_sex)] <- df1$Seen_sex[!is.na(df1$Seen_sex)]
df1$Seen_sex <- NULL

CodePudding user response：

You may use match in base R -

df1$Sex[match(df2$Bird_ID, df1$Bird_ID)] <- df2$Seen_sex
df1

#  Bird_ID    Sex
#1       1   Male
#2       2 Female
#3       3   Male
#4       4   Male
#5       5   Male
#6       6 Female