How to update dataframe column using information from another dataframe

Question

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.

Ronak Shah · Accepted Answer · 2021-10-08 10:15:56Z

2

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

answered Oct 8, 2021 at 10:15

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

U13-Forward · Accepted Answer · 2021-10-08 10:03:53Z

2

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
>

answered Oct 8, 2021 at 10:03

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Comments

Quixotic22 · Accepted Answer · 2021-10-08 09:55:21Z

1

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

answered Oct 8, 2021 at 9:55

Quixotic22

2,9241 gold badge9 silver badges14 bronze badges

Comments

Chris Ruehlemann · Accepted Answer · 2021-10-08 10:13:25Z

0

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

edited Oct 8, 2021 at 10:13

answered Oct 8, 2021 at 9:59

Chris Ruehlemann

21.5k4 gold badges15 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to update dataframe column using information from another dataframe

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related